首页 > 代码库 > MySQL备份和还原系列二：cp冷备份 和 mysqldump
MySQL备份和还原系列二：cp冷备份 和 mysqldump
如果要备份，请确保 mysql 打开 log-bin，有了 binarylog，mysql 才可以在必要的时候做完整恢复，或基于时间点的恢复，或基于位置的恢复。
我的数据存放目录为：
mysql> SHOW VARIABLES LIKE ‘%datadir%‘; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | datadir | /var/lib/mysql/ | +---------------+-----------------+
1、建立目录用于存放二进制日志
[root@localhost ~]# mkdir /mybinlog [root@localhost ~]# chown mysql:mysql /mybinlog
2、编辑配置文件，启用二进制日志
[root@localhost ~]# vi /etc/my.cnf ### 二进制日志目录及文件名前缀 log-bin = /mybinlog/mysql-bin ## 然后重启mysqld服务 [root@localhost ~]# service mysqld restart
3、查看生成的binlog日志
[root@localhost ~]# ls /mybinlog/ mysql-bin.000001 mysql-bin.index
4、准备测试数据
mysql> CREATE DATABASE mydb; mysql> USE mydb; ### 创建 myisam 引擎的表 mysql> CREATE TABLE myisam_tbl( -> id INT NOT NULL AUTO_INCREMENT, -> name VARCHAR(50), -> PRIMARY KEY(id) -> ) ENGINE=myisam DEFAULT charset=utf8; mysql> INSERT INTO myisam_tbl(name) -> VALUES(‘one‘),(‘joy‘),(‘li‘),(‘tom‘),(‘jerry‘),(‘hello‘); ### 造数据的好办法 mysql> INSERT INTO myisam_tbl(name) -> SELECT name FROM myisam_tbl; ### 创建innodb引擎的表 mysql> CREATE TABLE innodb_tbl( -> id INT NOT NULL AUTO_INCREMENT, -> name VARCHAR(50), -> PRIMARY KEY(id) -> ) engine = innodb DEFAULT charset=utf8; # 把myisam_tbl的数据弄到innodb_tbl表 mysql> INSERT INTO innodb_tbl(name) -> SELECT name FROM myisam_tbl;
一、直接拷贝数据库文件（文件系统备份工具 cp），物理备份（适合小型数据库）
标准流程：锁表->刷新表到磁盘->停止服务->拷贝文件->解锁
冷备份步骤：
备份：
1.停掉 mysql 服务，在操作系统级别备份 mysql 的数据文件。
2.重启 mysql 服务，备份重启以后生成的 binlog。
恢复：
1.停掉 mysql 服务，在操作系统级别恢复 mysql 的数据文件。
2.重启 mysql 服务，使用 mysqlbinlog 恢复自备份以来的 binlog。
1、在终端1
# 刷新，打开读锁 mysql> FLUSH TABLES WITH READ LOCK; Query OK, 0 rows affected (0.00 sec)
2、在终端2
# 创建备份目录 [root@localhost ~]# mkdir /mnt/mysql-$(date +%F) # 以归档模式拷贝所有的数据文件 [root@localhost ~]# cp -a /var/lib/mysql/* /mnt/mysql-2014-07-21/
3、回到第一个终端
# 解锁 mysql> UNLOCK TABLES; Query OK, 0 rows affected (0.10 sec)
4、模拟数据库损坏， 删除原来的所有数据文件
[root@localhost ~]# rm -rf /var/lib/mysql/* # 然后关闭Mysql服务 [root@localhost ~]# service mysqld stop ### 我是通过yum安装的mysql，是可以顺利停掉服务的。 # 如果不能停掉 ERROR! MySQL server PID file could not be found! ， 那么 [root@localhost ~]# ps -ef | grep mysql [root@localhost ~]# killall mysqld ###进行数据库初始化 #1 编译安装的 ，请根据实际情况指定参数 [root@localhost ~]# /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/mydata/data/ --user=mysql #2 yum安装的 [root@localhost ~]# /usr/bin/mysql_install_db # 初始化后，我们的数据已经丢失 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | test | +--------------------+ 3 rows in set (0.00 sec)
5、恢复过程，复制完全备份的数据文件到数据目录中
# \cp 转义别名，不然复制时老是提醒是否覆盖 [root@localhost ~]# \cp -a /mnt/mysql-2014-07-21/* /var/lib/mysql/ # 启动Mysql [root@localhost ~]# service mysqld start mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mydb | | mysql | | test | +--------------------+ mysql> USE mydb; Database changed mysql> show tables; +-----------------+ | Tables_in_mydb | +-----------------+ | innodb_tbl | | myisam_tbl | | test_myisam_tbl | +-----------------+ 3 rows in set (0.00 sec)
OK, 我们看到数据已经恢复。cp命令，对其进行的备份，速度快，还原速度几乎最快，但是灵活度很低，可以跨系统，但是跨平台能力很差，适合小型数据库备份！
二、mysqldump逻辑备份数据库（完全备份+增加备份，速度相对较慢，适合中小型数据库）（MyISAM是温备份，InnoDB是热备份）
逻辑备份步骤：
备份：
1.选择在系统空闲时，比如在夜间，使用 mysqldump –F(flush-logs)备份数据
库。
# mysqldump –u root –p*** pointcard –F > pointcard.sql
2.并备份 mysqldump 开始以后生成的 binlog。
恢复：
1. 停掉应用，执行 mysql 导入备份文件.
mysql –u root –p*** pointcard < pointcard.sql
2. 使用 mysqlbinlog 恢复自 mysqldump 备份以来的 binlog。
mysqlbinlog $HOME/data/mysql-bin.123456 | mysql -u root –p***
mysqldump -u -h -p --all-databases --databases <dbname> --events #备份事件 --flush-logs #在数据库导出之前先执行FLUSH LOGS --lock-all-tables #锁定所有表 --lock-tables #锁定某些表 --master-data=http://www.mamicode.com/n #(指定备份点:log fiel name and position)>由于mysqldump针对不同的引擎有所差异，所以务必清楚引擎类型
温备:
在使用MyISAM引擎中，只能使用温备份，这时候要防止数据的写入，所以先加上读锁。这时候可以进入数据库手动加读锁。这样比较麻烦，在mysqldump工具中直接有一个加锁的选项
# mysqldump --databases mydb --lock-all-tables --flush-logs> /tmp/backup-`date +%F-%H-%M`.sql ### 备份所有库（完全备份） # mysqldump --all-databases --lock-all-tables --routines --triggers --events --master-data=http://www.mamicode.com/2 --flush-logs > /root/mybackup/`date +%F-%H-%M`.full.sql如果是针对某张表备份，只要在数据库名称后面加上表名称就行了
# mysqldump -uroot -p mydb course > /root/mydb.sql 备份表course # mysqldump -uroot -p --databases mydb dnsdata > /root/mydb.sql 同时备份两个数据库这里注意，要实现时间点的恢复，加上--flush-logs选项，在使用备份文件恢复后，然后再基于二进制日志进行时间点的恢复
热备：
如果使用的是InnoDB引擎，就不必进行对数据库加锁的操作，加一个选项既可以进行热备份：--single-transaction# mysqldump --databases mydb --single-transaction --flush-logs --master-data=http://www.mamicode.com/2 > /tmp/backup-`date +%F-%H-%M`.sql
实验：
1、备份前的binlog日志
mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000005 | 106 | | | +------------------+----------+--------------+------------------+2、备份所有库（完全备份）
[root@localhost ~]# mysqldump -uroot -p --all-databases --lock-all-tables --routines --triggers --events --master-data=http://www.mamicode.com/2 --flush-logs > /root/mybackup/$(date +%F-%H-%M).full.sql>3、查看备份后的binlog， 主要是--flush-logs选项
mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000006 | 106 | | | +------------------+----------+--------------+------------------+4、我们向myisam_tbl表插入几条新数据
mysql> INSERT INTO myisam_tbl(name) -> VALUES(‘new-1‘), (‘new-2‘), (‘new-3‘); # 查看binlog mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000006 | 263 | | | +------------------+----------+--------------+------------------+5、备份binlog（增量备份）
[root@localhost ~]# cp /mybinlog/mysql-bin.000006 /root/mybackup/$(date +%F-%H-%M).binlog.0000066、模拟误操作（删除myisam_tbl表）
mysql> drop table myisam_tbl; Query OK, 0 rows affected (0.01 sec)7、恢复
恢复前先关闭对恢复过程的二进制日志记录，因为记录恢复语句是毫无意义的mysql> set sql_log_bin=0; -- 关闭binlog Query OK, 0 rows affected (0.00 sec) mysql> \. /root/mybackup/2014-07-21-16-53.full.sql mysql> use mydb; mysql> show tables; +-----------------+ | Tables_in_mydb | +-----------------+ | innodb_tbl | | myisam_tbl | | test_myisam_tbl | +-----------------+ mysql> SELECT * FROM myisam_tbl ORDER BY id DESC LIMIT 5; +----+-------+ | id | name | +----+-------+ | 12 | hello | | 11 | jerry | | 10 | tom | | 9 | li | | 8 | joy | +----+-------+ok，现在已经恢复到完全备份时的状态，但我们最后插入的三条数据没有恢复。
[root@localhost ~]# mysqlbinlog /root/mybackup/2014-07-21-17-02.binlog.000006 | mysql -uroot -p mydb Enter password: mysqlbinlog: unknown variable ‘default-character-set=utf8‘mysqlbinlog报错： unknown variable ‘default-character-set=utf8‘it‘s because inside the my.cnf got
default-character-set=utf8
the default-character-set is deprecated in 5.5. we should use instead:character-set-server = utf8
or add --no-defaults
mysqlbinlog --no-defaults -v logbin-log.000003 > logbin003.sql==============END=================
[root@localhost ~]# mysqlbinlog --no-defaults /root/mybackup/2014-07-21-17-02.binlog.000006 | mysql -uroot -p mydb mysql> set sql_log_bin=1; -- 打开binlog Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM myisam_tbl ORDER BY id DESC LIMIT 5; +----+-------+ | id | name | +----+-------+ | 15 | new-3 | | 14 | new-2 | | 13 | new-1 | | 12 | hello | | 11 | jerry | +----+-------+OK, 恢复成功。
时间点的恢复：
1.如果今天上午 10 点发生了误操作，可以用以下语句用备份和 binglog 将数据恢复到故
障前：
mysqlbinlog --no-defaults --stop-date="2014-07-24 9:59:59" /var/log/mysql/bin.123456 | mysql -u root –pmypwd2.跳过故障时的时间点，继续执行后面的 binlog，完成恢复
mysqlbinlog --no-defaults --start-date="2014-07-24 10:01:00" /var/log/mysql/bin.123456 | mysql -u root -pmypwd
位置恢复：
和时间点恢复类似，但是更精确，步骤如下：
mysqlbinlog --no-defaults --start-date="2014-07-24 9:55:00" --stop-date="2014-07-24 10:05:00" /var/log/mysql/bin.123456 > /tmp/mysql_restore.sql
该命令将在/tmp 目录创建小的文本文件，编辑此文件，找到出错语句前后的位置号 ，
例如前后位置号分别是 368312 和 368315。恢复了以前的备份文件后，你应从命令
行输入下面内容：
mysqlbinlog --no-defaults --stop-position="368312" /var/log/mysql/bin.123456 | mysql -u root -pmypwd mysqlbinlog --no-defaults --start-position="368315" /var/log/mysql/bin.123456 | mysql -u root -pmypwd上面的第 1 行将恢复到停止位置为止的所有事务。下一行将恢复从给定的起始位置
直到二进制日志结束的所有事务。因为 mysqlbinlog 的输出包括每个 SQL 语句记录
之前的 SET TIMESTAMP 语句，恢复的数据和相关 MySQL 日志将反应事务执行的原时
间。
注意点
恢复的时刻关闭二进制日志mysql>set sql_log_bin=0;因为这是基于逻辑备份方式，在恢复日志时会执行sql语句插入数据，而恢复时候插入数据的日志没有意义。
本文出自 “Share your knowledge” 博客，请务必保留此出处http://skypegnu1.blog.51cto.com/8991766/1529586