删库跑路后mysql灾后恢复工作

mysql日志类型

1. 查询日志（general_log）
2. 慢查询日志（log_slow_queries）
3. 错误日志（log_error,log_warnings）
4. 二进制日志（binlog）
5. 中继日志（relay_log）
6. 事务日志（innodb_log）

查询日志（general_log）

默认关闭，因为查询日志默认收录几乎所有的查询sql语言记录（show select，就连），非常的大，慢查询日志有以下三个变量可以控制（可以在mysql中输入sql语句设置变量，也可以在mysql的配置文件my.cnf中配置）

general_log  = {ON|OFF}  //开启或者关闭general_log
general_log_file = HOSTNAME.log  //general_log的文件名是什么  
log_output = {FILE|TABLE|NONE} //我们的general_log是按照文件存储还是按照表存储，如果按照表存储存储在mysql库中  

MariaDB [(none)]> SHOW VARIABLES LIKE '%general%';
+------------------+------------------+
| Variable_name    | Value            |
+------------------+------------------+
| general_log      | OFF              |
| general_log_file | workstastion.log |
+------------------+------------------+

MariaDB [(none)]> SHOW VARIABLES LIKE 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
1 row in set (0.001 sec)

MariaDB [(none)]>

启用一下

MariaDB [(none)]> SET @@global.general_log = ON;
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> SHOW VARIABLES LIKE '%general%';
+------------------+------------------+
| Variable_name    | Value            |
+------------------+------------------+
| general_log      | ON               |
| general_log_file | workstastion.log |
+------------------+------------------+
2 rows in set (0.001 sec)

我们使用一个查询语句看看

MariaDB [(none)]> SELECT * FROM mydb.students WHERE stuid = 3;
+-------+------+------+--------+-------+
| stuid | name | age  | gender | major |
+-------+------+------+--------+-------+
|     3 | stu3 |   89 | M      | major |
+-------+------+------+--------+-------+
1 row in set (0.000 sec)

此时我们的相应mysql的目录中就会出现日志文件，日志文件名为workstation.log

root@workstastion:~# cd /var/lib/mysql/
root@workstastion:/var/lib/mysql# ls -l workstastion.log
-rw-rw---- 1 mysql mysql 453  2月  7 21:10 workstastion.log

我们tail以下这个文件的后几行

root@workstastion:/var/lib/mysql# tail workstastion.log
/usr/sbin/mysqld, Version: 10.3.25-MariaDB-0ubuntu1 (Ubuntu 20.10). started with:
Tcp port: 3306  Unix socket: /run/mysqld/mysqld.sock
Time                Id Command  Argument
210207 21:08:24   6336 Query    SHOW VARIABLES LIKE '%general%'
210207 21:10:21   6336 Query    SELECT * FROM mydb.students WHERE stuid = 3
210207 21:10:26   6336 Query    EXPLAIN SELECT * FROM mydb.students WHERE stuid = 3
210207 21:10:35   6336 Query    SELECT * FROM mydb.students WHERE stuid = 3
root@workstastion:/var/lib/mysql#

将general_log输出到table中

MariaDB [(none)]> SET @@global.log_output = 'table';
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> SHOW VARIABLES LIKE 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | TABLE |
+---------------+-------+
1 row in set (0.001 sec)

在mysql数据库中就出现相应的general_log表

MariaDB [(none)]> USE mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mysql]> SHOW TABLES;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| column_stats              |
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| gtid_slave_pos            |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| index_stats               |
| innodb_index_stats        |
| innodb_table_stats        |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| roles_mapping             |
| servers                   |
| slow_log                  |
| table_stats               |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| transaction_registry      |
| user                      |
+---------------------------+
31 rows in set (0.001 sec)

