mariadb实现主从同步

准备两台服务器
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 启动从服务器效验

2. 修改从数据库配置

    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下

3. 重启主从数据库，配置生效

    systemctl restart mariadb
   

4. 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)
   

5. 从节点配置

   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)]>
   

6. 验证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)