Maintenance (MySQL)

SHOW etc

Shows general information about a database, it's tables, columns etc.

SHOW STATUS;                                           # Shows general info
SHOW VARIABLES LIKE "%version%";                       # Show running MySQL version
SHOW ENGINE INNODB STATUS \G                           # Show InnoDB database status
SHOW DATABASES;                                        # Show databases
SHOW TABLES;                                           # Show tables in the current database
DESCRIBE table;                                        # Show column information for a table
SHOW INDEX FROM table;                                 # Show tables index/key information
SHOW CREATE TABLE table;                               # Shows syntax that would be used to recreate a table

Database Sizes

Databases

SELECT table_schema "Database", Round(Sum(data_length + index_length) / 1024 / 1024, 1) "Size (MB)" FROM information_schema.tables GROUP BY table_schema;

Top Tables Be sure to update the database name in the FROM line

SELECT table_name,
       ROUND(table_rows / 1000, 2) AS 'rows (K)',
       ROUND(data_length / ( 1024 * 1024), 2) AS 'data (MB)',
       ROUND(index_length / ( 1024 * 1024), 2) AS 'index (MB)',
       ROUND(( data_length + index_length ) / ( 1024 * 1024), 2) AS 'total (MB)'
FROM   information_schema.TABLES WHERE table_schema = 'databasename'
ORDER  BY data_length + index_length DESC
LIMIT  15;

Adapted from http://www.mysqlperformanceblog.com/2008/02/04/finding-out-largest-tables-on-mysql-server/

Export / Backup

mysqldump -u root -p dbname > dbname.sql              # Basic dump
mysqldump -u root -p dbname | dbname.sql.gz           # Create a compressed dump

# Backup one table only
mysqldump -u root -p dbname tablename > db_table.sql 

# Exclude certain tables
mysqldump -u root -p dbname --ignore-table=dbname.table1 --ignore-table=dbname.table2 > dbname.sql 

# Export a table as CSV
SELECT * INTO OUTFILE '/tmp/table.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '' ESCAPED BY '\\' 
LINES TERMINATED BY '\n' FROM table;

Create a Copy of a Table

1. Export the table
   • EG mysqldump -u root -p database table > /var/tmp/db_table.sql
2. Edit the dump to provide new table name
   • EG vi /var/tmp/db_table.sql
   • EG :%s/table/table_new/g
3. Import the new table in
   • EG mysql -u root -p < /var/tmp/db_table.sql

Corruption

CHECK TABLE table;                                     # Checks for table corruption
REPAIR TABLE table;                                    # Repairs a corrupted table

Shrink MyISAM Table

OPTIMIZE TABLE SystemEvents;

Skip Replication Errors

Sometimes replication to a Slave will stop due to an error. To skip the error and restart replication, perform the following on the slave...

STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;

Restart Replication

If replication is completely broken, or the slave no longer has an accurate copy of the master.

1. Create dump from master
   • mysqldump -u root -p --master-data --all-databases --flush-privileges | gzip -1 > /var/tmp/replication.sql.gz
2. Copy to slave
   • EG scp /var/tmp/replication.sql.gz root@10.1.1.55:/var/tmp/.
3. Stop slave if still running
   • In MySQL - STOP SLAVE;
4. Import into slave
   • zcat /var/tmp/replication.sql.gz | mysql -u root -p
5. Start slave
   • In MySQL - START SLAVE;

Remove Old Binary Logs

Make sure that any Slave's are up to date before deleting old binary logs

SHOW MASTER STATUS\g 
SHOW MASTER LOGS;                                      # Get list of logfiles
PURGE MASTER LOGS TO 'mysql-bin.012344';               # Removes all logs up to the one specified

Migrate Databases

There are a number of GUI tools provided to assist in migrating data from one server to another, and these are well worth a look (eg MySQL Migration Toolkit, MySQL Workbench). However I have found that on occasion I can't get these to work (normally due to apparent permissioning issues, despite having full rights), and revert to the old fashioned manual way.

This procedure works for Windows and Unix systems alike, the mysql and mysqldump commands need to be run from the normal Unix shell or Windows command line prompts, not from within the mysql software, though on Windows you'll probably need to change directory (cd) to where MySQL is installed (something like C:\Program Files\MySQL).

1. Export the database from the source machine (from either Unix shell / Windows command prompt as your sourec machine dictates), eg for database called sourcedb
   • mysqldump -u root -p sourcedb > sourcedb.sql
2. Copy the file to your destination database server
3. Create an empty database on the destination database server
   • CREATE DATABASE destdb;
4. Import the database dump
   • mysql -u root -p destdb < sourcedb.sql
5. Give you client server access to the new database
   • GRANT ALL PRIVILEGES on destdb.* to 'destdbuser'@'clientsvr' IDENTIFIED BY 'password';

Ignore Foreign Key Checks

If you receive an error similar to the following when trying to reimport a dump, its likely that you are falling fowl of foreign key checks which will fail as the table with the foreign key doesn't appear until later in the dump file

• ERROR 1005 (HY000) at line 14475: Can't create table 'db.table' (errno: 150)

Before starting the dump, disable foreign key checks

• mysql -u root -p -e "SET GLOBAL foreign_key_checks=0"

Then re-enable once all finished

• mysql -u root -p -e "SET GLOBAL foreign_key_checks=1"

Create Blank Copy

To create a blank copy of a database (ie with a schema but no data) follow the procedure above but use the -d option when creating the dump, so

• mysqldump -u root -p -d sourcedb > sourcedb-schema.sql