Mysql 主从同步（转载）

第一步：
在master上创建用于同步的用户
GRANT FILE,REPLICATION SLAVE,REPLICATION CLIENT,SUPER ON *.* TO backup@'192.168.0.246'IDENTIFIED by 'backup';

第二步：
配置master机的my.cnf，在mysqld段加上：
server-id = 1588
replicate-same-server-id = 1
relay-log = mysql-relay-bin
log-bin = mysql-bin
replicate-ignore-db = mysql
replicate-ignore-db = information_schema

第二步：
配置slave机的my.cnf，在mysqld段加上：
replicate-same-server-id = 1588
master-host = 192.168.0.245
master-user = backup
master-password = backup
relay-log = relay-bin
relay-log-index = relay-bin
replicate-ignore-db = mysql
replicate-ignore-db = information_schema

测试：在slave机上运行：show slave status\G; 显示为如下面这样就OK！
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
Seconds_Behind_Master: 0

异常处理：
一、
Slave_IO_Running: NO
Slave_SQL_Running: Yes
检查用户权限、服务器ID

二、
Slave_IO_Running: Yes
Slave_SQL_Running: NO
在master上
mysql> show master status
-> ;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000012 | 106 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
记住查到的两个值 。

转到slave上
mysql>slave stop;
mysql>change master to master_host='192.168.0.245', master_user='backup', master_password='backup', master_port=3306, master_log_file='mysql-bin.000012', master_log_pos=106;
mysql>slave start;

再查看状态：
mysql>show slave status\G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
Seconds_Behind_Master: 0

OK! 配置完成！

转载于http://blog.knowsky.com/242446.htm