MySQL数据库_备份与还原实验集

By | 2019年 11月 23日
0 Comment

实验：单数据库备份与还原

  1. 看下现有的数据库
[centos]$ mysql -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mysql              |
| performance_schema |
| test               |
+--------------------+
  1. 现在来备份它
[centos]$ mysqldump -B hellodb > hellodb_backup.sql
  1. 删除“hellodb”这个数据库，待会我们来还原它
[centos]$ mysql -e 'drop database hellodb;'
[centos]$ mysql -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
  1. 还原它
[centos]$ mysql < hellodb_backup.sql
[centos]$ mysql -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mysql              |
| performance_schema |
| test               |
+--------------------+
  1. 还原了。我们看下数据还在不在
[102]$ mysql -e 'select * from hellodb.students';
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
|    26 | wang          |  20 | F      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+

实验：备份所有数据库

  1. 来看下我们有什么数据库
[centos]$ mysql -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mysql              |
| performance_schema |
| test               |
+--------------------+
  1. 备份所有
[centos]$ mysqldump -A > mysql_all.sql

[centos]$ ll
-rw-r--r--  1 root root  521807 Jul 10 15:48 mysql_all.sql
  1. 来点暴力点的，直接把所有数据库文件删除了
[centos]$ rm -rf  /var/lib/mysql/*
  1. 重启服务，可以看到基本的数据库已经有了，但是我们的hellodb数据库不见了，当然如果你有其它数据库，也都没了
[centos]$ systemctl restart mariadb

[centos]$ mysql  -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
  1. 如果我们开启了二进制日志记录，那就得临时禁用二进制日志功能，否则会生成大量的二进制日志
[centos]$ mysql

MariaDB [(none)]> set sql_log_bin=off;
  1. source来调用还原的文件
MariaDB [(none)]> source /data/backup/all.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

...
  1. 及时把二进制日志功能启用
MariaDB [test]> set sql_log_bin=on;
Query OK, 0 rows affected (0.00 sec)
  1. 看下。我们的数据库回来了
MariaDB [test]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

实验：拯救误操作并还原到最新的数据库

  • 场景：数据库每日凌晨2点进行备份，而在当天的18点的时候，有人不小心把表删除了，等到18:10的时候，才发现业务故障。
  • 面临问题：现在需要还原数据库到最新而不想丢失数据，该怎么办呢？
  • 注意：假设我们已经开启二进制日志功能，

模拟每日备份

  1. 备份
[centos]$ mysqldump -A -F --single-transaction --master-data=2 > /data/all.sql

[centos]$ ll
-rw-r--r--  1 root root  523114 Jul 10 16:15 mysql-all.sql

模拟生产业务产生的数据
2. 数据库修改 

insert hellodb.students(name,age)value('wang',20);
insert hellodb.students(name,age)value('chen',20);

这时候，18：00，有人不小心删除了某个表
3. 模式数据库损坏 

#这里用删库来模式,删除你的数据库的路径(删除文件，不要把目录删除了)
mysql> drop table hellodb.students;

18：10，我们得及时的禁止别的用户访问
4. 确保无用户访问 

vim /etc/my.cnf
    [mysqld]
    skip-networking

systemctl restart mysqld

还原数据库
5. 查看二进制日志的位置，并导出 

#查看最近一次备份的二进制日志的位置,记住这2个值，mysql-bin.000002和245
[centos]$ cat /data/all.sql
  22  MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=245

#查看当前二进制日志的位置
[centos]$ mysql -e 'show master logs'
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| my-bin.000001 |       729 |
| my-bin.000002 |       776 |
| my-bin.000003 |       245 |
+---------------+-----------+
  1. 把二进制日志导出
[centos]$ mysqlbinlog --start-position=245 /data/logbin/my-bin.000002 > bindump.sql
[centos]$ mysqlbinlog /data/logbin/my-bin.000003 >> bindump.sql


#找到删除表的那个操作，删除了
[centos]$ vim bindump.sql
    DROP TABLE...   #把这条删除了，或者注释了

下面的还原就和上面一样了
7. 恢复之前看下，我们的表是没了的 

[centos]$ mysql -e 'use hellodb;show tables'
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| teachers          |
| testlog           |
| toc               |
+-------------------+
  1. 比较干净的方法，把原有的数据库删除了，再恢复。当然也可以直接恢复
[centos]$ rm -rf /var/lib/mysql/* 

#如果启用了二进制日志，还原时不启动二进制日志服务
mysql> set sql_log_bin=off;

#配合二进制日志还原
mysql> source /data/mysql-all.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
...
mysql> source /data/bindump.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
...

#重新开启二进制日志服务
mysql> set sql_log_bin=on;
  1. 确认数据库恢复成功并恢复用户访问
    把上面的配置skip-networking删除了
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| testlog           |
| toc               |
+-------------------+

MariaDB [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
|    26 | wang          |  20 | F      |    NULL |      NULL |
|    27 | wang          | 100 | F      |    NULL |      NULL |
|    28 | wang100       | 100 | F      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+

实验：xtrabackup完全备份及还原

在原主机做完全备份到/backups 

#备份
[centos]$ xtrabackup --backup --target-dir=/backup/
#拷贝到要还原的主机
[centos]$ scp -r /backup/* 目标IP:/backup

备份时出错?：

[101]$ xtrabackup --backup --user='root' --password
xtrabackup: recognized server arguments: --datadir=/data/mysql
xtrabackup: recognized client arguments: --datadir=/data/mysql --target-dir=/data/backup --backup=1 --user=root --password
Enter password:
190708 22:01:49  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root'  (using password: NO).
190708 22:01:49  version_check Connected to MySQL server
190708 22:01:49  version_check Executing a version check against the server...
190708 22:01:49  version_check Done.
190708 22:01:49 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set
Using server version 10.4.6-MariaDB
xtrabackup version 2.4.13 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 3e7ca7c)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /data/mysql
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 50331648
InnoDB: Number of pools: 1
InnoDB: Unsupported redo log format. The redo log was created with MariaDB 10.4.6. Please follow the instructions at http://dev.mysql.com/doc/refman/5.7/en/upgrading-downgrading.html

这是因为我们安装的是mariadb-server-10.2版本，xtrabackup已经不支持了。
我们用的是这个版本，用yum直接安装。做实验建议使用

[103]$ yum info mariadb-server
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
Installed Packages
Name        : mariadb-server
Arch        : x86_64
Epoch       : 1
Version     : 5.5.60
Release     : 1.el7_5
Size        : 58 M
Repo        : installed
...

在目标主机上，也就是你要还原数据库的主机上

  1. 预准备：确保数据一致，提交完成的事务，回滚未完成的事务
[centos]$ xtrabackup --prepare --target-dir=/backup/
...
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1642024
190710 16:50:51 completed OK!

#最后一条OK就表示成功了
  1. 复制到数据库目录
    注意：数据库目录必须为空，MySQL服务不能启动
[centos]$ systemctl stop mariadb
#这里是你数据库的目录，别把mysql这个目录删除了，
[centos]$ rm -rf /var/lib/mysql/*

[centos]$ xtrabackup --copy-back --target-dir=/backup/
......
190710 16:52:37 [01]        ...done
190710 16:52:37 [01] Copying ./ibtmp1 to /var/lib/mysql/ibtmp1
190710 16:52:37 [01]        ...done
190710 16:52:37 completed OK!

#最后一条OK，就表示成功了
  1. 还原属性
[centos]$ chown -R mysql:mysql /var/lib/mysql

[centos]$ ll /var/lib/mysql/
total 40976
drwxr-x--- 2 mysql mysql      310 Jul 10 16:52 hellodb
-rw-r----- 1 mysql mysql 18874368 Jul 10 16:52 ibdata1
-rw-r----- 1 mysql mysql  5242880 Jul 10 16:52 ib_logfile0
-rw-r----- 1 mysql mysql  5242880 Jul 10 16:52 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 Jul 10 16:52 ibtmp1
drwxr-x--- 2 mysql mysql     4096 Jul 10 16:52 mysql
drwxr-x--- 2 mysql mysql     4096 Jul 10 16:52 performance_schema
drwxr-x--- 2 mysql mysql       20 Jul 10 16:52 test
-rw-r----- 1 mysql mysql      460 Jul 10 16:52 xtrabackup_info
-rw-r----- 1 mysql mysql        1 Jul 10 16:52 xtrabackup_master_key_id
  1. 启动服务
[centos]$ systemctl start mariadb

实验：xtrabackup完全，增量备份及还原

如果你做过上一个实验，请先还原下虚拟机

完全备份：

  1. 完全备份：
[centos]$ xtrabackup --backup --target-dir=/backup/base/
......
190710 17:01:03 [00] Writing /backup/base/xtrabackup_info
190710 17:01:03 [00]        ...done
xtrabackup: Transaction log of lsn (1641721) to (1641721) was copied.
190710 17:01:03 completed OK!

增量备份：

  1. 第一次修改数据
[centos]$ mysql

MariaDB [hellodb]> use hellodb;
#随便做一些变化
MariaDB [hellodb]> insert students(name,age)value('chen000',000);
MariaDB [hellodb]> insert students(name,age)value('chen999',999);
  1. 记住它，这是第一次的变化
MariaDB [hellodb]> select * from students;
.....
|    27 | wang          | 100 | F      |    NULL |      NULL |
|    28 | wang100       | 100 | F      |    NULL |      NULL |
|    29 | chen000       |   0 | F      |    NULL |      NULL |
|    30 | chen999       | 255 | F      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+
30 rows in set (0.00 sec)
  1. 第一次增量备份
#incremental-basedir后面跟的参数是指你对谁的增量，那就是对完全备份来增量咯
[centos]$ xtrabackup --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base
......
190710 17:05:02 [00] Writing /backup/inc1/xtrabackup_info
190710 17:05:02 [00]        ...done
xtrabackup: Transaction log of lsn (1644991) to (1644991) was copied.
190710 17:05:02 completed OK!
  1. 对比下增量备份和完全备份
[centos]$ du -h /backup/base
...
21M     /backup/base

[centos]$ du -h /backup/inc1
...
1.8M    /backup/inc1

第二次增量备份

  1. 第二次修改数据
[centos]$ mysql

MariaDB [hellodb]> use hellodb

MariaDB [hellodb]> insert students(name,age)value('zhao222',222);
Query OK, 1 row affected (0.00 sec)

MariaDB [hellodb]> insert students(name,age)value('li222',222);
Query OK, 1 row affected (0.00 sec)
  1. 改完后
MariaDB [hellodb]> select * from students;
......
|    28 | wang100       | 100 | F      |    NULL |      NULL |
|    29 | chen000       |   0 | F      |    NULL |      NULL |
|    30 | chen999       | 255 | F      |    NULL |      NULL |
|    31 | zhao222       | 222 | F      |    NULL |      NULL |
|    32 | li222         | 222 | F      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+
  1. 第二次增量
[centos]$ xtrabackup --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1
......
190710 17:11:34 [00] Writing /backup/inc2/xtrabackup_info
190710 17:11:34 [00]        ...done
xtrabackup: Transaction log of lsn (1647068) to (1647068) was copied.
190710 17:11:34 completed OK!

如果是对本机还原，就不用做下面这一步了
3. 复制到目标主机

[centos]$ ll
drwxr-x--- 6 root root 217 Jul 10 17:01 base
drwxr-x--- 6 root root 243 Jul 10 17:05 inc1
drwxr-x--- 6 root root 243 Jul 10 17:11 inc2

[centos]$ scp -r /backup/* 目标IP:/backup/

还原过程

  1. 预准备完成备份，此选项--apply-log-only 阻止回滚未完成的事务
[centos]$ xtrabackup --prepare --apply-log-only --target-dir=/backup/base
......
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1641730
InnoDB: Number of pools: 1
190710 17:34:37 completed OK!
  1. 合并第1次增量备份到完全备份，
[centos]$ xtrabackup --prepare --apply-log-only --target-dir=/backup/base --incremental-dir=/backup/inc1
......
190710 19:35:00 [00]        ...done
190710 19:35:00 [00] Copying /backup/inc1//xtrabackup_info to ./xtrabackup_info
190710 19:35:00 [00]        ...done
190710 19:35:00 completed OK!
  1. 合并第2次增量备份到完全备份：最后一次还原不需要加选项--apply-log-only
[centos]$ xtrabackup --prepare --target-dir=/backup/base --incremental-dir=/backup/inc2
......
190710 19:35:39 [00]        ...done
190710 19:35:39 [00] Copying /backup/inc2//xtrabackup_info to ./xtrabackup_info
190710 19:35:39 [00]        ...done
190710 19:35:39 completed OK!
  1. 在还原前先把服务停了，把数据库目录删除了
[centos]$ systemctl stop mariadb
#注意，不要把mysql这个目录给删除了 
[centos]$ rm -rf /var/lib/mysql/*
  1. 复制到数据库目录，注意数据库目录必须为空，MySQL服务不能启动
[centos]$ xtrabackup --copy-back --target-dir=/backup/base
.....
190710 19:38:57 [01]        ...done
190710 19:38:57 [01] Copying ./xtrabackup_info to /var/lib/mysql/xtrabackup_info
190710 19:38:57 [01]        ...done
190710 19:38:57 completed OK!
  1. 还原属性：
[centos]$ chown -R mysql:mysql /var/lib/mysql
  1. 启动服务：
[centos]$ systemctl start mariadb
  1. 看下数据在不在
MariaDB [hellodb]> select * from students;
......
|    28 | wang100       | 100 | F      |    NULL |      NULL |
|    29 | chen000       |   0 | F      |    NULL |      NULL |
|    30 | chen999       | 255 | F      |    NULL |      NULL |
|    31 | zhao222       | 222 | F      |    NULL |      NULL |
|    32 | li222         | 222 | F      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+

实验：分库备份

  1. 先放上结果，如果直接看懂了，就不用往下看了
[centos]$ mysql -e 'show databases' | grep -Evi '\<information_schema\>|\<performance_schema\>|\<Database\>' | sed -r 's#(^.*$)#mysqldump -B \1 --single-transaction --master-data=2 \| gzip \> \/root\/backup\/\1_`date +%F`.sql.gz#' | bash
  1. 下面就一步一步来分解，mysql是可以直接调用SQL语句的，如下
[centos]$ mysql -e 'show databases'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mysql              |
| performance_schema |
| test               |
+--------------------+
  1. 那么就可以把我们不想备份的数据库用grep过滤了
[centos]$ mysql -e 'show databases' | grep -Evi '\<information_schema\>|\<performance_schema\>|\<Database\>'
hellodb
mysql
test
  1. 既然我们过滤出我们要备份的数据库的名称了,我们可以用下面这语句来备份
[centos]$ mysqldump -B DB_NAME --single-transaction --master-data=2
  1. 有时候备份比较大的时候，可以压缩
#echo "xxx" | gzip >  file.gz
#利用这条就可以结合成下面这条
[centos]$ mysqldump -B DB_NAME --single-transaction --master-data=2 | gzip > /root/backup/DB_NAME_`date +%F`.sql.gz
  1. 如何把数据库名加上去呢，可以用sed，举个例子
[centos]$ echo "hellodb" | sed -r 's#(^.*$)#mysqldump -B \1 > \1.sql#'
mysqldump -B hellodb > hellodb.sql
  1. 好了，合并之
#分行显示
mysql -e 'show databases' 
    | grep -Evi '\<information_schema\>|\<performance_schema\>|\<Database\>' 
    | sed -r 's#(^.*$)#mysqldump -B \1 --single-transaction 
    --master-data=2 \| gzip \> \/root\/backup\/\1_`date +%F`.sql.gz#' 
    | bash
  1. 来试一下
[centos]$ ll /root/backup/
total 148 
-rw-r--r-- 1 root root   2111 Jul 10 20:42 hellodb_2019-07-10.sql.gz
-rw-r--r-- 1 root root 139818 Jul 10 20:42 mysql_2019-07-10.sql.gz
-rw-r--r-- 1 root root    602 Jul 10 20:42 test_2019-07-10.sql.gz

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注