MySQL 5.7 用xtrabackup搭建gtid主從

甲骨文技術支援發表於2017-12-14
原文網址 : http://blog.itpub.net/20893244/viewspace-2148729/
MySql
mysql版本是5.7.17

作業系統是centos 7

mysql資料目錄:/alidata1/mysql

mysql備份目錄:/alidata1/backup/full_mysql 

在master及slave機器安裝xtrabackup軟體
  1. [root@iz2ze6jo3o3bqbcongnypqz innobackupex]# rpm -ivh percona-xtrabackup-24-2.4.9-1.el7.x86_64.rpm
  2. warning: percona-xtrabackup-24-2.4.9-1.el7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
  3. error: Failed dependencies:
  4. libev.so.4()(64bit) is needed by percona-xtrabackup-24-2.4.9-1.el7.x86_64
  5. perl(DBD::mysql) is needed by percona-xtrabackup-24-2.4.9-1.el7.x86_64
  6. perl(Digest::MD5) is needed by percona-xtrabackup-24-2.4.9-1.el7.x86_64
  7. rsync is needed by percona-xtrabackup-24-2.4.9-1.el7.x86_64
libev.so.4()的解決到下面這裡下載作業系統對應的版本，本例下載的是libev-4.15-7.el7.x86_64.rpm

  1. http://rpmfind.net/linux/rpm2html/search.php?query=libev.so.4%28%29%2864bit%29&submit=Search+...&system=&arch=
perl(DBD::mysql)和perl(Digest::MD5)，需要安裝mysql-community-libs-compat-5.7.17-1.el7.x86_64.rpm，在安裝包裡找到即可

在master機器操作

1、在資料庫建立備份賬號

  1. CREATE USER xtrabk@'localhost' IDENTIFIED BY 'onlyxtrabk!@#$';
  2. GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT,Process ON *.* TO xtrabk@'localhost';
  3. FLUSH PRIVILEGES;
2、備份主庫


  1. innobackupex --defaults-file=/etc/my.cnf --user=xtrabk --password='onlyxtrabk!@#$' --parallel=4 /alidata1/backup/full_mysql --no-timestamp

在slave機器操作

1、停止mysql,刪除或者重新命名Mysql資料目錄

  1. systemctl stop mysqld.service
  2. rm -rf /alidata1/mysql/data
  3. rm -rf /alidata1/mysql/redolog
2、應用日誌及資料庫還原
  1. innobackupex --defaults-file=/etc/my.cnf --apply-log /alidata1/backup/full_mysql


  4. innobackupex --defaults-file=/etc/my.cnf --copy-back /alidata1/backup/full_mysql

3、修改資料目錄的宿主許可權

  1. chown -R mysql:mysql /alidata1/mysql

4、啟動mysql

  1. systemctl start mysqld.service
5.過濾掉已執行過的gtid



  1. cat /alidata1/backup/full_mysql/xtrabackup_info |grep binlog_pos
  2. [root@iz2ze6jo3o3bqbcongnyppz full_mysql]# cat /alidata1/backup/full_mysql/xtrabackup_info |grep binlog_pos
  3. binlog_pos = filename 'bin.000131', position '615481029', GTID of the last change 'c9c73c70-c089-11e7-8544-00163e0ad76e:1-107089934'
6.檢視slave已執行的gtid是否為空，如果不為空，需要執行reset MASTER進行清理，否則無法設定gtid。



  1. mysql> show master status \G;
  2. *************************** 1. row ***************************
  3.              File: bin.000001
  4.          Position: 154
  5.      Binlog_Do_DB:
  6.  Binlog_Ignore_DB:
  7. Executed_Gtid_Set: c9c73c70-c089-11e7-8544-00163e0ad76e:1-106016597
  8. 1 row in set (0.00 sec)
7.執行reset master

8.執行GTID_PURGED

  1. SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
  2. SET @@SESSION.SQL_LOG_BIN= 0;
  3. SET @@GLOBAL.GTID_PURGED='c9c73c70-c089-11e7-8544-00163e0ad76e:1-107089934';
  4. SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;
9.change master



  1. change master to
  2. master_host='192.168.2.71',
  3. master_port=3306,
  4. master_user='repl',
  5. master_password='REPLsafe!@#$71',
  6. MASTER_AUTO_POSITION = 1;
10. start slave ;

11.show slave status\G;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/20893244/viewspace-2148729/，如需轉載，請註明出處，否則將追究法律責任。

相關文章