CentOS6.5配置MySQL主从同步.doc

修改主MySQL的配置rootlocalhost etcvi /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.创建主从复制的帐号：mysqlGRANT REPLICATION SLAVE,RELOAD,SUPER ON *.* TO backup97 IDENTIFIED BY 123456;Query OK, 0 rows affected (0.00 sec)如果该命令无法执行，请检查是否用了圆角的，需要用半角的，再不行，那就是mysql数据库安装不成功。97是从库的地址backup是需要同步的用户名(有些博客说是数据库名称，坑人不倦)123456为远程同步密码解锁表mysql UNLOCK TABLES;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)配置错误的情况，解决办法见文章最后的疑难解答。mysql SHOW MASTER STATUS;Empty set (0.00 sec)修改从库配置：rootlocalhost # vi /etc/f添加语句log-bin = mysql-binserver_id = 2master-host = 3master-user = backupmaster-pass = 123456master-port = 3306master-connect-retry = 60mysqlCHANGE MASTER TO MASTER_HOST=3, 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 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）往主库插入数据行：INSERT 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;+-+-+-+-+| 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 slave 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; 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 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 = 3