MySQL的备份与恢复( 二 ) _MySQL

Mysql的增量备份需要开启二进制功能。

[root@localhost /]# vim /etc/my.cnf
log-bin=/usr/local/mysql/mysql-bin 添加

[root@localhost /]# systemctl restart mysqld
[root@localhost /]# ls -l /usr/local/mysql/mysql-bin.*

-rw-rw----. 1 mysql mysql 120 8月 10 09:43 /usr/local/mysql/mysql-bin.000001
-rw-rw----. 1 mysql mysql 34 8月 10 09:43 /usr/local/mysql/mysql-bin.index
MySQL企业备份案例
1、一般恢复
1）添加数据库、表、录入信息

mysql> create database local；
mysql> use local;

Database changed

mysql> create table test(id int);

Query OK, 0 rows affected (0.00 sec)

mysql> insert into test values(1);

Query OK, 1 row affected (0.00 sec)

mysql> insert into test values(2);

Query OK, 1 row affected (0.01 sec)
2）先进行一次完全备份

[root@localhost ~]# mkdir /mysql_back
[root@localhost ~]# mysqldump -uroot -p local test >/mysql_back/local_test-$(date +%F).sql

Enter password:

[root@localhost ~]# ls /mysql_back/

local_test-2020-08-10.sql

[root@localhost ~]# mysqladmin -uroot -p flush-logs 生产新的二进制文件

Enter password:

[root@localhost ~]# ls -l /usr/local/mysql/mysql-bin.*

-rw-rw----. 1 mysql mysql 894 8月 10 10:03 /usr/local/mysql/mysql-bin.000001
-rw-rw----. 1 mysql mysql 120 8月 10 10:03 /usr/local/mysql/mysql-bin.000002
-rw-rw----. 1 mysql mysql 68 8月 10 10:03 /usr/local/mysql/mysql-bin.index
3）继续录入新的数据并进行增量备份

mysql> insert into test values(3);

Query OK, 1 row affected (0.01 sec)

mysql> insert into test values(4);

Query OK, 1 row affected (0.00 sec)

[root@localhost ~]# mysqladmin -uroot -p flush-logs

Enter password:

[root@localhost ~]# ls -l /usr/local/mysql/mysql-bin.*

-rw-rw----. 1 mysql mysql 894 8月 10 10:03 /usr/local/mysql/mysql-bin.000001
-rw-rw----. 1 mysql mysql 595 8月 10 10:06 /usr/local/mysql/mysql-bin.000002
-rw-rw----. 1 mysql mysql 120 8月 10 10:06 /usr/local/mysql/mysql-bin.000003
-rw-rw----. 1 mysql mysql 102 8月 10 10:06 /usr/local/mysql/mysql-bin.index