MySQL/MariaDB
Common commands/use
mysql -e presumes that you have a .my.cnf in your user directory populated with the username and password of a user that has the privileges to do what you are doing. Such as: /root/.my.cnf
Otherwise, you would need to log into MySQL/MariaDB then execute the queries that are within the quotes.
Time and Date in MySQL/MariaDB
mysql -e "SELECT NOW();"
Show size of databases
mysql -e "SELECT table_schema AS 'Database Name', SUM(data_length + index_length) / 1024 / 1024 AS 'Database Size (MB)' FROM information_schema.TABLES GROUP BY table_schema;" | grep 'Database\|USERNAME_'
MyISAM table size & InnoDB table size
MYD+MYI = total MyISAM, ibdata1+ibd = total InnoDB. MYI are the MyISAM indexes, MYD is the MyISAM data.
lsof -u mysql|awk '$9~/\/var\/lib\/mysql\// {print $7, $9}'|awk -F'[/,.]' '{print $1, $NF}'|sort -k2|awk '{array[$2]+=$1} END {for (i in array) {print array[i]"\t"i}}'
You can also do this query, but it can trash the cache:
mysql -e "SELECT ENGINE,ROUND(SUM(data_length) /1024/1024, 1) AS 'Data MB', ROUND(SUM(index_length)/1024/1024, 1) AS 'Index MB', ROUND(SUM(data_length + index_length)/1024/1024, 1) AS 'Total MB', COUNT(*) 'Num Tables' FROM INFORMATION_SCHEMA.TABLES WHERE table_schema not in ('information_schema', 'performance_schema') GROUP BY ENGINE;"
Max Connections used
mysql -e 'show status;' | grep Max
Grants (Privileges)
Show Grants
cPanel:
Databases > MySQL Databases > Click the username on the database.
CLI:
Login as the user in question first. Provides grants for all dbs this user has access to.
mysql -u username -p -e "SHOW GRANTS;"
Or, as root:
show grants for 'user_name'@localhost;
Set Grants (non-cPanel)
GRANT ALL PRIVILEGES ON database.* to 'database_user'@'localhost' IDENTIFIED BY 'password';
Change User Password (non-cPanel)
SET PASSWORD FOR 'user_name'@'localhost' = PASSWORD('password')
Show compiled-in options
Binary options plus my.cnf and other option files:
mysqld --verbose --help
Binary options ignoring my.cnf and other option files:
mysqld --no-defaults --verbose --help
This also works for mysqldump, myisamchk, and anything else that you'll see as a header in my.cnf. For example:
mysqldump --verbose --help
Handy to see if "Dump Completed" message will be appended to .sql dump files:
mysqldump --verbose --help | grep 'comments\|dump-date'
Change User Password (non-cPanel)
SET PASSWORD FOR 'user_name'@'localhost' = PASSWORD('password');
Dumping and Importing
Dumping
mysqldump database_name > database_name.sql
Should append a "Dump completed on %date" at the end of each .sql file to show a successful dump. This is usually a binary option, but it it is not, set these in my.cnf:
comments
dump-date
Importing
CLI:
mysql database_name < database_name.sql
phpMyAdmin:
Import tab. Make sure that upload_filesize, post_max_size, and max_execution_time are generous enough in WHM >> Tweak Settings >> PHP for the import.
You usually need to drop a database before safely importing a .sql file. Dump the db, note its users and their privileges, recreate it, reassign the users and privileges, then import. If phpMyAdmin or cPanel is not available to you, then you need the MySQL users and passwords to see the privileges (easiest for noting).
Table-by-table dump for a single database
for table in `mysql DB -e 'show tables;'| sed -n '1!p'`; do echo $table; mysqldump DB $table > DB.$table.sql ; done
Restore only one table from a .sql file
sed -n '/-- Table structure for table `'TABLE_NAME'`/,/-- Table/{ /^--.*$/d;p }' DUMPFILE.sql > DB.TABLE_NAME.sql
mysql DB < DB.TABLE_NAME.sql
Or try this:
sed -n -e '/-- Table structure for table `'"tablename"'`/,/UNLOCK TABLES/p' "databasename.sql" > "database.table.sql"
Check stored procedures
phpMyAdmin >> SQL
show procedure status
See all db users
phpMyAdmin >> Privileges
Binlog Dump
This is associated with replication, it's the process of the master server writing to the slave server.
Create secondary MySQL instance for restoring raw files from another server
Start a screen and create your work area:
screen -S restore mkdir -p /home/temp/$TICKET/$USER/to_restore/databases/datadir ls -ld /home/temp/
If perms are 750, make them 751, otherwise leave them:
chmod 751 /home/temp/
Copy the raw files from the backup server or wherever to the temp datadir:
[backupServer>] rsync -avHP mysql ibdata1 ib_logfile* my_db IP:/home/temp/$TICKET/$USER/to_restore/databases/datadir/
Create the socket file you will use:
touch /home/temp/$TICKET/$USER/to_restore/databases/datadir/socket.sql chown -Rv mysql. /home/temp/$TICKET/$USER/to_restore/databases/datadir/
Create the second MySQL instance:
mysqld \ --datadir=/home/temp/$TICKET/$USER/to_restore/databases/datadir \ --socket=/home/temp/$TICKET/$USER/to_restore/databases/datadir/socket.mysql \ --pid-file=/home/temp/$TICKET/$USER/to_restore/databases/datadir/mysql.pid \ --log-error=/home/temp/$TICKET/$USER/to_restore/databases/datadir/mysql.err \ --skip-grant-tables \ --skip-networking \ --user=mysql &
Make sure the databases are there:
mysql -S /home/temp/$TICKET/$USER/to_restore/databases/datadir/socket.mysql -e 'show databases;'
Dump:
mysqldump -S /home/temp/$TICKET/$USER/to_restore/databases/datadir/socket.mysql $my_db > $my_db.sql tail -n1 $my_db.sql
Shut down the second instance:
mysqladmin -S /home/temp/$TICKET/$USER/to_restore/databases/datadir/socket.mysql shutdown
Troubleshooting
InnoDB Disabled?
If you run into relentless DB corruption:
mysqlcheck -Br roundcube
roundcube.cache
Error : Incorrect information in file: './roundcube/cache.frm'
error : Corrupt
roundcube.cache_index
Error : Incorrect information in file: './roundcube/cache_index.frm'
error : Corrupt
roundcube.cache_messages
Error : Incorrect information in file: './roundcube/cache_messages.frm'
error : Corrupt
InnoDB may be disabled. Try:
mysql -e "show engines;"
If InnoDB is disabled, check the error log for why. If you get something like this:
130424 06:18:09 mysqld restarted
130424 6:18:28 InnoDB: Error: cannot allocate 536887296 bytes of
InnoDB: memory with fakeuser! Total allocated memory
InnoDB: by InnoDB 8154720 bytes. Operating system errno: 12
InnoDB: Check if you should increase the swap file or
InnoDB: ulimits of your operating system.
InnoDB: On FreeBSD check you have compiled the OS with
InnoDB: a big enough maximum process size.
InnoDB: Note that in most 32-bit computers the process
InnoDB: memory space is limited to 2 GB or 4 GB.
InnoDB: We keep retrying the allocation for 60 seconds...
InnoDB: Fatal error: cannot allocate the memory for the buffer pool
130424 6:20:09 [Note] /usr/sbin/mysqld: ready for connections.
In this case, lower innodb_buffer_pool_size from 512M to a reasonable value.