Use Innobackupex to quickly build (repair) MySQL master-slave architecture

There are many ways to build MySQL master-slave. The traditional mysqldump method is one of the choices for many people. But for larger databases, this method is not an ideal choice. Use Xtrabackup to quickly and easily build or repair mysql master-slave architecture. This article describes using innobackupex to quickly build or repair a master-slave architecture. For reference.

1、基于主库做一个完整备份
# mkdir -p /log/bakforslave
# innobackupex --user=root -password=*** --socket=/tmp/mysql.sock \
--defaults-file=/etc/my.cnf /log/bakforslave --parallel=3 --safe-slave-backup --no-timestamp 

2、复制数据库到备机
# tar -czvf bakforslave.tar.gz ./bakforslave/
# scp bakforslave.tar.gz robin@172.16.10.51:~
# scp /etc/my.cnf robin@172.16.10.51:~/mymaster.cnf

3、在备机上恢复
###备机解压打包的备份文件
# mv /home/robin/bakforslave.tar.gz /data
# cd /data
# tar -xvf bakforslave.tar.gz

### prepare 备份
# innobackupex --user=root -password=*** --socket=/tmp/mysql.sock --defaults-file=/home/robin/mymaster.cnf \
 --apply-log --use-memory=4GB /data/bakforslave

###如果是修复从库，从库为启动状态应先停止从库，再做如下操作，否则可以跳过 
# service mysqld stop 

###还原备份的数据文件
# mv mysqldata mysqldatabk
# mv bakforslave mysqldata
# chown -R mysql:mysql mysqldata

###如果是新搭建的从库，此时可以修改主库的my.cnf为本机的my.cnf，
###如果为修复，则可以直接使用原有的配置文件或根据需要修改。
# cp /home/robin/mymaster.cnf /etc/my.cnf
# vi /etc/my.cnf         ###此处应修改使用一个不同的server_id，同时可以根据需要修改相关路径及端口配置等。
# service mysqld start   ###修改完毕后可以启动mysqld

4、主库授权用于复制的用户
mysql> grant replication slave,replication client on *.* to repl2@'172.16.10.%' identified by '***';

### 验证shell 提示符下登陆到主库
# mysql -urepl2 -p -h172.16.10.88

5、启动slave
# more /data/mysqldata/xtrabackup_binlog_info
mysql-bin.000136        73752825

mysql> CHANGE MASTER TO
MASTER_HOST='172.16.10.88',            --Author: Leshami
MASTER_USER='repl2',                   --Blog  : http://www.php.cn/
MASTER_PASSWORD='***',
MASTER_LOG_FILE='mysql-bin.000136',
MASTER_LOG_POS=73752825;

mysql> start slave;

6、验证结果
mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.10.88
                  Master_User: repl2
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000136
          Read_Master_Log_Pos: 96592981
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 72113
        Relay_Master_Log_File: mysql-bin.000136
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: test,bs_com,bs_sysmsg,bs_bak
          Replicate_Ignore_DB: mysql
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 73824655
              Relay_Log_Space: 22840613
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 3815
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 2
                  Master_UUID: afd6bca4-6636-11e3-9d60-74867ae1c47c
             Master_Info_File: /data/mysqldata/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: updating
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)

The above is the content of using Innobackupex to quickly build (repair) the MySQL master-slave architecture. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!