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

主从配置过程：参看：    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/mysqlsocket=/var/lib/mysql/mysql.sock# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0innodb_file_per_tablelog_binserver-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.sqlMariaDB [(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/mysqlsocket=/var/lib/mysql/mysql.sock# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0# Settings user and group are ignored when systemd is used.innodb_file_per_tableserver_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: NULLMaster_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: 01 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.infodrwx------ 2 mysql mysql     4096 Feb 25 05:09 mysqlsrwxrwxrwx 1 mysql mysql        0 Feb 25 06:37 mysql.sockdrwx------ 2 mysql mysql     4096 Feb 25 05:09 performance_schema-rw-rw---- 1 mysql mysql       52 Feb 25 06:38 relay-log.infodrwx------ 2 mysql mysql        6 Feb 25 05:09 test

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

MariaDB [(none)]> start slave;ERROR 2006 (HY000): MySQL server has gone awayNo connection. Trying to reconnect...Connection id:    4Current 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: 11 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: 0Master_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: 11 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:    4Current 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  wangdbaria_log_control   ib_logfile0  mariadb-relay-bin.000005  mysql        relay-log.info      wangdb2hellodb            ib_logfile1  mariadb-relay-bin.index   mysql.sock   test                wangdb3[root@centos7x ~]$[root@centos7x ~]$cat /var/lib/mysql/relay-log.info ./mariadb-relay-bin.000005709mariadb-bin.0000025108[root@centos7x ~]$cat /var/lib/mysql/master.info 18mariadb-bin.000002510192.168.27.7replusercentos330660001800.0000主服务器；MariaDB [(none)]> show master logs;+--------------------+-----------+| Log_name           | File_size |+--------------------+-----------+| mariadb-bin.000001 |      8217 || mariadb-bin.000002 |       510 |+--------------------+-----------+2 rows in set (0.00 sec)