Ubuntu 20.04
Sponsored Link

MySQL 8.0 : Replication
2020/09/02
 
Configure MySQL Replication settings. This configuration is general Primary-Replica settings.
[1]
[2]
On Replication connection, it uses self signed certificates that mysqld generated by default like here on this example, however, warning messages are logged for it, so if you care those warnings, use valid certificate like from Let's encrypt and othets.
[3] On Primary Host, change settings and also create users for replication and clone.
root@dlp:~#
vi /etc/mysql/mysql.conf.d/mysqld.cnf
# line 31: change (listen all)
bind-address            = 0.0.0.0

# line 72: uncomment and change (specify uniq ID)
server-id               = 101

# line 73: uncomment
log_bin                 = /var/log/mysql/mysql-bin.log

# add to the end: enable clone plugin
plugin-load=mysql_clone.so

root@dlp:~#
systemctl restart mysql

root@dlp:~#
mysql

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.21-0ubuntu0.20.04.4 (Ubuntu)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

# create replication user (set any password for [password] section)
mysql> create user 'repl_user'@'%' identified by 'password';
Query OK, 0 rows affected (0.10 sec)

mysql> grant replication slave on *.* to repl_user@'%'; 
Query OK, 0 rows affected (0.05 sec)

# create clone user (set any password for [password] section)
mysql> create user 'clone_user'@'%' identified by 'password'; 
Query OK, 0 rows affected (0.07 sec)

mysql> grant backup_admin on *.* to 'clone_user'@'%'; 
Query OK, 0 rows affected (0.06 sec)

mysql> flush privileges; 
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
[4] On Replica Host, change settings for replication.
root@node01:~#
vi /etc/mysql/mysql.conf.d/mysqld.cnf
# line 31: change (listen all)
bind-address            = 0.0.0.0

# line 72: uncomment and change (specify uniq ID)
server-id               = 102

# line 73: uncomment
log_bin                 = /var/log/mysql/mysql-bin.log

# add to the end
# read only
read_only=1
# define own hostname
report-host=node01.srv.world
# define relay logs
relay-log=/var/log/mysql/node01-relay-bin
relay-log-index=/var/log/mysql/node01-relay-bin
# enable clone plugin
plugin-load=mysql_clone.so

root@node01:~#
systemctl restart mysql

root@node01:~#
mysql

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.21-0ubuntu0.20.04.4 (Ubuntu)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

# create clone user (set any password for [password] section)
mysql> create user 'clone_user'@'%' identified by 'password'; 
Query OK, 0 rows affected (0.07 sec)

mysql> grant clone_admin on *.* to 'clone_user'@'%'; 
Query OK, 0 rows affected (0.06 sec)

mysql> flush privileges; 
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
[5] On Replica Host, Run Clone job to copy data on Primary Host and Start replication.
After starting replication, make sure replication works normally to create test database or insert test data and so on.
root@node01:~#
mysql

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.21-0ubuntu0.20.04.4 (Ubuntu)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

# [set global clone_valid_donor_list = (Primary Host IP address:port)]
mysql> set global clone_valid_donor_list = '10.0.0.30:3306'; 
Query OK, 0 rows affected (0.00 sec)

# start Clone
mysql> clone instance from clone_user@10.0.0.30:3306 identified by 'password'; 
Query OK, 0 rows affected (3.01 sec)

# confirm clone status
# Ok if status is [Completed]
mysql> select ID,STATE,SOURCE,DESTINATION,BINLOG_FILE,BINLOG_POSITION from performance_schema.clone_status; 
+------+-----------+----------------+----------------+------------------+-----------------+
| ID   | STATE     | SOURCE         | DESTINATION    | BINLOG_FILE      | BINLOG_POSITION |
+------+-----------+----------------+----------------+------------------+-----------------+
|    1 | Completed | 10.0.0.30:3306 | LOCAL INSTANCE | mysql-bin.000001 |            1405 |
+------+-----------+----------------+----------------+------------------+-----------------+
1 row in set (0.00 sec)

# repliation settings
# master_host      ⇒ Primary Host IP address
# master_ssl       ⇒ enable SSL connection
# master_log_file  ⇒ specify [BINLOG_FILE] value it is displayed above
# master_log_pos   ⇒ specify [BINLOG_POSITION] value it is displayed above
mysql> change master to
master_host='10.0.0.30',
master_ssl=1,
master_log_file='mysql-bin.000001',
master_log_pos=1405;
Query OK, 0 rows affected (0.35 sec)

# start replication
# specify replication user and password for [user/password] value
mysql> start slave user='repl_user' password='password'; 
Query OK, 0 rows affected, 1 warning (0.13 sec)

# show status
mysql> show slave status\G 
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.0.30
                  Master_User: repl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 1405
               Relay_Log_File: node01-relay-bin.000002
                Relay_Log_Pos: 324
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              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: 1405
              Relay_Log_Space: 534
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: Yes
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
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: 101
                  Master_UUID: c9329622-ecad-11ea-9043-525400ac8f06
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
       Master_public_key_path:
        Get_master_public_key: 0
            Network_Namespace:
1 row in set (0.00 sec)
Matched Content