MySQL备份还原之一

一、备份准备工作
1.查看服务器状态：
mysql> \s
2.查看数据目录存放位置
mysql> show variables like '%datadir%';
3.修改二进制日志的存放位置
(1).建立一目录用于存放二进制日志
mkdir /mybinlog
chown mysql:mysql /mybinlog
(2).修改my.cnf
vim /etc/my.cnf
log-bin=/mybinlog/mysql-bin #二进制日志目录及文件名前缀
innodb_file_per_table = 1 #启用InnoDB表每表一文件，默认所有库使用一个表空间
(3).重新启动mysqld
service mysqld restart
4.查看新生成的binlog日志
5.准备一个test库，里面有两张表，t1表和t2表！


策略一：直接拷贝数据库文件（文件系统备份工具 cp）（适合小型数据库）
标准流程：锁表->刷新表到磁盘->拷贝文件->解锁（注,若有有可能的话，可以先停止数据库，再用cp命令准备，这样备份的数据最可靠）
具体步骤：
a.刷新表到时磁盘中并读锁
mysql> FLUSH TABLES WITH READ LOCK; 
b.打开第二个终端  
[root@mysql data]# mkdir /root/alldb.`date +%F-%H-%M-%S`/ #创建备份目录
[root@mysql data]# cp -rp /mydata/data/* /root/alldb.2013-07-22-13-46-22/ #复制所以的数据库文件
c.在第一个终端解锁
mysql> UNLOCK TABLES; #解锁
Query OK, 0 rows affected (0.01 sec)
[root@mysql ~]# ll alldb.2013-07-22-13-46-22/ #查看备份好的数据库
总用量 267468
-rw-rw---- 1 mysql mysql 262221824 7月  21 20:17 ibdata1
-rw-rw---- 1 mysql mysql   5242880 7月  22 13:40 ib_logfile0
-rw-rw---- 1 mysql mysql   5242880 7月  22 13:40 ib_logfile1
drwx------ 2 mysql mysql      4096 7月  20 12:33 mysql
-rw-rw---- 1 mysql mysql     27698 7月  20 12:33 mysql-bin.000001
-rw-rw---- 1 mysql mysql       190 7月  22 13:40 mysql-bin.index
-rw-rw---- 1 mysql mysql      1925 7月  21 13:07 mysql-slow.log
-rw-r----- 1 mysql mysql     21906 7月  22 13:40 mysql.test.com.err
-rw-rw---- 1 mysql mysql         5 7月  22 13:40 mysql.test.com.pid
drwx------ 2 mysql mysql      4096 7月  20 12:33 performance_schema
drwx------ 2 mysql mysql      4096 7月  21 20:00 test
(3).模拟数据库损坏
直接删除数据目录中的所有文件
(4).具体还原步骤
a.mysql这时是无法停止的
[root@mysql mydata]# service mysqld stop
ERROR! MySQL server PID file could not be found!
b.查找mysql所有进程  
[root@mysql mydata]# ps aux | grep mysqld
c.杀死mysql的所有进程
[root@mysql ~]# killall mysqld
d.初始化mysql
[root@mysql ~]# /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/mydata/data/ --user=mysql
e.复制完全备份的数据文件到数据目录中
[root@mysql test]# alias cp=cp #修改cp别名，不然复制时老是提醒是否覆盖
[root@mysql test]# cp -pr /root/alldb.2013-07-22-13-46-22/* /mydata/data/  #复制完全备份的文件到数据目录中
f.启动mysql数据库
[root@mysql test]# service mysqld start
Starting MySQL SUCCESS!
g.测试并查看数据
[root@mysql test]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.32-log Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use test
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
| t2             |
+----------------+
2 rows in set (0.01 sec)
mysql> select count(*) from t1;
+-----------+
| count(*)  |
+-----------+
| 167772160 |
+-----------+
1 row in set (0.01 sec)
mysql> select count(*) from t2;
+----------+
| count(*) |
+----------+
| 20971520 |
+----------+
1 row in set (9.95 sec)
(5).总结
cp命令，对其进行的备份，速度快，还原速度几乎最快，但是灵活度很低，可以跨系统，但是跨平台能力很差，适合小型数据库备份


2.策略二：mysqldump备份数据库（完全备份+增加备份，速度相对较慢，适合中小型数据库）（MyISAM是温备份，InnoDB是热备份）
(1).mysqldump命令详解
mysqldump --all-databases --lock-all-tables --routines --triggers --events --master-data=2 --flush-logs > /root/mybackup/2013-07-22-16-20.full.sql
--all-tables #备份所有库
--lock-all-tables #为所有表加读锁
--routinge #存储过程与函数
--triggers #触发器
--events #记录事件
--master-data=2 #在备份文件中记录当前二进制日志的位置，并且为注释的，1是不注释掉在主从复制中才有意义
--flush-logs #日志滚动一次


