Mysql主从复制及主主复制的实现

主从复制

Node	Master	Slave
Linux	Centos8	Centos8
Mysql	10.3.28-MariaDB	10.3.28-MariaDB
IP	192.168.0.114	192.168.0.117

主节点配置文件

[mysqld]
server-id=1    #为当前节点设置一个全局惟的ID号（不能和主节点一
log-bin    #启用二进制
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mariadb/mariadb.log
pid-file=/run/mariadb/mariadb.pid

从节点配置文件:

[mysqld]
server-id=2
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mariadb/mariadb.log
pid-file=/run/mariadb/mariadb.pid

查看主节点当前的二进制文件名及位置：

MariaDB [(none)]> show master logs;
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| mariadb-bin.000001 |       536 |
+--------------------+-----------+

在主机点上创建有复制权限的用户账号 ：
 

grant replication slave on *.* to repluser@'192.168.0.%' identified by 'test                                                                       db';

在从节点上使用有复制权限的用户账号连接至主服务器，并启动复制线程 

change master to MASTER_HOST='192.168.0.114',MASTER_USER='repluser',MASTER_P                                                                       ASSWORD='testdb',MASTER_PORT=3306,MASTER_LOG_FILE='mariadb-bin.000001',MASTER_LOG_POS=536;

查看从节点IO线程及SQL线程

MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 192.168.0.114
                   Master_User: repluser
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mariadb-bin.000001
           Read_Master_Log_Pos: 536
                Relay_Log_File: mariadb-relay-bin.000002
                 Relay_Log_Pos: 557
         Relay_Master_Log_File: mariadb-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: 536
               Relay_Log_Space: 868
               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: 1
                Master_SSL_Crl:
            Master_SSL_Crlpath:
                    Using_Gtid: No
                   Gtid_IO_Pos:
       Replicate_Do_Domain_Ids:
   Replicate_Ignore_Domain_Ids:
                 Parallel_Mode: conservative
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread                                                                        to update it
              Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 0

验证主从复制是否成功

在主节点上创建库查看从节点是否同步

MariaDB [(none)]> create database test001;
Query OK, 1 row affected (0.001 sec)

从节点查看

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test001            |
+--------------------+
4 rows in set (0.000 sec)

主主复制

主节点配置文件

[mysqld]
server-id=1    #为当前节点设置一个全局惟的ID号（不能和主节点一
log-bin    #启用二进制
auto_increment_offset=1	#开始点
auto_increment_increment=2	#增长幅度
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mariadb/mariadb.log
pid-file=/run/mariadb/mariadb.pid

从节点配置文件:

 

[mysqld]
server-id=2
log-bin
auto_increment_offset=2 #开始点
auto_increment_increment=2      #增长幅度
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mariadb/mariadb.log
pid-file=/run/mariadb/mariadb.pid

配置连接master的相关信息

change master to MASTER_HOST='192.168.0.117',MASTER_USER='repluser',MASTER_PASSWORD='testdb',MASTER_PORT=3306,MASTER_LOG_FILE=' mariadb-bin.000001',MASTER_LOG_POS=330;
start slave;

查看主从状态

MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 192.168.0.114
                   Master_User: repluser
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mariadb-bin.000002
           Read_Master_Log_Pos: 1198
                Relay_Log_File: mariadb-relay-bin.000002
                 Relay_Log_Pos: 557
         Relay_Master_Log_File: mariadb-bin.000002
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes

 测试验证：

在114上执行

create database t1;
create table t1(id int auto_increment primary key,name char(10));
insert t1 (name) values('user1');

在117上执行

insert t1 (name) values('user2');

 结果验证

MariaDB [db1]> select * from t1;
+----+-------+
| id | name  |
+----+-------+
|  1 | user1 |
|  3 | user2 |
+----+-------+