@mysql数据库读写分离

0|1文章目录

1|0一、主库切换优先级

1|11.数据量切换测试

 
1.做好主从

2.创建一个表
	use test;
	create table linux(id int not null primay key auto_increment,name varchar(10));
	
3.写一个一直添加数据的脚本
	#!/bin/bash
	while true;do
		mysql -e "insert into test.oldboy(name) values(lhd)"
		sleep 1
	done

4.执行脚本
5.一台从库不停数据，一台数据库将IO线程停止
	stop slave io_thread;
6.停止主库查看切换情况

1|22.优先级切换测试

 
1.配置优先级测试
	candidate_master=1
	check_repl_delay=0

1|33.如果断电或者断网,binlog如何保存?

 
[root@db03 ~]# vim /service/mha/app1.cnf
... ...
[binlog1]
no_master=1
hostname=172.16.1.53
master_binlog_dir=/data/mysql/binlog/
... ...

#创建备份binlog目录
[root@mysql-db03 ~]# mkdir -p /data/mysql/binlog/
#进入该目录
[root@mysql-db03 ~]# cd /data/mysql/binlog/
#备份binlog
[root@mysql-db03 binlog]# mysqlbinlog  -R --host=10.0.0.51 --user=mha --password=mha --raw  --stop-never mysql-bin.000001 &
#启动mha
[root@mysql-db03 binlog]# nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &

#查看binlog目录中的binlog
[root@mysql-db03 binlog]# ll
total 44
-rw-r--r-- 1 root root 285 Mar  8 03:11 mysql-bin.000001
#登录主库
[root@mysql-db01 ~]# mysql -uroot -poldboy123
#刷新binlog
mysql> flush logs;
#再次查看binlog目录
[root@mysql-db03 binlog]# ll
total 48
-rw-r--r-- 1 root root 285 Mar  8 03:11 mysql-bin.000001
-rw-r--r-- 1 root root 143 Mar  8 04:00 mysql-bin.000002

2|0二、VIP漂移

2|11.VIP漂移的两种方式

 
1.通过keepalived的方式
2.通过MHA自带的脚本

2|22.配置MHA读取脚本

 
#编辑配置文件
[root@db03 ~]# vim /service/mha/app1.cnf 
[server default]
master_ip_failover_script=/service/mha/master_ip_failover

2|33.编写脚本

 
[root@db03 ~]# cp ~/mha4mysql-manager-0.56/samples/scripts/master_ip_failover /service/mha/
或者上传现成的脚本
[root@db03 mha]# rz master_ip_failover
[root@db03 mha]# chmod 755 master_ip_failover

#编辑脚本
[root@db03 mha]# vim master_ip_failover
#在my变量下面添加以下内容
my $vip = '10.0.0.55/24';
my $key = '1';
my $ssh_start_vip = "/sbin/ifconfig eth1:$key $vip";
my $ssh_stop_vip = "/sbin/ifconfig eth1:$key down";

[root@db03 mha]# yum install -y dos2unix
[root@db03 mha]# dos2unix master_ip_failover 
dos2unix: converting file master_ip_failover to Unix format ...

2|44.手动绑定VIP

 
#手动将网卡绑定在当前的主库上
[root@db01 ~]# ifconfig eth1:1 172.16.1.55/24
[root@db01 ~]# ip addr
3: eth1: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:f4:ca:65 brd ff:ff:ff:ff:ff:ff
    inet 172.16.1.51/24 brd 172.16.1.255 scope global noprefixroute eth1
       valid_lft forever preferred_lft forever
    inet 172.16.1.55/24 brd 172.16.1.255 scope global secondary eth1:1
       valid_lft forever preferred_lft forever
    inet6 fe80::20c:29ff:fef4:ca65/64 scope link 
       valid_lft forever preferred_lft forever

#删除网卡上VIP
[root@db01 ~]# ifconfig eth1:1 down

2|55.启动MHA

 
[root@db03 ~]# nohup masterha_manager --conf=/service/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /service/mha/manager.lognager.log 2>&1 &

#正常启动前提:
	1.语法必须正确
	2.授权要正确
		[root@db03 mha]# chmod 755 master_ip_failover
	3.脚本格式要正确
		[root@db03 mha]# dos2unix master_ip_failover

2|66.测试VIP漂移

 
#查看从库信息
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.1.51
                  Master_User: rep
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

#查看主库ip
[root@db01 ~]# ip a
3: eth1: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:f4:ca:65 brd ff:ff:ff:ff:ff:ff
    inet 172.16.1.51/24 brd 172.16.1.255 scope global noprefixroute eth1
       valid_lft forever preferred_lft forever
    inet 172.16.1.55/24 brd 172.16.1.255 scope global secondary eth1:1
       valid_lft forever preferred_lft forever
    inet6 fe80::20c:29ff:fef4:ca65/64 scope link 
       valid_lft forever preferred_lft forever
       
#停止主库
	[root@db01 ~]# systemctl stop mysql
	
