- A+
目录
5. 主从复制配置
1. 主从MySQL主从复制介绍
MySQL的主从复制方案，其实就和文件及文件系统级别同步是类似的，都是数据的传输，只不过MySQL无需借助第三方工具，而是自带的同步复制功能，另外一点，MySQL的主从复制并不是磁盘上文件直接同步，而是逻辑的binlog日志同步到本地在应用执行的过程。
MySQL数据库支持单向、双向、链式级联等不同场景的复制，在复制过程中，一台服务器充当主服务器（Master），而一个或多个其它的服务器充当从服务器（Slave）。
复制可以单向：M==>S；可以双向：M<==>S；当然也可以多M环装同步等。
如果设置了链式级联复制，那么，从（slave）服务器本身除了充当从服务器外，也会同时充当其下面从服务器的主服务器，链式级联复制类似A==>B==>C==>D的复制形式。
下面是MySQL各种同步结构的逻辑图：
1. 单向主从复制逻辑图，此架构只能在Master端进行数据写入
2. 双向主主同步逻辑图，此架构可以在Master1端或Master2端进行数据写入
3. 线性级联单向双主同步逻辑图，此架构只能在Master端进行数据写入
4. 环装级联单向多主同步逻辑图，任意一个点都可以写入数据
5. 环装级联单向多主多从同步逻辑图，此架构只能在任意一个master端进行数据写入
6. MySQL官方同步架构图
在当前的生产环境中，MySQL主从复制都是异步的复制方式，既不是严格实时的数据同步。
2. MySQL主从复制的企业应用场景
MySQL主从复制集群功能使得MySQL数据库支持大规模高并发读写称为可能，同时有效的保护了物理服务器宕机场景的数据备份。
应用场景1：从服务器作为主服务器的实时数据备份
主从服务器架构的设置可以大大加强MySQL数据库架构的健壮性，例如：当主服务器出现问题时，我们可以人工或设置自动切换到从服务器继续提供服务，此时从服务器的数据与宕机时的数据库几乎是一致的。
这类似NFS存储数据通过inotify+rsync同步到备份的NFS服务器，只不过MySQL的复制方案是其自带的工具。
利用MySQL的复制功能进行数据备份时，在硬件故障、软件故障的场景下，该数据备份是有效的，但对于人为执行drop、delete等语句删除数据的情况，从库的备份功能就没用了，因为从服务器也会执行删除的语句。
应用场景2：主从服务器实现读写分离，从服务器实现负载均衡
主从服务器架构可通过（PHP、java等）或代理软件（mysql-proxy、Amoeba）实现对用户（客户端）的请求读写分离，既让从服务器处理用户的select查询请求，降低用户查询响应时间，以及同时读写在主服务器上带来的访问压力，对于更新的数据（例如：update、insert、delete语句），则仍然交给主服务器处理，确保主服务器和从服务器保持实时同步。
应用场景3：把多个从服务器根据业务重要性进行拆分访问
可以把几个不同的从服务器，根据公司的业务进行拆分，例如：在为外部用户提供查询服务的从服务器，在内部DBA用来数据备份的从服务器，还有为公司内部人员提供访问的后台、脚本、日志分析及供开发人员查询使用的从服务器，这样拆分除了减轻主服务器的压力外，还可以是数据库对外部用户浏览，内部用户业务处理及DBA人员的备份等互不影响。
3. 实现MySQL主从读写分离的方案
（1）通过程序实现读写分离（性能和效率最佳，推荐）
PHP和java程序都可以通过设置多个连接文件轻松地实现对数据库的读写分离，即当语句关键字为select时，就去连接读库的连接文件，若为update、insert、delete时，则连接写库的文件。
通过程序实现读写分离的缺点就是需要开发人员对程序进行改造，使其对下层不透明，但这种方式更容易开发和实现，适合互联网业务场景。
（2）通过开源的软件实现读写分离
MySQL-Proxy、Amoeba等代理软件也可以是实现读写分离功能，这些软件的稳定性和功能一般，不建议生产使用，绝大多数公司常用的还是在应用端发程序实现读写分离。
（3）大型门户独立开发DAL层综合软件
百度、阿里等大型门户都有开发牛人，会花大力气开发适合自己业务的读写分离，负载均衡，监控报警，自动扩容，自动收缩等一系列功能的DAL层软件。
4. MySQL主从复制原理
简单描述MySQL Replication的复制原理过程：
1. 在Slave服务器上执行start slave命令开启主从复制开关，开始进行主从复制。
2. 此时，slave服务器的I/O线程会通过在Master上已经授权的复制用户权限请求选择Master服务器，并请求从指定binlog日志文件的指定位置，之后，之后开始发送binlog日志内容。
3. Master服务器接收来自slave服务器的IO线程的请求后，其上负责复制的I/O线程会根据slave服务器的io线程请求的信息分批读取指定binlog日志文件指定位置之后的binlog日志信息，然后返回给slave端的I/O线程，返回的信息中除了binlog日志内容外，还有在master服务器端记录的新的binlog文件名称，以及在新的binlog中的下一个指定的跟新位置。
4. 当slave服务器的io线程获取带master服务器上io线程发送的日志内容，日志文件及位置点后，会将binlog日志内容写到slave端自身的relay log（即中继日志）的最末端，并将新的binlog日志文件名和位置点记录到master-info文件中，以便下一次读取master端新的binlog日志是能够告诉master服务端从新的binlog日志指定文件及位置开始请求新的binlog日志内容。
5. slave服务器端的SQL线程会实时监测本地relay log中的io线程新增加的日志内容。
6. 然后及时的把relay.log文件中的内容解析成SQL语句，并且自身slave服务器上按解析sql语句的位置顺序执行应用这些SQL语句，并在relay-log.info中记录当前应用中继日志文件名及位置点。
MySQL主从复制原理的重点小结：
- 主从复制是异步的逻辑的SQL语句级的复制
- 复制时，主库有一个线程，从库有两个线程，即I/O和SQL线程
- 实现主从复制的前提条件时主库开启记录binlog功能
- 作为复制的所有MySQL节点的sever id都不能相同
- binlog文件只记录对数据有更改的SQL语句（来自主数据库内容的变更），不记录任何查询（如：select、show）语句。
5. 主从复制配置
实现主从复制条件
1. 开启binlog功能。
2. 主库建立同步账号。
3. 从库配置master.info（change master..）。
4. start slave复制开关。
5.1 MySQL主从配置
如图：
本文部署环境：
|
HostName |
IP |
服务 |
角色 |
系统版本 |
|
urcar0 |
172.16.83.200 |
MySQL-5.6.21 |
主库 |
CentOS-6.x |
|
urcar2 |
172.16.83.202 |
从库 |
||
|
urcar3 |
172.16.83.203 |
从库 |
本文部署环境补充说明：
- 本公司部署MySQL一主两从，使用服务器3台。
- 3台服务器均以安装并正常使用的MySQL-5.6.21，所以本文直接部署一主两从
- 如果开主库iptables防火墙开启请注意添加相应规则。
（1）主库操作
1. 先确认主库binlog功能是否开启和各个实例server-id实例的不同
[root@urcar0 ~]# egrep "log-bin|server-id" /etc/my.cnf log-bin = /app/mysql/mysql-bin server-id = 1 [root@urcar2 ~]# egrep "log-bin|server-id" /etc/my.cnf log-bin = /app/mysql/mysql-bin server-id = 2 [root@urcar3 ~]# egrep "log-bin|server-id" /etc/my.cnf log-bin = /app/mysql/mysql-bin server-id = 3
2. 主库授权同步的用户
[root@urcar0 ~]# mysql -uroot -p mysql> grant replication slave on *.* to rep@'172.16.83.%' identified by '******'; Query OK, 0 rows affected (0.00 sec) mysql> select user,host from mysql.user; #查看新建rep用户 mysql> flush privileges; #刷新权限 mysql> show grants for rep@'172.16.83.%'; #查看用户rep权限
3. 备份前对主数据库锁表（只读）
mysql> flush table with read lock; Query OK, 0 rows affected (0.00 sec) #测试：可单开一个窗口，创建一个数据库（创建不了，因为表锁了） mysql> create database testdatabase; ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock
4. 查看全备与增量之间的临界点
mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 6903857 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
5. 开始备份
#创建备份目录 [root@urcar0 ~]# mkdir /home/baskup #全备： [root@urcar0 ~]# mysqldump -uroot -p -B -A --events|gzip > /home/baskup/rep_bak_$(date +%F).sql.gz Enter password: [root@urcar0 ~]# ll /home/baskup/ total 25828 -rw-r--r-- 1 root root 26445066 Mar 12 10:33 rep_bak_2018-03-12.sql.gz
6. 因为数据已经备份完，所以可以开放用户写入功能，此时会发现测试窗口的数据会写入
mysql> unlock tables; Query OK, 0 rows affected (0.00 sec)
7. 将备份的数据库全备传到葱库urcar2和urcar3主机
#scp -rp /home/baskup/rep_bak_2018-03-12.sql.gz root@172.16.83.202:/home/backup/ rep_bak_2018-03-12.sql.gz 100% 25MB 25.2MB/s 00:01 #scp -rp /home/baskup/rep_bak_2018-03-12.sql.gz root@172.16.83.203:/home/backup/ rep_bak_2018-03-12.sql.gz 100% 25MB 25.2MB/s 00:00
（2）从库操作
现在值操作 urcar2 主机，urcar2操作完成后，urcar3和urcar2一样操作即可
1. 确保所有用户server-id不同（主库已经确认过）
[root@urcar2 ~]# egrep "log-bin|server-id" /etc/my.cnf log-bin = /app/mysql/mysql-bin server-id = 2
2. 把主库的全备导入到从库
[root@urcar2 ~]# gzip -d /home/backup/rep_bak_2018-03-12.sql.gz [root@urcar2 ~]# ll /home/backup/ total 90544 -rw-r--r-- 1 root root 92712967 Mar 12 10:33 rep_bak_2018-03-12.sql [root@urcar2 ~]# mysql -uroot -p </home/backup/rep_bak_2018-03-12.sql Enter password:
3. 找位置点，开始配置master
info
[root@urcar2 ~]# mysql -uroot -p
mysql> CHANGE MASTER TO
-> MASTER_HOST='172.16.83.200', #<== 主库IP
-> MASTER_PORT=3306, #<== 主库MySQL端口
-> MASTER_USER='rep', #<== 这里是主库上建立的用于复制的用户rep
-> MASTER_PASSWORD='******', #<== rep用户密码
-> MASTER_LOG_FILE=' mysql-bin.000001', #<== 这里是show master status; 时看到的临界点binlog文件，注意，这里不能多空格
-> MASTER_LOG_POS=6903857; #<== 这是show master status; 查看二进制文件偏移量，注意，不能多空格
Query OK, 0 rows affected, 2 warnings (0.44 sec)
参数：
CHANGE MASTER TO MASTER_HOST='172.16.83.200', MASTER_PORT=3306, MASTER_USER='rep', MASTER_PASSWORD='******', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=6903857;
4. 开启同步开关
mysql> start slave; Query OK, 0 rows affected (0.05 sec)
5. 查看同步状态，看到以下黄色部分是以下状态就算成功
mysql> show slave status\G
*************************** 1. row ***************************
-----------------------省略部分内容------------
Relay_Master_Log_File: mysql-bin.000017
Slave_IO_Running: Yes #<== 看这IO线程yes和SQLyes就成功了
Slave_SQL_Running: Yes
Replicate_Do_DB:
-------------------------------------省略-----------
Seconds_Behind_Master: 0 #<== 主到从复制的延迟
1 row in set (0.00 sec)
或者使用命令查看：
[root@urcar2 ~]# mysql -uroot -p -e "show slave status\G"| \ egrep -i "_running|Behind " Slave_IO_Running: Yes Slave_SQL_Running: Yes Seconds_Behind_Master: 0
到这里MySQL主从配置就成功啦一半嘞，去urcar3上面做相同的5步操作即可。
6. 测试
在主库创建一个数据库查看葱库是否能同步成功
[root@urcar0 ~]# mysql -uroot -p mysql> create database testdatabase; Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | devops | | mysql | | performance_schema | | testdatabase | | zabbix | +--------------------+ 6 rows in set (0.00 sec)
在从库查看：
[root@urcar2 ~]# mysql -uroot -p -e 'show databases;' Enter password: +--------------------+ | Database | +--------------------+ | information_schema | | devops | | mysql | | performance_schema | | testdatabase | #同步成功 | zabbix | +--------------------+
------------------成功！！！-------------------