MariaDB [mysql]>
MariaDB [mysql]> SELECT * FROM mysql.general_log;
+----------------------------+---------------------------+-----------+-----------+--------------+----------------------------------------+
| event_time                 | user_host                 | thread_id | server_id | command_type | argument                               |
+----------------------------+---------------------------+-----------+-----------+--------------+----------------------------------------+
| 2021-02-07 21:53:34.665675 | root[root] @ localhost [] |      6337 |         1 | Query        | SHOW VARIABLES LIKE 'general_log_file' |
| 2021-02-07 21:53:52.732082 | root[root] @ localhost [] |      6337 |         1 | Query        | SHOW VARIABLES LIKE 'log_output'       |
| 2021-02-07 21:54:43.501779 | root[root] @ localhost [] |      6337 |         1 | Query        | SELECT DATABASE()                      |
| 2021-02-07 21:54:43.502896 | root[root] @ localhost [] |      6337 |         1 | Init DB      | mysql                                  |
| 2021-02-07 21:54:43.504121 | root[root] @ localhost [] |      6337 |         1 | Query        | show databases                         |
| 2021-02-07 21:54:43.513562 | root[root] @ localhost [] |      6337 |         1 | Query        | show tables                            |
| 2021-02-07 21:54:43.515056 | root[root] @ localhost [] |      6337 |         1 | Field List   | column_stats                           |
| 2021-02-07 21:54:43.515797 | root[root] @ localhost [] |      6337 |         1 | Field List   | columns_priv                           |
| 2021-02-07 21:54:43.516310 | root[root] @ localhost [] |      6337 |         1 | Field List   | db                                     |
| 2021-02-07 21:54:43.516842 | root[root] @ localhost [] |      6337 |         1 | Field List   | event                                  |
| 2021-02-07 21:54:43.517311 | root[root] @ localhost [] |      6337 |         1 | Field List   | func                                   |
| 2021-02-07 21:54:43.517550 | root[root] @ localhost [] |      6337 |         1 | Field List   | general_log                            |
| 2021-02-07 21:54:43.517864 | root[root] @ localhost [] |      6337 |         1 | Field List   | gtid_slave_pos                         |
| 2021-02-07 21:54:43.518190 | root[root] @ localhost [] |      6337 |         1 | Field List   | help_category                          |
| 2021-02-07 21:54:43.518561 | root[root] @ localhost [] |      6337 |         1 | Field List   | help_keyword                           |
| 2021-02-07 21:54:43.518803 | root[root] @ localhost [] |      6337 |         1 | Field List   | help_relation                          |
| 2021-02-07 21:54:43.519305 | root[root] @ localhost [] |      6337 |         1 | Field List   | help_topic                             |
| 2021-02-07 21:54:43.519794 | root[root] @ localhost [] |      6337 |         1 | Field List   | host                                   |
| 2021-02-07 21:54:43.520399 | root[root] @ localhost [] |      6337 |         1 | Field List   | index_stats                            |
| 2021-02-07 21:54:43.520887 | root[root] @ localhost [] |      6337 |         1 | Field List   | innodb_index_stats                     |
| 2021-02-07 21:54:43.521438 | root[root] @ localhost [] |      6337 |         1 | Field List   | innodb_table_stats                     |
| 2021-02-07 21:54:43.521919 | root[root] @ localhost [] |      6337 |         1 | Field List   | plugin                                 |
| 2021-02-07 21:54:43.522193 | root[root] @ localhost [] |      6337 |         1 | Field List   | proc                                   |
| 2021-02-07 21:54:43.522921 | root[root] @ localhost [] |      6337 |         1 | Field List   | procs_priv                             |
| 2021-02-07 21:54:43.523390 | root[root] @ localhost [] |      6337 |         1 | Field List   | proxies_priv                           |
| 2021-02-07 21:54:43.523845 | root[root] @ localhost [] |      6337 |         1 | Field List   | roles_mapping                          |
| 2021-02-07 21:54:43.524141 | root[root] @ localhost [] |      6337 |         1 | Field List   | servers                                |
| 2021-02-07 21:54:43.524484 | root[root] @ localhost [] |      6337 |         1 | Field List   | slow_log                               |
| 2021-02-07 21:54:43.525089 | root[root] @ localhost [] |      6337 |         1 | Field List   | table_stats                            |
| 2021-02-07 21:54:43.525376 | root[root] @ localhost [] |      6337 |         1 | Field List   | tables_priv                            |
| 2021-02-07 21:54:43.525779 | root[root] @ localhost [] |      6337 |         1 | Field List   | time_zone                              |
| 2021-02-07 21:54:43.526067 | root[root] @ localhost [] |      6337 |         1 | Field List   | time_zone_leap_second                  |
| 2021-02-07 21:54:43.526351 | root[root] @ localhost [] |      6337 |         1 | Field List   | time_zone_name                         |
| 2021-02-07 21:54:43.526597 | root[root] @ localhost [] |      6337 |         1 | Field List   | time_zone_transition                   |
| 2021-02-07 21:54:43.526945 | root[root] @ localhost [] |      6337 |         1 | Field List   | time_zone_transition_type              |
| 2021-02-07 21:54:43.527304 | root[root] @ localhost [] |      6337 |         1 | Field List   | transaction_registry                   |
| 2021-02-07 21:54:43.528018 | root[root] @ localhost [] |      6337 |         1 | Field List   | user                                   |
| 2021-02-07 21:54:49.046213 | root[root] @ localhost [] |      6337 |         1 | Query        | SHOW TABALES                           |
| 2021-02-07 21:54:52.061429 | root[root] @ localhost [] |      6337 |         1 | Query        | SHOW TABLES                            |
| 2021-02-07 21:55:51.207936 | root[root] @ localhost [] |      6337 |         1 | Query        | SELECT * FROM mysql.general.log        |
| 2021-02-07 21:55:58.440534 | root[root] @ localhost [] |      6337 |         1 | Query        | SELECT * FROM mysql.general_log        |
+----------------------------+---------------------------+-----------+-----------+--------------+----------------------------------------+
41 rows in set (0.001 sec)

