mariadb实现主从同步

发布时间:2024年01月05日

准备两台服务器
Mariadb-Master:192.168.44.150
Mariadb-Backup:192.168.44.148

安装mariadb:

https://blog.csdn.net/qq_50247813/article/details/135402502?spm=1001.2014.3001.5502

组从复制原理如下
在这里插入图片描述

  1. 修改主数据库配置如下

    vi /etc/my.cnf.d/server.cnf
    
    [mysqld]
    character-set-server=utf8
    collation-server=utf8_general_ci
    server_id = 150
    log-bin= mysql-bin
    lower_case_table_names=1
    binlog-format=ROW
    log-slave-updates=True
    sync-master-info=1
    slave-parallel-threads=3
    binlog-checksum=CRC32 
    master-verify-checksum=1
    slave-sql-verify-checksum=1
    
  • character-set-server=utf8 设置数据库默认字符集
  • collation-server=utf8_general_ci 设置校验规则
  • server_id = 150 主从id必须唯一,推荐使用ip位
  • log-bin= mysql-bin 二进制日志,后面指定存放位置。如果只是指定名字,默认存放在/var/lib/mysql下
  • lower_case_table_names=1 库表不区分大小写
  • binlog-format=ROW 二进制日志文件格式
  • log-slave-updates=True slave更新是否记入日志
  • sync-master-info=1 值为1确保信息不会丢失
  • slave-parallel-threads=3 同时启动多少个复制线程,最多与要复制的数据库数量相等即可
  • binlog-checksum=CRC32 效验码
  • master-verify-checksum=1 启动主服务器效验
  • slave-sql-verify-checksum=1 启动从服务器效验
  1. 修改从数据库配置

     vi /etc/my.cnf.d/server.cnf
    
    	[mysqld]
    character-set-server=utf8
    collation-server=utf8_general_ci
    server_id=148
    relay_log = relay-bin
    lower_case_table_names=1
    
  • server_id=148 主从id必须唯一
  • relay_log = relay-bin 中继日志, 后面指定存放位置。如果只是指定名字,默认存放在/var/lib/mysql下
  1. 重启主从数据库,配置生效

     systemctl restart mariadb
    
  2. master节点配置

    注:如果使用普通用户需要授权binlog权限

     grant replication slave, replication client on *.* to 'user'@'%' identified by 'passwd';
    

    还有注意将slave加入到master的远程访问列表中

     GRANT ALL PRIVILEGES ON *.* to  `root`@`192.168.44.*` identified by '123456';
     flush privileges;
    

     mariadb -uroot -p123456
    
    MariaDB [(none)]> show master status;
    +------------------+----------+--------------+------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    +------------------+----------+--------------+------------------+
    | mysql-bin.000002 |     1218 |              |                  |
    +------------------+----------+--------------+------------------+
    1 row in set (0.000 sec)
    
    MariaDB [(none)]> select binlog_gtid_pos('mysql-bin.000002', 1218 );
    +--------------------------------------------+
    | binlog_gtid_pos('mysql-bin.000002', 1218 ) |
    +--------------------------------------------+
    | 0-150-6                                    |
    +--------------------------------------------+
    1 row in set (0.000 sec)
    
  3. 从节点配置

    MariaDB [(none)]> set global gtid_slave_pos='0-150-6';
    	Query OK, 0 rows affected (0.004 sec)
    	
    	MariaDB [(none)]>  change master to master_host='192.168.44.150',master_user='root',master_password='123456',master_use_gtid=slave_pos;
    	Query OK, 0 rows affected (0.008 sec)
    	
    	MariaDB [(none)]> start slave;
    	Query OK, 0 rows affected (0.005 sec)
    	
    	MariaDB [(none)]>
    
  4. 验证salve状态

    		MariaDB [(none)]> show slave status\G
    *************************** 1. row ***************************
                    Slave_IO_State: Waiting for master to send event
                       Master_Host: 192.168.44.150
                       Master_User: root
                       Master_Port: 3306
                     Connect_Retry: 60
                   Master_Log_File: mysql-bin.000002
               Read_Master_Log_Pos: 1722
                    Relay_Log_File: relay-bin.000002
                     Relay_Log_Pos: 1174
             Relay_Master_Log_File: mysql-bin.000002
                  Slave_IO_Running: Yes
                 Slave_SQL_Running: Yes
              Replicate_Rewrite_DB: 
                   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: 1722
                   Relay_Log_Space: 1477
                   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: 150
                    Master_SSL_Crl: 
                Master_SSL_Crlpath: 
                        Using_Gtid: Slave_Pos
                       Gtid_IO_Pos: 0-150-9
           Replicate_Do_Domain_Ids: 
       Replicate_Ignore_Domain_Ids: 
                     Parallel_Mode: optimistic
                         SQL_Delay: 0
               SQL_Remaining_Delay: NULL
           Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
                  Slave_DDL_Groups: 2
    Slave_Non_Transactional_Groups: 1
        Slave_Transactional_Groups: 0
    1 row in set (0.000 sec)
    
文章来源:https://blog.csdn.net/qq_50247813/article/details/135409572
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。