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
--檢視引數是否生效
mysql> show variables like 'log_slave_updates';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| log_slave_updates | ON |
+-------------------+-------+
1 row in set (0.00 sec)
--節點B,停止sql_thread執行緒
mysql> stop slave sql_thread;
Query OK, 0 rows affected (0.03 sec)
--節點B,記錄下二進位制日誌的檔案和位置
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000005 | 107 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 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;
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
--檢視引數是否生效
mysql> show variables like 'log_slave_updates';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| log_slave_updates | ON |
+-------------------+-------+
1 row in set (0.00 sec)
--節點B,停止sql_thread執行緒
mysql> stop slave sql_thread;
Query OK, 0 rows affected (0.03 sec)
--節點B,記錄下二進位制日誌的檔案和位置
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000005 | 107 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 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;
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/26506993/viewspace-2092735/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- MySQL 5.5 複製搭建流程MySql
- MySQL 5.5 主主複製搭建流程MySql
- redhat 5.5 配置 mysql AB複製RedhatMySql
- 配置mysql5.5主從複製、半同步複製、主主複製MySql
- MySQL 8 複製(九)——組複製聯機配置MySql
- MySQL 5.5半同步複製的配置與監控MySql
- MySQL級聯複製中資料同步MySql
- MySQL入門--MySQL複製技術之主從從級聯複製MySql
- MySQL 5.5.x 配置Master-Slave主從複製MySqlAST
- mysql5.5半同步複製探究MySql
- MySQL級聯複製的同步問題(一)MySql
- mysql複製--主從複製配置MySql
- mysql5.5中的半同步複製MySql
- MySQL 5.5複製升級到5.7的一點簡單嘗試MySql
- MySQL 5.5 常用的複製環境管理命令MySql
- MySQL5.5半同步複製實現原理MySql
- MySQL 5.5使用Xtrabackup線上搭建複製環境MySql
- Mysql的複製原理以及流程MySql
- MySQL 8 複製(五)——配置GTID複製MySql
- MySQL 5.5 Semi-sync 半同步複製測試MySql
- MySql 主從複製配置MySql
- MySQL主從複製配置MySql
- MySQL級聯複製中的資料同步(第二篇)MySql
- MYSQL主從複製製作配置方案MySql
- mysql主主複製(雙主複製)配置步驟MySql
- mysql5.5.20複製配置MySql
- MYSQL主從複製配置(整理)MySql
- MySQL複製3--配置MasterMySqlAST
- MySQL 5.7 延遲複製配置MySql
- MySQL 主從複製的執行流程MySql
- Mysql 5.6庫級表級複製的搭建MySql
- Mysql分散式部署 - 多級複製MySql分散式
- MySQL8.0.11 組複製配置MySql
- mysql for linux 配置主從複製MySqlLinux
- mysql主從複製原理及配置MySql
- MySQL 5.5 Master/Slave 配置MySqlAST
- MySQL 5.5 原始碼安裝流程MySql原始碼
- MySQL主主複製(雙主複製)配置過程介紹MySql