mysql主从复制

全栈开发工程师 2020年06月18日 30次浏览

mysql主从复制

通过binlog配置主从

1. 配置主从库的配置

修改my.conf

# vi /etc/my.cnf

加入如下三行代码

log-bin=mysql-bin  //将mysql二进制日志取名为mysql-bin

binlog_format=mixed //二进制日志的格式,有三种:statement/row/mixed,具体分别不多做解释,这里使用mixed

server-id=101 //为服务器设置一个独一无二的id便于区分,这里使用ip地址的最后一位充当server-id

保存,重启mysql

:wq 
service mysqld restart

同样的,进入从服务器,配置从服务器的my.cnf,重复步骤1即可。
唯一的区别是,server-id要改成从服务器的ip尾位,即server-id=105;其他两项是一样的,保存,并重启mySQL。

2. 在主服务器上创建同步账号

就像一把钥匙,从服务器拿着这个钥匙,才能到主服务器上来共享主服务器的日志文件。

进入主服务器的mysql界面

# mysql -u root -p 111111     //我这里mysql账号是root,密码是111111

在mysql操作界面下,输入下面一行命令:

GRANT replication slave ON *.* TO 'slave'@'%' IDENTIFIED BY '111111'; 

查看主服务器BIN日志的信息(执行完之后记录下这两值,然后在配置完从服务器之前不要对主服务器进行任何操作,因为每次操作数据库时这两值会发生改变).

show master status;
FilePositionBinlog_Do_DBBinlog_Ignore_DBExecuted_Gtid_Set
mysql-bin.0000042475test-master-slave

1 row in set (0.00 sec)

3.设置从服务器

进入从服务器mysql

# mysql -u root -p111111

关闭slave(如果你以前配置过主从的话,一定要先关闭)

stop slave;

开始配置,输入下面代码即可:

CHANGE MASTER TO
MASTER_HOST='192.168.119.128',
MASTER_USER='replication',
MASTER_PASSWORD='111111',
MASTER_LOG_FILE='mysql-bin.000004',
MASTER_LOG_POS=154; 

参数解释

MASTER_HOST:设置要连接的主服务器的ip地址

MASTER_USER:设置要连接的主服务器的用户名

MASTER_PASSWORD:设置要连接的主服务器的密码

Mater_LOG_FILE:设置要连接的主服务器的bin日志的日志名称,即第3步得到的信息

MASTER_LOG_POS:设置要连接的主服务器的bin日志的记录位置,即第3步得到的信息,(这里注意,最后一项不需要加引号。否则配置失败)

先在从服务器配置完成,启动从服务器:

start slave;

查看是否配置成功:

show slave status;

返回结果

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.119.128
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000120
          Read_Master_Log_Pos: 1885
               Relay_Log_File: Slave1-relay-bin.000003
                Relay_Log_Pos: 742
        Relay_Master_Log_File: mysql-bin.000120
             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: 1885
              Relay_Log_Space: 2606
              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: 128
                  Master_UUID: 0ced3554-72fd-11e7-86ca-000c293c4a02
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 0ced3554-72fd-11e7-86ca-000c293c4a02:1-6
            Executed_Gtid_Set: 0ced3554-72fd-11e7-86ca-000c293c4a02:1-6
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 

1 row in set (0.00 sec)

通过gtid配置主从

基于GTID的复制是MySQL 5.6后新增的复制方式.
GTID (global transaction identifier) 即全局事务ID, 保证了在每个在主库上提交的事务在集群中有一个唯一的ID.
在原来基于日志的复制中, 从库需要告知主库要从哪个偏移量进行增量同步, 如果指定错误会造成数据的遗漏, 从而造成数据的不一致.
而基于GTID的复制中, 从库会告知主库已经执行的事务的GTID的值, 然后主库会将所有未执行的事务的GTID的列表返回给从库. 并且可以保证同一个事务只在指定的从库执行一次.

GTID是由server_uuid和事物id组成,格式为:GTID=server_uuid:transaction_id。server_uuid是在数据库启动过程中自动生成,每台机器的server-uuid不一样。uuid存放在数据目录的auto.conf文件中,而transaction_id就是事务提交时系统顺序分配的一个不会重复的序列号。

GTID的好处:

(1)GTID使用master_auto_position=1代替了binlog和position号的主从复制搭建方式,相比binlog和position方式更容易搭建主从复制。

(2)GTID方便实现主从之间的failover,不用一步一步的去查找position和binlog文件。

GTID模式复制搭建过程中注意事项:

主从需要设置如下参数(一般直接在配置文件/etc/my.cnf下直接添加):

