Mysql Data Synchronization settings

MySQL version used: mysql4.0.x

MASTER: 192.168.1.102
SLAVE: 192.168.1.109

Modify C:/winnt/My. ini for Windows host. Modify/etc/My. CNF for Linux host.

1. Master operations
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: database to be synchronized
BINLOG-ignore-DB = databases that cannot be synchronized by MySQL

2. Restart MySQL.
C:/MySQL/bin/MySQL restart or/etc/init. d/MySQL restart

3. Add a synchronized user name to the master.
Mysql> grant replication slave on *. * To 'backup '@' % 'identified by '123 ';
Note:
If the mysql version is earlier than 4.0.2, use
Mysql> grant file on *. * To 'backup '@' % 'identified by '123 ';

4. perform the following operations on the database to be synchronized on the master node.
Mysql> use test;
Mysql> flush tables with read lock; # Lock the test table to be synchronized, and then export the Data Structure

Run the following command to check the Master Status:
Mysql> show Master status;

The following result is displayed.
Code:
+ ---------------------- + ---------- + -------------- + ------------------ +
| File | position | binlog_do_db | binlog_ignore_db |
+ ---------------------- + ---------- + -------------- + ------------------ +
| Mysql_binary_logtail25 | 796947 | test | MySQL |
+ ---------------------- + ---------- + -------------- + ------------------ +
1 row in SET (0.00 Sec)

Next, back up the database to be synchronized (to prepare for importing slave)
$ Mysqldump -- opt test> test. SQL

Mysql> unlock tables; # unlock the table after synchronizing the database structure to export

Ii. Slave operations
1. Import the synchronized database structure exported from the master database to the MySQL Server Load balancer instance.
MySQL tes <test. SQL

2. modify my. ini of slave
####################
Log-bin = C:/MySQL/logs/mysql_binary_log
Server-id = 2
Master-host = 192.168.1.102
Master-user = backup
Masters-Password = 123456
Master-connect-retry = 60
Replicate-do-DB = test
####################

Note:
Master-host = 192.168.1.102 # Master Ip address
Master-user = backup # user name used for synchronization on the master
Master-Password = 123456 # synchronize the password of the user name
Master-connect-retry = 60 # Set the synchronization time
Replicate-do-DB = test # database to be synchronized

3. re-enable MySQL
C:/MySQL/bin/MySQL restart or/etc/init. d/MySQL restart

4. log on to the MySQL Server Load balancer and perform operations on the MySQL Server Load balancer instance.
Mysql> stop slave; # Stop the slave server

Mysql> change master
-> Master_host = '192. 168.1.102 ',
-> Master_user = 'backup ',
-> Master_password = '123 ',
-> Master_log_file = 'mysql _ binary_log1_25 ',
-> Master_log_pos = 796947;

Mysql> Start slave; # enable the slave server to synchronize data.

Note:
Master_log_file = 'mysql _ binary_log1_25 ',
Master_log_pos = 796947;
The above two items start to enter MySQL from the master and run show Master status. As you can see, they can not be added in actual operations.

 

 

######################################## ######
Run in MySQL of Master
Mysql> show processlist;
The two system users are normal.
+ ---- + ------------- + ---------------- + ------ + ------------- + ------ + ---------------
---------------------------------------------------------- + ---------------- +
| ID | user | host | dB | command | time | state
| Info |
+ ---- + ------------- + ---------------- + ------ + ------------- + ------ + ---------------
---------------------------------------------------------- + ---------------- +
| 1 | system user | null | connect | 4499 | waiting for Ma
Ster to send event | null |
| 2 | system user | null | connect | 4499 | has read all r
Elay log; waiting for the I/O slave thread to update it | null |
| 7 | root | localhost: 1309 | test | query | 0 | null
| Show processlist |
| 40 | Backup | ETE-KF2: 1354 | null | BINLOG dump | 513 | has sent all B
Inlog to slave; waiting for BINLOG to be updated | null |
+ ---- + ------------- + ---------------- + ------ + ------------- + ------ + ---------------
---------------------------------------------------------- + ---------------- +
4 rows in SET (0.00 Sec)

Mysql> show Master status;
The following figure is displayed:
+ ---------------------- + ---------- + -------------- + ------------------ +
| File | position | binlog_do_db | binlog_ignore_db |
+ ---------------------- + ---------- + -------------- + ------------------ +
| Mysql_binary_log.001 | 1011 | test | MySQL |
+ ---------------------- + ---------- + -------------- + ------------------ +

Run in slave MySQL
Mysql> show processlist;
The following figure is displayed:
+ ---- + ------------- + ---------------- + ------ + --------- + ------ + -------------------
---------------------------------------------------- + ------------------ +
| ID | user | host | dB | command | time | state
| Info |
+ ---- + ------------- + ---------------- + ------ + --------- + ------ + -------------------
---------------------------------------------------- + ------------------ +
| 3 | root | localhost: 1353 | test | query | 0 | null
| Show processlist |
| 4 | system user | null | connect | 740 | waiting for Master
To send event | null |
| 5 | system user | null | connect | 730 | has read all relay
Log; waiting for the I/O slave thread to update it | null |
+ ---- + ------------- + ---------------- + ------ + --------- + ------ + -------------------
---------------------------------------------------- + ------------------ +
3 rows in SET (0.00 Sec)

Mysql> show slave status;
+ --------------- + ------------- + --------------- + -------------------
--- + --------------------- + --------------- + --------------
--------- + ------------------ + ------------------- + ----------------- + -------------
-------- + ------------ + -------------- + --------------------- + --------
--------- +
| Master_host | master_user | master_port | connect_retry | master_log_file
| Read_master_log_pos | relay_log_file | relay_log_pos | relay_master _
LOG_FILE | slave_io_running | slave_ SQL _running | replicate_do_db | replicate_ig
Nore_db | last_errno | last_error | skip_counter | exec_master_log_pos | relay_l
Og_space |
+ --------------- + ------------- + --------------- + -------------------
--- + --------------------- + --------------- + --------------
--------- + ------------------ + ------------------- + ----------------- + -------------
-------- + ------------ + -------------- + --------------------- + --------
--------- +
| 192.168.1.102 | Backup | 3306 | 10 | mysql_binary_log.0
01 | 1011 | ete-kf2-relay-bin.002 | 227 | mysql_binary _
Log.001 | Yes | test |
| 0 | 0 | 1011 | 223
|
+ --------------- + ------------- + --------------- + -------------------
--- + --------------------- + --------------- + --------------
--------- + ------------------ + ------------------- + ----------------- + -------------
-------- + ------------ + -------------- + --------------------- + --------
--------- +
1 row in SET (0.00 Sec)

 

Reference:
Http://forums.gentoo.org/viewtopic.php? T = 241123
Http://www.howtoforge.com/mysql_database_replication_p2
Http://dev.mysql.com/doc/refman/4.1/en/replication-howto.html
Http://bbs.chinaunix.net/viewthread.php? Tid = 692359 & Highlight = MySQL