首页 > 代码库 > 42-1 mysql备份与恢复
42-1 mysql备份与恢复
01 mysql备份与恢复
1、mysqldump备份数据库(使用场合：1G以下的数据库)
1）单个数据库
[root@www1 ~]# mysqldump -uroot --databases hellodb > /root/helldb01.sql
2）多个数据库
[root@www1 ~]# mysqldump -uroot --databases hellodb mydb > /root/hellodb_mydb.sql
3）备份全部数据库
[root@www1 ~]# mysqldump -uroot --all-databases --lock-all-tables --master-data=http://www.mamicode.com/2 > /root/all.sql
2、灾难恢复事例：（本示例有误，需要整理）
原服务器：192.168.1.73
恢复服务器：192.168.1.72
原服务器：执行插入、删除操作；
MariaDB [(none)]> use hellodb;
MariaDB [hellodb]> desc students;
#插入一条记录
MariaDB [hellodb]> insert into students (Name,Age,Gender,ClassID,TeacherID) VALUES (‘Chao Cai‘,47,‘M‘,3,7);
MariaDB [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 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 |
| 26 | Chao Cai | 47 | M | 3 | 7 |
+-------+---------------+-----+--------+---------+-----------+
26 rows in set (0.03 sec)
#删除某一条记录
MariaDB [hellodb]> DELETE FROM students WHERE StuID=3;
还原服务器：先使用mysqldump备份文件还原，再使用二进制日志文件还原
[root@source ~]# scp all.sql 192.168.1.72:/root
[root@restore ~]# yum -y install mariadb-server
[root@restore ~]# systemctl start mariadb.service
[root@restore ~]# mysql
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
MariaDB [(none)]> \q
[root@restore ~]# mysql < all.sql
[root@restore ~]# mysql
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mydb |
| mysql |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.00 sec)
MariaDB [(none)]> use hellodb;
MariaDB [hellodb]> SELECT * FROM students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 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 |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)
查看备份文件，查到MASTER_LOG_POS的值，并记下
[root@restore ~]# less all.sql
-- MySQL dump 10.14 Distrib 5.5.50-MariaDB, for Linux (x86_64)
--
-- Host: localhost Database:
-- ------------------------------------------------------
-- Server version 5.5.50-MariaDB
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE=‘+00:00‘ */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=‘NO_AUTO_VALUE_ON_ZERO‘ */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Position to start replication or point-in-time recovery from
--
-- CHANGE MASTER TO MASTER_LOG_FILE=‘mysql-bin.000001‘, MASTER_LOG_POS=245;
--
-- Current Database: `hellodb`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `hellodb` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `hellodb`;
--
这里的值为：MASTER_LOG_POS=245
[root@source ~]# mysqlbinlog --start-position=245 /var/lib/mysql/mysql-bin.000001 > incre.sql
[root@source ~]# scp incre.sql 192.168.1.72:/root
[root@restore ~]# mysql < incre.sql
[root@restore ~]# mysql
MariaDB [(none)]> use hellodb;
MariaDB [hellodb]> SELECT * FROM students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 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 |
| 26 | Chao Cai | 47 | M | 3 | 7 |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)
数据恢复完成
3、 基于lvm2实现热备（本示例待验证）
配置mariadb存储的lvm2分区
[root@source ~]# fdisk /dev/sda
Welcome to fdisk (util-linux 2.23.2).
Changes will remain in memory only, until you decide to write them.
Be careful before using the write command.
Command (m for help): n
Partition type:
p primary (2 primary, 0 extended, 2 free)
e extended
Select (default p):
Using default response p
Partition number (3,4, default 3):
First sector (51374080-251658239, default 51374080):
Using default value 51374080
Last sector, +sectors or +size{K,M,G} (51374080-251658239, default 251658239): +10G
Partition 3 of type Linux and of size 10 GiB is set
Command (m for help): w
The partition table has been altered!
Calling ioctl() to re-read partition table.
WARNING: Re-reading the partition table failed with error 16: Device or resource busy.
The kernel still uses the old table. The new table will be used at
the next reboot or after you run partprobe(8) or kpartx(8)
Syncing disks.
[root@source ~]# partx -a /dev/sda
[root@source ~]# pvcreate /dev/sda3
Physical volume "/dev/sda3" successfully created
[root@source ~]# vgcreate myvg /dev/sda3
Volume group "myvg" successfully created
[root@source ~]# lvcreate -L +5G -n mydata myvg
Logical volume "mydata" created.
[root@source ~]# mkfs.xfs /dev/myvg/mydata
[root@source ~]# systemctl stop mariadb.service
[root@source ~]# mount /dev/myvg/mydata /data/
[root@source ~]# mkdir -p /data/mysql
[root@source ~]# chown -R mysql.mysql /data/mysql/
[root@source ~]# vim /etc/my.cnf
修改
datadir=/var/lib/mysql
为
datadir=/data/mysql
[root@source ~]# systemctl start mariadb.service
[root@source ~]# mysql < all.sql
[root@source ~]# mysql -e ‘SHOW MASTER STATUS‘ > /root/pos.`date +%F`
[root@source ~]# cat pos.2016-11-02
File Position Binlog_Do_DB Binlog_Ignore_DB
mysql-bin.000002 245
#对数据库作lvm2备份
[root@source ~]# lvcreate -L 500M -s -n mydata-snap -p r /dev/myvg/mydata
Logical volume "mydata-snap" created.
#对数据库作修改操作
MariaDB [(none)]> UNLOCK TABLES;
MariaDB [(none)]> use hellodb;
MariaDB [hellodb]> DELETE FROM students WHERE StuID=5;
MariaDB [hellodb]> DELETE FROM students WHERE StuID=11;
MariaDB [hellodb]> SELECT * FROM students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 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 |
| 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 |
| 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 |
+-------+---------------+-----+--------+---------+-----------+
23 rows in set (0.00 sec)
[root@source ~]# systemctl stop mariadb.service
[root@source ~]# lvremove /dev/myvg/mydata-snap
[root@source ~]# umount /dev/myvg/mydata
[root@source ~]# mke2fs -t ext4 /dev/myvg/mydata
[root@source ~]# mount /dev/myvg/mydata /data/
[root@source ~]# mkdir /data/{mysql,binlogs}
[root@source ~]# chown -R mysql.mysql /data/*
[root@source ~]# vim /etc/my.cnf.d/server.cnf
在[server]行（9行左右）添加
log_bin=/data/binlogs/mysql-bin
[root@source ~]# systemctl start mariadb.service
MariaDB [(none)]> SET sql_log_bin=0;
MariaDB [(none)]> source /root/all.sql
MariaDB [test]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mydb |
| mysql |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.00 sec)
MariaDB [test]> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 245 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> SET sql_log_bin=1;
MariaDB [test]> FLUSH TABLES WITH READ LOCK;
[root@source ~]# mysql -e ‘flush logs;‘
[root@source ~]# mysql -e "SHOW MASTER STATUS;"
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 | 245 | | |
+------------------+----------+--------------+------------------+
[root@source ~]# mysql -e "SHOW MASTER STATUS;" > /root/pos-`date +%F`
[root@source ~]# cat pos-2016-11-03
File Position Binlog_Do_DB Binlog_Ignore_DB
mysql-bin.000004 245
[root@source ~]# lvcreate -L 2G -n mydata-snap -s -p r /dev/myvg/mydata Logical volume "mydata-snap" created.
MariaDB [test]> UNLOCK TABLES;
MariaDB [hellodb]> DELETE FROM students WHERE StuID=18;
Query OK, 1 row affected (0.01 sec)
MariaDB [hellodb]> SELECT * FROM students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 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 |
| 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 |
| 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 |
+-------+---------------+-----+--------+---------+-----------+
23 rows in set (0.01 sec)
[root@source ~]# mount -r /dev/myvg/mydata-snap /mnt
[root@source ~]# ls /mnt/
binlogs lost+found mysql
[root@source ~]# cd /mnt/
[root@source mnt]# ls mysql/
aria_log.00000001 hellodb ib_logfile0 mydb performance_schema
aria_log_control ibdata1 ib_logfile1 mysql test
[root@source mnt]# ls binlogs/
mysql-bin.000001 mysql-bin.000003 mysql-bin.index
mysql-bin.000002 mysql-bin.000004
[root@source mnt]# cp -a mysql/ /tmp/
[root@source mnt]# cp -a /data/binlogs/ /tmp/
[root@source mnt]# systemctl stop mariadb.service
#模拟误删除数据库中数据
[root@source mnt]# rm -rf /data/mysql/*
[root@source mnt]# rm -rf /data/binlogs/*
#恢复
[root@source mnt]# cp -a /tmp/mysql/* /data/mysql/
[root@source mnt]# cp -a /tmp/binlogs/* /data/binlogs/
[root@source mnt]# systemctl start mariadb.service
[root@source mnt]# mysql
MariaDB [(none)]> SHOW BINARY LOGS;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 30334 |
| mysql-bin.000002 | 1038814 |
| mysql-bin.000003 | 288 |
| mysql-bin.000004 | 245 |
| mysql-bin.000005 | 245 |
+------------------+-----------+
MariaDB [(none)]> use hellodb;
MariaDB [hellodb]> SELECT * FROM students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 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 |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)
#数据恢复到备份时的数据；
MariaDB [hellodb]> \q
Bye
[root@source ~]# cat pos-2016-11-03
File Position Binlog_Do_DB Binlog_Ignore_DB
mysql-bin.000004 245
[root@source ~]# cd /tmp/
[root@source ~]# cd /tmp/binlogs/
[root@source binlogs]# ls
mysql-bin.000001 mysql-bin.000003 mysql-bin.index
mysql-bin.000002 mysql-bin.000004
[root@source binlogs]# mysqlbinlog --start-position=245 mysql-bin.000004 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#161103 13:08:44 server id 1 end_log_pos 245 Start: binlog v 4, server v 5.5.50-MariaDB created 161103 13:08:44
# Warning: this binlog is either in use or was not closed properly.
BINLOG ‘
XMYaWA8BAAAA8QAAAPUAAAABAAQANS41LjUwLU1hcmlhREIAbG9nAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAA2QAEGggAAAAICAgCAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAA9VR4dg==
‘/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
本文出自 “追梦” 博客，请务必保留此出处http://sihua.blog.51cto.com/377227/1875522
42-1 mysql备份与恢复