mysql数据库备份

搭建mysql主从结构实现数据的自动备份

完全备份/恢复

备份操作：mysqldump命令

备份命令格式：mysqldump 源库名 > 路径 /xxx.sql

源库名的表示

– --all-databases 或 -A 所有库

[root@host ~]# mysqldump -uroot -p123456 -A  >/mybakdata/all.sql

– 数据库名 单个库

eg:[root@host ~]# mysqldump -uroot -p123456 db1 user >/mybakdata/db1.user.sql

– 数据库名 表名  单张表

[root@host ~]# mysqldump -uroot -p123456 db1 user >/mybakdata/db1.user.sql

– -B 数据库 1 数据库 2  备份多个库

[root@host ~]# mysqldump -u root -p123456 -B db1  db2   >/mybakdata/twodb.sql

• 注意事项

– 无论备份还是恢复,都要验证用户及权限

恢复操作：mysql命令

[root@host ~]# mysql -uroot -p123456 < /mybakdata/twodb.sql

[root@host ~]# mysql -uroot -p123456 db1 < /mybakdata/db1_user.sql

mysql>  create database db5;

mysql> use db5

mysql> source  /mybakdata/db1_user.sql

每周一半夜23点把db1 库的所有数据去全部备份到系统的/mybakdb1目录下‘

vim /shell/allbakdb1.sh

if [ ! -e /mydatadb1 ];then

   mkdir /mydatadb1

fi

day=$(date +%F)

mysqldump -uroot -p123456 db1   > /mydatadb1/${day}-db1.sql

:wq

增量备份/恢复

1、启用mysql服务的binlog日志，实现增量备份/恢复

binlog日至是一个二进制日志：记录除查询之外的SQL命令

select desc show

insert delect update drop granp remove

启用 binlog 日志

采用 binlog 日志的好处

– 记录除查询之外的所有 SQL 命令,

– 可用于数据恢复

– 配置 mysql 主从同步的必要条件

[root@dbsvr1 ~]# vim /etc/my.cnf

[mysqld]

.. ..

log_bin // 启用 binlog 日志

server_id=100 // 指定 id 值

[root@dbsvr1 ~]# systemctl restart mysqld

分析 binlog 日志

mysql> show variables like "binlog_format"; //查看日志当前记录格式

三种记录格式 :

1. statement :每一条修改数据的 sql 命令都会记录在 binlog 日志中。

2. row: 不记录 sql 语句上下文相关信息,仅保存哪条记录被修改。

3. mixed: 是以上两种格式的混合使用。

mkdir /logdir

chown -R mysql /logdir/

]# vim /etc/my.cnf

[mysqld]

server_id=50

#log-bin

log-bin=/logdir/plj

binlog_format=mixed

max_binlog_size=10m

#skip-grant-tables

validate_password_policy=0

validate_password_length=6

#default-storage-engine=MyISAM

]# systemctl restart mysqld

查看mysql二进制文件

命令格式：mysqlbinlog  /路径/文件名

eg:  mysqlbinlog /logdir/plj.000001 

执行binlog日志文件里的sql命令恢复数据。

记录sql命令的方式：时间点  偏移量

命令格式：mysqlbinlog   [选项]  日志文件

选项： --start-position=数字 --stop-position=数字

--start-datetime="yyyy-mm-dd hh:mm:ss" --stop-datetime="yyyy-mm-dd hh:mm:ss"

eg:mysqlbinlog --start-position=619  --stop-position=740 /logdir/plj.000001  |  mysql -u root -p123456

eg:mysqlbinlog --start-datetime="2018-06-22 15:39:25"  --stop-datetime="2018-06-22 16:37:22"   /logdir/plj.000001 | mysql -uroot -p123456

马上生成一个新的binlog文件

1、flush logs； //数据库中输入flush logs；

2、备份数据库: mysqldump -uroot -p123456 --flush-logs  db1 > /root/db1.sql

3、终端输入:mysql -uroot -p123456  -e  "flush logs"

4、重起服务:systemctl restart mysqld

显示binlog下的当前日志：show master status;

删除binlog指定日志之前的日志：purge master logs to "binlog 文件名 "；

删除所有 binlog 日志,重建新日志：reset master;

2、安装第三方软件percona，提供innobackupex命令做增量备份/恢复

主要含两个组件

– xtrabackup : C 程序,支持 InnoDB/XtraDB

