双机热备:保持两个数据库的状态自动同步。对任何一个数据库的操作都自动同步到另外一个数据库,始终保持两个数据库数据一致。
说到mysql的备份,那就必须要了解(不是理解)mysql的备份原理,借用一整很多大神都用的图:
这个备份的过程分为两部分:
主(Master): 打开复制模式之后,主服务器Master, 会把自己的每一次改动都记录到 二进制日志 Binarylog 中。
从(Slave): 打开复制模式之后,从服务器Slave的I/O线程, 会用master上的账号登陆到 master上, 读取master的Binarylog, 写入到自己的中继日志 Relaylog, 然后自己的sql线程会负责读取这个中继日志,并执行一遍。 如是,主服务器上的更改就同步到从服务器上了。
实验需求:
本次实验用的是mysql5.7.20版本:
[root@ansible ~]# mysql -V
mysql Ver 14.14 Distrib 5.7.20, for Linux (x86_64) using EditLine wrapper
mysql5.7.20的安装如果还有问题,可以借鉴:
http://blog.csdn.net/weixin_37998647/article/details/78649092
实验两台主机的IP分别是:
192.168.1.121 我们称之为A,
192.168.1.185 我们称之为B,
方便起见,直接关掉防火墙,selinux
废话已经太多了,下面直接开干吧:
1、在A上给用于备份的用户授权(我们直接用root用户):
mysql> grant replication slave on *.* to 'root'@'192.168.1.185' identified by 'abc123';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_length=6;
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication slave on *.* to 'root'@'192.168.1.185' identified by 'abc123';
Query OK, 0 rows affected, 1 warning (0.01 sec)
(报错是因为密码太简单,该本版的mysql带有密码强壮度校验的模块,按照上面的方法设置一下就可以了。)
2. 开启主服务器的 binarylog。
[root@ansible ~]# vim /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
log-bin = mysql-bin
binlog_format = mixed
server-id = 1
read-only = 0
binlog-ignore-db = information_schema
binlog-ignore-db = mysql
binlog-ignore-db = performance_schema
binlog-ignore-db = sys
binlog-ignore-db = zabbix
auto-increment-increment = 10
auto-increment-offset = 1
在最后面加了一些参数:
log-bin = mysql-bin
binlog_format = mixed
server-id = 1
read-only = 0
binlog-ignore-db = information_schema
binlog-ignore-db = mysql
binlog-ignore-db = performance_schema
binlog-ignore-db = sys
binlog-ignore-db = zabbix
auto-increment-increment = 10
auto-increment-offset = 1
解释一下这些参数的的意义:
server-id 必须保证每个服务器不一样。 这可能和循环同步有关。 防止进入死循环。
bi