MySQL备份工具

末蓝、 2022-01-12 14:17 575阅读 0赞

# MySQL备份工具 #

**常用的 MySQL 备份工具**  
• 物理备份缺点  
– 跨平台性差  
– 备份时间长、冗余备份、浪费存储空间  
**• mysqldump 备份缺点**  
– 效率较低，备份和还原速度慢  
– 备份过程中，数据插入和更新操作会被挂起  
**XtraBackup 工具**  
• 一款强大的在线热备份工具  
– 备份过程中不锁库表，适合生产环境  
– 由专业组织 Percona 提供（改进 MySQL 分  
支）  
**• 主要含两个组件**  
– xtrabackup ： C 程序，支持 InnoDB/XtraDB  
– innobackupex ：以 Perl 脚本封装 xtrabackup ，还支持 MyISAM  
**步骤一：安装XtraBackup软件包**  
1）了解软件包描述信息

[root@dbsvr1 pub]# rpm -qpi percona-xtrabackup-24-2.4.6-2.el7.x86_64.rpm
    Name        : percona-xtrabackup-24
    Version     : 2.4.6
    Release     : 2.el7
    Architecture: x86_64
    Install Date: (not installed)
    Group       : Applications/Databases
    Size        : 32416340
    License     : GPLv2
    Signature   : DSA/SHA1, 2017年02月27日 星期一 20时28分17秒, Key ID 1c4cbdcdcd2efd2a
    Source RPM  : percona-xtrabackup-24-2.4.6-2.el7.src.rpm
    Build Date  : 2017年02月27日 星期一 20时27分21秒
    Build Host  : vps-centos7-x64-01.ci.percona.com
    Relocations : (not relocatable)
    URL         : http://www.percona.com/software/percona-xtrabackup
    Summary     : XtraBackup online backup for MySQL / InnoDB
    Description :
    Percona XtraBackup is OpenSource online (non-blockable) backup solution for InnoDB and XtraDB engines