MariaDB [mysql]>

关闭general_log

MariaDB [mysql]> SET @@global.general_log = OFF;
Query OK, 0 rows affected (0.000 sec)

慢查询日志（log_slow_queries）

一般要开启，我们如果有一些查询半天没有结果，这些语句都是存储在慢查询日志中，我们要根据这个日志查明是什么导致查询效率下降（或许锁，或许数据库非常大），那么查询时间大于什么会出发慢查询日志呢，我们可以参数设置

long_query_time //大于这个参数设定的值触发慢查询日志，一般十秒

MariaDB [mysql]> SELECT @@global.long_query_time;
+--------------------------+
| @@global.long_query_time |
+--------------------------+
|                10.000000 |
+--------------------------+
1 row in set (0.000 sec)

慢查询还有一些非常重要的参数

log_slow_filter //指明因为那些问题导致慢查询，只有因为这些问题导致的慢查询才会被记录  
log_slow_queries //关闭开启慢查询 OFF/ON
log_output = {file|table|none} //这个参数和general日志一样
slow_query_log_file //慢查询日志的类型   
log_slow_verbosity //定义慢查询的级别，越详细就越损耗io

错误日志（log_error,log_warnings）

记录以下信息

1. mysqld启动关闭的过程信息
2. mysqld运行中产生的错误信息
3. event scheduler运行时产生的信息
4. 主从复制的架构中，从服务器复制线程启动时产生的日志

重要的参数

log_error //指定错位日志路径默认/var/log/mariadb/mariadb.log

二进制日志（binlog）

二进制日志和中继日志在mysql主从复制中有用，mysql主从复制非常的简单，主服务器接收客户端发送的写/改变请求，写的数据放到主服务器的存储中，写的sql语句放置于二进制日志binlog中，此时从mysql服务器不断的从主mysql的binlog中复制到自己的中继日志中，从服务器再从中继日志读取sql语句执行。

二进制日志记录有3种

1. 语句 （statement）

   这个是语句执行的结果，一般非常的多，且时效性不是很高，但是速度快，新版本的mysql可以按照时间戳去记录，按照时间戳运行会减少非常多的问题

2. 行（row）

   就是sql语句，最节约资源，但是慢

3. 混编模式（mixed）

   当语句记录不准确就记录行

以上可以根据binlog_format = {STATEMENT|ROW|MIXED}设置

MariaDB [mysql]> SHOW VARIABLES LIKE 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
1 row in set (0.002 sec)

MariaDB [mysql]>

二进制日志文件因为需要记录的东西非常的多，但是每一个日志的大小固定，所以每当我们写的多，或者做了日志滚动flush log二进制日志就变得非常的分散，所以他的形式就像XXX.00001,XXX.00002…记录最近的操作是编号靠后的二进制日志，

我们先开启二进制日志，二进制日志开启和其他的不一样，他必须要在mysql的配置文件中输入配置再重启服务开启如下

root@workstastion:~# vim /etc/mysql/my.cnf
[mysqld] 
log_bin = master_log 

重启服务

