mysql 学习记录(二十六)--mysql数据库的备份与恢复

原创 2015年11月21日 00:50:37
一、理论:
1.进行备份或恢复操作时考虑的因素:
a.备份的表的存储引擎,事务型与非事务型在处理数据一致性方面有所区别
b.确定使用全备份还是增量备份
c.可以采用复制的方式采用异地备份。
d.可以采用定期备份
e.确保mysql打开bin-log选项
f.经常做备份恢复测试以确保备份的有效性
2.备份工具:
a.mysqldump
3.完全恢复:
a.mysql -uroot -p*** dbname < bakfile
b.mysqlbinlog binlog-file | mysql -uroot -p***
4.基于时间点的恢复:
a.mysqlbinlog --start-date="2015-11-15 10:01:00" /var/log/mysql/bin.123 | mysql -uroot -p*** 
5.基于位置恢复:
a.mysqlbinlog --start-date="2015-09-20 9:55:00" --stop-date="2015-09-20 10:00:03" /var/log/mysql/bin.123 > /tmp/mysql_restore.sql
b.查出出错的前后:位置号
c.mysqlbinlog --stop-position="381031" /var/log/mysql/bin.123456 | mysql -uroot -p***
d.mysqlbinlog --start-position="381083" /var/log/mysql/bin.123456 | mysql -uroot -p***
6.物理备份和恢复(冷备份):
a.备份操作:停止mysql服务,将数据文件和日志文件备份到备份目录
b.恢复操作:停止mysql服务,将操作系统级别恢复mysql数据文件,重启mysql服务,使用mysqlbinlog工具恢复自备份以来的所有binlog
7.物理备份和恢复(热备份):
a.(第一种办法):mysqlhotcopy自带热备份工具
b.mysqlhotcopy --help(可以看帮助)
c.(第二种办法):手工锁表copy
d.flush tables for read;
e.innoDB存储引擎方法1---使用ibbackup
f.innoDB存储引擎方法2---使用innobackupex
8.表的导出
a.select * from tablename into outfile 'target_file' [option]
b.mysqldump -u username -T target_dir dbname tablename [option]
9.表的导入
a.LOAD DATA [local] INFILE 'filename' INTO TABLE tablename [option]
b.mysqlimport -u root -p *** [--LOCAL] dbname order_db.txt [option]

二、实践:

