一、备份准备工作
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通常我们就是完整备份+二进制日志来进行恢复的!
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通常我们就是完整备份+二进制日志来进行恢复的!