root@workstastion:~# systemctl restart mariadb

重新进入mysql发现已经开启了二进制日志

MariaDB [(none)]> SHOW VARIABLES LIKE 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.001 sec)

我们可以查看所有的二进制文件

MariaDB [(none)]> SHOW MASTER LOGS;
+-------------------+-----------+
| Log_name          | File_size |
+-------------------+-----------+
| master_log.000001 |       329 |
+-------------------+-----------+
1 row in set (0.009 sec)

MariaDB [(none)]>

查看当前使用的二进制日志文件和其属性，发现用的xxx.00001

MariaDB [(none)]> SHOW MASTER  STATUS;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master_log.000001 |      329 |              |                  |
+-------------------+----------+--------------+------------------+
1 row in set (0.000 sec)

MariaDB [(none)]>

我们做一次日志滚动 ，看到新创建了一个master_log.000002，并且当前使用的是这个二进制日志

MariaDB [(none)]> FLUSH LOGS;
Query OK, 0 rows affected (0.013 sec)

MariaDB [(none)]> SHOW MASTER LOGS;
+-------------------+-----------+
| Log_name          | File_size |
+-------------------+-----------+
| master_log.000001 |       377 |
| master_log.000002 |       373 |
+-------------------+-----------+
2 rows in set (0.000 sec)

MariaDB [(none)]> SHOW MASTER  STATUS;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master_log.000002 |      373 |              |                  |
+-------------------+----------+--------------+------------------+
1 row in set (0.000 sec)

MariaDB [(none)]>

首先我们一个请求就是一个event，在二进制日志中position（字节）代表一个event的结束，也代表下一个event的开始
我们做一次数据改变的sql语言(因为查询语句不会记录再binlog中)再看这个position，已经改变，因为我们向binlog插入了一个event

MariaDB [(none)]> DELETE FROM mydb.students WHERE stuid = 1086;
Query OK, 1 row affected (0.012 sec)

MariaDB [(none)]> SHOW MASTER  STATUS;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master_log.000002 |      553 |              |                  |
+-------------------+----------+--------------+------------------+
1 row in set (0.000 sec)

MariaDB [(none)]>

我们可以看一下mster_log.000002的event有那些内容

MariaDB [(none)]> SHOW BINLOG EVENTS IN 'master_log.000002';
+-------------------+-----+-------------------+-----------+-------------+---------------------------------------------------------+
| Log_name          | Pos | Event_type        | Server_id | End_log_pos | Info                                                    |
+-------------------+-----+-------------------+-----------+-------------+---------------------------------------------------------+
| master_log.000002 |   4 | Format_desc       |         1 |         256 | Server ver: 10.3.25-MariaDB-0ubuntu1-log, Binlog ver: 4 |
| master_log.000002 | 256 | Gtid_list         |         1 |         285 | []                                                      |
| master_log.000002 | 285 | Binlog_checkpoint |         1 |         329 | master_log.000001                                       |
| master_log.000002 | 329 | Binlog_checkpoint |         1 |         373 | master_log.000002                                       |
| master_log.000002 | 373 | Gtid              |         1 |         415 | BEGIN GTID 0-1-1                                        |
| master_log.000002 | 415 | Query             |         1 |         522 | DELETE FROM mydb.students WHERE stuid = 1086            |
| master_log.000002 | 522 | Xid               |         1 |         553 | COMMIT /* xid=68 */                                     |
+-------------------+-----+-------------------+-----------+-------------+---------------------------------------------------------+
7 rows in set (0.000 sec)

MariaDB [(none)]>

以上是我们手动的日志滚动，我们要让二进制日志的大小超过一定的数值进行自动的滚动，可以设置每个二进制日志固定大小

max_binlog_size = XXXXXX单位字节

我们都知道mysql是将数据从从盘中拿到内存，再在内存中修改，修改完毕再放入磁盘，但是如果在放入磁盘之前崩了就非常尴尬，数据丢失，我们之前为了保证redolog不被就是就有相应的参数只要下发到内存立马进行写磁盘操作，这里binlog也有相应的参数，当写的操作到了内存中立马同步磁盘

sync_binlog = {1|0} //

我们知道一个线程操作一个session，如果某个session是测试，我们不希望记录到binlog中，我们可以在测试这个会话级别关闭binlog

SET @@session.sql_log_bin = OFF;