abc@ubuntu:~/Downloads/mysql$ mysqldump -uroot -p123 test > test.sql;  
abc@ubuntu:~/Downloads/mysql$ mysqldump -uroot -p123 test t2 > t2.sql;  
abc@ubuntu:~/Downloads/mysql$ more /tmp/emp.txt  
1,z1  
1,z2  
1,z1  
1,z2  
3,z3  
abc@ubuntu:~/Downloads/mysql$ mysql -uroot -p123 -e "use test;select * from emp order by id";  
+------+------+  
| id   | info |  
+------+------+  
|    1 | z1   |  
|    1 | z2   |  
|    1 | z1   |  
|    1 | z2   |  
|    3 | z3   |  
+------+------+  
abc@ubuntu:~/Downloads/mysql$ mysql -uroot -p123 -e "use test;select * from emp order by id";  
+------+------+  
| id   | info |  
+------+------+  
|    1 | z1   |  
|    1 | z2   |  
|    1 | z1   |  
|    1 | z2   |  
|    3 | z3   |  
|    5 | z5   |  
|   56 | z56  |  
+------+------+  
abc@ubuntu:~/Downloads/mysql$ mysql -uroot -p123 -e "use test;select * from emp order by id";  
+------+------+  
| id   | info |  
+------+------+  
|    1 | z1   |  
|    1 | z2   |  
|    1 | z1   |  
|    1 | z2   |  
|    3 | z3   |  
+------+------+  
abc@ubuntu:/usr/local/mysql/data$ mysql -uroot -p123 -e "use test;select * from emp order by id";  
+------+------+  
| id   | info |  
+------+------+  
|    1 | z1   |  
|    1 | z2   |  
|    1 | z1   |  
|    1 | z2   |  
|    3 | z3   |  
+------+------+  
abc@ubuntu:~/Downloads/mysql$ mysql -uroot -p123 -e "use test;select * from emp order by id";  
+------+------+  
| id   | info |  
+------+------+  
|    1 | z1   |  
|    1 | z2   |  
|    1 | z1   |  
|    1 | z2   |  
|    3 | z3   |  
+------+------+  
abc@ubuntu:~/Downloads/mysql$ mysql -uroot -p123 -e "use test;insert into emp values(5,'z5')";  
abc@ubuntu:~/Downloads/mysql$ mysql -uroot -p123 -e "use test;select * from emp order by id";  
+------+------+  
| id   | info |  
+------+------+  
|    1 | z1   |  
|    1 | z2   |  
|    1 | z1   |  
|    1 | z2   |  
|    3 | z3   |  
|    5 | z5   |  
+------+------+  
abc@ubuntu:~/Downloads/mysql$ mysql -uroot -p123 -e "use test;select * from emp order by id";  
+------+------+  
| id   | info |  
+------+------+  
|    1 | z1   |  
|    1 | z2   |  
|    1 | z1   |  
|    1 | z2   |  
|    3 | z3   |  
|    5 | z5   |  
+------+------+  
abc@ubuntu:~/Downloads/mysql$ mysql -uroot -p123 -e "use test;insert into emp values(5,'z5')";  
abc@ubuntu:~/Downloads/mysql$ mysql -uroot -p123 -e "use test;select * from emp order by id";  
+------+------+  
| id   | info |  
+------+------+  
|    1 | z1   |  
|    1 | z2   |  
|    1 | z1   |  
|    1 | z2   |  
|    3 | z3   |  
|    5 | z5   |  
+------+------+  
abc@ubuntu:~/Downloads/mysql$ mysql -uroot -p123 -e "use test;truncate emp;";  
abc@ubuntu:~/Downloads/mysql$ mysql -uroot -p123 -e "use test;select * from emp order by id";  
abc@ubuntu:~/Downloads/mysql$ mysql -uroot -p123 -e "select now();";  
+---------------------+  
| now()               |  
+---------------------+  
| 2015-11-08 04:45:31 |  
+---------------------+  
abc@ubuntu:~/Downloads/mysql$ mysql -uroot -p123 -e "use test;select * from emp order by id";  
abc@ubuntu:~/Downloads/mysql$ mysql -uroot -p123 -e "use test;insert into emp values(1,'t1')";  
abc@ubuntu:~/Downloads/mysql$ mysql -uroot -p123 -e "select now();";  
+---------------------+  
| now()               |  
+---------------------+  
| 2015-11-08 04:46:02 |  
+---------------------+  
abc@ubuntu:~/Downloads/mysql$ mysql -uroot -p123 -e "use test;select * from emp order by id";  
+------+------+  
| id   | info |  
+------+------+  
|    1 | t1   |  
+------+------+  
abc@ubuntu:~/Downloads/mysql$ mysql -uroot -p123 -e "select now();";  
+---------------------+  
| now()               |  
+---------------------+  
| 2015-11-08 04:46:37 |  
+---------------------+  
abc@ubuntu:~/Downloads/mysql$ mysql -uroot -p123 -e "use test;insert into emp values(3,'t3')";  
abc@ubuntu:~/Downloads/mysql$ mysql -uroot -p123 -e "select now();";  
+---------------------+  
| now()               |  
+---------------------+  
| 2015-11-08 04:46:51 |  
+---------------------+  
abc@ubuntu:~/Downloads/mysql$ mysql -uroot -p123 -e "use test;select * from emp order by id";  
+------+------+  
| id   | info |  
+------+------+  
|    1 | t1   |  
|    3 | t3   |  
+------+------+  
abc@ubuntu:~/Downloads/mysql$ mysql -uroot -p123 -e "select now();";  
+---------------------+  
| now()               |  
+---------------------+  
| 2015-11-08 04:47:35 |  
+---------------------+  
abc@ubuntu:~/Downloads/mysql$ mysql -uroot -p123 -e "use test;insert into emp values(53,'t53')";  
abc@ubuntu:~/Downloads/mysql$ mysql -uroot -p123 -e "use test;select * from emp order by id";  
+------+------+  
| id   | info |  
+------+------+  
|    1 | t1   |  
|    3 | t3   |  
|   53 | t53  |  
+------+------+  
mysql> select * from payment limit 10;  
+------------+-------------+----------+-----------+--------+---------------------+---------------------+  
| payment_id | customer_id | staff_id | rental_id | amount | payment_date        | last_update         |  
+------------+-------------+----------+-----------+--------+---------------------+---------------------+  
|          1 |           1 |        1 |        76 |   2.99 | 2005-05-25 11:30:37 | 2006-02-15 22:12:30 |  
|          2 |           1 |        1 |       573 |   0.99 | 2005-05-28 10:35:23 | 2006-02-15 22:12:30 |  
|          3 |           1 |        1 |      1185 |   5.99 | 2005-06-15 00:54:12 | 2006-02-15 22:12:30 |  
|          4 |           1 |        2 |      1422 |   0.99 | 2005-06-15 18:02:53 | 2006-02-15 22:12:30 |  
|          5 |           1 |        2 |      1476 |   9.99 | 2005-06-15 21:08:46 | 2006-02-15 22:12:30 |  
|          6 |           1 |        1 |      1725 |   4.99 | 2005-06-16 15:18:57 | 2006-02-15 22:12:30 |  
|          7 |           1 |        1 |      2308 |   4.99 | 2005-06-18 08:41:48 | 2006-02-15 22:12:30 |  
|          8 |           1 |        2 |      2363 |   0.99 | 2005-06-18 13:33:59 | 2006-02-15 22:12:30 |  
|          9 |           1 |        1 |      3284 |   3.99 | 2005-06-21 06:24:45 | 2006-02-15 22:12:30 |  
|         10 |           1 |        2 |      4526 |   5.99 | 2005-07-08 03:17:05 | 2006-02-15 22:12:30 |  
+------------+-------------+----------+-----------+--------+---------------------+---------------------+  
10 rows in set (0.00 sec)  
  
