mysql-mariadb 双主热备

概述
    mysql从3.23版本开始提供复制功能，复制是将主库的DDL和DML操作通过二进制日志传递到复制服务器（从库）上，然后从库对这些日志重新执行（重做），从而使得主库和从库保持数据一致。

    mysql复制的优点：

如果主库出现问题，可以快速切换到从库提供服务
可以在从库执行查询操作，降低主库的访问压力。
可以在从库进行备份，以免备份期间影响主库的服务。
注意：由于mysql实现的异步复制，所以主库和从库数据之间存在一定的差异，在从库执行查询操作需要考虑这些数据的差异，一般只有更新不频繁和对实时性要求不高的数据可以通过从库插叙，实行要求高的仍要从主库查询。

复制原理
mysql的复制原理大致如下。

(1)首先，mysql主库在事务提交时会把数据库变更作为事件Events记录在二进制文件binlog中；mysql主库上的sys_binlog控制binlog日志刷新到磁盘。

(2)主库推送二进制文件binlog中的事件到从库的中继日志relay log,之后从库根据中继日志重做数据库变更操作。通过逻辑复制，以此来达到数据一致。

Mysql通过3个线程来完成主从库之间的数据复制：其中BinLog Dump线程跑在主库上，I/O线程和SQl线程跑在从库上。当从库启动复制（start slave）时，首先创建I/O线程连接主库，主库随后创建Binlog Dump线程读取数据库事件并发给I/O线程，I/O线程获取到数据库事件更新到从库的中继日志Realy log中去，之后从库上的SQl线程读取中继日志relay log 中更新的数据库事件并应用。

复制中的各类文件
除了 二进制文件binlog 、中继日志relay-log外，为了保证从库crash重启后，从库的io线程和sql线程仍能够知道从哪里复制，从库上默认还创建两个日志文件master.info和relay-log.info用来保存复制进度。

三种复制方式
二进制binlog的格式有三种：

statement：基于sql的binlog，每条修改数据的sql都会保存到binlog里。

row：基于行级别，记录每一行数据的变化，也就是将每一行数据的变化都记录到binlog里，记录非常详细。

mixed：混合statement和row模式。

复制的常见三种架构：
一主多从
多级复制
双主复制 

一、搭建主从复制

1、修改 主 配置文件（my.cnf） [mysqld]下增加
[mysqld]
server-id=1 #配置server-id 唯一
log-bin=log-bin  ###bin log 文件名字
binlog_format=mixed  ###binlog的格式

2、保存配置 并重启数据库。重启完成连接数据库，使用 show master status 命令查看主库的状态。
MariaDB [(none)]> show master status;
+----------------+----------+--------------+------------------+
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------+----------+--------------+------------------+
| log-bin.000001 |      245 |              |                  |
+----------------+----------+--------------+------------------+

3、修改 从 配置文件（my.cnf）， [mysqld]下增加
[mysqld]

server-id=2  ##配置server id 唯一
relay-log=relay-log ###中继日志 文件名
binlog_format=mixed  ###binlog的格式

4、保存配置 重启数据

5、主机授权从机复制，主 机器 上操作
mysql> grant replication slave on *.* to ‘slave’@‘192.168.1.2’ identified by '123456'; 
Query OK, 0 rows affected (0.02 sec)
##解释下 赋予 replication slave 权限给 slave 用户 ，*.* 代表所有库的所有表， 123456 是登录密码
6、从机 配置监听主机

## 确保 停止salve
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
 
#开始配置
#master_host      配置你监听的主机地址
#master_port      你监听主机的端口
#master_user      用户名，就是我们刚刚在主机授权时的用户名
#master_password  授权的用户密码
#master_log_file  是你主机二进制文件，可以在主机中使用show mater status 查看
#master_log_pos   开始复制的位置。 show mater status 查看
mysql> 
mysql> change master to 
    -> master_host='192.168.1.1', 
    -> master_port=3307,
    -> master_user='slave',
    -> master_password='123456',
    -> master_log_file='log-bin.000001',
    -> master_log_pos=245;
Query OK, 0 rows affected (0.06 sec)
 
#开启slave 
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
 
#配置完成 查看 slave 状态
#可以看到 Slave_IO_State: Waiting for master to send event slave状态是等待主机发送事件
#我们只要观察Slave_IO_Running: Yes
#           Slave_SQL_Running: Yes
#这两个线程都是yes
 
mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: localhost
                  Master_User: slave
                  Master_Port: 3307
                Connect_Retry: 60
              Master_Log_File: log-bin.000001
          Read_Master_Log_Pos: 245
               Relay_Log_File: relay-log.000001
                Relay_Log_Pos: 258
        Relay_Master_Log_File: log-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 254
              Relay_Log_Space: 419
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 107
1 row in set (0.00 sec)
 
ERROR: 
No query specified
 
mysql> 
至此从机 已配置完成。

二、双主热备实现

1、slave 从机 开启binlog，[mysqld]下增加

log-bin=log-bin   ###bin-log日志的名称
binlog_format=mixed ###binlog的格式
log_slave_updates=1 
----------------------------------
log_slave_updates =1 是代表通过主机同步过来的数据操作也写入到bin-log 中，这样就能保证 从库的bin-log和主库的bin-log是一致的。编辑完成后，保存退出。

2、保存配置 并重启数据库。重启完成连接数据库，使用 show master status 命令查看主库的状态。

MariaDB [(none)]> show master status;
+----------------+----------+--------------+------------------+
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------+----------+--------------+------------------+
| log-bin.000001 |      245 |              |                  |
+----------------+----------+--------------+------------------+

2、从机授权主机 复制

mysql> grant replication slave on *.* to ‘salve’@'192.168.1.1' identified by '123456';

3、主机 开启 relay_log ，修改主机配置文件，[mysqld]下增加

relay_log=msyql-relay-log
log_slave_updates=1

4、配置 主机 开始 监听 从机

mysql> change master to
    -> master_host='192.168.1.2',
    -> master_port=3306,
    -> master_user='slave',
    -> master_password='123456',
    -> master_log_file='log-bin.000001',
    -> master_log_pos=245;
Query OK, 0 rows affected (0.06 sec)
 
##开启复制
三、其他参数配置

log-bin = mysql-bin     #开启mysql的binlog日志功能
sync_binlog = 1         #master 配置。控制数据库的binlog刷到磁盘上去 , 0 不控制，性能最好，1每次事物提交都会刷到日志文件中，性能最差，最安全
binlog_format = mixed   #binlog日志格式，mysql默认采用statement，建议使用mixed
expire_logs_days = 7                           #binlog过期清理时间
max_binlog_size = 100m                    #binlog每个日志文件大小
binlog_cache_size = 4m                        #binlog缓存大小
max_binlog_cache_size= 512m              #最大binlog缓存大

binlog-ignore-db=mysql    #不生成日志文件的数据库，多个忽略数据库可以用逗号拼接，或者 复制这句话，写多行
binlog_do_db = test1,test2    #日志记录那些数据库
binlog_ignore_db = mysql,performance_schema,information_schema #日志记录忽略那些数据库的

auto-increment-offset = 1     # 自增值的偏移量
auto-increment-increment = 1  # 自增值的自增量

mysql中有自增长字段，在做数据库的主主同步时需要设置自增长的两个相关配置：auto_increment_offset和auto_increment_increment。

• auto_increment_offset表示自增长字段从那个数开始，他的取值范围是1 .. 65535
• auto_increment_increment表示自增长字段每次递增的量，其默认值是1，取值范围是1 .. 65535

在主主同步配置时，需要将两台服务器的auto_increment_increment增长量都配置为2，而要把auto_increment_offset分别配置为1和2.

这样才可以避免两台服务器同时做更新时自增长字段的值之间发生冲突。

slave-skip-errors = all #跳过从库错误

replicate_do_db = test1,test2 #是在slave上配置，指定slave要复制哪个库
replicate-ignore-db=mysql,performance_schema,information_schema #是在slave上配置，指定slave要忽略哪个库
replicate-wild-ignore-table=mysql.%  # slave上配置。从库复制跳过的表
relay_log_recovery = 1  　　 #slave上配置。从库建议开启，有利于数据一致性
log_slave_updates = 1   　　 #如果从库还会用做主库，建议开启

2、relay log的相关参数说明

通过语句：show variables like '%relay%'，查看先骨干的relay的所有相关参数
mysql> show variables like '%relay%';
+-----------------------+----------------+

| Variable_name | Value |

+-----------------------+----------------+
| max_relay_log_size        
| relay_log                 
| relay_log_basename       
| relay_log_index          
| relay_log_info_file        
| relay_log_info_repository  
| relay_log_purge            
| relay_log_recovery         
| relay_log_space_limit     
| sync_relay_log               
| sync_relay_log_info  
+-----------------------+----------------+

