mysql5.7 Master-Slave synchronization

One: Make Bin-log log configuration

1 Setting the Bin-log log

[Mysqld]

log-bin=//path #<=== red Bold omitted

Server-id = 1 #主库

2. Check the configuration login database

Show variables like "%log_bin%"

Show variables like "%server%"

3. Create an account and give permissions

Create user ' Tom ' @ ' 111.111.111.111 ' identified by ' password '

Grant Replication Slave on * * to [email protected] ' 111.111.111.111 ';

4. Refresh Permissions:

Flush privileges;

II: Guide The operation of the library:

Request a lock list (test environment does not have to do this step)

1. Flush tables with read lock;

2. Get location point: View status

   Show master status;

3. Export Database

   Mysqldump-uroot-p123456-b-A >/tmp/new.sql

   --all-databases,-A

   --databases ,-B

4. Send data

   SCP All.sql [Email protected]:/tmp

5. Unlock table; Unlock (test environment does not have to do this step)

Three: (from the library my.cnf configuration)

1. Whether to open Bin-log

Scenario 1:

Cascade Synchronization A->b->c The Middle B will begin to Log-bin.

Scenario 2:

All-in-one data backup from the library, the database backup must have full standby and Binlog log, is the full backup

2.server-id=3 #server-id Don't be like the main vault.

3. Reboot from Library

4. View status

Show variables like "%log_bin%"

Show variables like "%server%"

Four: Recovering from a library

mysql-uroot-p123456 </tmp/new.sql

V: Tell from which point the library is backed up

Change Master to master_host= ' master address ',

Master_user= ' Tom ',

master_password= ' Password ',

Master_log_file= ' mysql-bin.000002 ', #根据 show master status;

master_log_pos=3663; #根据 Show master status;

VI: Perform the sync switch

From library:

1. Start slave;

2. View status

   show slave status;

Normal state:

Slave_io_state:waiting for Master to send event

Master_host:master Host IP

Master_user:tom

master_port:3306

Connect_retry:60

master_log_file:mysql-bin.000002

Slave_io_running:yes

Slave_sql_running:yes

mysql5.7 Master-Slave synchronization