mysql基于位点的数据库恢复

一、使用mysqldump以SQL格式转储数据

1、导出

备份所有数据库

[root@19c01 tmp]# mysqldump -uroot -p --all-databases>/tmp/all_database.sql

只备份test数据库

[root@19c01 tmp]# mysqldump -uroot -p --databases test>/tmp/test.sql

[root@19c01 tmp]# mysqldump -uroot -p  test t1>/tmp/test.sql

注意：没有–databases参数，那么表示只备份test库中的t1表

只导出个别表

[root@19c01 tmp]# mysqldump -uroot -p test t1 t2 t3 > /tmp/test.sql

[root@19c01 tmp]# mysqldump -uroot -p --all-databases --single-transaction >/tmp/all_database.sql 

[root@19c01 tmp]# mysqldump  -uroot -p --all-databases --source-data --single-transaction >/tmp/alldatabase.sql

[root@19c01 tmp]# mysqldump  -uroot -p  --single-transaction --flush-logs --source-data=2 --all-databases > /tmp/all_databases.sql

[root@19c01 tmp]# mysqldump  -uroot -p --single-transaction --flush-logs --source-data=2 --all-databases --delete-source-logs > /tmp/all_database.sql

–single-transaction执行不锁定表的在线备份

使用–all-database和–database参数的备份，会在转储集中加入create database和use语法，导入的时候不要再关注这部分。

如果加入–add-drop-database参数，那么会在create database语句之前加入drop database语法，用于删除已经存在的数据库。

2、导入

[root@19c01 tmp]# mysql -uroot -p </tmp/alldatabase.sql

(root@localhost) [(none)]> source  /tmp/alldatabase.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

二、使用mysqldump以分隔文本格式转储数据

1、导出

[root@19c01 tmp]# mysqldump -uroot -p --tab=/tmp/mysql test
Enter password: 
[root@19c01 tmp]# cd  mysql/
[root@19c01 mysql]# ls
t1.sql  t1.txt

当使用–tab参数指向目录时，可以转储出来.sql文件和.txt文件。其中.sql文件存储表的创建语句,.txt存储数据。.txt文件是由mysql使用select …into outfile写入的。
如果使用远程登录执行，那么.txt将写入服务器上.sql将写入客户端。可以控制分割符

–fields-terminated-by=str

用于分隔列值的字符串（默认值：制表符）。

–fields-enclosed-by=char

用于括起列值的字符（默认值：无字符）。

–fields-optionally-enclosed-by=char

用于包含非数字列值的字符（默认值：无字符）。

–fields-escaped-by=char

用于转义特殊字符的字符（默认：不转义）。

–lines-terminated-by=str

行终止字符串（默认：换行符）。

[root@19c01 mysql]# mysqldump -uroot -p  --tab=/tmp/mysql --fields-terminated-by=, --fields-enclosed-by='"' --lines-terminated-by=0x0d0a mysql
Enter password: 
[root@19c01 mysql]# ls -lrt
total 1428
-rw-r----- 1 mysql mysql   44721 Mar 12 16:16 t1.txt
-rwxrwxrwx 1 root  root     2632 Mar 12 16:16 t1.sql
-rw-r----- 1 mysql mysql       0 Mar 12 16:38 columns_priv.txt
-rw-r--r-- 1 root  root     2112 Mar 12 16:38 columns_priv.sql
-rw-r----- 1 mysql mysql       0 Mar 12 16:38 component.txt
-rw-r--r-- 1 root  root     1521 Mar 12 16:38 component.sql
-rw-r--r-- 1 root  root     3643 Mar 12 16:38 db.sql
-rw-r----- 1 mysql mysql     233 Mar 12 16:38 db.txt
-rw-r--r-- 1 root  root     1839 Mar 12 16:38 default_roles.sql
-rw-r----- 1 mysql mysql       0 Mar 12 16:38 default_roles.txt
-rw-r--r-- 1 root  root     1864 Mar 12 16:38 engine_cost.sql
-rw-r----- 1 mysql mysql     143 Mar 12 16:38 engine_cost.txt
-rw-r----- 1 mysql mysql       0 Mar 12 16:38 func.txt
-rw-r--r-- 1 root  root     1711 Mar 12 16:38 func.sql
-rw-r----- 1 mysql mysql    4935 Mar 12 16:38 global_grants.txt
-rw-r--r-- 1 root  root     1817 Mar 12 16:38 global_grants.sql
-rw-r--r-- 1 root  root     1668 Mar 12 16:38 gtid_executed.sql
-rw-r--r-- 1 root  root     1612 Mar 12 16:38 help_category.sql
-rw-r----- 1 mysql mysql    1790 Mar 12 16:38 help_category.txt

