Implementing MySQL Cascade replication

The so-called cascade replication is the master server, only to one slave server synchronization data, and then slave server in the back end of all slave server synchronization data, reduce the master server write pressure, and replication data network IO.

One, configure master server 1, modify the master configuration file

    vim /etc/my.cnf      在[mysql]配置块下添加如下两行配置       [mysql]       log_bin          #开启二进制日志功能       server_id=1      

2. Restart the MySQL service for the configuration to take effect

    systemctl restart mairadb

3. Create a user account with copy permissions

GRANT REPLICATION SLAVE  ON *.* TO ‘repluser‘@‘HOST‘ IDENTIFIED BY ‘replpass‘;     命令解析：        ‘repluser‘@‘HOST‘ :设置用户名即主机ip或网段，网段用%表示 例如10.0.0.%        IDENTIFIED BY:设置密码        *.* ：表示所有数据库，所有表        GRANT REPLCATION SLAVE:就是允许该用户复制数据    该命令作用就是授权repluser能拷贝数据库的所有内容

Second, relay slave server configuration 1, modify master configuration file

    vim /etc/my.cnf   在[mysql]配置块中添加如下两行配置        [mysqld]           log_bin        server_id=2      #为当前节点设置一个全局惟一的ID号                 read_only=ON #限制从服务器为只读."注意：此限制对拥有SUPER权限的用户均无效"        log_slave_updates #该项的作用是把master服务器的二进制日志计入到本机，然后再把二进制日志复制给后端的其他slave服务器

2. Restart the MySQL service for the configuration to take effect

    systemctl  restart mariadb

3. Connect to the primary server using a user account with replication privileges and start the replication thread

     CHANGE MASTER TO      MASTER_HOST=‘host‘,        #指定master主机IP     MASTER_USER=‘repluser‘,    #指定master被授权的用户名     MASTER_PASSWORD=‘replpass‘,#指定被授权的用户密码 MASTER_LOG_FILE=‘mysql-bin.xxxxx‘, #指定从master服务器的那个二进制日志开始复制     MASTER_LOG_POS=#;          #二进制日志位置，可以在master服务器上执行该命令查看，show master logs;     启动复制线程IO_THREAD和SQL_THREAD     

4. View relay Slave Server Status

    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 Ma                    ster_host:192.168.68.7 Master_user:repluser master_port:3306                   Connect_retry:60 master_log_file:mariadb-bin.000001 read_master_log_pos:557 relay_log_file:mariadb-relay-bin.000002 relay_log_pos:843 relay_master_log_file:m ariadb-bin.000001 Slave_io_running:yes "Focus on if no means the thread is not up" slave_sql_running:yes "focus on if            No indicates that the thread is not up "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_l og_pos:557 relay_log_space:1139 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 "This item means that the synchronization time of 0 means even if the synchronization" 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

Third, backend slave configuration 1, modify configuration file

    vim /etc/my.cnf        在[mysql]配置块中添加如下两行配置            [mysqld]               server_id=3     #为当前节点设置一个全局惟一的ID号            read_only=ON #限制从服务器为只读."注意：此限制对拥有SUPER权限的用户均无效"

2. Restart the MySQL service for the configuration to take effect

    systemctl restart mariadb

3. Connect to the primary server using a user account with replication privileges and start the replication thread

CHANGE MASTER TO      MASTER_HOST=‘中继host‘,        #指定中继slave主机IP     MASTER_USER=‘repluser‘,    #指定master被授权的用户名     MASTER_PASSWORD=‘replpass‘,#指定被授权的用户密码 MASTER_LOG_FILE=‘mysql-bin.xxxxx‘, #指定从中继slave服务器的那个二进制日志开始复制     MASTER_LOG_POS=#;          #二进制日志位置，可以在slave服务器上执行该命令查看，show master logs;     启动复制线程IO_THREAD和SQL_THREAD     

4. View Slave Server Status

    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 Ma                    ster_host:192.168.68.17 Master_user:repluser master_port:3306                   Connect_retry:60 master_log_file:mariadb-bin.000001 read_master_log_pos:557 relay_log_file:mariadb-relay-bin.000002 relay_log_pos:843 Relay_master_log_file: mariadb-bin.000001 Slave_io_running:yes "Focus on if no means the thread is not up" slave_sql_running:yes "focus on            No indicates that the thread is not up "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:557 relay_log_space:1139 Until_condition:none Until_log_fil                e:until_log_pos:0 Master_ssl_allowed:no Master_ssl_ca_file: Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SS l_key:seconds_behind_master:0 "This item means that synchronization time 0 means even if the Master_ssl_verify_server_cert:no Las       t_io_errno:0 last_io_error:last_sql_errno:0 Last_sql_error: Replicate_ignore_server_ids:master_server_id:1

5, finally create a database test on the master server to see if the synchronous Cascade replication feature

• Reduce the pressure on master server, network IO pressure
• But there is a problem with inconsistent data.

Summarize

• Trunk slave need to open binary log, must add log_slave_updates configuration item
• Note the Read_only=on function, which restricts the server from being read-only. " Note: This restriction is not valid for users who have super privileges "

Implementing MySQL Cascade replication