Debian10备份与恢复MariaDB数据库

发表于 更新于 分类于 ， ， ， 阅读次数： Waline： 阅读次数： 本文字数： 2.8k 阅读时长 ≈ 6 分钟
Debian10备份与恢复mariaDB数据库的一些操作记录

一、利用mysqldump备份与恢复命令：

1
2
3
4
5
$ sudo mysqldump -uroot -p wpDB > ~/mysql-wpDB.dump  //备份数据库名及保存路径

$ sudo mysql -uroot  //进入数据库
MariaDB [(none)]> create database wpDBtest; //创建测试恢复数据库,单库备份需先创建该数据库
$ mysql -uroot -p wpDBtest < ~/mysql-wpDB.dump;  //恢复数据库

注意：上诉备份方案存在一定的弊端如：用户正在执行数据操作时，数据则会丢失等情况。

解决方案：

1》在备份数据库表加上锁，保证数据的完整性。

--lock-all-tables:请求锁定所有表
实例：

1
$ mysqldump --databases wpDBtest --lock-all-tables >~/wpDBtest .sql

--single-transaction:单事务，能够对innodb存储引擎做热备

--events:备份事件调度器代码

--routines:备份存储过程和存储函数

--triggers:备份触发器

备份时请求锁之后滚动日志：--flush-logs

复制时同步位置标记：--master-data=[0|1|2]

注意：恢复时需关闭二进制文件，恢复成功后则开启

关闭二进制日志：set session sql_log_bin=0

开启二进制日志：set session sql_log_bin=1

2》锁表备份步骤：

1)锁表并且给只读权限
1
MariaDB [(none)]>flush tables with read lock;
2)滚动日志
1
MariaDB [(none)]>flush logs;
3)查看二进制日志信息
1
MariaDB [(none)]>show master status;
4)使用mysqldump进行数据备份
1
$ mysqldump --databases wpDBtest >/tmp/wpDBtest.sql
5)备份完成之后则需解锁
1
MariaDB [(none)]>unlock tables;

命令行备份实例如下：

1
2
3
mysqldump -p --databases wpDBtest --lock-all-tables --flush-logs >/home/yk/wpDBtest2.sql 
mysqldump -p --databases wpDBtest --single-trasaction --flush-logs >~/wpDBtest3.sql 
mysqldump -p --databases wpDBtest --lock-all-tables --flush-logs --master-data=2 >~/wpDBtest4.sql

2》查看恢复的数据

1
2
3
$ sudo mysql -uroot  //进入数据库 
$ use wpDBtest;   /选择查看数据库
MariaDB [wpDBtest]> select * from wp_options where option_name in("siteurl","home"); //查看恢复信息

二、用二进制日志恢复命令：

1》开启binlog

1
2
3
$ sudo vim /etc/alternatives/my.cnf  //添加如下内容，开启binlog
[mysqld]
log_bin=mysql-bin

2》//查看log_bin是否开启

1
MariaDB [(none)]>  show variables like 'log_bin';

//查看二进制log，--no-defaults避免出现字符问题错误

1
$ sudo mysqlbinlog --no-defaults /var/lib/mysql/mysql-bin.000001;

3》测试过程

1、创建测试数据库并新增表和记录

1
2
3
4
5
6
MariaDB [wpDBtest]> create database testDB;
MariaDB [testDB]> create table testtable (id int null, name varchar(255) null);
MariaDB [(none)]> source ~/wpDBtest-re.sql
MariaDB [testDB]> insert into testtable values(1,"xiaoming");
MariaDB [testDB]> insert into testtable values(2,"wang");
MariaDB [testDB]> insert into testtable values(3,"王");

2、删除一条测试数据

1
2
3
4
5
6
7
8
9
10
11
MariaDB [testDB]> delete from testtable where id =2;
##MariaDB [testDB]> delete from testtable;
##Query OK, 5 rows affected (0.027 sec)
MariaDB [testDB]> select * from testtable; //显示如下：
+------+----------+
| id   | name     |
+------+----------+
|    1 | xiaoming |
|    3 | 王       |
+------+----------+
2 rows in set (0.001 sec)

3、导出二进制log为sql,--database=testDB 指定导出数据库testDB的数据

1
$ sudo  mysqlbinlog --no-defaults --database=testDB /var/lib/mysql/mysql-bin.000001>~/testDB.sql;

4、进入~/文件夹，复制原数据并另存testDB-re.sql

1
2
$ cp testDB.sql testDB-re.sql
$ sudo vim testDB-re.sql  //打开sql文件，删除其中“delete from testtable where id =2”语句

5、进入mariadb，恢复数据

1
2
3
4
5
6
7
8
9
10
11
12
MariaDB [testDB]> source ~/testDB-re.sql
MariaDB [testDB]> select * from testtable;
+------+----------+
| id   | name     |
+------+----------+
|    1 | xiaoming |
|    3 | 王       |
|    1 | xiaoming |
|    2 | wang     |
|    3 | 王       |
+------+----------+
5 rows in set (0.001 sec)

关于前期基础介绍请参考
Debian10下MariaDB/MySQL数据库增、删、改、查、备份及恢复

参考资料：
mariadb二进制日志和备份恢复

相关文章