1200字范文 > 【mysql】mysql数据备份与恢复

【mysql】mysql数据备份与恢复

时间：2024-01-09 09:45:10

相关推荐

【mysql】mysql数据备份与恢复

数据的重要性不言而喻，所以做好数据的备份工作显得尤其必要，不知不觉我又开始扯犊子了。。。。

一、逻辑备份与恢复

数据库逻辑备份就是备份软件按照最初设计的逻辑关系，以数据库的逻辑结构对象为单位，将数据库中的数据按照预定义的逻辑关联格式一条一条生成相关的文本文件，以达到备份的目的。

逻辑备份可以说是最简单，也是目前中小型系统最常使用的备份方式。

备份方式使用mysqldump进行数据备份该工具为mysql自带的备份工具位于mysql安装目录下的bin目录下

###使用mysqldump进行数据备份，如需使用其他命令参数可使用 mysqldump --help 查看[root@bf529efb00b7 data]# /usr/local/mysql/bin/mysqldump -h localhost -uroot -p cmm >/mysql/cmm.sqlEnter password: ###使用mysql 还原数据库[root@bf529efb00b7 data]# /usr/local/mysql/bin/mysql -f -h localhost -uroot -p cmm </mysql/cmm.sqlEnter password: [root@bf529efb00b7 data]# /usr/local/mysql/bin/mysql -uroot -pEnter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 18Server version: 8.0.20 MySQL Community Server - GPLCopyright (c) 2000, , Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> use cmmReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> show tables;+---------------+| Tables_in_cmm |+---------------+| test|+---------------+1 row in set (0.00 sec)mysql>

注意：在主从环境下备份前请将主库加锁以确保数据一致性进入mysql客户端执行flush tables with read lock在备份完成后执行unclock tables释放锁。

mysql> flush tables with read lock;#......mysql> unnlock tables;

二、物理备份与恢复

停止主库，然后复制主库中的data放到从库中

一般使用工具 mydumper
mydumper是一个针对MySQL和drizzle的高性能多线程的备份和恢复工具。此工具的开发人员分别来自MySQL、facebook、skysql公司、目前已经有一些大型产品业务测试并使用了该工具。我们在恢复
数据库时也可使用myloader工具。 Mydumper的主要特性包括：
· 采用轻量级C语言写的代码。
· 相比于mysqldump，其速度快了近10倍。
· 具有事务性和非事务性表一致的快照（适用于0.2.2+）。
· 可快速进行文件压缩（File compression on-the-fly）。
· 支持导出binlog。
· 可多线程恢复（适用于0.2.1+）。
· 可以用守护进程的工作方式，定时扫描和输出连续的二进制日志。

备份命令如下所示：

###安装前请先下载rpm包[root@bf529efb00b7 dd]# yum install mydumper-0.9.5-2.el7.x86_64.rpm ###由于此处我修改了sock配置重启了服务[root@bf529efb00b7 dd]# service mysql restartShutting down MySQL.. SUCCESS! Starting MySQL.. SUCCESS! ###备份方式[root@bf529efb00b7 dd]# mydumper --defaults-file=/etc/f -h localhost -u root -p 'Cmm@111111' -B cmm -o /mysql/dumper_data###如果有内容说明备份成功了[root@bf529efb00b7 dd]# ll /mysql/dumper_data/total 16-rw-r--r-- 1 root root 127 Aug 26 01:53 cmm-schema-create.sql-rw-r--r-- 1 root root 217 Aug 26 01:53 cmm.test-schema.sql-rw-r--r-- 1 root root 138 Aug 26 01:53 cmm.test.sql-rw-r--r-- 1 root root 215 Aug 26 01:53 metadata[root@bf529efb00b7 dd]# ###数据还原操作[root@bf529efb00b7 dd]# myloader -u root -p 'Cmm@111111' -B cmm -d /mysql/dumper_data** (myloader:1626): CRITICAL **: 01:59:52.435: Error restoring cmm.test from file cmm.test-schema.sql: Table 'test' already exists###表已经存在提示无法还原[root@bf529efb00b7 dd]# myloader -u root -p 'Cmm@111111' -B cmm01 -d /mysql/dumper_data[root@bf529efb00b7 dd]#

