MySQL主从搭建及主从不同步问题处理

1、使用主从同步的好处：

1.通过增加从服务器来提高数据库的性能，在主服务器上执行写入和更新，在从服务器上向外提供读功能，可以动态地调整从服务器的数量，从而调整整个数据库的性能。
2.提高数据安全-因为数据已复制到从服务器，从服务器可以终止复制进程，所以，可以在从服务器上备份而不破坏主服务器相应数据
3.在主服务器上生成实时数据，而在从服务器上分析这些数据，从而提高主服务器的性能

2、主从同步机制

1、slave 服务器执行 start slave，开启主从复制开关， slave 服务器的 IO 线程请求从 master 服务器读取 binlog（如果该线程追赶上了主库，会进入睡眠状态）。
2、master 服务器的更新SQL(update、insert、delete)被写到binlog, 主库的 binlog dump thread，把 bin log 的内容发送到从库。
3、从库启动之后，创建一个 I/O 线程，读取主库传过来的 bin log 内容并写到 relay log（会记录位置信息，以便下次继续读取）。
4、slave 服务器的 SQL 线程会实时检测 relay log 中新增的日志内容，把 relay log解析成 SQL 语句，并执行。

3、MySql数据库安装

　　先检查系统是否装有mysql：rpm -qa | grep mysql (无返回则没有安装)

　　删除可用：yum remove mysql

　　下载地址：https://dev.mysql.com/downloads/mysql/

　　解压：tar -xvf /usr/local/mysql-8.0.30-linux-glibc2.12-x86_64.tar.xz -C /usr/local/

　　下载mysql的repo源： wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm

　　安装mysql.rpm包 ：rpm -ivh mysql-community-release-el7-5.noarch.rpm

　　安装rpm包后在 /etc/yum.repos.d 会获取两个yum repo源

　　

　　安装mysql：yum install mysql-server

　　再次查看：rpm -qa | grep mysql

　　

　　如果报错，内容含有：

?

1 2 3 4 5 6 7 8 9 10 11 12

Error: Package: mysql-community-libs-5.6.35-2.el7.x86_64 (mysql56-community)            Requires: libc.so.6(GLIBC_2.17)(64bit) Error: Package: mysql-community-server-5.6.35-2.el7.x86_64 (mysql56-community)            Requires: libc.so.6(GLIBC_2.17)(64bit) Error: Package: mysql-community-server-5.6.35-2.el7.x86_64 (mysql56-community)            Requires: systemd Error: Package: mysql-community-server-5.6.35-2.el7.x86_64 (mysql56-community)            Requires: libstdc++.so.6(GLIBCXX_3.4.15)(64bit) Error: Package: mysql-community-client-5.6.35-2.el7.x86_64 (mysql56-community)            Requires: libc.so.6(GLIBC_2.17)(64bit)  You could try using --skip-broken to work around the problem  You could try running: rpm -Va --nofiles --nodigest

　　解决：yum install glibc.i686 yum list libstdc++*

　　登入mysql：mysql -u root

　　登入报错可能为/var/lib/mysql文件权限问题，解决：chown root /var/lib/mysql/

　　重启mysql：service mysqld restart

　　为root添加远程连接的能力：GRANT ALL PRIVILEGES ON . TO root@"%" IDENTIFIED BY "password";

　　查询数据库编码格式，确保是 UTF-8：show variables like "%char%";

　　修改编码格式为UTF-8：set names utf8;

　　开放3306端口号，并重启（centos7）：

　　firewall-cmd --zone=public --add-port=3306/tcp --permanent firewall-cmd --reload

4、修改主库和从库的配置文件(/etc/my.cnf)

4.1更改配置文件，首先检查你的主服务器上的my.cnf文件中是否已经在[mysqld]模块下配置了log-bin和server-id

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

