mysql增量备份恢复有几种方法_Mysql增量备份与恢复

不念不忘少年蓝@ 2023-01-11 09:29 227阅读 0赞

案例：

关闭防火墙：

systemctl stop firewalld

iptables -F

setenforce 0

安装mariadb mariadb-server

yum -y install mariadb

yum -y install mariadb-server

vim /etc/my.cnf

\[mysqld\]

server-id=1

log-bin=mysql-bin

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

symbolic-links=0

default-storage-engine = INNODB

character-set-server = utf8

collation-server = utf8\_general\_ci

\# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

\# Settings user and group are ignored when systemd is used.

\# If you need to run mysqld under a different user or group,

\# customize your systemd unit file for mariadb according to the

\# instructions in http://fedoraproject.org/wiki/Systemd

\[mysqld\_safe\]

log-error=/var/log/mariadb/mariadb.log

pid-file=/var/run/mariadb/mariadb.pid

\# include all files from the config directory

!includedir /etc/my.cnf.d

添加数据库、表，录入数据

\[root@localhost ~\]\# mysql -uroot -p123456

mysql> create database client;

mysql> use client;

mysql> create table user\_info(身份证 char(20) not null,姓名 char(20) not null,性别 char(4),用户ID号 char(10) not null,资费 int(10));

mysql> insert into user\_info values('000000006','张三','男','016','10');

mysql> insert into user\_info values('000000007','李四','女','017','91');

mysql> insert into user\_info values('000000008','王五','女','018','23');

mysql> select \* from user\_info;

先进行一次完全备份

\[root@localhost ~\]\# mkdir /mysql\_bak

\[root@localhost~\]\#mysqldump -uroot -p123456 client user\_info >/mysql\_bak/client\_userinfo-$(date +%F).sql

\[root@localhost ~\]\# mysqldump -uroot -p123456 --databases client >/mysql\_bak/client-$(date +%F).sql

\[root@localhost ~\]\# ls /mysql\_bak/

client-2018-12-14.sql  client\_userinfo-2018-12-14.sql

进行一次日志回滚(生成新的二进制日志)

\[root@localhost ~\]\# ls /usr/local/mysql/data

client  ibdata1  ib\_logfile0  ib\_logfile1  localhost.localdomain.err  mysql  mysql-bin.000001  mysql-bin.index  mysql.sock  test

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

\[root@localhost ~\]\# ls /usr/local/mysql/data

client  ibdata1  ib\_logfile0  ib\_logfile1  localhost.localdomain.err  mysql  mysql-bin.000001  mysql-bin.000002  mysql-bin.index  mysql.sock  test

继续录入新的数据

\[root@localhost ~\]\# mysql -uroot -p123456

mysql> use client;

mysql> insert into user\_info values('000000009','赵六','男','019','37');

mysql> insert into user\_info values('0000000010','孙七','男','020','36');

mysql> select \* from user\_info;

进行增量备份

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

\[root@localhost ~\]\# ls /usr/local/mysql/data

client   ib\_logfile0  localhost.localdomain.err  mysql-bin.000001  mysql-bin.000003  mysql.sock ibdata1  ib\_logfile1  mysql  mysql-bin.000002  mysql-bin.index   test

\[root@localhost ~\]\# mysqlbinlog --no-defaults --base64-output=decode-rows -v /mysql\_bak/mysql-bin.000002   //查看新操作的日志记录

\[root@localhost ~\]\# cp -p /usr/local/mysql/data/mysql-bin.000002 /mysql\_bak/

模拟误操作删除user\_info表

\[root@localhost ~\]\# mysql -uroot -p123456 -e 'drop table client.user\_info;'