MySQL master-slave copy operation notes

On the premise that the data in the MySQL database on the master and slave server should be fully consistent before it can be implemented. If there was only one master MySQL, then the data should be backed up, restored to the MySQL, the data is consistent, and if necessary, the business should be stopped.

1. First create a copy user to use to pull the log from MySQL from the main MySQL.
Create user copy identified by ' 123456 ';

2. Authorization to copy user;
Grant replication Slave on . to ' copy ' @ ' percent ' identified by ' 123456 ';

3. Manually synchronize the master data to the slave server and ensure the unification;
You can use Mysqlpump,mysqldump,xtrabackup. (Make up the steps another day)

4. Configure the master-slave mysql configuration file
Main: [Mysqld]
Server-id = ten #全局唯一 ID
Log-bin =/data/mysql/binlog/mysql-bin #二进制文件保存路径
From: [Mysqld]
Server-id = #全局唯一 ID

5. Query and record the position value of the main MySQL
Mysql> Show master Status \g;
+----------------+----------+--------------+------------------+-------------------+
| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |
+----------------+----------+--------------+------------------+-------------------+
| bin-log.000001 | 154 | | | |
+----------------+----------+--------------+------------------+-------------------+
1 row in Set (0.00 sec)

6.mysql> Change Master to master_host= ' 192.168.61.130 ', master_port=3306, master_user= ' copy ', master_password= ' 123456 ', master_log_file= ' bin-log.000001 ', master_log_pos=154,master_connect_retry=30;

7. Testing
A. Create a new database and create a new user in main MySQL:
Mysql>create database jbb;
Mysql>create user jbb;
B. In the query from MySQL, see if the JBB database and JBB users have synchronized in place:
mysql> show databases;
+-----------+
| user |
+-----------+
| Jbb |
| Mysql.sys |
| Root |
+-----------+
3 Rows in Set (0.00 sec)

Mysql> Select User from Mysql.user;
+-----------+
| user |
+-----------+
| Jbb |
| Mysql.sys |
| Root |
+-----------+
3 Rows in Set (0.00 sec)

可以看到slave上已经可以实现主库的同步.

MySQL master-slave copy operation notes