Day05.Mysql备份与恢复

物理备份
rm -rf /var/lib/mysql/*
systemctl stop mysqld
rm -rf /var/lib/mysql
把刚才的备份文件拷贝到的当前库下
scp 192.168.4.50:/root/mysql.bak /var/lib/mysql/
重启正常

重新初始化数据库
1、停数据库服务
systemctl stop mysqld
2、删除库
rm -rf /var/lib/mysql/*
3、重新启动,mysql数据库会自己检测
[[email protected] mysql]# grep password /var/log/mysqld.log | tail
2018-09-13T02:33:34.691299Z 0 [Note] Shutting down plugin ‘sha256_password’
2018-09-13T02:33:34.691305Z 0 [Note] Shutting down plugin ‘mysql_native_password’
2018-09-13T02:33:36.106363Z 0 [ERROR] unknown variable ‘validate_password_policy=0’
2018-09-13T02:33:37.992951Z 0 [Note] Shutting down plugin ‘sha256_password’
2018-09-13T02:33:37.992957Z 0 [Note] Shutting down plugin ‘mysql_native_password’
2018-09-13T02:33:41.240994Z 0 [Note] Shutting down plugin ‘sha256_password’
2018-09-13T02:33:41.240996Z 0 [Note] Shutting down plugin ‘mysql_native_password’
2018-09-13T02:48:15.931350Z 0 [Note] Shutting down plugin ‘sha256_password’
2018-09-13T02:48:15.931352Z 0 [Note] Shutting down plugin ‘mysql_native_password’
2018-09-13T02:48:27.688311Z 1 [Note] A temporary password is generated for [email protected]: p>su6#SVqixP

使用 innobackupex完全备份与恢复
• 应用示例
– 将所有库完全备份到 /backup
[[email protected] ~]# innobackupex –user root –password
123456 /backup –no-timestamp // 完全备份
[[email protected] ~]# innobackupex –user root –password 123456
–apply-log /backup // 同步日志
[[email protected] ~]# rm -rf /var/lib/mysql // 恢复时要求空的库目录
[[email protected] ~]# mkdir /var/lib/mysql
[[email protected] ~]# innobackupex –user root –password 123456
–copy-back /backup // 恢复数据
[[email protected] ~]# chown -R mysql:mysql /var/lib/mysql
[[email protected] ~]# systemctl stop mysqld
[[email protected] ~]# systemctl start mysqld
[[email protected] ~]# mysql -uroot -p123456
mysql> show databases;

代码如下
第一步,准备素材,创建一个数据库
mysql> create database test;
Query OK, 1 row affected (10.08 sec)
mysql> exit

第二步,安装软件
[[email protected] ~]# yum install -y libev-4.15-1.el6.rf.x86_64.rpm 1/1
已安装:
libev.x86_64 0:4.15-1.el6.rf

[[email protected] ~]# yum install -y percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm
已安装:
percona-xtrabackup-24.x86_64 0:2.4.7-1.el7
作为依赖被安装:
perl-Digest.noarch 0:1.17-245.el7 perl-Digest-MD5.x86_64 0:2.52-3.el7
完毕!

第三步,执行完全备份
[[email protected] ~]# innobackupex –user root –password 123456 /backup –no-timestamp
180914 09:51:12 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!”.
第四步
同步日志
[[email protected] ~]# innobackupex –user root –password 123456 –apply-log /backup
180914 09:51:46 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!”.

删除库
[[email protected] ~]# systemctl stop mysqld
[[email protected] ~]# rm -rf /var/lib/mysql-
[[email protected] ~]# mkdir /var/lib/mysql

拷贝日志
[[email protected] ~]# innobackupex –user root –password 123456 –copy-back /backup
180914 09:52:46 innobackupex: Starting the copy-back operation

给数据库文件夹授权
[[email protected] ~]# chown -R mysql.mysql /var/lib/mysql

登陆查看确认
[[email protected] ~]# mysql -uroot -p123456
mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+——————–+

增量备份

增量备份与恢复
– 必须先有一次完全备份 , 备份到 /allbak
– 第 1 次增量备份到 /new1
– 第 2 次增量备份到 /new2
#innobackupex –user root –password 123456 \
–databases =” 库名列表 ” /fullbak –no-timestamp // 完全备份
#innobackupex –user root –password 123456 \
–databases =” 库名列表” –incremental /new1 \
–incremental-basedir=”/fullbak” –no-timestamp // 第 1 次增量备份
#innobackupex –user root –password 123456 \
–databases=” 库名列表 ” –incremental /new2 \
–incremental-basedir=”/new1″ –no-timestamp // 第 2 次增量备份

代码如下
第一步,先完全备份
[[email protected] ~]# innobackupex –user root –password 123456 /fullbak –no-timestamp
180914 10:22:39 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!”.
#######################################
注意观察文件结尾的号码
180914 10:22:50 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS…
xtrabackup: The latest check point (for incremental): ‘2537019’
xtrabackup: Stopping log copying thread.
.180914 10:22:50 >> log scanned up to (2537028)

180914 10:22:50 Executing UNLOCK TABLES
180914 10:22:50 All tables unlocked
180914 10:22:50 [00] Copying ib_buffer_pool to /fullbak/ib_buffer_pool
180914 10:22:50 [00] …done
180914 10:22:51 Backup created in directory ‘/fullbak/’
MySQL binlog position: filename ‘master51.000001’, position ‘316’
180914 10:22:51 [00] Writing backup-my.cnf
180914 10:22:51 [00] …done
180914 10:22:51 [00] Writing xtrabackup_info
180914 10:22:51 [00] …done
xtrabackup: Transaction log of lsn (2537019) to (2537028) was copied.
180914 10:22:51 completed OK!
######################################

第二步,第一次增量备份
备份前,先整点数据进去
[[email protected] ~]# mysql -uroot -p123456
mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
| test1 |
+——————–+
6 rows in set (0.00 sec)

mysql> create database zengliang1;
Query OK, 1 row affected (0.08 sec)

mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
| test1 |
| zengliang1 |
+——————–+
7 rows in set (0.00 sec)

执行增量备份
[[email protected] ~]# innobackupex –user root –password 123456 –incremental /new1 –incremental-basedir=”/fullbak” –no-timestamp
180914 10:26: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!”.
################################################
注意观察数字
180914 10:26:56 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS…
xtrabackup: The latest check point (for incremental): ‘2537019’
xtrabackup: Stopping log copying thread.
.180914 10:26:56 >> log scanned up to (2537028)

180914 10:26:56 Executing UNLOCK TABLES
180914 10:26:56 All tables unlocked
180914 10:26:56 [00] Copying ib_buffer_pool to /new1/ib_buffer_pool
180914 10:26:56 [00] …done
180914 10:26:57 Backup created in directory ‘/new1/’
MySQL binlog position: filename ‘master51.000001’, position ‘493’
180914 10:26:57 [00] Writing backup-my.cnf
180914 10:26:57 [00] …done
180914 10:26:57 [00] Writing xtrabackup_info
180914 10:26:57 [00] …done
xtrabackup: Transaction log of lsn (2537019) to (2537028) was copied.
180914 10:26:57 completed OK!
###############################################

第三步,第2次增量备份
老规矩,备份之前先搞点数据进去
[[email protected] ~]# mysql -uroot -p123456
mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
| test1 |
| zengliang1 |
+——————–+
7 rows in set (0.00 sec)

mysql> create database zengliang2;
Query OK, 1 row affected (0.05 sec)

mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
| test1 |
| zengliang1 |
| zengliang2 |
+——————–+
8 rows in set (0.00 sec)

执行第2此增量备份
[[email protected] ~]# innobackupex –user root –password 123456 –incremental /new2 –incremental-basedir=”/new1″ –no-timestamp
180914 10:40:30 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!”.
#######################################################################
观察下数字
180914 10:40:42 >> log scanned up to (2537028)
180914 10:40:42 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS…
xtrabackup: The latest check point (for incremental): ‘2537019’
xtrabackup: Stopping log copying thread.
.180914 10:40:42 >> log scanned up to (2537028)

180914 10:40:43 Executing UNLOCK TABLES
180914 10:40:43 All tables unlocked
180914 10:40:43 [00] Copying ib_buffer_pool to /new2/ib_buffer_pool
180914 10:40:43 [00] …done
180914 10:40:43 Backup created in directory ‘/new2/’
MySQL binlog position: filename ‘master51.000001’, position ‘670’
180914 10:40:43 [00] Writing backup-my.cnf
180914 10:40:43 [00] …done
180914 10:40:43 [00] Writing xtrabackup_info
180914 10:40:43 [00] …done
xtrabackup: Transaction log of lsn (2537019) to (2537028) was copied.
180914 10:40:43 completed OK!
######################################################################

增量备份与恢复(续 1 )
#rm -rf /var/lib/mysql ; mkdir /var/lib/mysql/
#innobackupex –user root –password 123456 –databases =” 库名列表 ” –apply-log /fullbak // 恢复完全备份
#innobackupex –user root –password 123456 –databases=“ 库名列表” –apply-log –redo-only /fullbak –incremental-dir=”/new1″ // 恢复增量
#innobackupex –user root –password 123456 –databases=“ 库名列表 ” –apply-log –redo-only /fullbak–incremental-dir=”/new2″ // 恢复增量
#innobackupex –user root –password 123456 –databases=“ 库名列表 ” –copy-back /fullbak // 拷贝文件
#chown -R mysql:mysql /var/lib/mysql/
#systemctl stop mysqld ; systemctl start mysqld \在完全备份文件中恢复单个表

– 完全备份数据库到 /allbak 目录
– 导出表信息

[[email protected] ~]#innobackupex –user root –password 123456 –databases=”gamedb” /allbak –no-timestamp
mysql> drop table gamedb.a;
[[email protected] ~]#innobackupex –user root –password 123456 –databases=”gamedb” –apply-log –export /allbak // 导出表信息
mysql> create table gamedb.a(id int); // 创建表
mysql> alter table gamedb.a discard tablespace; // 删除表空间在完全备份文件中恢复单个表
mysql> system cp /allbak/gamedb/a.{ibd,cfg,exp}
/var/lib/mysql/gamedb// 拷贝表信息文件

mysql> system chown mysql:mysql /var/lib/mysql/bbsdb/a.*
// 修改所有者
mysql> alter table gamedb.a import tablespace; // 导入表空间
mysql> select * from gamedb.a;
+——+
| id |
+——+
| 1001 |
| 1002 |
+——+

代码如下
本次测试,一次一次的恢复,来展示效果
恢复完全备份,先回滚日志
[[email protected] ~]# innobackupex –user=root –password=123456 –apply-log /fullbak
180914 10:47:26 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!”.
##############################################################
注意观察下面的数字
#########################################################
InnoDB: Starting crash recovery.
InnoDB: Removed temporary tablespace data file: “ibtmp1”
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file ‘./ibtmp1’ size to 12 MB. Physically writing the file full; Please wait …
InnoDB: File ‘./ibtmp1’ size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB: 5.7.13 started; log sequence number 2537493
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown…
InnoDB: Shutdown completed; log sequence number 2537512
180914 10:47:32 completed OK!

回滚完成后,拷贝数据
[[email protected] ~]# innobackupex –user root –password 123456 –copy-back /fullbak
180914 10:48:52 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!”.
############################################
180914 10:49:04 completed OK!

授权,并重启服务
[[email protected] ~]# chown -R mysql.mysql /var/lib/mysql
[[email protected] ~]# systemctl start mysqld

[[email protected] ~]# mysql -uroot -p123456

mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
| test1 |
+——————–+
6 rows in set (0.00 sec)

第一份增量备份恢复
老规矩,先回滚日志
[[email protected] ~]# innobackupex –user root –password 123456 –apply-log-only –redo-only /fullbak –incremental-dir=”/new1″180914 11:26:06 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!”.

180914 11:26:06 version_check Connecting to MySQL server with DSN ‘dbi:mysql:;mysql_read_default_group=xtrabackup’ as ‘root’ (using password: YES).
180914 11:26:06 version_check Connected to MySQL server
180914 11:26:06 version_check Executing a version check against the server…
180914 11:26:06 version_check Done.
180914 11:26:06 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set
Using server version 5.7.17-log
innobackupex version 2.4.7 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 6f7a799)
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
180914 11:26:06 >> log scanned up to (2537540)
#########################################################################3
注意观察数字
####################################################################
180914 11:26:17 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS…
xtrabackup: The latest check point (for incremental): ‘2537531’
xtrabackup: Stopping log copying thread.
.180914 11:26:17 >> log scanned up to (2537540)

180914 11:26:18 Executing UNLOCK TABLES
180914 11:26:18 All tables unlocked
180914 11:26:18 [00] Copying ib_buffer_pool to /fullbak/2018-09-14_11-26-06/ib_buffer_pool
180914 11:26:18 [00] …done
180914 11:26:18 Backup created in directory ‘/fullbak/2018-09-14_11-26-06/’
MySQL binlog position: filename ‘master51.000001’, position ‘154’
180914 11:26:18 [00] Writing backup-my.cnf
180914 11:26:18 [00] …done
180914 11:26:18 [00] Writing xtrabackup_info
180914 11:26:18 [00] …done
xtrabackup: Transaction log of lsn (2537531) to (2537540) was copied.
180914 11:26:18 completed OK!

再停止服务,
[[email protected] ~]# systemctl stop mysqld
[[email protected] ~]# rm -rf /var/lib/mysql

拷贝数据
[[email protected] ~]# innobackupex –user root –password 123456 –copy-back /fullbak
180914 11:30:21 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!”.
180914 11:32:41 completed OK!

登陆查看一下
(注意,刚才备份的时候忘记加上 no-timestamp,所以显示的是数字)
[[email protected] ~]# chown -R mysql.mysql /var/lib/mysql
[[email protected] ~]# systemctl start mysqld
[[email protected] ~]# mysql -uroot -p123456
mysql> show databases;
+——————————+
| Database |
+——————————+
| information_schema |
| #mysql50#2018-09-14_11-26-06 |
| mysql |
| performance_schema |
| sys |
| test |
| test1 |
+——————————+
7 rows in set (0.00 sec)

第二份增量备份恢复
跟上面一样,先回滚日志,再拷贝数据,授权,重启,登陆测试
代码如下
[[email protected] ~]# innobackupex –user root –password 123456 –apply-log –redo-only /fullbak –incremental-dir=/new2
180914 11:39:47 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.7 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 6f7a799)
incremental backup from 2537019 is enabled.
xtrabackup: cd to /fullbak/
xtrabackup: This target seems to be already prepared.
xtrabackup: error: applying incremental backup needs target prepared with –apply-log-only.
[[email protected] ~]#
[[email protected] ~]# innobackupex –user root –password 123456 –apply-log-only –redo-only /fullbak –incremental-dir=/new2
180914 11:39:53 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!”.
##############################################################################
注意观察数字
180914 11:40:05 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS…
xtrabackup: The latest check point (for incremental): ‘2537531’
xtrabackup: Stopping log copying thread.
.180914 11:40:05 >> log scanned up to (2537540)

180914 11:40:05 Executing UNLOCK TABLES
180914 11:40:05 All tables unlocked
180914 11:40:05 [00] Copying ib_buffer_pool to /fullbak/2018-09-14_11-39-53/ib_buffer_pool
180914 11:40:05 [00] …done
180914 11:40:05 Backup created in directory ‘/fullbak/2018-09-14_11-39-53/’
MySQL binlog position: filename ‘master51.000001’, position ‘154’
180914 11:40:05 [00] Writing backup-my.cnf
180914 11:40:05 [00] …done
180914 11:40:05 [00] Writing xtrabackup_info
180914 11:40:05 [00] …done
xtrabackup: Transaction log of lsn (2537531) to (2537540) was copied.
180914 11:40:06 completed OK!

[[email protected] ~]# rm -rf /var/lib/mysql

回滚数据
[[email protected] ~]# innobackupex –user root –password 123456 –copy-back /fullbak
180914 11:40:37 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!”.

重启服务,登陆测试一下
[[email protected] ~]# chown -R mysql.mysql /var/lib/mysql
[[email protected] ~]#
[[email protected] ~]# systemctl start mysqld
[[email protected] ~]# systemctl restart mysqld

[[email protected] ~]# mysql -uroot -p123456

mysql> show databases;
+——————————+
| Database |
+——————————+
| information_schema |
| #mysql50#2018-09-14_11-26-06 |
| #mysql50#2018-09-14_11-39-53 |
| mysql |
| performance_schema |
| sys |
| test |
| test1 |
+——————————+
8 rows in set (0.00 sec)

此条目发表在mysql分类目录,贴了标签。将固定链接加入收藏夹。

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注