Source code package mysqlAB replication (Data Synchronization)

Environment: master server: IP address 192.168.1.70

Slave server: IP 192.168.1.71

Master Server Configuration:

Modify permissions:

Chown-R root/usr/local/mysql51 /*

Chown-R mysql/usr/local/mysql51/var

Chown-R mysql/usr/local/mysql51 /*

Modify my. cnf

Vim my. cnf

Server-id = 1

Check whether bin-log is enabled. After modification, the Mysql service must be restarted.

Log-bin = mysql-bin

/Mysql. server restart -- user = root

Create an account from the database (for Data Synchronization) and use replication slave to grant permissions, such:

Mysql> grant replication slave, reload, super on *. * to "slave" @ "192.168.1.71" identified by "slave ";

Authorized Login User:

Grant all on to *. * "xcy" @ "192.168.1.70" identified by "xcy" with grant option;

Grant all on to *. * "xcy" @ "192.168.1.71" identified by "xcy" with grant option;

After creation, you can check authorized users:

Mysql> select Host, User, password from mysql. user;

+ -------------- + ----------- + ------------------------------------------- +

| Host | User | password |

+ -------------- + ----------- + ------------------------------------------- +

| 192.168.1.71 | slave | * 255.25b3597bee0fc43e0bcbfee002ef8641b44cf |

+ -------------- + ----------- + ------------------------------------------- +

View master Status

Mysql> show master status;

+ ------------------ + ---------- + -------------- + ------------------ +

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+ ------------------ + ---------- + -------------- + ------------------ +

| Mysql-bin.000007 | 468 |

+ ------------------ + ---------- + -------------- + ------------------ +

1 row in set (0.00 sec)

----------------------------------------------------------------------

Slave Server Configuration:

Modify permissions:

Chown-R root/usr/local/mysql51 /*

Chown-R mysql/usr/local/mysql51/var

Chgrp-R mysql/usr/local/mysql51 /*

Modify my. cnf

Vim my. cnf

Server-id = 2

Check whether bin-log is enabled

Log-bin = mysql-bin

Authorized Login User:> authorized login user:

Grant all on *. * "xcy" @ "192.168.1.70" identified by "xcy" with grant option;

Grant all on *. * "xcy" @ "192.168.1.71" identified by "xcy" with grant option;

Restart mysql

/Mysql. server restart -- user = root

Create a connection from the slave server to the master server

CHANGE MASTER

MASTER_HOST = '1970. 168.1.70 ',

MASTER_USER = 'slave ',

MASTER_PASSWORD = 'slave ',

MASTER_LOG_FILE = 'mysql-bin.000007 ',

MASTER_LOG_POS = 468;

Start the slave from the database

Mysql> slave start;

Check whether synchronization is performed;

Mysql> show slave status \ G

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.70

Master_User: slave

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000007

Read_Master_Log_Pos: 468

Relay_Log_File: linux-relay-bin.000002

Relay_Log_Pos: 613

Relay_Master_Log_File: mysql-bin.000007

Slave_IO_Running: Yes

Slave_ SQL _Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Note that the master status will change after the mysql instance is restarted. Note the following when creating a connection:

If yes, you can modify the connection file;

Change master to Master_Log_File = 'mysql-bin.000001 ', Master_Log_Pos = 98;

If the synchronization fails

========================================================== ========================================================== ======================================

Summary:

############################# Handle errors ####### ###################

For Slave_IO_Running: No

Slave_ SQL _Running: Yes

Error Response

Master slave replication error

Description:

Slave_IO_Running: NO

Slave_ SQL _Running: Yes

Seconds_Behind_Master: NULL

I have encountered Slave_IO_Running: NO in the following two cases:

1. When configuring slave synchronization, the slave has no permission to access the master;

2. I accidentally deleted all the mysql-bin.xxxxxx files on the master;

In the first case, carefully check the database access permissions;

For the second case:

Mysql> show slave status \ G

* *************************** 1. row ***************************

Slave_IO_State:

Master_Host: 192.168.0.123

Master_User: slave

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000016

Read_Master_Log_Pos: 173

Relay_Log_File: mysqld-relay-bin.000008

Relay_Log_Pos: 98

Relay_Master_Log_File: mysql-bin.000016

Slave_IO_Running: No

Slave_ SQL _Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table: br>

Last_Error: 0

Skip_Counter: 0

Exec_Master_Log_Pos: 173

Relay_Log_Space: 98

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: NULL

1 row in set (0.00 sec)

Solution:

Restart master database: service mysqld restart

Mysql> show master status;

+ ------------------ + -------------------- + ---------------- + ------------------ +

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+ ------------------ + ------------------- + ----------------- + ------------------ +

| Mysql-bin.000001 | 98 |

+ ------------------ + -------------------- + ----------------- + ------------------ +

Mysql> slave stop;

Mysql> change master to Master_Log_File = 'mysql-bin.000001 ', Master_Log_Pos = 98;

Mysql> slave start;

Mysql> show slave status \ G

* *************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.0.123

Master_User: slave

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 98

Relay_Log_File: mysqld-relay-bin.000002

Relay_Log_Pos: 235

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes

Slave_ SQL _Running: Yes

Replicate_Do_DB:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 98

Relay_Log_Space: 235

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

1 row in set (0.00 sec)

############################# Handle errors ####### ###################

Problem: If your mysql database has been used for data storage, it will cause the master and slave log errors!

Mysql> start slave;

ERROR 1201 (HY000): cocould not initialize master info structure; more error messages can be found in the MySQL error log

Mysql> show slave status;

Empty set (0.00 sec)

Processing: Delete the mysql-bin.000001 log file and restart the mysql Service

1. master

# Rm-rf/var/lib/mysql /*.*

2. slave

# Rm-rf/usr/local/mysql/var /*.*

3. service mysqld restart

This article is from the "history_xcy" blog, please be sure to keep this http://historys.blog.51cto.com/7903899/1295239