相对于MySQL,MariaDB就自带了很多实用的工具，就拿今天这个备份/还原工具Mariabackup来说吧。

Mariabackup是MariaDB提供的一个开源工具，它支持InnoDB、MyRocks、Aria和MyISAM表的物理在线备份，是在MariaDB10.1.23、MariaDB 10.2.7两个版本中基于Percona XtraBackup 2.3.8开发出来的，可运行在Linux和Windows上。

先来了解几个常用的参数

• backup 备份
• binlog-info binlog信息
• close-files 关闭文件
• copy-back 复制备份
• databases 指定要备份的数据库和表。
• databases-exclude 数据库中排除的哪些表
• databases-file 数据库文件
• datadir 数据目录

先来看看简单的备份命令吧mariabackup --backup --target-dir /dir --user user_name --password user_passwd，需要我们输入用户名、密码以及存放备份的目录。

E:\tool\mariadb-10.5.5\bin>mariabackup --backup --target-dir d:/data/backup/  --user root --password root
[00] 2020-09-13 16:39:39 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set
[00] 2020-09-13 16:39:39 Using server version 10.5.5-MariaDB
[00] 2020-09-13 16:39:39 Warning: option 'datadir' has different values:
  'E:/tool/mariadb-10.5.5/data' in defaults file
  'E:\tool\mariadb-10.5.5\data\' in SHOW VARIABLES
