本文详细介绍了MySQL主从复制的原理、配置步骤及常见问题解决，涵盖语句、行和混合复制类型，以及如何通过三线程实现数据同步。此外，还深入探讨了读写分离的实现，包括Atlas数据中间层的安装与配置，实现数据库压力分担。
文章目录
案例概述
在企业网站中，后端MySQL数据库只有一台时，会有以下问题
- 单点故障，服务不可用
- 无法处理大量的并发数据请求
- 数据丢失—大灾难
改造方法
- 增加MySQL数据库服务器，对数据进行备份，形成主备
- 确保主备MySQL数据库服务器数据是一样的
- 主服务器宕机了，备份服务器继续工作，数据有保障
MySQL主从复制与读写分离是密切相关的
MySQL主从复制原理
MySQL主从复制的类型
- 基于语句的复制（默认）
在主服务器上执行的语句，从服务器执行同样的语句 - 基于行的复制
把改变的内容复制到从服务器 - 混合类型的复制
一旦发现基于语句无法精确复制时，就会采用基于行的复制
主从复制的工作过程
- 3个线程
- MySQL通过3个线程来完成主从库间的数据复制
- Binlog Dump线程跑在主库上
- I/0线程和 SQL线程跑在从库上
当在从库上启动复制时,首先创建I/0程连接主库, 主库随后创建 Binlog Dump线程读取数据库事件并发送给 I/0线程, I0线程获取到事件数据后更新到从库的中继日志 Relay Log中去,之后从库上的 SQL线程读取中继日志RelayLog中更新的数据库事件并应用。
案例实施
- 案例拓扑图
搭建Mysql主从复制
- 建立时间同步环境，在主节点上搭建时间同步服务器
- 安装NTP
yum -y install ntp- 将主服务器的时间与网络时间同步
ntpdate ntp.aliyun.com- 配置NTP
vim /etc/ntp.conf 添加如下两行 server 127.127.1.0 开启本地ntp服务 fudge 127.127.1.0 stratum 8 设置时区- 重启服务，设为开启自启
systemctl restart ntpd systemctl enable ntpd - 在从节点上进行时间同步
yum -y install ntp
ntpdate 192.168.137.95
vim /etc/ntp.conf 添加ntp服务器
#server 0.centos.pool.ntp.org iburst
#server 1.centos.pool.ntp.org iburst
... 将默认的注释掉
server 192.168.137.95 iburst
systemctl restart ntpd
systemctl enable ntpd
-
安装MySQL，略过
-
配置MySQL主服务器
- 在/etc/my.cnf中修改或者增加以下内容
vim /etc/my.cnf server_id = 11 （修改） log_bin = master-bin （修改） log-slave-updates = true （增加)- 重启MySQL服务
systemctl restart mysqld- 登录MySQL程序，给从服务器授权
mysql -u root -p mysql> GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'192.168.137.%' IDENTIFIED BY '123456'; mysql> FLUSH PRIVILEGES; mysql> show master status; +-------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------------+----------+--------------+------------------+-------------------+ | master-bin.000001 | 412 | | | | +-------------------+----------+--------------+------------------+-------------------+ 其中File列显示日志名。Position列显示偏移量，这两个值在后面配置从服务器是会用到。slave应从该点在Master上进行新的更新。 -
配置从服务器
- 在/etc/my.cnf中修改或增加以下内容
server_id = 22 (修改) relay-log=relay-log-bin （增加） relay-log-index=slave-relay-bin.index （增加）- 重启Mysql服务
systemctl restart mysqld- 登录mysql ,配置同步
按主服务器结果更改下面命令中master_log_file和master_log_pos的参数
mysql -u root -p mysql> change master to master_host='192.168.9.100', master_user='myslave', master_password='123456', master_log_file='master-bin.000002',master_log_pos=120;- 启动同步
mysql>start slave;- 查看Slave状态，确保一下两个值为YES
-
验证主从复制效果
- 在主、从服务器上登录mysql
mysql -uroot -p mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | | zzq | +--------------------+ 5 rows in set (0.00 sec)主、从服务器执行结果应该一致
- 在主服务器上新建数据库
mysql> create database ABC; Query OK, 1 row affected (0.00 sec)- 在主、从服务器上查看数据库，显示数据库相同，则主从复制成功
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | ABC | | mysql | | performance_schema | | test | | zzq | +--------------------+ 6 rows in set (0.00 sec)- 搭建完成后，可以在主库show slave hosts查看有哪些从库节点
mysql> show slave hosts;
容易遇到的问题及解决
- 问题：Slave_IO_Running: No
- 可能的原因：二进制安装的mysql的UUID重复
- 解决UUID重复的问题：
rm -f /usr/local/mysql/data/auto.cnf 删除记录UUID的文件
systemctl restart mysqld 重启后会自动生成新的auto.cnf
然后可以重新配置主从备份
读写分离
原理
- 读写分离就是只在主服务器上写，只在从服务器上读
- 主数据库处理事务性查询，而从数据库处理select查询
- 数据库复制被用来把事务性查询导致的变更同步到集群中的从数据库
案例概述
- 在企业应用中，在大量的数据请求下，单台数据库将无法承担所有的读写操作
- 配置多台数据库服务器以实现读写分离
- 本案例在主从复制的基础上实现读写分离
案例实施
Atlas概述
- Atlas简介
Atlas是由 Qihoo 360公司Web平台部基础架构团队开发维护的一个基于MySQL协议的数据中间层项目。它在MySQL官方推出的MySQL-Proxy 0.8.2版本的基础上，修改了大量bug，添加了很多功能特性。目前该项目在360公司内部得到了广泛应用，很多MySQL业务已经接入了Atlas平台，每天承载的读写请求数达几十亿条。同时，有超过50家公司在生产环境中部署了Atlas，超过800人已加入了我们的开发者交流群，并且这些数字还在不断增加。 - Atlas主要功能
- 读写分离
- 从库负载均衡
- IP过滤
- 自动分表
- DBA可平滑上下线DB
- 自动摘除宕机的DB
- Atlas相对于官方MySQL-Proxy的优势
- 将主流程中所有Lua代码用C重写，Lua仅用于管理接口
- 重写网络模型、线程模型
- 实现了真正意义上的连接池
- 优化了锁机制，性能提高数十倍
准备工作
所有服务器关闭Firewalld或者进行规则设置
搭建Mysql主从复制环境
Atlas服务器环境安装
- 环境准备
| 服务器 | IP |
|---|---|
| MySQL主 | 192.168.137.95 |
| MySQL从1 | 192.168.137.96 |
| MySQL从2 | 192.168.137.97 |
| MySQL Atlas代理服务器 | 192.168.137.98 |
安装并配置Atlas软件
[root@localhost ~]# cd /usr/src/
[root@localhost src]# ls
Atlas-2.2.1.el6.x86_64.rpm debug kernels
[root@localhost src]# rpm -ivh Atlas-2.2.1.el6.x86_64.rpm
[root@localhost src]# cd /usr/local/mysql-proxy/
[root@localhost mysql-proxy]# ls
bin conf lib log
- 安装好后，会默认在”/usr/local/mysql-proxy”下生成4个文件夹，以及需要配置的文件，各目录说明：
- bin目录下放的是可执行文件
- “encrypt”是用来生成MySQL密码加密的，在配置的时候会用到
- “mysql-proxy”是MySQL自己的读写分离代理
- “mysql-proxyd”是360的，后面有个“d”，服务的启动、重启、停止。都是用他来执行的
- conf目录下放的是配置文件
- “test.cnf”只有一个文件，用来配置代理的，可以使用vim来编辑
- lib目录下放的是一些包，以及Atlas的依赖
- log目录下放的是日志，如报错等错误信息的记录
- 配置读写分离
- 配置master，slave1，slave2中开放权限给Atlas：
grant all on *.* to test@'192.168.137.%' identified by '123.com';
flush privileges;
- 加密用户名密码
进入bin目录，使用encrypt来对数据库的密码进行加密，我的MySQL数据的用户名是test，密码是123.com，我需要对密码进行加密
[root@localhost bin]# ./encrypt 123.com
tF5TeinkMj8=
- 编辑test.cnf配置文件
vi /usr/local/mysql-proxy/conf/test.cnf
[mysql-proxy]
#带#号的为非必需的配置项目
#管理接口的用户名
admin-username = user
#管理接口的密码
admin-password = pwd
#Atlas后端连接的MySQL主库的IP和端口，可设置多项，用逗号分隔
proxy-backend-addresses = 192.168.137.95:3306
#Atlas后端连接的MySQL从库的IP和端口，@后面的数字代表权重，用来作负载均衡，若省略则默认为1，可设置多项，用逗号分隔
proxy-read-only-backend-addresses = 192.168.137.96:3306@1,192.168.137.97:3306@1
#用户名与其对应的加密过的MySQL密码，密码使用PREFIX/bin目录下的加密程序encrypt加密，下行的user1和user2为示例，将其替换为你的MySQL的用户名和加密密码！
pwds = test:tF5TeinkMj8=
#Atlas监听的工作接口IP和端口
proxy-address = 0.0.0.0:1234
#Atlas监听的管理接口IP和端口
admin-address = 0.0.0.0:2345
- 配置无误后，启动Atlas软件
/usr/local/mysql-proxy/bin/mysql-proxyd test start
/usr/local/mysql-proxy/bin/mysql-proxyd test stop
- 测试
- 在代理主机（Altas）上：
yum -y install mysql
mysql -h127.0.0.1 -P2345 -uuser -ppwd （用管理账号user登录2345管理接口IP和端口）
MySQL [(none)]> select * from help;
+----------------------------+---------------------------------------------------------+
| command | description |
+----------------------------+---------------------------------------------------------+
| SELECT * FROM help | shows this help |
| SELECT * FROM backends | lists the backends and their state |
| SET OFFLINE $backend_id | offline backend server, $backend_id is backend_ndx's id |
| SET ONLINE $backend_id | online backend server, ... |
| ADD MASTER $backend | example: "add master 127.0.0.1:3306", ... |
| ADD SLAVE $backend | example: "add slave 127.0.0.1:3306", ... |
| REMOVE BACKEND $backend_id | example: "remove backend 1", ... |
| SELECT * FROM clients | lists the clients |
| ADD CLIENT $client | example: "add client 192.168.1.2", ... |
| REMOVE CLIENT $client | example: "remove client 192.168.1.2", ... |
| SELECT * FROM pwds | lists the pwds |
| ADD PWD $pwd | example: "add pwd user:raw_password", ... |
| ADD ENPWD $pwd | example: "add enpwd user:encrypted_password", ... |
| REMOVE PWD $pwd | example: "remove pwd user", ... |
| SAVE CONFIG | save the backends to config file |
| SELECT VERSION | display the version of Atlas |
+----------------------------+---------------------------------------------------------+
16 rows in set (0.00 sec)
MySQL [(none)]> select * from backends;
+-------------+--------------------+-------+------+
| backend_ndx | address | state | type |
+-------------+--------------------+-------+------+
| 1 | 192.168.9.100:3306 | up | rw |
| 2 | 192.168.9.3:3306 | up | ro |
| 3 | 192.168.9.4:3306 | up | ro |
+-------------+--------------------+-------+------+
3 rows in set (0.00 sec)
2.通过代理访问Mysql
[root@localhost bin]# mysql -h192.168.9.5 -utest -p123.com -P1234
MySQL [(none)]> select * from mytest.t1;
+-----+
| id |
+-----+
| 1 |
| 10 |
| 30 |
| 40 |
| 100 |
| 200 |
+-----+
6 rows in set (0.00 sec)
MySQL [(none)]> select * from mytest.t1;
+-----+
| id |
+-----+
| 1 |
| 10 |
| 20 |
| 40 |
| 100 |
| 200 |
+-----+
- 在MASTER/测试机上创建一个表
create database db_test;
use db_test;
create table zang (id int(10),name varchar(10),address varchar(20));
- 分别在两台从服务器上停止复制。
stop slave;
- 在主服务器上，插入数据
use db_test;
insert into zang values('1','zhang','this_is_master');
- 在从服务器上，手动插入其他内容
slave1：
use db_test;
insert into zang values('2','zhang','this_is_slave1');
slave2：
use db_test;
insert into zang values('3','zhang','this_is_slave2');
- 测试读操作
在测试机上第一次查询结果：
在测试机上第二次查询结果：
在测试机上第三次查询结果：
在client主机上插入一条语句：
insert into zang values（‘20’,‘hello’,‘write_test’);
在client上查询不到，最终只有在MASTER上才能看到这条语句内容，说明写操作只能在Master上。
由此验证：已经实现了MYSQL读写分离，目前所有的写操作都在MASTER主服务器上，用来避免数据的不同步，所有的读操作都分摊给了slave从服务器，用来分担数据库压力。
扫一扫
1209
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
