mysql备份和恢复

美奇科技

　　一、备份准备工作
　　1.查看服务器状态
mysql> \s　　
--------------
　　
mysql  Ver 14.14 Distrib 5.5.37, for Linux (x86_64) using  EditLine wrapper
　　

　　
Connection id:5
　　
Current database:
　　
Current user:root@localhost
　　
SSL:Not in use
　　
Current pager:stdout
　　
Using outfile:''
　　
Using delimiter:;
　　
Server version:5.5.37 Source distribution
　　
Protocol version:10
　　
Connection:127.0.0.1 via TCP/IP
　　
Server characterset:utf8
　　
Db     characterset:utf8
　　
Client characterset:utf8
　　
Conn.  characterset:utf8
　　
TCP port:3306
　　
Uptime:45 min 22 sec
　　

　　
Threads: 1  Questions: 17  Slow queries: 0  Opens: 41  Flush tables: 1  Open tables: 4  Queries per second avg: 0.006
　　
--------------
　　2.查看数据目录存放位置
mysql> show variables like '%datadir%';　　
+---------------+-------------------+
　　
| Variable_name | Value             |
　　
+---------------+-------------------+
　　
| datadir       | /data/mysql/data/ |
　　
+---------------+-------------------+
　　
1 row in set (0.02 sec)
　　3.修改my.cnf
[root@nagios-client ~]# vim /etc/my.cnf　　
log-bin=mysql-bin
　　
innodb_file_per_table = 1
　　重启mysqld服务
[root@nagios-client ~]# service mysqld restart　　
Shutting down MySQL. SUCCESS!
　　
Starting MySQL.. SUCCESS!
　　4.查看新生成的binlog日志
[root@nagios-client ~]# ll /data/mysql/data/　　
total 28700
　　
-rw-rw---- 1 mysql mysql 18874368 Jun  2 16:30 ibdata1
　　
-rw-rw---- 1 mysql mysql  5242880 Jun  2 16:30 ib_logfile0
　　
-rw-rw---- 1 mysql mysql  5242880 Jun  2 15:37 ib_logfile1
　　
drwx------ 2 mysql root      4096 Jun  2 15:37 mysql
　　
-rw-rw---- 1 mysql mysql      107 Jun  2 16:30 mysql-bin.000001
　　
-rw-rw---- 1 mysql mysql       19 Jun  2 16:30 mysql-bin.index
　　
srwxrwxrwx 1 mysql mysql        0 Jun  2 16:30 mysql.sock
　　
-rw-r----- 1 mysql root      3375 Jun  2 16:30 nagios-client.err
　　
-rw-rw---- 1 mysql mysql        6 Jun  2 16:30 nagios-client.pid
　　
drwx------ 2 mysql mysql     4096 Jun  2 15:37 performance_schema
　　
drwx------ 2 mysql root      4096 Jun  2 15:37 test
　　5.准备一个test库，里面有两张表，t1表和t2表！
　　第一张t1表，使用的是MyISAM引擎，其中有1亿多行数据，第二张t2表,使用的是INNODB引擎，其中有2千多万行数据！
mysql> create table t1 (id int(10) default null)engine=myisam default　　
Query OK, 0 rows affected (0.01 sec)    #创建一个简单的t1表，里面只有一个字段 id
　　

　　
mysql> show create table t1;
　　
+-------+--------------------------------------------------------------------------------------+
　　
| Table | Create Table                                                                         |
　　
+-------+--------------------------------------------------------------------------------------+
　　
| t1    | CREATE TABLE `t1` (
　　
  `id` int(10) DEFAULT NULL
　　
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
　　
+-------+--------------------------------------------------------------------------------------+
　　
1 row in set (0.00 sec)
　　

　　
mysql>insert into t1 values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10); #先插入十个数据
　　

　　
mysql> insert into t1 select * from t1;
　　

　　
mysql> select count(*) from t1;
　　
+-----------+
　　
| count(*)  |
　　
+-----------+
　　
| 335544320 |
　　
+-----------+
　　
1 row in set (0.04 sec)
　　t2表
mysql> create table t2 (id int(10) default null)engine=innodb default charset=utf8;　　
Query OK, 0 rows affected (0.14 sec)
　　

　　
mysql> show create table t2;
　　
+-------+--------------------------------------------------------------------------------------+
　　
| Table | Create Table                                                                         |
　　
+-------+--------------------------------------------------------------------------------------+
　　
| t2    | CREATE TABLE `t2` (
　　
  `id` int(10) DEFAULT NULL
　　
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
　　
+-------+--------------------------------------------------------------------------------------+
　　
1 row in set (0.02 sec)
　　

　　
mysql> insert into t2 values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
　　二、备份策略具体演示
　　1.策略一：直接拷贝数据库文件（文件系统备份工具 cp）（适合小型数据库）
　　(1).标准流程：锁表->刷新表到磁盘->拷贝文件->解锁（注,若有有可能的话，可以先停止数据库，再用cp命令准备，这样备份的数据最可靠）
　　(2).具体步骤：
　　a.打开第一个终端，
mysql> flush tables with read lock;　　
Query OK, 0 rows affected (0.06 sec)
　　b.打开第二个终端
[root@nagios-client ~]# mkdir /root/alldb.`date +%F-%H-%M-%S`/  #创建备份目录　　
[root@nagios-client ~]# cp -rp /data/mysql/data/* /root/alldb.2015-06-02-16-56-27/ #复制所以的数据库文件
　　c.在第一个终端解锁
mysql> unlock tables;　　
Query OK, 0 rows affected (0.08 sec)
　　

　　
[root@nagios-client ~]# ll /root/alldb.2015-06-02-16-56-27/    #查看备份好的数据库
　　
total 372772
　　
-rw-rw---- 1 mysql mysql 371195904 Jun  2 16:52 ibdata1
　　
-rw-rw---- 1 mysql mysql   5242880 Jun  2 16:52 ib_logfile0
　　
-rw-rw---- 1 mysql mysql   5242880 Jun  2 16:52 ib_logfile1
　　
drwx------ 2 mysql root       4096 Jun  2 15:37 mysql
　　
-rw-rw---- 1 mysql mysql     11027 Jun  2 16:52 mysql-bin.000001
　　
-rw-rw---- 1 mysql mysql        19 Jun  2 16:30 mysql-bin.index
　　
srwxrwxrwx 1 mysql mysql         0 Jun  2 16:30 mysql.sock
　　
-rw-r----- 1 mysql root       3375 Jun  2 16:30 nagios-client.err
　　
-rw-rw---- 1 mysql mysql         6 Jun  2 16:30 nagios-client.pid
　　
drwx------ 2 mysql mysql      4096 Jun  2 15:37 performance_schema
　　
drwx------ 2 mysql root       4096 Jun  2 16:45 test
　　(3).模拟数据库损坏
　　直接删除数据目录中的所有文件
[root@nagios-client ~]# cd /data/mysql/data/　　
[root@nagios-client data]# ll
　　
total 372776
　　
-rw-rw---- 1 mysql mysql 371195904 Jun  2 16:52 ibdata1
　　
-rw-rw---- 1 mysql mysql   5242880 Jun  2 16:52 ib_logfile0
　　
-rw-rw---- 1 mysql mysql   5242880 Jun  2 16:52 ib_logfile1
　　
drwx------ 2 mysql root       4096 Jun  2 15:37 mysql
　　
-rw-rw---- 1 mysql mysql     11027 Jun  2 16:52 mysql-bin.000001
　　
-rw-rw---- 1 mysql mysql        19 Jun  2 16:30 mysql-bin.index
　　
srwxrwxrwx 1 mysql mysql         0 Jun  2 16:30 mysql.sock
　　
-rw-r----- 1 mysql root       3375 Jun  2 16:30 nagios-client.err
　　
-rw-rw---- 1 mysql mysql         6 Jun  2 16:30 nagios-client.pid
　　
drwx------ 2 mysql mysql      4096 Jun  2 15:37 performance_schema
　　
drwx------ 2 mysql root       4096 Jun  2 16:45 test
　　
[root@nagios-client data]# rm -rf *
　　(4).具体还原步骤
　　a.mysql这时是无法停止的
[root@nagios-client data]# service mysqld stop　　
ERROR! MySQL server PID file could not be found!
　　b.查找mysql所有进程
[root@nagios-client data]# killall mysqld　　c.初始化mysql
[root@nagios-client data]# /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/data/mysql/data/ --user=mysql　　d.复制完全备份的数据文件到数据目录中
[root@nagios-client data]# cp -r /root/alldb.2015-06-02-16-56-27/ /data/mysql/data/　　e.启动mysql数据库
[root@nagios-client data]# service mysqld start　　
Starting MySQL.. SUCCESS!
　　f.测试并查看数据
mysql> show databases;　　
+--------------------+
　　
| Database           |
　　
+--------------------+
　　
| information_schema |
　　
| mysql              |
　　
| performance_schema |
　　
| test               |
　　
+--------------------+
　　
4 rows in set (0.09 sec)
　　

　　
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(*)  |
　　
+-----------+
　　
| 335544320 |
　　
+-----------+
　　
1 row in set (0.06 sec)
　　

　　
mysql> select count(*) from t2;
　　
+----------+
　　
| count(*) |
　　
+----------+
　　
| 41943040 |
　　
+----------+
　　
1 row in set (15.37 sec)
　　(5).总结
　　cp命令，对其进行的备份，速度快，还原速度几乎最快，但是灵活度很低，可以跨系统，但是跨平台能力很差，适合小型数据库备份！
　　2.策略二：mysqldump备份数据库（完全备份+增加备份，速度相对较慢，适合中小型数据库）（MyISAM是温备份，InnoDB是热备份）
　　(1).mysqldump命令详解
mysqldump --all-databases --lock-all-tables --routines --triggers --events --master-data=2 --flush-logs > /root/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.000005 |      107 |              |                  |
　　
+------------------+----------+--------------+------------------+
　　
1 row in set (0.00 sec)
　　b.备份所有库（完全备份）
mysqldump -u root -p -h 127.0.0.1 --all-databases --lock-all-tables --master-data=2 --flush-logs >/root/full.sql　　c.查看备份是否成功
[root@nagios-client ~]# ll -h /root/ |grep full　　
-rw-r--r--   1 root root  1.5G Jun  3 09:35 full.sql
　　d.查看新生成的binlog日志
mysql> show master status;　　
+------------------+----------+--------------+------------------+
　　
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
　　
+------------------+----------+--------------+------------------+
　　
| mysql-bin.000006 |      107 |              |                  |
　　
+------------------+----------+--------------+------------------+
　　
1 row in set (0.00 sec)
　　e.插入几条新的数据
mysql> use test;　　
Database changed
　　
mysql> show tables;
　　
+----------------+
　　
| Tables_in_test |
　　
+----------------+
　　
| t1             |
　　
| t2             |
　　
+----------------+
　　
2 rows in set (0.00 sec)
　　

　　
mysql> insert into t1 values(335544321),(335544322),(335544323);
　　
Query OK, 3 rows affected (0.03 sec)
　　
Records: 3  Duplicates: 0  Warnings: 0
　　f.再次查看binlog日志
mysql> show master status;　　
+------------------+----------+--------------+------------------+
　　
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
　　
+------------------+----------+--------------+------------------+
　　
| mysql-bin.000006 |      363 |              |                  |
　　
+------------------+----------+--------------+------------------+
　　
1 row in set (0.00 sec)
　　g.备份二进制日志（增量备份）
[root@nagios-client ~]# cp /data/mysql/data/mysql-bin.000006 /root　　i.模拟数据库损坏
mysql> drop database test;        #这里我们删掉test库　　
Query OK, 2 rows affected (0.38 sec)
　　(3).具体还原过程如下
#首先通过全备还原test库　　
mysql> system ls -l /root |grep full
　　
-rw-r--r--   1 root root  1548283305 Jun  3 09:35 full.sql
　　
mysql> system pwd
　　
/root
　　
mysql> source full.sql
　　a.查看还原结果
mysql> show databases;　　
+--------------------+
　　
| Database           |
　　
+--------------------+
　　
| information_schema |
　　
| mysql              |
　　
| performance_schema |
　　
| test               |
　　
+--------------------+
　　
4 rows in set (0.08 sec)
　　

　　
mysql> use test;
　　
Database changed
　　
mysql> show tables;
　　
+----------------+
　　
| Tables_in_test |
　　
+----------------+
　　
| t1             |
　　
| t2             |
　　
+----------------+
　　
2 rows in set (0.00 sec)
　　b.查看t1表最后10条数据
mysql> select * from t1 order by id desc limit 10;　　
+------+
　　
| id   |
　　
+------+
　　
|   10 |
　　
|   10 |
　　
|   10 |
　　
|   10 |
　　
|   10 |
　　
|   10 |
　　
|   10 |
　　
|   10 |
　　
|   10 |
　　
|   10 |
　　
+------+
　　
10 rows in set (32.60 sec)
　　

　　
#从结果能看出，我们后来插入的数据没有了
　　c.下面恢复后来插入的数据
#通过之前备份的二进制日志文件还原　　
[root@nagios-client ~]# mysqlbinlog /root/mysql-bin.000006 |mysql -u root -p -h 127.0.0.1 test
　　
mysql> select * from t1 order by id desc limit 10;
　　
+-----------+
　　
| id        |
　　
+-----------+
　　
| 335544323 |
　　
| 335544322 |
　　
| 335544321 |
　　
|        10 |
　　
|        10 |
　　
|        10 |
　　
|        10 |
　　
|        10 |
　　
|        10 |
　　
|        10 |
　　
+-----------+
　　
10 rows in set (30.39 sec)
　　
#从这里能看到数据已恢复