南京华籁网络科技有限公司
025-85999293 4006661495 15950533238
  1. 首页 > 解决方案 > 技术博客

mariadb10.1以及新版本如何进行数据库备份与恢复

作者:华籁云 日期:2019-11-14 08:29:13 点击数:

1.查询日志:

记录每一条sql语句,建议不开启,因为如果访问量较大,会占用相当大的资源,影响性能;

vim /etc/my.cnf.d/server.cnf

general_log = ON| OFF#查询日志开关

general_log_file localhost.log #查询日志的文件名字(/var/lib/mysql)

log_output TABLE | FILE | NONE #查询日志的存储形式

2.慢查询日志

执行时长超出指定时间的查询操作(测试命令:select sleep(4);)

slow_query_log = OFF|ON  #开启慢查询日志

slow_query_log_file = LOCALHOST-SLOW.log #慢查询日志的文件路径

long_query_time  #慢查询时长;默认是10s

log_slow_rate_limit#如果要记录的慢查询日志非常多的话,会按照速率来记录,默认1秒记录一个

log_slow_verbosity=full | query_plan #记录的详细级别

3.错误日志

mysqld启动和关闭过程中输出的事件信息

mysqld运行中产生的错误信息

event scheduler 运行一个event时产生的日志信息

在主从复制架构中的从服务器上启动从服务器线程时产生的信息

log_error = /var/log/mysql_error.log#指定错误日志的输出位置

log_warnings 为0, 表示不记录告警信息。

log_warnings 为1, 表示告警信息写入错误日志。

log_warnings 大于1, 表示各类告警信息,例如有关网络故障的信息和重新连接信息写入错误日志。(默认为2)

4.二进制日志

针对时间点还原起着至关重要的作用

1)查看二进制日志:

SHOW {BINARY | MASTER} LOGS

SHOW BINLOG EVENTS [IN 'log_name']

show master status;

2)记录导致数据改变或者可能导致数据改变的SQL语句

log_bin = OFF | ON

log_bin_basename = /var/lib/mysql/mysql-bin

binlog_format=STATEMENT|ROW|MIXED:二进制记录格式

STATEMENT:基于“语句”记录

ROW:基于“行”记录

MIXED:让系统自行判定该基于哪种方式进行

sql_log_bin=1|0:是否启用二进制日志

log_bin_index=PATH:二进制日志索引位置

sync_binlog=1|0:设定是否启动二进制日志同步功能

max_binlog_size=SIZE:单个二进制文件最大体积,默认为1G

expire_logs_days=0#超过多少天就清除二进制日志,默认为0,代表不启用此功能

3)二进制日志滚动:

1.flush logs;

2.文件超出指定大小

3.service mariadb restart

4)二进制日志文件的构成:

两类文件:

日志文件:mysql-bin.xxxxx,二进制格式

索引文件:mysql-bin.index,索引文件

5)二进制日志格式:

#190613 14:17:32 server id 1  end_log_pos 666 CRC32 0xeb1cde6b  Query   thread_id=9     exec_time=

0       error_code=0

use `testdb`/*!*/;

事件发生的日期和时间:190613 14:17:32

事件发生的服务器标识:server id 1

事件的结束位置:end_log_pos 666

事件的类型:Query

事件发生时所在服务器执行此事件的线程ID:thread_id=9

语句的时间戳与将其写入二进制文件中的时间差:exec_time=0

错误代码:error_code=0

事件内容:

GTID:Global Transaction ID;

专属属性:GTID

6)mysqlbinlog:客户端命令工具

mysqlbinlog [options] log_file ...

--start-datetime=

--stop-datetime=

--start-position=

--stop-position=

5.中继日志:

复制架构中,备服务器用于保存主服务器的二进制日志中读取到的事件

6.事务日志:transaction log(ib_logfile0,ib_logfile1)

innodb_buffer_pool_size                                一般设置成为物理内存的3/4,或者4/5

innodb_log_files_in_group = 2                          事务日志文件的个数,默认为2个事务日志文件

innodb_log_file_size = 50331648(48m)        事务日志文件的单个大小48m

innodb_log_group_home_dir = ./                         事务日志文件的所在路径,默认就在mariadb的数据目录/var/lib/mysql

事务型存储引擎自行管理和使用(Innodb,myisam引擎是不支持事务,外键,行级锁)

redo log : 重做日志

undo log :撤销日志

7.备份和恢复:

1)为什么要备份?

灾难恢复:硬件故障,软件故障,自然灾害,黑客攻击,误操作

测试

2)要注意的要点:

备份需要多少时间

能容忍最多丢失多少数据

恢复数据需要在多场时间内完成

需要恢复哪些数据

(1)做还原测试,用于测试备份的可用性

(2)还原演练

3)备份类型:

完全备份,部分备份

完全备份:整个数据集

部分备份:只备份数据子集

完全备份、增量备份、差异备份

增量备份:仅备份最近一次完全备份或增量备份(如果有增量备份)以来变化的数据

差异备份:仅备份最近一次完全备份以来变化的数据;

热备、温备、冷备

热备:读写操作均可执行

温备:读操作可以、写不行

冷备:读写操作均不能执行

MyISAM:温备,不能热备;

Innodb:热备

物理备份、逻辑备份

物理备份:直接复制数据文件进行备份,与存储引擎无关

逻辑备份:从数据库中“导出”数据库另存而进行备份

