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

#实现主从复制配置，官网参考
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)