4.mysqldump备份
mysqldump备份语法
Mysqldump -u用户名 -p密码 参数 数据库名 > 数据备份文件
mysql自带的备份工具，可以实现本地备份，远程备份
mysqldump命令备份过程，实际上是把数据库、表，以SQL语句的形式，输出为文件的备份过程，这种方式称之为逻辑备份。
但是这种方式效率并不高，以SQL导出，在海量数据下，例如几十G的场景，备份、恢复的时间都会过长。
因此还会有其他备份方案。
4.1mysqldump连接参数
-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 interface can be insecure.
指定数据库
---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 interface can be insecure.
备份单个数据表
这里不能加上-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
锁表，防止数据写入
数据插入
解锁表
只要表结构，不要数据
有些情况下会只需要表结构，不要数据，命令如下
--no-data，-d
不写表的任何行信息。
如果你只想转储表的结构这很有用。
# 备份world库下，所有的表结构
mysqldump -uroot -pwww.yuchaoit.cn -d world > /mysql_backup/world_all_table_no_data.sql
# 只有建表语句而已了
grep -Ev '#|/*|--|^$' /mysql_backup/world_all_table_no_data.sql
# 单独备份某个表的结构
mysqldump -uroot -pwww.yuchaoit.cn -d world city > /mysql_backup/world_city_no_data.sql
grep -Ev '#|/*|--|^$' /mysql_backup/world_city_no_data.sql
只要表数据，不要结构
--no-create-info，-t
不写重新创建每个转储表的CREATE TABLE语句。
# 只要city表的数据
mysqldump -uroot -pwww.yuchaoit.cn -t world city > /mysql_backup/world_city_only_data.sql
grep -Ev '#|/*|--|^$' /mysql_backup/world_city_only_data.sql
备份且压缩数据
mysqldump -uroot -pwww.yuchaoit.cn employees departments | gzip > /mysql_backup/departments.sql.gz
结合Binlog的备份参数
--master-data[=value]
该选项将二进制日志的位置和文件名写入到输出中。
该选项要求有RELOAD权限，并且必须启用二进制日志。
如果该选项值等于1，位置和文件名被写入CHANGE MASTER语句形式的转储输出，如果你使用该SQL转储主服务器以设置从服务器，从服务器从主服务器二进制日志的正确位置开始。
如果选项值等于2，CHANGE MASTER语句被写成SQL注释。
--single-transaction
一般和--master-data=2 结合使用，保证所有库、表的一致性。
binlog日志
binlog是mysql一大重点，Binlog是一个二进制格式的文件，用于记录用户对数据库更新的SQL语句信息
例如更改数据库库表和更改表内容的SQL语句都会记录到binlog里，但是对库表等内容的查询则不会记录到日志中。
记录
DML，insert update，delete
DDL，create drop，alter，truncate
DCL，grant revoke
binlog的作用
当有数据写入到数据库时，还会同时把更新的SQL语句写入到对应的binlog文件里，这个文件就是上文所说的binlog文件。
配置binlog
1.修改配置文件
[root@db-51 ~]$cat /etc/my.cnf
[mysqld]
port=3306
user=mysql
basedir=/opt/mysql
datadir=/linux0224/mysql_3306/
socket=/tmp/mysql.sock
log_bin=/linux0224/mysql_3306/logs/mysql-bin
character_set_server=utf8mb4
server_id=51
[mysql]
socket=/tmp/mysql.sock
2.创建相关文件夹，授权，然后重启
[root@db-51 ~]$mkdir /linux0224/mysql_3306/logs/
[root@db-51 ~]$chown -R mysql.mysql /linux0224/
[root@db-51 ~]$systemctl restart mysqld
[root@db-51 ~]$
查看mysql关于bin_log的变量参数
mysql> select @@log_bin;
+-----------+
| @@log_bin |
+-----------+
| 1 |
+-----------+
1 row in set (0.00 sec)
mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------------------+
| log_bin | ON |
| log_bin_basename | /linux0224/mysql_3306/logs/mysql-bin |
| log_bin_index | /linux0224/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.01 sec)
mysql>
binlog内容的格式
事件event记录方式
1. 事件描述
时间戳
server_id
加密方式
开始位置 start_pos
结束位置 end_pos
2.事件内容
修改类的操作，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
查看binlog日志文件情况
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 154 |
+------------------+-----------+
1 row in set (0.00 sec)
mysql>
刷新新日志文件
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.000001 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql>
模拟binlog记录
1.主动写入新数据
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 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.000001 | 337 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
2.写入表数据
mysql> create table chaoge_linux.students(id int);
Query OK, 0 rows affected (0.01 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 522 | | | |
+------------------+----------+--------------+------------------+-------------------+
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.000001'
-> ;
+------------------+-----+----------------+-----------+-------------+--------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+--------------------------------------------+
| mysql-bin.000001 | 4 | Format_desc | 51 | 123 | Server ver: 5.7.28-log, Binlog ver: 4 |
| mysql-bin.000001 | 123 | Previous_gtids | 51 | 154 | |
| mysql-bin.000001 | 154 | Anonymous_Gtid | 51 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 219 | Query | 51 | 337 | create database chaoge_linux |
| mysql-bin.000001 | 337 | Anonymous_Gtid | 51 | 402 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 402 | Query | 51 | 522 | create table chaoge_linux.students(id int) |
+------------------+-----+----------------+-----------+-------------+--------------------------------------------+
6 rows in set (0.00 sec)
解密查看binlog日志
[root@db-51 ~]$mysqlbinlog /linux0224/mysql_3306/logs/mysql-bin.000001
binlog日志截取与恢复实践
1.前提是打开binlog功能
创建、导入数据库等操作，要提前就打开binlog，否则无法记录
2.模拟误删库，恢复数据
模拟误删除库，恢复到删库之前
3.模拟数据误删除
1.确保当前是开启binlog的
mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql>
2.创建数据
mysql> create database linux1024;
Query OK, 1 row affected (0.01 sec)
mysql> use linux1024;
Database changed
mysql>
mysql> use linux1024;
Database changed
mysql> create table user (
-> id int(11) not null auto_increment comment 'id',
-> name varchar(10) not null comment 'name',
-> age tinyint(4) not null comment 'age',
-> primary key (id)
-> ) engine=innodb default charset=utf8mb4;
Query OK, 0 rows affected (0.00 sec)
3.写入数据
insert into user(name,age) values
('于超',28),
('郑佳强',22),
('李文杰',24);
4.查看数据
mysql> select * from user;
+----+-----------+-----+
| id | name | age |
+----+-----------+-----+
| 1 | 于超 | 28 |
| 2 | 郑佳强 | 22 |
| 3 | 李文杰 | 24 |
+----+-----------+-----+
3 rows in set (0.00 sec)
5.模拟某个大傻子，误删除了数据，如何恢复？
drop database linux1024;
4.恢复思路
1. 截取从建库到删库之间的所有的binlog
2.先看看当前的binlog
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 1180 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
3.找到linux0224库创建的位置
mysql> show binlog events in 'mysql-bin.000002';
+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mysql-bin.000002 | 4 | Format_desc | 51 | 123 | Server ver: 5.7.28-log, Binlog ver: 4 |
| mysql-bin.000002 | 123 | Previous_gtids | 51 | 154 | |
| mysql-bin.000002 | 154 | Anonymous_Gtid | 51 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000002 | 219 | Query | 51 | 328 | create database linux1024 |
| mysql-bin.000002 | 328 | Anonymous_Gtid | 51 | 393 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000002 | 393 | Query | 51 | 699 | use `linux1024`; create table user (
id int(11) not null auto_increment comment 'id',
name varchar(10) not null comment 'name',
age tinyint(4) not null comment 'age',
primary key (id)
) engine=innodb default charset=utf8mb4 |
| mysql-bin.000002 | 699 | Anonymous_Gtid | 51 | 764 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000002 | 764 | Query | 51 | 841 | BEGIN |
| mysql-bin.000002 | 841 | Table_map | 51 | 897 | table_id: 111 (linux1024.user) |
| mysql-bin.000002 | 897 | Write_rows | 51 | 977 | table_id: 111 flags: STMT_END_F |
| mysql-bin.000002 | 977 | Xid | 51 | 1008 | COMMIT /* xid=39 */ |
| mysql-bin.000002 | 1008 | Anonymous_Gtid | 51 | 1073 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000002 | 1073 | Query | 51 | 1180 | drop database linux1024 |
+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
13 rows in set (0.00 sec)
4.起点为| mysql-bin.000002 | 154 | Anonymous_Gtid | 51 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
5.终点为 | mysql-bin.000002 | 977 | Xid | 51 | 1008 | COMMIT /* xid=39 *
6.导出创建、删除之间的操作，也就是你要的数据
[root@db-51 ~]$mysqlbinlog --start-position=154 --stop-position=1008 /linux0224/mysql_3306/logs/mysql-bin.000002 > /tmp/restore_linux1024.sql
6.截取的日志，进行回放，重新开关binlog
# 基于sql_log_bin 参数，临时关闭二进制日志写入，否则会重复加载恢复数据的日志
# 导入数据后，重新打开即可
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
mysql> source /tmp/restore_linux1024.sql
mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)
数据已经恢复
原创文章，作者：ItWorker，如若转载，请注明出处：https://blog.ytso.com/279640.html