Live Monitoring of MySQLThere are two useful tools:
mytop -u <user> -p<password> innotop -u <user> -p<password>Alternatively you can provide a .mytop file to provide the credentials automatically.
Show MySQL StatusYou can get a very simple status by just entering "\s" in the "mysql" command line client prompt:
mysql> \sYou can show the replication status using
SHOW SLAVE STATUS \G SHOW MASTER STATUS \GNote 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;
List Databases/Tables/ColumsYou 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 DBAnd you can also do it using queries:
SHOW DATABASES; USE <database>; SHOW TABLES; DESCRIBE <table>;
Check and Change Live Configuration ParametersNote 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 OptimizationYou can check MySQL parameters of a running instance using tools like MySQL config parameter explanation.
Remote MySQL Dump and ImportThe 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 initializationHappens 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 tableThis 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;
Dump Skip Event TableIf your MySQL backup tool or self-written script complains about an event table than you have run into an issue caused by newer MySQL versions (>5.5.30) that introduced a new table "events" in the internal schema. If you run into this you need to decide wether you want to include or exclude the new events table when dumping your database. To skip: Due to a MySQL bug #68376 you have two choices. You can check documentation and add the logical option
--skip-eventswhich will cause the event table not to be exported. But the warning won't go away. To also get rid of the warning you need to use this instead:
--events --ignore-table=mysql.eventsAnd of course you can also choose just to dump the events table: Add the option
--eventsto your "mysqldump" invocation. If you use a tool that invokes "mysqldump" indirectly check if the tool allows to inject additional parameters.
Forgotten 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 HandlingUsing "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
pager lessPage output into a script
pager /home/joe/myscript.shOr if you have Percona installed get a tree-like "EXPLAIN" output with
pager mk-visual-explainand 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 fileThere is an extra tool to inspect bin logs:
mysqlbinlog <binary log file>
Skip one statement on replication issue HA_ERR_FOUND_DUPP_KEYIf 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 FormatWhen 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
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 ReplicationWhen 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 TimeThere 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 0to 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...