搭建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