42-2 mysql备份与恢复

首页 > 代码库 > 42-2 mysql备份与恢复

2024-08-22 14:29:47 218人阅读

02 mysql备份与恢复

实战：percona-xtrabackup备份还原数据库

原服务器：source 192.168.1.133 CentOS7.2

备份服务器：restore 192.168.1.132 CentOS7.2

1、使用indobackupex进行完全备份

[root@source ~]# yum install percona-xtrabackup-2.3.2-1.el7.x86_64.rpm

[root@source ~]# rpm -ql percona-xtrabackup

MariaDB [(none)]> use hellodb;

MariaDB [hellodb]> show table status \G

*************************** 1. row ***************************

Name: classes

Engine: InnoDB

Version: 10

Row_format: Compact

Rows: 8

Avg_row_length: 2048

Data_length: 16384

Max_data_length: 0

Index_length: 0

Data_free: 9437184

Auto_increment: 9

Create_time: 2016-11-19 22:01:43

Update_time: NULL

Check_time: NULL

Collation: utf8_general_ci

Checksum: NULL

Create_options:

Comment:

确保Engine为： InnoDB

[root@source ~]# systemctl stop mariadb

[root@source ~]# rm -rf /data/mysql/*

[root@source ~]# rm -rf /data/binlogs/*

[root@source ~]# vim /etc/my.cnf

添加

innodb_file_per_table=ON

[root@source ~]# systemctl start mariadb.service

[root@source ~]# mysql < all.sql

[root@source ~]# ls /data/mysql/

aria_log.00000001 hellodb ib_logfile0 mydb performance_schema

aria_log_control ibdata1 ib_logfile1 mysql test

[root@source ~]# ls /data/mysql/hellodb/

classes.frm coc.ibd db.opt students.frm teachers.ibd

classes.ibd courses.frm scores.frm students.ibd toc.frm

coc.frm courses.ibd scores.ibd teachers.frm toc.ibd

[root@source ~]# mkdir /backups #创建备份目录

MariaDB [(none)]> SHOW BINARY LOGS;

+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

| mysql-bin.000001 | 30334 |

| mysql-bin.000002 | 1038814 |

| mysql-bin.000003 | 523472 |

+------------------+-----------+

[root@source ~]# innobackupex --user=root /backups/

[root@source ~]# ls /backups/2016-11-20_12-04-51/

backup-my.cnf mydb test xtrabackup_info

hellodb mysql xtrabackup_binlog_info xtrabackup_logfile

ibdata1 performance_schema xtrabackup_checkpoints

[root@restore ~]# systemctl stop mariadb.service

[root@restore ~]# vim /etc/my.cnf

修改

datadir=/var/lib/mysql

为

datadir=/data/mysql

添加

innodb_file_per_table=ON

[root@restore ~]# mkdir -p /data/mysql

[root@restore ~]# chown mysql.mysql /data/mysql/

[root@restore ~]# systemctl start mariadb.service

[root@restore ~]# yum -y install percona-xtrabackup-2.3.2-1.el7.x86_64.rpm

[root@source ~]# scp -r /backups/2016-11-20_12-04-51/ 192.168.1.132:/root

[root@restore ~]# mkdir /backups

[root@restore ~]# mv 2016-11-20_12-04-51/ /backups/

#整理

[root@restore ~]# innobackupex --apply-log /backups/2016-11-20_12-04-51/

[root@restore ~]# systemctl stop mariadb.service

[root@restore ~]# rm -rf /data/mysql/*

[root@restore ~]# innobackupex --copy-back /backups/2016-11-20_12-04-51/

[root@restore ~]# chown -R mysql.mysql /data/mysql/*

[root@restore ~]# rm -f /data/mysql/ib_logfile*

[root@restore ~]# systemctl start mariadb.service

MariaDB [(none)]> SHOW DATABASES;

+--------------------+

| Database |

+--------------------+

| information_schema |

| hellodb |

| mydb |

| mysql |

| performance_schema |

| test |

+--------------------+

MariaDB [(none)]> USE hellodb;

MariaDB [hellodb]> SHOW TABLES;

+-------------------+

| Tables_in_hellodb |

+-------------------+

| classes |

| coc |

| courses |

| scores |

| students |

| teachers |

| toc |

+-------------------+

MariaDB [hellodb]> SELECT * FROM students;

+-------+---------------+-----+--------+---------+-----------+

+-------+---------------+-----+--------+---------+-----------+