#[mysqld]标签下追加   #表示是本机的序号为1,一般来讲就是master的意思 server_id =1 #服务器在关闭它之前在一个连接上等待行动的秒数。 wait_timeout=360000 #开启Binlog日志 log-bin=binlog #开启Binlog日志的索引文件 log-bin-index=binlog.index #表示slave将复制事件写进自己的二进制日志 log_slave_updates=1 #当每进行1次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘 sync-binlog = 1 #表示自增长字段从那个数开始，他的取值范围是1 .. 65535 auto_increment_offset = 2 #表示自增长字段每次递增的量，其默认值是1，取值范围是1 .. 65535 auto_increment_increment = 2

4.2主库创建用AB复制所需的用户

?

1 2 3

GRANT replication slave ON . TO 'user'@'%' identified by 'password';   flush privileges;

4.3获取二进制日志的信息并导出数据库，步骤：

首先登陆数据库，然后刷新所有的表，同时给数据库加上一把锁，阻止对数据库进行任何的写操作

?

1	flush tables with read lock;   　　#数据库执行

然后执行下面的语句获取二进制日志的信息

?

1	show master status;　　　　#数据库执行

 File的值是当前使用的二进制日志的文件名，Position是该日志里面的位置信息（不需要纠结这个究竟代表什么），记住这两个值，会在下面配置从服务器时用到。

注意：如果之前的服务器并没有配置使用二进制日志，那么使用上面的sql语句会显示空，在锁表之后，再导出数据库里的数据（如果数据库里没有数据，可以忽略这一步）

?

1	mysqldump -uroot -p'123456' -S /data/3306/data/mysql.sock --all-databases > /server/backup/mysql_bak.$(date +%F).sql    #服务器执行

如果数据量很大，可以在导出时就压缩为原来的大概三分之一

?

1	mysqldump -uroot -p'123456' -S /data/3306/data/mysql.sock --all-databases | gzip > /server/backup/mysql_bak.$(date +%F).sql.gz　　#服务器执行

这时可以对数据库解锁，恢复对主数据库的操作

?

1	unlock tables;　　　　#数据库执行

4.4备库：

此时配置文件中：relay_log、relay_log_index后面配置的文件夹可能会报错找不到，

解决：1、配置mysql访问此文件夹的权限； 2、修改：relay-log=relay-log 修改：relay-log-index=relay-log.index

?

1 2 3 4 5 6 7 8 9 10 11 12

#[mysqld]标签下追加 #只读操作控制 read_only=1 #是否自动清空不再需要中继日志时,0不启动 relay_log_purge=0 server_id=2<br># 忽略表 <br>replicate-wild-ignore-table=mysql.* <br>replicate-wild-ignore-table=sys.* #定义relay_log的位置和名称 relay_log=/usr/local/mysql/mysql-relay-bin #定义relay_log的索引文件位置和名称 relay_log_index=/usr/local/mysql/mysql-relay-bin.index #服务器在关闭它之前在一个连接上等待行动的秒数。 wait_timeout=360000

4.5重启服务：

?

1	service mysqld restart　#主备服务器中执行

如果有多个从服务器上，那么每个服务器上配置的server-id都必须不一致。从服务器上没必要配置log-bin，当然也可以配置log-bin选项，因为可以在从服务器上进行数据备份和灾难恢复，或者某一天让这个从服务器变成一个主服务器

如果主服务器导出了数据，下面就导入该文件，如果主服务器没有数据，就忽略这一步

?

1	mysql -uroot -p'123456' -S /data/3306/data/mysql.sock < /server/backup/mysql_bak.2015-07-01.sql　　　　#服务器中执行

如果从主服务器上拿过来的是压缩文件，就先解压再导入

配置同步参数，登陆mysql，输入如下信息：

?

1	change master to master_host='主库IP', master_port=主库端口, master_user='同步用户', master_password='同步用户的密码', master_log_file='binlog.000002',master_log_pos=1797;  #备服务执行start slave;

　#提示报错：Slave failed to initialize relay log info structure from the repository
　#解决
　reset slave;
　start slave;

