mysql备份和恢复

fewrwe

一、备份准备工作

1.查看服务器状态

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

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.查看数据目录存放位置

1 2 3 4 5 6 7

mysql> show variables like '%datadir%'; +---------------+-------------------+ | Variable_name | Value             | +---------------+-------------------+ | datadir       | /data/mysql/data/ | +---------------+-------------------+ 1 row in set (0.02 sec)

3.修改my.cnf

1 2 3

[iyunv@nagios-client ~]# vim /etc/my.cnf log-bin=mysql-bin innodb_file_per_table = 1

重启mysqld服务

1 2 3

[iyunv@nagios-client ~]# service mysqld restart Shutting down MySQL. SUCCESS! Starting MySQL.. SUCCESS!

4.查看新生成的binlog日志

1 2 3 4 5 6 7 8 9 10 11 12 13

[iyunv@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千多万行数据！

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

mysql> create table t1 (id int(10) default null)engine=myisam default charset=utf8; 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表

1 2 3 4 5 6 7 8 9 10 11 12 13 14

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.打开第一个终端，

1 2	mysql> flush tables with read lock; Query OK, 0 rows affected (0.06 sec)

b.打开第二个终端  

1 2	[iyunv@nagios-client ~]# mkdir /root/alldb.`date +%F-%H-%M-%S`/  #创建备份目录 [iyunv@nagios-client ~]# cp -rp /data/mysql/data/* /root/alldb.2015-06-02-16-56-27/ #复制所以的数据库文件

c.在第一个终端解锁

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

mysql> unlock tables; Query OK, 0 rows affected (0.08 sec) [iyunv@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).模拟数据库损坏
直接删除数据目录中的所有文件

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

[iyunv@nagios-client ~]# cd /data/mysql/data/ [iyunv@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 [iyunv@nagios-client data]# rm -rf *

(4).具体还原步骤
a.mysql这时是无法停止的

1 2	[iyunv@nagios-client data]# service mysqld stop ERROR! MySQL server PID file could not be found!

b.查找mysql所有进程   

1	[iyunv@nagios-client data]# killall mysqld

c.初始化mysql

1	[iyunv@nagios-client data]# /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/data/mysql/data/ --user=mysql

d.复制完全备份的数据文件到数据目录中

1	[iyunv@nagios-client data]# cp -r /root/alldb.2015-06-02-16-56-27/ /data/mysql/data/

e.启动mysql数据库

1 2	[iyunv@nagios-client data]# service mysqld start Starting MySQL.. SUCCESS!

f.测试并查看数据

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37

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命令，对其进行的备份，速度快，还原速度几乎最快，但是灵活度很低，可以跨系统，但是跨平台能力很差，适合小型数据库备份！