mydumper命令参数说明

· -host，-h：连接的MySQL服务器。· -user，-u：用户备份的连接用户。· -password，-p：用户的密码。· -port，-P：连接端口。· -socket，-S：连接socket文件。· -database，-B：需要备份的数据库。· -table-list，-T：需要备份的表，用逗号（，）分隔。· -outputdir，-o：输出的目录。· -build-empty-files，-e：默认无数据则只有表结构文件。· -regex，-x：支持正则表达式，如mydumper-regex'（2l（mysqltest）'。· -ignore-engines，-i：忽略的存储引擎。· -no- schemas，-m：不导出表结构。· -long-query-guard：长查询，默认60s。· -kill-long-queries，-k：可以设置kill长查询。· -verbose，-v：0=silent，1=errors， 2=warmings，3=info，默认是2。· -binlogs，-b：导出binlog。· -daemon，-D：启用守护进程模式。· -snapshot-interval，-I:dump快照间隔时间，默认60s。· -logfile，-L:mysaqldumper的目志输出，一般在Daemon模式下使用。

myloader参数说明

-d, --directory 备份文件的文件夹-q, --queries-per-transaction每次事物执行的查询数量，默认是1000-o, --overwrite-tables 如果要恢复的表存在，则先drop掉该表，使用该参数，需要备份时候要备份表结构-B, --database需要还原的数据库-e, --enable-binlog启用还原数据的二进制日志-h, --host 主机-u, --user 还原的用户-p, --password密码-P, --port 端口-S, --socket socket文件-t, --threads 还原所使用的线程数，默认是4-C, --compress-protocol 压缩协议-V, --version 显示版本-v, --verbose 输出模式, 0 = silent, 1 = errors, 2 = warnings, 3 = info, 默认为2

三、热备份及数据恢复

xtrabackup手册：/doc/percona-xtrabackup/2.4/installation/yum_repo.html
热备份的方式也是直接复制数据物理文件，和冷备份一样，但热备份可以不停机直接复制，一般用于7×24小时不间断的重要核心业务。MySQL社区版的热备份工具ImnoDB Hot Backup是付费的，只能
试用30天，只有购买企业版才可以得到永久使用权。Percona公司发布了一个xtrabackup热备份工具，和官方付费版的功能一样，支持在线热备份（备份时不影响数据读写），是商业备份工具
InnoDBHot Backup的一个很好的替代品。下面具体介绍一下这个软件的使用方法。 xtrabackup是Percona公司的开源项目，用以实现类似ImnoDB官方的热备份工具ImmoDB Hot Backup的功能，它能
非常快速地备份与恢复MySQL数据库。

xtrabackup备份原理

innobackupex启动后，会先fork一个进程，用于启动xtrabackup，然后等待xtrabackup备份ibd数据文件；xtrabackup在备份innoDB数据是，有2种线程：redo拷贝线程和ibd数据拷贝线程。xtrabackup进程开始执行后，会启动一个redo拷贝的线程，用于从最新的 checkpoint点开始顺序拷贝
redo.log；再启动ibd数据拷贝线程，进行拷贝ibd数据。这里是先启动redo拷贝线程的。在此阶段，innobackupex进行处于等待状态（等待文件被创建）xtrabackup拷贝完成ibd数据文件后，会通知innobackupex（通过创建文件），同时xtrabackup进入等待状态（redo线程依旧在拷贝redo.log）innobackupex收到xtrabackup通知后哦，执行FLUSH TABLES WITH READ LOCK（FTWRL），取得一致性位点，然后开始备份非InnoDB文件（如frm、 MYD、MYI、CSV、opt、par等格式
的文件），在拷贝非InnoDB文件的过程当中，数据库处于全局只读状态。当innobackup拷贝完所有的非InnoDB文件后，会通知xtrabackup，通知完成后，进入等待状态；xtrabackup收到innobackupex备份完成的通知后，会停止redo拷贝线程，然后通知innobackupex，redo.log文件拷贝完成；innobackupex收到redo.log备份完成后，就进行解锁操作，执行：UNLOCK TABLES；最后innbackupex和xtrabackup进程各自释放资源，写备份元数据信息等，innobackupex等xtrabackup子进程结束后退出。

