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

修改主MySQL的配置[root@localhostetc]?vi /etc/my.cnf添加以下配置server-id=1log-bin=mysql-bin #这个一定得设置，否则没有日志的话，从数据库上会报错[root@localhostetc]# servicemysqld stopStopping mysqld:? [? OK? ][root@localhostetc]# service mysqld startStarting mysqld:? [? OK? ][root@localhostetc]# servicemysqld statusmysqld (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)如果该命令无法执行，请检查是否用了圆角的’，需要用半角的，再不行，那就是mysql数据库安装不成功。192.168.137.197是从库的地址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)修改从库配置：[root@localhost ~]# vi /etc/my.cnf添加语句log-bin = mysql-binserver_id = 2master-host = 192.168.137.33master-user = backupmaster-pass = 123456master-port = 3306master-connect-retry = 60mysql?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；（2）往主库插入数据行：INSERT into test(name,update_time) value (helo,2015-10-27 19:09:00);恭喜，你会发现数据已经得到同步。总结：主从复制完成，但是还不能满足我们的需求。这种配置方式只是将主库的数据同步到从库。我们设想下：有一天我们往从库插入或者删除数据，那么主库的数据不会得到改变。就会造成数据不一致。下一章将配置主从数据互为同步。疑难解答：1、mysql SHOW M