replicate-rewrite-db

时间：2022-06-23 09:13:49

replicate-rewrite-db：

  1. Tells the slave to translate the default database (that is, the one selected by USE) to to_name if it was from_name on the master.Only statements involving tables are affected (not statements such as CREATE DATABASE, DROP DATABASE, and ALTER DATABASE), and only if from_name is the default database on the master.This does not work for cross-database updates.To specify multiple rewrites, use this option multiple times.The server uses the first one with a from_name value that matches.The database name translation is done before the --replicate-* rules are tested.
  2. If you use this option on the command line and the “>” character is special to your command interpreter, quote the option value.For example:
  3. shell> mysqld --replicate-rewrite-db="olddb->newdb"
比如，master与slave同步fandb这个库，但因为需要slave需要将fandb改名为dudb，master不能改，就需要这个参数
在Slave端的my.cnf中加入
replicate-rewrite-db=fandb->dudb

重启Slave库，然后首先要创建dudb这个库

加入Master端执行
  1. (mysql@localhost)[fandb]> create table rewrite asselect*from ab;
如果Slave没有dudb.ab这个表，就会报错
  1. [ERROR]Slave SQL:Error'Table 'dudb.ab' doesn't exist' on query. Default database: 'dudb'. Query: 'create table rewrite as select * from ab', Error_code: 1146
  2. 2015-07-10 09:44:58 48962 [Warning] Slave: Table 'dudb.ab' doesn't exist Error_code:1146
  3. 2015-07-1009:44:5848962[ERROR]Error running query, slave SQL thread aborted.Fix the problem, and restart the slave SQL thread with "SLAVE START".We stopped at log 'mysql-bin.000003' position 3973
然后sql_thread也会被停止
  1. mysql> show slave status\G
  2. ***************************1. row ***************************
  3. Slave_IO_State:Waitingfor master to send event
  4. Master_Host:192.168.134.132
  5. Master_User: repl
  6. Master_Port:3306
  7. Connect_Retry:60
  8. Master_Log_File: mysql-bin.000003
  9. Read_Master_Log_Pos:4089
  10. Relay_Log_File: mysql-relay-bin.000002
  11. Relay_Log_Pos:283
  12. Relay_Master_Log_File: mysql-bin.000003
  13. Slave_IO_Running:Yes
  14. Slave_SQL_Running:No
这时需要手动create table dudb.ab as select * from fandb.ab
然后在start slave sql_thread
如果有多个库需要换名字，那么需要多次指定这个参数 
replicate-rewrite-db=fandb->dudb
replicate-rewrite-db=a->b

相关文章