Mysql的数据备份和恢复

2024-04-02 19:04:59 184人浏览薄情痞子

摘要

这篇文章主要介绍“Mysql的数据备份和恢复”，在日常操作中，相信很多人在mysql的数据备份和恢复问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”Mysql的数据备份和恢复”

这篇文章主要介绍“Mysql的数据备份和恢复”，在日常操作中，相信很多人在mysql的数据备份和恢复问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”Mysql的数据备份和恢复”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！

Mysql的备份分类：
mysql的数据备份：
逻辑备份、物理备份
mysql按照数据集分类：
完全备份
增量备份
差异备份
一般这几种都是组合起来使用的，比如完全备份可以一周进行一次，增量备
份和差异备份可以一天备份一次，但是这要根据企业的业务逻辑而定，所以
合适的选择，将会在数据恢复的时候将损失降到最低。
mysql按照服务器是否停机分类：
冷备份：服务完全停止
温备份：服务不关闭，但是在备份的时候，其他的事务只有查询的操作
热备份:服务在线，不影响读写（但是非事务存储引擎的很难实现热备，但
是可以使用LVM实现几乎热备的功能）
需要备份的东西：
1.数据文件
2.日志文件（事务日志，二进制日志）
3.存储过程，存储函数，触发器
4.配置文件
日志分类：
1.事务日志：
   随机io转换成顺序IO
   事件重放（撤销）提供基础
2.事件事务:
   记录历史时间
MySQL的日志文件
1.the error log 错误日志
     错误信息，服务器进程启动或停止产生的信息，主从中添加复制的时
候，也会记录
     主配置文件启动错误日志，
     log_error=/var/log/mysqld.err（确保对mysqld有写的权限）
2.the binary log 二进制日志
     提供增量备份
     基于时间点的恢复
     复制架构提供基础
     log_bin=/data/logs/binary/changelog （默认在数据文件中）
     log_bin_index=/data/logs/relay/binarylog 定义二进制文件的索引
位置
3.the general query log 平时建议不开
      general_log=ON|OFF
      general_log_file=/PATH
4.the slow query log 慢查询日志
      slow_query_log=ON
      slow_query_log_file=/PATH
      long_query_time=NUM 定义超时时间
5.relay log 中继日志
默认Mysql不开启任何日志，但在mysqld脚本中开启了错误日志(the
error log)

mysql常用的备份工具：
mysqldump
SELECT INTO OUTFILE
mk-parallel-backup
ibbackup
mysqlhostcopy (MyISAM存储引擎的几乎冷备份，看着名字像是热备)

接下来我将借助我的一个简单的进行演示数据的备份和还原的过程：
1.使用dump来备份
mysqldump的备份是一个逻辑备份；
备份的格式如下;
#mysqldump db_name [table_name] > /PATH
当然mysqldump的备份还有很多选项：
--all-databases 备份所有的数据库
--events 备份事件
--routines 备份存储函数
--triggers 备份触发器
--extended-insert 备份扩展的插入
--skip-extended-insert 关闭备份扩展的插入
--lock-all-tables 加锁，这个是必须的
--flush-logs 刷新日志文件到磁盘中去，也是必须的
--master-data=n{0|1|2} 做复制的时候用到（2）
--no-data 只备份数据库中的表结构
--where "WHERE CLAUSE" 备份指定的数据
mysql> CREATE DATABASE luowei;
mysql> SHOW DATABASES;//查看所有的数据库
+--------------------+
| Database           |
+--------------------+
| infORMation_schema |
| luowei             |
| mysql              |
| ndodb              |
| test               |
+--------------------+
mysql> USE luowei;
mysql> CREATE TABLE st (
    -> ID INT UNIQUE AUTO_INCREMENT,
    -> Name VARCHAR(30) );
