Replicating tables from different databases of the same mysql server

Question

I have one server with two databases, and I want to replicate several tables from one database to another. The goal is that we use the same user table as in the projects.

As with other tables used by InnoDB with foreign keys for the user table, I chose the replication method.

For this, I made changes for my.cnf

master-user=root server-id = 2 replicate-rewrite-db = dou->jobs replicate-do-table = jobs.auth\_user replicate-wild-do-table = jobs.geo\_% replicate-do-table = jobs.user\_profile replicate-same-server-id = 1 report-host = master-is-slave binlog-do-db = dou log-bin 

after synchronizing the tables from binlog-do-db and running slave error.log, the following lines appear:

 111112 15:10:22 [Note] 'CHANGE MASTER TO executed'. Previous state master_host='localhost', master_port='3306', master_log_file='', master_log_pos='4'. New state master_host='localhost', master_port='3306', master_log_file='mysql-bin.000074', master_log_pos='106'. 111112 15:10:36 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000074' at position 106, relay log '/var/log/mysql/dell-relay-bin.000001' position: 4 111112 15:10:36 [Note] Slave I/O thread: connected to master ' root@localhost :3306',replication started in log 'mysql-bin.000074' at position 106 

Everything seems to be fine at this step, and show slave status shows no errors.

 mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: localhost Master_User: root Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000074 Read_Master_Log_Pos: 814 Relay_Log_File: dell-relay-bin.000002 Relay_Log_Pos: 959 Relay_Master_Log_File: mysql-bin.000074 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: jobs.user\_profile,jobs.auth\_user Replicate_Ignore_Table: Replicate_Wild_Do_Table: jobs.geo\_% Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 814 Relay_Log_Space: 1113 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: 1 row in set (0.00 sec) ERROR: No query specified 

The fact is that master changes do not affect slave, but subordinate status changes.

Thanks for the help in solving this problem.

Answer 1

I will not tell you that replicating a table to the same database is a bad idea, the reason has increased several times by IO.

The slave is not updated because server-id is the same for both the master and the slave. Typically, the slave ignores updates with the same server identifier as it.

Add replicate-same-server-id to my.cnf. documentation replicating the same server

Answer 2

What will you want out of interest?

I really don't think that starting replication in the same mysql instance to the same instance is a good idea.

Another option that you might want to explore if server hardware is a problem would be to run multiple instances of mysql running on different ports on the same computer, which can help you achieve what you are looking for. This is what I use in the test environment to simulate the failure of the main database and the promotion of the slave database.

Answer 3

There are many situations where you can achieve more optimizations, for example. DB1 is replicated to DB2 (same server). Where does DB2 only store data for 1 week. All data older than 1 week is deleted (On db2). in such a setting, for example, on a server with high traffic traffic, where it is necessary so that it is as clean as possible. Setting up dual db on the same server uses "less" resources from the server if most of the time your server reads data from the database. I have this setup, but I use 4 different servers. server 1) 3 days. server 2) 30 days. server 3) 2 months, and server 4 - all data from the beginning. (Server 4 is mainly used to use very old registers, which are not so heavily used. Sorry for my English, but I think I made a conclusion about where you CAN and should use db replication on the same server. to reduce memory usage and CPU usage.

Answer 4

As pointed out by @mente, server_is cannot be the same.

One solution is to start another server and configure them to replicate the required database. I would suggest switching to the Galera cluster as it follows synchronous replication and multi-wizard topology. In this regard, there will be no delay in replication between nodes and many other benefits.

Another solution is to synchronize databases by running synchronization jobs at midnight (when there is no active development). You can also try SQLyog with a graphical interface to schedule a synchronization job.