(2).具体备份过程如下
a.查看备份前的binlog日志
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000022 |      107 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.01 sec)
b.备份所有库（完全备份）
[root@mysql mybackup]# mysqldump --all-databases --lock-all-tables --routines --triggers --events --master-data=2 --flush-logs > /root/mybackup/2013-07-22-16-20.full.sql
c.查看备份是否成功
[root@mysql mybackup]# ll -h
总用量 739M
-rw-r--r-- 1 root root 739M 7月  22 16:31 2013-07-22-16-20.full.sql
d.查看新生成的binlog日志
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000023 |      107 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.01 sec)
e.插入几条新的数据
mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
| t2             |
+----------------+
2 rows in set (0.00 sec)
mysql> select count(*) from t1;
+-----------+
| count(*)  |
+-----------+
| 167772160 |
+-----------+
1 row in set (0.01 sec)
mysql>  insert into t1 values(167772164),(167772165),(167772166);
f.再次查看binlog日志
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000023 |      363 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.01 sec)
g.备份二进制日志（增量备份）
[root@mysql mybackup]# cp /mybinlog/mysql-bin.000023 /root/mybackup/2013-07-22-16-20.binlog.full.000001


h.查看备份的二进制日志
[root@mysql mybackup]# ll
总用量 756264
-rw-r----- 1 root root       363 7月  22 16:34 2013-07-22-16-20.binlog.full.000001
-rw-r--r-- 1 root root 774402118 7月  22 16:31 2013-07-22-16-20.full.sql
i.模拟数据库损坏
[root@mysql mybackup]# cd /mydata/data/
[root@mysql data]# ls
ibdata1      mysql-bin.000001  mysql-bin.000005  mysql-bin.000009  mysql.test.com.err
ib_logfile0  mysql-bin.000002  mysql-bin.000006  mysql-bin.000010  mysql.test.com.pid
ib_logfile1  mysql-bin.000003  mysql-bin.000007  mysql-bin.index   performance_schema
mysql        mysql-bin.000004  mysql-bin.000008  mysql-slow.log    test
[root@mysql data]# rm -rf * #删除所有数据
[root@mysql data]# ll
总用量 0
(3).具体还原过程如下
a.查找mysql进程
[root@mysql data]# ps -aux | grep mysqld
b.杀死所有进程
[root@mysql data]# killall mysqld
c.初始化mysql并启动mysql
[root@mysql data]# /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/mydata/data/ --user=mysql
d.因为我们不是全新初始化的，可能会有报错的二进制日志，所有我们这里全部删除
[root@mysql data]# rm -rf  /mybinlog/*
e.启动mysql数据库，启动时会重新生成新的二进制日志的
[root@mysql ~]# service mysqld start
f.恢复到备份状态,备份前先关闭对恢复过程的二进制日志记录，因为记录恢复语句是毫无意义的
mysql> set global sql_log_bin=0;
mysq> source /root/mybackup/2013-07-22-16-20.full.sql
g.打开另一个终端查询数据
mysql> select count(*) from t1;
+-----------+
| count(*)  |
+-----------+
| 167772163 |
+-----------+
1 row in set (1 min 29.63 sec) #可以看到用mysqldump备份数据，还原myisam引擎时大概需要30s时间(共1亿多条数据，速度不是挺快的)
mysql> show tables;    
+----------------+
| Tables_in_test |
+----------------+
| t1             |
| t2             |
+----------------+
2 rows in set (0.00 sec)
mysql> select count(*) from t2;
+----------+
| count(*) |
+----------+
| 20971520 |
+----------+
1 row in set (46.14 sec)  #还原INNODB引擎，大概50s左右(共2千多万条数据)
h.查看最后十条数据
mysql> select * from t1 order by id desc limit 10;
+-----------+
| id        |
+-----------+
| 167772163 |
| 167772162 |
| 167772161 |
|        10 |
|        10 |
|        10 |
|        10 |
|        10 |
|        10 |
|        10 |
+-----------+
10 rows in set (0.00 sec)
大家可以看到，我们已经恢复到，完全备份时的状态，但我们最后插入的三条数据没有恢复，下面我们恢复，数据库损坏前我们插入的三条数据！
[root@mysql ~]# mysqlbinlog /root/mybackup/2013-07-22-16-20.binlog.full.000001 | mysql test
[root@mysql ~]# mysql test
mysql> select * from t1 order by id desc limit 10;
+-----------+
| id        |
+-----------+
| 167772166 |
| 167772165 |
| 167772164 |
| 167772163 |
| 167772162 |
| 167772161 |
|        10 |
|        10 |
|        10 |
|        10 |
+-----------+
10 rows in set (47.01 sec)
mysql>
大家可以看到，已经恢复我们最后增加的三条数据！
i.最后，打开二进制记录并查看恢复状况
mysql> set global sql_log_bin=1;
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.01 sec)
mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
| t2             |
+----------------+
2 rows in set (0.00 sec)
(4).总结：
基于mysqldump通常我们就是完整备份+二进制日志来进行恢复的！