CentOS6.5配置MySQL主从同步
原文地址：http://www.cnblogs.com/zhongshengzhen/
修改主MySQL的配置
[root@localhost etc] vi /etc/my.cnf
添加以下配置
server-id=1
log-bin=mysql-bin #这个一定得设置，否则没有日志的话，从数据库上会报错
log-bin=mysql-bin #这个一定得设置，否则没有日志的话，从数据库上会报错
[root@localhost etc]# service mysqld stop
Stopping mysqld: [ OK ]
[root@localhost etc]# service mysqld start
Starting mysqld: [ OK ]
Stopping mysqld: [ OK ]
[root@localhost etc]# service mysqld start
Starting mysqld: [ OK ]
[root@localhost etc]# service mysqld status
mysqld (pid 3129) is running...
mysqld (pid 3129) is running...
创建主从复制的帐号：
mysql> GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.* TO backup@'192.168.137.197' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
如果该命令无法执行，请检查是否用了圆角的’，需要用半角的'，再不行，那就是mysql数据库安装不成功。
192.168.137.197是从库的地址
backup是需要同步的用户名(有些博客说是数据库名称，坑人不倦)
123456为远程同步密码
解锁表
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
正确的状态如下：
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 106 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 106 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
配置错误的情况，解决办法见文章最后的疑难解答。
mysql> SHOW MASTER STATUS;
Empty set (0.00 sec)
Empty set (0.00 sec)
修改从库配置：
[root@localhost ~]# vi /etc/my.cnf
添加语句
log-bin = mysql-bin
server_id = 2
master-host = 192.168.137.33
master-user = backup
master-pass = 123456
master-port = 3306
master-connect-retry = 60
master-host = 192.168.137.33
master-user = backup
master-pass = 123456
master-port = 3306
master-connect-retry = 60
mysql> CHANGE MASTER TO
MASTER_HOST='192.168.137.33',MASTER_USER='backup',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=106,
MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected (0.02 sec)
测试主从是否配置成功。
mysql> show slave status\G;
数据库测试：
（1）主从都新建了test数据库和test数据表：
CREATE TABLE `test` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`NAME` varchar(255) DEFAULT NULL,
`UPDATE_TIME` datetime DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8；
`ID` int(11) NOT NULL AUTO_INCREMENT,
`NAME` varchar(255) DEFAULT NULL,
`UPDATE_TIME` datetime DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8；
（2）往主库插入数据行：
INSERT into test(name,update_time) value ('helo','2015-10-27 19:09:00');
恭喜，你会发现数据已经得到同步。
总结：
主从复制完成，但是还不能满足我们的需求。这种配置方式只是将主库的数据同步到从库。
我们设想下：有一天我们往从库插入或者删除数据，那么主库的数据不会得到改变。就会造成数据不一致。
下一章将配置主从数据互为同步。
疑难解答：
1、
mysql> SHOW MASTER STATUS;
Empty set (0.00 sec)
Empty set (0.00 sec)
主库出现上面的提示说明my.cnf配置不正确。
正常的应该是如下：
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 106 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 106 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
原因是：没有启动log-bin日志生成项
处理办法：
在/etc/my.conf的第一项填上log-bin=mysql-bin，如下：
[mysqld]
log-bin=mysql-bin
......
网络上很多垃圾教程都TM把这项不是写到第一行，造成了无法产生日志。这些没有实践抄别人BLOG的人都TM是一群猪。
2、此外，执行：
mysql> show slave status\G;
如果出现这样子的错误：
Slave_IO_Running: No
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Misconfigured master - server id was not set'
或者：
Slave_IO_Running: No
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).
正确的应该是：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Slave_SQL_Running: Yes
你会疑惑，不是已经设置了server-id了吗?
如果在网上有人告诉你使用指令：mysql> set global sql_slave_skip_counter=1;
你可以直接问候他全家。
造成该错误的原因是：server-id没有列在[mysqld]下面：
正确的my.cnf如下：
[mysqld]
log-bin = mysql-bin
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
server_id = 2
master-host = 192.168.137.33
master-user = backup
master-pass = 123456
master-port = 3306
master-connect-retry = 60
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
log-bin = mysql-bin
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
server_id = 2
master-host = 192.168.137.33
master-user = backup
master-pass = 123456
master-port = 3306
master-connect-retry = 60
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
