Sometimes we have configured MySQL master-slave replication, but there are new projects and databases coming in. We also hope that this data will allow mysql to achieve master-slave replication, next, we will introduce how to add a new database to the MySQL master-slave copy list.
MySQL master-slave replication generally sets the database to be synchronized, using the parameter configuration option, binlog-do-db, you can specify the database to be synchronized on the master, replicate-do-db specifies the database to be synchronized from the data perspective. (Generally, you only need to set binlog-do-db on the master, and do not need to set both. Just in case, replicate-ignore-db can be added to the slave ).
The problem we encountered was that we added a database to the master database. How can we add the newly added database to the master-slave replication chain of MySQL? (That is, master-slave replication is reset without re-copying the entire database ).
First, let's take a rough look at the basic steps of master-slave replication (MySQL master-slave must be configured on their respective servers first ).
1. Copy the database.
The Code is as follows: |
Copy code |
Mysqldump -- master-data -- single-transaction-R -- databases [db_name] | gzip-9-| PVS> all-db-with-master-data. SQL .gz |
Note: innodb uses-single-transaction, and myisam uses-lock-all-tables.
2. Copy and import data.
The Code is as follows: |
Copy code |
Pv <all-db-with-master-data. SQL .gz | zcat | mysql |
3. Start the slave database.
Slave start note: the switch to the master statement is already in the exported SQL statement. Check it carefully. Change master to master_log_file = '(binlog name in relay_master_log_file)', master_log_pos = (exec_master_log_pos number ).
In the existing Master-slave replication structure, how do I add a new database? For example, we want to add a database on the master server, for example, a database named newdb.
The procedure is as follows:
1. Stop the slave database from the service.
The Code is as follows: |
Copy code |
Stop slave; |
2. Export the new database on the master server.
The Code is as follows: |
Copy code |
Mysqldump -- master-data -- single-transaction-R -- databases newdb> newdb. SQL |
3. On the master server, modify the my. cnf file, add the new database to the binlog-do-db parameter, and restart mysql.
4. Find the current log file and location (change master to…) in the exported newdb. SQL ...)
Then let the slave server execute to this location.
The Code is as follows: |
Copy code |
Start slave until MASTER_LOG_FILE = "mysql-bin.000001", MASTER_LOG_POS = 1222220; |
MASTER_LOG_FILE
And MASTER_LOG_POS at the top of the exported database newdb. SQL.
4. Import the new database to the slave server.
The Code is as follows: |
Copy code |
Mysql <newdb. sql5. start slave |
The most important part is the log position (position A) when the new database is exported on the master server. This point is very important. Use this point as the demarcation line to import the new database.
This method also applies to the case where a database or a data table is not synchronized. For example, if a table in the Master/Slave database is inconsistent for some reason, the above method only needs to be removed from restarting the database, other operations