| 1 | Shi Zhongyu | 22 | M | 2 | 3 |

| 2 | Shi Potian | 22 | M | 1 | 7 |

| 3 | Xie Yanke | 53 | M | 2 | 16 |

| 4 | Ding Dian | 32 | M | 4 | 4 |

| 5 | Yu Yutong | 26 | M | 3 | 1 |

| 6 | Shi Qing | 46 | M | 5 | NULL |

| 7 | Xi Ren | 19 | F | 3 | NULL |

| 8 | Lin Daiyu | 17 | F | 7 | NULL |

| 9 | Ren Yingying | 20 | F | 6 | NULL |

| 10 | Yue Lingshan | 19 | F | 3 | NULL |

| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |

| 12 | Wen Qingqing | 19 | F | 1 | NULL |

| 13 | Tian Boguang | 33 | M | 2 | NULL |

| 14 | Lu Wushuang | 17 | F | 3 | NULL |

| 15 | Duan Yu | 19 | M | 4 | NULL |

| 16 | Xu Zhu | 21 | M | 1 | NULL |

| 17 | Lin Chong | 25 | M | 4 | NULL |

| 18 | Hua Rong | 23 | M | 7 | NULL |

| 19 | Xue Baochai | 18 | F | 6 | NULL |

| 20 | Diao Chan | 19 | F | 7 | NULL |

| 21 | Huang Yueying | 22 | F | 6 | NULL |

| 22 | Xiao Qiao | 20 | F | 1 | NULL |

| 23 | Ma Chao | 23 | M | 4 | NULL |

| 24 | Xu Xian | 27 | M | NULL | NULL |

| 25 | Sun Dasheng | 100 | M | NULL | NULL |

+-------+---------------+-----+--------+---------+-----------+

2、使用innodbbackupex进行增量备份

[root@source ~]# mysq

MariaDB [(none)]> use hellodb;

MariaDB [hellodb]> CREATE TABLE testtb (id int);

MariaDB [hellodb]> INSERT INTO testtb VALUES (1),(10),(99);

MariaDB [hellodb]> SELECT * FROM testtb;

+------+

| id |

+------+

| 1 |

| 10 |

| 99 |

+------+

MariaDB [hellodb]> \q

#进行完全备份

[root@source ~]# innobackupex /backups/

[root@source ~]# ls /backups/

2016-11-20_12-55-15

[root@source ~]# mysql

MariaDB [(none)]> use hellodb;

MariaDB [hellodb]> SHOW TABLES;

+-------------------+

| Tables_in_hellodb |

+-------------------+

| classes |

| coc |

| courses |

| scores |

| students |

| teachers |

| testtb |

| toc |

+-------------------+

MariaDB [hellodb]> DROP TABLE coc;

MariaDB [hellodb]> INSERT INTO testtb VALUES (44),(32);

MariaDB [hellodb]> \q

#增量备份

[root@source ~]# innobackupex --incremental /backups/ --incremental-base=/backups/2016-11-20_12-55-15

#查看备份结果

[root@source ~]# less /backups/2016-11-20_13-08-46/xtrabackup_checkpoints

backup_type = incremental

from_lsn = 1655411

to_lsn = 1658073

last_lsn = 1658073

compact = 0

recover_binlog_info = 0

[root@source ~]# systemctl stop mariadb.service

#整理完全备份

[root@source ~]# innobackupex --apply-log --redo-only /backups/2016-11-20_12-55-15/

#整理增量备份并合并入原来的完全备份

[root@source ~]# innobackupex --apply-log --redo-only /backups/2016-11-20_12-55-15/ --incremental-dir=/backups/2016-11-20_13-08-46

#删除原来数据库

[root@source ~]# rm -rf /data/mysql/*

[root@source ~]# rm -rf /data/binlogs/*

#用刚才的备份还原数据库

[root@source ~]# innobackupex --copy-back /backups/2016-11-20_12-55-15/

[root@source ~]# cd /data/mysql/

[root@source mysql]# ll -h

total 115M

drwx------ 2 root root 4.0K Nov 20 16:38 hellodb

-rw-r----- 1 root root 18M Nov 20 16:38 ibdata1

-rw-r----- 1 root root 48M Nov 20 16:38 ib_logfile0

-rw-r----- 1 root root 48M Nov 20 16:38 ib_logfile1

drwx------ 2 root root 4.0K Nov 20 16:38 mydb

drwx------ 2 root root 4.0K Nov 20 16:38 mysql

