MySQL 5.5級聯復制配置的示例分析

175次閱讀

沒有評論

共計 5057 個字符，預計需要花費 13 分鐘才能閱讀完成。

這篇文章主要介紹 MySQL 5.5 級聯復制配置的示例分析，文中介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們一定要看完！

如下圖，A 節點是 B 節點的 Master，B 節點是 C 節點的 Master
A – B – C

在已有的 A – B 復制環境中，添加級聯 slave 節點 C
– 各節點 IP 如下
A 192.168.78.141
B 192.168.78.137
C 192.168.78.135

A – B 復制配置流程，請參考文章
http://blog.itpub.net/26506993/viewspace-2091601/

– 在節點 B 上，增加下面參數后，重啟 MySQL 數據庫服務
–log-slave-updates

通常，在 Mysql 復制環境中，slave 節點不會將從主節點接收的數據更新寫入到它的二進制日志中。這個參數將會使 SQL 線程在應用日志的時候將相關變更記錄到它自己的二進制日志中。

[root@localhost 5505]# vim /mysql_data/cnf/my.cnf
[mysqld]
# Replication
log-slave-updates

– 節點 B，重啟 Mysql 服務
[root@localhost bin]# /data/bin/mysqladmin -usystem -p Mysql#2015 shutdown
160502 19:36:01 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
[1]+ Done /data/bin/mysqld_safe –defaults-file=/mysql_data/cnf/my.cnf
[root@localhost bin]# /data/bin/mysqld_safe –defaults-file=/mysql_data/cnf/my.cnf
[1] 57552
[root@localhost bin]# 160502 19:36:13 mysqld_safe Logging to /mysql_log/err.log .
160502 19:36:13 mysqld_safe Starting mysqld daemon with databases from /mysql_data/5505

– 節點 B，停止 sql_thread 線程
mysql stop slave sql_thread;
Query OK, 0 rows affected (0.03 sec)

– 配置 B – C 的復制環境

– 在 C 節點，安裝好 Mysql 數據庫軟件

– 創建數據文件目錄
[root@localhost 70005]# mkdir -p /mysql_data/70005

– 創建配置文件的目錄
[root@localhost 70005]# mkdir -p /mysql_data/cnf/

– 創建日志文件所在目錄
[root@localhost ~]# mkdir -p /mysql_log/binlog
[root@localhost ~]# mkdir -p /mysql_log/innodb
[root@localhost ~]# chown -R mysql.mysql /mysql_log/

– 在 B 節點，使用 Xtrabackup 創建完整備份
[root@localhost percona-xtrabackup-2.4.2-Linux-x86_64]# ./bin/innobackupex –defaults-file=/mysql_data/cnf/my.cnf –stream=tar /tmp –user system –password Mysql#2015 | gzip –
/backup/xtra_fullbackup_20160503.tar.gz

– 拷貝備份和配置文件到 C 節點
[root@localhost backup]# scp /backup/xtra_fullbackup_20160503.tar.gz root@192.168.78.135:/backup/20160503
[root@localhost percona-xtrabackup-2.4.2-Linux-x86_64]# scp /mysql_data/cnf/my.cnf root@192.168.78.135:/mysql_data/cnf/

–C 節點，解壓備份到數據文件目錄
[root@localhost 5505]# tar xivfz /backup/20160503/xtra_fullbackup_20160503.tar.gz -C /mysql_data/70005
[root@localhost ~]# chown -R mysql.mysql /mysql_data/

– 在 C 節點上面需要安裝 Xtraback，可參看文章
http://blog.itpub.net/26506993/viewspace-2087734/
http://blog.itpub.net/26506993/viewspace-2088737/

– 節點 C，使用 Xtrabackup 準備數據、應用日志，使數據文件達到一致性的狀態
[root@localhost bin]# ./innobackupex –defaults-file=/mysql_data/cnf/my.cnf –apply-log /mysql_data/70005
…..
InnoDB: Log scan progressed past the checkpoint lsn 68409356
InnoDB: Doing recovery: scanned up to log sequence number 68409365 (0%)
InnoDB: Doing recovery: scanned up to log sequence number 68409365 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: xtrabackup: Last MySQL binlog file position 823, file name /mysql_log/binlog/mysql-bin.000002
InnoDB: Removed temporary tablespace data file: ibtmp1
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file ./ibtmp1 size to 12 MB. Physically writing the file full; Please wait …
InnoDB: File ./ibtmp1 size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: 5.7.11 started; log sequence number 68409365
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown…
InnoDB: Shutdown completed; log sequence number 68409384
160502 20:41:13 completed OK!

– 節點 B，在 Master 節點 B 上面創建復制專用賬戶
mysql grant replication slave on *.* to repl @ 192.168.78.% identified by Mysql#2015
Query OK, 0 rows affected (0.04 sec)

– 節點 C，配置 Slave 節點 C 的配置文件
[root@localhost bin]# vim /mysql_data/cnf/my.cnf
# Log
server-id = 50005
log-bin = /mysql_log/binlog/product-node3-mysql-bin
relay-log = /mysql_log/binlog/product-node3-relay-bin
relay-log-index = /mysql_log/binlog/product-node3-relay-index
binlog_cache_size = 32M
max_binlog_cache_size = 512M
max_binlog_size = 512M
binlog_format = MIXED

– 節點 C，啟動 Slave 節點的 Mysql 服務
[root@localhost bin]# /software/bin/mysqld_safe –defaults-file=/mysql_data/cnf/my.cnf

– 節點 C，配置 Slave 節點復制環境
– 節點 C，在 Slave 節點執行 CHANGE MASTER 語句
mysql change master to
– master_host= 192.168.78.137 ,
– master_port=5505,
– master_user= repl ,
– master_password= Mysql#2015 ,
– master_log_file= mysql-bin.000005 ,
– master_log_pos=107;

– 節點 C，啟動應用線程
mysql start slave;

– 節點 C，查看應用狀態
mysql show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.78.137
Master_User: repl
Master_Port: 5505
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 107
Relay_Log_File: product-node3-relay-bin.000002
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

– 啟動節點 B 的 SQL 線程
mysql start slave sql_thread;

以上是“MySQL 5.5 級聯復制配置的示例分析”這篇文章的所有內容，感謝各位的閱讀！希望分享的內容對大家有幫助，更多相關知識，歡迎關注丸趣 TV 行業資訊頻道！

正文完