mysql的备份和主从架构

mysql冷备份还原
1停服务器 systemctl stop mariadb.service
2打包数据库 tar Jcvf /data/all.bak.tar.xz /var/lib/mysql/
3打包二进制文件 tar Jcvf logbin.tar.xz /data/logbin/
4移动配置文件 cp /etc/my.cnf .
5 把所有数据 移动到一个文件夹 mv all.bak.tar.xz logbin.tar.xz my.cnf backup/
6 找一个可靠地方存放 scp -r backup/ 192.168.37.127:/data
还原
还原前确认 服务器停机 数据库为空 二进制文件存在否是否为空
1.创建一个二进制日志文件夹 mkdir /data/logbin
2.修改所属 chown mysql.mysql /data/mysql
3.移动配置文件cp my.cnf /etc/my.cnf -b
4.解压二进制目录 tar -xvf /data/backup/logbin.tar.xz -C /data/logbin/ 确实是否在指定目录
5.解压数据文件 tar xvf all…bak.tar.xz -C /var/lib/mysql/
6.启动服务 确认数据是否完整

LVM快照 ：先加锁 在做快照后解锁 借助文件系统工具备份 几乎热备
基于LVM的备份
(1) 请求锁定所有表
mysql> FLUSH TABLES WITH READ LOCK; 整个数据库加实例级读锁
(2) 记录二进制日志文件及事件位置
mysql> FLUSH LOGS; 刷新二进制日志生成新的二进制日志
mysql> SHOW MASTER STATUS; 查看二进制日志 的位置
mysql -e ‘SHOW MASTER STATUS’ > /PATH/TO/SOMEFILE 记录日志记录
(3) 创建快照 数据库在逻辑卷中 快照存放旧的没改过的数据
lvcreate -L # -s -p r -n NAME /DEV/VG_NAME/LV_NAME
(4) 释放锁
mysql> UNLOCK TABLES;
(5) 挂载快照卷，执行数据备份
(6) 备份完成后，删除快照卷
(7) 制定好策略，通过原卷备份二进制日志

