How to add a new database to the MySQL master-slave copy list

In the LAMP architecture, we generally use MySQL as a database, and MySQL master-slave is also a necessary part of the high-performance website architecture (such as drupal, Wordpress and other architecture websites ). This article describes master-slave MySQL replication and problems. MySQL master-slave replication generally sets the database to be synchronized and selects the database using the parameter configuration.

In the LAMP architecture, we generally use MySQL as a database, and MySQL master-slave is also a necessary part of the high-performance website architecture (such as drupal, Wordpress and other architecture websites ). This article describes master-slave MySQL replication and problems. MySQL master-slave replication generally sets the database to be synchronized and selects the database using the parameter configuration.

In the LAMP architecture, we generally use MySQL as a database, and MySQL master-slave is also a necessary part of the high-performance website architecture (such as drupal, Wordpress and other architecture websites ). This article describes master-slave MySQL replication and problems.

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.

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

Note: innodb uses-single-transaction, and myisam uses-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 switch to the primary statement is already in the exported SQL statement. Check it. 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.

stop slave;

2. Export the new database on the master server.

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.

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

MASTER_LOG_FILE and MASTER_LOG_POS are located at the top of the exported database newdb. SQL.

4. Import the new database to the slave server.

mysql < newdb.sql

5. 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

Original article address: how to add a new database to the MySQL master-slave copy list, thanks to the original author for sharing.