Specific steps for configuring mysql database cascading synchronization

The following is about the specific steps of configuring mysql database cascading synchronization. The secret of the text is that it is close to the topic. So, no gossip, let's go straight to the following, I believe you will benefit from reading this article on the specific steps of configuring mysql database cascading synchronization.

Mysql database cascade synchronization configuration step, this article takes a mysql database multi-instance 3306, 3307 and 3309 as an example, with 3306 as the master database, 3307 as the slave database and 3309 as the sub-slave database (multiple single instance configuration is the same as a multi-instance configuration)

I. my.cnf file configuration

1. Three database instances modify the my.cnf configuration file. Master library 3306 and slave library 3307 need to open log-bin, but the three server-id cannot be the same.

[root@mysql ~] # egrep "log-bin | server-id" / data/ {3306 3307 3309} / my.cnf

/ data/3306/my.cnf:log-bin = / data/3306/mysql-bin

/ data/3306/my.cnf:server-id = 1

/ data/3307/my.cnf:log-bin = / data/3307/mysql-bin

/ data/3307/my.cnf:server-id = 3

/ data/3307/my.cnf:#log-bin = / data/3309/mysql-bin

/ data/3307/my.cnf:server-id = 5

two。 Add a content from the [mysqld] module of the my.cnf file in library 3307

Log-slave-updates

3. Restart databases 3306, 3307, and 3309

[root@mysql ~] # / data/3306/mysql stop

[root@mysql ~] # / data/3306/mysql start

[root@mysql ~] # / data/3307/mysqlstop

[root@mysql ~] # / data/3307/mysqlstart

[root@mysql ~] # / data/3309/mysqlstop

[root@mysql ~] # / data/3309/mysqlstart

4. Enter the main database 3306, query whether log_bin is open, what the server_id is, and 3307 to do the same

[root@mysql] # mysql-uroot-p123456-S / data/3306/mysql.sock

Mysql > show variables like "log_bin"

+-+ +

| | Variable_name | Value |

+-+ +

| | log_bin | ON |

+-+ +

1 row in set (0.01 sec)

Mysql > show variables like "server_id"

+-+ +

| | Variable_name | Value |

+-+ +

| | server_id | 1 | |

+-+ +

1 row in set (0.00 sec)

5. Create a user dedicated to synchronizing the database on the main library 3306

Mysql > grant replicationslave on *. * to rep@'10.0.0.%' identified by '123456'

# *. * represents all libraries and all tables

Mysql > flush privileges

Mysql > select user,hostmysql.user

Mysql > show grants forrep@'10.0.0.%'

two。 Master library 3306 backup data, import slave library 3307

1. Back up data on the main library 3306

[root@mysql] # mysqldump-uroot-p123456-S / data/3306/mysql.sock-A-- events-B-x-- master-data=1 | gzip > / opt/$ (date +% F) .sql.gz

-A: all libraries-x: global locking table

two。 Import data from library 3307

[root@mysql ~] # ls / opt

2017-07-03.sql.gz

[root@mysql ~] # cd / opt

[root@mysql opt] # gzip-d2017-07-03.sql.gz

[root@mysql opt] # ls

2017-07-03.sql

[root@mysql opt] # mysql-uroot-p123456-S / data/3307/mysql.sock