使用二進位熱備份建立MySQL主從複製

搭建環境說明

• 機器說明

  Master 190: 192.168.1.190 (開啟binlog, server-id=1)
  Slave 191: 192.168.1.191
  Slave 192: 192.168.1.192
  以上三台機器都安裝了MySQL 5.5.29, Percona XtraBackup 2.06

基於Master的二進位熱備份建立新Slave

• 在Master 190上建立Slave 191用於MySQL複製的帳號

  mysql> create user 'newrepl'@'192.168.1.191' identified by '123456';mysql> grant replication slave on *.* to 'newrepl'@'192.168.1.191';mysql> flush privileges;

• 將Master 190的MySQL資料直接遠程熱備份到Slave 191

  [root@CentOS190 ~]# innobackupex --user=backup --password=123456 --parallel=4 --stream=tar ./ | ssh root@192.168.1.191 "tar -ixf - -C /var/lib/mysql/data"

• 在Slave 191上應用備份,並對備份資料目錄做相應使用權限設定

  [root@centos191 ~]# innobackupex --apply-log --use-memory=4G /var/lib/mysql/data[root@centos191 ~]# chown -R mysql:mysql /var/lib/mysql/data

• Slave 191上停止MySQL,進行MySQL配置,然後啟動MySQL

  [root@centos191 mysql]# service mysql stop[root@centos191 mysql]# scp root@192.168.1.190:/etc/my.cnf /etc/my.cnf

  修改/etc/my.cnf:

  ...[mysqld]datadir             = /var/lib/mysql/dataserver-id           = 2 # Master的server-id = 1relay-log           = slave-relay-binrelay-log-index     = slave-relay-bin.index...

  配置完後,啟動MySQL:

  [root@centos191 mysql]# service mysql startStarting MySQL....                                         [  OK  ]

• 建立複製串連開始複製,並檢查slave運行狀態

  查看熱備份的binlog檔案名稱和位移位置資訊:

  [root@centos191 mysql]# cat /var/lib/mysql/data/xtrabackup_binlog_info master-bin.000005107

  建立複製串連並檢查是否成功:

  mysql> CHANGE MASTER TO    -> MASTER_HOST='192.168.1.190',    -> MASTER_USER='newrepl',    -> MASTER_PASSWORD='123456',    -> MASTER_PORT=3306,    -> MASTER_LOG_FILE='master-bin.000005',    -> MASTER_LOG_POS=107;Query OK, 0 rows affected (0.96 sec)mysql> start slave;Query OK, 0 rows affected (0.00 sec)mysql> show global status like 'Slave_running';+---------------+-------+| Variable_name | Value |+---------------+-------+| Slave_running | ON    |+---------------+-------+1 row in set (0.00 sec)

基於Slave的二進位熱備份建立新Slave

• 在Master 190上建立Slave 192用於MySQL複製的帳號

  [root@centos191 mysql]# mysql -uroot -p -h192.168.1.190...mysql> create user 'newrepl'@'192.168.1.192' identified by '123456';mysql> grant replication slave on *.* to 'newrepl'@'192.168.1.192';mysql> flush privileges;

• 將Slave 191的MySQL資料直接遠程熱備份到Slave 192

  # innobackupex --user=backup --password=123456 --parallel=4 --slave-info --safe-slave-backup --stream=tar ./ | ssh root@192.168.1.192 "tar -ixf - -C /var/lib/mysql/data"

  注:這裡備份使用--slave-info可以將Master的binary log的檔案名稱和位移位置記錄到xtrabackup_slave_info檔案中.而使用--safe-slave-backup會暫停Slave的SQL線程直到備份結束,這樣可以確保一致性的複製狀態.

• 在新Slave 192上應用備份,並對備份資料目錄做相應使用權限設定

  [root@centos192 ~]# innobackupex –apply-log –use-memory=4G /var/lib/mysql/data
  [root@centos192 ~]# chown -R mysql:mysql /var/lib/mysql/data

• 新Slave 192上停止MySQL,進行MySQL配置,然後啟動MySQL

  [root@centos192 mysql]# service mysql stop[root@centos192 mysql]# scp root@192.168.1.191:/etc/my.cnf /etc/my.cnf

  修改/etc/my.cnf:

  ...[mysqld]server-id  = 3skip-slave-start...

  啟動MySQL:

  [root@centos192 mysql]# service mysql startStarting MySQL...                                          [  OK  ]

• 建立複製串連開始複製,並檢查slave運行狀態

  查看Slave熱備份的binlog檔案名稱和位移位置資訊:

  [root@centos192 mysql]# cat /var/lib/mysql/data/xtrabackup_slave_info CHANGE MASTER TO MASTER_LOG_FILE='master-bin.000005', MASTER_LOG_POS=614672

  建立複製串連並檢查是否成功:

  mysql> CHANGE MASTER TO    -> MASTER_HOST='192.168.1.190',    -> MASTER_USER='newrepl',    -> MASTER_PASSWORD='123456',    -> MASTER_PORT=3306,    -> MASTER_LOG_FILE='master-bin.000005',    -> MASTER_LOG_POS=614672;Query OK, 0 rows affected (0.33 sec)mysql> start slave;Query OK, 0 rows affected (0.00 sec)mysql> show slave status \G;   ...             Slave_IO_Running: Yes            Slave_SQL_Running: Yes  ...         Seconds_Behind_Master: 0  ...

  修改/etc/my.cnf,注釋掉”skip-slave-start”,然後重啟MySQL即可.

  # sed -i 's/skip-slave-start/#skip-slave-start/g' /etc/my.cnf# service mysql restart

推薦閱讀：

Ubuntu下Nginx做負載實現高效能WEB伺服器5---MySQL主主同步

生產環境MySQL主主同步主鍵衝突處理

MySQL主從失敗 錯誤Got fatal error 1236

MySQL主從複製，單台伺服器上實施