MySQL Master/Slave Hot Backup

1. Perform all replication operations in the foreground where the master node is not offline

Basic Steps for recall:
1. premise: the master opens the binary log file and sets server-id = 1.
2. account required to establish a slave service connection on the master. Permission: Replication slave
3. Multiple methods to back up the master data, which are all restored from the server. Before backup, record the name and POS of the binary log file being used on the master server
Method:
Use the MEB Enterprise Backup Tool to automatically record binary information
This backup method requires the installation of a software MEB, which is installed in the/OPT directory by default. The installation process is omitted.

Add the following parameters to my. CNF before backup (show variables like 'innodb % ')
Innodb_data_home_dir =/data/MySQL # specify the datadir path
Innodb_data_file_path = ibdata1: 10 m: autoextend
Innodb_log_group_home_dir =/data/MySQL # specify the Log Path, which is generally the same as the datadir path.
Innodb_log_files_in_group = 2
Innodb_log_file_size = 5242880
Log-bin = MySQL

Method 2:
Lock all tables and record the information of binary log files. Use System commands to back up data to the slave server.

Method 3:
Mysqldump is used for backup. With relevant parameters, tools can be used to record binary log file information while hot backup
-- Master-data -- lock-all-tables -- Single-transaction

Create an account

Mysql> grant replication slave on *. * to slave @ '%' identified by '123 ';
Mysql> flush privileges;

Ii. Use MEB to back up Master Data
# Mkdir/backup
# Cd/opt/MySQL/meb-3.5/
#. /Bin/innobackup -- user = root -- Password = 123 -- ibbackup =/opt/MySQL/meb-3.5/bin/ibbackup -- socket =/data/mysqld. sock/etc/My. CNF/backup -- slave-Info
.....
Ibbackup: A copied database page was modified at 44233.
Ibbackup: Scanned log up to LSN 46136 .《---
Ibbackup: was able to parse the log up to LSN 46136.

Innobackup: Backup created in directory '/backup/2011-05-19_15-21-21'
Innobackup: MySQL BINLOG position: Filename 'mysqld-binlog.000003', position 1662 《----
110519 15:21:55 innobackup: innobackup completed OK!

Iii. Set up slave server slave1
1. Install the MySQL server, set the parameters, and run them.
[Mysqld]
Datadir =/Data
User = MySQL
Socket =/data/mysqld. Sock
Innodb_data_home_dir =/Data
Innodb_data_file_path = ibdata1: 10 m: autoextend
Innodb_log_group_home_dir =/Data
Innodb_log_files_in_group = 2
Innodb_log_file_size = 5242880
Innodb_flush_log_at_trx_commit = 1
Log-bin =/data/mysqld-BINLOG
Sync_binlog = 1
Server-id = 2 ##### it must be larger than its own master

Log-slave-Updates #####

[Mysqld_safe]
PID-file =/data/mysqld. PID
Log-error =/data/mysqld. Err
2. Obtain the backup data of the master and restore it.
1) recover data on the master node (MEB is directly restored on the backup without affecting any running data)
Master #. /bin/innobackup -- apply-log -- user = root -- Password = 123 -- ibbackup =/opt/MySQL/meb-3.5/bin/ibbackup -- socket =/data/mysqld. sock/etc/My. CNF/backup/2011-05-19_15-21-21/
2) copy the recovered data to the data directory of the slave server
Note: It is strongly recommended that the data directory path be consistent.
Master # tar CVF master-data.tar./2011-05-19_15-21-21/
Master # SCP master-data.tar 10.1.1.19 :~ /

Slave1 # tar xvf master-data.tar
Slave1 # mv 2011-05-19_15-21-21/*/Data
Slave1 # chown-r MYSQL: MySQL/Data

3. Start and set information about the master to connect to the master.
# Service MySQL start

Mysql> change master
-> Master_host = '10. 1.1.18 ',
-> Master_user = 'slave ',
-> Master_password = '123 ',
-> Master_log_file = 'mysqld-binlog.000003 ',
-> Master_log_pos = 1662;
4. Enable related slave threads
Mysql> Start slave;
Mysql> show slave status \ G;
Slave_io_running: Yes
Slave_ SQL _running: Yes