MySQL master-slave synchronization and delayed synchronization

MySQL synchronization and delay synchronization

MySQL Sync

Operation on a MySQL master server

1 Turn on the Log_bin feature on the server

# VIM/ETC/MY.CNF

Add two lines

Log_bin=mysql-bin

Server_id=1

2 restarting MySQL

# service Mysqldrestart

3 log in to MySQL to create an account and authorize the slave feature

#mysql –uroot–p

mysql> grant replication Slave on * * to ' repl ' @ ' percent ' identified by ' repl ';

mysql> flush Privileges;

4 using the mysqldump command to back up the MySQL server database

# ln-s/data/mysql/bin/mysqldump/usr/bin/

#mysqldump-uroot-p--single-transaction--all-databases--master-data =1 >/data/mysql/data/mysqlbackup20170727.s Ql

5 transferring the SQL files backed up by the primary library server to the directory specified from the library

#scp/data/mysql/data/mysqlbackup20170727.sql [email protected]:/data/mysql/data

Two MySQL operations from the library server

1 log on MySQL server stop slave sync operation

#mysql –uroot–p

mysql> stop Slave;

2 Modifying the configuration file from the library

#vim/etc/my.cnf

Add the following two lines

Log_bin=mysql-bin

server_id=2

3 restarting the MySQL server

# Service Mysqld Restart

4 Import the backup file of the main library from library

#mysql –uroot–p </data/mysql/data/mysqlbackup20170727.sql

5 configuring synchronization from the server to the primary server

#head –n 30/data/mysql/data/mysqlbackup20170727.sql

The name and location of the MySQL main library binlog file are recorded in the backed up SQL file

As shown in the red box:

#mysql –uroot–p

Mysql>change Master tomaster_host= ' 10.44.30.153 ', master_user= ' replll ', master_password= ' mame33888888e333crqeqsnt8w ', master_log_file= ' mysql-bin.000065 ', master_log_pos=760539299;

Parameter explanation in the command:

Master_host the IP address where the MySQL master server resides

Master_user MySQL Master service authorizes users from the library slave feature

Master_password MySQL master server licensed from Library Salve feature user's password

Master_log_file MySQL Master service binlog name

Master_log_pos the location that is executed in the MySQL master service Binlog.

Note: Master_log_file and Master_log_pos are the corresponding values in the red box.

6 starting the slave from the server

mysql> start slave;

7 Checking the status of the slave server

Mysql>show slave status\g; As shown: The two items in the red box are yes to indicate that the master-slave synchronization was successful.

Second, MySQL delay synchronization

The aim is to prevent the misuse of the main library from being manipulated by the library.

#mysql –uroot–p

Mysql>stop slave;

Mysql>change MASTER to Master_delay = 1800; units in seconds

Mysql>start slave;

mysql>show slave status \g;

As shown: The meaning of the parameters in the red box:

Master_log_file Main Library MySQL binlog file name

Read_master_log_pos read the location of the Binlog file for the main library MySQL

Exec_master_log_pos the location of the Binlog file from the library to perform the main library MySQL

Seconds_behind_master Time unit seconds from library delay Master Library synchronization

Sql_delay Setting the delay synchronization time from the library server compared to the primary library server

MySQL master-slave synchronization and delayed synchronization