linux进阶之mariadb（三）主从复制

一、主机要求

1、最少两台互通的服务器
2、设置防火墙规则
3、setenforce 0

二、环境要求及原理图

1、两台服务器均安装mariadb-server
2、master IP：192.168.192.105
slave IP：192.168.192.106

 

三、步骤

主节点配置：

[root@test1 mysql]# vim /etc/my.cnf.d/server.cnf 
[mysqld]
server-id = 1 # 设置唯一ID
log-bin = maste-log # 设置日志文件名称
skip_name_resolve = ON # 跳过域名解析
sync_binlog = 1 # 同步二进制日志文件
innodb-file-per-table = ON # 创建的表均为单一表
[root@test1 ~]# mysql -uroot -pwang
MariaDB [(none)]> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| maste-log.000003 |      563 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
# 记录file文件名字和position位置编号，后面配置从服务器时需要使用。
# 授权同步的账号
MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO  'wang'@'192.168.192.106' IDENTIFIED BY 'wang';
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.02 sec)

从节点配置：

[root@test1 mysql]# vim /etc/my.cnf.d/server.cnf 
[mysqld]
server-id = 2 # 设置唯一ID不能与其他ID冲突
skip_name_resolve = ON
innodb-file-per-table = ON 
relay-log = relay-log
read_only = ON
[root@test2 ~]# mysql -uroot -pwang
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.192.105',MASTER_USER='wang',MASTER_PASSWORD='wang',MASTER_LOG_FILE='maste-log.000003',MASTER_LOG_POS=563;
MariaDB [(none)]> START SLAVE;
MariaDB [(none)]> SHOW SLAVE STATUS;
MariaDB [(none)]>  SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.192.105
                  Master_User: wang
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: maste-log.000003
          Read_Master_Log_Pos: 812
               Relay_Log_File: relay-log.000002
                Relay_Log_Pos: 861
        Relay_Master_Log_File: maste-log.000003
             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: 812
              Relay_Log_Space: 1149
              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
1 row in set (0.00 sec)

测试：
在主服务器上操作

MariaDB [(none)]>CREATE DATABASE WANG;
然后再插入数据，这里就不再插入了。

再从服务器上操作

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| WANG               |
| mysql              |
| performance_schema |
| test               |
+--------------------+

OK 同步成功。
下面介绍互为主从的两台mariadb服务器，这里我们还是使用上述的两台服务器；
不过是需要修改配置文件。

[root@test1 mysql]# vim /etc/my.cnf.d/server.cnf

[mysqld]
auto_increment_offset=1 # 关键设置自增长ID的初始值为1
auto_increment_increment=2 # 设置自增长ID的每次增大的数值位2
skip_name_resolve = ON
innodb-file-per-table = ON 
relay-log = relay-log
read_only = ON
log-bin = maste-log
sync_binlog = 1 

[root@test2 mysql]# vim /etc/my.cnf.d/server.cnf

[mysqld]
auto_increment_offset=2 # 关键设置自增长ID的初始值为2
auto_increment_increment=2 # 设置自增长ID的每次增大的数值位2
skip_name_resolve = ON
innodb-file-per-table = ON 
relay-log = relay-log
read_only = ON
log-bin = maste-log
sync_binlog = 1 

按照原来的配置上的话我们已经在test1节点上配置了授权用户，在test2节点上认证了属组。这里我们需要将操作反过来在test1节点上认证属组，在test2节点上配置授权用户。
在test2上执行MariaDB

MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO  'wang'@'192.168.192.106' IDENTIFIED BY 'wang';
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.02 sec)

在test1上执行

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.192.105',MASTER_USER='wang',MASTER_PASSWORD='wang',MASTER_LOG_FILE='maste-log.000003',MASTER_LOG_POS=563;
MariaDB [(none)]> START SLAVE;
MariaDB [(none)]> SHOW SLAVE STATUS;
MariaDB [(none)]>  SHOW SLAVE STATUS\G;

这里当执行完最后的命令的时候会出现和上面从节点执行的相同命令的界面就OK了。不过这里还是建议实测一下。test1和test2互相创建库文件查看是否在另外一个节点上出现对应的库文件就OK了。