参数详细解释：
2.1 max_relay_log_size：
标记relay log 允许的最大值，如果该值为0，则默认值为max_binlog_size(1G)；如果不为0，则max_relay_log_size则为最大的relay_log文件大小；
2.2  relay_log：
定义relay_log的位置和名称，如果值为空，则默认位置在数据文件的目录（datadir），文件名为host_name-relay-bin.nnnnnn（By default, relay log file names have the form host_name-relay-bin.nnnnnn in the data directory）；
2.3 relay_log_index：
同relay_log，定义relay_log的位置和名称；一般和relay-log在同一目录
2.4 relay_log_info_file：
设置relay-log.info的位置和名称（relay-log.info记录MASTER的binary_log的恢复位置和relay_log的位置）
2.5 relay_log_purge：
是否自动清空不再需要中继日志时。默认值为1(启用)。
2.6 relay_log_recovery：
当slave从库宕机后，假如relay-log损坏了，导致一部分中继日志没有处理，则自动放弃所有未执行的relay-log，并且重新从master上获取日志，这样就保证了relay-log的完整性。默认情况下该功能是关闭的，将relay_log_recovery的值设置为 1时，可在slave从库上开启该功能，建议开启。
2.7 relay_log_space_limit：
防止中继日志写满磁盘，这里设置中继日志最大限额。但此设置存在主库崩溃，从库中继日志不全的情况，不到万不得已，不推荐使用；
2.8 sync_relay_log：
这个参数和sync_binlog是一样的，
当设置为1时，slave的I/O线程每次接收到master发送过来的binlog日志都要写入系统缓冲区，然后刷入relay log中继日志里，这样是最安全的，因为在崩溃的时候，你最多会丢失一个事务，但会造成磁盘的大量I/O。
当设置为0时，并不是马上就刷入中继日志里，而是由操作系统决定何时来写入，虽然安全性降低了，但减少了大量的磁盘I/O操作。这个值默认是0，可动态修改，建议采用默认值。
2.9 sync_relay_log_info：
这个参数和sync_relay_log参数一样，当设置为1时，slave的I/O线程每次接收到master发送过来的binlog日志都要写入系统缓冲区，然后刷入relay-log.info里，这样是最安全的，因为在崩溃的时候，你最多会丢失一个事务，但会造成磁盘的大量I/O。当设置为0时，并不是马上就刷入relay-log.info里，而是由操作系统决定何时来写入，虽然安全性降低了，但减少了大量的磁盘I/O操作。这个值默认是0，可动态修改，建议采用默认值。
3、总结：以上只是简单的介绍了每个参数的作用，这些参数具体的设置还是需要根据每个用户的实际系统情况进行设置的；

四、相关连接

http://1987.name/1094.html

mysql主从复制原理 https://blog.csdn.net/qq_16399991/article/details/82749333

mysql搭建 主从复制 https://blog.csdn.net/qq_16399991/article/details/82740881

https://www.jianshu.com/p/19cb0f16dea4

https://www.cnblogs.com/myIvan/p/10164926.html

mysql复制-mysql双主热备实现  https://blog.csdn.net/qq_16399991/article/details/82771584

https://www.cnblogs.com/lzhdonald/archive/2020/04/13/12689712.html

怎么查看mysql 的binlog日志存放的位置 https://www.cnblogs.com/jpfss/p/11112283.html

CHANGE MASTER TO 语法详解 https://blog.csdn.net/jesseyoung/article/details/41942809

relay log 详细参数解释  https://blog.51cto.com/douya/1788753

五、mysql 其他命令

flush logs;    #生成新的log文件

reset slave;    #重置master ,执行完可以重新执行 change master to 命令

reset master;    #重置master binlog 日志，删除日志和index文件

show master status;  #查看master 状态 

show slave status \G;  #查看slave状态
 

查看从库的relay log
[root@mysqlb relaybin]# mysqlbinlog -vv slave-relay-bin.000010

六、如果主库里已经数据库数据了，那还得进行以下操作：

刷新表然后锁表（只允许查数据不允许写数据）：
flush tables with read lock;
然后备份要同步的数据库的数据，然后拷贝到从库里面
mysqldump -uroot -p test1>test1.sql;
mysqldump -uroot -p test2>test2.sql;
#从库要先创建数据库test1和test2,然后导入数据
mysql -uroot -p test1<test1.sql;
mysql -uroot -p test1<test2.sql;

命令介质下：source /xxx/xxx.sql
配置主从同步，然后解除锁表
unlock table