索引查询
image.png
MySQL程序架构简图
image.png
MySQL程序的架构
image.png
分区变为不同的"块"--存储数据和元数据
中间层--VFS(虚拟文件系统)--同一接口，操作不同的文件系统
程序=指令(算法)+数据(数据结构)
索引--平衡树 Blance Tree--每个叶子到根的距离相同--范围/等值查询
mysql 老不死的 降低数据冗余/支持事务操作
事务
付款-收款--两个操作必须同时完成，否则回滚至初始状态，即:事务不能忽略
评价--显示了一半--两个操作可以不同时完成，即:事务可以忽略
评论转发显示的次数--不是很重要--kv store
MySQL 事务好，性能低
MariaDB
Percona-server 提供开源工具和新论文
NewSQL 事务和性能兼具
TiDB 国内
系统工程师(安装启动数据库并部署在文件系统上) dba 程序员
表 索引 行式存储
yum -y install mariadb-server
rpm -ql mariadb-server 服务端
systemctl start mariadb.service; ss -ntl 3306
ls /var/lib/mysql 数据存放处
ls /var/lib/mysql/mysql
.MYD
.MYI
.frm 格式
mysql
show engines; 支持的存储引擎 如下图1 横排显示
show engines\G 竖排显示
create database mydb;
use mydb
create table tbl1 (id int unsigned,name varchar(200));
show table status; 显示所有表
show table status\G 竖排显示 如下图2
show table status like 'tbl1'; 显示指定的表
show table status like 'tbl1'\G
% 任意长度的任意字符
_ 单个长度的任意字符
show table status like 'tbl%'\G
show table status like 'tbl_'\G
show table status where engine='Innodb'\G 按条件进行过滤
ls /var/lib/mysql/mydb/ 只显示tbl1的.frm表格式文件
数据文件都存放在/var/lib/mysql/ibdata1中，不支持高级功能
vim /etc/my.cnf.d/server.cnf
更改配置--每表使用单独的表空间文件
[mysqld]
innodb_file_per_table = ON 不区分字符大小写，路径可能区分
systemctl restart mariadb.service
mysql
use mydb
create table tbl2 (id int unsigned,name varchar(200));
ls /var/lib/mysql/mydb/ 显示tbl2的.frm表格式文件和.ibd数据文件，支持高级的数据管理特性
show engine innodb status; innodb本身自带的内容
show engine innodb status\G
窗口1
mysql
use mydb
lock tables tbl1 read;
窗口2
mysql
use mydb
select * from tbl1; 虽然锁了tbl1，但是可以查询
insert into tbl1 values (1,'tom'); 需等待别人释放锁，因为此数据被施加读锁
窗口1：unlock tables; 释放施加的所有表中的锁
窗口2：显示insert操作完成
窗口1：lock tables tbl1 write;
窗口2：select * from tbl1; tbl1被施加写锁，不能查询
窗口1：unlock tables;
窗口2：显示select操作完成
窗口1：lock tables tbl1 write;
窗口2：lock tables tbl1 read; tbl1被施加写锁--需等别人释放锁后--我们才可施加读锁
其他
flush tables 把表数据同步至磁盘
flush tables with read lock;
flush tables with write lock; 把表数据同步至磁盘+立即施加读锁 (for update;备份用)
select cluase [for update | lock in share mode] 较少使用
for update 写锁
lock in share mode 读锁
查询到表中某一行数据后，以更新的方式对此行数据加锁
每个操作语句会自动施加锁，不用手动添加
只有当我们偶尔备份需要请求全局锁时，才需要去手动施加锁
图1
image.png
图2
image.png
图3 事务日志介绍
image.png
事务日志介绍 如上图3
mysql两类变量 服务器变量 状态变量
mysql
show variables; 显示服务器变量--配置参数--可自己改
show variables\G
运行时可修改--不重启，修改后立即有效，临时生效
运行时不可修改--配置文件中修改，需重启，才会永久有效
show global variables; 有管理权才可以看
show global variables\G
全局设置--管理权才可修改
修改后，对新建立的所有会话有效，对当前会话和已经建立的会话无效
show session variables; session可省略
会话设置--当前会话有效
set session/global 变量名
set @@global.变量名
set @@session.变量名
show status; 显示状态变量--计数器--服务器的工作状态--手动置0/服务器自动调整
show status\G
show variables like 'innodb%';
show variables like 'innodb%log%';
innodb_log_files_in_group 2
ll /var/lib/mysql/ -h 5M ib_logfile0/ib_logfile1
innodb_mirrored_log_groups 1
设置为2，最好两个镜像组放在不同的路径下
innodb_flush_log_at_trx_commit 1/0/2
提交事务时，如何同步事务 每秒同步一次 or commit就立即同步
show global variables like 'autocommit';
ON/true/yes/1 表示真--开启
OFF/faulse/no/0 表示假--未开启
set @@global.autocommit=0;
show global variables like 'autocommit'; 显示OFF
set @@global.autocommit=1;
show global variables like 'autocommit'; 显示ON
select @@global.autocommit; 显示1
select @@session.autocommit; 显示1
set @@global.autocommit=0;
select @@global.autocommit; 显示0
select @@session.autocommit; 显示1 修改全局设置后，对当前会话和已经建立的会话无效
窗口2：select @@session.autocommit; 显示0
临时生效，写入配置文件，重启mariadb-server，永久有效
set @@session.autocommit=0; 不会自动提交事务/减少io压力/需要手动提交事务
启动事务/标记为一个事务的开始/在一个事务内部操作 手动提交事务
start transaction;
show tables;
select * from tbl1; 生产中，一定要加where条件
insert into tbl1 values (2,'jerry'),(3,'lucy');
select * from tbl1;
rollback; 回滚 (or commit; 提交)
select * from tbl1; 显示插入的数据没了
支持保存点，一个事务很大(30个语句/执行到29个时/发现第26个错误/想回滚)
每执行2个语句就保持一下，类似快照
start transaction;
insert into tbl1 values (2,'jerry');
savepoint first; 保存一次即保存点1，命名为first
insert into tbl1 values (3,'lucy');
savepoint second; 保存点2
delete from tbl1 where id=1;
select * from tbl1;
rollback to second; 不想删除1了，回滚至最近一次的保存状态即保存点2
select * from tbl1; 又有1了
commit; 确认提交，此时再回滚，就要手动操作删除了
数据本身回滚--容易；如果create table/database，可能就麻烦了
select @@session.tx_isolation; 查看隔离级别，默认第三级别--可重复读(幻读)
设置为第一级别--读未提交
窗口1和窗口2：set @@session.tx_isolation='read-uncommitted';
窗口1和窗口2：start transaction;
窗口1：delete from tbl1 where id=2; select * from tbl1;
窗口2：select * from tbl1; 未显示id=2
设置隔离级别为"读未提交"，则只要改了，就可以看见
窗口1：rollback;
窗口2：select * from tbl1; 显示id=2 看到数据变化，脏读
窗口2：commit; or rollback; 结束事务
设置为第二级别--读提交
窗口1和窗口2：set @@session.tx_isolation='read-committed';
窗口1和窗口2：start transaction;
窗口1：delete from tbl1 where id=2; select * from tbl1; id=2消失
窗口2：select * from tbl1; 显示id=2仍在
设置隔离级别为"读提交"，只要别人没提交，就看不见改变的数据
窗口1：commit;
窗口2：select * from tbl1; id=2消失 看到数据变化，不可重复读
窗口2：commit; 结束事务
设置为第三级别--可重复读
窗口1和窗口2：set @@session.tx_isolation='repeatable-read';
窗口1和窗口2：start transaction;
窗口1：insert into tbl1 values (4,'lily'); select * from tbl1; 显示id=4
窗口2：select * from tbl1; 没有id=4
窗口1：commit;
窗口2：select * from tbl1; 别人提交了，但仍然没有id=4，可重复读--幻读
此时，自己再添加id=4，则会冲突，底层已经变化
窗口2：commit; select * from tbl1; 自己提交了，才会有数据变化显示id=4
设置为第四级别--串行化
窗口1和窗口2：set @@session.tx_isolation='serializable';
窗口1和窗口2：start transaction;
窗口2：select * from tbl1; 显示id=4存在(省略此步骤，否则会造成缓存)
窗口1：delete from tbl1 where id=4; select * from tbl1; id=4消失
窗口2：select * from tbl1; 显示id=4仍在；应该被阻塞才对，有缓存的原因
窗口1：commit;
窗口2：select * from tbl1; 没有缓存的情况下，别人提交后，显示id=4消失
窗口2：commit;
vim /etc/my.cnf.d/server.cnf
在[mysqld]中添加以下信息
skip_name_resolve = ON 跳过反解，不使用IP解析为主机名
重启才会生效
mysql
use mydb
select current_user(); 显示当前登陆的用户账号
create user test@'192.168.%.%' identified by 'testpass';
set password for 'test'@'192.168.%.%' = password('qianggedu'); 更改用户密码
flush privileges; 更新权限表，使之生效
desc mysql.user;
select user,host,password from mysql.user; 查看用户密码表
update mysql.user set password=password('testpass') where user='test' and host='192.168.%.%';
更新表，where一定要加，host不能忘了，不会立即生效 更改用户密码
flush privileges; 需要重载权限文件
连接上mysql然后操作的命令都可以使用mysqladmin在命令行操作
命令行操作，不用连接mysql
man mysqladmin
mysqladmin -uroot -hlocalhost ping 显示alive--正常
mysqladmin -uroot -hlocalhost flush-privileges
mysqladmin -uroot -hlocalhost create testdb
mysql -uroot -hlocalhost -e 'show databases'
(mysqladmin -uroot -hlocalhost drop databases 删除数据库/一定思虑再三)
(mysqladmin -uroot -hlocalhost -p password 'new_pass' 没有密码时/可直接设定)
忘记mysql管理员密码
update mysql.user set password=password('qiang') where user='root';
flush privileges;
mysql 不能登陆了
假设忘记管理员密码
systemctl stop mariadb.service
vim /usr/lib/systemd/system/mariadb.service
在ExecStart后面添加2个选项
ExecStart=/usr/bin/mysqld_safe --basedir=/usr --skip-grant-tables --skip-networking
systemctl daemon-reload 重载配置文件
systemctl start mariadb.service
mysql 连接上了，此时没读授权表，不能使用"set password"更改密码
update mysql.user set password='' where user='root'; 设置root密码为空
select user,host,password from mysql.user; 验证是否更改
exit 更改完，退出即可，不用flush，因为没读授权表
systemctl stop mariadb.service
vim /usr/lib/systemd/system/mariadb.service
删除后面添加的两个选项"--skip-grant-tables --skip-networking"
ExecStart=/usr/bin/mysqld_safe --basedir=/usr
systemctl daemon-reload
systemctl start mariadb.service
mysql 登陆，此时读取了授权表
窗口1
mysql
show grants; 查询自己拥有的权限
show grants for 'test'@'192.168.%.%'; 查询test拥有的权限
USAGE--普通权限--information_schema和test--不能create/...
窗口2
mysql -utest -ptestpass -h 192.168.1.7
show databases;
create database mytest; 没有权限，不能create
窗口1
grant create on mytest.* to 'test'@'192.168.%.%';
flush privileges;
窗口2
create database mytest; 可以create
use mytest
create table tbl1 (id int); 可以
select * from tbl1; 没有查看权限
窗口1
grant select on mytest.tbl1 to 'test'@'192.168.%.%'; 授权test，只能查看这一张表
show grants for 'test'@'192.168.%.%'; 查询test用户拥有的权限
窗口2
create table tbl2 (id int,name char(20),age tinyint);
insert into tbl2 values (1,'tom',13); 没有权限，禁止操作
窗口1
grant insert(id,name) on mytest.tbl2 to 'test'@'192.168.%.%';
授权test只能对mytest库的tbl2表插入某些字段--id,name
窗口2
insert into tbl2 values (1,'tom',13); 还是不行，只能在id和name字段处插入数据
insert into tbl2(id,name) values (1,'tom'); ok，字段集权限
索引
systemctl start mariadb.service
mysql
use mydb
mydb使用的字符集和排序规则要合适 使用中文--要更改默认使用的字符集为utf8
先创建数据
create table students (stuid int unsigned auto_increment primary key,name varchar(200),age tinyint unsigned,gender enum('f','m'),major varchar(200));
也可使用char定长(不足固定长度--自动补充内容)，可提高查询性能，但会浪费空间
insert into students (name,age,gender,major) values ('Jia Baoyu',17,'m','Pixiejian'),('Lin Chong',37,'m','Xianglongzhang'),('Ximen Qing',31,'m','Kuihuabaodian'),('Li Mochou',27,'f','Wuxianggong');
数据少--用不用索引都行
另一个窗口--命令行操作：批量创建用户数据
for i in {1001..2000};do mysql -e "insert into mydb.students values ('$i','stu$i',$[$RANDOM%100+1],'m','major$i')";done
select * from students;
select name,age from students where age >= 15;
explain select name,age from students where age >= 15;
explain select * from students where name = 'stu1002';
rows显示全表扫描，其他信息含义--参考文档
select count(stuid) from students; 查询stuid的总行数
help create index
create index name on students(name);
show indexes from students; indexes可以变为index
explain select * from students where name = 'stu1002';
rows显示1行--索引起作用了，其他的信息也发生变化
explain select * from students where name like 'stu100%';
rows显示9行
explain select * from students where name like '%stu100%';
rows显示全表扫描--B TREE索引--左前缀索引(%只能放在右侧，否则全表扫描)
create index name_and_age on students(name,age); 组合索引
explain select * from students where name like 'stu100%';
possible_keys显示两个索引--重复索引，name/name_and_age索引，副作用
drop index name on students; 删除name索引
show indexes from students;
explain select * from students where name like 'stu100%';
rows显示9行，possible_keys显示name_and_age索引
explain select * from students where age >= 50;
name_and_age索引的最左边一个字段是name，不能跳过字段找下一个
但可以查询--name/name and age
所以possible_keys显示为空，没有可用索引，即全表扫描
explain select * from students where name like 'stu%' and age >= 50;
possible_keys显示name_and_age索引，但key为空--没用索引，匹配行数量太多
explain select * from students where name like 'stu100%' and age >= 50;
possible_keys显示name_and_age索引，key为name_and_age，使用了索引
语句写法不同--可导致--是否使用索引/如何使用索引
explain select * from students where age >= 98 union select * from students where name like 'stu100%';
select搜索来自两个不同的表的字段才有意义
explain select * from students where stuid=4;
type和ref显示const--性能最好
explain select * from students where stuid in (1,77,1009,1200);
ref为空--使用的是一个集合(显示结果为多行内容)--范围查询range
主从复制
image.png
日志存储
image.png
日志
mysql
use mysql
show tables;
查询日志--general_log
show global variables like 'general%';
show global variables like 'log_output'; 日志输出至何处
set global general_log=on; 开启查询日志
show global variables like 'general%'; 显示on
select * from mydb.students where stuid=3;
ls /var/lib/mysql/centos7.log 生成查询日志
tail /var/lib/mysql/centos7.log 显示执行过的查询语句
set @@global.log_output='TABLE'; 输出位置更改为表
show global variables like 'log_output';
select * from mysql.general_log; 显示执行的查询语句
set @@global.general_log=off; 关闭查询日志
show tables;
慢查询--slow_log
select @@global.long_query_time; 默认10s
show global variables like '%slow%';
set @@global.slow_query_log=on; 开启慢查询
log_slow_queries/slow_query_log 两个版本/兼容，更改其中一个，两个都会更改
show global variables like '%slow%';
show global variables like 'log_output';
set @@global.log_output='FILE'; 记录文件中
错误日志
show global variables like '%error%';
tail /var/log/mariadb/mariadb.log
二进制日志
show global variables like 'binlog_format%'; 二进制日志的格式
不支持运行时修改
exit
vim /etc/my.cnf.d/server.cnf
[mysqld]
log_bin=master.log log_bin或log-bin
systemctl restart mariadb.service
mysql
show global variables like '%log%bin%';
show master logs; 显示master.000001
show master status; 显示当前正在使用的文件--master.000001
flush logs; 滚动
show master logs; 显示master.000001/2
show master status; 当前正在使用master.000002
use mydb
select * from students;
show master status; Position未变化
delete from students where stuid=1986;
show master status; Position改变
show binlog events; 默认看master.000001文件中的事件
show binlog events in 'master.000002'; 看master.000002
xid--事务编号 Pos End_log_pos
max_binlog_size=1073741824(字节) 1g 二进制日志达到此大小就滚动；
测试时，临时关闭二进制文件，不记录
set @@session.sql_log_bin=off;
delete from students where stuid=2000;
show binlog events in 'master.000002'; 无变化，未记录日志
select * from students; 没有stuid=2000，数据被删除但未记录
set @@session.sql_log_bin=on;
delete from students where stuid=1999;
show binlog events in 'master.000002'; 数据被删除并被记录
show binlog events in 'master.000002' from 440; 从440位置开始
show binlog events in 'master.000002' from 440 limit 2; 只显示两行
ls /var/lib/mysql/master.000001
cat /var/lib/mysql/master.index 显示master.000001/2/...
cd /var/lib/mysql/; mysqlbinlog master.000001 查看文件
show processlist; 线程列表
mysqlbinlog --start-position 440 master.000002 从440位置开始
mysqlbinlog --start-position 440 --stop-position=508 master.000002 440-508(结束)
增量和差异备份
image.png
备份和恢复
备份时，要启动二进制日志
centos7.4
man mysqldump
备份一个库
mysqldump mydb|less 输出至屏幕
mysqldump mydb > /tmp/mydb-fullback-$(date +%F-%H-%M-%S)
备份文件名称：库名-备份类型-日期
ls /tmp/
mysql
create database txdb;
exit
mysql txdb < /tmp/mydb-fullback-2018-05-07-21-35-41
mysql
use txdb
show tables;
select * from students;
exit
ll -h /var/lib/mysql/ 生成很多日志
线上系统备份时必须加锁
-x 锁定所有库的所有表，读锁
-l 锁定指定库所有表
备份多个库
mysqldump --databases mydb txdb mysql > /tmp/mydb-txdb-mysql-fullback-$(date +%F-%H-%M-%S)
less /tmp/mydb-txdb-mysql-fullback-2018-05-07-22-11-03
与创建一个库的区别是：会自动添加"create database"语句
centos7.4-2
yum -y install mariadb-server
systemctl start mariadb.service
mysql
grant all on *.* to 'admin'@'192.168.%.%' identified by 'admin';
grant all on *.* to 'admin'@'%.fgq.com' identified by 'admin';
flush privileges;
exit
centos7.4
mysql -uadmin -h192.168.1.8 -padmin < /tmp/mydb-txdb-mysql-fullback-2018-05-07-22-11-03
centos7.4-2
mysql
show databases;
mysql协议明文--不安全--ssl/vpn
centos7.4
mysql
use mydb
show table status\G 看当前库的存储引擎
show table status where engine='innodb'\G 当前库存储引擎是innodb的表
show table status where engine!='innodb'\G 当前库存储引擎不是innodb的表
exit
mysqldump --single-transaction --databases mydb
仅适用于InnoDB存储引擎；支持热备；适合线上系统；
备份后自动提交事务；自动释放事务锁
以上是无脑备份
完整备份+binlog(模拟增量/差异备份)
假设备份所有库
mysql
flush tables with read lock; 锁定所有表
flush logs; 滚动一下数据库的二进制日志(sql--新变化--文件开始处)
show master logs; 看日志文件有几个
show master status; 看现在处于哪个日志文件哪个位置(重放binlog的开始位置)
sql语句执行完成，形成最终数据结果，sql语句记录在二进制日志中
二进制日志和数据库--备份其中之一
恢复:
备份的数据库(即数据最终结果)+二进制日志重放(开始位置:最后一次备份结束位置)
exit
备份所有库--使用选项--简便操作
mysqldump -x -R -E --triggers --all-databases --master-data=2 --flush-logs > /tmp/alldatabases-fullback-$(date +%F-%H-%M-%S)
参考文档
-x 锁定所有库的所有表，读锁
-R 存储历程；备份指定库的存储过程和存储函数；
--triggers 备份指定库的触发器；
-E 备份指定库的调度器
--master-data[=#] 备份时处于哪个日志文件哪个位置，1--未注释，2--注释
--flush-logs 锁定表完成后，即进行日志刷新操作；
less /tmp/alldatabases-fullback-2018-05-08-00-01-47
mysql
use mydb
create table teachers (tid int unsigned primary key auto_increment,name varchar(200));
insert into teachers (name) values ('Ouyang Feng'),('Song Jiang');
show master status;
exit
systemctl stop mariadb.service 模拟故障
cd /var/lib/mysql/
mysqlbinlog master.000007 > /tmp/alldatabases-binlog-$(date +%F-%H-%M-%S)
rm -rf ./* 模拟故障
备份放在--非本机设备上/本机上与原数据不同的硬盘上
假设准备了一台新主机(仍是此centos7.4)
systemctl start mariadb.service
mysql
show databases;
set @@session.sql_log_bin=off; 关闭二进制日志--从备份中恢复，不需要记录二进制日志
或者
vim /etc/my.cnf.d/server.cnf
[mysqld]
#log_bin=master.log 注释掉此行
systemctl restart mariadb.service
\. /tmp/alldatabases-fullback-2018-05-08-00-01-47 加载备份的所有数据库数据
use mydb
show tables;
\. /tmp/alldatabases-binlog-2018-05-08-00-16-34 加载备份的二进制文件
show tables;
或者借助另一台主机centos7.4-2(不操作--了解写法)
假设和down掉的主机centos7.4有一样的数据库和一样的二进制文件
centos7.4上需要授权用户
mysql
grant all on *.* to 'admin'@'192.168.%.%' identified by 'admin';
grant all on *.* to 'admin'@'%.fgq.com' identified by 'admin';
flush privileges;
vim /etc/my.cnf.d/server.cnf
[mysqld]
#log_bin=master.log 注释掉此行
systemctl restart mariadb.service
centos7.4-2上需要连接centos7.4--192.168.1.7
mysql -uadmin -h192.168.1.7 -padmin < /tmp/alldatabases-fullback-2018-05-08-00-01-47
mysql -uadmin -h192.168.1.7 -padmin < /tmp/alldatabases-binlog-2018-05-08-00-16-34
centos7.4
mysql
show databases;
use mydb
show tables;
mysqldump备份--总有些内容没有顾及到，较麻烦--更好的方式用Xtrabackup
Xtrabackup
https://www.percona.com/
--Software--MySQL Database Software--Percona XtraBackup
--Feature Comparison版本比较
--Download Percona XtraBackup Now下载--选择合适的version和software
表空间管理系统--自管理的文件系统--自管理空间划分为盘区--每盘区/每修改/有一个日志序列号lsn: log sequence number
盘区1插入1行数据--lsn=1
盘区2插入1行数据--lsn=2
盘区1修改1行数据--lsn=3
......
假设lsn:1-1000
完全备份：根据lsn进行1-1000的备份
新修改的数据变化：
增量备份：lsn:第一次1001-1002 第二次lsn:1003-1007 第三次lsn:1008-1019
差异备份：lsn:第一次1001-1002 第二次lsn:1001-1007 第三次lsn:1001-1019
Xtrabackup备份出的数据集，用于做还原前，先做prepare(准备)操作，后做recover操作：mysqldump无此功能
prepare(准备)操作：
若此备份后，无其他增量备份：已提交的事务做同步，未提交的事务做回滚
若此备份后，有其他增量备份：已提交的事务做同步，未提交的事务保持原状(下次增量备份可能会提交)
完全备份--增量备份--修改部分内容--down机
恢复时：
完全备份+增量备份+binlog的时间点还原
完全备份+差异备份+binlog的时间点还原
完全备份--同步
增量备份(第一次增量--最后一次增量)--同步
最后是未同步--回滚
全部合并在一起，复制到mysql的数据目录(datadir)下，修改权限即可使用
二进制日志文件--时间点还原
前提--备份alldatabases 若是备份部分库--还原部分库即可
完全备份--备份所有库
InnoDB--热备--可读写；MyISAM--温备--只读
增量备份
InnoDB--热备--增量；MyISAM--温备--(另外一次)完全(MyISAM不支持增量)
centos7.4
rz 上传包文件(~)
yum -y install ./percona-xtrabackup-24-2.4.11-1.el7.x86_64.rpm
rpm -qa |grep percona 程序包没问题
rpm -ql percona-xtrabackup-24
man innobackupex
cd /var/lib/mysql/
mkdir -pv /mydata/xbdata
innobackupex --user=root --host=localhost /mydata/xbdata/
无密码，就不用写了，完全备份 显示completed OK!
ls /mydata/xbdata/; ls /mydata/xbdata/2018-05-08_16-47-58/
cd /mydata/xbdata/2018-05-08_16-47-58/
less backup-my.cnf 备份时所依赖的关键配置(非所有内容)
less xtrabackup_binlog_info 备份时处于哪个日志文件的哪个位置
less xtrabackup_checkpoints 备份类型/lsn的开始和结束/
less xtrabackup_info 程序文件信息--uuid/备份工具/命令/版本号/时间始末/二进制文件名称和位置/加密/压缩/增量/部分备份
没有增量--仅完全备份的还原--无binlog
systemctl stop mariadb.service 模拟故障
cd /var/lib/mysql; rm -rf ./*
准备
cd /mydata/xbdata/2018-05-08_16-47-58/
innobackupex --apply-log ./ 显示completed OK!
还原
innobackupex --copy-back ./ 显示completed OK!
ll /var/lib/mysql/ 属主和属组不对
chown -R mysql.mysql /var/lib/mysql/
systemctl start mariadb.service; ss -ntl
mysql
show databases;
exit
全量+增量/差异+binlog
最小权限
create user 'backup'@'localhost' identified by 'backup';
grant reload,lock tables,replication client,process on *.* to 'backup'@'localhost';
flush privileges;
exit
准备 全量备份
窗口1
innobackupex --user=backup --host=localhost --password=backup /mydata/xbdata/
显示completed OK!
ls /mydata/xbdata
cat /mydata/xbdata/2018-05-08_18-02-45/xtrabackup_checkpoints
backup_type = full-backuped
to_lsn = 1884712 日志序列号:1884712
mysql
use mydb
delete from students where stuid=1300;
delete from students where stuid=1400;
delete from students where stuid=1500;
不用退出--热备即可
窗口2
第一次增量备份
innobackupex --incremental /mydata/xbdata --incremental-basedir=/mydata/xbdata/2018-05-08_18-02-45
--incremental 当前备份的增量
--incremental-basedir 基于谁做增量
cat /mydata/xbdata/2018-05-08_18-20-37/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 1884712
to_lsn = 1888446
窗口1
insert into teachers (name) values ('Jiao Da');
select * from teachers;
窗口2
第二次增量备份
ll /mydata/xbdata/
innobackupex --incremental /mydata/xbdata --incremental-basedir=/mydata/xbdata/2018-05-08_18-20-37
cat /mydata/xbdata/2018-05-08_18-34-24/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 1888446
to_lsn = 1888734
窗口1
delete from teachers where tid=1;
select * from teachers;
窗口2
二进制日志文件备份
mkdir /mydata/binlogs/
cat /mydata/xbdata/2018-05-08_18-34-24/xtrabackup_info
mysqlbinlog -j 2088 /var/lib/mysql/master.000001 > /mydata/binlogs/2018-05-08_18-34-24-binlog
systemctl stop mariadb.service 模拟故障
rm -rf /var/lib/mysql/* 模拟故障
cd /mydata/xbdata/2018-05-08_18-02-45/ 进入全量备份文件夹中
仅作同步--redo，不做回滚--undo
innobackupex --apply-log --redo-only ./ 全量恢复
innobackupex --apply-log --redo-only ./ --incremental-dir=/mydata/xbdata/2018-05-08_18-20-37
全量备份和第一次增量备份合并
cat xtrabackup_checkpoints
backup_type = log-applied
last_lsn = 1888446 第一个增量结束位置
innobackupex --apply-log --redo-only ./ --incremental-dir=/mydata/xbdata/2018-05-08_18-34-24
全量+第一次增量+第二次增量合并
cat xtrabackup_checkpoints
last_lsn = 1888734 第二个(最后一个)增量结束位置
回滚操作
innobackupex --apply-log ./ 把未提交的事务回滚
还原
innobackupex --copy-back ./ 显示completed OK!
ll /var/lib/mysql/ 数据文件恢复，但属主和属组要更改
chown -R mysql.mysql /var/lib/mysql/*
systemctl start mariadb.service; ss -ntl
mysql
use mydb
select * from students where stuid=1300; 第一次修改删除了，显示无
select * from teachers; 第二次修改，添加了"Jiao Da"
第二次增量备份后，Ouyang Feng被删除，但仍有--使用binlog
set @@session.sql_log_bin=off; 使用binlog恢复时，要关闭二进制日志文件
\. /mydata/binlogs/2018-05-08_18-34-24-binlog 重放二进制日志
select * from teachers; "Jiao Da"没有了--恢复故障前的那一刻了
set @@session.sql_log_bin=on; 开启二进制日志
接下来赶紧做一下全量备份/上线后再做全量备份
备份部分库--参考文档
生产中--策略：每月1号--全量；非1号--增量；
恢复时--手工操作--监控每一步都不能出错；
备份后--输出的内容已邮件形式保存到邮箱；
图1：主从复制
image.png
图2：级联复制
image.png
图3：双主模型1
image.png
图4：多主模型2
image.png
图5：一从多主模型
image.png
图6：不同的主从集群模型--基于奇偶
image.png
图7：shard分片冗余
image.png
超过系统的承载能力--需要扩展
向上--性价比低
向外--多增加主机
主节点down了--冗余/从节点代替
主节点无法承载写请求--高可用
主从复制 如上图1
一主多从
异步：从节点从主节点复制更新数据时有延迟(有时间窗)
读写分离器(read write splitter)
将用户的请求分发至合适的位置
读请求--平均分发出去；
写请求--一点没有少--对写请求没有负载均衡
有效利用时间差
drop误删库，有延迟，拔网线，从节点还未删除；或从节点删除，我们还有备份
主节点--只写
从节点--只读+从主节点复制写操作
如果主节点只写，还是压力大，解决方案：级联复制 如上图2
双主模型--互为主从 如上图3
多主模型 如上图4
一从多主模型 如上图5
基于奇偶范围/列表查询 如上图6
shard分片冗余 如上图7
主从复制
centos7.4 主节点 192.168.1.5
centos7.4-2 从节点 192.168.1.6
1 时间同步
2 安装mariadb-server
yum -y install mariadb-server
3 主节点centos7.4
vim /etc/my.cnf.d/server.cnf
[mysqld]
server-id = 1
log-bin = master-log 最好放在非/var/lib/myqsl路径下或者不同的磁盘上
skip_name_resolve = on centos6不用写on，只需要把参数写在此处即可
systemctl start mariadb.service
mysql
show master status; 位置245
grant replication client,replication slave on *.* to 'repluser'@'192.168.1.%' identified by 'replpass';
最小权限--192.168.1.6
flush privileges;
show master status; 位置变化497
show binlog events in 'master-log.000003';
show binlog events in 'master-log.000003'\G
主节点创建拥有复制权限的用户账号时，从节点不需要同步，从497开始复制即可
4 从节点centos7.4-2
vim /etc/my.cnf.d/server.cnf
[mysqld]
server-id = 7
relay-log = relay-log
skip_name_resolve = on
systemctl start mariadb.service
mysql
help change master to 查看用法
change master to master_host='192.168.1.5',master_user='repluser',master_password='replpass',master_log_file='master-log.000003',master_log_pos=497;
ll /var/lib/mysql 生成几个新文件
cat /var/lib/mysql/master.info 执行的命令
cat /var/lib/mysql/relay-log.info 开始和结束位置
show slave status\G
help start slave 查看用法
start slave; IO_THREAD | SQL_THREAD都开启
start slave io_thread; 只复制不重放
show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Relay_Log_File: relay-log.000002 Relay_Log_Pos: 530 变化--做了初始化
Read_Master_Log_Pos: 497 没有变化
5 测试
主节点centos7.4
create database mydb;
show master status; 580位置
从节点centos7.4-2
show slave status\G
Read_Master_Log_Pos: 580 已经复制过来
Seconds_Behind_Master: 0 没有落后主节点
show databases; 显示mydb已经创建
主节点centos7.4
use mydb
create table tbl1 (id int,name char(50));
insert into tbl1 values (1,'Ouyang Feng');
从节点centos7.4-2
show slave status\G
Read_Master_Log_Pos: 882 数据已经复制过来
Seconds_Behind_Master: 0
use mydb
show tables;
select * from tbl1; 数据ok
6 恢复初始状态
centos7.4和centos7.4-2
systemctl stop mariadb.service
rm -rf /var/lib/mysql/*
主主复制 互为主从 双主复制
centos7.4 192.168.1.5 node1
centos7.4-2 192.168.1.6 node2
centos7.4
vim /etc/my.cnf.d/server.cnf
[mysqld]
server-id = 1
log-bin = master-log
relay-log = relay-log
skip_name_resolve = on
auto_increment_offset = 1 从几增长
auto_increment_increment = 2 加几
systemctl start mariadb.service
mysql
grant replication client,replication slave on *.* to 'repluser'@'192.168.1.%' identified by 'replpass';
flush privileges;
show master status; 507位置
centos7.4-2
[mysqld]
server-id = 7
log-bin = master-log
relay-log = relay-log
skip_name_resolve = on
auto_increment_offset = 2
auto_increment_increment = 2
systemctl start mariadb.service
mysql
grant replication client,replication slave on *.* to 'repluser'@'192.168.1.%' identified by 'replpass';
flush privileges;
show master status; 507位置
centos7.4-2
node1是node2的主
change master to master_host='192.168.1.5',master_user='repluser',master_password='replpass',master_log_file='master-log.000003',master_log_pos=507;
show slave status\G
centos7.4
node1是node2的从
change master to master_host='192.168.1.6',master_user='repluser',master_password='replpass',master_log_file='master-log.000003',master_log_pos=507;
show slave status\G
centos7.4和centos7.4-2
start slave;
show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
tail /var/log/mariadb/mariadb.log 启动的线程出现在错误日志中
centos7.4
create database mydb;
centos7.4-2
show databases;
use mydb
create table tbl1 (id int unsigned auto_increment primary key,name char(50));
centos7.4
use mydb
show tables;
desc tbl1;
insert into tbl1 (name) values ('Jia Baoyu'),('Sun Erniang');
select * from tbl1; 显示id为1和3
centos7.4-2
select * from tbl1; 显示id为1和3
insert into tbl1 (name) values ('Lin Chong'),('Sun Houzi');
select * from tbl1; 显示id为1/3/4/6；跳过了id=2，出现间隙
centos7.4
select * from tbl1; 跳过了id=2，也出现间隙
centos7.4和centos7.4-2
systemctl stop mariadb.service
rm -rf /var/lib/mysql/*
恢复初始状态
主从复制 从节点添加读锁--不允许写操作
主节点centos7.4
vim /etc/my.cnf.d/server.cnf
[mysqld]
server-id = 1
log-bin = master-log
skip_name_resolve = on
systemctl start mariadb.service
mysql
show master status; 位置245
grant replication client,replication slave on *.* to 'repluser'@'192.168.1.%' identified by 'replpass';
最小权限--192.168.1.6
flush privileges;
select @@global.read_only; 显示0--off
show master status; 位置497
从节点centos7.4-2
vim /etc/my.cnf.d/server.cnf
[mysqld]
server-id = 7
relay-log = relay-log
skip_name_resolve = on
read_only = on 只读选项
systemctl restart mariadb.service
mysql
show global variables like 'read_only'; 显示on
从节点centos7.4-2
change master to master_host='192.168.1.5',master_user='repluser',master_password='replpass',master_log_file='master-log.000003',master_log_pos=497;
start slave;
show slave status\G
主节点centos7.4
create database mydb;
use mydb
create table tbl1 (id int unsigned,name char(30));
从节点centos7.4-2
use mydb
show tables;
desc tbl1;
主节点centos7.4
grant select,update,create,drop on testdb.* to 'test'@'192.168.1.%' identified by 'testpass';
flush privileges;
mysql -utest -ptestpass -h192.168.1.5
create database testdb; 主节点上可修改数据
use testdb
create table t1 (name char(30));
从节点centos7.4-2
use testdb
show tables;
主节点centos7.4
mysql -utest -ptestpass -h192.168.1.6 连接至从节点ok
show databases;
use testdb
show tables;
insert into t1 values ('Hello');
显示"running with the --read-only option so it cannot execute this statement"
select * from t1; 连接至从节点上--写不可以，读可以(非super权限的账号)
图1
image.png
半同步复制 如上图1
需要安装插件来实现半同步复制
主节点centos7.4
rpm -ql mariadb-server
/usr/lib64/mysql/plugin/semisync_master.so 半同步主节点插件
/usr/lib64/mysql/plugin/semisync_slave.so 半同步从节点插件
设置为同步的从节点必须安装
设置为异步的从节点不安装
mysql
show plugins; 启用的插件
help install plugin
install plugin rpl_semi_sync_master soname 'semisync_master';
rpl_semi_sync_master 插件名
soname 模块名
show plugins; 显示rpl_semi_sync_master
show global variables like 'rpl_semi_%';
set @@global.rpl_semi_sync_master_enabled=on;
show global variables like 'rpl_semi_%'; 显示为on
rpl_semi_sync_master_timeout 从节点同步时长超过此时间(ms)，即降级为异步
从节点centos7.4-2
mysql
install plugin rpl_semi_sync_slave soname 'semisync_slave';
show plugins; 显示rpl_semi_sync_slave
show global variables like 'rpl_semi_%';
set @@global.rpl_semi_sync_slave_enabled=on;
show global variables like 'rpl_semi_%'; 显示为on
主节点centos7.4
show global status like 'rpl_semi_%';
Rpl_semi_sync_master_clients 有几个半同步的客户端
从节点centos7.4-2
stop slave io_thread;
start slave io_thread;
主节点centos7.4
show global status like 'rpl_semi_%';
Rpl_semi_sync_master_clients 显示为1
非事务型
Rpl_semi_sync_master_net_avg_wait_time 平均等待时长
Rpl_semi_sync_master_net_wait_time 等待时长
Rpl_semi_sync_master_net_waits 等待几次
事务型
Rpl_semi_sync_master_tx_avg_wait_time
Rpl_semi_sync_master_tx_wait_time
Rpl_semi_sync_master_tx_waits
use mydb
show tables;
desc tbl1;
insert into tbl1 values (1,'Yang Guo'),(2,'Xiao Longnv');
insert into tbl1 values (3,'Xu Zhu'),(4,'Qiao Feng');
show global status like 'rpl_semi_%'; 数值发生变化
可以看出为了实现半同步复制，需要付出的时间代价
Rpl_semi_sync_master_net_wait_time 包含 Rpl_semi_sync_master_tx_wait_time
Rpl_semi_sync_master_net_wait_time 整体的时长
Rpl_semi_sync_master_tx_wait_time 事务的时长
从节点centos7.4-2
use mydb
show tables;
select * from tbl1;
10个从节点--2个设置为半同步--根据需要：同步客户端越少越好
同一个机柜内的设置为半同步，跨机柜做异步；
带宽充足--做同步，其他做异步；
复制过滤器
主服务器上过滤--可能丢失数据
从服务器上过滤--io增大--建议
参考文档
从节点centos7.4-2
show slave status\G
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
vim /etc/my.cnf.d/server.cnf
[mysqld]中添加选项
replicate_do_db = hidb 多个表用逗号或空格隔开
systemctl restart mariadb.service
show slave status\G 显示Replicate_Do_DB: hidb，线程自动启动(可以设置为关闭)
主节点centos7.4
create database hidb; 有延迟
从节点重启后，rpl_semi_sync_slave_enabled参数为off
从节点centos7.4-2
select @@global.rpl_semi_sync_slave_enabled; 显示为0
show databases; 显示hidb
主节点centos7.4
use mydb
show tables;
create table tbl2 (name varchar(30));
show tables; 显示tbl2
从节点centos7.4-2
show tables; 未显示tbl2，过滤后，仅同步hidb到从节点
偶尔会用到过滤
图1：PXC：Percona XtraDB Cluster
image.png
复制的监控和维护--更多内容，参考文档
主节点centos7.4
help purge
PURGE BINARY LOGS TO 'mysql-bin.010'; 某文件(序号)之前的内容都清除
PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26'; 某时间点之前的内容都清除
show binary logs; 显示master-log.000001/2/3
如果master-log.000003之前的内容都不需要了，就清理掉
purge binary logs to 'master-log.000003'; 只显示master-log.000003，1和2被清理了
如果都清理掉了，就恢复不了；
一定要复制一份，移到其他磁盘保存下来，以备不时之需；
purge命令不能随便尝试
ls /var/lib/mysql/ master-log.000003之前的内容被清理了
cat /var/lib/mysql/master-log.index 只有"./master-log.000003"
PXC:Percona XtraDB Cluster 如上图1
复制集群 多主节点集群 主从复制架构 不依赖二进制日志
参考文档
mariadb-cluster 也有类似功能--但是需要第三方插件--需要下载，系统不自带
图1：ProxySQL
image.png
主从复制的读写分离
其他中间件参考文档，此处仅介绍ProxySQL
ProxySQL
下载：http://www.proxysql.com/
centos7.4--主节点--192.168.1.5
centos7.4-2--从节点1--192.168.1.6
centos7.4-3--从节点2--192.168.1.7
centos7.4-4--ProxySQL节点--192.168.1.8
1 同步时间
2 安装mariadb-server
centos7.4/centos7.4-2/centos7.4-3
yum -y install mariadb-server (centos7.4-4先不安装)
3 centos7.4--主节点
vim /etc/my.cnf.d/server.cnf
[mysqld]
server-id = 1
log-bin = master-log
skip_name_resolve = on
innodb_file_per_table = on
sync_binlog = 1
systemctl start mariadb.service; ss -ntl
mysql
grant replication client,replication slave on *.* to 'repluser'@'192.168.1.%' identified by 'replpass';
flush privileges;
show master status;
4 centos7.4-2--从节点1和centos7.4-3--从节点2
vim /etc/my.cnf.d/server.cnf
[mysqld]
server-id = 2/3 2--从节点1；3--从节点2；
relay-log = relay-log
skip_name_resolve = on
innodb_file_per_table = on
read_only = on
systemctl start mariadb.service; ss -ntl
mysql
change master to master_host='192.168.1.5',master_user='repluser',master_password='replpass',master_log_file='master-log.000003',master_log_pos=497;
start slave;
show slave status\G
5 测试
centos7.4--主节点
create database hidb;
centos7.4-2--从节点1和centos7.4-3--从节点2
show databases; 出现hidb
6 安装ProxySQL
授权用户
centos7.4--主节点
grant all on *.* to 'proxysql'@'192.168.1.%' identified by 'proxypass';
flush privileges;
centos7.4-2--从节点1和centos7.4-3--从节点2
show grants for 'proxysql'@'192.168.1.%'; 已经同步过来
配置ProxySQL
centos7.4-4--ProxySQL节点
rz 上传proxysql-1.3.6-1-centos7.x86_64.rpm
yum -y install ./proxysql-1.3.6-1-centos7.x86_64.rpm
rpm -ql proxysql
cp /etc/proxysql.cnf{,.bak}
vim /etc/proxysql.cnf
admin_variables中定义
mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock" 去掉注释
refresh_interval=2000 去掉注释
debug=true 去掉注释
mysql_variables中定义
interfaces="0.0.0.0:3306;/tmp/mysql.sock" mysql.sock在/tmp下
因为是代理mysql的，所以监听在3306上，更易于访问
default_schema="hidb"
mysql_servers中的定义
{
address = "192.168.1.5"
port = 3306
hostgroup = 0
status = "ONLINE"
weight = 1
compression = 0
},
{
address = "192.168.1.6"
port = 3306
hostgroup = 1
status = "ONLINE"
weight = 1
compression = 0
},
{
address = "192.168.1.7"
port = 3306
hostgroup = 1
status = "ONLINE"
weight = 1
compression = 0
}
mysql_users中的定义
{
username = "proxysql"
password = "proxysqlpass"
default_hostgroup = 0
active = 1
}
mysql_replication_hostgroups中定义
{
writer_hostgroup=0
reader_hostgroup=1
comment="repl cluster 1"
}
service proxysql start 可使用service命令；ss -ntl 3306端口--4个
yum -y install mariadb-server
mysql 不能连接，mysql.sock文件在/tmp目录下(/etc/proxysql.cnf配置文件定义)
ls /tmp
mysql -S /tmp/mysql.sock -uproxysql -pproxypass
show databases;
use hidb
create table t1 (id int,name char(30));
show tables;
insert into t1 values (1,'tom');
insert into t1 values (2,'jerry');
select * from t1;
centos7.4和centos7.4-2和centos7.4-3
show tables from hidb; 都有
select * from hidb.t1; 都有
centos7.4-2
create database db2;
centos7.4-3
create database db3;
centos7.4-4
show databases; 无内容--配置文件中默认是0组
vim /etc/proxysql.cnf
mysql_users中的定义
{
username = "proxysql"
password = "proxysqlpass"
default_hostgroup = 1 0改为1
active = 1
}
service proxysql restart
mysql -S /tmp/mysql.sock -uproxysql -pproxypass
show databases; 还是没有node2/3db，仍是以主节点为主
service proxysql status；service proxysql stop 为MHA准备
chkconfig proxysql off
图1：减轻主节点的负载(写操作)
image.png
图2：DRBD:分布式的复制块设备
image.png
图3：管理节点和代理(数据)节点
image.png
图4：管理节点可管理多个主从复制集群
image.png
主节点down机--高可用
解决方案1--如上图1
解决方案2--如上图2 drbd:Distributed Replicated Block Device--分布式的复制块设备
解决方案3--如上图3 MHA 详情参考文档
从节点1提升为新的主节点--但仅有一部分数据
从节点1--需要把从其他节点同步过来的数据合并起来--避免数据不一致
故障主节点--使用"漂移IP"--主节点故障，IP漂移到新的主节点
各个从节点"change master to..."要指向"漂移IP"
前端--读写分离器配置--也要使用"漂移IP"
判定谁代替主节点(优先级提升)+监测主节点down机+IP漂移至从节点(脚本)
MHA--无IP漂移功能--keepalived可代替
配置1主n备
管理节点和代理(数据)节点 如上图3
如何获取合并数据
每个节点：中继日志(禁止自动清理)+启动二进制日志(很有可能成为新的主节点)+read only(被提升为主节点/管理节点通过agent将read only自动关闭)
管理节点--可管理多个主从复制集群--可为每个集群提供一个单独的定义--如上图4
一个application--代表一个集群
从节点切换为主节点时，需要有权限读取各个从节点的中继日志，且ssh无密钥连接
不能有认证
某个节点生成一对密钥--复制给其他节点--即可互通了
MHA
centos7.4--主节点--192.168.1.5
centos7.4-2--从节点1--192.168.1.6
centos7.4-3--从节点2--192.168.1.7
centos7.4-4--管理节点--192.168.1.8
根据前面的ProxySQL实验做一下调整即可
vim /etc/hosts 确保可通过主机名解析
192.168.1.5 centos7.4.fgq.com
192.168.1.6 centos7.4-2.fgq.com
192.168.1.7 centos7.4-3.fgq.com
192.168.1.8 centos7.4-4.fgq.com
centos7.4--主节点
vim /etc/my.cnf.d/server.cnf
[mysqld]
server-id = 1
log-bin = master-log
relay-log = relay-log 添加中继日志
skip_name_resolve = on
innodb_file_per_table = on
sync_binlog = 1
systemctl restart mariadb.service；ss -ntl
centos7.4-2和centos7.4-3--从节点
vim /etc/my.cnf.d/server.cnf
[mysqld]
server-id = 2
log-bin = master-log 启动二进制日志
relay-log = relay-log
skip_name_resolve = on
innodb_file_per_table = on
read_only = on
relay_log_purge = 0 启动清理中继日志
systemctl restart mariadb.service；ss -ntl
centos7.4--主节点
创建管理权限的用户账号
mysql
grant all on *.* to 'mhaadmin'@'192.168.1.%' identified by 'mhapass';
flush privileges;
centos7.4-2和centos7.4-3--从节点
mysql
show grants for 'mhaadmin'@'192.168.1.%'; 从节点自动同步管理权限的用户账号
centos7.4-4--管理节点
安装MHA
管理节点安装MHA的manager和node
主/从节点安装MHA的node
下载
https://github.com/yoshinorim/mha4mysql-manager
rpm文件夹--.spec文件--根据.spec文件自定义rpm包
https://github.com/yoshinorim/mha4mysql-manager/wiki/Downloads
直接下载rpm包
当前节点需要安装manager和node(非关键/可能用到其中的工具)
rz 上传mha4mysql-manager-0.56-0.el6.noarch.rpm和mha4mysql-node-0.56-0.el6.noarch.rpm
yum -y install ./mha4mysql-*.rpm 依赖epel很多模块/确保仓库ok
scp mha4mysql-node-0.56-0.el6.noarch.rpm centos7.4.fgq.com:/root/
for i in {-2,-3};do scp mha4mysql-node-0.56-0.el6.noarch.rpm centos7.4$i.fgq.com:/root/;done
centos7.4和centos7.4-2和centos7.4-3
yum -y install ./mha4mysql-node-0.56-0.el6.noarch.rpm
配置ssh互信--没有密码可互相通信
在一个主机上生成一对密钥，完成自己主机的认证，后将其复制到其他主机上
centos7.4-4--管理节点
ssh-keygen -t rsa -P '' 生成一对密钥
ssh-copy-id -i .ssh/id_rsa.pub root@centos7.4-4.fgq.com
ssh centos7.4-4.fgq.com 显示已连接
exit
复制密钥和认证文件给其他节点
centos7.4-2和centos7.4-3
mkdir .ssh centos7.4有此文件夹，就不要创建，否则就创建
centos7.4-4--管理节点
scp .ssh/id_rsa .ssh/authorized_keys centos7.4.fgq.com:/root/.ssh
for i in {-2,-3};do scp -p .ssh/id_rsa .ssh/authorized_keys root@centos7.4$i.fgq.com:/root/.ssh/;done
ssh centos7.4-3.fgq.com
ssh centos7.4-2.fgq.com
ssh centos7.4.fgq.com 不需要密码即可登陆
cat /etc/ssh/ssh_config
centos7.4
ssh centos7.4.fgq.com 第一次连接需要回答yes
cat /etc/ssh/ssh_config; man ssh
ssh -o StrictHostKeyChecking=no centos7.4-2.fgq.com 第一次直接连接/不回答yes
确保连接任何一个节点都ok，包括连接自己
定义集群
centos7.4-4
mkdir /etc/masterha 自己写配置文件
vim /etc/masterha/app1.cnf
[server default]
user=mhaadmin
password=mhapass
manager_workdir=/data/masterha/app1 此目录可自动创建
manager_log=/data/masterha/app1/manager.log
remote_workdir=/data/masterha/app1
ssh_user=root
repl_user=repluser 之前实验创建过的复制用户
repl_password=replpass
ping_interval=1
[server1]
hostname=192.168.1.5
ssh_port=22
candidate_master=1
[server2]
hostname=192.168.1.6
ssh_port=22
candidate_master=1
[server3]
hostname=192.168.1.7
ssh_port=22
candidate_master=1
检测是否可正常连接
masterha_check_ssh --conf=/etc/masterha/app1.cnf
检测集群是否可正常使用
masterha_check_repl --conf=/etc/masterha/app1.cnf
显示Not OK--User repluser does not exist
之前实验时，主节点创建repluser，没有让从节点同步该sql语句
需要在主节点再创建一次，从节点会自动同步
centos7.4
grant replication client,replication slave on *.* to 'repluser'@'192.168.1.%' identified by 'replpass';
show grants for 'repluser'@'192.168.1.%';
centos7.4-2和centos7.4-3
show grants for 'repluser'@'192.168.1.%'; 已经同步过来了
centos7.4-4
masterha_check_repl --conf=/etc/masterha/app1.cnf 显示集群OK
[warning] master_ip_failover_script is not defined
自己写脚本--主节点down机，把主节点IP地址down掉，在新的主节点上把IP加上
注意设置主从时，需要change to 到漂移的IP上
[warning] shutdown_script is not defined
脚本--停止原来的主节点或把主节点切换为从节点，避免脑裂
启动服务
masterha_manager --conf=/etc/masterha/app1.cnf
ps -aux 显示perl /usr/bin/masterha_manager...当前处于运行状态
masterha_check_status --conf=/etc/masterha/app1.cnf 当前集群状态是否ok
故障测试
centos7.4
killall mysqld mysqld_safe
ps aux |grep msyqld 已经kill掉了
rm -rf /var/lib/mysql/*
centos7.4-4
执行"masterha_manager --conf=/etc/masterha/app1.cnf"命令的窗口会显示信息并退出
架构已经改变，需要重新设定并启动
masterha_check_repl --conf=/etc/masterha/app1.cnf
显示Not OK，显示Current Alive Master: 192.168.1.6 已经切换
centos7.4-2--192.168.1.6
show global variables like 'read_only'; 显示off，变为主节点后，自动关闭只读限制
故障的主节点要修复
启动--在新的主节点上做一次备份，恢复到centos7.4--node1上
在node1上，指向centos7.4-2--node2(此时为主节点)，node1为从节点
再次启动MHA:masterha_manager --conf=/etc/masterha/app1.cnf
centos7.4-2 新主节点
mysqldump -x -R -E --triggers --all-databases --master-data=2 --flush-logs > /tmp/alldatabases.sql
centos7.4 故障主节点
vim /etc/my.cnf.d/server.cnf
在[mysqld]中添加信息：read_only = on
systemctl start mariadb.service
centos7.4-2
scp /tmp/alldatabases.sql centos7.4.fgq.com:/root/
centos7.4
mysql < /root/alldatabases.sql
mysql
show databases; 数据恢复
命令行：head -30 /root/alldatabases.sql 备份时，处于哪个日志文件的哪个位置
显示CHANGE MASTER TO MASTER_LOG_FILE='master-log.000002', MASTER_LOG_POS=245;
change master to change master to master_host='192.168.1.6',master_user='repluser',master_password='replpass',master_log_file='master-log.000002',master_log_pos=245;
start slave;
show slave status\G
centos7.4-4 管理节点
检测集群是否ok
masterha_check_repl --conf=/etc/masterha/app1.cnf
显示OK 192.168.1.6(192.168.1.6:3306) (current master)
masterha_manager --conf=/etc/masterha/app1.cnf
nohup masterha_manager --conf=/etc/masterha/app1.cnf &> /data/masterha/app1/manager.log &
&--后台运行，nohup--当前终端关闭，此程序也不关闭
如果主节点down机，此程序会关闭
ps aux; jobs -l
centos7.4-2 新主节点down机
killall mysqld mysqld_safe
centos7.4-4
回车几次，就可以看到nohup程序关闭
masterha_check_repl --conf=/etc/masterha/app1.cnf 显示Not OK
图1
image.png
图2
image.png
网友评论