Create test library and table
create database test; use test; create table leo (id int,name varchar(10));
insert data
insert into leo values (1,"liufeng"); insert into leo values (2,"zhangsan"); insert into leo values (3,"liufeng"); insert into leo values (4,"zhangsan"); mysql> select * from leo; +------+----------+ | id | name | +------+----------+ | 1 | liufeng | | 2 | zhangsan | | 3 | liufeng | | 4 | zhangsan | +------+----------+ .......
Full database
#!/bin/sh
bak_path=/home/mysql/backup
file_name=bak_$(date +%F)
if [ ! -e $bak_path ];then
mkdir -p $bak_path
fi
mysqldump -uroot -proot -S /home/mysql/3306/data/mysql.sock -A --single-transaction --master-data=2 > $bak_path/${file_name}.sql
md5sum $bak_path/${file_name}.sql > $bak_path/${file_name}.flag
find $BakPath -name "*.sql.gz" -mtime +7|xargs rm -f
Confirm backup status
[root@leo home]# cd mysql/backup/ [root@leo backup]# ll total 792 -rw-r--r-- 1 root root 72 Jun 28 22:18 bak_2019-06-28.flag -rw-r--r-- 1 root root 804496 Jun 28 22:18 bak_2019-06-28.sql [root@leo backup]# more bak_2019-06-28.flag db297e95d491ae3b85ed2b5d2496e527 /home/mysql/backup/bak_2019-06-28.sql [root@leo backup]# cd .. [root@leo mysql]# [root@leo mysql]# md5sum -c /home/mysql/backup/bak_2019-06-28.flag /home/mysql/backup/bak_2019-06-28.sql: OK
insert data
mysql> insert into leo values (5,"liufeng"); Query OK, 1 row affected (0.01 sec) mysql> insert into leo values (6,"zhangsan"); Query OK, 1 row affected (0.01 sec) mysql> select * from leo; +------+----------+ | id | name | +------+----------+ | 1 | liufeng | | 2 | zhangsan | | 3 | liufeng | | 4 | zhangsan | | 5 | liufeng | | 6 | zhangsan | +------+----------+
Delete database
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | leo | | mysql | | performance_schema | | sys | | test | +--------------------+ 6 rows in set (0.00 sec) mysql> drop database test; Query OK, 0 rows affected (0.01 sec) mysql> drop database test; Query OK, 1 row affected (0.01 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)
Backup bin log
[root@leo backup]# cd /home/mysql/3306/data/ [root@leo data]# ll total 123740 -rw-r-----. 1 mysql mysql 56 Jun 18 00:59 auto.cnf -rw-r----- 1 mysql mysql 456 Jun 28 17:41 ib_buffer_pool -rw-r-----. 1 mysql mysql 12582912 Jun 29 02:45 ibdata1 -rw-r-----. 1 mysql mysql 50331648 Jun 29 02:45 ib_logfile0 -rw-r-----. 1 mysql mysql 50331648 Jun 18 00:59 ib_logfile1 -rw-r----- 1 mysql mysql 12582912 Jun 29 02:42 ibtmp1 drwxr-x---. 2 mysql mysql 4096 Jun 25 23:09 mysql -rw-r----- 1 mysql mysql 177 Jun 25 22:54 mysql-bin.000003 -rw-r----- 1 mysql mysql 815245 Jun 25 23:17 mysql-bin.000004 -rw-r----- 1 mysql mysql 642 Jun 28 17:41 mysql-bin.000005 -rw-r----- 1 mysql mysql 5909 Jun 29 02:45 mysql-bin.000006 -rw-r----- 1 mysql mysql 76 Jun 28 19:36 mysql-bin.index srwxrwxrwx 1 mysql mysql 0 Jun 28 19:36 mysql.sock -rw------- 1 mysql mysql 5 Jun 28 19:36 mysql.sock.lock drwxr-x---. 2 mysql mysql 4096 Jun 18 00:59 performance_schema -rw-r----- 1 mysql mysql 5 Jun 28 19:36 resourcepool-0559.pid drwxr-x---. 2 mysql mysql 12288 Jun 18 00:59 sys [root@leo data]# cp -a mysql-bin.* /home/mysql/backup/ [root@leo data]# ll /home/mysql/backup/ total 1612 -rw-r--r-- 1 root root 72 Jun 29 02:42 bak_2019-06-29.flag -rw-r--r-- 1 root root 804515 Jun 29 02:42 bak_2019-06-29.sql -rw-r----- 1 mysql mysql 177 Jun 25 22:54 mysql-bin.000003 -rw-r----- 1 mysql mysql 815245 Jun 25 23:17 mysql-bin.000004 -rw-r----- 1 mysql mysql 642 Jun 28 17:41 mysql-bin.000005 -rw-r----- 1 mysql mysql 5909 Jun 29 02:45 mysql-bin.000006 -rw-r----- 1 mysql mysql 76 Jun 28 19:36 mysql-bin.index
Recovery steps
1. Stop external access to the database to prevent data damage caused by update.
2. Because -- master data = 2 is added to the backup statement, the binlog corresponding recovery point after full backup can be recorded.
[root@leo data]# cd /home/mysql/backup/ [root@leo backup]# ll total 1612 -rw-r--r-- 1 root root 72 Jun 29 02:42 bak_2019-06-29.flag -rw-r--r-- 1 root root 804515 Jun 29 02:42 bak_2019-06-29.sql -rw-r----- 1 mysql mysql 177 Jun 25 22:54 mysql-bin.000003 -rw-r----- 1 mysql mysql 815245 Jun 25 23:17 mysql-bin.000004 -rw-r----- 1 mysql mysql 642 Jun 28 17:41 mysql-bin.000005 -rw-r----- 1 mysql mysql 5909 Jun 29 02:45 mysql-bin.000006 -rw-r----- 1 mysql mysql 76 Jun 28 19:36 mysql-bin.index [root@leo backup]# sed -n '22p' bak_2019-06-29.sql -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=5181;
3. Convert binlog to SQL
[root@leo backup]# mysqlbinlog -d test mysql-bin.000006 --start-position=5181 -r test.sql [root@leo backup]# cat test.sql
4. In the actual environment, mysql-bin.00000 7... Needs to be converted to SQL in succession.
5. Delete the drop statement in test.sql
[root@leo backup]# grep -w drop test.sql drop database test [root@leo backup]# sed -i '/drop database test/d' test.sql [root@leo backup]# grep -w drop test.sql
6. Full recovery database
[root@leo backup]# mysql -u root -p -S /home/mysql/3306/data/mysql.sock < bak_2019-06-29.sql Enter password: [root@leo backup]# mysql -u root -p -S /home/mysql/3306/data/mysql.sock -e "select * from test.leo"; Enter password: +------+----------+ | id | name | +------+----------+ | 1 | liufeng | | 2 | zhangsan | | 3 | liufeng | | 4 | zhangsan | +------+----------+
7. Restore binlog
[root@leo backup]# mysql -u root -p -S /home/mysql/3306/data/mysql.sock < test.sql Enter password: [root@leo backup]# mysql -u root -p -S /home/mysql/3306/data/mysql.sock -e "select * from test.leo"; Enter password: +------+----------+ | id | name | +------+----------+ | 1 | liufeng | | 2 | zhangsan | | 3 | liufeng | | 4 | zhangsan | | 5 | liufeng | | 6 | zhangsan | +------+----------+
8. Verify data and restore external access to database