数据库集群的主从复制模型完整实现

主从配置过程：
参看：
    https://mariadb.com/kb/en/library/setting-up-replication/
    https://dev.MySQL.com/doc/refman/5.5/en/replication-configuration.html

成都创新互联公司是一家集网站建设,平潭企业网站建设,平潭品牌网站建设,网站定制,平潭网站建设报价,网络营销,网络优化,平潭网站推广为一体的创新建站企业，帮助传统企业提升企业形象加强企业竞争力。可充分满足这一群体相比中小企业更为丰富、高端、多元的互联网需求。同时我们时刻保持专业、时尚、前沿，时刻以成就客户成长自我，坚持不断学习、思考、沉淀、净化自己，让我们为更多的企业打造出实用型网站。

主服务器192.168.27.7配置：

[root@master ~]$vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
innodb_file_per_table
log_bin
server-id=1


[root@master ~]$systemctl start mariadb.service 

MariaDB [(none)]> show binary logs;
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| mariadb-bin.000001 |       245 |
+--------------------+-----------+
1 row in set (0.00 sec)

[root@master ~]$mysql < hellodb_InnoDB.sql


MariaDB [(none)]> show binary logs;
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| mariadb-bin.000001 |      7655 |
+--------------------+-----------+
1 row in set (0.00 sec)

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

从服务器192.168.27.17服务配置：

从服务器上的配置：
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
innodb_file_per_table
server_id=2

注意：默认的server_id是0；
MariaDB [(none)]> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 0     |
+---------------+-------+
1 row in set (0.01 sec)


查看帮助：
MariaDB [(none)]> help change master to;
CHANGE MASTER TO
  MASTER_HOST='master2.mycompany.com',
  MASTER_USER='replication',
  MASTER_PASSWORD='bigs3cret',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='master2-bin.001',
  MASTER_LOG_POS=4,
  MASTER_CONNECT_RETRY=10;


MariaDB [(none)]> CHANGE MASTER TO
    ->   MASTER_HOST='192.168.27.7',
    ->   MASTER_USER='repluser',
    ->   MASTER_PASSWORD='centos',
    ->   MASTER_PORT=3306,
    ->   MASTER_LOG_FILE='mariadb-bin.000001',
    ->   MASTER_LOG_POS=245,
    ->   MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected (0.02 sec)




MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.27.7
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mariadb-bin.000001
          Read_Master_Log_Pos: 245
               Relay_Log_File: mariadb-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mariadb-bin.000001
             Slave_IO_Running: No     线程未启动；
            Slave_SQL_Running: No     线程未启动；
              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: 245
              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: NULL
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: 0
1 row in set (0.00 sec)

中继日志已经创建；
[root@centos7x ~]$ll /var/lib/mysql/
total 28724
-rw-rw---- 1 mysql mysql    16384 Feb 25 06:37 aria_log.00000001
-rw-rw---- 1 mysql mysql       52 Feb 25 06:37 aria_log_control
-rw-rw---- 1 mysql mysql 18874368 Feb 25 06:37 ibdata1
-rw-rw---- 1 mysql mysql  5242880 Feb 25 06:37 ib_logfile0
-rw-rw---- 1 mysql mysql  5242880 Feb 25 05:09 ib_logfile1
-rw-rw---- 1 mysql mysql      264 Feb 25 05:15 mariadb-bin.000001
-rw-rw---- 1 mysql mysql       21 Feb 25 05:10 mariadb-bin.index
-rw-rw---- 1 mysql mysql      245 Feb 25 06:38 mariadb-relay-bin.000001
-rw-rw---- 1 mysql mysql       27 Feb 25 06:38 mariadb-relay-bin.index
-rw-rw---- 1 mysql mysql       84 Feb 25 06:38 master.info
drwx------ 2 mysql mysql     4096 Feb 25 05:09 mysql
srwxrwxrwx 1 mysql mysql        0 Feb 25 06:37 mysql.sock
drwx------ 2 mysql mysql     4096 Feb 25 05:09 performance_schema
-rw-rw---- 1 mysql mysql       52 Feb 25 06:38 relay-log.info
drwx------ 2 mysql mysql        6 Feb 25 05:09 test

启动从服务器上的复制线程：

MariaDB [(none)]> start slave;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    4
Current database: *** NONE ***

Query OK, 0 rows affected, 1 warning (0.00 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mysql              |
| performance_schema |
| test               |
+--------------------+


MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.27.7
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mariadb-bin.000002
          Read_Master_Log_Pos: 245     读取的日志的位置；
               Relay_Log_File: mariadb-relay-bin.000005
                Relay_Log_Pos: 531
        Relay_Master_Log_File: mariadb-bin.000002
             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: 1113
              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)]> show master logs;
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| mariadb-bin.000001 |      8217 |
| mariadb-bin.000002 |       245 |
+--------------------+-----------+
2 rows in set (0.00 sec)



主服务器的写：
MariaDB [(none)]> create database wangdb;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> show master logs;
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| mariadb-bin.000001 |      8217 |
| mariadb-bin.000002 |       332 |
+--------------------+-----------+
2 rows in set (0.00 sec)

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.27.7
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mariadb-bin.000002
          Read_Master_Log_Pos: 332
               Relay_Log_File: mariadb-relay-bin.000003
                Relay_Log_Pos: 618
        Relay_Master_Log_File: mariadb-bin.000002
             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: 332
              Relay_Log_Space: 9153
              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)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mysql              |
| performance_schema |
| test               |
| wangdb             |
+--------------------+
6 rows in set (0.00 sec)



停止从服务器；并在主服务器上更新操作；
MariaDB [(none)]> create database wangdb2;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database wangdb3;
Query OK, 1 row affected (0.00 sec)

从服务器；
[root@centos7x ~]$systemctl stop mariadb.service 
[root@centos7x ~]$
[root@centos7x ~]$
[root@centos7x ~]$systemctl start mariadb.service 

unknown [(none)]> show databases;
No connection. Trying to reconnect...
Connection id:    4
Current database: *** NONE ***

+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mysql              |
| performance_schema |
| test               |
| wangdb             |
| wangdb2            |
| wangdb3            |
+--------------------+
8 rows in set (0.00 sec)



从服务器上的relay-log.info记录的是
[root@centos7x ~]$ls /var/lib/mysql/
aria_log.00000001  ibdata1      mariadb-relay-bin.000004  master.info  performance_schema  wangdb
aria_log_control   ib_logfile0  mariadb-relay-bin.000005  mysql        relay-log.info      wangdb2
hellodb            ib_logfile1  mariadb-relay-bin.index   mysql.sock   test                wangdb3
[root@centos7x ~]$
[root@centos7x ~]$cat /var/lib/mysql/relay-log.info 
./mariadb-relay-bin.000005
709
mariadb-bin.000002
510
8

[root@centos7x ~]$cat /var/lib/mysql/master.info 
18
mariadb-bin.000002
510
192.168.27.7
repluser
centos
3306
60
0



0
1800.000

0

主服务器；
MariaDB [(none)]> show master logs;
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| mariadb-bin.000001 |      8217 |
| mariadb-bin.000002 |       510 |
+--------------------+-----------+
2 rows in set (0.00 sec)