主库配置:

gtid_mode=on

enforce_gtid_consistency=on

log_bin=on

server-id=数字(主从不能相同)

binlog_format=row

从库配置

gtid_mode=on

enforce_gtid_consistency=on

log_slave_updates=1 //5.7后不需要配置

server-id= 数字(主从不能相同)

从库配置主机信息

CHANGE MASTER TO
MASTER_HOST='192.168.119.128',
MASTER_USER='replication',
MASTER_PASSWORD='111111',
master_auto_position=1;

其他和普通模式一致

MGR组复制

MGR(MySQL Group Replication)是MySQL官方在5.7.17版本引进的一个数据库高可用与高扩展的解决方案,以插件形式提供,实现了分布式下数据的最终已执行,总结MGR的特点如下:

1. 高一致性:基于分布式paxos协议实现组复制,保证数据一致性

2. 高容错性:自动检测机制,只要不是大多数节点都宕机就可以继续工作,内置防脑裂保护机制;

3. 高扩展性:节点的添加与移除会自动更新组成员信息,新节点加入后,自动从其他节点同步增量数据,知道与其他节点数据一致;

4. 高灵活性:提供单住和多主模式,单主模式在主库宕机后能够自动选主,所有写入都在主节点进行,多主模式支持多节点写入。

搭建步骤

配置服务器基本信息

实验环境

角色Ip地址host
node-1192.168.119.128Master.Hadoop
node-2192.168.119.129Slave1.Hadoop
node-3192.168.119.130Slave2.Hadoop

为了方便实验,先关闭linux防火墙和selinux

# systemctl stop firewalld

# setenforce 0
# 或者修改/etc/selinux/config 文件SELINUX=disabled

由于我之前的虚拟机都是拷贝的,所以数据库的uuid是一样,所以也需要修改,否则会出现相同某些机器连接不上

配置主服务器

修改my.cnf

vi /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=row
log-slave-updates = 1
sync-master-info = 1
sync_binlog = 1


skip_slave_start = 1


gtid_mode=on
enforce_gtid_consistency=on
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE



transaction_write_set_extraction=XXHASH64      #以便在server收集写集合的同时将其记录到二进制日志。写集合基于每行的主键,并且是行更改后的唯一标识此标识将用于检测冲突。
loose-group_replication_group_name="5db40c3c-180c-11e9-afbf-005056ac6820"     #组的名字可以随便起,但不能用主机的GTID! 所有节点的这个组名必须保持一致!
loose-group_replication_start_on_boot=off    #为了避免每次启动自动引导具有相同名称的第二个组,所以设置为OFF。
loose-group_replication_local_address= "192.168.119.128:24901"
loose-group_replication_group_seeds= "192.168.119.128:24901,192.168.119.129:24901,192.168.119.130:24901"
loose-group_replication_bootstrap_group=off
loose-group_replication_single_primary_mode=off      #关闭单主模式的参数(本例测试时多主模式,所以关闭该项)
loose-group_replication_enforce_update_everywhere_checks=on    #开启多主模式的参数
loose-group_replication_ip_whitelist="192.168.119.128,192.168.119.129,192.168.119.130,127.0.0.1"    # 允许加入组复制的客户机来源的ip白名单
server-id=128

然后重启mysql

[root@Master-Hadoop ~]# mysql -p123456   #进入mysql


mysql> SET SQL_LOG_BIN=0;                       #即不记录二进制日志
Query OK, 0 rows affected (0.00 sec)
    
mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_slave@'%' IDENTIFIED BY 'slave@123';
Query OK, 0 rows affected, 1 warning (0.00 sec)
    
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
    
mysql> reset master;
Query OK, 0 rows affected (0.11 sec)
    
mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)
    
mysql> CHANGE MASTER TO MASTER_USER='rpl_slave', MASTER_PASSWORD='slave@123' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.29 sec)
    
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';            #安装复制组插件
Query OK, 0 rows affected (0.04 sec)

mysql> SHOW PLUGINS; #可以看到一堆插件



mysql> STOP GROUP_REPLICATION;
Query OK, 0 rows affected (0.00 sec)
   
mysql> SET GLOBAL group_replication_bootstrap_group=ON;  #只在第一个节点执行这个步骤
Query OK, 0 rows affected (0.00 sec)
   
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (2.14 sec)
   
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)
   
mysql> SELECT * FROM performance_schema.replication_group_members;#可以看到对应的节点信息

从服务器配置

配置MGR-node2节点的my.cnf文件

只需要修改 server_id 和 loose-group_replication_local_address的配置即可