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

This article mainly tells you the detailed steps of configuring mysql master and master synchronization mode. 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 on the detailed steps of configuring mysql master and master synchronization mode can bring you some practical help.

Create a new database db1 db2 db3 for both master 192.168.56.20 and slave (if the database is in use, you need to lock the master backup manually, and then restore from the slave)

Mysql > create database db1

Query OK, 1 row affected (0.00 sec)

Mysql > create database db2

Query OK, 1 row affected (0.00 sec)

Mysql > create database db3

Query OK, 1 row affected (0.01sec)

The account number of the master authorized slave

Grant replication slave on. To 'repl'@'192.168.56.21' identified by' repl'

Flush privileges

Configuration file opens bin-log

Vim / etc/my.cnf

[mysql]

Server-id=101 # each node must be different

Log-bin=/var/lib/mysql/mysql-bin

Restart the main service

Service mysqld restart

View log files and offsets

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | |

+-+

| | mysql-bin.000001 | 120 | |

+-+

1 row in set (0.00 sec)

From 192.168.56.21

Modify the configuration file

Vim / etc/my.cnf

Server-id=109

Restart the slave node service

Service mysqld restart

Configure slave library

Turn off replication

Mysql > stop slave

Mysql > change master to master_host='192.168.56.20',master_user='repl',master_password='repl',master_log_file='mysql-bin.000001',master_log_pos=120

Start replication

Mysql > start slave

Check the replication function

Mysql > show slave status\ G

Both processes need yes to function properly

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Create a new database in the main database, and then go to the slave library to see if it is synchronized. Synchronization is normal.

Create database hi_db

Master-slave synchronization has been configured, now start to configure master.

Configure the main configuration file 192.168.56.20

Vim / etc/my.cnf

[mysql]

Server-id=101 # each node must be different

Log-bin=/var/lib/mysql/mysql-bin

Auto_increment_increment=2 # step value auto_imcrement. Usually fill in n if there are n master MySQL

Auto_increment_offset=1 # starting value. Generally fill in the nth main MySQL. This is the first master MySQL.

# binlog-ignore=mysql # ignore the mysql library [I don't usually write]

# binlog-ignore=information_schema # ignore the information_schema library [I don't usually write]

# replicate-do-db=aa # databases to be synchronized. Default is all libraries.

Restart the primary node service

Service mysqld restart

Configuration from configuration file 192.168.56.21

Vim / etc/my.cnf

Server-id=109

Log-bin=/var/lib/mysql/mysql-bin

Auto_increment_increment=2 # step value auto_imcrement. Usually fill in n if there are n master MySQL

Auto_increment_offset=2 # starting value. Generally fill in the nth main MySQL. This is the second main MySQL.

Restart the slave node service

Service mysqld restart

Create a master replicated account on the slave node

Grant replication slave on. To 'repl1'@'192.168.56.20' identified by' repl1'

Flush privileges

View binaries

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | |

+-+

| | mysql-bin.000001 | 420 |

+-+

1 row in set (0.00 sec)

In the main execution

Turn off replication

Mysql > stop slave

Mysql > change master to master_host='192.168.56.21',master_user='repl1',master_password='repl1',master_log_file='mysql-bin.000001',master_log_pos=420

Start replication

Mysql > start slave

Check the replication function

Mysql > show slave status\ G

Both processes need yes to function properly

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Then create databases on both sides to see if they are synchronized.

1. Auto_increment_increment and auto_increment_offset in the master replication configuration file can only ensure that the primary key does not repeat, but not the order of the primary key.

2. When the configuration is completed with Slave_IO_Running and Slave_SQL_Running is not all YES, there is an error message in the show slave status\ G message, which can be corrected according to the error prompt. 3. When Slave_IO_Running and Slave_SQL_Running are not all YES, most of the problems are caused by data inconsistency. Common error points: 1, two databases have db database, and the first MySQL db has tab1, the second MySQL db does not have tab1, it will not be successful. 2. The binary log name and location of the data have been obtained, and the data operation has been carried out, which leads to the change of POS. The previous POS is still used when configuring CHANGE MASTER. 3. After stop slave, the data changes, and then start slave. Something went wrong.

Ultimate correction: just run the CHANGE MASTER again.

The detailed steps of configuring mysql master master synchronization mode will 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.