Add new database to MySQL master copy list

Source: Internet
Author: User

MySQL master-slave replication in general, we will set the database that needs to be synchronized, using the parameter configuration option, BINLOG-DO-DB, you can specify the database to be synchronized on master, and replicate-do-db specify the database to be synchronized from the data. (generally only set master on the binlog-do-db can, do not need two simultaneous settings.) In case of slave can also be added replicate-ignore-db).
The problem we're having is that a new database is added to master, so how do you add this database to MySQL's master-slave replication chain? (To reset master-slave replication without copying the entire library.)


First of all, we will outline the basic steps of master-slave copying, (MySQL master and slave first need to be configured in their respective servers).

1. Copy the database.

The code is as follows Copy Code

mysqldump--master-data--single-transaction-r--databases [db_name] | Gzip-9-| PV > all-db-with-master-data.sql.gz

Note: InnoDB with –single-transaction, MyISAM need to use –lock-all-tables.

2. Copy, 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 main statement is already inside the exported SQL statement, and look at it . Change Master to master_log_file= ' (binlog name in Relay_master_log_file) ', master_log_pos= (exec_master_log_pos number).

So, in the existing master-slave replication structure, how to add a new database to go in? For example, we want to add a database on the master server, for example, a database named newdb.
The specific actions are as follows:

1. From the service, stop the slave database.

The code is as follows Copy Code

Stop slave;

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

The code is as follows Copy Code

Mysqldump--master-data--single-transaction-r--databases newdb > Newdb.sql

3. On the main server, modify MY.CNF file, add new library to binlog-do-db parameter, restart MySQL.

4. Find the current log file and location in the exported newdb.sql (change Master to ...)
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;

of which Master_log_file

And Master_log_pos is located at the top of the exported database Newdb.sql.

4. Import the new library to the server.

The code is as follows Copy Code

MySQL < Newdb.sql5. Start slave

One of the more important is the log location (position A) when the new library is exported on the primary server, which is important, with this point as the dividing line to import the new library.

This method also applies to a database or a data table is not synchronized, such as the master-slave database has a table for some reason data inconsistency, then the above method only need to remove the restart database one step, the other operation of the basic

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.