MySQL - A detailed introduction on how to add a new database to the MySQL master-slave replication list

MySQL - A detailed introduction on how to add a new database to the MySQL master-slave replication list

MySQL master-slave replication Normally we will set up the database that needs to be synchronized, using The parameter configuration option, binlog-do-db, can specify the database that needs to be synchronized on the master, and replicate-do-db specifies the database that needs to be synchronized from the data point of view. (Generally, only binlog-do-db on the master is set, and there is no need to set both at the same time. Just in case, you can also add replicate-ignore-db on the slave).

Today, the problem I encountered was that a new database was added to the master. How to add the newly added database to the master-slave replication chain of MySQL? (That is, reset the master-slave replication without re-replicating the entire library).

First, let’s enumerate the basic steps of master-slave replication (MySQL master-slave needs to be configured on their respective servers first).

1. Copy the database

mysqldump --master-data --single-transaction -R --databases [db_name] | gzip -9 - | pv > all-db-with-master-data.sql.gz

Note: innodb uses –single-transaction, myisam needs to use –lock-all-tables.

2. Copy and import data

pv < all-db-with-master-data.sql.gz | zcat | mysql

3. Start the slave database.

slave start

Note: The statement to switch to the main is already in the exported sql statement, please check it carefully. change master to master_log_file=’(binlog name in relay_master_log_file)’, master_log_pos=(exec_master_log_pos number).
So, how to add a new database to the existing master-slave replication structure? For example, we want to add a database on the master server, for example, a database named newdb. The specific operations are as follows:

1. From the service, stop the slave database.

stop slave;

2. On the main server, export the new database.

mysqldump --master-data --single-transaction -R --databases newdb > newdb.sql

3. Modify the my.cnf file on the main server

On the main server, modify the my.cnf file, add the new library to the binlog-do-db parameter, and restart mysql.

4. Find the current log file and location

Find the current log file and location in the exported newdb.sql (change master to...)
Then let slave The server executes to this location.

start slave until MASTER_LOG_FILE="mysql-bin.000001", MASTER_LOG_POS=1222220;

Among them, MASTER_LOG_FILE and MASTER_LOG_POS are searched at the top of the exported database newdb.sql.

5. Import the new library to the slave server.

mysql < newdb.sql

6. Start the slave server

start slave

The more important thing is the log position (position A) when exporting the new library on the master server. This point is very important. Take this point as Demarcation line, import new library.
This method is also applicable to situations where a certain database or a certain data table is out of sync. For example, if a table in the master-slave database has inconsistent data due to some reasons, then the above method only needs to remove the step of restarting the database, and the other steps The operations are basically the same

#

The above is the detailed content of MySQL - A detailed introduction on how to add a new database to the MySQL master-slave replication list. For more information, please follow other related articles on the PHP Chinese website!