接着上周的mysql，重点终结日志管理 ，备份还原，主从复制和mysql集群
事务日志 transaction log
错误日志 error log
通用日志 general log
慢查询日志 slow query log
二进制日志 binary log
中继日志 reley log
1.事务日志
事务型存储引擎自行管理和使用，建议和数据文件分开存放
redo log
undo logInnodb事务日志相关配置：
show variables like ‘%innodb_log%‘;
innodb_log_file_size 5242880 每个日志文件大小
innodb_log_files_in_group 2 日志组成员个数
innodb_log_group_home_dir ./ 事务文件路径
innodb_flush_log_at_trx_commit 默认为1innodb_flush_log_at_trx_commit说明：设置为1，同时sync_binlog = 1表示最高级别的容错 innodb_use_global_flush_log_at_trx_commit的值确定是否可以使用SET语句重置此变量
1默认情况下，日志缓冲区将写入日志文件，并在每次事务后执行刷新到磁盘。 这是完全遵守ACID特性
0提交时没有任何操作; 而是每秒执行一次日志缓冲区写入和刷新。 这样可以提供更好的性能，但服务器崩溃可以清除最后一秒的事务
2每次提交后都会写入日志缓冲区，但每秒都会进行一次刷新。 性能比0略好一些，但操作系统或停电可能导致最后一秒的交易丢失
3模拟MariaDB 5.5组提交（每组提交3个同步），此项MariaDB 10.0支持
2.错误日志
mysqld启动和关闭过程中输出的事件信息
mysqld运行中产生的错误信息
event scheduler运行一个event时产生的日志信息
在主从复制架构中的从服务器上启动从服务器线程时产生的信息
错误日志相关配置
SHOW GLOBAL VARIABLES LIKE ‘log_error‘
错误文件路径
log_error=/PATH/TO/LOG_ERROR_FILE
是否记录警告信息至错误日志文件
log_warnings=1|0 默认值1通用日志：记录对数据库的通用操作，包括错误的SQL语句
文件：file，默认值
表：table
通用日志相关设置
general_log=ON|OFF
general_log_file=HOSTNAME.log
log_output=TABLE|FILE|NONE
4.慢查询日志
slow_query_log=ON|OFF 开启或关闭慢查询
long_query_time=N 慢查询的阀值，单位秒
slow_query_log_file=HOSTNAME-slow.log 慢查询日志文件
log_slow_filter = admin,filesort,filesort_on_disk,full_join,full_scan,
query_cache,query_cache_miss,tmp_table,tmp_table_on_disk
上述查询类型且查询时长超过long_query_time，则记录日志
log_queries_not_using_indexes=ON 不使用索引或使用全索引扫描，不论是否达到慢查询阀值的语句是否记录日志，默认OFF，即不记录
log_slow_rate_limit = 1 多少次查询才记录，mariadb特有
log_slow_verbosity= Query_plan,explain 记录内容
log_slow_queries = OFF 同slow_query_log 新版已废弃5.二进制日志
二进制日志
记录导致数据改变或潜在导致数据改变的SQL语句
记录已提交的日志
不依赖于存储引擎类型
功能：通过“重放”日志文件中的事件来生成数据副本
注意：建议二进制日志和数据文件分开存放
6.中继日志：relay log
主从复制架构中，从服务器用于保存从主服务器的二进制日志中读取的事件
1.二进制日志记录格式
二进制日志记录三种格式
基于“语句”记录：statement，记录语句，默认模式
基于“行”记录：row，记录数据，日志量较大
混合模式：mixed, 让系统自行判定该基于哪种方式进行
格式配置
show variables like ‘binlog_format‘;
2.二进制日志文件的构成
有两类文件
日志文件：mysql|mariadb-bin.文件名后缀，二进制格式
如： mariadb-bin.000001
索引文件：mysql|mariadb-bin.index，文本格式
3.二进制日志相关的服务器变量：
sql_log_bin=ON|OFF：是否记录二进制日志，默认ON
log_bin=/PATH/BIN_LOG_FILE：指定文件位置；默认OFF，表示不启用二进制日志功能，上述两项都开启才可
binlog_format=STATEMENT|ROW|MIXED：二进制日志记录的格式，默认STATEMENT
max_binlog_size=1073741824：单个二进制日志文件的最大体积，到达最大值会自动滚动，默认为1G
说明：文件达到上限时的大小未必为指定的精确值
sync_binlog=1|0：设定是否启动二进制日志即时同步磁盘功能，默认0，由操作系统负责同步日志到磁盘
expire_logs_days=N：二进制日志可以自动删除的天数。 默认为0，即不自动删除
4.二进制日志相关配置
查看mariadb自行管理使用中的二进制日志文件列表，及大小
SHOW {BINARY | MASTER} LOGS
查看使用中的二进制日志文件
SHOW MASTER STATUS
查看二进制文件中的指定内容
SHOW BINLOG EVENTS [IN ‘log_name‘] [FROM pos] [LIMIT [offset,] row_count]
show binlog events in ‘mysql-bin.000001‘ from 6516 limit 2,3
mysqlbinlog：二进制日志的客户端命令工具
命令格式：
mysqlbinlog [OPTIONS] log_file…
--start-position=# 指定开始位置
--stop-position=#
--start-datetime=
--stop-datetime=
时间格式：YYYY-MM-DD hh:mm:ss
--base64-output[=name]
-v -vvv
示例：mysqlbinlog --start-position=6787 --stop-position=7527 /var/lib/mysql/mariadb-bin.000003 -v
mysqlbinlog --start-datetime="2018-01-30 20:30:10" --stop-datetime="2018-01-30 20:35:22" mariadb-bin.000003 -vvv二进制日志事件的格式：
# at 328
#151105 16:31:40 server id 1 end_log_pos 431 Query thread_id=1 exec_time=0 error_code=0
use `mydb`/*!*/;
SET TIMESTAMP=1446712300/*!*/;
CREATE TABLE tb1 (id int, name char(30))
/*!*/;
事件发生的日期和时间：151105 16:31:40
事件发生的服务器标识：server id 1
事件的结束位置：end_log_pos 431
事件的类型：Query
事件发生时所在服务器执行此事件的线程的ID：thread_id=1
语句的时间戳与将其写入二进制文件中的时间差：exec_time=0
错误代码：error_code=0
事件内容：
GTID：Global Transaction ID，mysql5.6以mariadb10以上版本专属属性：GTID清除指定二进制日志：
PURGE { BINARY | MASTER } LOGS
{ TO ‘log_name‘ | BEFORE datetime_expr }
示例：
PURGE BINARY LOGS TO ‘mariadb-bin.000003’;删除3之前的日志
PURGE BINARY LOGS BEFORE ‘2017-01-23‘;
PURGE BINARY LOGS BEFORE ‘2017-03-22 09:25:30‘;
删除所有二进制日志，index文件重新记数
RESET MASTER [TO #]; 删除所有二进制日志文件，并重新生成日志文件，文件名从#开始记数，默认从1开始，一般是master主机第一次启动时执行，MariaDB10.1.6开始支持TO #
切换日志文件：
FLUSH LOGS;为什么要备份
灾难恢复：硬件故障、软件故障、自然灾害、******、误操作测试等数据丢失场景
备份注意要点
能容忍最多丢失多少数据
恢复数据需要在多长时间内完成
需要恢复哪些数据
还原要点
做还原测试，用于测试备份的可用性
还原演练
备份类型：
完全备份，部分备份
完全备份：整个数据集
部分备份：只备份数据子集，如部分库或表
完全备份、增量备份、差异备份
增量备份：仅备份最近一次完全备份或增量备份 （如果存在增量）以来变化的数据，备份较快，还原复杂
差异备份：仅备份最近一次完全备份以来变化的数据，备份较慢，还原简单
注意：二进制日志文件不应该与数据文件放在同一磁盘
冷、温、热备份
冷备：读写操作均不可进行
温备：读操作可执行；但写操作不可执行
热备：读写操作均可执行
MyISAM：温备，不支持热备
InnoDB：都支持
物理和逻辑备份
物理备份：直接复制数据文件进行备份，与存储引擎有关，占用较多的空间，速度快
逻辑备份：从数据库中“导出”数据另存而进行的备份，与存储引擎无关，占用空间少，速度慢，可能丢失精度
备份工具
cp, tar等复制归档工具：物理备份工具，适用所有存储引擎；只支持冷备；完全和部分备份
LVM的快照：先加锁，做快照后解锁，几乎热备；借助文件系统工具进行备份
mysqldump：逻辑备份工具，适用所有存储引擎，温备；支持完全或部分备份；对InnoDB存储引擎支持热备，结合binlog的增量备份
xtrabackup：由Percona提供支持对InnoDB做热备(物理备份)的工具，支持完全备份、增量备份
MariaDB Backup： 从MariaDB 10.1.26开始集成，基于Percona XtraBackup 2.3.8实现
mysqlbackup：热备份， MySQL Enterprise Edition组件
mysqlhotcopy：PERL 语言实现，几乎冷备，仅适用于MyISAM存储引擎，使用LOCK TABLES、FLUSH TABLES和cp或scp来快速备份数据库实验
1.MySQL的备份还原(mysqldump)
开启二进制日志
开启二进制日志需要将MySQL中的sql_log_bin和log_bin这两个选项
1.开启sql_log_bin
系统中默认开启sql_log_bin选项所以此处无需修改
MariaDB [(none)]> SHOW VARIABLES LIKE ‘sql_log_bin‘;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin | ON |
+---------------+-------+
1 row in set (0.01 sec)
2.开启log_bin
此选项需要对MySQL的配置文件进行修改，在修改之前需要先创建一个二进制日志存放的位置。注意：不要和数据库存放在统一磁盘内，不要和数据库存放在统一磁盘内，不要和数据库存放在统一磁盘内重要的事情说三遍。
[root@localhost ~]# mkdir /data/bin
[root@localhost ~]# chown -R mysql.mysql /data/bin #将目录的属主和属组都改为mysql
二进制日志目录创建完毕后，修改配置文件，添加log-bin选项，并指定路径，此处要注意mysql-bin是二进制日志的抬头。
[root@localhost ~]# vim /etc/mysql/my.cnf
log-bin=/data/bin/mysql-bin #mysql-bin为二进制日志的文件名的抬头
以上配置完成后重启mysql服务
[root@localhost ~]# service mysqld restart
Restarting mysqld (via systemctl): [ OK ]
此时二进制日志目录下已经多出了两个二进制日志文件
[root@localhost ~]# ll /data/bin/
total 12
-rw-rw---- 1 mysql mysql 351 May 6 16:51 mysql-bin.000001
-rw-rw---- 1 mysql mysql 54 May 6 16:51 mysql-bin.index磁盘破坏恢复
一、对数据库进行备份
[root@localhost ~]# mysql -e "SHOW DATABASES;"
+--------------------+
| Database |
+--------------------+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
[root@localhost ~]# mysqldump -A --single-transaction --master-data=2 | xz > /data/all.sql.xz
二、备份完毕后数据库发生小部分变化
MariaDB [(none)]> INSERT hellodb.students(stuid,name,gender,age) VALUE(26,‘linchong‘,‘M‘,30) ;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> INSERT hellodb.students(stuid,name,gender,age) VALUE(27,‘Lujunyi‘,‘M‘,30);
Query OK, 1 row affected (0.00 sec)
三、数据库发生破坏，数据丢失
[root@localhost ~]# rm -rf /data/mysql/*
数据恢复
一、将mysqld服务停止
[root@localhost ~]# service mysqld stop
Stopping mysqld (via systemctl): [ OK ]
二、查看下二进制日志。
由于每次启动服务都会重新生成一个新的二进制日志，所以先查看下二进制日志的编号以免等下在使用二进制日志还原数据时还原了不必要的数据
[root@localhost ~]# ll /data/bin/
total 504
-rw-rw---- 1 mysql mysql 351 May 6 16:51 mysql-bin.000001
-rw-rw---- 1 mysql mysql 9388 May 6 17:01 mysql-bin.000002
-rw-rw---- 1 mysql mysql 81 May 6 17:03 mysql-bin.index
三、重启MySQL服务，初始化数据库
[root@localhost ~]# service mysqld start
Starting mysqld (via systemctl): [ OK ]
[root@localhost ~]# ll /data/mysql/
total 122924
-rw-rw---- 1 mysql mysql 16384 May 6 17:01 aria_log.00000001
-rw-rw---- 1 mysql mysql 52 May 6 17:01 aria_log_control
drwx------ 2 mysql mysql 272 May 6 16:54 hellodb
-rw-rw---- 1 mysql mysql 1298 May 6 17:01 ib_buffer_pool
-rw-rw---- 1 mysql mysql 12582912 May 6 17:03 ibdata1
-rw-rw---- 1 mysql mysql 50331648 May 6 17:03 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 Apr 29 12:49 ib_logfile1
-rw-rw---- 1 mysql mysql 12582912 May 6 17:03 ibtmp1
-rw-rw---- 1 mysql mysql 6 May 6 17:03 localhost.localdomain.pid
-rw-rw---- 1 mysql mysql 0 Apr 29 12:57 multi-master.info
drwx------ 2 mysql root 4096 Apr 29 12:49 mysql
-rw-rw---- 1 mysql mysql 351 Apr 29 14:06 mysql-bin.000001
-rw-rw---- 1 mysql mysql 351 May 6 16:50 mysql-bin.000002
-rw-rw---- 1 mysql mysql 38 May 6 16:26 mysql-bin.index
-rw-rw---- 1 mysql mysql 0 May 6 16:50 mysql-bin.state
srwxrwxrwx 1 mysql mysql 0 May 6 17:03 mysql.sock
drwx------ 2 mysql mysql 20 Apr 29 12:49 performance_schema
drwx------ 2 mysql root 6 Apr 29 12:49 test
四、将完全备份解压
[root@localhost ~]# unxz /data/all.sql.xz
五、由于完全备份后数据又发生过改变所以需要利用二进制日志进行还原在利用二进制还原前，先查看下完全备份时二进制日志所在的位置。
[root@localhost ~]# vim /data/all.sql
-- CHANGE MASTER TO MASTER_LOG_FILE=‘mysql-bin.000002‘, MASTER_LOG_POS=8946;
六、将完全备份后产生二进制日志的数据导出
[root@localhost ~]# mysqlbinlog --start-position=8946 /data/bin/mysql-bin.000002 > /data/inc.sql
七、进入MySQL,停止二进制日志记录
由于接下来的操作是恢复数据，所以此处不需要让二进制日志记录数据。
MariaDB [(none)]> SET sql_log_bin=off;
Query OK, 0 rows affected (0.00 sec)
八、导入之前做的完全备份
MariaDB [(none)]> source /data/all.sql
九、导入完全备份后产生的数据
MariaDB [(none)]> source /data/inc.sql
十、验证
查看数据库，以及完全备份后增加的内容是否存在
[root@localhost ~]# mysql -e ‘SHOW DATABASES;SELECT * FROM hellodb.students where stuid>25;‘
+--------------------+
| Database |
+--------------------+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
+-------+----------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+----------+-----+--------+---------+-----------+
| 26 | linchong | 30 | M | NULL | NULL |
| 27 | Lujunyi | 30 | M | NULL | NULL |
+-------+----------+-----+--------+---------+-----------+误删除恢复
一、对数据库进行备份
[root@localhost ~]# mysql -e "SHOW DATABASES;"
+--------------------+
| Database |
+--------------------+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
[root@localhost ~]# mysqldump -A --single-transaction --master-data=2 | xz > /data/all.sql.xz
二、备份完毕后数据库发生小部分变化
MariaDB [(none)]> INSERT hellodb.students(stuid,name,gender,age) VALUE(26,‘linchong‘,‘M‘,30) ;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> INSERT hellodb.students(stuid,name,gender,age) VALUE(27,‘Lujunyi‘,‘M‘,30);
Query OK, 1 row affected (0.00 sec)
三、数据库发生破坏，数据丢失
[root@localhost ~]# rm -rf /data/mysql/*
数据恢复
一、将mysqld服务停止
[root@localhost ~]# service mysqld stop
Stopping mysqld (via systemctl): [ OK ]
二、查看下二进制日志。
由于每次启动服务都会重新生成一个新的二进制日志，所以先查看下二进制日志的编号以免等下在使用二进制日志还原数据时还原了不必要的数据
[root@localhost ~]# ll /data/bin/
total 504
-rw-rw---- 1 mysql mysql 351 May 6 16:51 mysql-bin.000001
-rw-rw---- 1 mysql mysql 9388 May 6 17:01 mysql-bin.000002
-rw-rw---- 1 mysql mysql 81 May 6 17:03 mysql-bin.index
三、重启MySQL服务，初始化数据库
[root@localhost ~]# service mysqld start
Starting mysqld (via systemctl): [ OK ]
[root@localhost ~]# ll /data/mysql/
total 122924
-rw-rw---- 1 mysql mysql 16384 May 6 17:01 aria_log.00000001
-rw-rw---- 1 mysql mysql 52 May 6 17:01 aria_log_control
drwx------ 2 mysql mysql 272 May 6 16:54 hellodb
-rw-rw---- 1 mysql mysql 1298 May 6 17:01 ib_buffer_pool
-rw-rw---- 1 mysql mysql 12582912 May 6 17:03 ibdata1
-rw-rw---- 1 mysql mysql 50331648 May 6 17:03 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 Apr 29 12:49 ib_logfile1
-rw-rw---- 1 mysql mysql 12582912 May 6 17:03 ibtmp1
-rw-rw---- 1 mysql mysql 6 May 6 17:03 localhost.localdomain.pid
-rw-rw---- 1 mysql mysql 0 Apr 29 12:57 multi-master.info
drwx------ 2 mysql root 4096 Apr 29 12:49 mysql
-rw-rw---- 1 mysql mysql 351 Apr 29 14:06 mysql-bin.000001
-rw-rw---- 1 mysql mysql 351 May 6 16:50 mysql-bin.000002
-rw-rw---- 1 mysql mysql 38 May 6 16:26 mysql-bin.index
-rw-rw---- 1 mysql mysql 0 May 6 16:50 mysql-bin.state
srwxrwxrwx 1 mysql mysql 0 May 6 17:03 mysql.sock
drwx------ 2 mysql mysql 20 Apr 29 12:49 performance_schema
drwx------ 2 mysql root 6 Apr 29 12:49 test
四、将完全备份解压
[root@localhost ~]# unxz /data/all.sql.xz
五、由于完全备份后数据又发生过改变所以需要利用二进制日志进行还原在利用二进制还原前，先查看下完全备份时二进制日志所在的位置。
[root@localhost ~]# vim /data/all.sql
-- CHANGE MASTER TO MASTER_LOG_FILE=‘mysql-bin.000002‘, MASTER_LOG_POS=8946;
六、将完全备份后产生二进制日志的数据导出
[root@localhost ~]# mysqlbinlog --start-position=8946 /data/bin/mysql-bin.000002 > /data/inc.sql
七、进入MySQL,停止二进制日志记录
由于接下来的操作是恢复数据，所以此处不需要让二进制日志记录数据。
MariaDB [(none)]> SET sql_log_bin=off;
Query OK, 0 rows affected (0.00 sec)
八、导入之前做的完全备份
MariaDB [(none)]> source /data/all.sql
九、导入完全备份后产生的数据
MariaDB [(none)]> source /data/inc.sql
十、验证
查看数据库，以及完全备份后增加的内容是否存在
[root@localhost ~]# mysql -e ‘SHOW DATABASES;SELECT * FROM hellodb.students where stuid>25;‘
+--------------------+
| Database |
+--------------------+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
+-------+----------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+----------+-----+--------+---------+-----------+
| 26 | linchong | 30 | M | NULL | NULL |
| 27 | Lujunyi | 30 | M | NULL | NULL |
+-------+----------+-----+--------+---------+-----------+MySQL的备份还原(xtrabackup)
xtrabackup简介
xtrabackup是percona提供的MySQL数据库的备份工具，是唯一开源的能对innodb和xtradb数据库进行热备的工具
xtrabackup的特点
1.备份还原过程快、可靠
2.备份过程不会打断正在执行的事务
3.能够基于压缩等功能介于磁盘空间和流量
4.自动实现备份检验
5.开源免费
xtrabackup安装
xtrabackup在centos的EPEL源中，可以在配置完EPEL源后使用yum安装
[root@localhost ~]# yum install -y percona-xtrabackup
也可以去官网下载最新版本的xtrabackup
https://www.percona.com/downloads/XtraBackup/LATEST
完全备份及还原
在完全备份之前需要创建一个备份的目录作为存放备份使用
[root@localhost ~]# mkdir /data/backup
数据库内存放的数据
[root@localhost ~]# mysql -e ‘SHOW DATABASES;‘
+--------------------+
| Database |
+--------------------+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
一、完全备份
[root@localhost ~]# mariabackup --backup --target-dir=/data/backup --user=root --password=‘‘
二、对数据库破坏
[root@localhost ~]# rm -rf /data/mysql/*
三、恢复
3.1在恢复数据库前需要先将数据库内数据清理，然后停止mysql服务
[root@localhost ~]# rm -rf /data/mysql/*
[root@localhost ~]# service mysqld stop
Stopping mysqld (via systemctl): [ OK ]
3.2对备份目录做整理
[root@localhost ~]# mariabackup --prepare --target-dir=/data/backup
3.3将整理好的备份数据还原至数据库
[root@localhost ~]# mariabackup --copy-back --target-dir=/data/backup
3.4此时还原回去的数据属性还有问题，需要将其修改
[root@localhost ~]# ll /data/mysql/
total 12320
-rw-r----- 1 root root 16384 May 6 21:28 aria_log.00000001
-rw-r----- 1 root root 52 May 6 21:28 aria_log_control
drwx------ 2 root root 272 May 6 21:28 hellodb
-rw-r----- 1 root root 942 May 6 21:28 ib_buffer_pool
-rw-r----- 1 root root 12582912 May 6 21:28 ibdata1
drwx------ 2 root root 4096 May 6 21:28 mysql
drwx------ 2 root root 20 May 6 21:28 performance_schema
drwx------ 2 root root 20 May 6 21:28 test
-rw-r----- 1 root root 527 May 6 21:28 xtrabackup_info
[root@localhost ~]# chown -R mysql.mysql /data/mysql/
四、启动服务测试
[root@localhost ~]# service mysqld start
Starting mysqld (via systemctl): [ OK ]
[root@localhost ~]# mysql -e "SHOW DATABASES;SHOW TABLES FROM hellodb;"
+--------------------+
| Database |
+--------------------+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+完全备份+增量备份及还原
在备份之前先创建出完全备份的目录以及增量备份的目录
[root@localhost ~]# mkdir -pv /dataa/backup/{full,inc1,inc2}
mkdir: created directory ‘/data/backup’
mkdir: created directory ‘/data/backup/full’
mkdir: created directory ‘/data/backup/inc1’
mkdir: created directory ‘/data/backup/inc2’
数据库内所存放的数据
[root@localhost ~]# mysql -e ‘SHOW DATABASES;‘
+--------------------+
| Database |
+--------------------+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
一、对数据进行完全备份
[root@localhost ~]# mariabackup --backup --target-dir=/data/backup/full --user=root --password=‘‘
二、此时数据库内数据发生变化
[root@localhost ~]# mysql -e "INSERT hellodb.teachers VALUE(5,‘Li Xiaolong‘,30,‘M‘);"
[root@localhost ~]# mysql -e "SELECT * FROM hellodb.teachers WHERE tid>4;"
+-----+-------------+-----+--------+
| TID | Name | Age | Gender |
+-----+-------------+-----+--------+
| 5 | Li Xiaolong | 30 | M |
+-----+-------------+-----+--------+
三、对数据库进行增量备份
[root@localhost ~]# mariabackup --user=root --password=‘‘ --backup --incremental-basedir=/data/backup/full --target-dir=/data/backup/inc1
四、数据库内数据再次发生变化
[root@localhost ~]# mysql -e "INSERT hellodb.students(stuid,age,name) VALUE (26,35,‘Sun Wukong‘);"
[root@localhost ~]# mysql -e "SELECT * FROM hellodb.students WHERE stuid>25;"
+-------+------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+------------+-----+--------+---------+-----------+
| 26 | Sun Wukong | 35 | F | NULL | NULL |
+-------+------------+-----+--------+---------+-----------+
五、再次对数据库做增量备份
[root@localhost ~]# mariabackup --user=root --password=‘‘ --backup --incremental-basedir=/data/backup/inc1 --target-dir=/data/backup/inc2
六、破坏数据库
[root@localhost ~]# rm -rf /data/mysql/*
七、恢复数据库
7.1先对数据库做清理，并停止数据库服务
[root@localhost ~]# rm -rf /data/mysql/*
[root@localhost ~]# service mysqld stop
Stopping mysqld (via systemctl): [ OK ]
7.2对完全备份做预整理
因为后续还有增量备份，所以此处需要使用apply-log-only选项来阻止事务的回滚。
[root@localhost ~]# mariabackup --prepare --apply-log-only --target-dir=/data/backup/full
7.3合并第一次的增量备份
此为第一个增量备份后续还有第二个增量所以需要使用apply-log-only阻止回滚事务
[root@localhost backup]# mariabackup --prepare --apply-log-only --target-dir=/data/backup/full --incremental-dir=/data/backup/inc1
7.3合并第二次的增量备份
此为最后个增量备份无需再使用apply-log-only阻止回滚事务
[root@localhost backup]# mariabackup --prepare --target-dir=/data/backup/full --incremental-dir=/data/backup/inc2
7.4整理完毕将备份复制回数据库
[root@localhost backup]# mariabackup --copy-back --target-dir=/data/backup/full
7.5将数据库内的属主和属组进行更改
[root@localhost backup]# chown -R mysql.mysql /data/mysql/
八、重启服务测试
[root@localhost backup]# service mysqld start
Starting mysqld (via systemctl): [ OK ]
[root@localhost backup]# msyql
MariaDB [hellodb]> select *from students where stuid>25;
+-------+------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+------------+-----+--------+---------+-----------+
| 26 | Sun Wukong | 35 | F | NULL | NULL |
+-------+------------+-----+--------+---------+-----------+
1 row in set (0.00 sec)
MariaDB [hellodb]> select *from teachers where tid>4;
+-----+-------------+-----+--------+
| TID | Name | Age | Gender |
+-----+-------------+-----+--------+
| 5 | Li Xiaolong | 30 | M |
+-----+-------------+-----+--------+
1 row in set (0.00 sec)其他的注意事项
1.在数据恢复之前必须先要停止MySQL服务
2.在还原时MySQL的数据库目录必须为空，否则不会覆盖
3.数据库内容还原后，其属主和属组为root需要手动将其全部改为mysql
mysql的主从复制
所有的关系型数据库都存在一个通病性能差，在企业中如果用户量特别打，将所有的数据都存放在一台服务器上，其性能时远远达不到要求的。所以需要使用一些手段来解决其性能的问题。
提升性能的方式有向上扩展以及向外扩展
向上扩展(Scale Up):使用更新更好的硬件，但硬件在怎么更新也有其性能的极限。盲目的向上扩展无法结局根本的问题
向外扩展(Scale Out):就是使用多台机器分摊压力来提供服务
主从复制就是拿多个数据库服务器，组合成一个服务器的集合对外共同服务实现性能的提升，逻辑上使用的时对外扩展的方式(Scale out)来提升服务器的性能。
新主机搭建主从复制搭建
服务器类型 ip地址
主 192.168.73.133
从 192.168.73.145
主服务器操作
1.在主服务器上启用二进制日志
[root@localhost ~]# vim /etc/mysql/my.cnf
log-bin=/data/bin/mysql-bin
binlog-format=row
server-id=1
2.创建二进制日志目录
[root@localhost ~]# mkdir /data/bin
[root@localhost ~]# chown -R mysql.mysql /data/bin
3.重启服务
[root@localhost ~]# service mysqld restart
Restarting mysqld (via systemctl): [ OK ]
4.创建一个用来让从服务器复制数据的账号
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO repluser@‘192.168.73.%‘ IDENTIFIED BY ‘centos‘;
Query OK, 0 rows affected (0.00 sec)
5.查看主服务器正在使用的二进制日志
MariaDB [(none)]> SHOW MASTER LOGS;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 515 | #当前二进制文件及位置需要记录，从服务器设置时需要
+------------------+-----------+
1 row in set (0.00 sec)
从服务器操作
1.修改配置文件
server-id = 2 #server-id改为和主服务器不同
read-only #设置为只读
#log-bin=mysql-bin #将二进制日志关闭
2.启动MySQL服务器
[root@localhost ~]# service mysqld restart
Starting mysqld (via systemctl): [ OK ]
3.关联主服务器
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST=‘192.168.73.133‘,MASTER_USER=‘repluser‘,MASTER_PASSWORD=‘centos‘,MASTER_PORT=3306,MASTER_LOG_FILE=‘mysql-bin.000001‘,MASTER_LOG_POS=515;
Query OK, 0 rows affected (0.01 sec)
4.查看从服务器状态
MariaDB [(none)]> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.73.133
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 515
Relay_Log_File: localhost-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 515
Relay_Log_Space: 256
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
1 row in set (0.00 sec)
ERROR: No query specified
5.启动线程
MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.00 sec)
测试
在主服务器上导入一个hellodb数据库
[root@localhost ~]# mysql < hellodb_innodb.sql
从服务器上查看是否同步成功
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| hellodb | #已经有hellodb库
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
主服务器已有数据的情况下搭建主从
拥有一台已经有数据的mysql服务器，追加一台从服务器
服务器 IP地址
Master 192.168.73.148
Slave 192.168.73.149
Master中的数据
[root@localhost ~]# mysql -e "SHOW DATABASES;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mysql |
| performance_schema |
| test |
| test1 |
+--------------------+
配置主服务器
1.修改主服务器配置文件
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
log-bin=/data/bin/mysql-bin
binlog-format=row
[root@localhost ~]# systemctl restart mariadb
2.创建复制账号
[root@localhost ~]# mysql -e "GRANT REPLICATION SLAVE ON *.* TO ‘repluser‘@‘192.168.73.%‘ IDENTIFIED BY ‘centos‘;"
3.备份主节点中的所有数据
[root@localhost ~]# mysqldump -A --single-transaction -F --master-data=1 > /data/all.sql
[root@localhost ~]# vim /data/all.sql
4.将备份数据传送给从节点
[root@localhost ~]# scp /data/all.sql 192.168.73.149:/data
配置从服务器
1.修改配置文件
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
server-id=2
read-only
2.清空MySQL数据库
[root@localhost ~]# rm -rf /var/lib/mysql/*
3.修改备份数据将chang master to 加入文件中
CHANGE MASTER TO
MASTER_HOST=‘192.168.73.148‘,
MASTER_USER=‘repluser‘,
MASTER_PASSWORD=‘centos‘,
MASTER_PORT=3306,
MASTER_LOG_FILE=‘mysql-bin.000005‘,
MASTER_LOG_POS=245;
4.启动MySQL服务，并导入备份
[root@localhost ~]# mysql < /data/all.sql
5.查看从节点状态
MariaDB [(none)]> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.73.148
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 245
Relay_Log_File: mariadb-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 245
Relay_Log_Space: 245
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
1 row in set (0.00 sec)
6.启动线程
MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.00 sec)
7.再次查看从节点状态，复制的2个线程已经启动
MariaDB [(none)]> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.73.148
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 245
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 529
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 245
Relay_Log_Space: 825
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)
测试
主节点删test1库
MariaDB [(none)]> DROP DATABASE test1;
Query OK, 1 row affected (0.01 sec)
从节点查看是否同步
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec) #已经没有test1库MySQL主从复制出错的解决方法
主从复制中若是出现错误可以通过几个方法来进行解决
1.如果主从复制时发生了主键冲突，从而阻止了主从复制，可以使用sql_slave_skip_counter这个变量来忽略错误将其排除
2.如果发生了较大的错误，可以考虑使用reset slave的方法重新配置从服务器来恢复错误
以下演示如何使用这两种方法解决错误，及相关操作的详细说明
reset slave的使用方法
环境准备搭建主从同步
主节点配置
1.修改配置文件
[root@Master ~]# vim /etc/my.cnf
[mysqld]
log-bin=/data/bin/mysql-bin
binlog-format=row
server-id=1
2.创建二进制日志目录
[root@Master ~]# mkdir /data/bin
[root@Master ~]# chown -R mysql.mysql /data/bin
3.启动mysqld服务
[root@Master ~]# systemctl start mariadb
4.查看主服务器日志位置
[root@Master ~]# mysql -e "SHOW MASTER LOGS;"
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 26753 |
| mysql-bin.000002 | 921736 |
| mysql-bin.000003 | 245 |
+------------------+-----------+
5.创建一个用来复制数据的账户
[root@Master ~]# mysql -e "GRANT REPLICATION SLAVE ON *.* TO ‘repluser‘@‘192.168.73.%‘ IDENTIFIED BY ‘centos‘;"
从节点配置
1.修改配置文件
[root@Slave ~]# vim /etc/my.cnf
[mysqld]
read-only
server-id=2
2.启动服务
[root@Slave ~]# systemctl start mariadb
此处开始构建错误配置
以下所有CHANGE MASTER TO配置均为错误
3.配置CHANGE MASTER TO
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST=‘master2.mycompany.com‘,
-> MASTER_USER=‘replication‘,
-> MASTER_PASSWORD=‘bigs3cret‘,
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE=‘master2-bin.001‘,
-> MASTER_LOG_POS=4,
-> MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected (0.00 sec)
4.查看下SLAVE STATUS
MariaDB [(none)]> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: master2.mycompany.com
Master_User: replication
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: master2-bin.001
Read_Master_Log_Pos: 4
Relay_Log_File: mariadb-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: master2-bin.001
Slave_IO_Running: No
Slave_SQL_Running: No
...以下省略...
5.启动复制线程
MariaDB [(none)]> START SLAVE;
6.再次查看SLAVE STATUS
MariaDB [(none)]> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: master2.mycompany.com
Master_User: replication
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: master2-bin.001
Read_Master_Log_Pos: 4
Relay_Log_File: mariadb-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: master2-bin.001
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
...以下省略...
线程已经正常启动
主服务器导入数据进行测试
[root@Master ~]# mysql < hellodb_innodb.sql
[root@Master ~]# mysql -e "SHOW DATABASES;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mysql |
| performance_schema |
| test |
+--------------------+
从服务器查看是否同步(CHANGE MASTER TO信息不对怎么可能同步)
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
以下为错误解决方法
由于错误发生在CHANGE MASTER TO所以此处将CHANG MASTER TO部分纠正就行
1.首先将从服务器的复制线程停止
MariaDB [(none)]> STOP SLAVE;
Query OK, 0 rows affected (17.48 sec)
2.将从服务器上的SLAVE信息重置
MariaDB [(none)]> RESET SLAVE;
Query OK, 0 rows affected (0.01 sec)
3.重新输入正确的CHANGE MASTER TO信息
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST=‘192.168.73.110‘,MASTER_USER=‘repluser‘,MASTER_PASSWORD=‘centos‘,MASTER_PORT=3306,MASTER_LOG_FILE=‘mysql-bin.000003‘,MASTER_LOG_POS=245;
Query OK, 0 rows affected (0.01 sec)
4.查看SLAVE STATUS;
MariaDB [(none)]> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.73.110
Master_User: repluser
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 245
Relay_Log_File: mariadb-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
#此处信息已经改为正确
5.重新启动线程
MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.00 sec)
6.再次查看SLAVE STATUS;
MariaDB [(none)]> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.73.110
Master_User: repluser
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 7384 #已经有数据复制过来了
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 7668
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
#IO和SQL线程已经启动
7.查看下从节点内的库是否已经同步
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb | #hellodb库已经从主节点中复制过来了
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.01 sec)
其他说明：
如果生产中，发生主从节点之间的数据偏差较大并且迟迟不能同步，可以考虑将从服务器全部清除从新配置从服务器。具体配置方法可以参考https://blog.51cto.com/11886307/2390636
关于sql_slave_skip_counter的使用方法
当发生主键冲突时，从服务器会卡在出错的位置不再进行服务，此种错误一般会出现在主主复制或者从服务器已经占用了某条记录的情况下，此时可以使用此选项来忽略错误。
构建错误
此处继续沿用刚才的主从复制环境
1.在从服务器上创建一条记录
MariaDB [(none)]> INSERT hellodb.teachers VALUE (5,‘Li Xiaolong‘,30,‘M‘);
Query OK, 1 row affected (0.00 sec)
2.在主服务器上也创建一条主键相同的记录
MariaDB [(none)]> INSERT hellodb.teachers VALUE (5,‘Xiao Yan‘,20,‘M‘);
Query OK, 1 row affected (0.00 sec)
3.返回从节点查看SLAVE STATUS
MariaDB [(none)]> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.73.110
Master_User: repluser
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 7576
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 7668
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1062
Last_Error: Could not execute Write_rows event on table hellodb.teachers; Duplicate entry ‘5‘ for key ‘PRIMARY‘, Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event‘s master log mysql-bin.000003, end_log_pos 7549
Skip_Counter: 0
Exec_Master_Log_Pos: 7384
Relay_Log_Space: 8156
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1062
Last_SQL_Error: Could not execute Write_rows event on table hellodb.teachers; Duplicate entry ‘5‘ for key ‘PRIMARY‘, Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event‘s master log mysql-bin.000003, end_log_pos 7549
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)
4.从节点已经出错，在主节点继续添加记录
MariaDB [(none)]> INSERT hellodb.teachers VALUE (6,‘Xiao Xuner‘,20,‘M‘);
Query OK, 1 row affected (0.00 sec)
5.此时从节点已经不会再继续从主节点复制信息
MariaDB [(none)]> SELECT * FROM hellodb.teachers WHERE tid>4;
+-----+-------------+-----+--------+
| TID | Name | Age |Gender |
+-----+-------------+-----+--------+
| 5 | Li Xiaolong | 30 | M | #此为刚才从节点添加的记录
+-----+-------------+-----+--------+
1 row in set (0.00 sec)
排错
1.使用sql_slave_skip_counter变量忽略错误
MariaDB [(none)]> SET GLOBAL sql_slave_skip_counter=1;
Query OK, 0 rows affected (0.00 sec)
2.停止线程并重新启动
MariaDB [(none)]> STOP SLAVE;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.00 sec)
3.查看slave status状态，此时已经没有报错的信息
MariaDB [(none)]> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.73.110
Master_User: repluser
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 7770
Relay_Log_File: mariadb-relay-bin.000003
Relay_Log_Pos: 529
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 7770
Relay_Log_Space: 8634
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)
4.在从服务器上查看teachers表
MariaDB [(none)]> SELECT * FROM hellodb.teachers WHERE tid>4;
+-----+-------------+-----+--------+
| TID | Name | Age | Gender |
+-----+-------------+-----+--------+
| 5 | Li Xiaolong | 30 | M |
| 6 | Xiao Xuner | 20 | M | #此时刚才在主节点插入的6号记录已经复制过来
+-----+-------------+-----+--------+
2 rows in set (0.00 sec)
以上为主从复制时出错的一些相关的修复方法MySQL级联复制
在生产换进中有一种主从复制的方法主节点先将数据同步到一个中间的从节点，然后由从节点给后续的其他从节点来复制数据，这种复制方式称为级联复制。
级联复制的好处是可以极大的减轻主节点的压力
级联复制在配置时需要在中间节点上启用log_slave_updates的选项。
级联复制的配置方法
准备主机4台，1台主节点(Master)，1台中间从节点(Slave)，1台从节点(Slave1)
主机 系统 ip
Master CentOS7 192.168.73.110
Slave CentOS7 192.168.73.111
Slave1 CentOS7 192.168.73.112主节点配置(Master)
1.修改配置文件
[mysqld]
log-bin=/data/bin/mysql-bin #启动二进制日志
binlog-format=row #修改日志格式
server-id=1
2.创建二级制日志目录
[root@Master ~]# mkdir /data/bin
[root@Master ~]# chown -R mysql.mysql /data/bin
3.启动服务，查看当前二进制日志所在的位置
[root@Master ~]# systemctl start mariadb
[root@Master ~]# mysql -e "SHOW MASTER LOGS;"
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 26753 |
| mysql-bin.000002 | 921736 |
| mysql-bin.000003 | 245 | #记录当前二进制的位置
+------------------+-----------+
4.创建一个用来复制数据的账户
[root@Master ~]# mysql -e "GRANT REPLICATION SLAVE ON *.* TO ‘repluser‘@‘192.168.73.%‘ IDENTIFIED BY ‘centos‘;"中间从节点配置
1.修改配置文件
[root@Slave ~]# vim /etc/my.cnf
[mysqld]
log-bin=/data/bin/mysql-bin
binlog-format=row
read-only
log_slave_updates
server-id=2
2.创建二进制日志目录
[root@Slave ~]# mkdir /data/bin
[root@Slave ~]# chown -R mysql.mysql /data/bin
3.启动MySQL服务
[root@Slave ~]# systemctl start mariadb
4.写入CHANGE MASTER TO信息
CHANGE MASTER TO
MASTER_HOST=‘192.168.73.110‘,
MASTER_USER=‘repluser‘,
MASTER_PASSWORD=‘centos‘,
MASTER_PORT=3306,
MASTER_LOG_FILE=‘mysql-bin.000003‘,
MASTER_LOG_POS=245;
Query OK, 0 rows affected (0.01 sec)
5.查看下从节点的配置状态
MariaDB [(none)]> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.73.110
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 245
Relay_Log_File: mariadb-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: No #线程尚未开启
Slave_SQL_Running: No #线程尚未开启
6.启动线程
MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.00 sec)
7.再次查看从节点状态
MariaDB [(none)]> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.73.110
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 402 #已经有小部分数据被复制过来
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 686
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes #线程已经启动
Slave_SQL_Running: Yes #线程已经启动
8.测试
8.1在主节点导入数据库
[root@Master ~]# mysql < hellodb_innodb.sql
[root@Master ~]# mysql -e "SHOW DATABASES;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mysql |
| performance_schema |
| test |
+--------------------+
8.2从节点查看是否已经同步
[root@Slave ~]# mysql -e "SHOW DATABASES;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mysql |
| performance_schema |
| test |
+--------------------+配置Slave1
由于此时各节点上已经有数据，作为后来追加的从服务器，首先需要将之前的所有数据使用备份恢复一次然后再进行主从复制进行同步
1.在中间节点上将数据库备份出来，并将数据传送到后续的Slave1主机上
[root@Slave ~]# mysqldump -A --single-transaction -F --master-data=1 > /data/all.sql
[root@Slave ~]# scp /data/all.sql 192.168.73.112:/data
2.在Slave1主机上修改配置文件
[root@Slave1 ~]# vim /etc/my.cnf
[mysqld]
read-only
server-id=3
3.启动MySQL服务
[root@Slave1 ~]# systemctl start mariadb
4.对备份文件做修改
找到CHANGE MASTER TO行对信息加以修改
[root@Slave1 ~]# vim /data/all.sql
CHANGE MASTER TO MASTER_HOST=‘192.168.73.111‘,MASTER_USER=‘repluser‘,MASTER_PASSWORD=‘centos‘,MASTER_PORT=3306,MASTER_LOG_FILE=‘mysql-bin.000004‘, MASTER_LOG_POS=245;
5.将修改后的备份文件导入数据库
[root@Slave1 ~]# mysql < /data/all.sql
6.查看下slave status
[root@Slave1 ~]# mysql -e "SHOW SLAVE STATUS\G;"
1. row
Slave_IO_State:
Master_Host: 192.168.73.111
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 245
Relay_Log_File: mariadb-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: No
Slave_SQL_Running: No
7.启动线程
[root@Slave1 ~]# mysql -e "START SLAVE;"
8.再次查看slave status
[root@Slave1 ~]# mysql -e "show slave status\G;"
1. row
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.73.111
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 245
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 529
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes #线程已经全部启动
Slave_SQL_Running: Yes
测试
在主节点上删除hellodb库中的teachers表
[root@Master ~]# mysql -e "SHOW TABLES FROM hellodb;" #先查看下库是否有teachers表
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
[root@Master ~]# mysql -e "DROP TABLE hellodb.teachers;" #删表
[root@Master ~]# mysql -e "SHOW TABLES FROM hellodb;" #再次确认表是否删除
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| toc |
+-------------------+
在slave1节点上查看是否同步
[root@Slave1 ~]# mysql -e "SHOW TABLES FROM hellodb;" #查看从节点是否还存在teachers表
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| toc |
+-------------------+
级联复制配置成功
MySQL半同步复制
MySQL的复制方法有异步复制，同步复制，半同步复制。
异步复制
异步复制是当用户写入一条记录时，先将数据写入到主节点，然后回复用户一个写入成功的消息，然后慢慢的将数据复制到其背后的其他从节点，这样的好处是效率比较高，但是缺点也是非常明显，主服务器和从服务器的延迟过大并且主服务器突然发生异常，此时就会造成数据的丢失。
同步复制
同步复制是当用户写入一条记录时，主节点将数据写入数据库，然后将数据复制给其后面的其他从节点，当所有的从节点返回数据复制成功后，主节点再回复用户数据接入成功的消息，这样做的好处是，确保了数据的安全性，但损失了效率。
半同步复制
半同步复制是间于同步复制和异步复制之间的一种复制方法，他的工作原理是：当用户执行写操作时，主节点会将数据发送给其后面的其他从节点，只要有一个从节点返回复制成功的消息，主节点就直接返回写入成功，如果主节点背后的从节点迟迟不返回复制成功消息，此时就会有一个超时时长，一旦达到超时时长，主节点就先返回消息告诉用户复制成功，而后将数据继续给从节点复制。
同步复制的配置方法
半同步复制要实现方法
主服务器上安装semi_sync_master.so的插件，并启用，设置好超时的时长
从服务器上安装semi_sync_slave.so的插件，并启用
以下以两台主机来演示半同步复制的配置方法
主机 ip
Master 192.168.73.110
Slave 192.168.73.111一、配置主从
Master配置
1.修改MySQL配置文件
[root@Master ~]# vim /etc/my.cnf
[mysqld]
log-bin
binlog-format=row
server-id=1
2.启动服务
[root@Master ~]# systemctl start mariadb
3.查看二进制日志位置
[root@Master ~]# mysql -e "SHOW MASTER LOGS;"
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | 245 |
+--------------------+-----------+
4.创建一个用户用来做主从复制
[root@Master ~]# mysql -e "GRANT REPLICATION SLAVE ON *.* TO ‘repluser‘@‘192.168.73.%‘ IDENTIFIED BY ‘centos‘;"Slave配置
1.修改MySQL配置文件
[root@Slave ~]# vim /etc/my.cnf
[mysqld]
log-bin
binlog-format=row
server-id=2
read_only
2.启动服务
[root@Slave ~]# systemctl start mariadb
3.写入CHANGE MASTER TO
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST=‘192.168.73.110‘, MASTER_USER=‘repluser‘,MASTER_PASSWORD=‘centos‘,MASTER_PORT=3306,MASTER_LOG_FILE=‘mariadb-bin.000001‘,MASTER_LOG_POS=245;
Query OK, 0 rows affected (0.01 sec)
4.启动复制线程
MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.00 sec)
5.查看slave状态
MariaDB [(none)]> SHOW SLAVE STATUS\G;
1. row
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.73.110
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000001
Read_Master_Log_Pos: 521
Relay_Log_File: mariadb-relay-bin.000003
Relay_Log_Pos: 531
Relay_Master_Log_File: mariadb-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
测试
1.Master导入数据库
[root@Master ~]# mysql < hellodb_innodb.sql
[root@Master ~]# mysql -e "SHOW DATABASES;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mysql |
| performance_schema |
| test |
+--------------------+
2.Slave查看库
[root@Slave ~]# mysql -e "SHOW DATABASES;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mysql |
| performance_schema |
| test |
+--------------------+
主从同步配置成功，接下来配置半同步
配置半同步前先将主从复制关闭
[root@Slave ~]# mysql -e "STOP SLAVE;"二、配置半同步
Master节点配置
1.在Master节点上安装semisync_master.so的插件
MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_master SONAME ‘semisync_master.so‘;
Query OK, 0 rows affected (0.01 sec)
2.查看插件是否已经安装
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE ‘%semi%‘;
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled | OFF | #插件是否启用
| rpl_semi_sync_master_timeout | 10000 | #半同步的超时时长
| rpl_semi_sync_master_trace_level | 32 | #用于开启半同步复制模式时的调试级别
| rpl_semi_sync_master_wait_no_slave | ON | #是否允许master 每个事物提交后都要等待slave的receipt信号
+------------------------------------+-------+
4 rows in set (0.00 sec)
3.修改配置文件启用插件
[root@Master ~]# vim /etc/my.cnf
[mysqld]
log-bin
binlog-format=row
server-id=1
rpl_semi_sync_master_enabled #启用插件
4.重启服务，查看插件是否启动
[root@Master ~]# systemctl restart mariadb
[root@Master ~]# mysql -e "SHOW VARIABLES LIKE ‘%semi%‘";
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled | ON | #已经启动
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
+------------------------------------+-------+
5.设置超时时长
MariaDB [(none)]> SET GLOBAL rpl_semi_sync_master_timeout=3000; #此处为了方便后续测试将超时时间设置为3秒
Query OK, 0 rows affected (0.00 sec)Slave节点配置
1.在Slave节点上安装semisync_slave.so插件
MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME ‘semisync_slave.so‘;
Query OK, 0 rows affected (0.00 sec)
2.查看插件是否已经安装
MariaDB [(none)]> SHOW VARIABLES LIKE "%semi%";
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
2 rows in set (0.00 sec)
3.修改配置文件启用插件
[root@Slave ~]# vim /etc/my.cnf
[mysqld]
log-bin
binlog-format=row
server-id=2
read_only
rpl_semi_sync_slave_enabled
4.重启服务，查看插件是否启动
[root@Slave ~]# mysql -e "SHOW VARIABLES LIKE ‘%semi%‘";
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
5.启动复制线程
[root@Slave ~]# mysql -e "START SLAVE";检查
1.查看主节点的状态，由于没有复制过数据所有数据都为空
MariaDB [(none)]> SHOW GLOBAL STATUS LIKE ‘%semi%‘;
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 0 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 0 |
| Rpl_semi_sync_master_tx_wait_time | 0 |
| Rpl_semi_sync_master_tx_waits | 0 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 0 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
2.查看从节点的状态
MariaDB [(none)]> SHOW GLOBAL STATUS LIKE ‘%semi%‘;
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+
1 row in set (0.00 sec)
测试
1.将从节点网路掐断
[root@Slave ~]# ifdown ens33
2.在主节点添加一条记录
MariaDB [(none)]> INSERT hellodb.teachers VALUE (5,‘Tang San‘,20,‘M‘);
Query OK, 1 row affected (3.00 sec)
由于无法复制到从服务器，3秒后超时回复用户写入成功MySQL的高可用(MHA)
MHA简介
MHA:Master High Availability,对主节点进行监控，可实现自动故障转移至其他从节点；通过提升某一从节点为新的主节点，基于主从复制实现，还需要客户端配合实现，目前MHA主要支持一主二从，即一台充当master，一台充当备用master，另外一台充当从数据库，出于机器成本的考虑，淘宝进行了改造，目前淘宝TMHA已经一主一从。
MHA是由一台manager服务器远程监控主服务器,当主服务器挂了提升一台从服务器作为主服务器。
当主节点挂了，manager首先要查看哪台从节点，同步的数据最多，然后提升同步最多的从节点为主节点，再将其余的MySQL服务器对他做从节点。
如果原主节点没彻底死透，manager会让新的主机通过ssh协议远程连接到原先的主节点，拉取二进制日志进行同步。如果主节死透了那就放弃。
MHA搭建
环境准备
一、准备4台主机，管理节点1台，主节点MySQL服务器1台，从节点MySQL服务器2台
主机 IP
Manager 192.168.73.111
Master 192.168.73.110
Slave1 192.168.73.112
Slave2 192.168.73.113二、将Manager管理节点配置为时间服务器，向所有MySQL服务器提供时间同步。
1.安装chrony服务
[root@Manager ~]# yum install -y chrony
2.修改chrony配置文件
[root@Manager ~]# vim /etc/chrony.conf
server 172.22.0.1 iburst
allow 192.168.0.0/16
local stratum 10
3.启动chrony服务
[root@Manager ~]# systemctl start chronyd
4.将MySQL服务器与Manager服务器进行时间同步
4.1在所有MySQL主机上修改配置文件并启动，并启动服务
[root@Master ~]# sed -i ‘/^server 0/i server 192.168.73.111 iburst‘ /etc/chrony.conf
[root@Master ~]# systemctl start chronyd
4.2确认时间同步
[root@Master ~]# chronyc sources -v
210 Number of sources = 1
.-- Source mode ‘^‘ = server, ‘=‘ = peer, ‘#‘ = local clock.
/ .- Source state ‘*‘ = current synced, ‘+‘ = combined , ‘-‘ = not combined,
| / ‘?‘ = unreachable, ‘x‘ = time may be in error, ‘~‘ = time too variable.
|| .- xxxx [ yyyy ] +/- zzzz
|| Reachability register (octal) -. | xxxx = adjusted offset,
|| Log2(Polling interval) --. | | yyyy = measured offset,
|| \ | | zzzz = estimated error.
|| | | MS Name/IP address Stratum Poll Reach LastRx Last sample
===============================================================================
^* 192.168.73.111 4 6 377 54 +25us[ +41us] +/- 105ms三、配置ssh为的密钥认证登陆
当主节点宕机，manager会让从节点通过ssh协议去尝试连接主节点，并拉取二进制日志，所以要时用密钥的认证方式让从节点登陆到主节点拉取数据。
1.在manager服务器上生成私钥文件
[root@Manager ~]# ssh-keygen
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:yAvC2PJUlRyAf1udlrVXzmIsUljTdUdW6X6FVpQ3Ajo root@Manager
The key‘s randomart image is:
+---[RSA 2048]----+
| ..ooo ++. +%|
| . .o o oo.=*|
| .. E = oo*o|
| + ...... B o B.+|
|o = ..ooS. . =...|
| + . ... ..|
| . . .|
| |
| |
+----[SHA256]-----+
2.将公钥文件复制给自己
[root@Manager ~]# ssh-copy-id 127.0.0.1
3.将整个~/.ssh目录复制给所有的MySQL主机
[root@Manager ~]# scp -r ~/.ssh 192.168.73.110:/root至此所有环境准备完毕
一、配置主从复制
主节点配置
1.修改配置文件
[root@Master ~]# vim /etc/my.cnf
[mysqld]
server-id=1
log-bin
binlog-format=row
skip_name_resolve
2.启动数据库服务
[root@Master ~]# systemctl start mariadb
3.创建主从复制账号
[root@Master ~]# mysql -e "GRANT REPLICATION SLAVE ON *.* TO ‘repluser‘@‘192.168.73.%‘ IDENTIFIED BY ‘centos‘;"
4.添加mha的管理账号，让管理节点远程连接到主机用来设置主从调整
[root@Master ~]# mysql -e "GRANT ALL ON *.* TO ‘mhauser‘@‘192.168.73.%‘ IDENTIFIED BY ‘centos‘;"从节点配置
1.修改配置文件
[root@Slave1 ~]# vim /etc/my.cnf
[mysqld]
server-id=2
read-only
log-bin
relay_log_purge=0
skip_name_resolve
2.启动服务
[root@Slave1 ~]# systemctl start mariadb
3.配置CHANGE MASTER TO
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST=‘192.168.73.110‘, MASTER_USER=‘repluser‘,MASTER_PASSWORD=‘centos‘,MASTER_PORT=3306,MASTER_LOG_FILE=‘mariadb-bin.000001‘,MASTER_LOG_POS=245;
Query OK, 0 rows affected (0.00 sec)
4.启动线程
MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.00 sec)
在Slave2节点上也执行相同的操作，此处步骤省略，需要注意server-id需要修改为和其他主从节点不同
5.测试
主节点导入hellodb库
[root@Master ~]# mysql < hellodb_innodb.sql
从节点查看是否同步
slave1
[root@Slave1 ~]# mysql -e "SHOW DATABASES;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mysql |
| performance_schema |
| test |
+--------------------+Slave2
[root@Slave2 ~]# mysql -e "SHOW DATABASES;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mysql |
| performance_schema |
| test |
+--------------------+二、配置管理节点及被管理节点
1.在管理节上安装mha4mysql-manager、mha4mysql-node，将两个包放在同一目录下
[root@Manager ~]# yum install *.rpm -y #这两个包有依赖管理需要一起安装
2.在所有被管理节点上安装mha4mysql-node
[root@Master ~]# yum install mha4mysql-node-0.56-0.el6.noarch.rpm -y
[root@Slave1 ~]# yum install mha4mysql-node-0.56-0.el6.noarch.rpm -y
[root@Slave2 ~]# yum install mha4mysql-node-0.56-0.el6.noarch.rpm -y
3.在管理节点上创建配置文件
[root@Manager ~]# vim /etc/mha/aap1.conf
[server default]
user=mhauser
password=centos
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=centos
ping_interval=1
[server1]
hostname=192.168.73.110
candidate_master=1
[server2]
hostname=192.168.73.112
candidate_master=1
[server3]
hostname=192.168.73.113
candidate_master=1
4.做检查
4.1检查ssh连接
[root@Manager ~]# masterha_check_ssh --conf=/etc/mha/aap1.conf
4.2检查主从复制
[root@Manager ~]# masterha_check_repl --conf=/etc/mha/aap1.conf
5.以上两项全部成功后启动程序
mha这个程序是跑在前台的，一次性的可以使用nohub或screen来解决跑在前台的问题
[root@Manager ~]# masterha_manager --conf=/etc/mha/aap1.conf三、测试
1.在master上跑个存储过程，导入存储过程
[root@Master ~]# mysql hellodb < testlog.sql
2.调用存储过程
MariaDB [(none)]> USE hellodb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [hellodb]> call pro_testlog;
3.另起一个主节点窗口将主节点断网
[root@Master ~]# ifdown ens33
4.manager端完成切换退出，查看日志，查看新的主节点是哪台slave
[root@Manager app1]# tail /data/mastermha/app1/manager.log
Started automated(non-interactive) failover.
The latest slave 192.168.73.112(192.168.73.112:3306) has all relay logs for recovery.
Selected 192.168.73.112(192.168.73.112:3306) as a new master.
192.168.73.112(192.168.73.112:3306): OK: Applying all logs succeeded.
192.168.73.113(192.168.73.113:3306): This host has the latest relay log events.
Generating relay diff files from the latest slave succeeded.
192.168.73.113(192.168.73.113:3306): OK: Applying all logs succeeded. Slave started, replicating from 192.168.73.112(192.168.73.112:3306)
192.168.73.112(192.168.73.112:3306): Resetting slave info succeeded.
Master failover to 192.168.73.112(192.168.73.112:3306) completed successfully.
#此处显示最新的主节点为192.168.73.112
由于从节点在配置文件中定义的为read-only，此时被提升为主能执行写操作时应为管理服务器上有管理账号，他将从节点的服务器全局变量read_only给关闭了
[root@Slave1 ~]# mysql -e "SELECT @@read_only;"
+-------------+
| @@read_only |
+-------------+
| 0 |
+-------------+
为了防止服务服务重启再次变为read-only，此时需要对新主节点的配置文件进行修改将read-only行注释
[mysqld]
server-id=2
#read-only
log-bin
relay_log_purge=0
skip_name_resolve四、测试新的主节点
1.对hellodb.teachers表插入数据
[root@Slave1 ~]# mysql -e "INSERT hellodb.teachers VALUES(5,‘Tang San‘,30,‘M‘);"
2.Slave2主机上查看是否同步
[root@Slave2 ~]# mysql -e "SELECT * FROM hellodb.teachers;"
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | Tang San | 30 | M | #已经同步
+-----+---------------+-----+--------+其他事项
当原主节点被修复后，将其添加为从节点使用。
MySQL高可用(Galera Cluster)
Galera Cluster简介
Galera Cluster是集成了Galera插件的MySQL集群，是一种新型的，数据不共享的，高度冗余的高可用方案，目前Galera Cluster有两个版本，分别是Percona Xtradb Cluster和MariaDB Cluster，Galera本时是具有多主特性，即采用Multi-master的集群架构，是一个即稳健，又在数据一致性、完整性及高性能方面有出色表现的高可用解决方案
Galera Cluster特点
1.多主架构：真正的多点读写的集群，在任何时候读写数据都是最新的
2.同步复制：集群不同节点之间数据同步，没有延迟，在数据库挂掉之后，数据不会丢失
3.并发复制：从节点APPLY数据时，支持并行执行有更好的性能。
4.故障切换：数据库故障时，因为支持多点写入，切换容易
5.热插拔：在服务期间，如果数据库挂了，只要监控程序发现的够快，不可服务的时间就会非常少。在节点故障期间，节点本身对集群的影响非常小
6.自动节点克隆： 在新增节点，或者停机维护时，增量数据或者基础数据不需要人工手动备份提供，Galera Cluster会自动拉取在线节点数据，最终集群会变为一直
7.对应用透明：集群的维护，对应用程序是透明的
Galera Cluster搭建
环境准备
准备3台msyql服务器
主机名 ip地址
node1 192.168.73.110
node2 192.168.73.111
node3 192.168.73.11
一、配置YUM源
在每个节点上配置YUM源
[root@node1 ~]# vim /etc/yum.repos.d/mysql.repo
[mysql]
name=galera cluster
baseurl=http://mirrors.neusoft.edu.cn/mariadb//mariadb-10.0.38/yum/centos7-amd64/
gpgcheck=0
二、安装MariaDB-Galera-server
在每个节点上安装MariaDB-Galera-server
[root@node1 ~]# yum install MariaDB-Galera-server -y
三、修改配置文件
配置文件在/etc/my.cnf.d/server.cnf，有必须设置和可选设置两个配置
# Mandatory settings #这里的项目为必填
wsrep_provider= #添加模块路径
wsrep_cluster_address= #添加所有服务器的地址gcomm服务器间代替通讯协议
binlog_format=row #启用二进制日志
# Optional setting #以下项为可选项
#wsrep_slave_threads=1
#innodb_flush_log_at_trx_commit=0
#wsrep_cluster_name=‘testcluster‘ #集群的名称
#wsrep_node_name=‘node1‘ #当前节点的名称
#wsrep_node_address=‘192.168.73.110‘ #当前节点的地址
对每个节点配置文件进行修改，此处以node1为例
[root@node1 ~]# vim /etc/my.cnf.d/server.cnf
[galera]
# Mandatory settings
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://192.168.73.110,192.168.73.111,192.168.73.112"
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
四、启动服务
第一个启动的节点需要添加--wsrep-new-cluster选项，说明这是一个新的集群，其余节点启动服务无需添加参数
[root@node1 ~]# service mysql start --wsrep-new-cluster
node2节点启动服务
[root@node2 ~]# service mysql start
node3节点启动服务
[root@node3 ~]# service mysql start
五、测试
1.测试同步
从node1导入数据库
[root@node1 ~]# mysql < hellodb_innodb.sql
node2查看
[root@node2 ~]# mysql -e "SHOW DATABASES;"
+--------------------+
| Database |
+--------------------+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
node3查看
[root@node3 ~]# mysql -e "SHOW DATABASES;"
+--------------------+
| Database |
+--------------------+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
2.测试冲突
同时在3节点创建表
在node2节点成功，其余节点都失败
[root@node2 ~]# mysql -e "CREATE TABLE hellodb.test(id int auto_increment primary key,name char(20));"
[root@localhost ~]#
node1节点
[root@node1 ~]# mysql -e "CREATE TABLE hellodb.test(id int auto_increment primary key,name char(20));"
ERROR 1050 (42S01) at line 1: Table ‘test‘ already exists
node3节点
[root@node3 ~]# mysql -e "CREATE TABLE hellodb.test(id int auto_increment primary key,name char(20));"
ERROR 1050 (42S01) at line 1: Table ‘test‘ already exists
其他
Galera Cluster系统和状态变量
1.查看状态变量:SHOW STATUS LIKE ‘wsrep_%‘
MariaDB [(none)]> SHOW STATUS LIKE ‘wsrep_%‘;
+------------------------------+-------------------------------------------------------------+
| Variable_name | Value |
+------------------------------+-------------------------------------------------------------+
| wsrep_local_state_uuid | e9f07cd3-7253-11e9-b27e-174ea2b4587d |
| wsrep_protocol_version | 9 |
| wsrep_last_committed | 38 |
| wsrep_replicated | 36 |
| wsrep_replicated_bytes | 18960 |
| wsrep_repl_keys | 144 |
| wsrep_repl_keys_bytes | 2016 |
| wsrep_repl_data_bytes | 14522 |
| wsrep_repl_other_bytes | 0 |
| wsrep_received | 12 |
| wsrep_received_bytes | 1782 |
| wsrep_local_commits | 6 |
| wsrep_local_cert_failures | 0 |
| wsrep_local_replays | 0 |
| wsrep_local_send_queue | 0 |
| wsrep_local_send_queue_max | 1 |
| wsrep_local_send_queue_min | 0 |
| wsrep_local_send_queue_avg | 0.000000 原文：https://blog.51cto.com/14231603/2393958