MySQL Administration Commands
Below you find a unordered list of solutions by tasks useful for a MySQL DBA:
Live Monitoring of MySQL
There are two useful tools:
with "mytop" being an own Debian package, while "innotop" is included in the "mysql-client" package. From both innotop has the more advanced functionality. Both need to be called with credentials to connect to the database:
mytop -u <user> -p<password> innotop -u <user> -p<password>
Alternatively you can provide a .mytop file to provide the credentials automatically.
Show MySQL Status
You can get a very simple status by just entering "\s" in the "mysql" command line client prompt:
You can show the replication status using
SHOW SLAVE STATUS \G SHOW MASTER STATUS \G
Note that the "\G" instead of ";" just makes the output more readable.
If you have configured slaves to report names you can list them on the master with:
SHOW SLAVE HOSTS;
Check InnoDB status
show /*!50000 ENGINE*/ INNODB STATUS;
You can either use the "mysqlshow" tool:
mysqlshow # List all databases mysqlshow <database> # List all tables of the given database mysqlshow <database> <table> # List all columns of the given table in the given DB
And you can also do it using queries:
SHOW DATABASES; USE <database>; SHOW TABLES; DESCRIBE <table>;
Check and Change Live Configuration Parameters
Note that you cannot change all existing parameters. Some like innodb_pool_buffer require a DB restart.
show variables; # List all configuration settings show variables like 'key_buffer_size'; # List a specific parameter set global key_buffer_size=100000000; # Set a specific parameter # Finally ensure to edit my.cnf to make the change persistent
MySQL Parameter Optimization
You can check MySQL parameters of a running instance using tools like
Also have a look at this MySQL config parameter explanation.
Remote MySQL Dump and Import
The following command allows dumping a database from one source host that doesn't see the target host when executed on a third host that can access both. If both hosts can see each other and one has SSH access to the other you can simply drop one of the ssh calls.
ssh <user@source host> "mysqldump --single-transaction -u root --password=<DB root pwd> <DB name>" | ssh <user@target host> "mysql -u root --password=<DB root pwd> <DB name>"
How to solve: Could not find target log during relay log initialization
Happens on corrupted/missing relay logs. To get the DB working
- Stop MySQL
- Remove /var/lib/mysql/relay-log-index.*
- Remove all relay log files
- Remove relog log file index
- Start MySQL
mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table
This is caused by timeouts when copying overly large database tables. The default network timeouts are very short per-default. So you can workaround this by increasing network timeouts
set global net_write_timeout = 100000; set global net_read_timeout = 100000;
Forgotten MySQL root Password
# 1. Stop MySQL and start without grant checks /usr/bin/mysqld_safe --skip-grant-tables & mysql --user=root mysql # 2. Change root password UPDATE user SET password=PASSWORD('xxxxx') WHERE user = 'root';
Import a CSV file into MySQL
LOAD DATA IN '<CSV filename>' INTO TABLE <table name> FIELDS TERMINATED BY ',' (<name of column #1>,<<name of column #2>,<...>);
MySQL Pager - Output Handling
Using "PAGER" or \P you can control output handling. Instead of having 10k lines scrolling by you can write everything to a file or use "less" to scroll through it for example.
To use less issue
Page output into a script
Or if you have Percona installed get a tree-like "EXPLAIN" output with
and then run the "EXPLAIN" query.
MySQL - Check Query Cache
# Check if enabled SHOW VARIABLES LIKE 'have_query_cache'; # Statistics SHOW STATUS LIKE 'Qcache%';
Check for currently running MySQL queries
show processlist; show full processlist;
Filter items in process list by setting grep as a pager. The following example will only print replication connections:
\P grep system show processlist;
To abort/terminate a statement determine it's id and kill it:
kill <id>; # Kill running queries by id from process listing
Show Recent Commands
SHOW BINLOG EVENTS; SHOW BINLOG EVENTS IN '<some bin file name>';
Inspect a MySQL binlog file
There is an extra tool to inspect bin logs:
mysqlbinlog <binary log file>
Skip one statement on replication issue HA_ERR_FOUND_DUPP_KEY
If replication stops with "HA_ERR_FOUND_DUPP_KEY" you can skip the current statement and continue with the next one by running:
STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; START SLAVE;
Changing Replication Format
When you want to change the replication format of a running setup you might want to follow this steps:
- Ensure you have a database backup
- Make master read-only by running
FLUSH TABLES WITH READ LOCK;
- Wait until all slaves do catch up
- Stop all slaves (shutdown MySQL)
- On master:
FLUSH LOGS; SET GLOBAL binlog_format='xxxxxx'; FLUSH LOGS; UNLOCK TABLES;
(ensure to replace 'xxxxxx' with for example 'ROW')
- Start all slaves
- Ensure to put the new binlog_format in all /etc/mysql/my.cnf
Note: the second "FLUSH LOGS;" ensures that the a new binary log is opened on the master with the new binlog_format. The stopping of the slaves ensures that they open a new relay log matching the new binlog_format.
Munin MySQL Plugin Setup on Debian
apt-get install libcache-cache-perl for i in `./mysql_ suggest` do do ln -sf /usr/share/munin/plugins/mysql_ $i; done /etc/init.d/munin-node reload
Fix Slow Replication
When replication is slow check the status of the replication connection. If it is too often in "invalidating query cache" status you need to decrease your query cache size. You might even consider disabling query cache for the moment if the DB load does allow it:
set global query_cache_size=0;
Debug DB Response Time
There is generic TCP response analysis tool developed by Percona called tcprstat. Download the binary from Percona, make it executable and run it like
tcprstat -p 3306 -t 1 -n 0
to get continuous statistics on the response time. This is helpful each time some developer claims the DB doesn't respond fast enough!
If you think about buying MySQL books...