前言
除了使用mysqldump对mysql进行备份，percona也提供另一个工具percona xtrabackup，也可以对数据库进行备份。操作非常简单。本文只是介绍它的全量备份及如何使用全量备份进行还原恢复数据库。
具体操作
1,数据库的当前数据
mysql> use zxydb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+-----------------+
| Tables_in_zxydb |
+-----------------+
| t_go |
| t_loop |
| t_other |
+-----------------+
3 rows in set (0.00 sec)
mysql> select * from t_go;
+---+------+
| a | b |
+---+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 5 | 5 |
+---+------+
4 rows in set (0.00 sec)
2，执行首次全量备份
2.1，构建存储全量备份的目录
mysql> system mkdir -p back_full_dir
2.2，执行首次全量备份
[root@standbygtid ~]# xtrabackup --defaults-file=/usr/my.cnf -uroot -psystem --backup --target-dir=/back_full_dir
[root@standbygtid ~]# xtrabackup --defaults-file=/usr/my.cnf -uroot -psystem --backup --target-dir=/back_full_dir
----检查需要备份的数据库的版本及相关信息
191104 14:20:47 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root' (using password: YES).
191104 14:20:47 version_check Connected to MySQL server
191104 14:20:47 version_check Executing a version check against the server...
191104 14:20:47 version_check Done.
---连接到需要备份的数据库
191104 14:20:47 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set
Using server version 5.6.25-enterprise-commercial-advanced-log
xtrabackup version 2.4.9 based on MySQL server 5.7.13 Linux (x86_64) (revision id: a467167cdd4)
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
191104 14:20:47 >> log scanned up to (28517968)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 13 for completedb/t_loop, old maximum was 0
191104 14:20:47 [01] Copying ./ibdata1 to back_full_dir/ibdata1
191104 14:20:48 [01] ...done
191104 14:20:48 [01] Copying ./completedb/t_loop.ibd to back_full_dir/completedb/t_loop.ibd
191104 14:20:48 [01] ...done
191104 14:20:48 [01] Copying ./completedb/t_other.ibd to back_full_dir/completedb/t_other.ibd
略
191104 14:20:48 [01] ...done
191104 14:20:48 [01] Copying ./zxydb/t_go.ibd to back_full_dir/zxydb/t_go.ibd
191104 14:20:48 [01] ...done
191104 14:20:48 [01] Copying ./zxydb/t_other.ibd to back_full_dir/zxydb/t_other.ibd
191104 14:20:48 [01] ...done
191104 14:20:48 >> log scanned up to (28517968)
191104 14:20:48 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
191104 14:20:48 Executing FLUSH TABLES WITH READ LOCK...
191104 14:20:48 Starting to backup non-InnoDB tables and files
191104 14:20:48 [01] Copying ./performance_schema/objects_summary_global_by_type.frm to back_full_dir/performance_schema/objects_summary_global_by_type.frm
191104 14:20:48 [01] ...done
191104 14:20:48 [01] Copying ./performance_schema/session_connect_attrs.frm to back_full_dir/performance_schema/session_connect_attrs.frm
191104 14:20:48 [01] ...done
略
191104 14:20:49 [01] Copying ./performance_schema/events_waits_current.frm to back_full_dir/performance_schema/events_waits_current.frm
191104 14:20:49 [01] ...done
191104 14:20:49 [01] Copying ./performance_schema/events_stages_summary_by_thread_by_event_name.frm to back_full_dir/performance_schema/events_stages_summary_by_thread_by_event_name.frm
191104 14:20:49 [01] ...done
191104 14:20:49 [01] Copying ./performance_schema/rwlock_instances.frm to back_full_dir/performance_schema/rwlock_instances.frm
191104 14:20:49 [01] ...done
191104 14:20:49 [01] Copying ./performance_schema/events_statements_history_long.frm to back_full_dir/performance_schema/events_statements_history_long.frm
略
191104 14:20:50 [01] ...done
191104 14:20:50 Finished backing up non-InnoDB tables and files
191104 14:20:50 [00] Writing back_full_dir/xtrabackup_binlog_info
191104 14:20:50 [00] ...done
191104 14:20:50 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '28517968'
xtrabackup: Stopping log copying thread.
.191104 14:20:50 >> log scanned up to (28517968)
191104 14:20:50 Executing UNLOCK TABLES
191104 14:20:50 All tables unlocked
191104 14:20:50 Backup created in directory '/back_full_dir/'
MySQL binlog position: filename 'binlog.000002', position '120'
191104 14:20:50 [00] Writing back_full_dir/backup-my.cnf
191104 14:20:50 [00] ...done
191104 14:20:50 [00] Writing back_full_dir/xtrabackup_info
191104 14:20:50 [00] ...done
xtrabackup: Transaction log of lsn (28517968) to (28517968) was copied.
191104 14:20:50 completed OK!
[root@standbygtid ~]#
3，关闭数据库
[root@standbygtid ~]# mysqladmin -uroot -psystem shutdown
Warning: Using a password on the command line interface can be insecure.
4，物理删除数据库的数据文件
[root@standbygtid ~]# cd var/lib/mysql/
[root@standbygtid mysql]# rm -rf *
5，执行基于全量备份的数据库恢复
[root@standbygtid mysql]# innobackupex --defaults-file=/backup_full_dir/backup-my.cnf --copy-back backup_full_dir/
191104 14:37:32 innobackupex: Starting the copy-back operation
--提示如何验证数据库恢复是否正常
IMPORTANT: Please check that the copy-back run completes successfully.
At the end of a successful copy-back run innobackupex
prints "completed OK!".
从备份目录把全量备份的数据文件原样复制回数据库的数据目录
innobackupex version 2.4.9 based on MySQL server 5.7.13 Linux (x86_64) (revision id: a467167cdd4)
191104 14:37:32 [01] Copying ib_logfile0 to var/lib/mysql/ib_logfile0
191104 14:37:33 [01] ...done
191104 14:37:33 [01] Copying ib_logfile1 to /var/lib/mysql/ib_logfile1
191104 14:37:33 [01] ...done
191104 14:37:33 [01] Copying ibdata1 to /var/lib/mysql/ibdata1
191104 14:37:33 [01] ...done
191104 14:37:33 [01] Copying ./ibtmp1 to /var/lib/mysql/ibtmp1
191104 14:37:33 [01] ...done
191104 14:37:33 [01] Copying ./xtrabackup_binlog_pos_innodb to /var/lib/mysql/xtrabackup_binlog_pos_innodb
191104 14:37:33 [01] ...done
191104 14:37:33 [01] Copying ./xtrabackup_info to /var/lib/mysql/xtrabackup_info
略
191104 14:37:35 [01] ...done
191104 14:37:35 [01] Copying ./test/db.opt to /var/lib/mysql/test/db.opt
191104 14:37:35 [01] ...done
191104 14:37:35 completed OK!
6，需要重新授权数据目录为mysql
[root@standbygtid mysql]# pwd
/var/lib/mysql
[root@standbygtid mysql]# ll
总用量 188448
drwxr-x--- 2 root root 4096 11月 4 14:37 completedb
-rw-r----- 1 root root 79691776 11月 4 14:37 ibdata1
-rw-r----- 1 root root 50331648 11月 4 14:37 ib_logfile0
-rw-r----- 1 root root 50331648 11月 4 14:37 ib_logfile1
-rw-r----- 1 root root 12582912 11月 4 14:37 ibtmp1
drwxr-x--- 2 root root 4096 11月 4 14:37 mysql
drwxr-x--- 2 root root 4096 11月 4 14:37 performance_schema
drwxr-x--- 2 root root 4096 11月 4 14:37 test
drwxr-x--- 2 root root 4096 11月 4 14:37 xtrabackup_backupfiles
-rw-r----- 1 root root 23 11月 4 14:37 xtrabackup_binlog_pos_innodb
-rw-r----- 1 root root 544 11月 4 14:37 xtrabackup_info
drwxr-x--- 2 root root 4096 11月 4 14:37 zxydb
[root@standbygtid mysql]# chown -Rf mysql:mysql *
7,重启数据库
[root@standbygtid mysql]# nohup mysqld_safe --user=mysql&
8，验证数据库恢复是否正常
mysql> use zxydb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+-----------------+
| Tables_in_zxydb |
+-----------------+
| t_go |
| t_loop |
| t_other |
+-----------------+
3 rows in set (0.00 sec)
mysql> select * from t_go;
+---+------+
| a | b |
+---+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 5 | 5 |
+---+------+
4 rows in set (0.00 sec)
提升数据库技能
本人的职业经历
中国普天 数据库技术顾问
北京科蓝软件系统 数据库DBA
北京云和恩墨有限公司数据库技术顾问
北京神州新桥科技有限公司数据库咨询顾问
参与的重点项目
四川达州商业银行核心系统建设项目
中国联通数据库系统运维项目
贵州移动数据库系统运维项目
京东方数据库运维项目
拉卡拉数据库系统运维项目
新疆汇和银行核心系统建设项目
新疆银行新核心系统建设项目
邢台银行影印平台数据库建设项目
保定银行核心系统建设项目
重庆富民银行数据中心建设项目
吉林亿联银行数据中心建设项目
云南工行数据库建设项目
江西裕民银行数据中心建设项目
湖北发改委数据库运维项目
武汉众邦银行数据中心建设项目
辽宁振兴银行数据库运维项目
学习资料
（注：oracle方面体系化且非常实惠，仅仅20元）
相关文章
mysql 5.6备份工具mysqldump在生产环境的使用思考
学习oracle或mysql数据库的一些方法percona toolkit pt-variable-advisor验证mysql variable是否配置合理
redhat 6.5 deploying percona toolkit 3.0.9 pt-mysql-summary
联系方式