mysql> create or replace view v_payment_myisam as select * from payment limit 15;  
Query OK, 0 rows affected (0.01 sec)  
  
mysql> Ctrl-C -- exit!  
Aborted  
  
abc@ubuntu:~/Downloads/mysql$ mysql -uroot -p123 -e "use test;select * from emp order by id";  
+------+------+  
| id   | info |  
+------+------+  
|    1 | t1   |  
|    3 | t3   |  
+------+------+  


abc@ubuntu:~/Downloads/mysql$ mysql -uroot -p123 -e "use test;select * from emp order by id";  
+------+------+  
| id   | info |  
+------+------+  
|    1 | t1   |  
|    3 | t3   |  
+------+------+  
  
abc@ubuntu:~/Downloads/mysql$ mysql -uroot -p123 -e "use test;select * from emp order by id";  
+------+------+  
| id   | info |  
+------+------+  
|    1 | z1   |  
|    1 | z2   |  
|    1 | z1   |  
|    1 | z2   |  
|    3 | z3   |  
|    5 | z5   |  
+------+------+  
abc@ubuntu:~/Downloads/mysql$ mysqlbinlog ./mysql-bin.000002 | mysql -uroot -p  
Enter password: mysqlbinlog: File './mysql-bin.000002' not found (Errcode: 13)  
  
abc@ubuntu:~/Downloads/mysql$ mysql -uroot -p123 -e "use test;select * from emp order by id";  
+------+------+  
| id   | info |  
+------+------+  
|    1 | z1   |  
|    1 | z2   |  
|    1 | z1   |  
|    1 | z2   |  
|    3 | z3   |  
|    5 | z5   |  
+------+------+  
abc@ubuntu:~/Downloads/mysql$ mysql -uroot -p123 -e "use test;truncate emp;";  
abc@ubuntu:~/Downloads/mysql$ mysql -uroot -p123 -e "use test;select * from emp order by id";  
abc@ubuntu:~/Downloads/mysql$ sudo chmod 0777 -R ./mysql-bin.000002   


