一、理论:
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]
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 |
+------+------+