2、导入

[root@19c01 mysql]# mysql -uroot -p -Dmysql <user.sql
Enter password: 

[root@19c01 mysql]# mysqlimport -uroot -p mysql /tmp/mysql/user.txt
Enter password: 

(root@localhost) [test]> load data infile '/tmp/mysql/t1.txt' into table t1;
Query OK, 330 rows affected (0.00 sec)
Records: 330  Deleted: 0  Skipped: 0  Warnings: 0

(root@localhost) [test]> select count(*) from t1;
+----------+
| count(*) |
+----------+
|      660 |
+----------+
1 row in set (0.02 sec)

[root@19c01 mysql]# mysqlimport -uroot -p test /tmp/mysql/t1.txt
Enter password: 
test.t1: Records: 330  Deleted: 0  Skipped: 0  Warnings: 0
[root@19c01 mysql]# 

三、如何转储存储程序（存储过程和函数、触发器和事件）

–events：转储事件调度程序事件

–routines：转储存储过程和函数

–triggers：表的转储触发器

默认情况下启用该–triggers选项，以便在转储表时，它们会附带它们拥有的任何触发器。其他选项默认禁用，必须显式指定才能转储相应的对象。要显式禁用任何这些选项，请使用其跳过形式： --skip-events、 --skip-routines或 --skip-triggers。

[root@19c01 mysql]# mysqldump  -uroot -p --no-data --routines --events test > test_def.sql
Enter password: 
[root@19c01 mysql]# mysqldump  -uroot -p --no-create-info test > test_data.sql
Enter password: 

四、时间点恢复

1、使用二进制日志进行时间点恢复

备份

[root@19c01 mysql]# mysqldump  -uroot -p --databases test >/tmp/mysql/test
Enter password: 

写入数据

(root@localhost) [test]> select count(*) from t1;
+----------+
| count(*) |
+----------+
|      990 |
+----------+
1 row in set (0.02 sec)

(root@localhost) [test]> insert into t1 select * from t1;
Query OK, 990 rows affected (0.01 sec)
Records: 990  Duplicates: 0  Warnings: 0

(root@localhost) [test]> insert into t1 select * from t1;
Query OK, 1980 rows affected (0.04 sec)
Records: 1980  Duplicates: 0  Warnings: 0

(root@localhost) [test]> insert into t1 select * from t1;
Query OK, 3960 rows affected (0.05 sec)
Records: 3960  Duplicates: 0  Warnings: 0

(root@localhost) [test]> commit;
Query OK, 0 rows affected (0.00 sec)

(root@localhost) [test]> select count(*) from t1;
+----------+
| count(*) |
+----------+
|     7920 |
+----------+
1 row in set (0.02 sec)

删除数据库

