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

In the master-master replication structure, any change in the data inventory on either of the two servers will be synchronized to the other server, so that the two servers are master and slave to each other and can provide services to the outside world. It should be noted when configuring: the primary key is repeated and the server-id cannot be repeated.

1. Create a new database

Ubuntu16.04, turn on two virtual machines.

Before configuration, the two mysql create a new database respectively to ensure that the data of the test database is consistent.

Create database test default character set utf8 default collate utf8_general_ci

two。 Modify the configuration file

Vim / etc/mysql/mysql.conf.d/mysqld.cnf

Server A (192.168.8.129) is configured as follows

Bind-address = 0.0.0.0

Log_bin = / var/log/mysql/mysql-bin.log # enable binary log

Server-id = 1 # any natural number n, as long as the two MySQL hosts are not duplicated

Expire-logs-days = 100

Replicate-do-db = test # databases to be synchronized. Default is all libraries.

Binlog-ignore-db = mysql # ignore the mysql library

Binlog-ignore-db = information_schema # ignore the information_ schema library

Auto-increment-increment = 2 # step value. Enter n if there are n master MySQL

Auto-increment-offset = 1 # starting value. The nth master MySQL, at this time is the first master MySQL

Server B (192.168.8.131) configuration

Bind-address = 0.0.0.0

Log_bin = / var/log/mysql/mysql-bin.log

Server-id = 2

Expire-logs-days = 100

Replicate-do-db = test

Binlog-ignore-db = mysql

Binlog-ignore-db = information_schema

Auto-increment-increment = 2

Auto-increment-offset = 2 # starting value, 2nd main MySQL

Only server-id is different from auto-increment- offset.

Auto-increment-offset is used to set the starting point of automatic growth in the database. Both servers set an automatic growth value of 2, so their starting points must be different, so as to avoid primary key conflicts when the two servers synchronize data.

Replicate-do-db specifies the synchronized database, and we only synchronize the test database between the two servers. The value of auto-increment-increment should be set to the total number of servers in the entire structure. In this case, two servers are used, so the value is set to 2

3. Restart the mysql service

/ etc/init.d/mysql restart

4. Create a shared user

Create a MySQL user repl that can log in on another host

See if there is a user repl in mysql.

Use mysql

Select user from user

If not, create a new user

Create user 'repl'@'%' IDENTIFIED BY' passwd'

Grant replication slave on. To 'repl'@'%'

Flush privileges

View permissions of the repl user

Show grants for 'repl'@'%'

5. Test whether you can log in remotely

On server A (192.168.8.129)

Mysql-h 192.168.8.131-u repl-p

Enter password jvkpxdYrlz

If successful, move on to the next step

If it fails, check the configuration in the mysqld.cnf file, which may be caused by bind-address=127.0.0.1 so that only local access is allowed.

View the binary log name and location respectively

Show master status

Mutually authorized users

Stop slave on the two services first.

Stop slave

On server A (192.168.8.129)

Change master to MASTER_HOST='192.168.8.131',MASTER_USER='repl', MASTER_PASSWORD= 'passwd', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=154

On server B (192.168.8.131)

Change master to MASTER_HOST='192.168.8.129',MASTER_USER='repl', MASTER_PASSWORD= 'passwd', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=154

Restart slave

Start slave

8. View slave status

Show slave status\ G

The correct result is shown in the following figure:

What may happen:

(1) when the configuration of Slave_IO_Running and Slave_SQL_Running is not all YES, there is an error message in show slave status\ G message, which can be corrected according to the error prompt.

(2) when Slave_IO_Running and Slave_SQL_Running are not all YES, most of the problems are caused by data inconsistency.

(3) after stop slave, the data is changed, and then start slave, error. Just execute the CHANGE MASTER again.

(4) if error 2003 as shown in the following figure occurs and the connection fails, the bind_address in mysqld.cnf has not been modified to 0.0.0.0. If you want mysql to listen to other addresses, you can comment out bind-address = 127.0.0.1 or bind-address = 0.0.0.0 to listen on all addresses.

(5) if error 1045 as shown in the following figure occurs, the password of the repl user entered when configuring change maseter is incorrect.

9. Test whether the synchronization is successful

On A

Create table test_user (id int NOT NULL AUTO_INCREMENT PRIMARY KEY,createtime datetime NOT NULL DEFAULT CURRENT_TIMESTAMP)

You can see that a new table has been added on B.

On B

Insert into test_user set id=1

On A, you can see that a new record has been added to the test_ user table.

Refer to the website https://www.cnblogs.com/zping/p/5315827.html

Https://www.cnblogs.com/phpstudy2015-6/p/6485819.html#_label7

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.