MySQL non-stop short time lock table backup master standby sync new backup machine

Refresh Data
[[email protected] ~]# mysql-e ' flush tables with read lock; ' Lock table Refresh table data
[[email protected] ~]# mysql-e ' flush logs; ' Refresh log (redo log and binlog log)
[[email protected] ~]# mysql-e ' show master status; ' >/root/back.$ (date +%f+%t) Save Binlog log file name and position location

Create a snapshot of the LV
[Email protected] ~]# lvcreate-l 1g-n/dev/mydata/mysqlback-p r-s/dev/mydata/mydatalv
Release lock
[Email protected] ~]# mysql-e ' unlock tables; '

Backing Up snapshot files
[Email protected] ~]# Mkdir/myback
[Email protected] ~]# mount-r/dev/mydata/mysqlback/mnt
[Email protected] ~]# cp-a/mnt/mysql/myback

Add a standby machine
Copy the backup files to the MySQL data directory of the standby machine
[Email protected] ~]# cp-a/myback/*/data/
Start MySQL
[Email protected] ~]# service mysqld restart
Start the slave synchronization on the standby, using the Binlog file name and position location saved on the host computer
[Email protected] ~]# mysql-e ' Change master to master_host= ' 192.168.56.87 ', master_user= ' test ', master_password= ' 123 ' , master_log_file= ' mysql-bin.000005 ', master_log_pos=1370; '
[[email protected] ~]# mysql-e ' start slave '

Recovering data on the host
Copy data to MySQL data Catalog
[Email protected] ~]# cp-a/myback/*/data/
Start MySQL
[Email protected] ~]# service mysqld restart
Recover subsequent writes using the Binlog file
[Email protected] ~]# Mysqlbinlog--start-position=245/var/lib/mysql/mysql-bin.000014 > Binlog.sql
[[email protected] ~]# MySQL </root/binlog.sql

Reference http://www.jb51.net/article/96609.htm

MySQL non-stop short time lock table backup master standby sync new backup machine