(root@localhost) [(none)]> drop database test;
Query OK, 1 row affected (0.01 sec)
(root@localhost) [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

恢复

[root@19c01 mysql]# mysql -uroot -p </tmp/mysql/test
Enter password: 

(root@localhost) [test]> select count(*) from t1;
+----------+
| count(*) |
+----------+
|      990 |
+----------+
1 row in set (0.02 sec)

应用日志

[root@19c01 data]# mysqlbinlog binlog.000011 binlog.000012 binlog.000013|mysql -uroot -p
Enter password: 
[root@19c01 data]

[root@19c01 data]# mysqlbinlog binlog.000011>/tmp/t1.sql
[root@19c01 data]# mysqlbinlog binlog.000012>>/tmp/t1.sql
[root@19c01 data]# mysqlbinlog binlog.000013>>/tmp/t1.sql

2、使用事件位置进行时间点恢复

[root@19c01 data]# mysqlbinlog --start-datetime="2024-03-12 16:05:00" --stop-datetime="2024-03-12 17:08:00" --verbose /data/binlog.000012 /data/binlog.000013 | grep -C 15 "drop database"


#240312 16:58:51 server id 1  end_log_pos 7146223 CRC32 0xa0c4ffae      Xid = 6096
COMMIT/*!*/;
# at 7146223
#240312 17:00:19 server id 1  end_log_pos 7146307 CRC32 0xa1d330c6      Anonymous_GTID  last_committed=645      sequence_number=646     rbr_only=no     original_committed_timestamp=1710234019955743   immediate_commit_timestamp=1710234525205855 transaction_length=188
# original_commit_timestamp=1710234019955743 (2024-03-12 17:00:19.955743 CST)
# immediate_commit_timestamp=1710234525205855 (2024-03-12 17:08:45.205855 CST)
/*!80001 SET @@session.original_commit_timestamp=1710234019955743*//*!*/;
/*!80014 SET @@session.original_server_version=80034*//*!*/;
/*!80014 SET @@session.immediate_server_version=80034*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 7146307
#240312 17:00:19 server id 1  end_log_pos 7146411 CRC32 0x12683268      Query   thread_id=23    exec_time=506   error_code=0    Xid = 6103
SET TIMESTAMP=1710234019/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
drop database test
/*!*/;

创建数据

(root@localhost) [test]> create table t1 as select * from information_schema.tables;
Query OK, 330 rows affected (0.08 sec)
Records: 330  Duplicates: 0  Warnings: 0

(root@localhost) [test]> select count(*) from t1;
+----------+
| count(*) |
+----------+
|      330 |
+----------+
1 row in set (0.01 sec)

备份

mysqldump  -uroot -p  --single-transaction --flush-logs --source-data=2 --databases test > /tmp/test.sql

删除数据库

(root@localhost) [test]> select count(*) from t1;
+----------+
| count(*) |
+----------+
|      330 |
+----------+
1 row in set (0.02 sec)

(root@localhost) [test]> insert into  t1 select * from t1;
Query OK, 330 rows affected (0.00 sec)
Records: 330  Duplicates: 0  Warnings: 0

(root@localhost) [test]> insert into  t1 select * from t1;
Query OK, 660 rows affected (0.01 sec)
Records: 660  Duplicates: 0  Warnings: 0

(root@localhost) [test]> insert into  t1 select * from t1;
Query OK, 1320 rows affected (0.03 sec)
Records: 1320  Duplicates: 0  Warnings: 0

(root@localhost) [test]> select count(*) from t1;
+----------+
| count(*) |
+----------+
|     2640 |
+----------+
1 row in set (0.00 sec)

(root@localhost) [(none)]> drop database test;
Query OK, 1 row affected (0.00 sec)

恢复

[root@19c01 ~]# mysql -uroot -p </tmp/mysql/test.sql 
Enter password:
(root@localhost) [test]> select count(*) from t1;
+----------+
| count(*) |
+----------+
|      330 |
+----------+
1 row in set (0.01 sec)

继续恢复

(root@localhost) [test]> show binary logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000010 |       547 | No        |
| binlog.000011 |     39157 | No        |
| binlog.000012 |       180 | No        |
| binlog.000013 |   7299737 | No        |
| binlog.000014 |       201 | No        |
| binlog.000015 |    341619 | No        |
+---------------+-----------+-----------+
6 rows in set (0.00 sec)


[root@19c01 ~]# mysqlbinlog --start-datetime="2024-03-12 17:05:00" --stop-datetime="2024-03-12 17:50:00" --verbose /data/binlog.000014  /data/binlog.000015 | grep -C 15 "drop database"
/*!80014 SET @@session.original_server_version=80034*//*!*/;
/*!80014 SET @@session.immediate_server_version=80034*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 234
...
#240312 17:41:24 server id 1  end_log_pos 301531 CRC32 0xe5af9efa       Xid = 6394
COMMIT/*!*/;
# at 301531
#240312 17:42:09 server id 1  end_log_pos 301608 CRC32 0x8ce81e1b       Anonymous_GTID  last_committed=10       sequence_number=11      rbr_only=no     original_committed_timestamp=1710236529577223   immediate_commit_timestamp=1710236529577223 transaction_length=181
# original_commit_timestamp=1710236529577223 (2024-03-12 17:42:09.577223 CST)
# immediate_commit_timestamp=1710236529577223 (2024-03-12 17:42:09.577223 CST)
/*!80001 SET @@session.original_commit_timestamp=1710236529577223*//*!*/;
/*!80014 SET @@session.original_server_version=80034*//*!*/;
/*!80014 SET @@session.immediate_server_version=80034*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 301608
#240312 17:42:09 server id 1  end_log_pos 301712 CRC32 0x92328389       Query   thread_id=60    exec_time=0     error_code=0    Xid = 6400
SET TIMESTAMP=1710236529/*!*/;
drop database test
/*!*/;
# at 301712

确定备份的恢复位置

[root@19c01 ~]# head -n 50 /tmp/mysql/test.sql 
--
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000015', MASTER_LOG_POS=157;

--
-- Current Database: `test`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;

USE `test`;

--
-- Table structure for table `t1`
--

[root@19c01 ~]# mysqlbinlog  --start-position=157 --stop-position=301608  /data/binlog.000015 | mysql -u root -p
Enter password: 

验证

(root@localhost) [test]> select count(*) from t1;
+----------+
| count(*) |
+----------+
|     2640 |
+----------+
1 row in set (0.00 sec)