#查看新主库IP
[root@db02 ~]# ip a
3: eth1: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:2b:e3:a1 brd ff:ff:ff:ff:ff:ff
    inet 172.16.1.52/24 brd 172.16.1.255 scope global noprefixroute eth1
       valid_lft forever preferred_lft forever
    inet 172.16.1.55/24 brd 172.16.1.255 scope global secondary eth1:1
       valid_lft forever preferred_lft forever
    inet6 fe80::20c:29ff:fe2b:e3a1/64 scope link 
       valid_lft forever preferred_lft forever

3|0三、atlas 读写分离

 
MySQL读写分离:
	1.官方提供一个:mysql-proxy
	2.Atlas

Atlas主要功能(代理)
	1.读写分离
	2.从库负载均衡
	3.IP过滤
	4.自动分表
	5.DBA可平滑上下线DB(不影响用户的体验,把你的数据库下线)
	6.自动摘除宕机的DB
	
Atlas相对于官方MySQL-Proxy的优势
	1.将主流程中所有Lua代码用C重写，Lua仅用于管理接口
	2.重写网络模型、线程模型
	3.实现了真正意义上的连接池
	4.优化了锁机制，性能提高数十倍

3|11.安装atlas

 
[root@db-03 ~]# rpm -ivh Atlas-2.2.1.el6.x86_64.rpm

#安装好文件
[root@db-03 ~]# cd /usr/local/mysql-proxy/
[root@db-03 mysql-proxy]# ll
total 0
drwxr-xr-x 2 root root  75 Jul 28 21:25 bin			#atlas的命令
drwxr-xr-x 2 root root  22 Jul 28 21:25 conf		#配置文件目录
drwxr-xr-x 3 root root 331 Jul 28 21:25 lib			#库文件
drwxr-xr-x 2 root root   6 Dec 17  2014 log			#日志文件

3|22.编辑配置文件

 
3.配置文件目录
[root@db-03 mysql-proxy]# cd conf/
[root@db-03 conf]# ll
total 4
-rw-r--r-- 1 root root 2810 Dec 17  2014 test.cnf

4.编辑配置文件
[root@m01 conf]# vim test.cnf 
[mysql-proxy]
#带#号的为非必需的配置项目
#管理接口的用户名
admin-username = user
#管理接口的密码
admin-password = pwd
#Atlas后端连接的MySQL主库的IP和端口，可设置多项，用逗号分隔
proxy-backend-addresses = 172.16.1.51:3306
#Atlas后端连接的MySQL从库的IP和端口，@后面的数字代表权重，用来作负载均衡，若省略则默认为1，可设置多项，用逗号分隔
proxy-read-only-backend-addresses = 172.16.1.52:3306,172.16.1.53:3306
#用户名与其对应的加密过的MySQL密码，密码使用PREFIX/bin目录下的加密程序encrypt加密，下行的user1和user2为示例，将其替换为你的MySQL的用户名和加密密码！
pwds = user1:+jKsgB3YAG8=, user2:GS+tr4TPgqc=  （一般情况 配置程序连接数据库的用户）
#工作线程数，对Atlas的性能有很大影响，可根据   企业中的情况  设置 主机的内核数
event-threads = 8
#日志级别，分为message、warning、critical、error、debug五个级别
log-level = error
#日志存放的路径
log-path = /usr/local/mysql-proxy/log
#SQL日志的开关，可设置为OFF、ON、REALTIME，OFF代表不记录SQL日志，ON代表记录SQL日志，REALTIME代表记录SQL日志且实时写入磁盘，默认为OFF
sql-log = ON
#慢日志输出设置。当设置了该参数时，则日志只输出执行时间超过sql-log-slow（单位：ms)的日志记录。不设置该参数则输出全部日志。
sql-log-slow = 10
#实例名称，用于同一台机器上多个Atlas实例间的区分
instance = test    （可以启动多个Atlas实例，配置文件也要修改名字）
#Atlas监听的工作接口IP和端口
proxy-address = 0.0.0.0:1234
#Atlas监听的管理接口IP和端口
admin-address = 0.0.0.0:2345
#默认字符集，设置该项后客户端不再需要执行SET NAMES语句
charset = utf8
#允许连接Atlas的客户端的IP，可以是精确IP，也可以是IP段，以逗号分隔，若不设置该项则允许所有IP连接，否则只允许列表中的IP连接
client-ips = 127.0.0.1, 192.168.1

3|33.启动Atlas

 
#启动Atlas
[root@m01 conf]# /usr/local/mysql-proxy/bin/mysql-proxyd test start
OK: MySQL-Proxy of test is started

#验证启动（没起来他也显示OK）
[root@m01 conf]# ps -ef|grep [m]ysql-proxy
root       7574      1  0 01:45 ?        00:00:00 /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/test.cnf
root       7575   7574  0 01:45 ?        00:00:00 /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/test.cnf
[root@m01 conf]#

#检测端口是否启动
[root@m01 conf]# netstat -lntup|grep [m]ysql-proxy

3|44.Atlas使用

 
#进入管理接口
[root@db-03 conf]# mysql -uuser -ppwd -h127.0.0.1 -P2345