2）安装依赖包perl-DBD-MySQL perl-Digest-MD5 libev  
使用RHEL 7自带的即可，yum方式安装：  
\`\[root@dbsvr1 pub\]\# yum -y install perl-DBD-MySQL perl-Digest-MD5  
libev使用网上找的rpm包 libev-4.15-1.el6.rf.x86.rpm  
\[root@dbsvr1 pub\]\#rpm –ivh libev-4.15-1.el6.rf.x86\_64.rpm\`\`

如果未安装这些依赖包，则直接安装percona-xtrabackup时会报错：
    3）安装percona-xtrabackup

\[root@dbsvr1 pub\]\#rpm -ivh percona-xtrabackup-\*.rpm  
警告：percona-xtrabackup-24-2.4.6-2.el7.x86\_64.rpm: 头V4 DSA/SHA1 Signature, 密钥 ID cd2efd2a: NOKEY  
准备中... \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# \[100%\]  
正在升级/安装...  
1:percona-xtrabackup-24-2.4.6-2.el7\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# \[ 33%\]  
2:percona-xtrabackup-test-24-2.4.6-\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# \[ 67%\]  
3:percona-xtrabackup-24-debuginfo-2\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# \[100%\]

4）确认安装的主要程序/脚本

\[root@dbsvr1 pub\]\# rpm -ql percona-xtrabackup-24-2.4.6-2.el7.x86\_64  
/usr/bin/innobackupex  
/usr/bin/xbcloud  
/usr/bin/xbcloud\_osenv  
/usr/bin/xbcrypt  
/usr/bin/xbstream  
/usr/bin/xtrabackup  
/usr/share/doc/percona-xtrabackup-24-2.4.6  
/usr/share/doc/percona-xtrabackup-24-2.4.6/COPYING  
/usr/share/man/man1/innobackupex.1.gz  
/usr/share/man/man1/xbcrypt.1.gz  
/usr/share/man/man1/xbstream.1.gz  
/usr/share/man/man1/xtrabackup.1.gz  
\`步骤二：innobackupex完整备份、增量备份操作

\--host 主机名

\--port 3306

\--user 用户名

\--password 密码

\--databases="库名"

\--databases="库1 库2"

\--databases="库.表"

\--no-timestamp 不用日期命名备份文件存储的子目录，使用备份的数据库名做备份目录名

\--no-timestmap 不使用日期命名备份目录名\`\`  
1）做一个完整备份

默认情况下，备份文件存储的子目录会用日期命名，

innobackupex作为客户端工具，以mysql协议连入mysqld，将数据备份到/backup文件夹

[root@dbsvr1 ~]# innobackupex --user=root --password=1234567 /backup/mysql –no-timestamp
    170425 11:05:44 innobackupex: Starting the backup operation
    IMPORTANT: Please check that the backup run completes successfully.
               At the end of a successful backup run innobackupex
               prints "completed OK!".
    Unrecognized character \x01; marked by <-- HERE after <-- HERE near column 1 at - line 1374.
    170425 11:05:45 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set
    Using server version 5.7.17
    innobackupex version 2.4.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 8ec05b7)
    xtrabackup: uses posix_fadvise().
    xtrabackup: cd to /var/lib/mysql
    xtrabackup: open files limit requested 0, set to 1024
    xtrabackup: using the following InnoDB configuration:
    xtrabackup:   innodb_data_home_dir = .
    xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
    xtrabackup:   innodb_log_group_home_dir = ./
    xtrabackup:   innodb_log_files_in_group = 2
    xtrabackup:   innodb_log_file_size = 50331648
    InnoDB: Number of pools: 1
    170425 11:05:45 >> log scanned up to (2543893)
    xtrabackup: Generating a list of tablespaces
    InnoDB: Allocated tablespace ID 2 for mysql/plugin, old maximum was 0
    170425 11:05:45 [01] Copying ./ibdata1 to /backup/ibdata1
    170425 11:05:45 [01]        ...done
    170425 11:05:46 [01] Copying ./mysql/plugin.ibd to /backup/mysql/plugin.ibd
    170425 11:05:46 [01]        ...done
    170425 11:05:46 [01] Copying ./mysql/servers.ibd to /backup/mysql/servers.ibd
    170425 11:05:46 [01]        ...done
    170425 11:05:46 [01] Copying ./mysql/help_topic.ibd to /backup/mysql/help_topic.ibd
    170425 11:05:46 [01]        ...done
    170425 11:05:46 >> log scanned up to (2543893)
    .. ..
    170425 11:06:00 [01] Copying ./sys/x@0024waits_global_by_latency.frm to /backup/sys/x@0024waits_global_by_latency.frm
    170425 11:06:00 [01]        ...done
    170425 11:06:00 [01] Copying ./sys/session_ssl_status.frm to /backup/sys/session_ssl_status.frm
    170425 11:06:00 [01]        ...done
    170425 11:06:00 [01] Copying ./db1/db.opt to /backup/db1/db.opt
    170425 11:06:00 [01]        ...done
    170425 11:06:00 [01] Copying ./db1/tb1.frm to /backup/db1/tb1.frm
    170425 11:06:00 [01]        ...done
    170425 11:06:00 Finished backing up non-InnoDB tables and files
    170425 11:06:00 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
    xtrabackup: The latest check point (for incremental): '2543884'
    xtrabackup: Stopping log copying thread.
    .170425 11:06:00 >> log scanned up to (2543893)
    170425 11:06:00 Executing UNLOCK TABLES
    170425 11:06:00 All tables unlocked
    170425 11:06:00 [00] Copying ib_buffer_pool to /backup/ib_buffer_pool
    170425 11:06:00 [00]        ...done
    170425 11:06:00 Backup created in directory '/backup/'
    170425 11:06:00 [00] Writing backup-my.cnf
    170425 11:06:00 [00]        ...done
    170425 11:06:00 [00] Writing xtrabackup_info
    170425 11:06:00 [00]        ...done
    xtrabackup: Transaction log of lsn (2543884) to (2543893) was copied.
    170425 11:06:01 completed OK

确认备份好的文件数据

[root@dbsvr1 ~]#ls /backup/
    backup-my.cnf  ib_buffer_pool  mysql      sys                   xtrabackup_info
    db1  ibdata1      performance_schema  xtrabackup_checkpoints  xtrabackup_logfile

2）做一个增量备份（基于前一步的完整备份）

随意做一些新增或更改库表的操作，比如在db1库中新建一个mytb的表：

mysql> USE db1;
    Database changed
    mysql> CREATE TABLE mytb(id int(4), name varchar(24));
    Query OK, 0 rows affected (0.38 sec)
    mysql> INSERT INTO tb1 VALUES
        -> (1,'bon'),
        -> (2,'bo'),
    Query OK, 2 rows affected (0.12 sec)
    Records: 2  Duplicates: 0  Warnings: 0
    mysql> SELECT * FROM tb1;
    +------+------+
    | id   | name |
    +------+------+
    |    1 | bob  |
    |    2 | bo   |
    +------+------+
    2 rows in set (0.00 sec)

以前一次保存到/backup的完整备份为基础，做一个增量备份，保存到/incr01/，指定增量备份参照的基本目录（完整备份目录）需要用到选项--incremental-basedir。相关操作如下

[root@dbsvr1 ~]# innobackupex  --user=root --password=12345678 --incremental /incr01 --incremental-basedir=/backup/ --no-timestamp
    170425 11:30:14 innobackupex: Starting the backup operation
    IMPORTANT: Please check that the backup run completes successfully.
               At the end of a successful backup run innobackupex
               prints "completed OK!".
    Unrecognized character \x01; marked by <-- HERE after <-- HERE near column 1 at - line 1374.
    170425 11:30:14 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set
    Using server version 5.7.17
    innobackupex version 2.4.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 8ec05b7)
    incremental backup from 2543884 is enabled.
    xtrabackup: uses posix_fadvise().
    xtrabackup: cd to /var/lib/mysql
    xtrabackup: open files limit requested 0, set to 1024
    xtrabackup: using the following InnoDB configuration:
    xtrabackup:   innodb_data_home_dir = .
    xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
    xtrabackup:   innodb_log_group_home_dir = ./
    xtrabackup:   innodb_log_files_in_group = 2
    xtrabackup:   innodb_log_file_size = 50331648
    InnoDB: Number of pools: 1
    170425 11:30:14 >> log scanned up to (2549933)
    xtrabackup: Generating a list of tablespaces
    InnoDB: Allocated tablespace ID 2 for mysql/plugin, old maximum was 0
    xtrabackup: using the full scan for incremental backup
    170425 11:30:15 [01] Copying ./ibdata1 to /incr01/ibdata1.delta
    170425 11:30:15 [01]        ...done
    170425 11:30:15 >> log scanned up to (2549933)
    170425 11:30:15 [01] Copying ./mysql/plugin.ibd to /incr01/mysql/plugin.ibd.delta
    170425 11:30:15 [01]        ...done
    ... ...
    170425 11:30:35 Executing UNLOCK TABLES
    170425 11:30:35 All tables unlocked
    170425 11:30:35 [00] Copying ib_buffer_pool to /incr01/ib_buffer_pool
    170425 11:30:35 [00]        ...done
    170425 11:30:35 Backup created in directory '/incr01/'
    170425 11:30:35 [00] Writing backup-my.cnf
    170425 11:30:35 [00]        ...done
    170425 11:30:35 [00] Writing xtrabackup_info
    170425 11:30:35 [00]        ...done
    xtrabackup: Transaction log of lsn (2549924) to (2549933) was copied.
    170425 11:30:35 completed OK!

确认备份好的文件数据：  
\[root@dbsvr1 ~\]\# ls /incr01/  
backup-my.cnf ib\_buffer\_pool ibdata1.meta performance\_schema xtrabackup\_checkpoints xtrabackup\_logfile  
db1 ibdata1.delta mysql sys  
对比完整备份、增量备份的大小：

[root@dbsvr1 ~]# du -sh /backup/ /incr01/
    142M    /backup/                              //完整备份的大小
    3.5M    /incr01/    
    ```                          //增量备份的大小
    步骤三：恢复数据
    
    通过XtraBackup工具备份的数据库目录，若要恢复到另一个MySQL服务器，需要先做一个“--apply-log --redo-only ”的准备操作。
    
    1）准备恢复“完整备份”
    
    完成准备以后，最终/backup可用来重建MySQL服务器。这种情况下，需要先做一个“--apply-log --redo-only ”的准备操作，以确保数据一致性：