启动主从同步进程　　

?

1	start slave;　　　　#备服务执行

检查状态

?

1	show slave status \G　　#备服务执行

?

1 2 3

Slave_IO_Running: Yes #I/O线程状态OK    　　#负责从库去主库读取二进制日志，并写入到从库的中继日志。 Slave_SQL_Running: Yes #SQL线程状态OK   　　#负责将中继日志转为sql后执行。 Seconds_Behind_Master: 0 　　　　　　　　　　  #同步效率非常好,没有延迟

可能出现问题汇总（备）：　　

1、mysql主从复制异常Slave_IO_Running: NO　　

*原因一*：mysql5.6以上版本使用唯一uuid表示符，数据迁移是使用的物理备份，uuid会重复，修改uuid不一致即可

?

1 2 3 4

vim /var/lib/mysql/auto.cnf [auto] server-uuid=85dba00a-e0ef-11e9-b341-000c29812345 # 任意修改几位，重启mysql

*原因二*： 确认server-id 是否唯一， mysql 有可能并没有加载my.cnf 文件中的server-id　　

?

1 2 3 4 5 6 7 8 9 10 11 12

# 修改/etc/my.cnf server-id=2  #主从要不一致    mysql>  show variables like 'server_id'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id     | 2    | +---------------+-------+    mysql> set global server-id=3; # 如果与my.cnf中设置的不一样，请修改一致 # 重启mysql

*原因三* ：这种原因有可能是主库重启造成的二进制文件位置从库于主库不一致。

解决: 需要手动将这些文件二进制日志mysql-bin.00000x,mysql-bin.index删除，rm -rf 掉 在重启数据库

*原因四* ：机器之间网络不通。

*原因五* ：一般是事务回滚造成的，解决办法：

?

1 2 3

mysql> stop slave ;  mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1; mysql> start slave ;

1、mysql主从复制异常Slave_SQL_Running: NO  ；Seconds_Behind_Master: null

*原因一*：1.程序可能在slave上进行了写操作 ；2.也可能是slave机器重起后，事务回滚造成的.

?

1 2 3 4

解决办法： mysql> stop slave ;　　　　　　#备 mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1; mysql> start slave ;

*原因二*：File和Position对应的值有所改变　　

?

1 2 3

show master status;　　　　#主 stop slave；　　　　#备 change master to master_host='主库IP', master_port=主库端口, master_user='同步用户', master_password='同步用户的密码', master_log_file='binlog.000002',master_log_pos=1797;  #备

5.模拟测试　　

主库 #模拟产生数据

?

1 2 3 4 5 6

#主库 mysql> create database slave; #创建数据库 mysql> use slave; #选择数据库 mysql> create table a(a int); #创建表 mysql> insert into a values(1); #插入数据 mysql> insert into a values(2); #插入数据

所有从库 #查看数据同步状态　　

?

1 2 3

mysql> show databases; #查看当前有哪些数据库(主库新建的slave库同步过来了) mysql> use slave; #选择数据库 mysql> select * from a; #查看a表数据

如果从库的同步进程停止了，主库的操作还会往从库中同步吗？会丢数据吗？

所有从库 #模拟服务出现问题

?

1	service mysql stop #停止mysql服务，模拟从库出现问题

主库 #从库出现问题，此期间主库正常操作　

?

1 2 3

use slave; #选择数据库 insert into a values(3); #插入数据 insert into a values(4); #插入数据

所有从库 #问题解决，查看有没有丢数据　　

?

1 2 3

service mysql start #启动Mysql服务 mysql >use slave; #选择数据库 mysql >select * from a; #数据自动同步过来了,主库的数据没有丢失

6.结语　　

看到这里的同学，你一定会对 MySQL 复制技术有一个清晰的了解，认识到复制是数据同步的基础，而二进制日志就是复制的基石。

转载请备注原文链接！！！