#实现主从复制配置，官网参考
https://dev.mysql.com/doc/refman/8.0/en/replication-configuration.html
https://dev.mysql.com/doc/refman/5.7/en/replication-configuration.html
https://dev.mysql.com/doc/refman/5.5/en/replication-configuration.html
https://mariadb.com/kb/en/library/setting-up-replication/
#一、两台centos8安装mariadb10.3.28 实现主从复制
#主节点配置：
[root@Centos8 ~]##hostnamectl set-hostname master
[root@master ~]#yum install -y mariadb-server
[root@master ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
log_bin
server-id=11
#设置开机自启并启动服务
[root@master ~]#systemctl --now enable mariadb
Created symlink /etc/systemd/system/mysql.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/mysqld.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/multi-user.target.wants/mariadb.service → /usr/lib/systemd/system/mariadb.service.
#登录数据库
[root@master ~]#mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.28-MariaDB-log MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show master status;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000002 | 343 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.000 sec)
#创建复制账号
MariaDB [(none)]> grant replication slave on *.* to 'repluser'@'10.0.0.%' identified by 'replpass';
Query OK, 0 rows affected (0.000 sec)
#刷新权限
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.000 sec)
#查看二进制文件名称及位置，后面从节点会用到
MariaDB [(none)]> show master status;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000003 | 343 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.000 sec)
#退出数据库
MariaDB [(none)]> exit
Bye
#从节点配置：
[root@Centos8 ~]#hostnamectl set-hostname slave1
[root@slave1 ~]#yum install -y mariadb-server
[root@slave1 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server_id=22
log-bin
read_only=ON
relay_log=relay-log
relay_log_index=relay-log.index
[root@slave1 ~]#systemctl --now enable mariadb
Created symlink /etc/systemd/system/mysql.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/mysqld.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/multi-user.target.wants/mariadb.service → /usr/lib/systemd/system/mariadb.service.
#使用有复制权限的用户账号连接至主服务器，并启动复制线程
[root@slave1 ~]#mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.28-MariaDB-log MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> change master to master_host='10.0.0.150',master_user='repluser',master_password='replpass',master_log_file='master-bin.000003',master_log_pos=343;
Query OK, 0 rows affected (0.002 sec)
#开始从节点复制
MariaDB [(none)]> start slave ;
Query OK, 0 rows affected (0.001 sec)
#查看从节点复制状态
MariaDB [(none)]> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.150
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000003
Read_Master_Log_Pos: 343
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 556
Relay_Master_Log_File: master-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes #出现这两个yes表示连接成功，配置成功
#说明，从节点配置错误，清除信息后检查从节点配置重新配置change master to
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.002 sec)
MariaDB [(none)]> reset slave all;
Query OK, 0 rows affected (0.000 sec)
#验证主从复制
#主节点创建数据库
第一次查看数据库
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.000 sec)
主节点创建test111数据库
MariaDB [(none)]> create database test111;
Query OK, 1 row affected (0.000 sec)
#从节点查看是否同步
第一次查看数据库
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.001 sec)
在主节点创建test111数据库后，在从节点查看数据库，发现已经同步过来
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test111 |
+--------------------+
4 rows in set (0.000 sec)
#二、两台centos8安装mariadb10.3.28 实现主主复制
主主复制：两个节点，都可以更新数据，并且互为主从
容易产生的问题：数据不一致；因此慎用
考虑要点：自动增长id
配置一个节点使用奇数id
auto_increment_offset=1 #开始点
auto_increment_increment=2 #增长幅度
另一个节点使用偶数id
auto_increment_offset=2
auto_increment_increment=2
主主复制的配置步骤简述：
(1) 各节点使用一个惟一server_id
(2) 都启动binary log和relay log
(3) 创建拥有复制权限的用户账号
(4) 定义自动增长id字段的数值范围各为奇偶
(5) 均把对方指定为主节点，并启动复制线程
主主复制的具体实现步骤：
#第一台mster节点
[root@master1 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
log_bin
server-id=11
auto_increment_offset=1
auto_increment_increment=2
[root@master1 ~]#systemctl restart mariadb
[root@master1 ~]#mysql
MariaDB [(none)]> show master status;
+--------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| master1-bin.000001 | 330 | | |
+--------------------+----------+--------------+------------------+
1 row in set (0.000 sec)
MariaDB [(none)]> show master logs;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| master1-bin.000001 | 330 |
+--------------------+-----------+
1 row in set (0.000 sec)
#说明，如果是先查看在创建复制账号，则下面的创建账号信息不会同步到从节点。如果先创建的复制账号，再查看的二进制文件位置，则会把账号创建也复制过去。
MariaDB [(none)]> grant replication slave on *.* to 'repluser'@'*' identified by 'replpass';
MariaDB [(none)]>flush privileges;
#第二台master节点
[root@master2 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server_id=22
log-bin
auto_increment_offset=2
auto_increment_increment=2
[root@master2 ~]#systemctl restart mariadb
[root@master2 ~]#mysql
MariaDB [(none)]> change master to master_host='10.0.0.150',master_user='repluser',master_password='replpass',master_log_file='master1-bin.000001',master_log_pos=330;
Query OK, 0 rows affected (0.011 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> show master logs;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| master2-bin.000001 | 913 |
+--------------------+-----------+
1 row in set (0.000 sec)
#因上面没有先创建复制用户再查看二进制文件位置，所以创建账号这步没有同步过来，因此这台也要创建复制账号。
MariaDB [(none)]>grant replication slave on *.* to 'repluser'@'*' identified by 'replpass';
MariaDB [(none)]>flush privileges;
#回到第一台master节点
因为第一台的复制账号信息已经同步到第二台机器，因此有复制账号了，直接配置change master to
MariaDB [(none)]> change master to master_host='10.0.0.160',master_user='repluser',master_password='replpass',master_log_file='master2-bin.000001',master_log_pos=913;
Query OK, 0 rows affected (0.014 sec)
#启动复制线程
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.001 sec)
#验证
#第一台
MariaDB [(none)]> create database d111;
Query OK, 1 row affected (0.000 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| d111 |
| d222 |
| information_schema |
| mysql |
| performance_schema |
| t1 |
| test111 |
+--------------------+
7 rows in set (0.000 sec)
#第二台
MariaDB [(none)]> create database d222;
Query OK, 1 row affected (0.000 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| d111 |
| d222 |
| information_schema |
| mysql |
| performance_schema |
| t1 |
| test111 |
+--------------------+
7 rows in set (0.000 sec)