似乎是明天测试下 replicate-rewrite-do-db=master->slave/*要将主服务器的数据库同步到从服务器的指定数据库中*/ replicate-rewrite-do-table=slave.tablename1 /*指定需要同步的表,表名必须与主服务器的表名相同*/ replicate-rewrite-do-table=slave.tablename2 重新启动各从服务器。参考: replicate-rewrite-db="PROJ->_PROJ" replicate-do-db=_PROJ replicate-rewrite-db="mysql->_mysql" replicate-do-db=_mysqlHowever, this doesn't work because of the problem mentioned in http://dev.mysql.com/doc/refman/5.0/en/replication-options.html concerning replicate-do-db: it seems "grant" statements are considered "cross-database-updates".So I tried different combinations of replicate-do-db including replicate-wild-do-table=_%.% to no avail.So my question is: how can I configure this? Or should I simply set up a second mysql-server for replication only? http://dev.mysql.com/doc/refman/5.0/en/replication-options.html 先,选定192.168.0.1做服务器,命名为father; 然后,在服务器上创建好三个用户分别为 首先,选定192.168.0.1做服务器,命名为father; 然后,在服务器上创建好三个用户分别为 [email protected]; [email protected]; [email protected]; 并分配权限:GRANT REPLICATION SLAVE ON *.* TO [email protected] IDENTIFIED BY ‘son1’; GRANT REPLICATION SLAVE ON *.* TO [email protected] IDENTIFIED BY ‘son2’; GRANT REPLICATION SLAVE ON *.* TO [email protected] IDENTIFIED BY ‘son3’;接着,在主服务器上的配置文件/etc/my.ini中启用日志,指定要同步的数据库,步骤如下: server-id = 1 log-bin=mysql-bin/*启用二进制日志功能*/ binlog-do-db=MASTER/*这是我要备份的数据库名,如果你要备份多个数据库,重复设置这个选项即可*/ binlog-ignore-db=mysql/*这是我不需要备份的数据库名,如果不需要备份备份多个数据库,重复设置这个选项即可*/ binlog-ignore-db=test 重新启动数据库,使得设置生效。再次,制作数据库快照,并取得日志节点,步骤如下: FLUSH TABLES WITH READ LOCK;/*锁定数据库的所有表,防止数据库不修改*/ SHOW MASTER STATUS;/*得到日志节点,记录下来。*/ +--------------------------------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +--------------------------------+--------------+------------------+ | TCI-ERIC-bin.000032 | 106 | master | mysql,test | +--------------------------------+--------------+------------------+ 在服务器的命令终端下执行 mysqldump -uroot -p --skip-opt --single-transaction --add-drop-table --create-options --quick --extended-insert --set-charset --disable-keys demo> demo.sql /*创建一个完整的备份,这就是快照*/下面,将快照导出到三台从服务器上,并进行还原。 mysql slave< /tmp/master.sql /*把数据导入到从服务器上,对应的是slave数据库*/ 最后,在每台从服务器上面的etc/my.ini中如下设置: server-id=2(每个从服务器时依次设置不同的id号) master-host=192.168.0.1 master-user=son1(每个从服务器时依次设置自己的备份用户名字) master-password=son1(每个从服务器时依次设置自己的备份用户名字的秘密码) master-port=3306 replicate-rewrite-do-db=master->slave/*要将主服务器的数据库同步到从服务器的指定数据库中*/ replicate-rewrite-do-table=slave.tablename1 /*指定需要同步的表,表名必须与主服务器的表名相同*/ replicate-rewrite-do-table=slave.tablename2 重新启动各从服务器。 解除主服务器的锁,unlock tables;主从备份开始。这是主从单向备份,若要双向备份,只需在主从服务器上各自设置为对方的主服务器和从服务器即可。如下:在主服务器上增加以下设置,使其同时成为从服务器。 master-host=192.168.0.2 master-user=father master-password=father master-port=3306 在从服务器上增加以下设置,使其成为主服务器。 log-bin=mysql-bin/*启用二进制日志功能*/ binlog-do-db=SLAVE binlog-ignore-db=mysql binlog-ignore-db=test设置完毕。 http://www.pczpg.com/a/2010/0726/15525.html
实际测试发现错误 如何解决 ?2:33:13 mysqld started 110118 22:33:13 [Warning] The syntax for replication startup options is deprecated and will be removed in MySQL 5.2. Please use 'CHANGE MASTER' instead. 110118 22:33:13 [ERROR] /usr/sbin/mysqld: unknown variable 'replicate-rewrite-do-db=adb->bdb'110118 22:33:13 mysqld ended110118 22:33:39 mysqld started 110118 22:33:39 [Warning] The syntax for replication startup options is deprecated and will be removed in MySQL 5.2. Please use 'CHANGE MASTER' instead. 110118 22:33:39 [ERROR] /usr/sbin/mysqld: unknown variable 'replicate-rewrite-do-db=adb->bdb'110118 22:33:39 mysqld ended110118 22:35:13 mysqld started 110118 22:35:13 [Warning] The syntax for replication startup options is deprecated and will be removed in MySQL 5.2. Please use 'CHANGE MASTER' instead. 110118 22:35:13 [ERROR] /usr/sbin/mysqld: unknown variable 'replicate-rewrite-do-db=adb->bdb'110118 22:35:13 mysqld ended
replicate-rewrite-db=adb->bdb楼主可以试下把主上的复制过来之后再在从上设置: replicate-do-table=bdb.User replicate-do-table=bdb.passwd 45 mysqld started 110118 22:39:45 [Warning] The syntax for replication startup options is deprecated and will be removed in MySQL 5.2. Please use 'CHANGE MASTER' instead. 110118 22:39:45 [ERROR] /usr/sbin/mysqld: unknown variable 'replicate-rewrite-do-table=bdb.user'-----------------------按照楼上的 到表级别有错误110118 22:39:45 mysqld ended
replicate-rewrite-db=db1name ->db2name
把主服务器的db1name 复制到从服务器的db2name
从机器以前的配置是
replicate-do-table=adb.User
replicate-do-table=adb.passwd
replicate-do-table=adb.company
replicate-do-table=adb.unit现在开发人员已经定义了新库 bdb, 实在不方便把所有代码存储过程 脚本都更改库名。
所以准备用新库 bdb.
按照楼上的看法是否可以改成如下
replicate-do-table=bdb.User
replicate-do-table=bdb.passwd
replicate-do-table=bdb.company
replicate-do-table=bdb.unit
replicate-rewrite-do-table=adb.passwd->bdb.passwd
replicate-rewrite-do-table=adb.company->bdb.company
replicate-rewrite-do-table=adb.unit->bdb.unit
replicate-rewrite-do-db=master->slave/*要将主服务器的数据库同步到从服务器的指定数据库中*/ replicate-rewrite-do-table=slave.tablename1 /*指定需要同步的表,表名必须与主服务器的表名相同*/ replicate-rewrite-do-table=slave.tablename2 重新启动各从服务器。参考:
replicate-rewrite-db="PROJ->_PROJ" replicate-do-db=_PROJ replicate-rewrite-db="mysql->_mysql" replicate-do-db=_mysqlHowever, this doesn't work because of the problem mentioned in http://dev.mysql.com/doc/refman/5.0/en/replication-options.html concerning replicate-do-db: it seems "grant" statements are considered "cross-database-updates".So I tried different combinations of replicate-do-db including replicate-wild-do-table=_%.% to no avail.So my question is: how can I configure this? Or should I simply set up a second mysql-server for replication only?
http://dev.mysql.com/doc/refman/5.0/en/replication-options.html
先,选定192.168.0.1做服务器,命名为father; 然后,在服务器上创建好三个用户分别为
首先,选定192.168.0.1做服务器,命名为father; 然后,在服务器上创建好三个用户分别为 [email protected]; [email protected]; [email protected]; 并分配权限:GRANT REPLICATION SLAVE ON *.* TO [email protected] IDENTIFIED BY ‘son1’; GRANT REPLICATION SLAVE ON *.* TO [email protected] IDENTIFIED BY ‘son2’; GRANT REPLICATION SLAVE ON *.* TO [email protected] IDENTIFIED BY ‘son3’;接着,在主服务器上的配置文件/etc/my.ini中启用日志,指定要同步的数据库,步骤如下: server-id = 1
log-bin=mysql-bin/*启用二进制日志功能*/
binlog-do-db=MASTER/*这是我要备份的数据库名,如果你要备份多个数据库,重复设置这个选项即可*/
binlog-ignore-db=mysql/*这是我不需要备份的数据库名,如果不需要备份备份多个数据库,重复设置这个选项即可*/ binlog-ignore-db=test
重新启动数据库,使得设置生效。再次,制作数据库快照,并取得日志节点,步骤如下: FLUSH TABLES WITH READ LOCK;/*锁定数据库的所有表,防止数据库不修改*/ SHOW MASTER STATUS;/*得到日志节点,记录下来。*/ +--------------------------------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------------------+--------------+------------------+
| TCI-ERIC-bin.000032 | 106 | master | mysql,test |
+--------------------------------+--------------+------------------+ 在服务器的命令终端下执行 mysqldump -uroot -p --skip-opt --single-transaction --add-drop-table --create-options --quick --extended-insert --set-charset --disable-keys demo> demo.sql /*创建一个完整的备份,这就是快照*/下面,将快照导出到三台从服务器上,并进行还原。 mysql slave< /tmp/master.sql /*把数据导入到从服务器上,对应的是slave数据库*/
最后,在每台从服务器上面的etc/my.ini中如下设置: server-id=2(每个从服务器时依次设置不同的id号)
master-host=192.168.0.1
master-user=son1(每个从服务器时依次设置自己的备份用户名字)
master-password=son1(每个从服务器时依次设置自己的备份用户名字的秘密码) master-port=3306 replicate-rewrite-do-db=master->slave/*要将主服务器的数据库同步到从服务器的指定数据库中*/ replicate-rewrite-do-table=slave.tablename1 /*指定需要同步的表,表名必须与主服务器的表名相同*/ replicate-rewrite-do-table=slave.tablename2 重新启动各从服务器。
解除主服务器的锁,unlock tables;主从备份开始。这是主从单向备份,若要双向备份,只需在主从服务器上各自设置为对方的主服务器和从服务器即可。如下:在主服务器上增加以下设置,使其同时成为从服务器。
master-host=192.168.0.2
master-user=father
master-password=father master-port=3306
在从服务器上增加以下设置,使其成为主服务器。
log-bin=mysql-bin/*启用二进制日志功能*/
binlog-do-db=SLAVE binlog-ignore-db=mysql binlog-ignore-db=test设置完毕。
http://www.pczpg.com/a/2010/0726/15525.html
replicate-do-table=bdb.User
replicate-do-table=bdb.passwd
....这样过滤,我没测试过,不知道行不.楼主测试的话,记得分享结果
110118 22:33:13 [Warning] The syntax for replication startup options is deprecated and will be removed in MySQL 5.2. Please use 'CHANGE MASTER' instead.
110118 22:33:13 [ERROR] /usr/sbin/mysqld: unknown variable 'replicate-rewrite-do-db=adb->bdb'110118 22:33:13 mysqld ended110118 22:33:39 mysqld started
110118 22:33:39 [Warning] The syntax for replication startup options is deprecated and will be removed in MySQL 5.2. Please use 'CHANGE MASTER' instead.
110118 22:33:39 [ERROR] /usr/sbin/mysqld: unknown variable 'replicate-rewrite-do-db=adb->bdb'110118 22:33:39 mysqld ended110118 22:35:13 mysqld started
110118 22:35:13 [Warning] The syntax for replication startup options is deprecated and will be removed in MySQL 5.2. Please use 'CHANGE MASTER' instead.
110118 22:35:13 [ERROR] /usr/sbin/mysqld: unknown variable 'replicate-rewrite-do-db=adb->bdb'110118 22:35:13 mysqld ended
改成:
'replicate-rewrite-db=adb->bdb'
replicate-do-table=bdb.User
replicate-do-table=bdb.passwd
45 mysqld started
110118 22:39:45 [Warning] The syntax for replication startup options is deprecated and will be removed in MySQL 5.2. Please use 'CHANGE MASTER' instead.
110118 22:39:45 [ERROR] /usr/sbin/mysqld: unknown variable 'replicate-rewrite-do-table=bdb.user'-----------------------按照楼上的 到表级别有错误110118 22:39:45 mysqld ended
replicate-rewrite-do-table=bdb.user
怎么看都不正确.红字标明;
要是do去掉的话,也不正确,不支持表级别的;
直接使用replicate-do-table=bdb.Userrewrite不能到达表级别