@mysql数据库读写分离
文章目录
一、主库切换优先级
1.数据量切换测试
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.停止主库查看切换情况
2.优先级切换测试
1.配置优先级测试
candidate_master=1
check_repl_delay=0
3.如果断电或者断网,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
二、VIP漂移
1.VIP漂移的两种方式
1.通过keepalived的方式
2.通过MHA自带的脚本
2.配置MHA读取脚本
#编辑配置文件
[root@db03 ~]# vim /service/mha/app1.cnf
[server default]
master_ip_failover_script=/service/mha/master_ip_failover
3.编写脚本
[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 ...
4.手动绑定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
5.启动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
6.测试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
三、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.优化了锁机制，性能提高数十倍
1.安装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 #日志文件
2.编辑配置文件
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.启动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
4.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
+-------------+------------------+-------+------+
四、atlas的故障恢复+编写自动恢复脚本
atlas配置文件里面我们已经写死了，那如果出现问题我们怎么修改呢？？
1.思路
#MHA切换的时候,都需要做什么?
1.找到哪个从库被提升为主库
2.先在Atlas管理接口中,确认被提升为主库的从库,backend_id是什么
3.摘除remove backend $backend_id;
4.保存atlas的配置文件
2.编写脚本
#!/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
3.将脚本写入MHA定时任务
略
本文来自博客园，作者：ଲ一笑奈&何，转载请注明原文链接：https://www.cnblogs.com/zeny/p/15657656.html
