在应对数据库压力的时候，选择读写分离是比较常用的手段，下面将总结一下，如何配置读库和写库，以及读写不同步的时候如何去保持同步。在这里，我们将写库称为主库，读库称为从库
主从库的基本配置
1、主库的配置：
a、分配权限
grant replication slave on *.* to ‘repl_90‘@‘192.168.90.90‘ identified by ‘sina_sport‘;
b、修改主库配置文件
server-id = 91003306 #保持唯一即可 log-bin = mysql-bin relay-log = mysql-relay-bin binlog-ignore-db=information_schema binlog-ignore-db=cluster binlog-ignore-db=mysql
c、重启主库服务
2、从库的配置
a、修改从库配置文件
server-id = 890091 #保持唯一即可 log-bin = mysql-bin relay-log = mysql-relay-bin
b、重启从库服务
c、执行主从同步sql
mysql>CHANGE MASTER TO MASTER_HOST=‘192.168.90.91‘, MASTER_USER=‘repl_90‘, MASTER_PASSWORD=‘sk_sport‘, MASTER_PORT=3306, MASTER_LOG_FILE=‘mysql-bin.000001‘, MASTER_LOG_POS=98, MASTER_CONNECT_RETRY=10; #可不填写 mysql>start slave;
其中，MASTER_LOG_FILE和MASTER_LOG_POS 在主库中执行show master status可以看到
3、验证服务
mysql> show processlist; +----+---------+----------------------+------+-------------+--------+-----------------------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+---------+----------------------+------+-------------+--------+-----------------------------------------------------------------------+------------------+ | 6 | repl_90 | X.X.X.X:54004 | NULL | Binlog Dump | 245837 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL | | 7 | repl_90 | X.X.X.X:33431 | NULL | Binlog Dump | 245716 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL | | 86 | root | localhost | NULL | Query | 0 | NULL | show processlist | +----+---------+----------------------+------+-------------+--------+-----------------------------------------------------------------------+------------------+
其中X.X.X.X是从库的IP地址或者域名
主从不同步的处理办法
1、从库执行停止从库
mysql> slave stop;
2、查看主库binlog
mysql> flush logs;
mysql> show master status; +------------------+----------+--------------+----------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+----------------------------------+ | mysql-bin.000025 | 14628 | | information_schema,cluster,mysql | +------------------+----------+--------------+----------------------------------+
3、从库执行，保持同步
mysql> CHANGE MASTER TO MASTER_HOST=‘x.x.x.x‘, MASTER_USER=‘user‘, MASTER_PASSWORD=‘password‘, MASTER_LOG_FILE=‘ mysql-bin.000025‘, MASTER_LOG_POS=14628;
mysql> START SLAVE;