MySQL builds a replication environment with filtering

MySQL builds a replication environment with filtering

The Organization is preparing to launch some new functions. These functions have nothing to do with the functions of the original production database module and are ready to create a new database for use.
However, you need to query tables in the original production database.
Later, the solution was to create a new database and use the tables required for copying from the original production database for association query.

The simulation is as follows:
Master simulates the production database. Slave is the newly created database, and only copies Table A from the Master server.
 
1. Master initialization data, simulating a database running for a period of time on the line
 
Create database mvbox;
 
Use mvbox;

Create table a as select * from mysql. user;

Create table B as select * from mysql. db;
2. Create a replication account on the Master node.
 
Grant replication slave, replication client on *.*
 
To xx @ '%' identified by 'xx ';
3. Modify the Slave configuration file
Replicate-do-table = mvbox.
 
4. Create the target database mvbox with Slave
Create database mvbox;
 
5. Server Load balancer configuration replication Information
 
Change master
 
Master_host = '1970. 168.56.1 ',

Master_port = 3306,

Master_user = 'XX ',

Master_password = 'xx ';
 
6. import data from the Master node to the Server Load balancer (executed on the server Load balancer)
 
Mysqldump-uxx-pxx-h192.168.56.1 -- single-transaction -- master-data mvbox a | mysql-uroot-Dmvbox
Principle:
Before this process is executed, view the binlog location of the Master.

Assume that the content of the Master SQL file is generated on Slave.
Mysqldump-uxx-pxx-h192.168.56.1 -- single-transaction -- master-data mvbox a> a. SQL
The generated content includes the binlog information of the Master server.

 

After mysqldump is applied
Check the replication status of Slave.

7. Start replication on Slave
Start slave;

-------------------------------------- Split line --------------------------------------

Install MySQL in Ubuntu 14.04

MySQL authoritative guide (original book version 2nd) Clear Chinese scan PDF

Ubuntu 14.04 LTS install LNMP Nginx \ PHP5 (PHP-FPM) \ MySQL

Build a MySQL Master/Slave server in Ubuntu 14.04

Build a highly available distributed MySQL cluster using Ubuntu 12.04 LTS

Install MySQL5.6 and Python-MySQLdb in the source code of Ubuntu 12.04

MySQL-5.5.38 universal binary Installation

-------------------------------------- Split line --------------------------------------

This article permanently updates the link address: