centos7 MySql双主同步

Bertha 。 2023-09-29 11:44 18阅读 0赞

##### MySql配置 #####

*  1.  修改3服务上的MySql配置文件
    
        [root@node2 mysql]# vim /etc/my.cnf
        [mysqld]
        basedir=/home/console/mysql
        datadir=/home/console/mysql/data
        port=3306   #端口
        server-id=1 #服务id
        
        #mysql双主配置
        #binlog-do-db=test  #需要记录到二进制日志的数据库
        binlog-ignore_db=mysql  #忽略记录二进制日志的数据库
        #replicate-do-db=test   #指定复制的数据库
        replicate_ignore_db=mysql   #不复制的数据库
        binlog-ignore-db=information_schema #不复制的数据库
        binlog-ignore-db=performance_schema #不复制的数据库
        log-slave-updates=1 #该从库是否写入二进制日志，如果需要成为多主则可启用。只读可以不需要
        auto_increment_offset=2 #该服务器自增列的初始值
        auto_increment_increment=2  #该服务器自增列增量
        relay_log=mysql-relay-bin   #从库的中继日志，主库日志写到中继日志，中继日志再重做到从库
        
        log-bin=mysql-bin
        max_connections=151
        max_connect_errors=30
        default-storage-engine=InnoDB
        transaction_isolation=REPEATABLE-READ
        socket=/home/console/mysql/tmp/mysql.sock
        sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
        interactive_timeout=28800
        wait_timeout=28800
        [client]
        socket=/home/console/mysql/tmp/mysql.sock
        [mysqld]
        init_connect='SET collation_connection = utf8_unicode_ci'
        init_connect='SET NAMES utf8'
        character-set-server=utf8
        collation-server=utf8_unicode_ci
        skip-name-resolve
        skip-character-set-client-handshake
        [client]
        default-character-set=utf8
        [mysqld_safe]
        log-error=/var/log/mysqld.log
        pid-file=/var/run/mysqld/mysqld.pid
 *  1.  修改4服务上的MySql配置
    
        [root@node3 mysql]# vim /etc/my.cnf
        [mysqld]
        basedir=/home/console/mysql
        datadir=/home/console/mysql/data
        port=3306   #端口
        server-id=2 #服务id
        
        #mysql双主配置
        #binlog-do-db=test  #需要记录到二进制日志的数据库
        binlog-ignore_db=mysql  #忽略记录二进制日志的数据库
        #replicate-do-db=test   #指定复制的数据库
        replicate_ignore_db=mysql   #不复制的数据库
        binlog-ignore-db=information_schema #不复制的数据库
        binlog-ignore-db=performance_schema #不复制的数据库
        log-slave-updates=1 #该从库是否写入二进制日志，如果需要成为多主则可启用。只读可以不需要
        auto_increment_offset=1 #该服务器自增列的初始值
        auto_increment_increment=2  #该服务器自增列增量
        relay_log=mysql-relay-bin   #从库的中继日志，主库日志写到中继日志，中继日志再重做到从库
        
        log-bin=mysql-bin
        max_connections=151
        max_connect_errors=30
        default-storage-engine=InnoDB
        transaction_isolation=REPEATABLE-READ
        socket=/home/console/mysql/tmp/mysql.sock
        sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
        interactive_timeout=28800
        wait_timeout=28800
        [client]
        socket=/home/console/mysql/tmp/mysql.sock
        [mysqld]
        init_connect='SET collation_connection = utf8_unicode_ci'
        init_connect='SET NAMES utf8'
        character-set-server=utf8
        collation-server=utf8_unicode_ci
        skip-name-resolve
        skip-character-set-client-handshake
        [client]
        default-character-set=utf8
        [mysqld_safe]
        log-error=/var/log/mysqld.log
        pid-file=/var/run/mysqld/mysqld.pid
        
        需注意，3与4MySql服务的server-id不能相同。
        如果为多主的话注意设置 auto-increment-offset 和 auto-increment-increment
        如上面为双主的设置：
        服务器 4 自增列显示为：1,3,5,7,……（offset=1，increment=2）
        服务器 3 自增列显示为：2,4,6,8,……（offset=2，increment=2）
 *  1.  重启3，4两台服务器上的MySql
    
        [root@node3 mysql]# service mysqld restart

--------------------

##### 搭建MySql双主 #####

*  1.  连接数据库查看日志记录位置相关信息
    
        [root@node3 mysql]# cd bin/
        [root@node3 bin]# ./mysql -uroot -p
        Enter password: 
        Welcome to the MySQL monitor.  Commands end with ; or \g.
        Your MySQL connection id is 10
        Server version: 5.7.21-log MySQL Community Server (GPL)
        
        Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
        
        Oracle is a registered trademark of Oracle Corporation and/or its
        affiliates. Other names may be trademarks of their respective
        owners.
        
        Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
        
        mysql> flush logs;
        mysql> show master status;
        +------------------+----------+--------------+---------------------------------------------+-------------------+
        | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                            | Executed_Gtid_Set |
        +------------------+----------+--------------+---------------------------------------------+-------------------+
        | mysql-bin.000005 |     154 |              | mysql,information_schema,performance_schema |                   |
        +------------------+----------+--------------+---------------------------------------------+-------------------+
        1 row in set (0.00 sec)
        
        File :当前正在记录的二进制日志文件
        Position ：记录偏移量，日志 mysql-bin.000008 所记录到的位置。
        Binlog_Do_DB ：要记录日志的数据库
        Binlog_Ignore_DB ：不记录日志的数据库
        Executed_Gtid_Set ：已执行的事务ID
 *  1.  查看二进制日志情况
    
        mysql> show variables like '%log_bin%';
        +---------------------------------+------------------------------------------+
        | Variable_name                   | Value                                    |
        +---------------------------------+------------------------------------------+
        | log_bin                         | ON                                       |
        | log_bin_basename                | /home/console/mysql/data/mysql-bin       |
        | log_bin_index                   | /home/console/mysql/data/mysql-bin.index |
        | log_bin_trust_function_creators | OFF                                      |
        | log_bin_use_v1_row_events       | OFF                                      |
        | sql_log_bin                     | ON                                       |
        +---------------------------------+------------------------------------------+
        6 rows in set (0.00 sec)
 *  1.  连接上3数据库，创建slave 4同步账号
    
        mysql>  grant replication slave on *.* to 'repl_user'@'172.16.33.4' identified by 'slave@4';
 *  1.  获取3数据日志记录位置相关信息
    
        mysql> show master status;
        +------------------+----------+--------------+---------------------------------------------+-------------------+
        | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                            | Executed_Gtid_Set |
        +------------------+----------+--------------+---------------------------------------------+-------------------+
        | mysql-bin.000005 |     154 |              | mysql,information_schema,performance_schema |                   |
        +------------------+----------+--------------+---------------------------------------------+-------------------+
        1 row in set (0.00 sec)
 *  1.  连接上4数据库，使用刚在3数据库创建的同步账号连接到master主库（3）
    
        mysql> 
         CHANGE MASTER TO 
               MASTER_HOST='172.16.33.3', 
               MASTER_USER='repl_user', 
               MASTER_PASSWORD='slave@4',
        	   MASTER_PORT=3406,
               MASTER_LOG_FILE='mysql-bin.000005', 
               MASTER_LOG_POS=154;
               
        说明：
            MASTER_HOST 主节点ip
            MASTER_USER 用户名
            MASTER_PASSWORD 密码
            MASTER_LOG_FILE 3服务正在记录的二进制日志文件（实时获取）
            MASTER_LOG_POS  3服务记录二进制日志的偏移量（实时获取）
 *  1.  连接上4数据库，创建slave（3）同步账号
    
        mysql> grant replication slave on *.* to 'repl_user'@'172.16.33.3' identified by 'slave@3';
 *  1.  获取4数据日志记录位置相关信息
    
        mysql> show master status;
        +------------------+----------+--------------+---------------------------------------------+-------------------+
        | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                            | Executed_Gtid_Set |
        +------------------+----------+--------------+---------------------------------------------+-------------------+
        | mysql-bin.000004 |     154 |              | mysql,information_schema,performance_schema |                   |
        +------------------+----------+--------------+---------------------------------------------+-------------------+
        1 row in set (0.00 sec)
 *  1.  连接上3数据库，使用刚在4数据库创建的同步账号连接到master主库（4）
    
        mysql> 
         CHANGE MASTER TO 
               MASTER_HOST='172.16.33.4', 
               MASTER_USER='repl_user', 
               MASTER_PASSWORD='slave@3', 
        	   MASTER_PORT=3406,
               MASTER_LOG_FILE='mysql-bin.000004', 
               MASTER_LOG_POS=154;
               
        说明：
            MASTER_HOST 主节点ip
            MASTER_USER 用户名
            MASTER_PASSWORD 密码
            MASTER_LOG_FILE 4服务正在记录的二进制日志文件（实时获取）
            MASTER_LOG_POS  4服务记录二进制日志的偏移量（实时获取）
 *  1.  重启3，144MySql数据库
    
        [root@node3 mysql]# service mysqld restart

开启防火墙端口

#查看端口是否占用
        netstat  -anp  |grep 3406
        # 查看防火墙状态
        systemctl status firewalld 
        firewall-cmd --state
        # 开启3406端口
        firewall-cmd --zone=public --add-port=3306/tcp --permanent
        # 重启防火墙
        firewall-cmd --reload
        # 查看端口
        firewall-cmd --list-ports
        
        firewall-cmd --zone=public --add-port=5119/tcp --permanent
         5119/tcp