《centos6.5配置mysql主从同步》由会员分享，可在线阅读，更多相关《centos6.5配置mysql主从同步（9页珍藏版）》请在金锄头文库上搜索。
1、修改主 MySQL 的配置rootlocalhost etc vi /etc/f添加以下配置server-id=1log-bin=mysql-bin #这个一定得设置，否则没有日志的话，从数据库上会报错rootlocalhost etc# service mysqld stopStopping mysqld: OK rootlocalhost etc# service mysqld startStarting mysqld: OK rootlocalhost etc# service mysqld statusmysqld (pid 3129) is running.创建主从复制的帐号：mys
2、ql GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.* TO backup192.168.137.197 IDENTIFIED BY 123456;Query OK, 0 rows affected (0.00 sec)如果该命令无法执行，请检查是否用了圆角的，需要用半角的，再不行，那就是 mysql 数据库安装不成功。192.168.137.197 是从库的地址backup 是需要同步的用户名(有些博客说是数据库名称，坑人不倦)123456 为远程同步密码解锁表mysql UNLOCK TABLES;Query OK, 0 rows affected (
3、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)配置错误的情况，解决办法见文章最后的疑难解答。mysql SHOW MASTER STATUS;Empty set (0.00 sec)修改从库配置：rootlocalhost # vi /etc/f添加语句log-bin = mysql-binse
4、rver_id = 2master-host = 192.168.137.33master-user = backupmaster-pass = 123456master-port = 3306master-connect-retry = 60mysql CHANGE MASTER TOMASTER_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_RET
5、RY=10;Query OK, 0 rows affected (0.02 sec)测试主从是否配置成功。mysql show slave statusG;数据库测试：（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；（2）往主库插入数据行：INSE
6、RT into test(name,update_time) value (helo,2015-10-27 19:09:00);恭喜，你会发现数据已经得到同步。总结：主从复制完成，但是还不能满足我们的需求。这种配置方式只是将主库的数据同步到从库。我们设想下：有一天我们往从库插入或者删除数据，那么主库的数据不会得到改变。就会造成数据不一致。下一章将配置主从数据互为同步。疑难解答：1、mysql SHOW MASTER STATUS;Empty set (0.00 sec)主库出现上面的提示说明 f 配置不正确。正常的应该是如下：mysql SHOW MASTER STATUS;+-+-+-+-+
7、| 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，如下：mysqldlog-bin=mysql-bin.网络上很多垃圾教程都 TM 把这项不是写到第一行，造成了无法产生日志。这些没有实践抄别人 BLOG 的人都 TM 是一群猪。2、此外，执行：mysql show slav
8、e statusG;如果出现这样子的错误：Slave_IO_Running: NoLast_IO_Error: Got fatal error 1236 from master when reading data from binary log: Misconfigured master - server id was not set或者：Slave_IO_Running: NoLast_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids;
9、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: YesSlave_SQL_Running: Yes你会疑惑，不是已经设置了 server-id 了吗?如果在网上有人告诉你使用指令：mysql set global
10、 sql_slave_skip_counter=1;你可以直接问候他全家。造成该错误的原因是：server-id 没有列在mysqld下面：正确的 f 如下：mysqldlog-bin = mysql-bindatadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockuser=mysql# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0server_id = 2master-host = 192.168.137.33master-user = backupmaster-pass = 123456master-port = 3306master-connect-retry = 60mysqld_safelog-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid
