Shulou(Shulou.com)06/01 Report--

This article mainly tells you about the method of building MySQL database for each other. You can check the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here, so let's go straight to the topic. I hope this article can bring you some practical help to realize the method of building MySQL database for each other.

1. Dual-master implementation scheme:

1. Let the id of the watch increase by itself, and then the main one writes 1Magol 3JEI 5, and the Master 2 writes 2pm Jet 4QR 6.

2. Do not allow the id of the table to increase itself, and then read the id from the seq CVM through the web program and write it to the dual master.

Two main work scenes: write scenes with high temples, use with caution.

Compared with multi-instance and master-slave synchronization: additional parameters of master database:

two。 The first method implements the master master replication:

1. The parameters that need to be added for master / slave synchronization [Master Library] M:

Auto_increment_increment = 2

Auto_increment_offset = 1

Log-slave-updates

Log-bin = / data/3306/mysql-bin

Expire_logs_days = 7

/ etc/init.d/mysqldrestart

2. Parameters that need to be added for master-slave synchronization [slave library] S:

Auto_increment_increment = 2

Auto_increment_offset = 2

Log-bin = / data/3307/mysql-bin

Log-slave-updates

Expire_logs_days = 7

/ etc/init.d/mysqld restart

3. Validation of parameter configuration:

Mysql-uroot-p123456-e "showvariables like 'log_%';" | grep-I "on"

Mysql-uroot-p123456-e "showvariables like 'log_%';" | grep-I "on"

Mysql-uroot-p123456-e "showvariables like 'auto%';" | egrep "2 | 1 | 2"

Mysql-uroot-p123456-e "showvariables like 'auto%';" | egrep "2 | 1 | 2"

4. For mysql-5.6.16-slave1 backup from the slave library:

Mysqldump-uroot-p123456-B-A-F-R-x--master-data=1-- events | gzip > / backup/slave_$ (date +% F) .sql.gz

Scp-rp-P22/backup/slave_2016-08-19.sql.gz root@172.16.1.41:/backup

5. The main library imports data pushed from the library:

Gzip-d / backup/slave_2016-08-19.sql.gz

Mysql-uroot-p123456 use wjw01

Mysql > CREATE table `t1` (`id` bigint (12) NOTNULL auto_increment, `name` varchar (12) NOTNULL, PRIMARY KEY (`id`))

Mysql > INSERT INTO T1 (name) values ('wangwu')

Slave2:172.16.1.43

[root@mysql-5 backup] # mysql- uroot-p123456-e "select * from wjw01.t1"

Warning: Using a password on the commandline interface can be insecure.

+-+ +

| | id | name |

+-+ +

| | 1 | wangwu |

After viewing from the library slave2, the data has been synchronized.

Master2___slave1:172.16.1.42

Mysql-uroot-p123456-e "select * from wjw01.t1"

The data of the main library master1 is not synchronized to the main library master2__slave1

Master2__ slave1 terminal inserts data demonstration:

Mysql > use wjw01

Mysql > INSERT INTO T1 (name) values ('oldgirl')

Slave2:172.16.1.43 View:

[root@mysql-5 backup] # mysql- uroot-p123456-e "select * from wjw01.t1"

Warning: Using a password on the command lineinterface can be insecure.

+-+ +

| | id | name |

+-+ +

| | 1 | wangwu |

| | 2 | oldgirl |

Data synchronization has passed.

Master1:172.16.1.41 View:

[root@mysql-5 data] # mysql- uroot-p123456mure "select * from wjw01.t1"

Warning: Using a password on the commandline interface can be insecure.

+-+ +

| | id | name |

+-+ +

| | 1 | wangwu |

The database of Master2_ slave1 is not synchronized to Master1

Check on master2__ Slave1 and Master1: no error was reported during synchronization, which is too strange

[root@mysql-5 binlog] # mysql- uroot-p123456-e "show slavestatus\ G" | grep-I "yes"

Warning: Using a password on the commandline interface can be insecure.

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

[root@mysql-5 data] # mysql- uroot-p123456-e "show slavestatus\ G" | grep-I "yes"

Warning: Using a password on the commandline interface can be insecure.

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Search the Internet to find the cause of the problem:

Solution: (courtesy of netizens)

Statement mode is not enough for applications, so try mixed instead:

Operate on the master2__slave1 side:

Mysql > STOP SLAVE

Query OK, 0 rows affected (0.02 sec)

Mysql > SET GLOBAL binlog_format=MIXED

Query OK, 0 rows affected (0.00 sec)

Mysql > START SLAVE

Query OK, 0 rows affected (0.00 sec)

Mysql > show slave status\ G

Problem solved. Tip: the modification here is only temporary. Once the MySQL service is restarted, it will become invalid. Add the parameters to the my.cnf configuration file to start the MySQL service, and it will take effect permanently.

The correct setting method for the parameters of Master2__ Slave 1:

[root@mysql-5 binlog] # cat-n / etc/my.cnf | sed-nasty 42mai 49p'

42 # # double main implementation method #

43 log_bin = / home/mysql/data/binlog/mysql-bin

44 auto_increment_increment = 2

45 auto_increment_offset = 2

46 log-slave-updates

47 expire_logs_days = 7

48 binlog_format = mixed

49 # double main implementation method # #

Continue inserting data on master1 side

Mysql > INSERT INTO T1 (name) values ('pig')

Slave2 section view:

[root@mysql-5 binlog] # mysql- uroot-p123456-e "select * fromwjw01.t1"

Warning: Using a password on the command lineinterface can be insecure.

+-+ +

| | id | name |

+-+ +

| | 1 | wangwu |

| | 2 | oldgirl |

| | 3 | pig |

View on Master2__ Slave1:

[root@mysql-5 backup] # mysql- uroot-p123456-e "select * from wjw01.t1"

Warning: Using a password on the command lineinterface can be insecure.

+-+ +

| | id | name |

+-+ +

| | 1 | wangwu |

| | 2 | oldgirl |

| | 3 | pig |

Master2__ Slave1 inserts data:

Mysql > INSERT INTO T1 (name) values ('apple')

Check in the master1 section: data synchronization has passed.

[root@mysql-5 data] # mysql- uroot-p123456-e "select * from wjw01.t1"

Warning: Using a password on the command lineinterface can be insecure.

+-+ +

| | id | name |

+-+ +

| | 1 | wangwu |

| | 2 | oldgirl |

| | 3 | pig |

| | 4 | apple |

Check in the slave2 section: data synchronization has passed.

[root@mysql-5 data] # mysql- uroot-p123456-e "select * from wjw01.t1"

Warning: Using a password on the command lineinterface can be insecure.

+-+ +

| | id | name |

+-+ +

| | 1 | wangwu |

| | 2 | oldgirl |

| | 3 | pig |

| | 4 | apple |

To achieve the method of building MySQL database for each other, let's stop here. If you want to know about other related issues, you can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

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.