drwx------ 2 root root 4.0K Nov 20 16:38 performance_schema

drwx------ 2 root root 4.0K Nov 20 16:38 test

-rw-r----- 1 root root 38 Nov 20 16:38 xtrabackup_binlog_pos_innodb

-rw-r----- 1 root root 445 Nov 20 16:38 xtrabackup_info

[root@source mysql]# chown -R mysql.mysql ./*

[root@source mysql]# systemctl start mariadb.service

[root@source mysql]# mysql

MariaDB [(none)]> USE hellodb;

MariaDB [hellodb]> SELECT * FROM testtb;

+------+

| id |

+------+

| 1 |

| 10 |

| 99 |

| 44 |

| 32 |

+------+

3、导出单张表

#完全备份

[root@source mysql]# innobackupex /backups/

[root@source mysql]# innobackupex --apply-log --export /backups/2016-11-20_16-47-57/

[root@source mysql]# cd /backups/2016-11-20_16-47-57/

[root@source 2016-11-20_16-47-57]# cd hellodb/

[root@source hellodb]# ls

classes.cfg courses.exp scores.exp students.frm teachers.ibd toc.cfg

classes.exp courses.frm scores.frm students.ibd testtb.cfg toc.exp

classes.frm courses.ibd scores.ibd teachers.cfg testtb.exp toc.frm

classes.ibd db.opt students.cfg teachers.exp testtb.frm toc.ibd

courses.cfg scores.cfg students.exp teachers.frm testtb.ibd

[root@restore ~]# mysql

MariaDB [(none)]> use mydb;

MariaDB [mydb]> SHOW TABLES;

+----------------+

| Tables_in_mydb |

+----------------+

| t1 |

| t2 |

| tb1 |

+----------------+

MariaDB [mydb]> CREATE TABLE `students` ( `StuID` int(10) unsigned NOT NULL AUTO_INCREMENT, `Name` varchar(50) NOT NULL, `Age` tinyint(3) unsigned NOT NULL, `Gender` enum(‘F‘,‘M‘) NOT NULL, `ClassID` tinyint(3) unsigned DEFAULT NULL, `TeacherID` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`StuID`) ) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8;

MariaDB [mydb]> desc students;

+-----------+---------------------+------+-----+---------+----------------+

+-----------+---------------------+------+-----+---------+----------------+

| StuID | int(10) unsigned | NO | PRI | NULL | auto_increment |

| Name | varchar(50) | NO | | NULL | |

| Age | tinyint(3) unsigned | NO | | NULL | |

| Gender | enum(‘F‘,‘M‘) | NO | | NULL | |

| ClassID | tinyint(3) unsigned | YES | | NULL | |

| TeacherID | int(10) unsigned | YES | | NULL | |

+-----------+---------------------+------+-----+---------+----------------+

MariaDB [mydb]> ALTER TABLE students DISCARD TABLESPACE;

MariaDB [mydb]> \q

Bye

[root@restore ~]# cd /data/mysql/

[root@restore mysql]# ls

aria_log.00000001 ib_logfile0 performance_schema

aria_log_control ib_logfile1 test

hellodb mydb xtrabackup_binlog_pos_innodb

ibdata1 mysql xtrabackup_info

[root@restore mysql]# cd mydb/

[root@restore mydb]# ls

db.opt students.frm t1.frm t1.ibd t2.frm t2.ibd tb1.frm tb1.ibd

[root@source ~]# cd /backups/2016-11-20_16-47-57/hellodb/

[root@source hellodb]# ls

classes.cfg courses.exp scores.exp students.frm teachers.ibd toc.cfg

classes.exp courses.frm scores.frm students.ibd testtb.cfg toc.exp

classes.frm courses.ibd scores.ibd teachers.cfg testtb.exp toc.frm

classes.ibd db.opt students.cfg teachers.exp testtb.frm toc.ibd

courses.cfg scores.cfg students.exp teachers.frm testtb.ibd

[root@source hellodb]# scp students.exp students.ibd 192.168.1.132:/data/mysql/mydb/

[root@restore mydb]# chown mysql.mysql students.*

测试结果：数据导出成功，导入不成功！

本文出自 “追梦” 博客，请务必保留此出处http://sihua.blog.51cto.com/377227/1875524

42-2 mysql备份与恢复

声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉：投诉/举报工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。

联系
我们

首页 > 代码库 > 42-2 mysql备份与恢复

42-2 mysql备份与恢复

看完仍有疑问？有类似问题直接问程序猿