mysql> SHOW TABLES; //查看表
+------------------+
| Tables_in_luowei |
+------------------+
| st               |
+------------------+
mysql> INSERT INTO st (Name) VALUE ('a'),('b'),('c');
mysql> SELECT * FROM st; //显示表中的数据
+----+------+
| ID | Name |
+----+------+
| 1 | a    |
| 2 | b    |
| 3 | c    |
+----+------+
这是我的数据库中原始的信息，因为等会要删掉数据库和表，所以先看一下
里面的数据，方便等会验证；
[root@nfs ~]# mysqldump -uroot -p luowei st > /root/st.sql //开始
备份表st，这里没有使用任何参数的
Enter passWord:
OK，这个时候备份完了，由于mysqldump使用的是逻辑备份，所以备份下来
的是一些create、insert语句
我们可以打开看一下
[root@nfs ~]# cat /root/luoweidb | grep -v "^/" | grep -v "^$"
-- MySQL dump 10.11
--
-- Host: localhost    Database: luowei
-- ------------------------------------------------------
-- Server version 5.0.77
--
-- Current Database: `luowei`
--
CREATE DATABASE `luowei` ;
USE `luowei`;
--
-- Table structure for table `st`
--
DROP TABLE IF EXISTS `st`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `st` (
`ID` int(11) NOT NULL auto_increment,
`Name` varchar(30) default NULL,
UNIQUE KEY `ID` (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
--
-- Dumping data for table `st`
--
LOCK TABLES `st` WRITE;
INSERT INTO `st` VALUES (1,'a'),(2,'b'),(3,'c');
UNLOCK TABLES;
-- Dump completed on 2011-09-05 8:00:08
看到了吧，这里面是整个我刚做过的创建数据库，创建表，插入数据的语句
；
接下来，我们做一下数据库恢复：
[root@nfs ~]# mysql -uroot -e   "DROP DATABASE luowei;" -p //删除
数据库
Enter password:
[root@nfs ~]# mysql -uroot -e   "SHOW DATABASES;" -p //查看删除后
的数据库
Enter password:
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| ndodb              |
| test               |
+--------------------+
我把luowei的数据库给删除了，现在将进行数据库的恢复
[root@nfs ~]# mysql -uroot -p < /root/luoweidb
Enter password:
[root@nfs ~]# mysql -e "SHOW DATABASES;"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| luowei             |
| mysql              |
| ndodb              |
| test               |
+--------------------+
OK ，数据库是恢复过来了，接下来就是查看表中的信息是否完整了
[root@nfs ~]# mysql -e "SELECT * FROM luowei.st" -uroot -p
Enter password:
+----+------+
| ID | Name |
+----+------+
| 1 | a    |
| 2 | b    |
| 3 | c    |
+----+------+
Ok,一起安好，呵呵，当然其他的参数就不做详细介绍了，需要用到的话，
可以使用HELP命令寻求帮助。
下面介绍一个本机不用输入Mysql密码的方法：
#vim /root/.my.cnf
[client]
user = root
password = redhat
保存起来，下次对数据库的操作就直接使用mysql命令了，不用在输入密码
了；
接下来的实验我也就不输入密码了；

2.使用SELECT INTO OUTFILE 的方法进行备份
还是用上面的表，
[root@nfs ~]# mysql -e "SELECT * FROM luowei.st"
+----+------+
| ID | Name |
+----+------+
| 1 | a    |
| 2 | b    |
| 3 | c    |
+----+------+
如果想把ID小于等于2的备份出来，就可以使用这种方法备份了
mysql> SELECT * INTO OUTFILE '/tmp/st.bak' FROM st WHERE ID <=2;
[root@nfs ~]# cat /tmp/st.bak
1 a
2 b
我们可以看到一个文本文件，类似于表，但是这种方法只备份数据本身，不
备份表结构，所以恢复的时候，就需要按照结构重新建立一个新表，然后在
恢复
mysql> CREATE TABLE mt LIKE st; //我这里新创建一个基于st表格式的新
表mt，然后把刚备份的数据恢复到新表mt中，这也类是与恢复到原表了，呵
呵，小偷懒一下！！！
mysql> LOAD DATA INFILE '/tmp/st.bak' INTO TABLE mt;
mysql> SELECT * FROM mt;
+----+------+
| ID | Name |
+----+------+
| 1 | a    |
| 2 | b    |
+----+------+
ok了，我们现在来总结一下这两种方法，首先使用mysqldump备份的数据使
用逻辑备份的方式，备份的是一些对数据库的操作的语句，恢复简单；而使
用SELECT INTO OUTFILE这种可以备份指定的数据，有选择性的备份数据，
但是在恢复的时候就要麻烦一点，还要创建表。

3.基于LVM的快照备份：
操作步骤：
a.确保数据文件在LV上
b.对数据库中的表施加读锁
c.对数据所在的LV做快照
d.释放数据库的读锁
e.挂载快照卷，备份数据文件（对于InnoDB存储引擎，同时备份事务日志
文件）
f.删除快照
使用LVM做快照备份的条件是：
>SHOW VARIABLES LIKE 'log_binlog';处于ON
>SHOW VARIABLES LIKE 'sync_binlog'改为1
>SHOW VARIABLES LIKE 'innodb_support_xa' 处于ON的状态
LV快照备份
#mysql
>FLUSH TABLES WITH READ LOCK; //加上读锁
# lvcreate -L 50M -n mysnap -s /dev/myvg/mydata //创建快照
>UNLOCK TABLES; //解锁
#mount /dev/myvg/mysnap /mnt //挂载快照卷
#cd /mnt/data
#tar -jcf /root/mysql.tar.bz2 luowei/ ibdata1 ib_logfile* mysql-
bin.* //备份数据并压缩
#umount /mnt //卸载快照
#lvremove --force /dev/myvg/mysnap //移除快照

LV恢复
cd /usr/local/mysql
#chown -R mysql:mysql . //修改权限
#scripts/mysql_install_db --user=mysql --datadir=/mydata/data/ //
重新生成
#chown -R root .
#killall mysqld //杀掉所有关于mysql的进程
#cd /mydata/data
#tar xf /root/mysql.tar.bz2 -C ./ //把备份恢复到mysql的数据文件中
然后我们在登录到Mysql，就会发现原来的数据仍然存在了

4.使用binary日志文件实现时间点的恢复
显示当前正在使用的事务文件
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000006 |      330 |              |                  |
+------------------+----------+--------------+------------------+
mysql> FLUSH LOGS; //滚动日志文件
再查看正在使用的事务文件，然后记录下这个Position就行了
由于二进制日志文件只记录数据修改的信息，所以每次的修改对应的
Position都会改变
现在我向表中插入一行数据
mysql> INSERT INTO st (Name) VALUE ('d');
mysql> INSERT INTO st (Name) VALUE ('E');
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000007 |      553 |              |                  |
+------------------+----------+--------------+------------------+
由于我们前面已经做了一个完全备份，所有我们现在可以备份从330开始的
数据了
# mysqlbinlog --start-position 330 mysql-bin.000007
>/root/mysql.incr.sql //备份增加的二进制文件
然后我们这个时候登录到mysql
mysql>DROP DATABASE luowei;
然后恢复该数据库
# service mysqld stop
# scripts/mysql_install_db --user=mysql --datadir=/mydata/data/
# cd /mydata/data/
# tar xf /root/mysql.incr.sql -C .
# mysql < /root/mysql.incr.sql
# service mysqld start
登录到数据库，
就会发现有数据恢复正常