3.6. Replicating MySQL

Procédure

1. Include the following options in the /etc/my.cnf.d/mysql-server.cnf file under the [mysqld] section:

   • bind-address=source_ip_adress

     This option is required for connections made from replicas to the source.

   • server-id=id

     The id must be unique.

   • log_bin=path_to_source_server_log

     This option defines a path to the binary log file of the MySQL source server. For example: log_bin=/var/log/mysql/mysql-bin.log.

   • gtid_mode=ON

     This option enables global transaction identifiers (GTIDs) on the server.

   • enforce-gtid-consistency=ON

     The server enforces GTID consistency by allowing execution of only statements that can be safely logged using a GTID.

   • Optional: binlog_do_db=db_name

     Use this option if you want to replicate only selected databases. To replicate more than one selected database, specify each of the databases separately:

     binlog_do_db=db_name1
     binlog_do_db=db_name2
     binlog_do_db=db_name3

   • Optional: binlog_ignore_db=db_name

     Use this option to exclude a specific database from replication.

2. Restart the mysqld service:

   # systemctl restart mysqld.service

Procédure

1. Include the following options in the /etc/my.cnf.d/mysql-server.cnf file under the [mysqld] section:

   • server-id=id

     The id must be unique.

   • relay-log=path_to_replica_server_log

     The relay log is a set of log files created by the MySQL replica server during replication.

   • log_bin=path_to_replica_sever_log

     This option defines a path to the binary log file of the MySQL replica server. For example: log_bin=/var/log/mysql/mysql-bin.log.

     This option is not required in a replica but strongly recommended.

   • gtid_mode=ON

     This option enables global transaction identifiers (GTIDs) on the server.

   • enforce-gtid-consistency=ON

     The server enforces GTID consistency by allowing execution of only statements that can be safely logged using a GTID.

   • log-replica-updates=ON

     This option ensures that updates received from the source server are logged in the replica’s binary log.

   • skip-replica-start=ON

     This option ensures that the replica server does not start the replication threads when the replica server starts.

   • Optional: binlog_do_db=db_name

     Use this option if you want to replicate only certain databases. To replicate more than one database, specify each of the databases separately:

     binlog_do_db=db_name1
     binlog_do_db=db_name2
     binlog_do_db=db_name3

   • Optional: binlog_ignore_db=db_name

     Use this option to exclude a specific database from replication.

2. Restart the mysqld service:

   # systemctl restart mysqld.service

Procédure

1. Create a replication user:

   mysql> CREATE USER 'replication_user'@'replica_server_ip' IDENTIFIED WITH mysql_native_password BY 'password';

2. Grant the user replication permissions:

   mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'replica_server_ip';

3. Reload the grant tables in the MySQL database:

   mysql> FLUSH PRIVILEGES;

4. Set the source server to read-only state:

   mysql> SET @@GLOBAL.read_only = ON;

Procédure

1. Set the replica server to read-only state:

   mysql> SET @@GLOBAL.read_only = ON;

2. Configure the replication source:

   mysql> CHANGE REPLICATION SOURCE TO
       -> SOURCE_HOST='source_ip_address',
       -> SOURCE_USER='replication_user',
       -> SOURCE_PASSWORD='password',
       -> SOURCE_AUTO_POSITION=1;

3. Start the replica thread in the MySQL replica server:

   mysql> START REPLICA;

4. Unset the read-only state on both the source and replica servers:

   mysql> SET @@GLOBAL.read_only = OFF;

5. Optional: Inspect the status of the replica server for debugging purposes:

   mysql> SHOW REPLICA STATUS\G;

   Note

   If the replica server fails to start or connect, you can skip a certain number of events following the binary log file position displayed in the output of the SHOW MASTER STATUS command. For example, skip the first event from the defined position:

   mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

   and try to start the replica server again.

6. Optional: Stop the replica thread in the replica server:

   mysql> STOP REPLICA;

1. Create an example database on the source server:

   mysql> CREATE DATABASE test_db_name;

2. Verify that the test_db_name database replicates on the replica server.

3. Display status information about the binary log files of the MySQL server by executing the following command on either of the source or replica servers:

   mysql> SHOW MASTER STATUS;

   The Executed_Gtid_Set column, which shows a set of GTIDs for transactions executed on the source, must not be empty.

   Note

   The same set of GTIDs is displayed in the Executed_Gtid_Set row when you use the SHOW SLAVE STATUS on the replica server.