对于binlog我们可以在linux中根据mysqlbinlog进行查看 --no-defaults是跳过字符

root@workstastion:/var/lib/mysql# mysqlbinlog --no-defaults master_log.000002
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#210207 22:49:20 server id 1  end_log_pos 256 CRC32 0xda34be4e  Start: binlog v 4, server v 10.3.25-MariaDB-0ubuntu1-log created 210207 22:49:20
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
8P0fYA8BAAAA/AAAAAABAAABAAQAMTAuMy4yNS1NYXJpYURCLTB1YnVudHUxLWxvZwAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAA5AAEGggAAAAICAgCAAAACgoKAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAEEwQADQgICAoKCgFOvjTa
'/*!*/;
# at 256
#210207 22:49:20 server id 1  end_log_pos 285 CRC32 0xf5f22622  Gtid list []
# at 285
#210207 22:49:20 server id 1  end_log_pos 329 CRC32 0xeba20898  Binlog checkpoint master_log.000001
# at 329
#210207 22:49:20 server id 1  end_log_pos 373 CRC32 0x33f51912  Binlog checkpoint master_log.000002
# at 373
#210207 22:55:51 server id 1  end_log_pos 415 CRC32 0xe3a93fd9  GTID 0-1-1 trans
/*!100101 SET @@session.skip_parallel_replication=0*//*!*/;
/*!100001 SET @@session.gtid_domain_id=0*//*!*/;
/*!100001 SET @@session.server_id=1*//*!*/;
/*!100001 SET @@session.gtid_seq_no=1*//*!*/;
START TRANSACTION
/*!*/;
# at 415
#210207 22:55:51 server id 1  end_log_pos 522 CRC32 0x00c1c890  Query   thread_id=37    exec_time=0     error_code=0
SET TIMESTAMP=1612709751/*!*/;
SET @@session.pseudo_thread_id=37/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/;
SET @@session.sql_mode=1411383296/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=45/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
DELETE FROM mydb.students WHERE stuid = 1086
/*!*/;
# at 522
#210207 22:55:51 server id 1  end_log_pos 553 CRC32 0xb49af82e  Xid = 68
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
root@workstastion:/var/lib/mysql#

简单解读一下这个日志的信息，首先一个写sql是一个event，at后面都是一个事件包括本事件元数据的内容，at后面以#开头的都是元数据，记录时间之类的
mysqlbinlog也可以远程连接别的数据库但是要开启主从复制的权限
二进制日志是我们备份恢复的关键数据，所以二进制日志非常重要！

中继日志（relay_log）

同上

事务日志（innodb_log）

保证事务提交后能保持ACID的原则,包含redo log和undo log，具体看上一个blog

数据备份与恢复

了解数据备份之前先了解一下时间戳，时间戳是一个 signed int类型的数字，它记录了从1970年1月1日到现在最近一次更改文件中间一共经历了多少秒，这个最近一次更改文件时间是按照格林威治时间计算的，所以如果中国所处于的东八区将减去相应的秒数，美国处于的西几区也将加上相应的秒数，所以，时间戳是一致的，不论你在那个时区

备份

首先备份建议在业务低峰期进行，可以用脚本检测然后备份
备份级别
备份手段有三种

1. 冷备份

   直接下线业务，不允许都和写，直接进行备份

2. 温备份

   不下线业务，但是不支持写，只能读，在此基础上进行备份

3. 热备份

   不下线业务可以读和写，在此基础上进行备份

备份的类型

1. 物理备份

复制数据文件进行备份,也就是基于文件系统的接口进行备份，直接针对原式数据

2. 逻辑备份

从数据库导出数据另存在一个或者多个文件中，这个是基于数据库接口进行备份,针对数据库

为什么备份？
备份除了防止硬件，软件故障，或者认为操作事务，再或者黑客攻击导致数据被加密，还可以测试，当我们要测试的时候，根据备份可以模拟在某一时刻的环境进行测试

从备份的数据集范围来说

