.mysqldump备份
2022/8/9 2:22:48
本文主要是介绍.mysqldump备份，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！
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 转储所有数据库中的所有表。 [[email protected] ~]#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命令压缩 指定备份库，以及所有数据 [[email protected] /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工资表 [email protected] /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. [[email protected] /opt]# [[email protected] /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
备份多个表
# 备份库下的多个表 [[email protected] /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. [[email protected] /opt]# [[email protected] /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.修改配置文件 [[email protected] ~]$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.创建相关文件夹，授权，然后重启 [[email protected] ~]$mkdir /linux0224/mysql_3306/logs/ [[email protected] ~]$chown -R mysql.mysql /linux0224/ [[email protected] ~]$systemctl restart mysqld [[email protected] ~]$
查看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日志
[[email protected] ~]$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.导出创建、删除之间的操作，也就是你要的数据
[[email protected] ~]$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)
数据已经恢复
这篇关于.mysqldump备份的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！
- 2024-11-04部署MySQL集群项目实战：新手入门教程
- 2024-11-04如何部署MySQL集群资料：新手入门指南
- 2024-11-02MySQL集群项目实战：新手入门指南
- 2024-11-02初学者指南：部署MySQL集群资料
- 2024-11-01部署MySQL集群教程：新手入门指南
- 2024-11-01如何部署MySQL集群：新手入门教程
- 2024-11-01部署MySQL集群学习：新手入门教程
- 2024-11-01部署MySQL集群入门：新手必读指南
- 2024-10-23BinLog入门：新手必读的MySQL二进制日志指南
- 2024-10-23Binlog入门：MySQL数据库的日志管理指南
