mysql数据库备份操作

数据库备份

文章目录

• 数据库备份
• • 一、mysqldump备份
  • • • 1、备份所有数据库
      • 2、备份指定数据库
      • 3、备份指定数据库指定表
      • 二、mysqldump还原
      • • • 1、mysql外还原
          • 2、soure方法
          • 三、使用Gtid通过二进制日志进行数据还原
          • • 1、gtid（Global Transaction ID）简介
            • 2、gtid配置
            • • （1）查看gtid
              • （2）修改配置
              • 3、基于gtid截取日志
              • • （1）基于 gtid 截取日志
                • （2）gtid 截取日志实例
                • 四、MySQL物理备份 xtrabackup
                • • 1、xtrabackup 介绍
                  • 2、xtrbackup安装
                  • • (1)依赖包安装
                    • （2）xtrbackup下载安装
                    • （3）配置文件
                    • 3、xtrbackup使用
                    • • （1）用户权限说明
                      • （2）常用参数
                      • 4、xtrbackup备份和还原
                      • • （1）完整备份
                        • （2）全量还原

                          一、mysqldump备份

                          mysqldump是在数据库外在服务器内进行备份

                          1、备份所有数据库

                          mysqldump -uroot -p --all-databases > /var/lib/mysql/all.db

                          2、备份指定数据库

                          mysqldump -uroot -p test2 > /var/lib/mysql/test2.db

                          3、备份指定数据库指定表

                          mysqldump -uroot -p mysql db event > /var/lib/mysql/2table.db

                          二、mysqldump还原

                          1、mysql外还原

                          mysqladmin -uroot -p create test3mysql -uroot -p test3  
                          2、soure方法
                           
                          在mysql内还原
                           
                          create database test3;use test3;source /var/lib/mysql/test2.db
                            
                          三、使用Gtid通过二进制日志进行数据还原
                           
                          1、gtid（Global Transaction ID）简介
                           
                          全局唯一的事务编号。
                           
                          幂等性。
                           
                          GtID包括两部分：
                           
                          ​ Server_uuid：
                           
                          ​ Tx_id：
                           
                          2、gtid配置
                           
                          （1）查看gtid
                           
                          show variables like '%gtid%';
                            
                          （2）修改配置
                           
                          vim /etc/my.cnf
                           
                          gtid_mode=on#开启gtidenforce_gtid_consistency=true#强制GTID一致性log_slave_updates=1   #主从复制中从库记录 binlog，并统一GTID信息
                           
                          systemcat restart mysqld
                           
                          3、基于gtid截取日志
                           
                          对于 DDL和 DCL 一个操作就是一个 GTID。
                           
                          对于 DML，一个完整的事务就是已给 GTID。
                           
                          show variables like '%gtid%';
                            
                          show master status;
                            
                          我这日志到了9
                           
                          show binlog events in 'mysql-bin.000009';
                            
                          （1）基于 gtid 截取日志
                           

                          • –include-gtids= 包含
                          • –exclude-gtids= 排除
                          • –skip-gtids= 跳过

                            截取1-3号事务

                            mysqlbinlog --include-gtids='57c3d0f1-1f99-11ee-a697-000c29938b7f:1-3' /var/lib/mysql/mysql-bin.000001>/var/lib/mysql/gtid1-3.sql

                            （2）gtid 截取日志实例

                            • 准备环境

                              create database gtid charset utf8mb4;use gtid;create table t1(id int) engine=innodb charset=utf8mb4;insert into t1 values(1),(2),(3);commit;insert into t1 values(11),(12),(13);commit;select * from t1;

                              • 删除表

                                drop database gtid;show databases;

                                • 找起点和gtid

                                  show master status;show binlog events in 'mysql-bin.000009';

                                  • 截取日志

                                    mysqlbinlog --skip-gtids --include-gtids='57c3d0f1-1f99-11ee-a697-000c29938b7f:16-21' /var/lib/mysql/mysql-bin.000009 > /var/lib/mysql/gtid.sql

                                    • 恢复数据

                                      set sql_log_bin=0;source /var/lib/mysql/gtid1.sqlshow databases;

                                      四、MySQL物理备份 xtrabackup

                                      1、xtrabackup 介绍

                                      Xtrabackup 是一个开源的免费的热备工具，在 Xtrabackup 包中主要有 Xtrabackup 和innobackupex 两个工具。其中 Xtrabackup 只能备份 InnoDB 和 XtraDB 两种引擎; innobackupex则是封装了Xtrabackup，同时增加了备份MyISAM引擎的功能。

                                      Xtrabackup备份时不能备份表结构、触发器等等，也不能智能区分.idb 数据文件。另外innobackupex还不能完全支持增量备份，需要和xtrabackup结合起来实现全备的功能。

                                      2、xtrbackup安装

                                      (1)依赖包安装

                                      依赖包下载，下载地址

                                      导入到服务器中安装

                                      rpm -ivh libev-4.04-2.el6.x86_64.rpm

                                      （2）xtrbackup下载安装

                                      下载地址

                                      导入到服务器中安装

                                      tar -xf Percona-XtraBackup-8.0.32-26-r34cf2908-el9-x86_64-bundle.taryum -y install percona-xtrabackup-80.x86_64

                                      （3）配置文件

                                      修改配置文件/etc/my.cnf，保证[mysqld]模块存在参数datadir=/var/lib/mysql（指向数据目录），因xtrbackup是根据/etc/my.cnf配置文件来获取需要备份的文件。

                                      3、xtrbackup使用

                                      （1）用户权限说明

                                      备份数据库时会涉及到两个用户：系统用户与数据库内部的用户。

                                      • 系统用户

                                        需要在 datadir（配置文件内设置的目录）上具有读写执行权限（rwx）

                                        • 数据库内部用户

                                          RELOAD 和 LOCK TABLES 权限，执行 FLUSH TABLES WITH READ LOCK；

                                          REPLICATION CLIENT 权限，获取 binary log（二进制日志文件）位置；

                                          CREATE TABLESPACE权限，导入表，用户表级别的恢复；

                                          SUPER权限，在slave环境下备份用来启动和关闭slave线程。

                                          （2）常用参数

                                          --user# 以什么用户身份进行操作--password# 数据库用户的密码--port# 数据库的端口号，默认3306--stream# 打包（数据流）--defaults-file# 指定默认配置文件，默认读取/etc/my.cnf--no-timestamp# 不创建时间戳文件，而改用目的地址以自动创建）--copy-back# 备份还原的主要选项--incremental# 使用增量备份，默认使用的 整备份--incremental-basedir# 与--incremental选项联合，该参数指定上一级备份的地址来做增量备份

                                          4、xtrbackup备份和还原

                                          （1）完整备份

                                          xtrabackup --host=127.0.0.1 --user=root --password=Admin@123.. --port=3306 --backup --target-dir=/var/lib/mysql/backup  --no-server-version-check

                                          查看备份信息

                                          cat xtrabackup_checkpoints

                                          （2）全量还原

                                          xtrabackup --prepare --target-dir=/var/lib/mysql/backup --incremental-dir=/var/lib/mysql/backup