mysql> SELECT * FROM help;
+----------------------------+---------------------------------------------------------+
| command                    | description                                             |
+----------------------------+---------------------------------------------------------+
| SELECT * FROM help         | 查看帮助                                                |
| SELECT * FROM backends     | 查看后端的数据库                                        |
| SET OFFLINE $backend_id    | DBA可平滑下线DB(逻辑下线)                               |
| SET ONLINE $backend_id     | DBA可平滑上线DB(逻辑上线)                               |
| ADD MASTER $backend        | 添加主库:add master 172.16.1.57:3306                    |
| ADD SLAVE $backend         | 添加从库:add slave 172.16.1.58:3306                     |
| REMOVE BACKEND $backend_id | 摘除后端的数据库(逻辑摘除，主从状态仍然在，能够继续同步数据，只是不被请求)  |
| SELECT * FROM clients      | 查看客户端                                              |
| ADD CLIENT $client         | 添加客户端                                              |
| REMOVE CLIENT $client      | 移除客户端                                              |
| SELECT * FROM pwds         | 查看用户名和加密的密码                                  |
| ADD PWD $pwd               | 添加用户密码:add pwd root:123;(没加密的密码)（只能添加一个用户不能同名） |
| ADD ENPWD $pwd             | 添加用户密码:add pwd root:3yb5jEku5h4=;(加密的密码)     |
| REMOVE PWD $pwd            | 删除用户:remove pwd zls;                                |
| SAVE CONFIG                | 保存配置文件                                            |
| SELECT VERSION             | 查看版本信息                                            |
+----------------------------+---------------------------------------------------------+

#查看后端的数据库
mysql> SELECT * FROM backends;
+-------------+------------------+-------+------+
| backend_ndx | address          | state | type |
+-------------+------------------+-------+------+
|           1 | 172.16.1.51:3306 | up    | rw   |#可读可写
|           2 | 172.16.1.52:3306 | up    | ro   |#只读read-only
|           3 | 172.16.1.53:3306 | up    | ro   |#只读read-only
+-------------+------------------+-------+------+

4|0四、atlas的故障恢复+编写自动恢复脚本

 
atlas配置文件里面我们已经写死了，那如果出现问题我们怎么修改呢？？

4|11.思路

 
#MHA切换的时候,都需要做什么?
1.找到哪个从库被提升为主库
2.先在Atlas管理接口中,确认被提升为主库的从库,backend_id是什么
3.摘除remove backend $backend_id;
4.保存atlas的配置文件

4|22.编写脚本

 
#!/bin/bash
mysql_cli="mysql -uuser -ppwd -h172.16.1.55 -P2345"
mysql_cli="mysql -uuser -ppwd -h127.0.0.1 -P2345"

#找到哪个从库被提升为主库
new_master=$(ssh 172.16.1.53 "grep -i 'New master is' /service/mha/manager.log | sed -nr 's#.*is (.*)\(.*#\1#gp'")
new_master=$(ssh 172.16.1.53 "grep -i 'New master is' /service/mha/manager.log | awk -F "[ \(]+" '{print $12}'")

#被提升为主库的从库,backend_id是什么
new_backend_id=$($mysql_cli -e "select * from backends;" | grep $new_master | cut -f 1)

#摘除remove backend $backend_id;
$mysql_cli -e "remove backend $new_backend_id; save config;"

#找到down机的数据库
down_server=$(grep '^Master' /service/mha/manager.log | sed -nr '1s#.* (.*)\(.*#\1#gp')

ssh $down_server '/bin/sh /root/recover.sh

4|33.将脚本写入MHA定时任务

略


__EOF__

本文作者：ଲ小何才露煎煎饺
本文链接：https://www.cnblogs.com/zeny/p/15657656.html
关于博主：评论和私信会在第一时间回复。或者直接私信我。
版权声明：本博客所有文章除特别声明外，均采用 BY-NC-SA 许可协议。转载请注明出处！
声援博主：如果您觉得文章对您有帮助，可以点击文章右下角【推荐】一下。您的鼓励是博主的最大动力！
posted @   ଲ小何才露煎煎饺  阅读(37)  评论(0)  编辑  收藏  举报
编辑推荐：
· Linux系列：如何用heaptrack跟踪.NET程序的非托管内存泄露
· 开发者必知的日志记录最佳实践
· SQL Server 2025 AI相关能力初探
· Linux系列：如何用 C#调用 C方法造成内存泄露
· AI与.NET技术实操系列（二）：开始使用ML.NET
阅读排行：
· 【自荐】一款简洁、开源的在线白板工具 Drawnix
· 没有Manus邀请码？试试免邀请码的MGX或者开源的OpenManus吧
· 园子的第一款AI主题卫衣上架——"HELLO! HOW CAN I ASSIST YOU TODAY
· 无需6万激活码！GitHub神秘组织3小时极速复刻Manus，手把手教你使用OpenManus搭建本
· C#/.NET/.NET Core优秀项目和框架2025年2月简报
点击右上角即可分享
微信分享提示