mariabackup based on MariaDB server 10.5.5-MariaDB Win64 (AMD64)
[00] 2020-09-13 16:39:39 cd to E:\tool\mariadb-10.5.5\data\
[00] 2020-09-13 16:39:39 open files limit requested 0, set to 0
[00] 2020-09-13 16:39:39 mariabackup: using the following InnoDB configuration:
[00] 2020-09-13 16:39:39 innodb_data_home_dir =
[00] 2020-09-13 16:39:39 innodb_data_file_path = ibdata1:12M:autoextend
[00] 2020-09-13 16:39:39 innodb_log_group_home_dir = .\
2020-09-13 16:39:39 0 [Note] InnoDB: Number of pools: 1
[00] 2020-09-13 16:39:39 mariabackup: Generating a list of tablespaces
2020-09-13 16:39:39 0 [Warning] InnoDB: Allocated tablespace ID 4 for mysql/gtid_slave_pos, old maximum was 0
[00] 2020-09-13 16:39:39 >> log scanned up to (64467)
[01] 2020-09-13 16:39:39 Copying ibdata1 to d:\data\backup\ibdata1
[01] 2020-09-13 16:39:39         ...done
[01] 2020-09-13 16:39:39 Copying .\mysql\gtid_slave_pos.ibd to d:\data\backup\mysql\gtid_slave_pos.ibd
[01] 2020-09-13 16:39:39         ...done
[01] 2020-09-13 16:39:39 Copying .\mysql\innodb_index_stats.ibd to d:\data\backup\mysql\innodb_index_stats.ibd
[01] 2020-09-13 16:39:39         ...done
[01] 2020-09-13 16:39:39 Copying .\mysql\innodb_table_stats.ibd to d:\data\backup\mysql\innodb_table_stats.ibd
[01] 2020-09-13 16:39:39         ...done
[01] 2020-09-13 16:39:39 Copying .\mysql\transaction_registry.ibd to d:\data\backup\mysql\transaction_registry.ibd
[01] 2020-09-13 16:39:39         ...done
[01] 2020-09-13 16:39:39 Copying .\test\student_details.ibd to d:\data\backup\test\student_details.ibd
[01] 2020-09-13 16:39:39         ...done
[01] 2020-09-13 16:39:39 Copying .\test\student_tests.ibd to d:\data\backup\test\student_tests.ibd
[01] 2020-09-13 16:39:39         ...done
[01] 2020-09-13 16:39:39 Copying .\test\table_name.ibd to d:\data\backup\test\table_name.ibd
[01] 2020-09-13 16:39:39         ...done
[00] 2020-09-13 16:39:40 Acquiring BACKUP LOCKS...
[00] 2020-09-13 16:39:40 >> log scanned up to (64467)
[00] 2020-09-13 16:39:40 Starting to backup non-InnoDB tables and files
[01] 2020-09-13 16:39:40 Copying .\mysql\columns_priv.frm to d:\data\backup\mysql\columns_priv.frm
[01] 2020-09-13 16:39:40         ...done
[01] 2020-09-13 16:39:40 Copying .\mysql\columns_priv.MAD to d:\data\backup\mysql\columns_priv.MAD
[01] 2020-09-13 16:39:40         ...done
[01] 2020-09-13 16:39:40 Copying .\mysql\columns_priv.MAI to d:\data\backup\mysql\columns_priv.MAI
[01] 2020-09-13 16:39:40         ...done
[01] 2020-09-13 16:39:40 Copying .\mysql\column_stats.frm to d:\data\backup\mysql\column_stats.frm
[01] 2020-09-13 16:39:40         ...done
[01] 2020-09-13 16:39:40 Copying .\mysql\column_stats.MAD to d:\data\backup\mysql\column_stats.MAD
[01] 2020-09-13 16:39:40         ...done
[01] 2020-09-13 16:39:40 Copying .\mysql\column_stats.MAI to d:\data\backup\mysql\column_stats.MAI
[01] 2020-09-13 16:39:40         ...done
[01] 2020-09-13 16:39:40 Copying .\mysql\db.frm to d:\data\backup\mysql\db.frm
[01] 2020-09-13 16:39:40         ...done
[01] 2020-09-13 16:39:40 Copying .\mysql\db.MAD to d:\data\backup\mysql\db.MAD
[01] 2020-09-13 16:39:40         ...done
[01] 2020-09-13 16:39:40 Copying .\mysql\db.MAI to d:\data\backup\mysql\db.MAI
[01] 2020-09-13 16:39:40         ...done
[01] 2020-09-13 16:39:40 Copying .\mysql\event.frm to d:\data\backup\mysql\event.frm
[01] 2020-09-13 16:39:40         ...done
[01] 2020-09-13 16:39:41 Copying .\mysql\event.MAD to d:\data\backup\mysql\event.MAD
[01] 2020-09-13 16:39:41         ...done
[01] 2020-09-13 16:39:41 Copying .\mysql\event.MAI to d:\data\backup\mysql\event.MAI
[01] 2020-09-13 16:39:41         ...done
[01] 2020-09-13 16:39:41 Copying .\mysql\func.frm to d:\data\backup\mysql\func.frm
[01] 2020-09-13 16:39:41         ...done
[01] 2020-09-13 16:39:41 Copying .\mysql\func.MAD to d:\data\backup\mysql\func.MAD
[01] 2020-09-13 16:39:41         ...done
[01] 2020-09-13 16:39:41 Copying .\mysql\func.MAI to d:\data\backup\mysql\func.MAI
[01] 2020-09-13 16:39:41         ...done
[01] 2020-09-13 16:39:41 Copying .\mysql\general_log.CSM to d:\data\backup\mysql\general_log.CSM
[01] 2020-09-13 16:39:41         ...done
[01] 2020-09-13 16:39:41 Copying .\mysql\general_log.CSV to d:\data\backup\mysql\general_log.CSV
[01] 2020-09-13 16:39:41         ...done
[01] 2020-09-13 16:39:41 Copying .\mysql\general_log.frm to d:\data\backup\mysql\general_log.frm
[01] 2020-09-13 16:39:41         ...done
[01] 2020-09-13 16:39:41 Copying .\mysql\global_priv.frm to d:\data\backup\mysql\global_priv.frm
[01] 2020-09-13 16:39:41         ...done
[01] 2020-09-13 16:39:41 Copying .\mysql\global_priv.MAD to d:\data\backup\mysql\global_priv.MAD
[01] 2020-09-13 16:39:41         ...done
[01] 2020-09-13 16:39:41 Copying .\mysql\global_priv.MAI to d:\data\backup\mysql\global_priv.MAI
[01] 2020-09-13 16:39:41         ...done
[01] 2020-09-13 16:39:41 Copying .\mysql\gtid_slave_pos.frm to d:\data\backup\mysql\gtid_slave_pos.frm
[01] 2020-09-13 16:39:41         ...done
[01] 2020-09-13 16:39:41 Copying .\mysql\help_category.frm to d:\data\backup\mysql\help_category.frm
[01] 2020-09-13 16:39:41         ...done
[01] 2020-09-13 16:39:41 Copying .\mysql\help_category.MAD to d:\data\backup\mysql\help_category.MAD
[01] 2020-09-13 16:39:41         ...done
[01] 2020-09-13 16:39:41 Copying .\mysql\help_category.MAI to d:\data\backup\mysql\help_category.MAI
[01] 2020-09-13 16:39:41         ...done
[01] 2020-09-13 16:39:41 Copying .\mysql\help_keyword.frm to d:\data\backup\mysql\help_keyword.frm
[01] 2020-09-13 16:39:41         ...done
[01] 2020-09-13 16:39:41 Copying .\mysql\help_keyword.MAD to d:\data\backup\mysql\help_keyword.MAD
[00] 2020-09-13 16:39:41 >> log scanned up to (64467)
[01] 2020-09-13 16:39:41         ...done
[01] 2020-09-13 16:39:41 Copying .\mysql\help_keyword.MAI to d:\data\backup\mysql\help_keyword.MAI
[01] 2020-09-13 16:39:41         ...done
[01] 2020-09-13 16:39:41 Copying .\mysql\help_relation.frm to d:\data\backup\mysql\help_relation.frm
[01] 2020-09-13 16:39:41         ...done
[01] 2020-09-13 16:39:41 Copying .\mysql\help_relation.MAD to d:\data\backup\mysql\help_relation.MAD
[01] 2020-09-13 16:39:41         ...done
[01] 2020-09-13 16:39:41 Copying .\mysql\help_relation.MAI to d:\data\backup\mysql\help_relation.MAI
[01] 2020-09-13 16:39:41         ...done
[01] 2020-09-13 16:39:41 Copying .\mysql\help_topic.frm to d:\data\backup\mysql\help_topic.frm
[01] 2020-09-13 16:39:41         ...done
[01] 2020-09-13 16:39:41 Copying .\mysql\help_topic.MAD to d:\data\backup\mysql\help_topic.MAD
[01] 2020-09-13 16:39:41         ...done
[01] 2020-09-13 16:39:41 Copying .\mysql\help_topic.MAI to d:\data\backup\mysql\help_topic.MAI
[01] 2020-09-13 16:39:41         ...done
[01] 2020-09-13 16:39:41 Copying .\mysql\index_stats.frm to d:\data\backup\mysql\index_stats.frm
[01] 2020-09-13 16:39:41         ...done
[01] 2020-09-13 16:39:41 Copying .\mysql\index_stats.MAD to d:\data\backup\mysql\index_stats.MAD
[01] 2020-09-13 16:39:41         ...done
[01] 2020-09-13 16:39:41 Copying .\mysql\index_stats.MAI to d:\data\backup\mysql\index_stats.MAI
[01] 2020-09-13 16:39:41         ...done
[01] 2020-09-13 16:39:41 Copying .\mysql\innodb_index_stats.frm to d:\data\backup\mysql\innodb_index_stats.frm
[01] 2020-09-13 16:39:41         ...done
[01] 2020-09-13 16:39:41 Copying .\mysql\innodb_table_stats.frm to d:\data\backup\mysql\innodb_table_stats.frm
[01] 2020-09-13 16:39:41         ...done
[01] 2020-09-13 16:39:41 Copying .\mysql\plugin.frm to d:\data\backup\mysql\plugin.frm
[01] 2020-09-13 16:39:41         ...done
[01] 2020-09-13 16:39:41 Copying .\mysql\plugin.MAD to d:\data\backup\mysql\plugin.MAD
[01] 2020-09-13 16:39:41         ...done
[01] 2020-09-13 16:39:41 Copying .\mysql\plugin.MAI to d:\data\backup\mysql\plugin.MAI
[01] 2020-09-13 16:39:41         ...done
[01] 2020-09-13 16:39:41 Copying .\mysql\proc.frm to d:\data\backup\mysql\proc.frm
[01] 2020-09-13 16:39:41         ...done
[01] 2020-09-13 16:39:41 Copying .\mysql\proc.MAD to d:\data\backup\mysql\proc.MAD
[01] 2020-09-13 16:39:41         ...done
[01] 2020-09-13 16:39:41 Copying .\mysql\proc.MAI to d:\data\backup\mysql\proc.MAI
[01] 2020-09-13 16:39:41         ...done
[01] 2020-09-13 16:39:41 Copying .\mysql\procs_priv.frm to d:\data\backup\mysql\procs_priv.frm
[01] 2020-09-13 16:39:41         ...done
[01] 2020-09-13 16:39:41 Copying .\mysql\procs_priv.MAD to d:\data\backup\mysql\procs_priv.MAD
[01] 2020-09-13 16:39:41         ...done
[01] 2020-09-13 16:39:41 Copying .\mysql\procs_priv.MAI to d:\data\backup\mysql\procs_priv.MAI
[01] 2020-09-13 16:39:41         ...done
[01] 2020-09-13 16:39:41 Copying .\mysql\proxies_priv.frm to d:\data\backup\mysql\proxies_priv.frm
[01] 2020-09-13 16:39:42         ...done
[01] 2020-09-13 16:39:42 Copying .\mysql\proxies_priv.MAD to d:\data\backup\mysql\proxies_priv.MAD
[01] 2020-09-13 16:39:42         ...done
[01] 2020-09-13 16:39:42 Copying .\mysql\proxies_priv.MAI to d:\data\backup\mysql\proxies_priv.MAI
[01] 2020-09-13 16:39:42         ...done
[01] 2020-09-13 16:39:42 Copying .\mysql\roles_mapping.frm to d:\data\backup\mysql\roles_mapping.frm
[01] 2020-09-13 16:39:42         ...done
[01] 2020-09-13 16:39:42 Copying .\mysql\roles_mapping.MAD to d:\data\backup\mysql\roles_mapping.MAD
[01] 2020-09-13 16:39:42         ...done
[01] 2020-09-13 16:39:42 Copying .\mysql\roles_mapping.MAI to d:\data\backup\mysql\roles_mapping.MAI
[01] 2020-09-13 16:39:42         ...done
[01] 2020-09-13 16:39:42 Copying .\mysql\servers.frm to d:\data\backup\mysql\servers.frm
[01] 2020-09-13 16:39:42         ...done
[01] 2020-09-13 16:39:42 Copying .\mysql\servers.MAD to d:\data\backup\mysql\servers.MAD
[01] 2020-09-13 16:39:42         ...done
[01] 2020-09-13 16:39:42 Copying .\mysql\servers.MAI to d:\data\backup\mysql\servers.MAI
[01] 2020-09-13 16:39:42         ...done
[01] 2020-09-13 16:39:42 Copying .\mysql\slow_log.CSM to d:\data\backup\mysql\slow_log.CSM
[01] 2020-09-13 16:39:42         ...done
[01] 2020-09-13 16:39:42 Copying .\mysql\slow_log.CSV to d:\data\backup\mysql\slow_log.CSV
[01] 2020-09-13 16:39:42         ...done
[01] 2020-09-13 16:39:42 Copying .\mysql\slow_log.frm to d:\data\backup\mysql\slow_log.frm
[01] 2020-09-13 16:39:42         ...done
[01] 2020-09-13 16:39:42 Copying .\mysql\tables_priv.frm to d:\data\backup\mysql\tables_priv.frm
[01] 2020-09-13 16:39:42         ...done
[00] 2020-09-13 16:39:42 >> log scanned up to (64467)
[01] 2020-09-13 16:39:42 Copying .\mysql\tables_priv.MAD to d:\data\backup\mysql\tables_priv.MAD
[01] 2020-09-13 16:39:42         ...done
[01] 2020-09-13 16:39:42 Copying .\mysql\tables_priv.MAI to d:\data\backup\mysql\tables_priv.MAI
[01] 2020-09-13 16:39:42         ...done
[01] 2020-09-13 16:39:42 Copying .\mysql\table_stats.frm to d:\data\backup\mysql\table_stats.frm
[01] 2020-09-13 16:39:42         ...done
[01] 2020-09-13 16:39:42 Copying .\mysql\table_stats.MAD to d:\data\backup\mysql\table_stats.MAD
[01] 2020-09-13 16:39:42         ...done
[01] 2020-09-13 16:39:42 Copying .\mysql\table_stats.MAI to d:\data\backup\mysql\table_stats.MAI
[01] 2020-09-13 16:39:42         ...done
[01] 2020-09-13 16:39:42 Copying .\mysql\time_zone.frm to d:\data\backup\mysql\time_zone.frm
[01] 2020-09-13 16:39:42         ...done
[01] 2020-09-13 16:39:42 Copying .\mysql\time_zone.MAD to d:\data\backup\mysql\time_zone.MAD
[01] 2020-09-13 16:39:42         ...done
[01] 2020-09-13 16:39:42 Copying .\mysql\time_zone.MAI to d:\data\backup\mysql\time_zone.MAI
[01] 2020-09-13 16:39:42         ...done
[01] 2020-09-13 16:39:42 Copying .\mysql\time_zone_leap_second.frm to d:\data\backup\mysql\time_zone_leap_second.frm
[01] 2020-09-13 16:39:42         ...done
[01] 2020-09-13 16:39:42 Copying .\mysql\time_zone_leap_second.MAD to d:\data\backup\mysql\time_zone_leap_second.MAD
[01] 2020-09-13 16:39:42         ...done
[01] 2020-09-13 16:39:42 Copying .\mysql\time_zone_leap_second.MAI to d:\data\backup\mysql\time_zone_leap_second.MAI
[01] 2020-09-13 16:39:42         ...done
[01] 2020-09-13 16:39:42 Copying .\mysql\time_zone_name.frm to d:\data\backup\mysql\time_zone_name.frm
[01] 2020-09-13 16:39:42         ...done
[01] 2020-09-13 16:39:42 Copying .\mysql\time_zone_name.MAD to d:\data\backup\mysql\time_zone_name.MAD
[01] 2020-09-13 16:39:42         ...done
[01] 2020-09-13 16:39:42 Copying .\mysql\time_zone_name.MAI to d:\data\backup\mysql\time_zone_name.MAI
[01] 2020-09-13 16:39:42         ...done
[01] 2020-09-13 16:39:42 Copying .\mysql\time_zone_transition.frm to d:\data\backup\mysql\time_zone_transition.frm
[01] 2020-09-13 16:39:42         ...done
[01] 2020-09-13 16:39:42 Copying .\mysql\time_zone_transition.MAD to d:\data\backup\mysql\time_zone_transition.MAD
[01] 2020-09-13 16:39:42         ...done
[01] 2020-09-13 16:39:42 Copying .\mysql\time_zone_transition.MAI to d:\data\backup\mysql\time_zone_transition.MAI
[01] 2020-09-13 16:39:42         ...done
[01] 2020-09-13 16:39:42 Copying .\mysql\time_zone_transition_type.frm to d:\data\backup\mysql\time_zone_transition_type.frm
[01] 2020-09-13 16:39:42         ...done
[01] 2020-09-13 16:39:42 Copying .\mysql\time_zone_transition_type.MAD to d:\data\backup\mysql\time_zone_transition_type.MAD
[01] 2020-09-13 16:39:42         ...done
[01] 2020-09-13 16:39:42 Copying .\mysql\time_zone_transition_type.MAI to d:\data\backup\mysql\time_zone_transition_type.MAI
[01] 2020-09-13 16:39:42         ...done
[01] 2020-09-13 16:39:42 Copying .\mysql\transaction_registry.frm to d:\data\backup\mysql\transaction_registry.frm
[01] 2020-09-13 16:39:42         ...done
[01] 2020-09-13 16:39:42 Copying .\mysql\user.frm to d:\data\backup\mysql\user.frm
[01] 2020-09-13 16:39:42         ...done
[01] 2020-09-13 16:39:42 Copying .\performance_schema\db.opt to d:\data\backup\performance_schema\db.opt
[01] 2020-09-13 16:39:42         ...done
[01] 2020-09-13 16:39:42 Copying .\test\db.opt to d:\data\backup\test\db.opt
[01] 2020-09-13 16:39:42         ...done
[01] 2020-09-13 16:39:42 Copying .\test\student_details.frm to d:\data\backup\test\student_details.frm
[01] 2020-09-13 16:39:42         ...done
[01] 2020-09-13 16:39:42 Copying .\test\student_tests.frm to d:\data\backup\test\student_tests.frm
[01] 2020-09-13 16:39:42         ...done
[01] 2020-09-13 16:39:43 Copying .\test\table_name.frm to d:\data\backup\test\table_name.frm
[01] 2020-09-13 16:39:43         ...done
[00] 2020-09-13 16:39:43 Finished backing up non-InnoDB tables and files
[01] 2020-09-13 16:39:43 Copying .\aria_log.00000001 to d:\data\backup\aria_log.00000001
[01] 2020-09-13 16:39:43         ...done
[01] 2020-09-13 16:39:43 Copying .\aria_log_control to d:\data\backup\aria_log_control
[01] 2020-09-13 16:39:43         ...done
[00] 2020-09-13 16:39:43 Waiting for log copy thread to read lsn 64467
[00] 2020-09-13 16:39:43 >> log scanned up to (64467)
[00] 2020-09-13 16:39:43 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
[00] 2020-09-13 16:39:43 mariabackup: The latest check point (for incremental): '64455'
mariabackup: Stopping log copying thread.[00] 2020-09-13 16:39:43 >> log scanned up to (64467)

