.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   转储所有数据库中的所有表。

[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)

.mysqldump备份

解密查看binlog日志

[root@db-51 ~]$mysqlbinlog /linux0224/mysql_3306/logs/mysql-bin.000001 

.mysqldump备份

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)

数据已经恢复

.mysqldump备份

原创文章,作者:ItWorker,如若转载,请注明出处:https://blog.ytso.com/279640.html

(0)
上一篇 2022年8月8日
下一篇 2022年8月8日

相关推荐

发表回复

登录后才能评论