\[root@dbsvr1 ~\]\#innobackupex --user=root --password=12345678 --apply-log --redo-only /backup/  
170425 11:42:19 innobackupex: Starting the apply-log operation  
IMPORTANT: Please check that the apply-log run completes successfully.  
At the end of a successful apply-log run innobackupex  
prints "completed OK!".  
innobackupex version 2.4.6 based on MySQL server 5.7.13 Linux (x86\_64) (revision id: 8ec05b7)  
xtrabackup: cd to /backup/  
xtrabackup: This target seems to be already prepared.  
InnoDB: Number of pools: 1  
xtrabackup: notice: xtrabackup\_logfile was already used to '--prepare'.  
xtrabackup: using the following InnoDB configuration for recovery:  
xtrabackup: innodb\_data\_home\_dir = .  
xtrabackup: innodb\_data\_file\_path = ibdata1:12M:autoextend  
xtrabackup: innodb\_log\_group\_home\_dir = .  
xtrabackup: innodb\_log\_files\_in\_group = 2  
xtrabackup: innodb\_log\_file\_size = 50331648  
xtrabackup: using the following InnoDB configuration for recovery:  
xtrabackup: innodb\_data\_home\_dir = .  
xtrabackup: innodb\_data\_file\_path = ibdata1:12M:autoextend  
xtrabackup: innodb\_log\_group\_home\_dir = .  
xtrabackup: innodb\_log\_files\_in\_group = 2  
xtrabackup: innodb\_log\_file\_size = 50331648  
xtrabackup: Starting InnoDB instance for recovery.  
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)  
InnoDB: PUNCH HOLE support available  
InnoDB: Mutexes and rw\_locks use GCC atomic builtins  
InnoDB: Uses event mutexes  
InnoDB: GCC builtin \_\_atomic\_thread\_fence() is used for memory barrier  
InnoDB: Compressed tables use zlib 1.2.7  
InnoDB: Number of pools: 1  
InnoDB: Not using CPU crc32 instructions  
InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M  
InnoDB: Completed initialization of buffer pool  
InnoDB: page\_cleaner coordinator priority: -20  
InnoDB: Highest supported file format is Barracuda.  
xtrabackup: starting shutdown with innodb\_fast\_shutdown = 1  
InnoDB: Starting shutdown...  
InnoDB: Shutdown completed; log sequence number 2544177  
InnoDB: Number of pools: 1  
170425 11:42:20 completed OK!