操作步骤

xtrabackup的安装方法，前提是下载好xtrabackup安装包，安装及备份还原过程如下：

### 由于我本地已经下载好此工具了直接安装即可[root@bf529efb00b7 dd]# lltotal 14244-rw-r--r-- 1 root root 1688520 Jun 13 01:25 mydumper-0.9.5-2.el7.x86_64.rpm-rw-r--r-- 1 root root 12866996 Jun 13 01:25 percona-xtrabackup-80-8.0.11-1.el7.x86_64.rpm[root@bf529efb00b7 dd]# yum install percona-xtrabackup-80-8.0.11-1.el7.x86_64.rpm Loaded plugins: fastestmirror, ovlRepository base is listed more than once in the configuration[root@7b012265fdf2 dd]# xtrabackup --defaults-file=/etc/f --user=root --password=Cmm@111111 --port=3306 --backup --target-dir=/mysql/mysql_ddxtrabackup: recognized server arguments: --datadir=/mysql/data --log_bin=mysql-bin --server-id=1 xtrabackup: recognized client arguments: --port=3306 --socket=/mysql/mysql.sock --user=root --password=* --port=3306 --backup=1 --target-dir=/mysql/mysql_dd xtrabackup version 8.0.11 based on MySQL server 8.0.18 Linux (x86_64) (revision id: 486c270)26 02:19:32 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/mysql/mysql.sock' as 'root' (using password: YES).。。。。。。。。此处省略很多26 02:19:32 Connecting to MySQL server host: localhost, user: root, password: set, port: 3306, socket: /mysql/mysql.sockUnknown redo log format (4). Please follow the instructions at /doc/refman/8.0/en/ upgrading-downgrading.html.xtrabackup: Error: recv_find_max_checkpoint() failed.[root@7b012265fdf2 dd]# ###重新安装新版本xtrabackup##下载最新版本xtrabackup[root@7b012265fdf2 dd]# wget /downloads/Percona-XtraBackup-LATEST/Percona-XtraBackup-8.0.13/binary/redhat/7/x86_64/percona-xtrabackup-80-8.0.13-1.el7.x86_64.rpm[root@7b012265fdf2 dd]# lspercona-xtrabackup-80-8.0.11-1.el7.x86_64.rpm percona-xtrabackup-80-8.0.13-1.el7.x86_64.rpm###查看已安装的xtrabackup信息[root@7b012265fdf2 dd]# yum list |grep xtrabpercona-xtrabackup-80.x86_64 8.0.11-1.el7 @/percona-xtrabackup-80-8.0.11-1.el7.x86_64###删除已安装的旧版本[root@7b012265fdf2 dd]# yum remove percona-xtrabackup-80.x86_64###重新安装支持mysql8.0.20版本的[root@7b012265fdf2 dd]# yum install percona-xtrabackup-80-8.0.13-1.el7.x86_64.rpm Loaded plugins: fastestmirror, ovlExamining percona-xtrabackup-80-8.0.13-1.el7.x86_64.rpm: percona-xtrabackup-80-8.0.13-1.el7.x86_64Marking percona-xtrabackup-80-8.0.13-1.el7.x86_64.rpm to be installedResolving Dependencies--> Running transaction check---> Package percona-xtrabackup-80.x86_64 0:8.0.13-1.el7 will be installed--> Finished Dependency Resolution##。。。。。。##。。省略海量数据。##。。。。。。Transaction test succeededRunning transactionInstalling : percona-xtrabackup-80-8.0.13-1.el7.x86_64 1/1 Verifying : percona-xtrabackup-80-8.0.13-1.el7.x86_64 1/1 Installed:percona-xtrabackup-80.x86_64 0:8.0.13-1.el7 Complete!### 重新执行备份操作[root@7b012265fdf2 dd]# xtrabackup --defaults-file=/etc/f --user=root --password=Cmm@111111 --port=3306 --backup --target-dir=/mysql/mysql_ddxtrabackup: recognized server arguments: --datadir=/mysql/data --log_bin=mysql-bin --server-id=1 xtrabackup: recognized client arguments: --port=3306 --socket=/mysql/mysql.sock --user=root --password=* --port=3306 --backup=1 --target-dir=/mysql/mysql_dd xtrabackup version 8.0.13 based on MySQL server 8.0.20 Linux (x86_64) (revision id: fdf0f4c)26 02:50:56 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/mysql/mysql.sock' as 'root' (using password: YES).26 02:50:56 version_check Connected to MySQL server26 02:50:56 version_check Executing a version check against the server...26 02:50:56 version_check Done.26 02:50:56 Connecting to MySQL server host: localhost, user: root, password: set, port: 3306, socket: /mysql/mysql.sockUsing server version 8.0.20##。。。。。。##。。省略海量数据。##。。。。。。26 02:50:59 [00] ...donextrabackup: Transaction log of lsn (17681125) to (17681155) was copied.26 02:51:00 completed OK![root@7b012265fdf2 dd]# #########使用xtrabackup还原数据##############################数据目录下面存在数据时无法还原[root@7b012265fdf2 dd]# xtrabackup --defaults-file=/etc/f --copy-back --target-dir=/mysql/mysql_ddxtrabackup: recognized server arguments: --datadir=/mysql/data --log_bin=mysql-bin --server-id=1 xtrabackup: recognized client arguments: --port=3306 --socket=/mysql/mysql.sock --copy-back=1 --target-dir=/mysql/mysql_dd xtrabackup version 8.0.13 based on MySQL server 8.0.20 Linux (x86_64) (revision id: fdf0f4c)Original data directory /mysql/data is not empty!####创建一个目录用于备份原数据[root@7b012265fdf2 dd]# mkdir /mysql/data-bak[root@7b012265fdf2 dd]# mv /mysql/data/* /mysql/data-bak ###将数据目录清空后再执行还原操作 [root@7b012265fdf2 dd]# xtrabackup --defaults-file=/etc/f --copy-back --target-dir=/mysql/mysql_ddxtrabackup: recognized server arguments: --datadir=/mysql/data --log_bin=mysql-bin --server-id=1 xtrabackup: recognized client arguments: --port=3306 --socket=/mysql/mysql.sock --copy-back=1 --target-dir=/mysql/mysql_dd xtrabackup version 8.0.13 based on MySQL server 8.0.20 Linux (x86_64) (revision id: fdf0f4c)26 03:03:52 [01] Copying undo_001 to /mysql/data/undo_001##。。。。。。##。。省略海量数据。##。。。。。。26 03:03:53 [01] Copying ./cmm/test.ibd to /mysql/data/cmm/test.ibd26 03:03:53 [01] ...done26 03:03:53 completed OK!###重启mysql 服务登录查看效果[root@7b012265fdf2 dd]# mysql -uroot -p'Cmm@111111'mysql: [Warning] Using a password on the command line interface can be insecure.####省略数据。。。。。Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show databases;+--------------------+| Database |+--------------------+| abc|| cmm|| information_schema || mysql || performance_schema || sys|+--------------------+6 rows in set (0.00 sec)mysql>