资料库对于网际互联网公司来说是一个公司的心脏，没有了它这个公司绝对只是一堆 ppt 。由此对于一个运维来说资料库绝对是维护的重中之重，每天都要对资料库进行增量备份，每周要进行一次完全备份。常用的备份工具 mysqldump 这是一个逻辑被分工具那就意味著效能将会被计算消耗一些；extrabackup 这是一个物理备份工具，具有较好的效能；还有一种借助 lvm 进行备份的方法，这种方法的显然不靠谱，因为 lvm 储存的资料不具有硬体级恢复资料的特性，一旦遇到极端情况，我们只能接受资料丢失。
资料库需要备份什么？1 资料，日志；2 程式，储存例程；3 配置档案。既然要备份这些资料，我们需要了解他们的结构，配置档案好理解也好备份，程式和储存例程的备份也比较好备份只要提供统样的执行环境。
资料备份就很麻烦了，我们需要充分的了解 mariadb 的执行原理。其中在 mariadb 储存时使用的是黑盒储存，那就造成一个问题，我们要检视资料库中的资料只有使用 mysql 客户端。那么我们备份这些资料就有两个方法。一个是逻辑备份，我们把资料反向生成一个 mysql 客户端产生资料的方法，这个备份会有一个极大的问题，反向生成会很慢，恢复资料也会很慢；另一个是物理备份，我们直接把黑盒资料复制一份，但是这种备份就很麻烦了，备份资料库我们不能把资料库停下来吧，万一来了一个大单资料库停会造成很大的影响。每天都例行对资料库进行维护貌似只有某个交通公司在这么干，作为以使用者为中心的公司这么做很有可能会流失客户，备份的特性依赖于储存引擎。
储存引擎
表型别：表级别概念，不建议在同一个库中的表上使用不同的 ENGINE；
CREATE TABLE … ENGINE[=]STORAGE_ENGINE_NAME … #定义资料库表使用的储存引擎
SHOW TABLE STATUS #检视储存引擎
常见的储存引擎：
MyISAM, Aria, InnoDB, MRG_MYISAM, CSV, BLACKHOLE, MEMORY, PERFORMANCE_SCHEMA, ARCHIVE, FEDERATED
当前资料库支援的储存引擎
mysql> SHOW ENGINES;
InnoDB：InnoBase
Percona-XtraDB, Supports transactions, row-level locking, and foreign keys
资料储存于 “表空间 (table space)” 中：
(1) 所有 InnoDB 表的资料和索引储存于同一个表空间中；
表空间档案：datadir 定义的目录中
档案：ibdata1, ibdata2, …
(2) innodb_file_per_table=ON，意味著每表使用单独的表空间档案；
资料档案（资料和索引，储存于资料库目录）: tbl_name.ibd
表结构的定义：在资料库目录，tbl_name.frm
事务型储存引擎，适合对事务要求较高的场景中；但较适用于处理大量短期事务；
基于 MVCC（Mutli Version Concurrency Control）支援高并发；支援四个隔离级别，预设级别为 REPEATABLE-READ；间隙锁以防止幻读；
使用聚集索引（主键索引）；
支援” 自适应 Hash 索引 “；
锁粒度：行级锁；间隙锁；
总结：
资料储存：表空间；
并发：MVCC，间隙锁，行级锁；
事务：REPEATABLE-READ；适用于大量短期事务；
索引：聚集索引、辅助索引；
效能：预读操作、内存资料缓冲、内存索引 WordPress 加速缓存、自适应 Hash 索引、插入操作 WordPress 加速缓存区；
备份：支援热备；
MyISAM：
支援全文索引（FULLTEXT index）、压缩、空间函式（GIS）；
不支援事务
锁粒度：表级锁
崩溃无法保证表安全恢复
适用场景：只读或读多写少的场景、较小的表（以保证崩溃后恢复的时间较短）；
档案：每个表有三个档案，储存于资料库目录中
tbl_name.frm：表格式定义；
tbl_name.MYD：资料档案；
tbl_name.MYI：索引档案；
特性：
加锁和并发：表级锁；
修复：手动或自动修复、但可能会丢失资料；
索引：非聚集索引；
延迟索引更新；
表压缩；
日志备份
日志备份就很难理解了，mariadb 设计时就考虑到资料的完全性，必须保证资料写入成功，那么设计一套记录日志：
访问日志
慢访问日志，访问时间较长的命令
二进位制日志，记录导致资料库变化的命令。
中继日志，资料库主从
那么为什么要备份日志，因为我们备份资料后，资料库发生变化那么怎么保证资料的完全性，这里我们可以借助二进位制日志，把备份的资料库后变更的操作再次执行一遍，我们就可以恢复资料库的资料了。
逻辑备份恢复资料库