[00] 2020-09-13 16:39:43 >> log scanned up to (64467)
[00] 2020-09-13 16:39:43 Executing BACKUP STAGE END
[00] 2020-09-13 16:39:43 All tables unlocked
[00] 2020-09-13 16:39:43 Copying ib_buffer_pool to d:\data\backup\ib_buffer_pool
[00] 2020-09-13 16:39:43         ...done
[00] 2020-09-13 16:39:43 Backup created in directory 'd:\data\backup\'
[00] 2020-09-13 16:39:43 Writing backup-my.cnf
[00] 2020-09-13 16:39:43         ...done
[00] 2020-09-13 16:39:43 Writing xtrabackup_info
[00] 2020-09-13 16:39:43         ...done
[00] 2020-09-13 16:39:43 Redo log (from LSN 64455 to 64467) was copied.
[00] 2020-09-13 16:39:43 completed OK!

因为是自带的工具，所以直接用cmd命令进入到MariaDB的bin目录执行即可，在执行命令前请确保备份目录是存在的。

图中左边是备份目录，右边是原始目录。其实在我们备份的时候，可能只关注我们自己的业务数据库，那么我们可以指定数据进行备份

mariabackup --backup --target-dir d:/data/backup/ --databases test  --user root --password root

现在我们就来把导出的test数据还原一下

在开始之前，我们了解一些EXPLAIN是怎么用的。从官方文档我们可以看到它的语法结构是这样的:

EXPLAIN tbl_name

EXPLAIN [EXTENDED | PARTITIONS] 
  {SELECT select_options | UPDATE update_options | DELETE delete_options}

我们先来看看第一个是什么作用，

我们可以看到它和DESCRIBE tbl_name,SHOW COLUMNS FROM tbl_name的作用是一样的，那么如果是后面跟select、update、delete这样的SQL语句呢？

可以看到这里给我们列出了这条SQL的一些执行信息，下面，我们把这些列的用途列出来

我只是记录我的学习过程，由于书读得少，可能很多地方表述或者是理解得不对，请轻喷并指正。