Mysql master-slave copy based on log point (real)

Implementing the Environment:
Master master: 192.168.100.165 (Mysql 5.6.36)
Slave from: 192.168.100.156 (Mysql 5.6.36)

Steps
1. Create a replication account on the master DB server (performed on master)
# MySQL
mysql> CREATE USER ' rpl ' @ ' 192.168.100.% ' identified by ' 123456 ';
Mysql> GRANT REPLICATION SLAVE on * * to ' repl ' @ ' 192.168.100.% ';

2. Primary server configuration (performed on master)
# VIM/ETC/MY.CNF
[Mysqld]
Basedir =/usr/local/mysql
DataDir =/data/mysql
Port = 3306

# Binary Logging #
Log-bin =/data/mysql/log-bin #启动二进制日志

# replice #
server_id = 165 #指定服务ID

3. From the server configuration (performed on slave)
# VIM/ETC/MY.CNF
[Mysqld]
Basedir =/usr/local/mysql
DataDir =/data/mysql
Port = 3306

# Binary Logging #
Log-bin =/data/mysql/mysql-bin #启动二进制日志

# replice #
Server-id = 156 #指定服务ID
Relay_log =/data/mysql/relay-bin #启动中继日志
#log_slave_update = on #在中继日志执行后写入到二进制日志中
#read_only = on #开启只读模式

4. Restart master MySQL (performed on master)
# Service Mysqld Restart
# #添加一些测试数据用于验证结果
# MySQL
mysql> CREATE DATABASE CHEN;
mysql> use CHEN;
Mysql> CREATE TABLE T1 (ID INT);
Mysql> INSERT into T1 VALUES (1), (2), (3);
Mysql> \q

5. Restart from MySQL (performed on slave)
# service mysqld restart

6. Back up the primary database (can be fully prepared if the database version is the same as the Master and Slave) (execute on Master)
# Mysqldump--single-transaction--master-data--triggers--routines--all-databases >>/root/165_full.sql
# # Record the values of Master_log_file and master_log_pos;
# more/root/165_full.sql #找到这样一句话: Change MASTER to master_log_file= ' log-bin.000003 ', master_log_pos=520;

7. Transfer the primary database backup to the slave database (executed on master)
# Scp/root/165_full.sql [email protected]:/root

8. Import the backup of the primary database into the slave database (executed on slave)
# Mysql-uroot-p </root/165_full.sql

9. Initial replication link (performed on slave)
# MySQL
mysql> Change Master to master_host= ' 192.168.100.165 ',
Master_user= ' Repl ',
-master_password= ' 123456 ',
-master_log_file= ' log-bin.000003 ' #从备份中查找
master_log_pos=520, #从备份中查找

10. Start when check results
mysql> start slave; (performed on slave)

Mysql> Show slave Status \g (performed on slave) process started
Mysql> show Processlist; Two system processes (performed on slave)
Mysql> show Processlist; (performed on master) a system is

Mysql> SELECT * from ' Chen '. T1; (performed on master) search results are consistent
Mysql> SELECT * from ' Chen '. T1; (performed on slave) the search results are consistent

mysql> INSERT into ' Chen '. T1 VALUES (4); (performed on Master)

Mysql> SELECT * from ' Chen '. T1; (performed on master) search results are consistent
Mysql> SELECT * from ' Chen '. T1; (performed on slave) the search results are consistent

If there's no problem, then OK.

Mysql master-slave copy based on log point (real)