mysql dump备份和恢复

---------------------------------------------
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)