准备恢复“增量备份”

\[root@dbsvr1 ~\]\#innobackupex --user=root --password=12345678 --apply-log --redo-only /backup/ --incremental-dir=/incr01  
170425 11:42:55 innobackupex: Starting the apply-log operation  
IMPORTANT: Please check that the apply-log run completes successfully.  
At the end of a successful apply-log run innobackupex  
prints "completed OK!".  
innobackupex version 2.4.6 based on MySQL server 5.7.13 Linux (x86\_64) (revision id: 8ec05b7)  
incremental backup from 2543884 is enabled.  
xtrabackup: cd to /backup/  
xtrabackup: This target seems to be already prepared with --apply-log-only.  
InnoDB: Number of pools: 1  
xtrabackup: xtrabackup\_logfile detected: size=8388608, start\_lsn=(2549924)  
xtrabackup: using the following InnoDB configuration for recovery:  
xtrabackup: innodb\_data\_home\_dir = .  
xtrabackup: innodb\_data\_file\_path = ibdata1:12M:autoextend  
xtrabackup: innodb\_log\_group\_home\_dir = /incr01/  
xtrabackup: innodb\_log\_files\_in\_group = 1  
xtrabackup: innodb\_log\_file\_size = 8388608  
xtrabackup: Generating a list of tablespaces  
InnoDB: Allocated tablespace ID 2 for mysql/plugin, old maximum was 0  
xtrabackup: page size for /incr01//ibdata1.delta is 16384 bytes  
Applying /incr01//ibdata1.delta to ./ibdata1...  
... ...  
170425 11:43:09 \[01\] Copying /incr01/performance\_schema/global\_status.frm to ./performance\_schema/global\_status.frm  
170425 11:43:09 \[01\] ...done  
170425 11:43:09 \[01\] Copying /incr01/performance\_schema/session\_status.frm to ./performance\_schema/session\_status.frm  
170425 11:43:09 \[01\] ...done  
170425 11:43:09 \[00\] Copying /incr01//xtrabackup\_info to ./xtrabackup\_info  
170425 11:43:09 \[00\] ...done  
170425 11:43:10 completed OK!

