我有一台带有 2 个数据库的服务器,我想将多个表从一个数据库复制到另一个数据库。目的是我们使用项目中使用的相同用户表。
正如在另一个表中使用 InnoDB 和用户表的外键一样,我选择了一种复制方式。
为此我对 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
同步表后binlog-do-db
并启动从属 error.log 下一行出现:
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
看来这一步一切正常,并且show slave status
显示没有错误。
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
问题是主设备的变化不会影响从设备,但从设备状态的变化会影响。
感谢您为解决该问题提供的任何帮助。