MariaDB主从复制（一）

实验环境：
两台linux主机：
主服务器：192.168.239.132
从服务器：192.168.239.130
主节点
一、编辑主节点的配置文件

[root@centos7 ~]# vim /etc/my.cnf

[mysqld]
server_id=1   ##为主节点设置一个全局唯一的ID号
datadir=/var/lib/mysql  
innodb_file_per_table=ON
log_bin  ##启用二进制日志

二、一般情况下主服务器都是已经运行了一段时间的，因此要先对主数据库服务器进行完全备份，然后到从数据库上还原。

[root@centos7 ~]# mysqldump -A -F --single-transaction --master-data=1 > /data/mysql/backup/all_`date +%F`.sql

把备份好的文件复制到从节点

[root@centos7 ~]# scp /data/mysql/backup/all_2020-03-08.sql 192.168.239.130:/data/backup/
The authenticity of host '192.168.239.130 (192.168.239.130)' can't be established.
ECDSA key fingerprint is SHA256:/v6+3jVZB+skD0cGwS3hfYn4zQNHoHveHVzJaQ/j3yg.
ECDSA key fingerprint is MD5:e1:be:5c:b2:2f:27:af:4d:e3:99:b4:61:e0:50:b2:a1.
Are you sure you want to continue connecting (yes/no)? yes     
Warning: Permanently added '192.168.239.130' (ECDSA) to the list of known hosts.
root@192.168.239.130's password: 
all_2020-03-08.sql                                                                                                                                       100%  509KB   7.3MB/s   00:00    

三、主节点上建立主从复制的账号

MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.239.%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

从节点
一、编辑配置文件

[root@centos7 ~]# vim /etc/my.cnf

[mysqld]
[mysqld]
server_id=2
read_only=ON  ##限制从服务器为只读在从服务器上设置read_only=ON,此限制对拥有SUPER权限的用户均无效
log_bin  ##从节点开启开启二进制日志，可用于级联复制。

二、把复制过来的all_2020-03-08.sql文件进行编辑。
添加以下信息。可以通过help change MASTER TO获取。

[root@centos7 backup]# vim all_2020-03-08.sql 

......

CHANGE MASTER TO MASTER_HOST='192.168.239.132',
  MASTER_USER='repluser',
  MASTER_PASSWORD='123456',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mariadb-bin.000002', 
  MASTER_LOG_POS=245,
  MASTER_CONNECT_RETRY=10;
.....

三、导入从数据库中。

[root@centos7 backup]# mysql</data/backup/all_2020-03-08.sql

四、进入数据库开启slave进程，初次复制时需要手动开启，后面即使数据库关闭了，只要重启数据库，就会自动启动。

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

查看从服务器状态，说明现在已经与主服务器同步，没有出现延迟。

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.239.132
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mariadb-bin.000003
          Read_Master_Log_Pos: 245
               Relay_Log_File: mariadb-relay-bin.000003
                Relay_Log_Pos: 531
        Relay_Master_Log_File: mariadb-bin.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: 245
              Relay_Log_Space: 1269
              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)

验证
往主服务器插入数据，看从服务器是否同步。
1、主服务器192.168.239.132

MariaDB [hellodb_innodb]> insert teachers (Name,Age,Gender) values ('Feng Qingyang',99,'M');
Query OK, 1 row affected (0.00 sec)

从服务器上查询数据，可以从服务器已经复制数据过来。

MariaDB [(none)]> select * from hellodb_innodb.teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | Feng Qingyang |  99 | M      |
+-----+---------------+-----+--------+
5 rows in set (0.00 sec)

2、主服务器创建一个函数，生成海量的数据

[root@centos7 scripts]# vim test_log.sql

create table testlog (id int auto_increment primary key,name char(10),age int default 20);

delimiter $$

create procedure pro_testlog()
begin
declare i int;
set i=1;
while i < 100000
do insert into testlog(name,age) values (concat('wang',i),i);
set i = i+1;
end while;
end$$

delimiter ;
~                                                                                                                                                                                          
~     

[root@centos7 scripts]# mysql hellodb_innodb < test_log.sql 

MariaDB [hellodb_innodb]> show tables;
+--------------------------+
| Tables_in_hellodb_innodb |
+--------------------------+
| classes                  |
| coc                      |
| courses                  |
| scores                   |
| students                 |
| teachers                 |
| testlog                  |
| toc                      |
+--------------------------+
8 rows in set (0.00 sec)

MariaDB [hellodb_innodb]> call pro_testlog ;

查看从服务器的状态。可以看到Read_Master_Log_Pos: 10138942和 Exec_Master_Log_Pos: 10138637不一致，说明复制海量数据时还是有点延迟的。

MariaDB [hellodb_innodb]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.239.132
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mariadb-bin.000003
          Read_Master_Log_Pos: 10138942
               Relay_Log_File: mariadb-relay-bin.000003
                Relay_Log_Pos: 10138923
        Relay_Master_Log_File: mariadb-bin.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: 10138637
              Relay_Log_Space: 10139966
              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 [hellodb_innodb]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.239.132
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mariadb-bin.000003
          Read_Master_Log_Pos: 11829252
               Relay_Log_File: mariadb-relay-bin.000003
                Relay_Log_Pos: 11829233
        Relay_Master_Log_File: mariadb-bin.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: 11828947
              Relay_Log_Space: 11830276
              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)

复制了99999条记录

MariaDB [hellodb_innodb]> select count(*) from testlog;
+----------+
| count(*) |
+----------+
|    99999 |
+----------+
1 row in set (0.05 sec)