1
2
3
4
5
6
7
8
9
10
11
12
13
14

#首先准备好备份
mysqldump –databases DATABASENAME –single-transaction -R –triggers -E –flush-logs –master-data=2 > /tmp/backup.sql
#然后备份二进位制日志，去掉不想执行的语句
mysqlbinlog –stop-position=# /var/lib/mysql/master-log.00000# > /tmp/bin.log
mysql < /tmp/bin.log #然后备份二进位制日志，去掉不想执行的语句

1
2
3
4
5
6
7
8
9

innobackupex –copy-back /BACKUP/DIR #恢复备份
chown -R mysql.mysql /var/lib/mysql
systemctl start mariadb.service
#恢复二进位制日志中产生的资料
mysql < GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO ‘USERNAME’@’HOST’ IDENTIFIED BY ‘YOUR_PASSWORD’;
mysql> FLUSH PRIVILEGES;

从站群服务器

1
2
3
4
5
6
7
8
9

vim /etc/my.cnf
innodb_file_per_table=1
skip_name_resolve=1
server_id=#
relay_log=relay-log
启动服务：
mysql> CHANGE MASTER TO MASTER_HOST=’HOST’,MASTER_USER=’USERNAME’,MASTER_PASSWORD=’YOUR_PASSWORD’,MASTER_LOG_FILE=’BINLOG’,MASTER_LOG_POS=#;
mysql> START SLAVE [IO_THREAD|SQL_THREAD];
mysql> SHOW SLAVE STATUS;

ssl
配置主站群服务器

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

建立证书档案
cd /etc/pki/CA/
touch index.txt
echo 01 > serial
(umask 066;openssl genrsa -out private/cakey.pem 2048)
openssl req -new -x509 -key private/cakey.pem -out cacert.pem
(umask 066;openssl genrsa -out mysql.key 2048; )
openssl req -new -key mysql.key -out mysql.csr -days 365
openssl ca -in mysql.csr -out mysql.crt -days 700
(umask 066;openssl genrsa -out client.key 2048; )
openssl req -new -key client.key -out client.csr -days 365
openssl ca -in client.csr -out client.crt -days 700
#复制证书档案，并更改属组属主
cp cacert.pem mysql.crt mysql.key client.key client.crt /etc/mysql/
chown -R mysql.mysql /etc/mysql
#复制秘钥证书，前提需要在 slave 上建立/etc/mysql 目录
scp -p client.key client.crt cacert.pem 172.16.29.10:/etc/mysql

1
2
3
4
5
6
7
8
9
10
11

vim /etc/my.cnf
#在 [mysqld] 段中新增如下配置
ssl #开启 SSL 功能
ssl-ca = /etc/mysql/cacert.pem #指定 CA 档案位置
ssl-cert = /etc/mysql/mysql.crt #指定证书档案位置
ssl-key = /etc/mysql/mysql.key #指定金钥所在位置
#开启站群服务器
systemctl restart mariadb.service
#授权
mysql
grant replication slave,replication client on *.* to ‘tom’@’172.16.%.%’ identified by ‘tom’ require ssl;

slave 节点

1
2
3
4
5
6