备份时需要考虑的因素:

持续多久

备份过程的时长

备份负载

恢复过程的时长

备份什么?

要备份的数据

二进制日志,innodb的事务日志

代码(存储过程,存储函数,触发器,时间调度器)

服务器的配置文件

设计备份方案:

数据集:完全+增量

备份手段:物理,逻辑

对于备份较大的数据建议物理备份,对于较小的数据建议用逻辑备份;

备份工具的选择:

mysqldump+复制binlog:

mysqldump:完全备份

复制binlog中指定时间范围内的event:增量备份

lvm2快照+复制binlog:

lvm2快照:适用cp或者tar等做物理备份:完全备份

复制binlog中指定时间范围内的event:增量备份

xtrabackup|mariabackup:

由Percona提供的支持对InnoDB做热备(物理备份)工具

完全备份,增量工具

4)基于二进制文件的恢复

1.算好要恢复数据的时间段,重定向输入到bin.sql文件中

mysqlbinlog --start-datetime="2019-06-08 22:55:13" --stop-datetime="2019-06-08 22:55:13" binlog.0000011 > bin.sql

mysqlbinlog /var/lib/mysql/mysqlbin.000001 --stop-pos=1093 | mysql -uroot -proot

2.执行bin.sql文件还原

source bin.sql

5)基于lvm2的备份

(1)请求锁定所有表

flush tables with read lock;

(2)记录二进制日志文件及事件位置或时间点

flush logs;

mysql -uroot -proot -e 'show  master status;' > /path/to/somefile

(3)创建快照

lvcreate -L # -s -p r -n NAME /DEV/VG_NAME/LV_NAME

(4)释放锁

unlock tables;

(5)挂在快照卷,执行数据备份

(6)备份完成后,删除快照卷

(7)指定好策略,通过原卷备份二进制日志

(8)还原的时候确保属组和属主是mysql:mysql

如果是yum安装默认数据目录在/var/lib/mysql,如果需要更改

[mysqld]

datadir=/mysql_data/

pid_file=/mysql_data/localhost.pid

socket=/mysql_data/mysql.sock

wsrep_data_home_dir=/mysql_data/

log-bin=mysql-bin

[client]

socket=/mysql_data/mysql.sock

6)备份工具mariabackup

Mariabackup

简介

Mariabackup是MariaDB提供的一个开源工具,用于对InnoDB,Aria和MyISAM表进行物理在线备份。这个工具是基于Percona的XtraBackup(版本2.3.8)的解决方案。

这里有一点需要注意,在MariaDB10.3.x及以上的版本用Percona XtraBackup工具会有问题。原因可能是MariaDB10.3以上版本的redo日志格式和之前不同了。

Percona的官方文档:https://www.percona.com/doc/percona-xtrabackup/2.4/index.html

Mariabackup的官方文档:https://mariadb.com/kb/en/library/mariabackup-overview/

安装方法

yum install MariaDB-backup

备份与恢复

全备+恢复

1.全量备份

mariabackup --backup --target-dir=/root/fullbackup --user=root --password=root

2.准备全备数据

mariabackup --prepare --target-dir=/root/fullbackup/ --user=root --password=root

3.还原数据(请确保数据目录下是空的)

mariabackup --copy-back --target-dir=/root/fullbackup/ --user=root --password=root

4.修改属组和属主

chown -R mysql.mysql /var/lib/mysql

全备+增量+恢复

1.全量备份

mariabackup --backup --target-dir=/root/fullbackup --user=root --password=root

2.增量备份

mariabackup --backup --target-dir=/root/inc1 --incremental-basedir=/root/fullbackup/ --user=root --password=root

3.准备全备数据

mariabackup --prepare --target-dir=/root/fullbackup/ --user=root --password=root

4.增量和全备数据合并

mariabackup --prepare --target-dir=/root/fullbackup/ --user=root --password=root --incremental-dir=/root/inc1/ --apply-log-only

5.恢复数据(请确保数据目录下是空的)

mariabackup --copy-back --target-dir=/root/fullbackup/ --user=root --password=root

6.修改属组和属主

chown -R mysql.mysql /var/lib/mysql

7)基于mysqldump工具进行备份(逻辑备份工具)

备份testdb数据库(-l代表备份单个数据库时锁定该库的所有表;-x代表对所有表进行备份时锁定所有数据库的所有表)

mysqldump -uroot -proot -l --databases testdb > testdb.sql

备份所有数据库

mysqldump -uroot -proot --all-databases > all-databases.sql

备份testdb数据库下的students表

mysqldump -uroot -proot testdb students > students.sql

还原数据

source testdb.sql


关于我们:华籁云(www.hualay.net.cn)成立于2009年,隶属南京华籁网络科技有限公司,是一家专注于网站及服务器综合业务提供商。服务包括:南京网站建设南京域名注册南京虚拟主机南京服务器租用南京服务器维护南京网站维护,公司配有客服、技术、开发等部门,拥有数名安全专家,7*24为用户提供技术服务。

有任何问题欢迎联系华籁云(HUALAY)我们将竭诚为您服务!

7*24小时咨询热线:15950533238    18066058025    联系技术QQ:465597938    75745732    236348786

全国统一客服热线:400-666-1495    公司总机:025-85999293    客服邮箱:service#hualay.net

联系地址:江苏省南京市建邺区庐山路188号  |  江苏省南京市江宁区万达广场F座


随便看看