MySQL5.6主从同步

一、配置MySQL的主从同步有什么好处？

1--数据分布 (Data distribution )
2--负载平衡(load balancing)
3--数据备份(Backups) ，保证数据安全（最主要的作用）
4--高可用性和容错行(High availability and failover)
5--实现读写分离，缓解数据库压力

二 、MySQL主从同步实现原理

master服务器将数据的改变记录二进制binlog日志，当master上的数据发生改变时，则将其改变写入二进制日志中；salve服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变，如果发生改变，则开始一个I/OThread请求master二进制事件，同时主节点为每个I/O线程启动一个dump线程，用于向其发送二进制事件，并保存至从节点本地的中继日志中，从节点将启动SQL线程从中继日志中读取二进制日志，在本地重放，使得其数据和主节点的保持一致，最后I/OThread和SQLThread将进入睡眠状态，等待下一次被唤醒。
注意几点：
1--master将操作语句记录到binlog日志中，然后授予slave远程连接的权限（master一定要开启binlog二进制日志功能；通常为了数据安全考虑，slave也开启binlog功能）。
2--slave开启两个线程：IO线程和SQL线程。其中：IO线程负责读取master的binlog内容到中继日志relay log里；SQL线程负责从relay log日志里读出binlog内容，并更新到slave的数据库里，这样就能保证slave数据和master数据保持一致了。
3--Mysql复制至少需要两个Mysql的服务，当然Mysql服务可以分布在不同的服务器上，也可以在一台服务器上启动多个服务。
4--Mysql复制最好确保master和slave服务器上的Mysql版本相同（如果不能满足版本一致，那么要保证master主节点的版本低于slave从节点的版本）
5--master和slave两节点间时间需同步

Mysql复制的流程图如下：

三 、系统环境

操作系统：CentOS 7.5
数据库版本：mysql-5.6.40
数据库地址：
172.16.3.91（主）
172.16.3.94（从）

在这里我们主要分四个步骤

第一步

我们来查看一下主从同步各自服务器的防火墙是否允许3306端口通讯

1 、 在主mysql-master 

[root@sumei-mysql-master ~]# iptables -L -n |grep 3306
ACCEPT     tcp  --  172.16.3.0/24        0.0.0.0/0            tcp dpt:3306

2、在从mysql-slave

[root@sumei-mysql-slave ~]# iptables -L -n |grep 3306
ACCEPT     tcp  --  172.16.3.0/24        0.0.0.0/0            tcp dpt:3306

第二步  1、主配置文件的操作

[mysqld]
#开启二进制日志
log-bin=mysql-bin
#标识唯一id（必须），一般使用ip最后位
server-id=1
#不同步的数据库，可设置多个
binlog-ignore-db=mysql
#指定需要同步的数据库（和slave是相互匹配的），可以设置多个
binlog-do-db=test

#设置存储模式不设置默认
binlog_format=MIXED
#日志清理时间
expire_logs_days=7
#日志大小
max_binlog_size=100m
#缓存大小
binlog_cache_size=4m
#最大缓存大小
max_binlog_cache_size=521m

2、 重启数据库

[root@sumei-mysql-master ~]# service mysql restart
Shutting down MySQL.... SUCCESS! 
Starting MySQL. SUCCESS!

3、数据库打包备份

[root@sumei-mysql-master mnt]# mysqldump -u root -p -A --events -B -x --master-data=1|gzip >/mnt/$(date +%F).sql.gz
Warning: Using a password on the command line interface can be insecure.

4、迁移数据到mysql-slave上

[root@sumei-mysql-master mnt]# scp -pr 2019-07-07.sql.gz root@172.16.3.94:/mnt/
root@172.16.3.94's password: 
2019-07-07.sql.gz                                                                                     100%  300MB 136.0MB/s   00:02

5、修改从的数据量配置

cp -pr /etc/my.cnf /etc/my.$(date +%F).cnf 

server-id=3
binlog-ignore-db=information_schema
binlog-ignore-db=cluster
binlog-ignore-db=mysql#与主库配置一直
replicate-do-db=test
replicate-ignore-db=mysql
log-slave-updates
slave-skip-errors=all
slave-net-timeout=60

6,查看 主库的binlog 号

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000012 |      619 | sumei        | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

第三步  数据库的操作

 1、在主库上创建账号授权获取主的日志权限
grant replication slave ,replication client on *.* to slave@'172.16.3.%' identified by "wISdauPk8upAoxbN";
flush privileges; #权限修改立即生效
flush tables with read lock; #锁定数据库为只读，确保备份数据一致性

    2、 删除多余数据库，导入数据，删除部分不予演示,sql的位置请自行指定

[root@sumei-mysql-slave opt]# mysql -u root -p'12345' </opt/2019-07-0711.sql 
Warning: Using a password on the command line interface can be insecure.

   3、从库授权

[root@sumei-mysql-slave opt]# mysql -u root -p'12345'
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.40-log Source distribution
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> change master to master_host='172.16.3.91',master_user='slave',master_password='wISdauPk8upAoxbN',master_log_file='mysql-bin.000012', master_log_pos=619;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    6
Current database: mysql

Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> start slave;
Query OK, 0 rows affected (0.02 sec)

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.3.91
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000016
          Read_Master_Log_Pos: 427
               Relay_Log_File: sumei-mysql-slave-relay-bin.000002
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000016
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: sumei_cms_new,sumei_ad,sumei_photo

5  碰到的问题

注：如果Slave_IO_Running: No　　出现下面的错误
1
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
　　说明主服务器的UUID和从服务器的UUID重复，更改方式
2
[root@localhost ~]# vim /usr/local/mysql/data/auto.cnf　　#这是我的安装路径修改auto.cnf的server-uuid
　　注：如果Slave_IO_Running: Connecting 并出现下面错误　　
3
error connecting to master 'root@172.16.3.91:3306' - retry-time: 60  retries: 1
　　解决方法，查看主库是否授权，查看change master to... 是否有用户密码ip填写错误
 
 　　注：如果Slave_IO_Running: No 出现下面错误
Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
　　解决方法：复位
mysql>stop slave;　　//停止
mysql>reset slave;　　//清空
mysql>start slave;　　//开启

 6、从数据量5.5  迁入到 5.6.40  碰到的问题

(HY000): Column count of mysql.user is wrong. Expected 45, found 43. Created with MySQL 5
[root@sumei-mysql-master ~]# /usr/local/mysql/bin/mysql_upgrade -u root -p'Cf10lbryou'
Warning: Using a password on the command line interface can be insecure.
Looking for 'mysql' as: /usr/local/mysql/bin/mysql
Looking for 'mysqlcheck' as: /usr/local/mysql/bin/mysqlcheck
Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/tmp/mysql.sock' 
Warning: Using a password on the command line interface can be insecure.
Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/tmp/mysql.sock' 
Warning: Using a password on the command line interface can be insecure.
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.host                                         OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv

感谢您的来访，获取更多精彩文章请收藏本站。