mysql dump备份和恢复
来源:互联网 发布:h3c dhcp mac地址绑定 编辑:程序博客网 时间:2024/05/19 02:17
---------------------------------------------
1.准备工作
---------------------------------------------
--查看当前mysql服务器使用的存储引擎
# mysqladmin variables | grep storage_engine
| storage_engine | MyISAM
--查看mysql数据库存放目录
# mysqladmin variables | grep datadir
| datadir | /usr/local/mysql/var/ |
---------------------------------------------
2.mysqldump备份数据库
---------------------------------------------
--mysqldump备份一个整个的数据库
# mysqldump -u root -ppassword --opt accp > /test/accpbak.sql
/*
--opt 这只是一个快捷选项,等同于同时添加 --add-drop-tables --add-locking --create-option --disable-keys --extended-insert --lock-tables --quick --set-charset 选项
*/
--mysqldump备份多个数据库
# mysqldump -u root -ppassword --opt --database accp suzhou > /test/twobak.sql
--mysqldump备份所有数据库
# mysqldump -u root -ppassword --opt --all-databases > /test/allbak.sql
---------------------------------------------
3.--mysqldump恢复数据库
---------------------------------------------
--mysqldump恢复:恢复的前提是accp数据库存在
#mysql -u root -ppassword accp < /test/accpbak.sql
--恢复多个数据库
#mysql -u root -ppassword
mysql>source /test/twobak.sql
---------------------------------------------
---mysql增量备份
---------------------------------------------
#vi /etc/my.cnf
[mysqld]
log-bin=/mysql-log/mysql-bin
1.全备
mysqldump -u root -ppassword --opt beijing > /test/beijing.sql
2.增量备份:切断日志,即备份之前所有内容。此备份点只有的修改都存在新日志里
mysqldump --flush-logs -u root -ppassword --opt beijing > /test/beijing.sql
恢复
1.恢复全备
mysql -u root -ppassword beijing < /test/beijing.sql
2.恢复日志
#mysqlbinlog /usr/local/mysql/var/mysql-bin.000013 | mysql -u root -ppassword beijing
启用新的更新日志
这样可以记录你备份后的数据改变为恢复数据准备。
#mysqladmin flush-logs -u root -ppassword
---案例:增量备份
(1).查看二进制日志存放位置
# ls /usr/local/mysql/var/ | grep mysql-bin*
mysql-bin.000001
mysql-bin.000002
mysql-bin.000003
mysql-bin.000004
mysql-bin.000005
mysql-bin.000006
mysql-bin.000007
mysql-bin.000008
mysql-bin.000009
mysql-bin.000010
mysql-bin.000011
mysql-bin.000012
mysql-bin.000013
mysql-bin.index
(2).准备存放备份数据的位置
# mkdir /backup
(2).准备备份的数据
# mysql -u root -ppassword beijing
mysql> show tables;
+-------------------+
| Tables_in_beijing |
+-------------------+
| t1 |
+-------------------+
1 row in set (0.00 sec)
mysql> select * from t1;
+------+
| sid |
+------+
| 101 |
| 102 |
| 103 |
| 104 |
+------+
4 rows in set (0.00 sec)
(3).做全备
#mysqldump -u root -ppassword --opt beijing > /backup/beijing.sql
(4).修改数据库中表内容
# mysql -u root -ppassword beijing
mysql> insert into t1 values (105);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+------+
| sid |
+------+
| 101 |
| 102 |
| 103 |
| 104 |
| 105 |
+------+
5 rows in set (0.00 sec)
(5).备份并启用新日志
#mysqldump --flush-logs -u root -ppassword --opt beijing > /backup/beijing.sql
# ls /usr/local/mysql/var/ | grep mysql-bin*
mysql-bin.000001
mysql-bin.000002
mysql-bin.000003
mysql-bin.000004
mysql-bin.000005
mysql-bin.000006
mysql-bin.000007
mysql-bin.000008
mysql-bin.000009
mysql-bin.000010
mysql-bin.000011
mysql-bin.000012
mysql-bin.000013
mysql-bin.000014
mysql-bin.index
--说明:即该备份完成后,数据库修改的日志都保存在新添加的mysql-bin.000014中.
(6).操作数据库
# mysql -u root -ppassword beijing
mysql> select * from t1;
+------+
| sid |
+------+
| 101 |
| 102 |
| 103 |
| 104 |
| 105 |
+------+
5 rows in set (0.01 sec)
mysql> delete from t1 where sid=103;
Query OK, 1 row affected (0.01 sec)
mysql> select * from t1;
+------+
| sid |
+------+
| 101 |
| 102 |
| 104 |
| 105 |
+------+
4 rows in set (0.00 sec)
(7).恢复刚才删除的数据
第一步:恢复全备
#mysql -u root -ppassword beijing < /backup/beijing.sql
第二步:恢复日志
#mysqlbinlog /usr/local/mysql/var/mysql-bin.000014 | mysql -u root -ppassword beijing
第三步:查看数据是否恢复
# mysql -u root -ppassword beijing
mysql> select * from t1;
+------+
| sid |
+------+
| 101 |
| 102 |
| 103 |
| 104 |
| 105 |
+------+
5 rows in set (0.00 sec)
- mysql dump备份和恢复
- Linux的备份dump和恢复restore
- mysql dump备份恢复,binlog基于时间点的恢复
- MySQL 备份和恢复
- MySQL备份和恢复
- MySQL 备份和恢复
- MySQL 备份和恢复
- MySQL 备份和恢复
- MySQL 备份和恢复
- MySQL备份和恢复
- MySQL备份和恢复
- MySQL 备份和恢复
- MySQL 备份和恢复
- MySQL 备份和恢复
- MySQL 备份和恢复
- MySQL 备份和恢复
- MySQL 备份和恢复
- MySQL 备份和恢复
- 查看用户和默认表空间
- 设置QTP replaytype
- C语言学习_002_C语言概述
- Linux的启动流程(/etc/inittab)
- RedHat linux AS 5下VirtualBox3.1.6增强功能的安装
- mysql dump备份和恢复
- 找出一堆数里面出现次数最多或最多的前N个数字
- socket(AF_INET, SOCK_RAW, IPPROTO_IP)的意思
- Mobile Web App发展现状及展望
- zeroMQ/jzmq 安装
- hibernate配置属性hbm2dll.auto初步理解
- 对C++ STL,list元素的循环删除操作 //断链,出错地方
- flex菜鸟必知一
- 使用CALL和EXEC調用存儲過程的區別