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: Entire database replication

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

   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

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

Scenario 2: Partial replication

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

      

      • 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.
   3. Press Esc, type :wq!, and press Enter to save the file and exit.

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. 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.

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