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

1. Preparatory work

(permanent address of this article: http://woymk.blog.51cto.com/10000269/1922786)

Configuration environment: centos6.9,mysql5.7

Install and configure two MySQL servers first

Server1 IP:192.168.1.1

Server2 IP:192.168.1.2

For mysql installation, please refer to the previous article:

LAMP environment building (centos6.9+apache2.4+mysql5.7+php7.1)

two。 Operate on server1

Vi / etc/my.cnf

Modify or add the following lines:

Server-id=1

Log-bin=mysql-bin # enable binary logging

Auto-increment-increment = 2 # increase 2 at a time

Auto-increment-offset = 1 # set the offset of the automatically growing field

Two optional parameters (2 to choose 1):

Binlog-do-db=db1,db2 # libraries to be synchronized

Binlog-ignore-db=db1,db2 # ignore out-of-sync libraries

Restart after saving

/ etc/init.d/mysqld restart

Run the mysql client

Mysql-uroot-p

Create a synchronization account

Grant replication slave on *. * to 'repl2'@'192.168.1.2' identified by' 1234'

Flush privileges

Lock the table to prevent data from being written

Flush tables with read lock

Show master status

Mysql > show master status

+-+

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

+-+

| | mysql-bin.000001 | 613 | | mysql |

+-+

1 row in set (0.00 sec)

Record the binary log file name and location

Backup database

Execute on mysql1

Mysqldump-uroot-p db1 > back.sql

Copy the backed-up data to the slave server

Scp back.sql 192.168.1.2 purl ~

3. Operating in server2

Vi / etc/my.cnf

To modify or add:

Server-id=2 # this number cannot be the same as the master

Log-bin=mysql-bin # enable binary logging

Auto-increment-increment = 2 # increase 2 at a time

Auto-increment-offset = 2 # set the offset of the automatically growing field

Optional parameters (2 choose 1, these two parameters are set to the same as the master):

Replicate-do-db=db1,db2

Replicate-ignore-db=db1,db2

Restart after saving

/ etc/init.d/mysqld restart

Import data backed up on server1 into server2

Execute on server2

[root@server2] # mysqldump-uroot-p db1

< back.sql 运行mysql客户端 mysql -uroot -p 创建同步账号 grant replication slave on *.* to 'repl1'@'192.168.1.1' identified by '1234'; flush privileges; show master status; mysql>

Show master status

+-+

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

+-+

| | mysql-bin.000001 | 613 | | mysql |

+-+

1 row in set (0.00 sec)

Record the binary log file name and location

Execute the following command

Stop slave

Change master to master_host='192.168.1.1',master_user='repl2',master_password='1234',master_log_file='mysql-bin.000001',master_log_pos=613

(master_log_file and master_log_pos fill in the binary log file name and location just recorded)

Start slave

View the status of the slave server:

Show slave status\ G

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.1

Master_User: repl2

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 613

Relay_Log_File: server2-relay-bin.000002

Relay_Log_Pos: 320

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

4. Go back to the server1 operation

Run the mysql client

Mysql-uroot-p

Unlock the database on server1

Mysql > unlock tables

Execute the following command

Stop slave

Change master to master_host='192.168.1.2',master_user='repl1',master_password='1234',master_log_file='mysql-bin.000001',master_log_pos=613

(master_log_file and master_log_pos fill in the binary log file name and location just recorded)

Start slave

View the status of the slave server:

Show slave status\ G

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.2

Master_User: repl1

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 613

Relay_Log_File: server1-relay-bin.000002

Relay_Log_Pos: 320

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

5. Master master replication test

After testing, the master replication configuration is successful.

For a brief description of the specific process, please refer to the method in the previous mysql5.7 master-slave configuration.

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.