Mariadb实现主从复制（一主一从（M-S））

一、原理：
概念
1. 在主库上把数据更改（DDL DML DCL）记录到二进制日志（Binary Log）中。
2. 备库I/O线程将主库上的日志复制到自己的中继日志（Relay Log）中。
3. 备库SQL线程读取中继日志中的事件，将其重放到备份数据库之上。
二、图示：

三、准备工作
准备两台主机 ，关闭两台主机的防火墙或者为mysql服务设置规则，并做好host域名解析。
192.168.231.31 master
192.168.231.32 slave
对master操作：

# yum install mariadb  mariadb-server -y
# systemctl  start mariadb
# systemctl  enable mariadb
# mysqladmin -uroot password 'Wiger@5111'
# mysql -p'Wiger@5111'
MariaDB [(none)]> create database jike1701;
MariaDB [(none)]> create table jike1701.t1(id int,name char(20));
MariaDB [(none)]> insert into  jike1701 values(1,'chen');
MariaDB [(none)]> insert into  jike1701 values(2,'zhang');
MariaDB [(none)]> select * from jike1701.t1;
    -> ;
+------+-------+
| id   | name  |
+------+-------+
|    1 | chen  |
|    2 | zhang |
+------+-------+

对slave操作

# yum install mariadb  mariadb-server -y
# systemctl  start mariadb
# systemctl  enable mariadb
# mysqladmin -uroot password 'Wiger@5111'

四、“干货开始”
对master操作：
1.添加箭头所指的两行

# vim /etc/my.cnf

2.重启

# systemctl restart mariadb

3.登录+授权+日志文件

# mysql -p'Wiger@5111'
MariaDB [(none)]> grant replication slave,replication client on *.* to 'wiger'@''192.168.231.%' identified by 'Wiger@5222';
MariaDB [(none)]>exit
# mysqldump -p'Wiger@5111' --all-databases --single-transaction --master-data=1 --flush-logs > `date +%F`-mysql-all.sql

4.发送日志文件到slave主机

# scp 2020-07-01-mysql-all.sql slave:/root

5.查看日志文件,出现箭头所指样式即可

# cat 2020-07-01-mysql-all.sql slave:/root

对slave操作：
1.测试master授权的用户能否链接上主服务器；要确保可以连接如下代码所示：

[root@slave ~]# mysql -hmaster -uwiger -p'Wiger@5222'
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 5.5.65-MariaDB 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 grants;
+--------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for wiger@192.168.231.%                                                                                                                   |
+--------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'wiger'@'192.168.231.%' IDENTIFIED BY PASSWORD '*6A3DD188ABAC680C94D1004C93B1171B2965BDD7' |
+--------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> exit
Bye

2.修改配置文件,添加如箭头所在行

# vim /etc/my.cnf

3.重启MySQL服务

# systemctl restart mariadb

4.复制操作

# mysql -p'Wiger@5111'
MariaDB [(none)]> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> source /root/2020-07-01-mysql-all.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
...
MariaDB [test]> change master to master_host='master', master_user='wiger',master_password='Wiger@5222';
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> start slave;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: master
                  Master_User: wiger
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File:
          Read_Master_Log_Pos: 4
               Relay_Log_File: mariadb-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File:
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes                     #要出现Yes
              Replicate_Do_DB: Yes                        #要出现Yes
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       ...

5.查看数据库

MariaDB [test]> select * from jike1701.t1 ;
+------+-------+
| id   | name  |
+------+-------+
|    1 | chen  |
|    2 | zhang |
+------+-------+

完~

值得注意：
master数据库上的数据（增、删、改）可以同步到slave上；
slave数据库上的数据（增、删、改）不可以同步到master服务器上；
从而衍生出读写分离的机制；
待下回分解。。。