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.
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'
Time and Date in MySQL/MariaDB
mysql -e "SELECT NOW();"
Show Active Storage Engines
mysql -e "show engines;"
Show InnoDB status
mysql -Bse 'show engine innodb status\G'
Show size of databases
Note: This doesn't give any results on MariaDB 10.6. Need to look into why, and what can substitute.
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_'
List tables of a particular storage engine
Using InnoDB for these examples, but you can substitute MyISAM or whatever inside the queries.
List all InnoDB tables separated by a space:
mysql -Bs information_schema -e 'SELECT table_schema, table_name FROM tables WHERE ENGINE="InnoDB";'
List all InnoDB tables separated by a dot:
mysql -Bs information_schema -e 'SELECT CONCAT(table_schema,".",table_name) FROM tables WHERE ENGINE="InnoDB";'
MyISAM table size & InnoDB table size
What is the total size of all files of different extensions in the MySQL datadir?
MYD+MYI = total MyISAM, ibdata1+ibd = total InnoDB. MYI are the MyISAM indexes, MYD is the MyISAM data.
find /var/lib/mysql/ -type f -printf "%s %f\n"|awk -F'[ ,.]' '{print $1, $NF}'|sort -k2|awk '{array[$2]+=$1} END {for (i in array) {print array[i]"\t"i}}'
What is the total size of all files that MySQL currently has open?
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;
Show users that have grants on a specific database
mysql -e "select db,host,user from mysql.db;" | grep $DATABASE
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');
Mass Drop Users
1) Prepare a file called drop_user.sql formatted like this:
drop user user1@38.142.129.%; drop user user2@38.142.129.%; drop user user3@38.140.142.%;
2) Reformat it with sed to add in the single quotes at the appropriate places like this:
cat drop_user.sql | sed -e "s|;|';|g" -e "s|r |r '|g" -e "s|@|'@'|g"
3) Put it in a while loop to whip through the list:
cat drop_user.sql | sed -e "s|;|';|g" -e "s|r |r '|g" -e "s|@|'@'|g" | while read line; do mysql -e "$line"; done
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
Dumping the skeletons only
Sometimes people want only the "skeletons" and not the data dumped and imported. For those times:
mysqldump --no-data database_name > sandbox_database_name-struc_only.sql
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
Or, at the CLI:
mysql -e "select user, host from mysql.user;"
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
Remote MySQL
It's best to have a private network connection between both servers for security. Failing that, you'll want an SSH tunnel.
Remote user is basically treated like a separate user from the localhost user. As in the grants and password need to be separately set for 'database_user'@'localhost' and 'database_user'@'123.45.678.910'. Domain names work as well as IPs, they are also considered separate. So:
- Set grant for remote user.
- Set password for remote user.
- Try connecting as remote user. For instance:
mysql --host=remotehostIPordomainname --user=myname --password=mypass mydb
Can't connect? Try telnetting from the web server to the MySQL server.
telnet <mysqlserverip> 3306
If that fails, make sure port 3306 is open for outgoing as well as incoming traffic on the web server, and that it is open for incoming traffic on the webserver.
cPanel
On a cPanel server, make sure these are in place:
On the web server:
WHM >> SQL Services >> Additional MySQL Access Hosts
List the IPs of all external MySQL servers you wish to use.
WHM >> SQL Services >> Manage MySQL Profiles
- Click Add Profile
- Manually enter an existing MySQL superuser’s credentials. You need the MySQL server's IP, MySQL port, and MySQL root password from its /root/.my.cnf (only use a secure private network to do it this way).
- Validate the profile and switch to it.
On the MySQL server:
WHM >> SQL Services >> Additional MySQL Access Hosts
List the IPs of all web servers using this server as a MySQL server.
cPanel >> Databases >> Remote MySQL
List the IPs of the web servers that need access to databases under this cPanel account.
Encrypting Database Tables At Rest
Sometimes, a customer will want to encrypt certain database tables, or all database tables of a given type, at rest. Meaning that the raw files in /var/lib/mysql will appear to be gibberish to anyone who will try to grep or cat them. Most users won't have access to this directory or those raw files, and if an intruder does, it will likely need to be treated as though the server has been rooted anyway. But sometimes the customer will want this too.
MySQL/MariaDB loads a key via plugin to do the encryption and decryption. The plugin needs to be loaded, and the variables for the plugin need to be set to encrypt and decrypt the data. The key of course needs to be stored in a secure directory with secure permissions. MySQL and MariaDB each come with a standard plugin preinstalled, but these plugins are not loaded or configured by default.
General limitations and documentation may be found here:
https://mariadb.com/kb/en/data-at-rest-encryption-overview/
https://dev.mysql.com/doc/refman/5.7/en/innodb-data-encryption.html
https://dev.mysql.com/doc/refman/8.0/en/innodb-data-encryption.html
What Does & Doesn't Get Encrypted
The raw tables within the datadir may be encrypted. Dump files will not be encrypted. If you import a dump file into a database that has encryption, the imported tables will be encrypted.
If the table type supports encryption, it may be encrypted. Encryption of all tables of a given type may be set to be done automatically in /etc/my.cnf, or not. If tables are not set to be automatically encrypted, individual tables may be set to be encrypted, though the customer will need to do this specifically and this may be done when the tables are created. Contrariwise, it is also possible to encrypt tables by default but exclude specific ones from being encrypted, though this exclusion needs to be explicitly set when the tables are created. All of this is going to depend on your encryption plugin settings in /etc/my.cnf.
In MySQL, temp tables are not encrypted. MySQL 5.7 will not encrypt any logs, but MySQL 8.0 allows InnoDB undo and redo logs to be encrypted, along with binlog and relay logs (but the latter two's indexes are not encrypted).
In MariaDB, the binlog, the InnoDB redo log, and certain temp tables may be encrypted.
In neither case do the slow query log or general log or the error log get encrypted.
Prerequisites For Encryption
Storage Engines
Not every storage engine supports encryption. The more common ones that we run into do or don't as follows:
MyISAM - No encryption supported.
Aria - Encryption supported for tablespaces only. Fewer options for encrypting & decrypting than InnoDB at this time.
InnoDB - Encryption supported. In MySQL 5.7, this is for file-per-table tablespaces only.
Plugins
MySQL and MariaDB need to have plugins that support encryption loaded. Each has several to choose from, in case there is a need too specialized for the standard encryption plugins. The standard plugins are included and should already be installed on the server, they just need to be activated. These plugins are:
file_key_management - Standard with MariaDB.
keyring_file - Standard with MySQL, the only one MySQL provides in Community Edition.
You can check to see if either plugin is loaded with:
mysql -e "show plugins;" | grep -i key
Setup
MariaDB - file_key_management
Create the key You'll be keeping the keys in a directory that needs to be secured.
mkdir /etc/mysql.encryption/ cd /etc/mysql.encryption/
Then you'll create the keyfile:
echo -n "1;"$(openssl rand -hex 32) > /etc/mysql.encryption/keyfile
That 1; is important. This is the key ID that will be set with the innodb_default_encryption_key_id directive in /etc/my.cnf for when encrypting tables automatically, and also it is used by ENCRYPTION_KEY_ID if encrypting them one at a time. MariaDB's documentation says it is possible to have multiple keys defined here, but key rotation is not supported by file_key_management at this time, and there must be a key for 1; even if you have others. If you pick a random second number in that range (say 75;) you will need to append it to /etc/mysql.encryption/keyfile on a new line, and if you want to use it by default you will set innodb_default_encryption_key_id = 75 in /etc/my.cnf. The keyfile in such a case would look something like this:
1;4503a16ba36352b2ece8058ab8ec7b38cf91c16b248fc068e2f2ed7d9e5e571c 75;afc5c2adefc7b71276a0b0ce72eaf71a490196ef8611c372678b957e64661863
Optional: Encrypt the keyfile
The keyfile itself may be encrypted, and if it is encrypted and used, the unencrypted version can be subsequently deleted. Heed the above warning should you delete it.
openssl rand -hex 128 > /etc/mysql.encryption/keyfile.key openssl enc -aes-256-cbc -md sha1 -pass file:/etc/mysql.encryption/keyfile.key -in /etc/mysql.encryption/keyfile -out /etc/mysql.encryption/keyfile.enc
Delete the unencrypted keyfile if the customer is ready for this, and if you dare:
rm /etc/mysql.encryption/keyfile
Lock down the directory and files within it (Always do this, especially if you're not encrypting the keyfile):
chown -Rv mysql:root /etc/mysql.encryption/ chmod -v 400 /etc/mysql.encryption/* chmod -v 500 /etc/mysql.encryption
Set /etc/my.cnf to use the plugin and key, and to configure options. At minimum, you'll add:
plugin_load_add = file_key_management file_key_management_filename = /etc/mysql.encryption/keyfile.enc file_key_management_filekey = FILE:/etc/mysql.encryption/keyfile.key file_key_management_encryption_algorithm = aes_ctr
Restart MariaDB, and see if it loads:
systemctl restart mysql mysql -e "show plugins;" | grep -i key
Further options can be set here, depending on what the customer needs.
Tell it which key to use by default (again, this is an integer which matches a line you made in the keyfile, NOT a binary option):
innodb_default_encryption_key_id = 1
Do not automatically encrypt newly created InnoDB tables, but allow encrypted ones to be created manually (this is currently the default option):
innodb_encrypt_tables = OFF
Automatically encrypt newly created InnoDB tables, but allow unencrypted ones to be created manually:
innodb_encrypt_tables = ON
Automatically encrypt newly created InnoDB tables, and do not allow unencrypted ones to be created at all:
innodb_encrypt_tables = FORCE
Automatically encrypt the InnoDB redo log:
innodb_encrypt_log = ON
Further options for InnoDB can be reviewed here: https://mariadb.com/kb/en/innodb-system-variables/
https://mariadb.com/kb/en/innodb-enabling-encryption/
Aria encryption doesn't have as many features as InnoDB encryption does at the moment. For details, see:
https://mariadb.com/kb/en/aria-encryption-overview/
Restart MariaDB when the options are set.
MySQL - keyring_file
Add to /etc/my.cnf:
early-plugin-load=keyring_file.so keyring_file_data=/var/lib/mysql-keyring/keyring
Make sure that the mysql-keyring directory exists, should have 750 permissions and mysql:mysql ownerships
Restart MySQL, it should generate the empty keyring file automatically
Encrypt a table, it should then populate the file automatically
To verify that the plugin is loaded, use:
mysql -e "SELECT plugin_name, plugin_status FROM INFORMATION_SCHEMA.PLUGINS WHERE plugin_name LIKE 'keyring%';"
To list encrypted tables, use:
mysql -e "SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE CREATE_OPTIONS LIKE '%ENCRYPTION%';"
Which Tables Are Currently Encrypted?
InnoDB
In MySQL 5.7 or 8.0, you can use:
mysql -e "SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE CREATE_OPTIONS LIKE '%ENCRYPTION%';"
MySQL 8.0 also allows you to check with:
mysql -e "SELECT SPACE, NAME, SPACE_TYPE, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE ENCRYPTION='Y';"
In MariaDB, use this to check InnoDB tables:
mysql -e "SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID from INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION;"
Aria
For Aria tables, there isn't anything similar for now. You need to query the table for some data, then search the raw file for it to see if it comes up in plain text or not. So, for example:
MariaDB [db1]> SELECT * FROM db1.aria_tab LIMIT 1; +----+------+ | id | str | +----+------+ | 1 | str1 | +----+------+ 1 row in set (0.00 sec
Then, we could search the data file that belongs to db1.aria_tab for str1 using a command-line tool, such as strings:
strings /var/lib/mysql/db1/aria_tab.MAD | grep "str1" str1
If it were encrypted, str1 would have been hashed instead.
Manually encrypt an InnoDB table
Substitute the database name for $database, the table for $tablename, and which key from the keyfile that you're using. 1 is the default. Aria tables can't be encrypted this way.
mysql $database -e "ALTER TABLE $tablename ENCRYPTED=YES ENCRYPTION_KEY_ID=1;"
Restoring Encrypted InnoDB Tables From Raw Files
This is similar to our procedure HERE except we need to add a few more flags to the second MySQL/MariaDB instance to load the plugin and the keys. For MariaDB with the file_key_encryption plugin for instance, you would use something like this after you have the second datadir set:
mysqld \ --datadir=$dir \ --socket=$dir/socket.mysql \ --pid-file=$dir/mysql.pid \ --log-error=$dir/mysql.err \ --skip-grant-tables \ --skip-networking \ --plugin-load-add=file_key_management \ --file-key-management-filename=/etc/mysql.encryption/keyfile.enc \ --file-key-management-filekey=FILE:/etc/mysql.encryption/keyfile.key \ --file-key-management-encryption-algorithm=aes_ctr \ --user=mysql &
Then, proceed as normal to dump the database to a .sql file, and import it. Since the customer wants their data encrypted at rest, you should ask them to confirm the deletion of that .sql file in addition to confirming that the database was restored with the proper data.
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.