– innobackupex :以 Perl 脚本封装 xtrabackup ,还支持 MyISAM

2.1 安装提供命令的软件包

# yum -y install perl-Digest-MD5  perl-DBD-mysq

# rpm -ivh libev-4.15-1.el6.rf.x86_64.rpm          //yum仓库没有此模块

# rpm -ivh percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm  //yum仓库没有，软件名percona-xtraabackup-24 

# rpm -ql percona-xtrabackup-24    //查看并列出安装的软件

man innobackupex

innobackupex <选项>

xtrabackup  只能做

完全备份

备份当前服务器上的所有数据：innobackupex  --user root  --passwrod 123456 /allbak  --no-timestamp

ls /allbak

备份指定库的所有数据：innobackupex --user root  --password 123456  --databases='mysql sys performance_schema buydb' /buydbfull --no-timestamp

ls /buydbfull

完全恢复(要求数据库目录为空)

1、重做日志  --apply-log

innobackupex --user root --password 123456 --apply-log /buydbfull 

2、清空数据库目录

systemctl stop mysqld

rm -rf /var/lib/mysql/*

3、恢复数据  --copy-back

innobackupex  --user root --password 123456 --copy-back  /buydbfull

chown  -R mysql:mysql  /var/lib/mysql

4、启动数据库服务

systemctl start mysqld

innobackupex 增量备份、恢复

首次备份（完全备份）

]# innobackupex --user root --password 123456 --databases="mysql sys performance_schema buydb" /fullbuydb --no-timestamp

ls /fullbuydb

写数据，做增量备份

--incremental 指定增量备份文件存储目录

--incremental-basedir=上次备份文件存储目录

insert into buydb.a values(201),(202),(203),(204),(205),(206),(207),(208),(209),(210);

]# innobackupex --user root --password 123456 --databases="mysql sys performance_schema buydb" --incremental /new1dir --incremental-basedir=/fullbuydb  --no-timestamp

ls /new1dir 

写数据，做增量备份

insert into buydb.a values(201),(202),(203),(204),(205),(206),(207),(208),(209),(210);

]# innobackupex --user root --password 123456 --databases="mysql sys performance_schema buydb" --incremental /new2dir --incremental-basedir=/new1dir  --no-timestamp

ls /new2dir 

innodb 和 xtrdb

cd /var/lib/mysql

事务日志文件

ib_logfile0

ib_logfile1

ibdata1    //记录所有的

lns 日志序列号

 xtrabackup_checkpoints

增量恢复

增量备份数据目录：--incremental-dir=备份数据的目录名

重做并合并日志 ：--apply-log --redo-only

]# innobackupex --user root --password 123456 --apply-log  --redo-only  /fullbuydb/

]# innobackupex --user root --password 123456 --apply-log  --redo-only  /fullbuydb/  --incremental-dir=/new1dir

]# innobackupex --user root --password 123456 --apply-log  --redo-only  /fullbuydb/  --incremental-dir=/new2dir

清空数据库目录

rm -rf /var/lib/mysql/*

停止服务

systemctl stop mysqld

rm -rf /var/lib/mysql/*

恢复数据：innobackupex --copy-back /fullbuydb

启动服务：systemctl start mysqld

在innobackupex 完全备份中，恢复某单个表

--expoxt 导出表信息

前提：首先有一个完全备份的数据库文件

]# innobackupex --user root --password 123456 --databases="buydb" /buydbfull2  --no-timestamp

删除表

mysql> drop table buydb.b;

恢复步骤：

1、从备份数据里导出表信息

]# innobackupex --user root --password 123456 --databases="buydb" /buydbfull2 --apply-log --export   --no-timestamp

2、创建删除的表（表的结构和删除的时候相同）

mysql> create  table buydb.b(name char(15));

3、删除表空间

mysql> alter table buydb.b discard tablespace;

4、把导出的表信息文件拷贝到数据库目录下

mysql> system cp /buydbfull2/buydb/b.{exp,cfg,ibd} /var/lib/mysql/buydb/

mysql> system chown mysql:mysql /var/lib/mysql/buydb/b.{exp,cfg,ibd}

5、导入表空间

6、查看数据select * from buydb.b;

*.ibd  表空间文件（存的是数据记录）

00  23   *   *   1     /shell/allbak.sh  -->完全备份innobackupex

00  23   *   *   2-7    /shell/newdata.sh   -->增量innobackupex