1. 完全备份

   分为两类

   1. 增量备份 :增量备份和差异备份的区别不是非常大，增量备份是根据时间来说的，他备份上一次完全备份或者增量备份后变化的数据，所以每一次备份非常的小，但是恢复起来的时候增量备份的数据文件非常多，要把所有的文件合在一起，所以对恢复来说不是很友好
   2. 差异备份 :差异备份是根据上一次完全备份（记住是完全备份没有差异备份），以来变化的数据都备份下来，所以恢复只用恢复最后一次完全备份加上最后一次差异备份的变化部分，一合并就行，但是每一次备份的数据会越来越大
   3. 注意但是我们的备份是按照天来算的，最差的情况损失一天的数据(在第二天要备份的时候正好坏掉了)，这一天的数据可以根据binlog来进行恢复

2. 部分备份

   顾名思义

备份注意事项

1. 我们能够容忍丢失多少的数据

   决定我们备份的资金人手投入

2. 数据恢复需要多少时间

   不同的存储设备的IO新能不一样，多根据备份做恢复演练，恢复最好不要用工具实现

3. 需要恢复那些数据

备份什么?

1. 数据
2. 二进制日志,innodb事务日志 （有的没有commit我们是不是需要undo）
3. 代码(存储过程，存储函数，触发器，事件调度器)
4. 服务器配置文件

备份工具
mysqldump

mysql自带的备份工具，逻辑备份，支持热备，温备，完全备份，部分备份，备份速度慢，恢复速度慢，但是免费，（Innodb支持热备，myisam支持温备）

mysqlhotcopy

其实是一个mysql冷备工具…

select

select其实可以将查出来的数据导入到一个外部文件中

select cluase INTO OUTFILE 'FILE_NAME'

然后我们恢复的时候先创建一个表

CREATE TABLE; //需要自己创建表结构非常麻烦...

在导入数据

LOAD DATA 

xtrabackup

专业备份工具percona（编写innodb的公司）提供，支持完全备份，部分备份，增量备份，差异备份，支持流式远程备份，支持并发，支持压缩，支持热备（innodb），开源

mysqldump

简介
首先mysqldump是mysql逻辑备份工具，是基于mysql协议的备份工具，安装完mysql后自带的工具，支持完全备份和部分备份，但是不支持增量和差异备份，它的逻辑是使用全量备份加上binlog进行还原，binlog来代表增量
简单使用
mysqldump的三种常用备份使用
备份单个库或者一部分表

root@workstastion:~# mysqldump mydb  > mydb-fullbackup-$(date +%F-%H-%M-%S)
//备份mydb库，因为mysqldump会把内容都转化成字符串，所以对精度要求比较高的字符类型不是很友好

导入

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 41
Server version: 10.3.25-MariaDB-0ubuntu1-log Ubuntu 20.10

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]> CREATE DATABASE testdb;
Query OK, 1 row affected (0.010 sec)

MariaDB [(none)]> exit
Bye
root@workstastion:~# mysql testdb < mydb-fullbackup-2021-02-12-21-01-04
root@workstastion:~#

备份多个库

root@workstastion:~# mysqldump --databases mydb mysql > mydb-fullbackup-$(date +%F-%H-%M-%S) //备份2个库
//加上--databases会加上create database语句，也就是恢复的时候直接恢复，不用手动创建

备份所有库

mysqldump [options] --all-databases

我们前面说到温备，其实mysqldump就可以实现，只需要加上-x或者-l即可，
-x表示备份的时候锁定所有被选择的库，被选择的库被锁定后不能写也不能读
-l表示备份的时候如果有多个库，在备份这个库的时候只锁住这个库，下一个还没开始备份的库先不锁

热备可以通过--single-transaction来实现，当然只有innodb可以，因为它支持事务，但是myisam不行，因为这个热备是通过事务进行的，先创建一个事务然后进行备份，再提交事务，因为事务有4个隔离级别，可以在一定的程度上实现热备的效果,所以极其建议同一个存储引擎的表存储在一个数据库中，因为备份的时候一个数据库不同的表非常麻烦

 root@workstastion:~# mysqldump --single-transaction mydb

其他关键选项
-R备份数据库的存储过程和存储函数
--triggers备份指定库的触发器
-E备份指定库的事件

存储过程就像一个函数一样，达到代码复用的效果，

CREATE PROCEDURE PROCEDURE_NAME
BEGIN 
SQL_STAT
END

我们调用这个存储过程可以直接call PROCEDURE_NAME()即可

