Shulou(Shulou.com)05/31 Report--

This article mainly introduces MySQL 5.5 cascading replication configuration example analysis, the article is very detailed, has a certain reference value, interested friends must read!

As shown below, Node A is the Master of Node B, and Node B is the Master of Node C.

A -> B -> C

Add cascading slave node C to existing A -> B replication environment

--IP of each node is as follows

A 192.168.78.141

B 192.168.78.137

C 192.168.78.135

A -> B copy configuration process, please refer to the article

http://blog.itpub.net/26506993/viewspace-2091601/

--On Node B, restart MySQL database service after adding the following parameters

--log-slave-updates

Typically, in a Mysql replication environment, a slave node does not write data updates received from the master node to its binary log. This parameter will cause the SQL thread to log changes to its own binary log when applying logs.

[root@localhost 5505]# vim /mysql_data/cnf/my.cnf

[mysqld]

# Replication

log-slave-updates

--Node B, restart Mysql service

[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

--Check whether parameters are valid

mysql> show variables like 'log_slave_updates';

+-------------------+-------+

| Variable_name | Value |

+-------------------+-------+

| log_slave_updates | ON |

+-------------------+-------+

1 row in set (0.00 sec)

--Node B, stop sql_thread thread

mysql> stop slave sql_thread;

Query OK, 0 rows affected (0.03 sec)

--Node B, record file and location of binary log

mysql> show master status;

+------------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000005 | 107 | | |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

--Configure replication environment for B -> C

--Install Mysql database software on node C

--Create data file directory

[root@localhost 70005]# mkdir -p /mysql_data/70005

--Create a directory of configuration files

[root@localhost 70005]# mkdir -p /mysql_data/cnf/

--Create log file directory

[root@localhost ~]# mkdir -p /mysql_log/binlog

[root@localhost ~]# mkdir -p /mysql_log/innodb

[root@localhost ~]# chown -R mysql.mysql /mysql_log/

--At Node B, create a full backup using 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

--Copy backup and configuration files to node 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 node, extract backup to data file directory

[root@localhost 5505]# tar xivfz /backup/20160503/xtra_fullbackup_20160503.tar.gz -C /mysql_data/70005

[root@localhost ~]# chown -R mysql.mysql /mysql_data/

--Xtraback needs to be installed on node C, see article

http://blog.itpub.net/26506993/viewspace-2087734/

http://blog.itpub.net/26506993/viewspace-2088737/

--Node C, use Xtrabackup to prepare data and apply logs, so that the data file reaches a consistent state

[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!

--Node B, create replication dedicated account on Master Node B

mysql> grant replication slave on *.* to 'repl'@'192.168.78.% ' identified by 'Mysql#2015';

Query OK, 0 rows affected (0.04 sec)

--Node C, configure Slave Node C's configuration file

[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

--Node C, start Mysql service of Slave node

[root@localhost bin]# /software/bin/mysqld_safe --defaults-file=/mysql_data/cnf/my.cnf &

--Node C, configure Slave node replication environment

--Node C, execute CHANGE MASTER statement at Slave node

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;

--Node C, start application thread

mysql> start slave;

--Node C, view application status

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

--Start SQL thread of Node B

mysql> start slave sql_thread;

The above is "MySQL 5.5 Cascading Replication Configuration Sample Analysis" all the content of this article, thank you for reading! Hope to share the content to help everyone, more relevant knowledge, welcome to pay attention to the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.