Configuring Secondary Databases

1. Log in to a secondary database.

   1	/usr/local/mysql/bin/mysql -uroot -p123456 -S /data/mysql/run/mysql.sock

2. Create a connection account (user name: replicate; password: 123456), grant the replication slave permission to the account, and refresh permissions.

   create user 'replicate'@'%' identified by '123456';
   grant replication slave on *.* to 'replicate'@'%';
   flush privileges;

3. Query the user.

   select user,host from mysql.user;

4. Exit the secondary database.

   exit

• Scenario 1: full back up
  1. Modify the configuration file of secondary database 1.
     1. Open the /etc/my.cnf file.

        1	vim /etc/my.cnf

     2. Press i to enter the insert mode and add the following content to the [mysqld] section. For details about the parameters server-id, log-bin, and relay_log, see Table 1.

        [mysqld]
        server-id=2
        log-bin=/data/mysql/data/mysql-bin
        relay_log=/data/mysql/relaylog/relay-bin

     3. Press Esc, type :wq!, and press Enter to save the file and exit.
  2. Modify the configuration file of secondary database 2.
     1. Open the /etc/my.cnf file.

        vim /etc/my.cnf

     2. Press i to enter the insert mode and add the following content to the [mysqld] section. For details about the parameters server-id, log-bin, and relay_log, see Table 1.

        [mysqld]
        server-id=3
        log-bin=/data/mysql/data/mysql-bin
        relay_log=/data/mysql/relaylog/relay-bin

     3. Press Esc, type :wq!, and press Enter to save the file and exit.
• Scenario 2: partial backup

  For example, synchronize the test database, and do not synchronize the MySQL, information_schema, or performance_schema database.

  1. Modify the configuration file of secondary database 1.
     1. Open the /etc/my.cnf file.

        vim /etc/my.cnf

     2. Press i to enter the insert mode and add the following content to the [mysqld] section. For details about the parameters server-id, log-bin, and relay_log, see Table 1.

        [mysqld]
        server-id=2
        log-bin=/data/mysql/data/mysql-bin
        relay_log=/data/mysql/relaylog/relay-bin
        replicate-do-db=test
        replicate-ignore-db=mysql,information_schema,performance_schema

     3. Press Esc, type :wq!, and press Enter to save the file and exit.
  2. Modify the configuration file of secondary database 2.
     1. Open the /etc/my.cnf file.

        1	vim /etc/my.cnf

     2. Press i to enter the insert mode and add the following content to the [mysqld] section. For details about the parameters server-id, log-bin, and relay_log, see Table 1.

        [mysqld]
        server-id=3
        log-bin=/data/mysql/data/mysql-bin
        relay_log=/data/mysql/relaylog/relay-bin
        replicate-do-db=test
        replicate-ignore-db=mysql,information_schema,performance_schema

     3. Press Esc, type :wq!, and press Enter to save the file and exit.

• If the primary/secondary switchover is not considered, add the log-bin parameter to the configuration file of the primary database and the relay_log parameter to the configuration file of secondary databases.
• If the primary/secondary switchover is considered, add the log-bin and relay_log parameters to the configuration files of the primary database and secondary databases.

1. Restart the MySQL service.

   1	service mysql restart

   Or

   1 2	/usr/local/mysql/bin/mysqladmin -uroot -p123456 shutdown -S /data/mysql/run/mysql.sock /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &

2. Check the database process and port number.

   1 2	ps -ef | grep mysql netstat -anpt | grep mysql

1. Log in to a secondary database.

   1	/usr/local/mysql/bin/mysql -uroot -p123456 -S /data/mysql/run/mysql.sock

2. Install the semisynchronous replication plugin rpl.

   install plugin rpl_semi_sync_slave soname 'semisync_slave.so';

   

   View the plugin rpl.

   show plugins;

   

3. Start semisynchronous replication.

   set global rpl_semi_sync_slave_enabled=1;

   

4. Check whether semisynchronous replication is started successfully.

   show variables like '%rpl_semi_sync_slave_enabled%';

   

   If the value is ON, the startup is successful.

   

   After semisynchronous replication is disabled (the primary database is disabled before secondary databases), asynchronous replication is implemented.

   1. Run the following commands on the primary database to disable semisynchronous replication:

      set global rpl_semi_sync_master_enabled=0;
      show variables like '%rpl_semi_sync_master_enabled%';

   2. Run the following commands on secondary databases to disable semisynchronous replication:

      set global rpl_semi_sync_slave_enabled=0;
      show variables like '%rpl_semi_sync_slave_enabled%';

1. Log in to a secondary database.

   1	/usr/local/mysql/bin/mysql -uroot -p123456 -S /data/mysql/run/mysql.sock

2. Configure the replication mode.

   Two replication modes are available: traditional mode and GTID mode. The GTID mode is recommended for MySQL 5.7 or later versions.

   • Run the following commands on both the primary database and secondary databases to configure the traditional mode:

     set global enforce_gtid_consistency='off';
     set global gtid_mode='on_permissive';
     set global gtid_mode='off_permissive';
     set global gtid_mode='off';
     show variables like '%gtid_mode%';

   • Run the following commands on both the primary database and secondary databases to configure the GTID mode:

     set global enforce_gtid_consistency='on';
     set global gtid_mode='off_permissive';
     set global gtid_mode='on_permissive';
     set global gtid_mode='on';
     show variables like '%gtid_mode%';

• Run the following command if the traditional mode is configured. You need to specify the binlog file and position.

  change master to master_host='192.168.220.58',master_port=3306,master_user='replicate',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=155;

• Run the following command if the GTID mode is configured:

  change master to master_host='192.168.220.58',master_port=3306,master_user='replicate',master_password='123456',master_auto_position=1;

When deploying primary-secondary replication for the first time, you need to run the change master command to specify the primary database and replication mode. Then, you do not need to run this command if the primary database is not changed. After the primary-secondary replication relationship is established, primary-secondary replication is automatically enabled upon restart of the primary and secondary databases (in any sequence). Log in to the primary and secondary databases to check whether they are in normal state.

start slave;

The command for stopping secondary databases is as follows:

stop slave;

1. Run the following command:

   show slave status\G;

   If the values of the following two parameters are Yes, secondary databases are started successfully.

   Slave_IO_Running: Yes

   Slave_SQL_Running: Yes

   

   If the error message "The slave I/O thread stops because master and slave have equal MySQL server UUIDs..." is displayed, rectify the fault by referring to "The slave IO thread stops" Displayed During the MySQL Primary-Secondary Replication Verification and check the status of the secondary database.

2. Exit secondary databases.

   exit

/usr/local/mysql/bin/mysql -uroot -p123456 -S /data/mysql/run/mysql.sock
show slave hosts;

If the Host column is empty, rectify the fault by referring to "no slaves were found" Is Displayed During Data Consistency Verification Between MySQL Primary and Secondary Databases.