触发器就是表面意思，就是当我们的触发器被预定义的某个事件在某个表上触发后，我们执行什么sql语句，比如我们的触发器规则是对某个表只要进行insert操作，在这个操作之前或者之后（BEFORE和AFTER设定）执行什么sql语句

这里我们来一次实操，恢复mysql数据库

实验

先查看一下数据库

MariaDB [(none)]> USE mydb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mydb]> SHOW TABLES;
+----------------+
| Tables_in_mydb |
+----------------+
| students       |
| tbl1           |
+----------------+
2 rows in set (0.000 sec)

MariaDB [mydb]>

再看一下bin_log开启了没有

MariaDB [(none)]> SHOW VARIABLES LIKE "log_bin";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.001 sec)

我们将mydb数据库进行常规的备份(完全备份)

root@workstastion:~# mysqldump  --all-databases -x -R -E --triggers  --flush-logs --master-data=2 > ./alldata-$(date +%F
-%H-%M-%S)

--master-data是二进制日志相关的选项用于记录备份的一刻你处于什么文件的什么位置中，如果等于1，将会在恢复数据一开始就切换到一个这个binlog中并且标明其位子，但是这个选项是用于从服务器中的，我们这里不需要，所以2，注释这一行，但是我们可以看到备份的一刻处于什么位置所以为了后面用binlog恢复增量数据时候提供binlog的起始位子
--flush-logs代表我们在备份的时候进行表锁定锁定完后立马刷新binlog，用新的binlog记录我们的写入，前面说了--master-data = 2记录了我们后面恢复增量数据的开始位子，这里就是我们恢复增量数据的结束位子

因为binlog记录所有的写操作，所以非常的庞大（包括完全备份和增量备份的数据），如果我们的数据完全备份后数据库又处理了多个写操作(已经被记录在binlog中)后挂机，我们用最后一次完全备份恢复到最后一次备份的状态，但是中间的几次写操作就可以通过binlog恢复，--master-data = 2记录了备份那一刻binlog的位子（也是我们增量数据开始的位置,我们恢复增量数据从这个位置开始），--flush-logs在备份之前刷新binlog，让我们在备份的时候进行写操作写入一个新的binlog文件中，也就是我们的增量数据都在这个新的binlog中，为什么不直接都用binlog恢复呢？而是用mysqldump进行恢复，因为binlog实际上是再执行一遍sql语句，效率非常低，而mysqldump恢复直接恢复数据效率比binlog高

此时我们的完全备份已经有了，我们再进行写操作，模拟增量数据

MariaDB [mydb]> CREATE TABLE test_table (id INT UNSIGNED AUTO_INCREMENT  PRIMARY KEY,name VARCHAR(20));
Query OK, 0 rows affected (0.014 sec)
MariaDB [mydb]> INSERT INTO test_table(name) VALUE ("ou yangfeng"),("yang guo");
Query OK, 2 rows affected (0.002 sec)
Records: 2  Duplicates: 0  Warnings: 0
MariaDB [mydb]> SELECT * FROM test_table;
+----+-------------+
| id | name        |
+----+-------------+
|  1 | ou yangfeng |
|  2 | yang guo    |
+----+-------------+
2 rows in set (0.000 sec)

MariaDB [mydb]>

此时我们进行删库跑路

MariaDB [(none)]> DROP DATABASE mysql;
Query OK, 31 rows affected (0.019 sec)

MariaDB [(none)]> DROP DATABASE mydb;
Query OK, 3 rows affected, 2 warnings (0.014 sec)

这个时候我们开始恢复，先看我们的最后一次完全备份在binlog的那个位置，之前mysqldump完全备份的时候--master-data = 2起了作用

root@workstastion:~# less alldata-2021-02-12-23-37-26
......
......
......
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE='master_log.000005', MASTER_LOG_POS=387;
......
......
......

在此处发现 我们完全备份的时候在master_log.000005中因为我们加上了--flush-logs参数所以不用疑问所有的新增写操作都在这个binlog中（包括我们的删库跑路操作，如果写的东西太多他会往后再创建一个文件master_log.000006，如果有这个文件我们也要恢复）

此时关闭我们的binlog因为恢复的时候会产生大量的binlog，导致我们的增量数据不好定位结尾

root@workstastion:~# vim /etc/mysql/my.cnf
[mysqld]
#log_bin = master_log

重启服务报错。。。

