MySQL

Purge Binary Logs

Purging binary logs can free up a lot of space in a crunch. These logs are primarily used for replication, and don't necessarily need to be kept for very long.

Purge Binary Log by Date

PURGE BINARY LOGS BEFORE '2003-04-02 22:46:26';

Purge Binary Logs by File Name

PURGE BINARY LOGS TO 'mysql-bin.000022';

Replication

Skip Replication Error

SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;

Set Master Server

CHANGE MASTER TO
    MASTER_HOST = 'host_name',
    MASTER_USER = 'user_name',
    MASTER_PASSWORD = 'password',
    MASTER_PORT = port_num,
    MASTER_LOG_FILE = 'master_log_name',
    MASTER_LOG_POS = master_log_pos

MySQL Dump for Replication

mysqldump --opt --master-data=1 --all-databases | gzip > mysqldump.sql.gz

Fix One-Way Replication

Where db1 is the master, and db2 is the (broken) slave

on db2:

mysql -e "slave stop;"

on db1:

mysqldump --opt --master-data=1 --all-databases | gzip > mysqldump.sql.gz

on db2:

zcat mysqldump.sql.gz | mysql -f
mysql -e "slave start;"

Fix Circular Replication

Where db1 is the "good" copy, and db2 is broken.

on both:

mysql -e "slave stop;"

on db1:

mysql -e "reset master;"
mysqldump --opt --master-data=1 --all-databases | gzip > mysqldump.sql.gz

on db2: (If db2 is actively accepting client connections, bind MySQL to localhost until after this step)

zcat mysqldump.sql.gz | mysql -f
mysql -e "reset master;"

on db1 (MASTER_LOG_POS=107 works on MySQL 5.5, check "show master status" to verify):

mysql -e "CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;"
mysql -e "SLAVE START;"

on db2:

mysql -e "SLAVE START;"

Verify everything is working

mysql -e "show slave status\G" | grep -e Slave_.\*_Running

Check and Repair All Tables in All Databases

mysqlcheck -u root -p --auto-repair --check --optimize --all-databases

Safely Stop MySQL Slave

Notes from http://dev.mysql.com/doc/refman/5.0/en/replication-features-temptables.html

STOP SLAVE SQL_THREAD;
SHOW STATUS LIKE 'slave_open_temp_tables';

Check that the Slave_open_temp_tables is 0. If not, restart the slave SQL thread with START SLAVE SQL_THREAD and repeat until it is 0. Then it is safe to stop the slave mysql process.

Export Usernames, Passwords, and Privileges

mysql -B -N $@ -e "SELECT DISTINCT CONCAT('SHOW GRANTS FOR ''', user, '''@''', host, ''';') AS query FROM mysql.user" | mysql $@ | sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/## \1 ##/;/##/{x;p;x;}'

Convert All MyISAM Tables to InnoDB

mysql -B -N -e "select concat('alter table ', TABLE_SCHEMA, '.', table_name, ' ENGINE=InnoDB;') FROM INFORMATION_SCHEMA.tables where table_type = 'BASE TABLE' AND TABLE_SCHEMA != 'mysql' AND engine = 'MyISAM'"

Convert All Tables in a Specific Database to InnoDB

mysql -B -N -e "select concat('alter table ', TABLE_SCHEMA, '.', table_name, ' ENGINE=InnoDB;') FROM INFORMATION_SCHEMA.tables where table_type='BASE TABLE' AND engine = 'MyISAM' AND TABLE_SCHEMA = 'some_db_name'"

Change User Password

SET PASSWORD FOR 'bob'@'%.loc.gov' = PASSWORD('newpass');

Show InnoDB Space Information

This only works if you are using innodb_file_per_table, but will show the data size, index size, and amount of free space for every InnoDB table that has more than 100MB of unused space.

SELECT table_schema, table_name, data_length/1024/1024 AS data_length_MB, index_length/1024/1024 AS index_length_MB, data_free/1024/1024 AS data_free_MB FROM information_schema.tables WHERE engine LIKE 'InnoDB' AND data_free > 100*1024*1024;

To reclaim this space, use "alter table". Beware, large tables will take a long time, and be sure you have enough disk space to hold double the table size so the operation can complete

ALTER TABLE tablename ENGINE=InnoDB;

MySQL Tuner

There is a very useful mysql tuner script available at http://mysqltuner.pl. You can run this with a one-liner like so, if you do not want to download it

wget -O - mysqltuner.pl | perl