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

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:

1. Set grant for remote user.
2. Set password for remote user.
3. 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

1. Click Add Profile
2. 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).
3. 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.

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.

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.

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.