mysql热备 mysqldump备份还原
mysqldump –B（备份数据库） DB1 [DB2 DB3…] 备份多个数据库 含创建数据库
mysqldump -B hellodb mysql >/data/myhello.sql 备份hellodb和mysql数据库
mysqldump –A --all-datebases 含创建数据库 备份全部数据库
mysqldump -A >/data/all.sql
–default-character-set=utf8 指定字符集 备份时字符集要一致
–master-data[=#]： 此选项须启用二进制日志 记录当时做备份在二进制的某一位置
=#：1 执行change master to 主从复制 2 命令前加注释 加时间标
1不注释
mysqldump -A --master-data=2 命令前加注释 加时间标 记录二进制位置
模拟数据库破坏 恢复数据
1 mysqldump -A --master-data=2 > /data/all.sql 数据库的完全备份
数据库修改
insert students (name,age)values(‘a’,20);
insert students (name,age)values(‘b’,30);
2 删除库 rm -rf /var/lib/mysql/*

3 还原
确保无用户访问数据库 防火墙拒接或者 skip-networking 禁止网络连接。改完#注释
1）systemctl restart mariadb
2)mysql > show master logs; 查看当前二进制位置

3）根据/data/all.sql中日志位置 定位需要的二进制日志范围
mysqlbinlog --start-position=完全备份的终点 mysql-bin.000003 > /data/log.sql
mysqlbinlog mysql-bin.000004 >> /data/log.sql
mysqlbinlog mysql-bin.000005 >> /data/log.sql

4. mysql > set sql_log_bin=off; 关闭二进制日志
   mysql>source /data/all.sql 恢复完全备份
   mysql>source /data/inc.sql 恢复恢复完全备份至今的数据

5. mysql > set sql_log_bin=on; 开启二进制日志

6. 做检查确认数据库恢复成功，恢复用户访问

删表恢复数据库
1 mysqldump -A --master-data=2 > /data/all.sql 数据库的完全备份
drop table testlog； 删表 改表；
2发现误删 恢复
先删库
rm -rf /var/lib/mysql/*
启动服务器
mysql > show master logs; 查看当前二进制位置
服务器根据/data/all.sql中日志位置 定位需要的二进制日志范围
mysqlbinlog --start-position=完全备份的终点 mysql-bin.000011 > /data/log.sql
mysqlbinlog mysql-bin.000012 >> /data/log.sql
mysqlbinlog mysql-bin.000013 >> /data/log.sql
vim /data/log.sql 找到误删操作 注释掉错误命令
进入数据库
4) mysql > set sql_log_bin=off; 关闭二进制日志
mysql>source /data/all.sql 恢复完全备份
mysql>source /data/inc.sql 恢复恢复完全备份至今的数据
5) mysql > set sql_log_bin=on; 开启二进制日志

6. 做检查确认数据库恢复成功，恢复用户访问

扫描数据库 备份数据库
for db in mysql -e 'show databases'|grep -Ev '^(information_schema|performance_schema|Database)$';do mysqldump -B $db--single-transaction--master-data=2|gzip>/data/$db.sql.gz ;done

#mysql -e ‘show databases’|grep -Ev ‘^(information_schema|performance_schema|Database)$’|sed -rn ‘s@(.*)@mysqldump -B \1 --single-transaction --master-data=2 |gzip > /data/\1.sql.gz@p’|bash

xtrabackup 需epel下载
yy percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm
创建一个存储备份文件夹 mkdir /data/backup
1 备份过程
1）完全备份：xtrabackup --backup --target-dir=/data/backup/base 此文件夹自动生成
还原过程 backup需自己建立
1）预准备 整理数据库：确保数据一致，提交完成的事务，回滚未完成的事务
xtrabackup --prepare --target-dir=/data/backup/base
2）复制到数据库目录
注意：数据库目录必须为空，MySQL服务不能启动
xtrabackup --copy-back --target-dir=/data/backup/base 数据还原到数据库目录
确认为空 确认服务停止 就可以还原 自动找配置文件中数据库指定文件夹
3）还原数据库属性 属主 属组
chown -R mysql:mysql /var/lib/mysql
4）启动服务
systemctl start mariadb

xtrabackup 增量备份
backup 需自己建立单独存储备份的文件夹
1）完全备份：xtrabackup --backup --target-dir=/data/backup/base base是文件夹 自动生成

星期1.增量备份
xtrabackup --backup --target-dir=/data/backup/zengliang1 --incremental-basedir=/data/backup/base
备份 增量备份生成的文件放在 增量备份相对于哪个数据备份文件

星期二增量备份
xtrabackup --backup --target-dir=/data/backup/zengliang2 --incremental-basedir=/data/backup/zengliang1

还原增量备份
1）预准备完成备份，此选项–apply-log-only 阻止回滚未完成的事务
xtrabackup --prepare --apply-log-only --target-dir=/data/backup/base
不封口 接下一还原
2）增量1导入到base中
xtrabackup --prepare --apply-log-only --target-dir=/data/backup/base --incremental-dir=/data/backup/zengliang1
3）增量2导入到base中 最后增量封口 回滚事务
xtrabackup --prepare --target-dir=/data/backup/base --incremental-dir=/data/backup/zengliang2
准备 源数据的集合 从增量2 中导出

4)数据还原到数据库目录 会自己寻找数据库目录
xtrabackup --copy-back --target-dir=/data/backup/base
确认为空 确认服务停止 就可以还原 自动找配置文件中数据库指定文件夹
3）还原数据库属性 属主 属组
chown -R mysql:mysql /var/lib/mysql
4）启动服务
systemctl start mariadb

主从复制—主服务器用了一段时间添加从服务器
主服务配置信息
1.更改主服务配置文件
vim /etc/my.cnf
server-id=1
log-bin

2.重启服务二进制日志才能开启 添加从服务用户
mysql -e “grant replication slave on . to repluser@‘192.168.37.%’ identified by ‘centos’”

3.备份主服务器
mysqldump -A --single-transaction --master-data=1 -F >/data/all.sql
4.将备份文件远程传送给从服务
scp /data/all.sql 192.168.37.120:/data/

从服务器配置
1.修改配置文件
vim /etc/my.cnf
server-id=2
read-only

重启服务 配置生效
可在mysql中用show variables like ‘server_id’; ‘read_only’；查询

2.从服务 修改备份文件all.sql 中的选项 为主服务器IP
CHANGE MASTER TO
MASTER_HOST=‘192.168.37.120’,
MASTER_USER=‘repluser’,
MASTER_PASSWORD=‘centos’,
MASTER_PORT=3306,
MASTER_LOG_FILE=‘mariadb-bin.000002’,
MASTER_LOG_POS=245;

3.导入备份文件 ；两种方法都行 MySQL中记得要写清备份文件路径

或

4.启动同步线程

5.查看同步状态 show slave status\G;

双线程是否是yes 开启状态

6.然后主服务器测试创建数据库或者表 看是否已经同步

主从数据库数据冲突
从服务器已经创建了DB2数据库 主服务也创建DB2数据库同步 从服务卡机不能同步

主服务器后续数据均不能同步到从服务器
如果发现单个文件或数据库冲突 可以删除数据库再重启线程
stop slave； start slave；
也可以在从服务器上添加配置设置忽略几个事件

关闭同步
stop slave；
跳过此处1个错误
set global sql_slave_skip_counter=1；

开启同步
start slave；

也可以忽略某个指定错误 set slave-skip-erroes=1062 需写进配置文件

重启服务 线程也会自动重启

#记录不多 可以忽略再去寻找解决
#错误记录太多 就从新建立一台新的从服务器

120-（127,137）
模拟 ：主服务器dang机 从服务器升级为主服务器 1主2从
1.主服务器 见上主从搭建
改配置文件

重启服务
添加用户信息

备份 传给2从服务器

一号从服务器
1.修改配置文件
vim /etc/my.cnf
server-id=2
read-only

重启服务 配置生效
可在mysql中用show variables like ‘server_id’; ‘read_only’；查询
2.从服务 修改备份文件all.sql 中的选项 为主服务器IP
CHANGE MASTER TO
MASTER_HOST=‘192.168.37.120’,
MASTER_USER=‘repluser’,
MASTER_PASSWORD=‘centos’,
MASTER_PORT=3306, MASTER_LOG_FILE=‘mariadb-bin.000002’,
MASTER_LOG_POS=245;

3.导入备份文件 ；两种方法都行 MySQL中记得要写清备份文件路径

或

4.启动同步线程

5.查看同步状态 show slave status\G;

双线程是否是yes 开启状态

6.然后主服务器测试创建数据库或者表 看是否已经同步

二号从服务器同上添加
1.修改配置文件
vim /etc/my.cnf
server-id=2
read-only

重启服务 配置生效
可在mysql中用show variables like ‘server_id’; ‘read_only’；查询
2.从服务 修改备份文件all.sql 中的选项 为主服务器IP
CHANGE MASTER TO
MASTER_HOST=‘192.168.37.7’,
MASTER_USER=‘repluser’,
MASTER_PASSWORD=‘centos’,
MASTER_PORT=3306, MASTER_LOG_FILE=‘mariadb-bin.000002’,
MASTER_LOG_POS=245;

3.导入备份文件 ；两种方法都行 MySQL中记得要写清备份文件路径

或

4.启动同步线程

5.查看同步状态 show slave status\G;

双线程是否是yes 开启状态

6.然后主服务器测试创建数据库或者表 看是否已经同步

主从服务器搭建完成 模拟主服务器宕机 断电
1查看两个从服务器的数据节点编号 看哪个从服务的同步点哪个更多 show slave status\G;

2.停止同步 删干净从节点信息
stop slave;
reset slave all;

3.清除read only 加二进制

4.重启服务
5.确认从服务器同步账户存不存在 select user from mysql.user;

6.查看二进制状态 show master logs；因为刚开启二进制所以第一份

7. 2号从服务器 停止同步 清空全部同步信息
   stop slave；
   reset slave all;

8.然后使用CHANGE MASTER TO设置从服务器开始读取的主服务器日志位置和日志名称

CHANGE MASTER TO MASTER_HOST=‘192.168.37.7’,MASTER_USER=‘repluser’,MASTER_PASSWORD=‘centos’,MASTER_PORT=3306, MASTER_LOG_FILE=‘mariadb-bin.000002’,MASTER_LOG_POS=245;

9.重新连接同步
start slave；
10. 查看连接状态
show slave status\G;
11.主服务器创建数据测试同步是否完成

级联复制
主服务器的配置
1.改配置文件

2.重启服务
3.创建账户mysql> grant replication slave on . to repluser@‘192.168.37.%’ identified by ‘centos’;

4.备份mysqldump -A --single-transaction --master-data=1 -F > /data/all.sql

5.复制到中间级联机 scp /data/all.sql 将来的级联从服务器上:/data

1.级联节点配置 添加一条log-slave-updates 把中继日志写到update中 再写到binlog中

2.重启服务
3.修改备份文件 vim /data/all.sql
CHANGE MASTER TO
MASTER_HOST=‘主服务器’,
MASTER_USER=‘repluser’,
MASTER_PASSWORD=‘centos’,
MASTER_PORT=3306,
MASTER_LOG_FILE=‘mariadb-bin.000001’, MASTER_LOG_POS=400;

4.导入备份文件mysql < /data/all.sql

5.开启同步start slave;

6.查看状态 创建数据 查看是否同步
7.备份 mysqldump -A --single-transaction --master-data=1 -F > /data/all.sql

8.备份文件传给 最终从服务器
scp /data/all.sql 最终的从服务器上:/data

最后的从服务器
1.vim /etc/my.cnf
server-id=3
read-only
2.重启服务
3. 编写备份文件 vim /data/all.sql
CHANGE MASTER TO
MASTER_HOST=‘级联从服务器’,
MASTER_USER=‘repluser’,
MASTER_PASSWORD=‘centos’,
MASTER_PORT=3306,
MASTER_LOG_FILE=‘mariadb-bin.000001’, MASTER_LOG_POS=400;

4. mysql <  /data/all.sql          导入备份
   

5. mysql> start slave;              开启同步
   

6.查看状态。 show slave status\G;

7.创建文件检查是否完成同步过程

主主建立配置 不建议使用
1主配置一个节点使用奇数id
auto_increment_offset=1 开始点
auto_increment_increment=2 增长幅度 配置文件
1主为使用一段时间的服务器所以需要备份到另一台主机
写配置文件
server-id=20
log-bin
auto_increment_offset=1
auto_increment_increment=2
重启服务
创建共同账户
grant replication slave on . to repluser@‘192.168.37.%’ identified by ‘centos’;

备份 拷贝备份文件到另一台主机

2主服务器
另一个节点使用偶数id
auto_increment_offset=2
auto_increment_increment=2

修改备份文件

导入备份文件执行

开启同步线程

查看状态

刷新用户权限 flush privileges; 因为是拷贝来的文件 用户权限未激活

查看日志位置 1号主服务同步的位置

1号服务器同步设置

同步进程 start slave； 查看同步状态
测试同步效果

半同步复制 生产中一定要有的
1.主服务器 见上主从搭建
改配置文件

重启服务
添加用户信息

备份 传给2从服务器

一号从服务器
1.修改配置文件
vim /etc/my.cnf
server-id=2
read-only

重启服务 配置生效
可在mysql中用show variables like ‘server_id’; ‘read_only’；查询
2.从服务 修改备份文件all.sql 中的选项 为主服务器IP
CHANGE MASTER TO
MASTER_HOST=‘192.168.37.7’,
MASTER_USER=‘repluser’,
MASTER_PASSWORD=‘centos’,
MASTER_PORT=3306, MASTER_LOG_FILE=‘mariadb-bin.000002’,
MASTER_LOG_POS=245;

3.导入备份文件 ；两种方法都行 MySQL中记得要写清备份文件路径

或

4.启动同步线程

5.查看同步状态 show slave status\G;

双线程是否是yes 开启状态

6.然后主服务器测试创建数据库或者表 看是否已经同步

二号从服务器同上添加
1.修改配置文件
vim /etc/my.cnf
server-id=2
read-only

重启服务 配置生效
可在mysql中用show variables like ‘server_id’; ‘read_only’；查询
2.从服务 修改备份文件all.sql 中的选项 为主服务器IP
CHANGE MASTER TO
MASTER_HOST=‘192.168.37.7’,
MASTER_USER=‘repluser’,
MASTER_PASSWORD=‘centos’,
MASTER_PORT=3306, MASTER_LOG_FILE=‘mariadb-bin.000002’,
MASTER_LOG_POS=245;

3.导入备份文件 ；两种方法都行 MySQL中记得要写清备份文件路径

或

4.启动同步线程

5.查看同步状态 show slave status\G;

双线程是否是yes 开启状态

6.然后主服务器测试创建数据库或者表 看是否已经同步

主从搭好 开始装插件 主服务器装 master 从服务器装slave

主服务器装插件INSTALL PLUGIN rpl_semi_sync_master SONAME ‘semisync_master.so’;
安装插件 插件名 插件库(如上图)对应的文件名称 会自动寻找

查看插件是否生成show plugins;

查看插件配置变量 show global variables like ‘%semi%’;

启动插件 set global rpl_semi_sync_master_enabled=on;

查看同步状态 SHOW GLOBAL STATUS LIKE ‘%semi%’; clients 有几个半同步的客服端

2台从服务搭半同步过程
装半同步插件 客户端 INSTALL PLUGIN rpl_semi_sync_slave SONAME ‘semisync_slave.so’;

开启半同步插件 变量设置 SET GLOBAL rpl_semi_sync_slave_enabled=1;

重启同步服务 stop slave； start slave；
查看插件状态 SHOW GLOBAL STATUS LIKE ‘%semi%’;

从服务器的复制过滤

从服务器上的复制过滤器相关变量 服务器配置文件中
replicate_do_db= 指定复制库的白名单 只允许白名单数据库同步主服务器、
必须主服务器在白名单指定库中操作增删改 才能同步到从服务器

replicate_ignore_db= 指定复制库黑名单
replicate_do_table= 指定复制表的白名单
replicate_ignore_table= 指定复制表的黑名单
replicate_wild_do_table= foo%.bar% 支持通配符
replicate_wild_ignore_table=

数据库传输数据 SSL加密
1 在主服务器上生成证书
1.mkdir /etc/my.cnf.d/ssl
cd /etc/my.cnf.d/ssl
2.生成600权限的私钥
(umask 066;openssl genrsa -out cakey.pem 2048)
3. 用私钥生成自签名证书 CA根证书
openssl req -new -x509 -key cakey.pem -out cacert.pem -days 3650
申请 私钥 生成的证书 有效期

4.生成主服务器的申请帧数文件和主服务器的私钥
openssl req -newkey rsa:1024 -days 365 -nodes -keyout master.key > master.csr
申请 新的 加密方式位数 有效期 不加密 生成主私钥 生成证书申请文件
申请证书文件 必须在国家 省份 和公司三项必须和根证书一致

5. openssl x509 -req -in master.csr -CA cacert.pem -CAkey cakey.pem -set_serial 01 > master.crt
   颁发正书 主服务器申请证书 CA根证书 CA根的kye 指定证书编号 01 生成颁发文件

从节点的证书颁发
6.openssl req -newkey rsa:1024 -nodes -keyout slave.key > slave.csr

7.openssl x509 -req -in slave.csr -CA cacert.pem -CAkey cakey.pem -set_serial 02 > slave.crt

查看证书和秘钥 是否齐全 主服务器用 carcert.pem master.crt master.key 这三个
从服务器用 cacert.pem slave.crt slave.key 这三个

1.主服务器的配置文件 加密可在配置文件中加密 可也以在同步设置中加密
vim /etc/my.cnf
[mysqld]
server-id=7
log-bin
ssl-ca=/etc/my.cnf.d/ssl/cacert.pem
ssl-cert=/etc/my.cnf.d/ssl/master.crt
ssl-key=/etc/my.cnf.d/ssl/master.key
重启服务

2.把三个 文件传送给从服务器 cacert.pem slave.crt slave.key 这三个
scp -r /etc/my.cnf.d/ssl/ 从服务器:/etc/my.cnf.d/ 此处是整个目录发送

3.生成新的账户 只能SSL加密登录 require ssl
mysql>grant replication slave on . to repluser2@‘192.168.37.%’ identified by ‘centos’ require ssl;

4.查看当前二进制位置 文件
mysql> show master logs;show
mariadb-bin.000004 POS=413

2 在从服务器上
1.测试用户是否支持加密连接 此处必须在下面文件所在文件夹 才可以连接成功
mysql -h192.168.37.120 -urepluser2 -pcentos --ssl-ca=cacert.pem --ssl-cert=slave.crt --ssl-key=slave.key

2.编写配置文件
vim /etc/my.cnf
server-id=17
read-only
重启服务

3.从服务器执行二进制同步命令
mysql> CHANGE MASTER TO
MASTER_HOST=‘主服务器’,
MASTER_USER=‘repluser2’,
MASTER_PASSWORD=‘centos’,
MASTER_LOG_FILE=‘mariadb-bin.000004’,
MASTER_LOG_POS=413,
MASTER_SSL=1,
MASTER_SSL_CA = ‘/etc/my.cnf.d/ssl/cacert.pem’,
MASTER_SSL_CERT = ‘/etc/my.cnf.d/ssl/slave.crt’,
MASTER_SSL_KEY = ‘/etc/my.cnf.d/ssl/slave.key’;
4.启动同步
mysql>start slave;
5.查看状态
show slave status\G;
6.主服务器创建数据 看从服务器是否更新

二进制安装的mysql 5.7 GTID同步实验

1.下载安装 mysql-5.7.26-el7-x86_64

2.解压到指定文件夹目录 tar xvf mysql-5.7.26-el7-x86_64.tar.gz -C /usr/local/

3.创建用户
useradd -r -s /sbin/logbin mysql

4. PATH变量
   echo ‘PATH=/usr/local/mysql/bin:$PATH’ > /etc/profile.d/mysql.sh

5.创建软链接 注意当前目录
cd /usr/local/
ln -s mysql-5.7.26-el7-x86_64/ mysql

6.导入数据库 注意当前目录
mysqld --initialize --user=mysql --datadir=/data/mysql

会生成随机登录密码 记住密码 改密码可用

7.根据生成数据的目录改配置文件改配置文件
vim /etc/my.cnf 测试配置
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
[client]
socket=/data/mysql/mysql.sock

8.添加服务启动 开机自启等设置
cp mysql/support-files/mysql.server /etc/init.d/mysqld
chkconfig --add mysqld

9.重启服务 sysre mysqld

10.启动服务改密码 随机密码带符号 要用引号
mysqladmin -uroot -p’随机密码’ password centos

GTID主从同步配置信息
1.主服务器
vim /etc/my.cnf
server-id=1
log-bin=mysql-bin
gtid_mode=ON
enforce_gtid_consistency

2.创建同步账户
mysql>grant replication slave on . to repluser@‘192.168.37.%’ identified by ‘centos’;

3.从服务器
vim /etc/my.cnf
server-id=2
gtid_mode=ON
enforce_gtid_consistency

4.命令设置同步信息
mysql>CHANGE MASTER TO
MASTER_HOST=‘主服务器IP’,
MASTER_USER=‘repluser’,
MASTER_PASSWORD=‘centos’,
MASTER_PORT=3306,
MASTER_AUTO_POSITION=1;

5.开启同步设置
mysql>start slave;

6.查看同步信息 show slave status\G;
主服务器创建数据测试

读写分离应用：
1.先搭建主从服务器2台

重启服务
创建同步账户 grant replication client on . to monitor@‘192.168.37.%’ identified by ‘magedu’;
2.从服务器

设置同步设置

启动同步 查看状态 测试同步情况

3.配置proxysql yum源
cat <<EOF | tee /etc/yum.repos.d/proxysql.repo
[proxysql_repo]
name= ProxySQL YUM repository
baseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/$releasever
gpgcheck=1
gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key
EOF
4.yum安装程序 proxysql mariadb客户端

5.启动服务器 service proxysql start 查看监听端口 6033 6032
6. 默认端口 默认连接自己的服务器 管理员端口
mysql -uadmin -padmin -P6032 -h127.0.0.1

7.添加主从主机的ip节点
insert into mysql_servers(hostgroup_id,hostname,port) values(10,‘192.168.37.127’,3306);

insert into mysql_servers(hostgroup_id,hostname,port) values(20,‘192.168.37.137’,3306);

8.重新加载 保存生效 load mysql servers to runtime; save mysql servers to disk;

9.127主服务器创建proxysql服务的默认用户 实现读写程序可查询

10. proxysql 上配置监控账户密码 并同步保存
    set mysql-monitor_username=‘monitor’;
    set mysql-monitor_password=‘magedu’;
    MySQL [(none)]> load mysql variables to runtime;
    MySQL [(none)]> save mysql variables to disk;

11. 测试客服端服务器是否正常 如果connect_error的结果为NULL则表示正常
    MySQL> select * from mysql_server_connect_log;
    查看监控心跳信息 (对ping指标的监控)：
    MySQL> select * from mysql_server_ping_log;

12添加读写分组
insert into mysql_replication_hostgroups values(10,20,‘test’);

13. 同步保存
    MySQL> load mysql servers to runtime;
    MySQL> save mysql servers to disk;
    14.查询分组情况是否正确 select hostgroup_id,hostname,port,status,weight from mysql_servers;

15.主服务器上创建访问用户 用户信息会同步到从服务器
grant all on . to sqluser@‘192.168.37.%’ identified by ‘magedu’;

16.读写分离服务器上添加用户 如果读写分离路径不符规则默认访问10服务器组

17.同步保存
MySQL> load mysql users to runtime;
MySQL> save mysql users to disk;
18.使用sqluser用户测试是否能路由到默认的10写组实现读、写数据 测试服务同步
mysql -usqluser -pmagedu -P6033 -h127.0.0.1 -e ‘select @@server_id’

mysql -usqluser -pmagedu -P6033 -h127.0.0.1 -e ‘create database testdb’
mysql -usqluser -pmagedu testdb -P6033 -h127.0.0.1 -e ‘create table t(id int)’
18.添加路由规则
insert into mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply) values(1,1,’^select.*for update$’,10,1),(2,1,’^select’,20,1);

注：注意顺序 ^select.*for updata$ 必须放前面
19.同步保存
MySQL> load mysql query rules to runtime;
MySQL> save mysql query rules to disk;

20.设置完成 测试同步情况 查询命令发往从服务器
mysql -usqluser -pmagedu -P6033 -h127.0.0.1 -e ‘select @@server_id’

不以select开头的查询 任然会发往27主服务器

路由的信息：查询stats库中的stats_mysql_query_digest表
MySQL > SELECT hostgroup hg,sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;

MHA系统的搭建 实现主服务器宕机 从服务器自动升主服务器
配置需求：4台主机 1台MHA服务端 3台主从搭建
3台主机 主从搭建
1、主服务器的配置文件
vim /etc/my.cnf
[mysqld]
server-id=127
log-bin
skip-name-resolve
2、重启服务
3、mysql中查看二进制日志位置 如果是工作中应该是备份恢复
show master logs；

4、创建主从复制用户
grant replication slave on . to repluser@‘192.168.37.%’ identified by ‘magedu’;
5、创建主从管理用户
grant all on . to mhauser@‘192.168.37.%’ identified by ‘magedu’;

6、从服务器
vim /etc/my.cnf
[mysqld]
server_id=137
log-bin
read_only
relay_log_purge=0
skip_name_resolve=1
7、重启服务器
8、mysql开启同步设置
CHANGE MASTER TO
MASTER_HOST=‘192.168.37.127’,
MASTER_USER=‘repluser’,
MASTER_PASSWORD=‘magedu’,
MASTER_LOG_FILE=‘mariadb-bin.000001’,
MASTER_LOG_POS=245;
9、start slave ；开启同步
show slave status\G; 查看同步是否完成
10、创建数据测试是否主从同步

MHA的下载安装
1、在管理节点上安装两个包
mha4mysql-manager
mha4mysql-node

2、在被管理节点（主从服务器3台主机）安装
mha4mysql-node

3、生成公私钥基于KEY的ssh验证
ssh-keygen 交互式 或者 ssh-keygen -t rsa -P “” -f /root/.ssh/id_rsa 命令模式

ssh-copy-id 192.168.37.120 简化 或者 ssh-copy-id -i /root/.ssh/id_rsa.pub 127.0.0.1
拷贝公私钥到主从服务器 实现key验证

SSH配置中 和useDNS 都要设置为no 连接会快点

4、创建个MHA专用配置文件夹 编写配置文件
mkdir /etc/mastermha

5、 编写MHA的配置文件 vim /etc/mastermha/app1.cnf
[server default]
user=mhauser
password=magedu
manager_workdir=/data/mastermha/app1/
manager_log=/data/mastermha/app1/manager.log
remote_workdir=/data/mastermha/app1/
ssh_user=root
repl_user=repluser
repl_password=magedu
ping_interval=1
[server1]
hostname=192.168.37.127
candidate_master=1
[server2]
hostname=192.168.37.137
candidate_master=1
[server3]
hostname=192.168.37.147

6、SSH的连接检查 masterha_check_ssh --conf=/etc/mastermha/app1.cnf

7、查看复制状态 masterha_check_repl --conf=/etc/mastermha/app1.cnf

8、启动manager服务 masterha_manager --conf=/etc/mastermha/app1.cnf

事务日志更新

宕机主服务器 测试从变主是否完成

Galera Cluster：集成了Galera插件的MySQL集群，是一种新型的，数据不共享的，高度冗余的高可用方案

实现Galera Cluster 需三台以上主机
清华yum源

阿里yum源

1、配置yum源

2、yum安装 MariaDB-Galera-server
yum install MariaDB-Galera-server

3、编写服务配置文件
vim /etc/my.cnf.d/server.cnf
[galera]
wsrep_provider = /usr/lib64/galera/libgalera_smm.so 程序模块
wsrep_cluster_address=“gcomm://192.168.37.7,192.168.37.17,192.168.37.27”
binlog_format=row

配置文件主要是前两行 模块和组成员 和binlog 行记录默认

5、配置文件给另外两台节点 scp /etc/my.cnf.d/server.cnf 192.168.37.137:/etc/my.cnf.d/server.cnf

6、第一个节点需创建集群启动 其他两个就正常启动就行
/etc/init.d/mysql start --wsrep-new-cluster

7、其他两台正常启动 service mysql start

查看集群中相关系统变量和状态变量
SHOW VARIABLES LIKE 'wsrep_%‘; 服务器变量
SHOW STATUS LIKE 'wsrep_%‘; 状态变量
SHOW STATUS LIKE 'wsrep_cluster_size‘; 节点的多少 几个

解决同步问题 但是性能明显下降