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
- Export the table
- EG
mysqldump -u root -p database table > /var/tmp/db_table.sql
- EG
- Edit the dump to provide new table name
- EG
vi /var/tmp/db_table.sql
- EG
:%s/table/table_new/g
- EG
- Import the new table in
- EG
mysql -u root -p < /var/tmp/db_table.sql
- EG
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.
- Create dump from master
mysqldump -u root -p --master-data --all-databases --flush-privileges | gzip -1 > /var/tmp/replication.sql.gz
- Copy to slave
- EG
scp /var/tmp/replication.sql.gz root@10.1.1.55:/var/tmp/.
- EG
- Stop slave if still running
- In MySQL -
STOP SLAVE;
- In MySQL -
- Import into slave
zcat /var/tmp/replication.sql.gz | mysql -u root -p
- Start slave
- In MySQL -
START SLAVE;
- In MySQL -
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
).
- 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
- Copy the file to your destination database server
- Create an empty database on the destination database server
CREATE DATABASE destdb;
- Import the database dump
mysql -u root -p destdb < sourcedb.sql
- 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