MySQL Data synchronization settings

mysql| Data | MySQL version of data synchronization: mysql4.0.x

master:192.168.1.102
slave:192.168.1.109

If the win machine to modify C:\winnt\my.ini if it is a Linux machine please modify/ETC/MY.CNF

One, master operation
1. Modify My.ini
##############
Server-id=1
Log-bin=c:\mysql\logs\mysql_binary_log
Binlog-do-db=test
Binlog-ignore-db=mysql
##############

Description
Binlog-do-db=test databases that allow synchronization
Binlog-ignore-db=mysql database not allowed to synchronize

2, and then restart MySQL
C:\mysql\bin\mysql Restart or/etc/init.d/mysql restart

3. Add a sync username to master
Mysql> GRANT REPLICATION SLAVE on *.* to ' backup ' @ '% ' identified by ' 123456 ';
Note:
If the MySQL version is in the previous version of 4.0.2, please use
Mysql> GRANT FILE on *.* to ' backup ' @ '% ' identified by ' 123456 ';

4, next operation to master on the database to be synchronized
mysql> use test;
Mysql> FLUSH TABLES with READ LOCK; #锁定要同步的test表, and then export the data structure

Perform the following command to view the status of master
Mysql> show MASTER STATUS;

Get the following results
Code:
+----------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+----------------------+----------+--------------+------------------+
| mysql_binary_log.025 | 796947 | Test | MySQL |
+----------------------+----------+--------------+------------------+
1 row in Set (0.00 sec)

Next backup to synchronize the database (prepare for importing slave)
$ mysqldump--opt Test > Test.sql

Mysql> UNLOCK TABLES; #已做好同步数据库结构导出后, unlock this table.

Second, slave operation
1, the Master of the exported synchronization database structure and then import into slave mysql
MySQL tes < Test.sql

2, modify the slave My.ini
####################
Log-bin=c:\mysql\logs\mysql_binary_log
server-id=2
master-host=192.168.1.102
Master-user=backup
master-password=123456
Master-connect-retry=60
Replicate-do-db=test
####################

Description
master-host=192.168.1.102 #master的IP
Master-user=backup #master上作为同步用的用户名
master-password=123456 #同步用户名的密码
Master-connect-retry=60 #设置同步的时间
Replicate-do-db=test #需要同步的数据库

3. Re-enable MySQL
C:\mysql\bin\mysql Restart or/etc/init.d/mysql restart

[1] [2] Next page