/**
* @ Date 2010.09.13
* @ Author Wolf * @ Email 1317983530@qq.com
* @ Qq 1317983530 * @ Blog http://hi.baidu.com/1317983530/home
*/ Mysql Data Synchronization is being performed these two days, which is cumbersome. I used two machines for testing here, both of which are my local machines.
Master Server
192.168.0.118
Slave server 192.168.0.187 1. Create backup_db and backup_table tables on the Master/Slave servers as test instances.
Create Table
'Backup _ table '(
'Id' int (11) not null auto_increment,
'Name'
Varchar (20) Character Set utf8 not null,
'Sex' varchar (2) Character Set
Utf8 not null,
Primary Key ('id ')
) Engine = InnoDB default
Charset = Latin1;
Note that the database structure of the master and slave servers must be consistent. Otherwise, an error occurs. 2. Locate the my. ini file in the MySQL directory on the server, open the file, and add the following code at the end of the file:
# Configure the following in my. CNF (or my. INI) of the Host: Server-id = 1 # indicates the master server
Log-bin = E:/mysqlback/# log file of the synchronization event
Log-bin-Index
= E:/mysqlback/master-log-bin.index
Log-error = E:/mysqlback/master-error.log
# Error Log
BINLOG-do-DB = backup_db # database that provides the data synchronization service (the test database just created here) 3. After configuring the master server, open the my. CNF (or my. INI) file of the slave server and add the following code to the end of the file: Server-id = 2 # indicates the slave server
Master-host = 192.168.0.118 # host a address
Master-user = testuser # the user that host a provides to B needs to include database backup_db permissions.
Master-Password = testpwd # Access Password
Master-Port = 3306 # port, MySQL port of the host
Master-connect-retry = 60 # Retry Interval 60 seconds
Replicate-do-DB = backup_db # synchronized Database 4. After that, enable the MySQL console in the CMD of the master-slave server to authorize the testuser user on the server to synchronize resources on the master server.
Master server execution
Grant file on *. * To testuser @ '192. 168.0.187 'identified by 'testpwd'
Run
Grant file on *. * To testuser @ '192. 168.0.118 'identified by 'testpwd' 5. Restart MySQL of the Master/Slave Server
Slave server mysql> slave start; The master server executes mysql> show Master status. The result is as follows:
Mysql> show Master
Status;
+ --------- + ---------- + -------------- + ------------------ +
| File
| Position | binlog_do_db | binlog_ignore_db
|
+ --------- + ---------- + -------------- + ------------------ +
|. 000002.
| 613 | backup_db |
|
+ --------- + ---------- + -------------- + ------------------ +
1 row in Set
(0.00 Sec) Slave Server
Mysql> Start slave;
Mysql> show slave
Status/g;
Execution result
* *************************** 1. Row
***************************
Slave_io_state:
Master_host:
192.168.0.118
Master_user: testuser
Master_port: 3306
Connect_retry:
60
Master_log _ mysql-bin.000016
Read_master_log_pos: 173
Relay_log _
Mysqld-relay-bin.000002
Relay_log_pos: 98
Relay_master_log _
Mysql-bin.000016
Slave_io_running: No
Slave_ SQL _running: Yes
... The execution result slave_io_running and slave_ SQL _running must be yes. If you find slave_io_running: No
This may be due to permission issues.
Solution:
Mysql> show Master
Status;
+ ------------------ + -------------------- + ---------------- + ------------------ +
|
File | position | binlog_do_db | binlog_ignore_db
|
+ ------------------ + ------------------- + ----------------- + ------------------ +
|
Mysql-bin.000001 | 98 |
+ ------------------ + -------------------- + ----------------- + ------------------ +
Mysql>
Slave stop;
Mysql> change master
Master_log_file = 'mysql-bin.000001 ', master_log_pos = 98;
Mysql> slave
Start; Alternatively, open phpMyAdmin of the master-slave server, find the permissions set by the testuser, restart them separately, and then go to MySQL> show on the slave server.
Slave status/g;
Found
Slave_io_running: Yes
Slave_ SQL _running: Yes In this case, you can find the backup_table table of backup_db in phpMyAdmin of the master server and insert a record,
Look at this table from the server and find an additional record !!! |
