数据库集群的级联复制完整实现

实验前的注意：

如果要启用级联复制,需要在从服务器启用以下配置；[mysqld]log_binlog_slave_updateslog_slave_updates的作用：    因为默认从主服务器过来的二进制日志保存在从服务器的中继日志；并应用到本地数据库；    但是中间这个从服务器是即便是开启二进制日志的记录功能，他也是不记录到本地的二进制日志，    那么也就不能将对数据库的操作再传输到从的从服务器上去。当log_slave_updates启用后，    从服务器也就能将对主服务器的二进制日志在本地执行后，也记录到自己的二进制日志中；删除数据库就可以干净做实验；[root@master ~]$systemctl stop mariadb[root@master ~]$rm -fr /var/lib/mysql/*[root@master ~]$systemctl start mariadb

主、从、二级从服务器的配置：

[root@master ~]$cat /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@slave ~]$cat /etc/my.cnf[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=2read_onlylog_binlog_slave_updates[root@slave2 ~]$cat  /etc/my.cnf[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sock# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0server_id=3

主服务器和从服务器实现主从复制，配置和授权：

主服务器：MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.27.%' identified by 'centos';Query OK, 0 rows affected (0.00 sec)MariaDB [(none)]> show master logs;+--------------------+-----------+| Log_name           | File_size |+--------------------+-----------+| mariadb-bin.000001 |       264 || mariadb-bin.000002 |       401 |+--------------------+-----------+2 rows in set (0.00 sec)中间的从服务器：CHANGE MASTER TOMASTER_HOST='192.168.27.7',MASTER_USER='repluser',MASTER_PASSWORD='centos',MASTER_LOG_FILE='mariadb-bin.000001',MASTER_LOG_POS=245;MariaDB [(none)]> CHANGE MASTER TO    -> MASTER_HOST='192.168.27.7',    -> MASTER_USER='repluser',    -> MASTER_PASSWORD='centos',    -> MASTER_LOG_FILE='mariadb-bin.000001',    -> MASTER_LOG_POS=245;Query OK, 0 rows affected (0.02 sec)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.27.7                  Master_User: repluser                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mariadb-bin.000002          Read_Master_Log_Pos: 401               Relay_Log_File: mariadb-relay-bin.000003                Relay_Log_Pos: 687        Relay_Master_Log_File: mariadb-bin.000002             Slave_IO_Running: Yes            Slave_SQL_Running: Yes         Exec_Master_Log_Pos: 401              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: 0MariaDB [(none)]> select user from mysql.user;+----------+| user     |+----------+| root     || repluser || root     ||          || root     ||          || root     |+----------+7 rows in set (0.00 sec)MariaDB [(none)]> show master logs;+--------------------+-----------+| Log_name           | File_size |+--------------------+-----------+| mariadb-bin.000001 |       401 |+--------------------+-----------+1 row in set (0.00 sec)

二级从服务器上的配置：

在从的从服务器上执行：[mysqld]innodb_file_per_tableread-onlyserver-id=3CHANGE MASTER TOMASTER_HOST='192.168.27.17',MASTER_USER='repluser',MASTER_PASSWORD='centos',MASTER_LOG_FILE='mariadb-bin.000001',MASTER_LOG_POS=401;