1 如何开启二进制备份机制
1 cd /etc/my.conf
2 在server字段下添加log_bin=/tmp/binlog #一般不建议此文件加后缀
3 service restart mysqld
4 查看/tmp目录下是否自动生成二进制日志文件
5 mysqlbinlog binlog.log 即可查看二进制日志文件内容
以下是查看日志命令
SHOW BINARY LOGS; #查看二进制日志信息
FLUSH LOGS; #刷新二进制日志信息
SHOW MASTER STATUS; #查看主服务器状态
SHOW BINLOG EVENTLOGS IN "日志名.000001" from 313; #查看序号313以后的二进制日志事件信息
SET @@session.sql_log_bin=OFF; #临时停止记录数据库监控操作记录
备份机制分全量备份,差异备份,增量备份。
完全备份 物理或逻辑上一次性将全部数据备份。
增量备份:仅备份自上一次完全备份或 增量备份以来变量的那部数据;优点,备份量小,
只备份一个小区间内的增量内容,缺点,恢复时需要基于全部备份之后的次叠加恢复。
差异备份:仅备份自上一次完全备份以来变量的那部数据;缺点,备份量大,优点,恢复
时不用分段进行叠加恢复
备份工具:
逻辑备份命令:
mysqldump -uroot -ppassword --all-databases -x -R -E --triggers --flush-log --master-data=2
/root/all.sql
#将备份存到/root/all.sql文件中
mysqldump:mysql服务自带的备份工具;逻辑备份工具;
完全、部分备份;
InnoDB:热备;
MyISAM:温备;
mysqldump -uroot -h172.18.xx.xx -ppassword --single-transaction -R --triggers -E --databases hidb
--master-data=2 --flush-logs > /root/hidb.sql
lvm2:快照(请求一个全局锁),之后立即释放锁,达到几乎热备的效果;物理备份;
注意:不能仅备份数据文件;要同时备份事务日志;
前提:要求数据文件和事务日志位于同一个逻辑卷;
xtrabackup:
由Percona提供,开源工具,支持对InnoDB做热备,物理备份工具;
完全备份、部分备份;
完全备份、增量备份;
完全备份、差异备份;
mysqlhotcopy
select:
备份:SELECT cluase INTO OUTFILE 'FILENAME';
恢复:CREATE TABLE
导入:LOAD DATA
xtrabackup工具
这个工具是用innobackupex 做主程序调用的。
innobackupex --user=DBUSER --password=DBPASS /PATH/TO/BACKUP-DIR
备份完成看到completed ok才算是成功备份
如果要使用一个最小权限的用户进行备份,则可基于如下命令创建此类用户:
mysql>CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY 's3cret';
mysql>REVOKE ALL PRIVILEGES,GRANT OPTION FROM 'bkpuser';
mysql>GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'bkpuser'@'localhost';
mysql>FLUSH PRIVILEGES;
要实现第一次全量备份,可以使用下边的命令进行:
innobackupex –user=root –password=password /backup #这里的路径要使用绝对路径,此次备份是第一次备份,是全量备。
实现第一次增量或差异备份
innobackupex –user=root –password=amoukj –host=localhost –incremental /BASEDIR –incremental-basedir=/BASEDIR/date 此后的路径相对于上次增量备份文件路径来备份的是增量备份,路径基于第一次全量备份文件路径而言则是差异备份。
其中,BASEDIR指的是完全备份所在目录,此命令执行结束后,innobackupex命令会在/backup目录中创建一个新的以时间命令的目录以存放所有的增量备份数据,另外在执行过增量备份之后再一次记性增量备份时,其–incremental-basedir应该指向上一次的增量备份所在的目录。
需要注意的是,增量备份仅能应用于innoDB或XtraDB表,对于MYISAM表而言,执行增量备份时其实进行的是完全备份。
需要查看二进制日志里修改操作
cd /BASEDIR
cat xtrabackup_binlog_info #记录下事件日志文件和标识符ID
mysqlbinlog -j xxx /BASEDIR/logs/yyy #yyy是看到的日志文件名称
准备(prepare)增量备份与整理完全备份有着一些不同,尤其要注意的是:
1) 需要在每个备份(包括扩完全和各个增量备份)上,将已提交的事物进行“重放”,
“重放”之后,所有的备份数据将合并到完全备份上。
2) 基于所有的备份将未提交的事物进行“回滚”。
于是操作就变成了:
innobackupex --apply-log --redo-only BASE-DIR
接着执行:
innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-1
而后是第二个增量:
innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-2
其中BASE-DIR指的是完全备份所在的目录,而INCREMENTAL-DIR指的是第一次增量
备份的目录,INCREMENTAL-DIR-2指的是第二次增量备份的目录,其他的以此类推,
即如果又多次增量备份,每一次都要执行如上操作。
最后一个增量命令完成后,要在完全备份上做“回滚”操作。
innobackupex --apply-log BASE-DIR
在别的机器恢复数据库:
innobackupex --copy-back /basedir #数据所在的绝对路径
cd /var/lib/mysql/
chown -R mysql.mysql /basedir
systemctl start mariadb
以上都做完之后,数据就算打包完整了,可以传输到别的机器上进行,因为回滚过的数据不能被再使用了
恢复数据库:
GRANT REPLICATION CLIENT,REPLICATION SLAVE ON *.* TO 'repluser '@'172.18.0.%' IDENTIFIED BY 'replpass';
FLUSH PRIVILEGES;
======================================================
MARIADB主从复制
1 安装mariadb并启动
主节点:
vim /etc/my.cnf.d/server.cnf
在server模块下边插入
skip_name_resolve= on #开启独立表空间模式,每个数据库的每个表都会生成一个数据空间
innodb_file_per_table= on
max_connections =20000
relay_log = relay-log #定义relay_log的位置和名称,如果值为空,则默认位置在数据文件的目录
server_id = 1
log_bin = master.log
配置完成后,登录mysql,FLUSH PRIVILEGES;
从节点:
skip_name_resolve = ON
innodb_file_per_table = ON
max_connections = 20000
relay_log = relay-log
server_id = 2 #这里的节点数不能与主节点相同
read_only=ON #设定从节点只读属性
配置完成,mysql 登录
SHOW SLAVE STATUS;
START SLAVE IO_THREAD,SQL_THREAD; #启动IO和SQL线程
======================================================
主主同步:
互为主从:两个节点各自都要开启binlog和relay log;
1 数据不一致;
2 自动增长id;
定义一个节点使用奇数id;
auto_increment_offset=1
auto_increment_increment=2
另一个节点使用偶数id;
auto_increment_offset=2
auto_increment_increment=2
配置:
1 server_id必须要使用不同值;
2 均启用binlog和relay log;
3 存在自动增长id的表,为了使得id不相冲突,需要定义其自动增长方式;
服务启动后执行如下两步:
4 都授权有复制权限的用户账号;
5 各把对方指定为主节点;
======================================================
两种复制方式应注意的问题:
1 从服务设定为只读
在从服务器启动read_only,但仅对非SUPER权限的用户有效
阻止所有用户
mysql>FLUSH TABLES WIHT READ LOCK
2 尽量确保复制时的事物安全
在master节点启用参数
sync_binlog= ON
如果用到的是InnoDB存储引擎
Innodb_flush_logs_trx_commit=on
innodb_support_xa=on
3 从服务器意外终止时尽量避免自动启动复制线程
4 从节点:设置参数
sync_master_info=ON
sync_relay_log_info=ON