2）关闭mysql服务，并将/var/lib/mysql/下的文件删除，假设数据被删除。
    
    [root@dbsvr1 ~]#systemctl  stop  mysqld
    [root@dbsvr1 ~]#rm -rf /var/lib/mysql
    3）恢复“完整备份+增量备份”
    
    完成准备以后，最终仍然是/backup用来重建MySQL服务器，但这种情况下需提前合并相关增量备份的数据

\[root@dbsvr1 ~\]\# innobackupex --user=root --password=12345678 --copy-back /backup/  
... ...  
170425 11:51:39 \[01\] Copying ./performance\_schema/global\_status.frm to /var/lib/mysql/performance\_schema/glo.frm  
170425 11:51:39 \[01\] ...done  
170425 11:51:39 \[01\] Copying ./performance\_schema/session\_status.frm to /var/lib/mysql/performance\_schema/seus.frm  
170425 11:51:39 \[01\] ...done  
170425 11:51:39 \[01\] Copying ./ib\_buffer\_pool to /var/lib/mysql/ib\_buffer\_pool  
170425 11:51:39 \[01\] ...done  
170425 11:51:39 \[01\] Copying ./ibtmp1 to /var/lib/mysql/ibtmp1  
170425 11:51:39 \[01\] ...done  
170425 11:51:39 \[01\] Copying ./xtrabackup\_info to /var/lib/mysql/xtrabackup\_info  
170425 11:51:39 \[01\] ...done  
170425 11:51:39 completed OK!

4）修改/var/lib/mysql/下文件属主与属组,查看数据：
    
    恢复后，/var/lib/mysql下文件属组与属主皆为root，需要更改为mysql

root@dbsvr1 ~\]\#chown -R mysql:mysql /var/lib/mysql  
\[root@dbsvr1 ~\]\#systemctl start mysqld.service  
\[root@dbsvr1 ~\]\#mysql -uroot -p12345678 -e "select \* from db1.tb1"  
mysql: \[Warning\] Using a password on the command line interface can be insecure.  
\+------+------+  
| id | name |  
\+------+------+  
| 1 | bob |  
| 2 | bo |  
\+------+------+

转载于:https://blog.51cto.com/14354846/2405722