此文档包含两部分：
一、MySQL主从库的配置 二、常见问题以及解决方法
一、MySQL主从库的配置
说明：
1、此文档记录的是MySQL主从库最常用的配置方法，即：一主一从（或一主多从）。
切记：此种配置一定是主写从读。
2、MySQL主从库的原理，这里就不介绍了，可以自行百度，这个网址也有详细说明：
http://blog.csdn.net/hguisu/article/details/7325124/
3、本次配置环境：
（1）VMware虚拟机；
（2）虚拟两台主机：
主机（master）：系统：ubuntu-16.04-desktop-amd64.iso； IP：192.168.142.166
主机（slave）： 系统：ubuntu-16.04-desktop-amd64.iso； IP：192.168.142.167
（3）MySQL版本：mysql-server_5.6.26-1ubuntu14.04_amd64.deb-bundle.tar
4、若仅想看主从库的配置，可直接查看第五步、第六步。
第一步：安装MySQL（已安装则忽略）
分别在两台主机安装MySQL：
1、cd
2、mkdir mysql-deb
3、cd mysql-deb
4、下载mysql-server_5.6.26-1ubuntu14.04_amd64.deb-bundle.tar到当前目录
5、tar -xf mysql-server_5.6.26-1ubuntu14.04_amd64.deb-bundle.tar
6、安装支持包：sudo apt-get install libaio1
7、sudo dpkg -i mysql-common_5.6.26-1ubuntu14.04_amd64.deb
8、sudo dpkg -i mysql-community-server_5.6.26-1ubuntu14.04_amd64.deb
9、sudo dpkg -i mysql-community-client_5.6.26-1ubuntu14.04_amd64.deb
启动、停止MySQL命令：
/etc/init.d/mysqlstart
/etc/init.d/mysqlstop
在主库（192.168.142.166）配置文件中注释bind-address = 127.0.0.1 （否则从 库不能远程登录主库）
cd/etc/mysql
vi my.cnf
# bind-address= 127.0.0.1
第二步：在主库建立数据库（已有库则忽略）
1、mysql -uroot -p
2、create database osyunweidb CHARACTER SET'utf8mb4' COLLATE 'utf8mb4_general_ci';
3、use osyunweidb;
4、CREATE TABLE `userinfo` (
`id` int(20) NOT NULL AUTO_INCREMENT,
`name` char(20) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`sex` char(2) DEFAULT NULL,
PRIMARY KEY (`id`)
);
5、INSERT INTO `userinfo`(name,age,sex) VALUES ( 'adai',18,'1');
第三步：建立同步用户——从库连接登陆主库的用户（已有用户则忽略）
1、mysql -uroot -p
2、创建用户：
GRANTUSAGE ON *.* TO 'osyunweidbbak '@'192.168.142.167' IDENTIFIED BY '123456' WITH GRANT OPTION;
用户：osyunweidbbak
密码：123456
只能从主机192.168.142.167（从库IP）远程登录
3、查看用户权限：
showgrants for osyunweidbbak @'192.168.142.167';
显示：
只有 USAGE权限：只允许登录--其它什么也不允许做。
4、更改用户osyunweidbbak的权限：
grantreplication slave on *.* to 'osyunweidbbak '@'192.168.142.167' identified by'123456' with grant option;
显示：
授权用户osyunweidbbak只能从192.168.142.167这个IP访问主库（192.168.142.166） 的数据库，并且只具有数据库备份的权限。
5、刷新系统授权表
flushprivileges;
6、测试在从库服务器上登录到主库
mysql-u osyunweidbbak -h 192.168.142.166 -p
第四步：把主库的数据库导入到从库中
1、在主库导出数据库（192.168.142.166）
进入mysql 客户端程序和脚本目录
cd/usr/bin
flushtables with read lock;
mysqldump-u root -p osyunweidb > /home/osyunweidbbak.sql
unlocktables;
2、导入数据到从库（192.168.142.167）
mysql -u root -p
source /home/osyunweidbbak.sql
第五步：配置主库（192.168.142.166）的my.cnf文件
1、cd /etc/mysql
2、vi my.cnf
配置如图：
注意：红色框部分。
说明：
（1）server-id=1
#设置服务器id，1表示主服务器（主库），注意：如果原来的配置文件中已经有这一行， 就不用再添加了。
（2）log_bin=mysql-bin
#启动MySQ二进制日志系统，注意：如果原来的配置文件中已经有这一行，就不用再 添加了。
（3）binlog-do-db=osyunweidb
#需要同步的数据库名，如果有多个数据库，可重复此参数，每个数据库一行。
（4）binlog-ignore-db=mysql
#不需要同步的数据库名，如果有多个数据库，可重复此参数，每个数据库一行。
3、重启MySQL
/etc/init.d/mysqlrestart
4、查看主库信息
mysql-u root -p
showmaster status;
显示：
说明：与my.cnf配置的参数一致，同步数据库：osyunweidb，不同步数据库： mysql；
特别注意：这里需要记下File和Position的值，这是从库同步主库数据开始的同步点。
第六步：配置从库（192.168.142.167）的my.cnf文件
1、cd /etc/mysql
2、vi my.cnf
配置如图：
注意：红色框部分。
说明：
（1）server-id=2
#设置服务器id，2表示从服务器（从库），注意：如果原来的配置文件中已经有这一行， 就不用再添加了。
（2）replicate_wild_do_table = osyunweidb.%
#需要同步的数据库名，如果有多个数据库，可重复此参数，每个数据库一行。
（3）replicate_wild_ignore_table=mysql .%
#不需要同步的数据库名，如果有多个数据库，可重复此参数，每个数据库一行。
（4）特别注意，注释掉的两行：
replicate-do-db=osyunweidb
replicate-ignore-db=mysql
从库的配置文件不可用这两个参数，在同步数据的时候会有隐患，参考“二、常见问 题以及解决方法”的说明。
（5）从库不需要开启二进制日志，除非有必要：如，此从库又作为别的从库的主库， 或者此从库需要增量备份。
（6）MySQL 5.1.7版本之后，已经不支持把主库（master）配置属性，如：
master_host='192.168.142.166', master_user='osyunweidbbak',
master_password='123456', master_log_file='mysql-bin.000006' ,
master_log_pos=44884752
写入my.cnf配置文件中了,只把要同步的数据库和要忽略的数据库写入my.cnf即可。
3、重启MySQL
/etc/init.d/mysqlrestart
4、mysql -uroot -p
5、停止slave同步进程
stopslave;
6、执行同步语句（设置主库IP、登录用户名密码、同步文件、同步点）
changemaster to master_host='192.168.142.166',master_user='osyunweidbbak',master_password='123456', master_log_file=' mysql-bin.000006' ,master_log_pos=44884752;
注意：这里的master_log_file=' mysql-bin.000006' ,master_log_pos=44884752 就是前面在 主库查看并记录下来的File和Position的值。
7、start slave;
8、查看slave同步信息
showslave status\G （用\G结尾而不是分号结尾，按行显示结果）
显示：
注意查看：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
以上这两个参数的值为Yes，即说明配置成功，而正在运行。
注意核对其他信息是否正确。
第七步：测试同步是否成功以及一般稳定性
手工测试只能简单的插入、删除或更新几条数据，所以可以写几行代码随机、长时间、 大批量的对主库插入、删除或更新数据；然后查看主库和从库的数据一致性（简单的只 能看总记录数是否一致了）。
结果如下（从前一天晚上6点运行到次日8点）：
可以看到，不管是插入数据还是删除数据操作，主库、从库 userinfo表的记录数都保 持一致。数据量已经有28万了。
OK，主从库配置完毕。
二、常见问题以及解决方法
1、从库没有同步主库的数据
分两种情况：
（1）从库在执行SQL语句时发生错误
默认下从库在同步数据执行SQL语句时，只要发生错误，就会停止同步，不会跳过；
（当然主库本就没有执行成功的SQL语句，不会影响从库同步）。
查看方法：
①主服务器（主库）查看进程是否Sleep太多：
show processlist;
②查看主库状态是否正常：
show master status;
③查看从库状态信息：
show slave status\G
显示：
Slave_IO_Running: Yes
Slave_SQL_Running: No
Last_SQL_Error: Error 'PROCEDURE BK.zoucmdoes not exist' on query. Default database: 'BK'. Query: 'drop procedure zoucm'
可见slave没有同步，SQL报错。
解决方法：
如果数据可控的情况下，可以忽略错误，继续同步：
1、停止slave服务器的主从同步:
stop slave;
2、跳过一步错误，数字表示跳过多少步:
set global sql_slave_skip_counter =1;
3、开启slave服务:
start slave;
如果数据已经不可控，可以考虑重做主从库：
1.先进入主库（192.168.142.166），锁表，防止数据写入
flush tables withread lock;
2、备份主库数据：
mysqldump -uroot-p > mysqlmaster.bak.sql
3、重置主库maste
RESET MASTER;
4、查看master状态，并记录file和postion
show master status;
5、对主库（192.168.142.166）解锁表
UNLOCK TABLES;
6、进入从库（192.168.142.167），停止从库的同步状态
stop slave;
或者直接停止从库mysql服务
/etc/init.d/mysql stop
7、找到从库的中继日志，把中继日志相关的文件都删除
find / -name mysqld-relay-bin.*
rm -rf ...
7、（在从库操作）导入备份数据库
source mysqlmaster.bak.sql
8、清除同步信息
reset slave all;
9、（在从库操作）配置从库同步，注意同步点，即第4步查看的file和position信息
change master to master_host='192.168.142.166',master_user='osyunweidbbak',master_password='123456',master_log_file='mysql-bin.000006' ,master_log_pos=44884752;
注意：这里的master_log_file=' mysql-bin.000006' ,master_log_pos=44884752
要按实际的填写。
10、重新开启从库同步
START SLAVE;
11、（在从库操作）查看同步状态
show slave status\G
显示：
Slave_IO_Running:Yes
Slave_SQL_Running:Yes
（2）从库配置文件中使用参数replicate-do-db 和replicate-ignore-db
在配置从库的my.cnf文件时，如果使用参replicate-do-db 和replicate-ignore-db 会导致从库同步主库数据时忽略掉主库的跨库更新SQL语句。例如，主库：
usedb1;
insert intodb2.userinfo(name,age,sex) values ( 'adai',18,'1');
那么从库同步时会忽略此语句，即不会同步此数据。
所以为了避免此问题，应该使用参数：
replicate_wild_do_table = osyunweidb.%
replicate_wild_ignore_table=mysql .%
配置需要同步和不需要同步的数据库。
2、从库同步状态显示：Slave_IO_Running 为connecting
这是从库无法登陆主库。
主要有三个原因：
（1）网络不通
（2）登陆主库的账号密码不对或者权限问题
（3）position的位置不对
（4）主库的配置文件my.cnf 没有注释bind-address = 127.0.0.1
一一检查即可。
完毕。
网友评论