从同一mysql服务器的不同数据库复制表 [英] Replicate tables from different database of same mysql server
问题描述
我有一台服务器上有2个数据库，我想将多个表从一个数据库复制到另一个数据库.目的是我们使用与项目中使用的相同的用户表.
I have one server with 2 databases, and i want to replicate several tables from one database to another. Purpose is that we uses same user's table that used in projects.
在其他人的表中，InnoDB与用户表的外键一起使用，我选择了一种复制方式.
As in anothers tables used InnoDB with foreign keys to users table i've chosen a replication way.
为此，我对my.cnf进行了更改
For that I made the 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
从binlog-do-db同步表并启动从属错误后，出现以下行日志:
after syncing tables from binlog-do-db and starting slave error.log next lines appears:
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没有显示错误.
Seems that on this step everything is ok, 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 thing is that master changes does not affect slave but slave status changes.
感谢您为解决该问题提供的帮助.
Thanks for any help in solve that problem.
推荐答案
出于兴趣，视图会做什么?
Out of interest would a view do what you want?
我真的不认为在mysql的同一实例上运行复制到同一实例是一个好主意.
I really don't think running replication on the same instance of mysql into to the same instance is a good idea.
您可能想调查服务器硬件是否有问题的另一种方法是，在同一台计算机上的多个端口上运行mysql的多个实例，这可能有助于您实现所需的功能.这是我在测试环境中用于模拟主数据库故障和从数据库升级的东西.
The other option you might want to investigate if server hardware is a problem, would to run multiple instances of mysql running off different ports on the same machine, which might help you achieve what you are looking for. This is something i am using in a test environment for simulated master DB failure and slave DB promotion.
这篇关于从同一mysql服务器的不同数据库复制表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
