Mysql – How to increase fast backup and restore of 500GB database using thesqldump

With mysqldump you can only safely use --single-transaction if all your tables are InnoDB, otherwise your backup is inconsistent.

If you have the requirement for a hybrid backup, then you need the lock-tables on all tables in the backup (default), which will be safe for all engines. It's also worth mentioning that the default options will make sure your backup is safe, you don't need to turn any special flag on.

Note: If you do have a hybrid mix, perhaps look at xtrabackup. It will only be locking during the MyISAM phase of the backup.

If you are dumping a mysql database that has a mixture of InnoDB and MyISAM and you have scheduled downtime:

mysqldump -u... -p... --master-data=1 --single-transaction --flush-privileges --routines --triggers --all-databases > /root/mydata.sql

Since --opt is enabled by default, the following options are already enabled

• --add-drop-table
• --add-locks
• --create-options
• --quick
• --lock-tables
• --set-charset
• --disable-keys

If you are concerned about InnoDB/MyISAM together in the mysqldump and you want the database placed in a read-only state, try putting a read lock across all tables manually.

MYSQL_CONN="-hhostip -uusername -ppassword"
mysql ${MYSQL_CONN} -A -e"FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400)" &
SEARCHING_FOR_SLEEP=0
while [ ${SEARCHING_FOR_SLEEP} -eq 0 ]
do
    sleep 3
    SEARCHING_FOR_SLEEP=`${MYSQL} ${MYSQL_CONN} -A -e"SHOW PROCESSLIST;" | grep -c "SELECT SLEEP(86400)"`
done
sleep 1
SLEEP_ID=`mysql ${MYSQL_CONN} -A -e"SHOW PROCESSLIST;" | grep "SELECT SLEEP(86400)" | awk '{print $1}'`

mysqldump ${MYSQL_CONN} --master-data=2 --single-transaction --flush-privileges --routines --triggers --all-databases > /root/mydata.sql

mysql ${MYSQL_CONN} -A -e"KILL ${SLEEP_ID}"

The reason this is better to do is that --single-transaction does not protect MyISAM from changing during the dump.

Before launching the mysqldump, make sure binary logging is enabled on the master. If it is not, do the following:

Step 01) Add this to /etc/my.cnf on the master

[mysqld]
log-bin=mysql-bin

Step 02) service mysql restart

This will enable binary logging on the master.

On the new slave, you can run the following command:

CHANGE MASTER TO
MASTER_HOST='IP of the master',
MASTER_PORT=3306,
MASTER_USER='whatever_username',
MASTER_PASSWORD='whatever_password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=107;

Make sure the rep_username exists in the master. If it does not, run this command on the master:

GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT
ON *.* TO 'whatever_username'@'%' IDENTIFIED BY 'whatever_password';

Then do the mysqldump to /root/mydata.sql.

Move the /root/mydata.sql from the master to the slave.

Next, execute in the mysql client this command on the slave:

source /root/mydata.sql

This will load mysqldump in the slave and the set the correct log file and log position in /var/lib/mysql/master.info.

Finally, run START SLAVE; on the slave and mysql replication should get going.

Run SHOW SLAVE STATUS\G to check replication status.

Give it a Try !!!