Mysql master-slave copy operation notes
The premise is that the data in the mysql database on the master-slave server should be completely consistent before it can be implemented. If there is only one master mysql before, the data should be backed up and restored to the slave mysql to ensure the consistency of the data. If necessary, you may have to stop the business before doing so.
1. First create a copy user to pull the log from the mysql from the main mysql.
Create user copy identified by '123456'
two。 Authorize copy users
Grant replication slave on. To 'copy'@'%' identified by' 123456'
3. Configure the configuration file of the master-slave mysql
Master: [mysqld]
Server-id = 10 # globally unique ID
Log-bin = / data/mysql/binlog/mysql-bin # binary file save path
From: [mysqld]
Server-id = 20 # globally unique ID
4. 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)
5. Manually synchronize the master data to the slave server and ensure that the new database can be backed up directly. If there is already a large amount of data, it is best to add a read lock or choose a time period when there is little business.
You can use mysqlpump,mysqldump,xtrabackup.sqlyog.
Here we use sqlyog under the Windows environment:
a. Select the database to be backed up, right-click > backup / export > backup database, dump to sql file > Save
b. In the target database: create a new database, right-click > Import > Select the sql file you just backed up > confirm.
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. test
a. Create a new database and a new user in the main mysql:
Mysql > create database JBB
Mysql > create user JBB
b. Query from Mysql to see if the JBB database and JBB users have been synchronized:
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)
You can see that the synchronization of the main library can be achieved on the slave.
