MySQL 8.0 : Replication2024/09/03 |
Configure MySQL Replication settings. This configuration is general Primary-Replica settings. |
|
[1] |
On all Hosts, Install and Start MySQL Server, refer to here. |
[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 others. |
[3] | On Primary Host, change settings and also create users for replication and clone. |
root@dlp:~ #
vi /usr/local/etc/mysql/my.cnf # line 13: change (listen all) bind-address = 0.0.0.0 # line 23: uncomment and change (specify unique ID) server-id = 101 # add in [mysqld] section : enable clone plugin plugin-load=mysql_clone.so
root@dlp:~ #
root@dlp:~ # service mysql-server restart mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.35 Source distribution Copyright (c) 2000, 2023, Oracle and/or its affiliates. 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) root@localhost [(none)]> create user 'repl_user'@'%' identified by 'password'; Query OK, 0 rows affected (0.10 sec) root@localhost [(none)]> grant replication slave on *.* to repl_user@'%'; Query OK, 0 rows affected (0.05 sec) # create clone user (set any password for [password] section) root@localhost [(none)]> create user 'clone_user'@'%' identified by 'password'; Query OK, 0 rows affected (0.07 sec) root@localhost [(none)]> grant backup_admin on *.* to 'clone_user'@'%'; Query OK, 0 rows affected (0.06 sec) root@localhost [(none)]> exit Bye |
[4] | On Replica Host, change settings for replication. |
root@node01:~ #
vi /usr/local/etc/mysql/my.cnf # line 31: change (listen all) bind-address = 0.0.0.0 # line 23: uncomment and change (specify unique ID) server-id = 102 # add in [mysqld] section # read only read_only=1 # define own hostname report-host=node01.srv.world # define relay logs relay-log=node01-relay-bin relay-log-index=node01-relay-bin # enable clone plugin plugin-load=mysql_clone.so
root@node01:~ #
root@node01:~ # service mysql-server restart mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.35 Source distribution Copyright (c) 2000, 2023, Oracle and/or its affiliates. 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) root@localhost [(none)]> create user 'clone_user'@'%' identified by 'password'; Query OK, 0 rows affected (0.07 sec) root@localhost [(none)]> grant clone_admin on *.* to 'clone_user'@'%'; Query OK, 0 rows affected (0.06 sec) root@localhost [(none)]> 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.35 Source distribution Copyright (c) 2000, 2023, Oracle and/or its affiliates. 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)] root@localhost [(none)]> set global clone_valid_donor_list = '10.0.0.30:3306'; Query OK, 0 rows affected (0.00 sec) # start Clone root@localhost [(none)]> clone instance from clone_user@10.0.0.30:3306 identified by 'password'; Query OK, 0 rows affected (0.49 sec) # confirm clone status # Ok if status is [Completed] root@localhost [(none)]> 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.000004 | 961 | +------+-----------+----------------+----------------+------------------+-----------------+ 1 row in set (0.00 sec) # replication settings # source_host ⇒ Primary Host IP address # source_user ⇒ replication user # source_password ⇒ replication user's password # source_ssl ⇒ enable SSL connection # source_log_file ⇒ specify [BINLOG_FILE] value it is displayed above # source_log_pos ⇒ specify [BINLOG_POSITION] value it is displayed above root@localhost [(none)]> change replication source to source_host='10.0.0.30', source_user='repl_user', source_password='password', source_ssl=1, source_log_file='mysql-bin.000004', source_log_pos=961; Query OK, 0 rows affected, 2 warnings (0.03 sec) # start replication root@localhost [(none)]> start replica; Query OK, 0 rows affected (0.03 sec) # show status root@localhost [(none)]> show replica status\G *************************** 1. row *************************** Replica_IO_State: Waiting for source to send event Source_Host: 10.0.0.30 Source_User: repl_user Source_Port: 3306 Connect_Retry: 60 Source_Log_File: mysql-bin.000004 Read_Source_Log_Pos: 961 Relay_Log_File: node01-relay-bin.000002 Relay_Log_Pos: 326 Relay_Source_Log_File: mysql-bin.000004 Replica_IO_Running: Yes Replica_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_Source_Log_Pos: 961 Relay_Log_Space: 537 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Source_SSL_Allowed: Yes Source_SSL_CA_File: Source_SSL_CA_Path: Source_SSL_Cert: Source_SSL_Cipher: Source_SSL_Key: Seconds_Behind_Source: 0 Source_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Source_Server_Id: 101 Source_UUID: adcd864f-69a5-11ef-aa7e-5254006392e4 Source_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates Source_Retry_Count: 86400 Source_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Source_SSL_Crl: Source_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: adcd864f-69a5-11ef-aa7e-5254006392e4:1-177 Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Source_TLS_Version: Source_public_key_path: Get_Source_public_key: 0 Network_Namespace: 1 row in set (0.00 sec) |
Sponsored Link |
|