mysql备份与恢复

备份

完全备份 --all-databases

局部备份 --database 库名

锁表（数据一致性，备份时只读）

全局锁 --lock-all-tables

局部锁 --lock-tables

备份bookshop数据库

# mysqldump --database bookshop -uroot -puplooking > /tmp/mysql.bookshop.sql

完全备份（所有数据库） 

# mysqldump --all-database -uroot -puplooking --lock-all-tables > /tmp/mysql.all.sql

查看

# cat /tmp/mysql.all.sql

恢复

# mysql < /tmp/mysql.all.sql    (输入重定向到mysql)

**恢复时是否一定要清除/var/lib/mysql中的数据库，建议如果数据库和表完全备份数据数量不一致，建议清除，因为完全备份恢复时，新建的库和表不会被删除，做日志重演是，库和表的重演会报错。

------------------------------------

实验1:

3点 完全备份并打开日志，3-10点新建一个库和表，10点数据库损坏，需要恢复（重演日志）。

1.建库建表

    create database db1;

    use db1;

    create table tb1(no int);

    insert into tb1 values(1);

2.完全备份

# mysqldump --all-databases --lock-all-tables -uroot -puplooking > /tmp/mysql.all.sql 

3.配置文件

# vi /etc/my.cnf

log-bin=mysql    添加一行

4.service mysqld restart

   mysql> show master status;

    

4.3点-10点 建一个新库和表（参考1步骤）

5.数据库损坏，需要完全恢复+日志重演(drop 数据库)

1) # mysql -puplooking < /tmp/mysql.all.sql

2) # mysqlbinlog mysql.000001 | mysql -puplooking

实验2：

历史重演，误操作之前和之后数据，跳过delete误操作。

3点完全备份 （锁表 刷新日志），10点建新库新表，delete误操作，12点恢复数据

1.# mysqldump --lock-all-tables --all-databases -uroot -puplooking --flush-logs > /tmp/mysql.all.sql

2.查看新日志编号

3.建新库新表

4.误操作delete 一张表，之后继续插入数据到该表

5.# mysql -puplooking < /tmp/mysql.all.sql 

    之前1.cd /var/lib/mysql      2.rm -rf 数据库

6.# mysqlbinlog mysql.000003 | grep -C3 "^delete"    查看drop之前之后时间或postion号（上下3行）

      

7.恢复delete之前数据

# mysqlbinlog mysql.000003 --stop-date="2014-01-20 22:16:20"|mysql -puplooking

8.恢复delete之后数据

# mysqlbinlog mysql.000003 --start-date="2014-01-20 22:16:24"|mysql -puplooking

实验3：

如果误操作delete通过脚本，在1秒钟完成 删除、插入，时间都相同不能用时间点分割，使用position号。

1.# vi for.sh

#!/bin/bash

for i in $(seq 1 1000)

do

echo "insert into tb1 values($i);" >> /root/test.txt

done

2. vi test.txt

第一行添加 use db1;

任意一行添加 delete from tb1;

3.mysql -puplooking < /root/test.txt  

4.恢复delete之前数据之后数据

mysql> show master status

# cd /var/lib/mysql

#  mysqlbinlog mysql.000002 | grep -C83"^delete"

1.先删除数据库

# rm -rf db*   

2.恢复完全备份

#mysql -puplooking< /tmp/mysql.all.sql

3.重演日志

# mysqlbinlog mysql.000002 --stop-position="8017"|mysql  -puplooking

# mysqlbinlog mysql.000002 --start-position="8182"|mysql  -puplooking