05_mysql备份方案
1.为什么要数据备份
- 保护服务器数据安全
- 维护公司运维资产7*24小时运转
企业丢失了数据，就等于失去了商机，客户，产品，甚至倒闭
在各种各样的数据中，数据库的数据是核心中的核心，当然其他各式各样的如静态文件数据，也很重要，也会通过其他的备份方式来保证安全
2.备份恢复的职责
- 备份，恢复策略备周期，备份工作，备份方式，数据恢复方式
- 日常备份检查日志，备份数据
- 定期恢复数据演练
- 数据故障时，利用现有的资源，快速恢复
- 数据迁移，数据库升级
3.备份工具
- 物理备份，直接备份数据库里所有数据甚至是整个磁盘
- 逻辑备份，将数据从数据库导出，并将导出的数据进行存档管理
逻辑备份
把数据库，表，以SQL语句的形式，输出为文件备份过程，叫做逻辑备份
这种方式备份效率不高，以SQL导出，在海量的数据下，备份时间太长了。
就聊到mysqldump的备份方式
4.mysqldump备份
mysqldump备份语法
Mysqldump -u用户名 -p密码 参数 数据库名 > 备份数据文件
mysql自带备份工具，实现本地备份，远程备份
mysqldump命令备份的过程中，实际上是吧数据库，表，以sql语句的形式输出为文件的备份过程，这个就叫做逻辑备份
但是这种备份方式效率不高
还会有其他方案
4.1mysqldump连接参数
-u mysql用户名
-p mysql用户密码
-S mysql本地socket文件
-h 指定主机地址
-P 指定mysql端口
4.2 mysqldump备份参数
可以利用如下语句，实现数据库的数据、结构、很实用的技巧。
全量备份
--all--database，-A 转储所有数据库中的所有表。[root@db-51 ~]#mysqldump -uroot -pwww.yuchaoit.cn -A > /mysql_backup/all_db.sql
mysqldump: [Warning] Using a password on the command line in
指定数据库
---database，-B转储几个数据库。通常情况，mysqldump将命令行中的第1个名字参量看作数据库名，后面的名看作表名。
使用该选项，它将所有名字参量看作数据库名。备份命令，尽量携带-B参数，会让sql更加完整-B可以跟上多个数据库名，同时备份多个库尽量结合gzip命令压缩指定备份库，以及所有数据[root@db-51 /opt]#mysqldump -uroot -pwww.yuchaoit.cn -B world employees > /mysql_backup/world_employess.sql
mysqldump: [Warning] Using a password on the command line i
- 检查
[root@db-51 /opt]#ll /mysql_backup/ -h
total 323M
-rw-r--r-- 1 root root 162M Jul 21 17:37 all_db.sql
-rw-r--r-- 1 root root 161M Jul 21 17:36 world_employess.sql
备份单个数据表
这里不能加上-B参数，这里指定数据库的作用
单独指定某个table
# 备份salaries工资表root@db-51 /opt]#mysqldump -uroot -pwww.yuchaoit.cn employees salaries > /mysql_backup/employees_salaries.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@db-51 /opt]#
[root@db-51 /opt]#ll /mysql_backup/ -h
total 433M
-rw-r--r-- 1 root root 162M Jul 21 17:37 all_db.sql
-rw-r--r-- 1 root root 111M Jul 21 17:40 employees_salaries.sql
-rw-r--r-- 1 root root 161M Jul 21 17:36 world_employess.sql
备份多个表
# 备份库下的多个表
[root@db-51 /opt]#mysqldump -uroot -pwww.yuchaoit.cn world city country > /mysql_backup/world_city_country.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@db-51 /opt]#
[root@db-51 /opt]#ll /mysql_backup/ -h
total 434M
-rw-r--r-- 1 root root 162M Jul 21 17:37 all_db.sql
-rw-r--r-- 1 root root 111M Jul 21 17:40 employees_salaries.sql
-rw-r--r-- 1 root root 214K Jul 21 17:48 world_city_country.sql
-rw-r--r-- 1 root root 161M Jul 21 17:36 world_employess.sql
通过sql可以看出，整个过程是
- 如果该表存在，则删除
- 创建table
- 锁表，防止数据写入
- 数据插入
- 解锁表
grep -Ev '#|\*|--|^$' /mysql_backup/world_city_country.sql
5.binlog日志
binlog是mysql一大重点，Binlog是一个二进制格式的文件，用于记录用户对数据库更新的SQL语句信息
例如更改数据库库表和更改表内容的SQL语句都会记录到binlog里，但是对库表等内容的查询则不会记录到日志中。
记录
DML，insert update，delete
DDL，create drop，alter，truncate
DCL，grant revoke
binlog的作用
当有数据写入到数据库时，还会同时把更新的SQL语句写入到对应的binlog文件里，这个文件就是上文所说的binlog文件。
配置binlog
mysql> select @@log_bin;
+-----------+
| @@log_bin |
+-----------+
| 0 |
+-----------+
1 row in set (0.00 sec)未开启
查看mysql关于bin_log的变量参数
mysql> show variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin | OFF |
| log_bin_basename | |
| log_bin_index | |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+-------+
6 rows in set (0.00 sec)
查看binlog的配置信息
mysql> select @@log_bin_basename;
+--------------------+
| @@log_bin_basename |
+--------------------+
| NULL |
+--------------------+
1 row in set (0.00 sec)mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 0 |
+-------------+
1 row in set (0.01 sec)
开启binlog
[root@db-51 /mysql_backup]#cat /etc/my.cnf
[mysqld]
server_id=51 # 主机id，必须要区别于其他机器
# 开启binlog的参数，以及日志文件路径，最终格式是如 mysql-bin.000001
log_bin=/www.yuchaoit.cn/mysql_3306/logs/mysql-bin
character_set_server=utf8mb4
port=3306
user=mysql
basedir=/opt/mysql
datadir=/www.yuchaoit.cn/mysql_3306
socket=/tmp/mysql.sock[mysql]
socket=/tmp/mysql.sock
[root@db-51 /mysql_backup]#
重启，再次查看配置
[root@db-51 /mysql_backup]#mkdir /www.yuchaoit.cn/mysql_3306/logs/[root@db-51 /mysql_backup]#chown -R mysql.mysql /www.yuchaoit.cn/[root@db-51 /mysql_backup]#systemctl restart mysqld[root@db-51 /mysql_backup]#mysql -uroot -pwww.yuchaoit.cn
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.28-log MySQL Community Server (GPL)Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------------------------+
| log_bin | ON |
| log_bin_basename | /www.yuchaoit.cn/mysql_3306/logs/mysql-bin |
| log_bin_index | /www.yuchaoit.cn/mysql_3306/logs/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+--------------------------------------------------+
6 rows in set (0.00 sec)
查看日志路径
[root@db-51 /mysql_backup]#ls /www.yuchaoit.cn/mysql_3306/logs/
mysql-bin.000001 mysql-bin.index[root@db-51 /mysql_backup]#cat /www.yuchaoit.cn/mysql_3306/logs/mysql-bin.index
/www.yuchaoit.cn/mysql_3306/logs/mysql-bin.000001
binlog内容的格式
事件event记录方式
1. 事件描述
时间戳
server_id
加密方式
开始位置 start_pos
结束位置 end_pos2.事件内容
修改类的操作，SQL语句，数据行的变化重点，使用binlog主要关注
start_pos
end_pos
事件内容
二进制日志事件内容格式
mysql> show variables like '%binlog%';
+--------------------------------------------+----------------------+
| Variable_name | Value |
+--------------------------------------------+----------------------+
| binlog_cache_size | 32768 |
| binlog_checksum | CRC32 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_error_action | ABORT_SERVER |
| binlog_format | ROW |
| binlog_group_commit_sync_delay | 0 |
| binlog_group_commit_sync_no_delay_count | 0 |
| binlog_gtid_simple_recovery | ON |
| binlog_max_flush_queue_time | 0 |
| binlog_order_commits | ON |
| binlog_row_image | FULL |
| binlog_rows_query_log_events | OFF |
| binlog_stmt_cache_size | 32768 |
| binlog_transaction_dependency_history_size | 25000 |
| binlog_transaction_dependency_tracking | COMMIT_ORDER |
| innodb_api_enable_binlog | OFF |
| innodb_locks_unsafe_for_binlog | OFF |
| log_statements_unsafe_for_binlog | ON |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| sync_binlog | 1 |
+--------------------------------------------+----------------------+
22 rows in set (0.01 sec)
这里看到| binlog_format 是ROW
对于DDL、DCL语句，直接将SQL本身记录到binlog中
对于DML : insert、update、delete 受到binlog_format参数控制。SBR : Statement : 语句模式。之前版本，默认模式
RBR : ROW : 行记录模式。5.7以后，默认模式
MBR : mixed : 混合模式。
查看binlog日志文件情况
查看所有日志文件的信息，二进制日志
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 154 |
+------------------+-----------+
1 row in set (0.00 sec)
刷新新日志文件
了解该命令即可，不能随便执行。。
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 201 |
| mysql-bin.000002 | 201 |
| mysql-bin.000003 | 154 |
+------------------+-----------+
3 rows in set (0.00 sec)mysql>
查看当前mysql用哪个日志文件
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
模拟binlog记录
1.主动写入新数据mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)mysql> create database chaoge_linux;
Query OK, 1 row affected (0.00 sec)mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 337 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)2.写入表数据
mysql> create table chaoge_linux.students(id int);
Query OK, 0 rows affected (0.00 sec)mysql> insert into chaoge_linux.students values(1);
Query OK, 1 row affected (0.01 sec)mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 785 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)3.确认上述的所有数据创建操作，属于mysql的一个完整事务，到执行commit命令。
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
查看日志事件
mysql> show binlog events in 'mysql-bin.000003';
+------------------+-----+----------------+-----------+-------------+--------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+--------------------------------------------+
| mysql-bin.000003 | 4 | Format_desc | 51 | 123 | Server ver: 5.7.28-log, Binlog ver: 4 |
| mysql-bin.000003 | 123 | Previous_gtids | 51 | 154 | |
| mysql-bin.000003 | 154 | Anonymous_Gtid | 51 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000003 | 219 | Query | 51 | 337 | create database chaoge_linux |
| mysql-bin.000003 | 337 | Anonymous_Gtid | 51 | 402 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000003 | 402 | Query | 51 | 522 | create table chaoge_linux.students(id int) |
| mysql-bin.000003 | 522 | Anonymous_Gtid | 51 | 587 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000003 | 587 | Query | 51 | 655 | BEGIN |
| mysql-bin.000003 | 655 | Table_map | 51 | 714 | table_id: 108 (chaoge_linux.students) |
| mysql-bin.000003 | 714 | Write_rows | 51 | 754 | table_id: 108 flags: STMT_END_F |
| mysql-bin.000003 | 754 | Xid | 51 | 785 | COMMIT /* xid=15 */ |
+------------------+-----+----------------+-----------+-------------+--------------------------------------------+
11 rows in set (0.00 sec)