MySQL

Note: never use kill to kill MySQL. Use service, or systemctl instead.

Backup (Dump)

To backup (dump) a single database:
mysqldump database_name > database_name.sql

Backup all databases:
mysqldump --all-databases > all_databases.sql

Restore

Restore a database:
mysql database_name < database_name.sql

Restore one database from a dump of all the databases: mysql --one-database database_name < all_databases.sql

Version

See: Software Versions to check the installed version.

Performance Tuning

1. First, Check innodb and myisam settings

Run the below to get the size of data in INNODB and MYISAM tables:

mysql -e 'SELECT SQL_NO_CACHE Engine, count(*) "# of Tables", concat(round(sum(table_rows)/1000,2),"K") "Rows", concat(round(sum(data_length)/(1024*1024),2),"M") "Size of Data", concat(round(sum(index_length)/(1024*1024),2),"M") "Size of Indexes", concat(round(sum(data_length+index_length)/(1024*1024),2),"M") "Total Size", concat(round(sum(index_length)/sum(data_length)*(100),2), "%") "% Data in Indexes" FROM information_schema.TABLES GROUP BY engine ORDER BY sum(data_length+index_length) DESC LIMIT 10;'

mysql -Bse 'show variables like "datadir";'|awk '{print $2}'|xargs -I{} find {} -type f -printf "%s %f\n"|awk -F'[ ,.]' '{print $1, $NF}'|awk '{array[$2]+=$1} END {for (i in array) {printf("%-15s %s\n", sprintf("%.3f MB", array[i]/1048576), i)}}' | egrep '(MYI|ibd)'

(You can use the below to get the size of each database.)
mysql -e 'select table_schema, sum((data_length+index_length)/1024/1024) AS MB from information_schema.tables group by 1;'

In /etc/my.cnf, change:

innodb_buffer_pool_size - set this to the value from the Total column, innodb row.

Note: if server has lots of RAM, and lots of data, set the innodb_buffer_pool_size = 1G, then set innodb_buffer_pool_instances = 4 (or smaller, or larger, depending on RAM) Also note that this will set the buffer pool size as 1G, and break that 1G into 4 chunks (It will NOT create 4, 1GB pools.) Also note that ideally each pool chunk should be 1Gb in size.

key_buffer_size - set this to the value from the Index column, myisam row.

It's a good idea to backup the current settings, just in case:
cp -a /etc/my.cnf /root/my.cnf.

Are these settings effective?

mysql -e "show global status like '%innodb_buffer_pool_read%%';" | awk '/reads/ {print $1,"=",$2}';mysql -e "show global status like '%key_read%%';" | awk '/reads/ {print $1,"=",$2}'

The results should be close to the values for innodb_buffer_pool_size and key_buffer_size

2. Opens

Warning: Don't mess with this unless the server is running out of memory. Even then, bigger is better than too small.

a.) Get the total number of tables on the server:
find /var/lib/mysql -name '*.frm' | wc -l - Example output: 4568

b.) Set table_open_cache variable in /etc/my.cnf to 50% of that number
Example, based on the above number: table_open_cache = 1371

c.) Set open_files_limit to three times the corrected value of table_open_cache
Example: open_files_limit = 3951

You can check to see if this value is good by running mysqladmin stat. That command will show the number of Opens, and the number of Open tables. Ideally, these numbers should be identical.

3. CPU Usage Issues

thread_cache_size - MySQL setting to adjust

SHOW GLOBAL STATUS LIKE 'Threads_created';

SHOW GLOBAL STATUS LIKE 'Connetions';

View the MySQL Error log

To check the MySQL error log, run:
mysql -e "show variables like '%log_error%';"

This will show the location of the MySQL error log, which you can then grep, tail, etc. (You're looking for stuff like innodb errors, disk space issues, connection messages, etc)

View MySQL Variables

You can view the variables that MySQL is using with:

mysql -e "show variables like 'max_connections%';" - This shows the max_connections variable. You can substitute any MySQL variable here.

This is helpful for database connection errors, or if a change to /etc/my.cnf isn't 'sticking' (In that case, look for other my.cnf files)

View Currently Running Queries

mysqladmin process stat - This will show the queries that are running, how many threads, Open tables, slow queries, and a bunch of other helpful stuff. (Note that you can "tail -f" by running: watch mysqladmin process stat )

Dump and Import

mysql database_name < file.sql - Import a file to the named database from a .sql file

mysqldump dbname > backupfile.sql - dump one database to a .sql file