#在 slave 上连线
cd /etc/mysql/
mysql –ssl-ca=cacert.pem –ssl-cert=client.crt –ssl-key=client.key -h172.16.29.2 -utom -ptom
#检视连线状态
s
quit

显示如下一行使用 ssl 代表成功

配置从站群服务器

1
2
3
4
5

systemctl restart mariadb.service
mysql
change master to master_host=’172.16.29.2′,master_user=’tom’,master_password=’tom’,master_log_file=’master-log.000008′,master_log_pos=245,master_ssl=1,master_ssl_ca=’/wp-content/uploads/sites/3/etc/mysql/cacert.pem’,master_ssl_cert=’/wp-content/uploads/sites/3/etc/mysql/client.crt’,master_ssl_key=’/wp-content/uploads/sites/3/etc/mysql/client.key’;
start slave;
show slave statusG

如图代表配置成功

mha
安装包下载地址 https://code.google.com/p/mysql-master-ha/downloads/list
从站群服务器安装

1

yum install mha4mysql-node-0.54-0.el6.noarch.rpm -y

主站群服务器

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

yum install mha4mysql-node-0.54-0.el6.noarch.rpm mha4mysql-manager-0.55-0.el6.noarch.rpm -y
[root@manager ~]# cat /usr/local/mha/mha.cnf
[server default]
user=mha_rep #MHA 管理 mysql 的使用者名称
password=123456 #MHA 管理 mysql 的密码
manager_workdir=/usr/local/mha #MHA 的工作目录
manager_log=/usr/local/mha/manager.log #MHA 的日志路径
ssh_user=root #免秘钥登陆的使用者名称
repl_user=backup #主从复制账号，用来在主从之间同步资料
repl_password=backup
ping_interval=1 #ping 间隔时间，用来检查 master 是否正常
[server1]
hostname=192.168.253.241
master_binlog_dir=/data/mysql/
candidate_master=1 #master 宕机后，优先启用这台作为 master
[server2]
hostname=192.168.253.242
master_binlog_dir=/data/mysql/
candidate_master=1
[server3]
hostname=192.168.253.243
master_binlog_dir=/data/mysql/
no_master=1 #设定 na_master=1，使站群服务器不能成为 master
#检查 ssh 是否畅通
masterha_check_ssh –conf=/usr/local/mha/mha.cnf
#执行，这个指令码生效后就自动退出了
nohup masterha_manager –conf=/usr/local/mha/mha.cnf > /tmp/mha_manager.log 2>&1 &

双主
互为主从：两个节点各自都要开启 binlog 和 relay log；
1 、资料不一致；
2 、自动增长 id；
定义一个节点使用奇数 id
auto_increment_offset=1
auto_increment_increment=2
另一个节点使用偶数 id
auto_increment_offset=2
auto_increment_increment=2
配置：
1 、 server_id 必须要使用不同值；
2 、均启用 binlog 和 relay log；
3 、存在自动增长 id 的表，为了使得 id 不相冲突，需要定义其自动增长方式；
服务启动后执行如下两步：
4 、都授权有复制许可权的使用者账号；
5 、各把对方指定为主节点；
复制过滤器
仅复制有限一个或几个资料库相关的资料，而非所有；由复制过滤器进行；
有两种实现思路：
(1) 主站群服务器
主站群服务器仅向二进位制日志中记录有关特定资料库相关的写操作；
问题：其它库的 point-recovery 将无从实现；
binlog_do_db=
binlog_ignore_db=
(2) 从站群服务器
从站群服务器的 SQL THREAD 仅重放关注的资料库或表相关的事件，并将其应用于本地；
问题：互联网 IO 和磁碟 IO；
Replicate_Do_DB=
Replicate_Ignore_DB=
Replicate_Do_Table=
Replicate_Ignore_Table=
Replicate_Wild_Do_Table=
Replicate_Wild_Ignore_Table=
总结
备份的资料有：资料，日志，配置档案，程式，
怎么备份：逻辑备份，借助 lvm 备份，物理备份（全量备份，增量备份，变数备份）
高可用：主从，双主，加密通讯，复制过滤