数据库对于互联网公司来说是一个公司的心脏,没有了它这个公司绝对只是一堆 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;适用于大量短期事务;
索引:聚集索引、辅助索引;
性能:预读操作、内存数据缓冲、内存索引缓存、自适应 Hash 索引、插入操作缓存区;
备份:支持热备;
MyISAM:
支持全文索引(FULLTEXT index)、压缩、空间函数(GIS);
不支持事务
锁粒度:表级锁
崩溃无法保证表安全恢复
适用场景:只读或读多写少的场景、较小的表(以保证崩溃后恢复的时间较短);
文件:每个表有三个文件,存储于数据库目录中
tbl_name.frm:表格式定义;
tbl_name.MYD:数据文件;
tbl_name.MYI:索引文件;
特性:
加锁和并发:表级锁;
修复:手动或自动修复、但可能会丢失数据;
索引:非聚集索引;
延迟索引更新;
表压缩;
日志备份
日志备份就很难理解了,mariadb 设计时就考虑到数据的完全性,必须保证数据写入成功,那么设计一套记录日志:
访问日志
慢访问日志,访问时间较长的命令
二进制日志,记录导致数据库变化的命令。
中继日志,数据库主从
那么为什么要备份日志,因为我们备份数据后,数据库发生变化那么怎么保证数据的完全性,这里我们可以借助二进制日志,把备份的数据库后变更的操作再次执行一遍,我们就可以恢复数据库的数据了。
逻辑备份恢复数据库
- #首先准备好备份
- 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 <<eof
- #停止记录二进制日志
- set @@session.sql_log_bin=OFF;
- #恢复备份的数据
- source /tmp/backup.sql;
- #恢复二进制日志中产生的数据
- source /tmp/bin.log;
- #开启记录二进制日志
- set @@session.sql_log_bin=ON;
- eof
物理备份
物理备份我们可以进行全量备份,增量备份,变量备份。在这个地址下载 xtrabackup:
全量备份
- innobackupex --user=root --host=localhost --password=aaa /BACKUP/DIR 备份数据库
- innobackupex --apply-log /BACKUP/DIR #备份日志
- mysqlbinlog --start-position=# --stop-position=# /var/lib/mysql/master-log.00000# > /tmp/bin.log #然后备份二进制日志,去掉不想执行的语句
- innobackupex --copy-back /BACKUP/DIR #恢复备份
- chown -R mysql.mysql /var/lib/mysql
- systemctl start mariadb.service
- #恢复二进制日志中产生的数据
- mysql <<eof
- set @@session.sql_log_bin=OFF;
- source /tmp/bin.log;
- set @@session.sql_log_bin=ON;
- eof
增量备份
- innodbbackupex --incremental --user=root --host=localhost --password=aaa incremental-basedir=/BACKUP/DIR
- 备份合并,把增量备份的数据合并到全量备份里
- innodbbackupex --apply-log --redo-only BASE-DIR incremental-basedir=/BACKUP/DIR
主从复制
只进行备份是远远不够的,当 mariadb 故障的这就需要我们提供高可用集群;数据库的读请求量特别大的时候,我们需要一个服务器分担读请求;写请求也变大,这里不讨论。先说读请求变大我们可以增加服务器,分担压力,一般使用主从,或者使用双主。
主从
使用主从的话,当写节点故障需要我们提供迁移工具常用的 mha
主服务器
- vim /etc/my.cnf
- innodb_file_per_table=1
- skip_name_resolve=1
- server_id=#
- log_bin=log-bin
- 启动服务,并授权
- mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'USERNAME'@'HOST' IDENTIFIED BY 'YOUR_PASSWORD';
- mysql> FLUSH PRIVILEGES;
从服务器
- 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
配置主服务器
- 创建证书文件
- 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
- 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 节点
- #在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 代表成功
配置从服务器
- 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 = ' / etc / mysql / cacert.pem',
- master_ssl_cert = ' / etc / mysql / client.crt',
- master_ssl_key = ' / etc / mysql / client.key';
- start slave;
- show slave status\G
如图代表配置成功
mha
安装包下载地址
从服务器安装
- yum install mha4mysql-node-0.54-0.el6.noarch.rpm -y
主服务器
- 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 备份,物理备份(全量备份,增量备份,变量备份)
高可用:主从,双主,加密通信,复制过滤
来源: http://www.bubuko.com/infodetail-1961465.html