root@workstastion:~# systemctl restart mariadb
Job for mariadb.service failed because the control process exited with error code.
See "systemctl status mariadb.service" and "journalctl -xe" for details.

经过分析日志发现mysql数据库被我们删了，，，这个数据库保存的账号密码验证表user等一类，所以导致我们开启服务的时候检查不到这个表加载不了权限导致服务重启失败。。。
这里只有一个办法，更改mariadb在systemd中的启动服务命令，在前面加上2个选项--skip-grant-tables（跳过授权表，一旦跳过mysql对你无限制所言）--skip-networking（禁止远程网络登陆，因为我们已经跳过了授权表任何人都有root权限，一旦别人网络登录，mysql将非常危险）,这样跳过重启服务授权表检查环节，开启服务，服务开启成功！

root@workstastion:~# vim /etc/systemd/system/multi-user.target.wants/mariadb.service
ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION --skip-grant-tables --skip-networking
root@workstastion:~# systemctl daemon-reload
root@workstastion:~# systemctl start mariadb
root@workstastion:~#

解决了服务的问题后我们这个时候可以看一下binlog，把我们删库跑路的写操作找出来重定向成我们完全备份的格式

root@workstastion:~# root@workstastion:~# mysqlbinlog --no-defaults /var/lib/mysql/master_log.000005  | less
# at 817
#210213  0:28:53 server id 1  end_log_pos 859 CRC32 0xc069a760  GTID 0-1-15 ddl
/*!100001 SET @@session.gtid_seq_no=15*//*!*/;
# at 859
#210213  0:28:53 server id 1  end_log_pos 946 CRC32 0x93240a1a  Query   thread_id=52    exec_time=1     error_code=0
SET TIMESTAMP=1613147333/*!*/;
SET @@session.pseudo_thread_id=52/*!*/;
DROP DATABASE mysql
/*!*/;
# at 946
#210213  0:28:57 server id 1  end_log_pos 988 CRC32 0x1f1fc062  GTID 0-1-16 ddl
/*!100001 SET @@session.gtid_seq_no=16*//*!*/;
# at 988
#210213  0:28:57 server id 1  end_log_pos 1073 CRC32 0x7fd3d68f         Query   thread_id=52    exec_time=0     error_code=1146
SET TIMESTAMP=1613147337/*!*/;
DROP DATABASE mydb
/*!*/;
# at 1073
#210213  0:38:02 server id 1  end_log_pos 1096 CRC32 0x53ddeb65         Stop
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
~
(END)                                              

drop命令是从859这个位置开始的，我们只需要恢复到859这个位置之前就行,用--start-positon=POSITION和--stop-position=POSITION可以过滤,stop-position输入859就行了，因为他表示过滤出这个字节之前的数据

root@workstastion:~# mysqlbinlog --no-defaults --start-position=1  --stop-position=859 /var/lib/mysql/master_log.000005  > alldata-$(date +%F-%H-%M-%S)

然后我们开始恢复

root@workstastion:~# mysql < alldata-2021-02-12-23-37-26
root@workstastion:~# mysql < alldata-2021-02-13-01-13-44

再看一下数据库恢复了没有

root@workstastion:~# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 13
Server version: 10.3.25-MariaDB-0ubuntu1 Ubuntu 20.10

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| mysql              |
| mytest             |
| performance_schema |
| testdb             |
+--------------------+
6 rows in set (0.001 sec)

MariaDB [(none)]> USE mydb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mydb]> SHOW TABLES;
+----------------+
| Tables_in_mydb |
+----------------+
| students       |
| tbl1           |
| test_table     |
+----------------+
3 rows in set (0.000 sec)

MariaDB [mydb]> SELECT * FROM test_table;
+----+-------------+
| id | name        |
+----+-------------+
|  1 | ou yangfeng |
|  2 | yang guo    |
+----+-------------+
2 rows in set (0.000 sec)

MariaDB [mydb]>

数据已经完全恢复！我们重新开启binlog和关闭服务启动时设置跳过权限检查的状态

root@workstastion:~# vim /etc/mysql/my.cnf
[mysqld]
log_bin = master_log 
root@workstastion:~# vim /etc/systemd/system/multi-user.target.wants/mariadb.service
ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION
root@workstastion:~# systemctl daemon-reload
root@workstastion:~# systemctl restart mariadb

xtrabackup

TODO