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数据库备份和恢复

  • 2013年08月21日 16:57
  • 1KB
  • 下载

一个PHP在线备份和恢复MySQL数据库的类

MySQL数据库备份和恢复

  • 2012年04月10日 12:44
  • 110KB
  • 下载

mysql数据库备份与恢复命令

mysqldump -h主机名  -P端口 -u用户名 -p密码 (–database) 数据库名 > 文件名.sql 备份MySQL数据库的命令 mysqldump -hhostname -...

在JSP页面调用JAVA方法实现MySQL数据库的备份和恢复

今天弄了好久,终于搞定了这个问题。使用java代码实现数据库的备份。 Java代码代码   package cn.qm.db;   import java.io.Buff...

用mysqldump远程备份mysql数据库 和 恢复

from http://hi.baidu.com/hyvpuqcjncagilr/item/7b55bc5bc49ad213db16355d 1、备份 摘要:mysqldump是备份m...

使用innobackupex在线备份及恢复mysql数据库

一、安装解压1.安装数据库 官网:http://www.percona.com 下载地址:http://www.percona.com/redir/downloads/XtraBackup/Xtr...

mysql数据库备份及恢复

还原一个数据库:mysql -h localhost -u root -p123456 www 备份一个数据库:mysqldump -h localhost -u root -p123456 www...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:mysql 学习记录(二十六)--mysql数据库的备份与恢复
举报原因:
原因补充:

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