abc@ubuntu:~/Downloads/mysql$ mysql -uroot -p123 -e "use test;select * from emp order by id";  
 
abc@ubuntu:~/Downloads/mysql$ cd /usr/local/mysql/data/  
abc@ubuntu:/usr/local/mysql/data$ sudo chmod -R 0777 ./mysql-bin.000002  
abc@ubuntu:/usr/local/mysql/data$ mysql -uroot -p123 -e "use test;select * from emp order by id";  
abc@ubuntu:/usr/local/mysql/data$ cd ~/Downloads/mysql/  
abc@ubuntu:~/Downloads/mysql$ mysql -uroot -p123 test < ./test-2015-11-08.dmp   
abc@ubuntu:~/Downloads/mysql$ mysql -uroot -p123 -e "use test;select * from emp order by id";  
+------+------+  
| id   | info |  
+------+------+  
|    1 | z1   |  
|    1 | z2   |  
|    1 | z1   |  
|    1 | z2   |  
|    3 | z3   |  
+------+------+  
abc@ubuntu:~/Downloads/mysql$ mysql -uroot -p123 -e "use test;insert into emp values(5,'z5')";  
abc@ubuntu:~/Downloads/mysql$ mysql -uroot -p123 -e "use test;select * from emp order by id";  
+------+------+  
| id   | info |  
+------+------+  
|    1 | z1   |  
|    1 | z2   |  
|    1 | z1   |  
|    1 | z2   |  
|    3 | z3   |  
|    5 | z5   |  
+------+------+  
abc@ubuntu:~/Downloads/mysql$ mysql -uroot -p123 -e "use test;truncate emp;";  
abc@ubuntu:~/Downloads/mysql$ mysql -uroot -p123 -e "use test;select * from emp order by id";  
abc@ubuntu:~/Downloads/mysql$ mysqlbinlog ./mysql-bin.000002 | mysql -uroot -p  
Enter password:   
ERROR 1051 (42S02) at line 25: Unknown table 'v_payment_myisam'  
abc@ubuntu:~/Downloads/mysql$ mysql -uroot -p123 -e "select now();";  
+---------------------+  
| now()               |  
+---------------------+  
| 2015-11-08 04:45:31 |  
+---------------------+  
abc@ubuntu:~/Downloads/mysql$ mysql -uroot -p123 -e "use test;select * from emp order by id";  
abc@ubuntu:~/Downloads/mysql$ mysql -uroot -p123 -e "use test;insert into emp values(1,'t1')";  
abc@ubuntu:~/Downloads/mysql$ mysql -uroot -p123 -e "select now();";  
+---------------------+  
| now()               |  
+---------------------+  
| 2015-11-08 04:46:02 |  
+---------------------+  
abc@ubuntu:~/Downloads/mysql$ mysql -uroot -p123 -e "use test;select * from emp order by id";  
+------+------+  
| id   | info |  
+------+------+  
|    1 | t1   |  
+------+------+  
abc@ubuntu:~/Downloads/mysql$ mysql -uroot -p123 -e "select now();";  
+---------------------+  
| now()               |  
+---------------------+  
| 2015-11-08 04:46:37 |  
+---------------------+  
abc@ubuntu:~/Downloads/mysql$ mysql -uroot -p123 -e "use test;insert into emp values(3,'t3')";  
abc@ubuntu:~/Downloads/mysql$ mysql -uroot -p123 -e "select now();";  
+---------------------+  
| now()               |  
+---------------------+  
| 2015-11-08 04:46:51 |  
+---------------------+  
abc@ubuntu:~/Downloads/mysql$ mysql -uroot -p123 -e "use test;select * from emp order by id";  
+------+------+  
| id   | info |  
+------+------+  
|    1 | t1   |  
|    3 | t3   |  
+------+------+  
abc@ubuntu:~/Downloads/mysql$ mysql -uroot -p123 -e "select now();";  
+---------------------+  
| now()               |  
+---------------------+  
| 2015-11-08 04:47:35 |  
+---------------------+  
abc@ubuntu:~/Downloads/mysql$ mysql -uroot -p123 -e "use test;insert into emp values(53,'t53')";  
abc@ubuntu:~/Downloads/mysql$ mysql -uroot -p123 -e "use test;select * from emp order by id";  
+------+------+  
| id   | info |  
+------+------+  
|    1 | t1   |  
|    3 | t3   |  
|   53 | t53  |  
+------+------+  


