Steps and precautions for configuring MySQL Replication

This article mainly describes the correct operation method of MySQL replication settings. I believe that if you master the MySQL replication settings technology, it will be of great help in your future studies or work, the following is a description of the specific solution. I hope it will help you in your future study.

MySQL master-slave backup

MySQL automatic online backup

MySQL master-slave in windows

Recommendation circle: Database circle

More related recommendations 1. Stop the MySQL Service

2. Copy all MySQL files, including data files

3. The my. ini of the master does not need to be modified. The master mode has been configured by default:

 
 

  
  
log-bin=MySQL-bin   
  
  
binlog_format=mixed   
  
  
server-id = 1  
 
 

4. modify my. ini of slave as follows:

Remove the master configuration in step 3 and add the following content:

 
 

  
  
server-id = 2   
  
  
master-host = localhost   
  
  
master-user = replication   
  
  
master-password = replication   
  
  
master-port = 3306  
 
 

Replicate-do-db = database to be copied

 
 

  
  
master-connect-retry=10  
 
 

Modify the port to 3307,

Add a replication user for replication.

5. Restart the master and slave databases.

6. If the master is successfully set, run the show master status Command to view the following information:

 
 

  
  
+------------------+----------+--------------+------------------+   
  
  
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |   
  
  
+------------------+----------+--------------+------------------+   
  
  
| MySQL-bin.000007 | 39259 | | |   
  
  
+------------------+----------+--------------+------------------+   
  
  
1 row in set (0.00 sec)  
 
 

7. If all MySQL instances are successfully copied, run the show slave status command in slave. The corresponding configuration parameters are displayed, indicating that both the master and slave instances are set properly.

8. If you want TO use the change master to command TO modify the corresponding parameters, you must first use the stop slave command TO stop the slave. After the modification is successful, run start slave and start the slave service.

Note: It is most convenient to stop the MySQL service directly without the need to lock the table.