版权声明:本博客原创文章欢迎转载,请转载的朋友最好注明出处,谢谢大家。

MySQL数据库学习初步

我使用的环境是Win7,开始学习PHP和MySQL,并且买了本《Head First PHP & MySQL》,可以从Head First Labs官网获得HeadFirst系列书籍的相关信息和源代码...
  • ccf19881030
  • ccf19881030
  • 2014年06月28日 22:11
  • 7200

MySQL详解-------数据库备份和还原

数据备份: 使用mysqldump命令备份 mysqldump命令可以讲数据库中的数据备份成一个文本文件。表结果和表中的数据将存储在生成的文本中。mysqldump的工作原理很简单。他先查...
  • icityfree
  • icityfree
  • 2015年07月07日 15:21
  • 750

MYSQL数据库学习(一)—初学常用语句命令

MySQL是一种开放源代码的关系型数据库管理系统(RDBMS),MySQL数据库系统使用最常用的数据库管理语言--结构化查询语言(SQL)进行数据库管理。现归属于Oracle(甲骨文)公司所有。 M...
  • ylyang12
  • ylyang12
  • 2016年11月20日 16:34
  • 1719

MySQL详解(20)-----------数据库备份和还原

数据备份: 使用mysqldump命令备份 mysqldump命令可以讲数据库中的数据备份成一个文本文件。表结果和表中的数据将存储在生成的文本中。mysqldump的工作原理很简单。他先查出需要备...
  • u011225629
  • u011225629
  • 2015年07月07日 10:21
  • 5172

MySQL数据库的数据备份和恢复(导入和导出)命令操作语法

快乐编程 2017-01-05 19:33 不管是Oracle数据库还是SQL Server数据库,每个数据库都有自己的一套数据备份和恢复的方法,MySQL数据库也不例外。MySQL数据库备...
  • u011277123
  • u011277123
  • 2017年01月09日 09:00
  • 2253

mysqldump备份数据库 mysql恢复数据库

mysqldump 备份与恢复数据库 备份: 1、备份全部数据库的数据和结构  mysqldump -uroot -pjsb -A > /bk/all.sql -A: 备份所有数据库=--all-...
  • u010098331
  • u010098331
  • 2016年03月15日 15:17
  • 5000

mysql数据库学习汇总

MySQL学习汇总 1、字段 Int类型占4个字节,32位,无符号最小值是0,最大值那就是4294967295,和Java基本类型差不多。建表的时候定义的长度比如Int(10),这个10跟字段类型允许...
  • hgd613
  • hgd613
  • 2015年12月30日 11:01
  • 3359

系统学习深度学习(二十六)--R-CNN

基于R-CNN的物体检测 原文地址:http://blog.csdn.net/hjimce/article/details/50187029 作者:hjimce 一、相关理论    本篇博...
  • App_12062011
  • App_12062011
  • 2017年03月28日 10:25
  • 606

DBA必知的mysql备份与还原

DBA必知的mysql备份与还原
  • mine_song
  • mine_song
  • 2017年04月26日 09:20
  • 518

Java 下实现对mysql数据库的备份和恢复

在Java里面实现对mysql数据库的备份和恢复. 我们知道在mysql的安装目录下有一些实用的工具(like- mysqldump,mysql,etc).经过本人一系列查找后,并没有找到相关的mys...
  • HinstenyHisoka
  • HinstenyHisoka
  • 2015年04月29日 19:36
  • 1200
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:mysql 学习记录(二十六)--mysql数据库的备份与恢复
举报原因:
原因补充:

(最多只允许输入30个字)