Shulou(Shulou.com)05/31 Report--

This article is about how to build a remote slave library through Clone in MySQL 8.0. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

The environment is as follows

Mater: 192.168.3.133 port:3307 doner donor

Slave: 192.168.3.134 port:3307 recipient recipient

Step 1: initialize and install the MySQL of the two machines respectively

Enter the MySQL software directory to initialize the installation and change the password: (MySQL8.0.19 download and decompression steps are omitted)

[root@mgr2 bin] # cd / zcloud/db/abcMgr/abcMgr02/mysql/bin [root@mgr2 bin] #. / mysqld-- initialize-- user=mysql [root@mgr2 bin] #. / mysqld_safe-- user=mysql & [1] 19556 [root@mgr2 bin] # 2020-03-12T01:32:26.503048Z mysqld_safe Logging to'/ rock/mysqldata/error.log'. 2020-03-12T01:32:26.536292Z mysqld_safe Starting mysqld daemon with databases from / rock/mysqldata [root@mgr2 bin] # [root@mgr2 bin] # mysql-uroot-p-P3307 Enter password: Welcome to the MySQL monitor. Commands end with; or\ g. Your MySQL connection id is 8 Server version: 8.0.19 Copyright ©2000, 2019, 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. Root@localhost: (none) 09:33:06 > alter user user () identified by 'root1234'; Query OK, 0 rows affected (0.00 sec) root@localhost: (none) 09:33:26 > flush privileges; Query OK, 0 rows affected (0.00 sec) root@localhost: (none) 09:33:31 > exit Bye

Step 2: related operations of doner node 192.168.3.133

-create a user

Root@localhost: (none) 10:07:05 > create user' donor_user'@'192.168.3.134' identified by 'password'; Query OK, 0 rows affected (0.00 sec) root@localhost: (none) 10:07:07 > grant backup_admin on. To 'donor_user'@'192.168.3.134'; Query OK, 0 rows affected (0.01 sec)

-install the clone plug-in

Root@localhost: (none) 10:23:16 > install plugin clone soname 'mysql_clone.so'; Query OK, 0 rows affected (0.01sec)

Step 3: related operations of recipient node 192.168.3.134

-create users (or operate directly with root instead of creating users)

Mysql > create user' recipient_user'@'192.168.3.134' identified by 'password'

-install the clone plug-in

Mysql > grant clone_admin on. To 'recipient_user'@'192.168.3.134'

-set parameter clone_valid_donor_list

Root@localhost: (none) 03:28:40 > set global clone_valid_donor_list='192.168.3.133:3307'; Query OK, 0 rows affected (0.00 sec)

-replace it with recipient_user'@'192.168.3.134' user login and execute clone statement (in fact, you can log in directly with local root user here)

[root@mgr3 bin] # mysql-urecipient_user-ppassword-P3307-h292.168.3.134 recipient_user@192.168.3.134: (none) 03:39:46 > clone instance from 'donor_user'@'192.168.3.133':3307 identified by' password'; Query OK, 0 rows affected (2.34 sec) recipient_user@192.168.3.134: (none) 03:39:57 > Restarting mysqld... 2020-03-12T07:40:01.285267Z mysqld_safe Number of processes running now: 0 2020-03-12T07:40:01.290169Z mysqld_safe mysqld restarted

At this point, the cloning of remote data is complete.

Monitor the progress and result status of the clone by querying two tables:

-View clone progress and status

Root@localhost: (none) 03:34:49 > SELECT * FROM performance_schema.clone_progress + -±- + | ID | STAGE | STATE | BEGIN_TIME | END_TIME | THREADS | ESTIMATE | DATA | NETWORK | DATA_SPEED | NETWORK_SPEED | ±- ±- ±- -- ±--±--±- ±- ±- ±- ±- ±- + | 1 | DROP DATA | Completed | 2020-03-12 1515 Ranger 29RO 15.385694 | | 2020-03-12 15 PAGE COPY 2915. 634609 | 1 | 0 | 0 | 0 | 0 | 1 | FILE COPY | Completed | 2020-03-12 15 PAGE COPY 2917.452961 | 1 | 465800520 | 465800520 | 465833356 | 0 | 0 | 1 | 0 | Completed | 2020-03-12 1515 Switzerland 2917.554224 | 1 | 0 | 0 | 99 | 0 | 0 | 1 | | | REDO COPY | Completed | 2020-03-12 15JV 29JV 17.554413 | 2020-03-12 15JV 29JV 17.654430 | 1 | 2560 | 2560 | 3031 | 0 | 0 | 1 | FILE SYNC | Completed | 2020-03-12 15JV 29V 17.654596 | 2020-03-12 1529V JV 17.730172 | 1 | 0 | 0 | 0 | 0 | 1 | RESTART | Completed | 2020-03-12 15V 29R 17.730172 | 2020-03-12 15V 29V 17.730172 | .0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | RECOVERY | Completed | 2020-03-12 15virtual 22,160372 | 2020-03-12 15virtual 22,478889 | 0 | 0 | 0 | 0 | 0 | ±- ±- ±- ±-- -±- ±- ±- ±- ±- ±- + 7 rows in set (0.00 sec) root@localhost: (none) 03:34:52 > SELECT * FROM Performance_schema.clone_status\ G * * 1. Row * * ID: 1 PID: 0 STATE: Completed BEGIN_TIME: 2020-03-12 15 PID 2915.385 END_TIME: 2020-03-12 15 15 PID 22.479 SOURCE: 192.168.3.133 3307 DESTINATION: LOCAL INSTANCE ERROR_NO: 0 ERROR_MESSAGE: BINLOG_FILE: mysql-bin.000002 BINLOG_POSITION: 421 GTID_EXECUTED: 3e75bf2f-6401-11ea-8995-000c29db65a6:1 1 row in set (0.00 sec)

-create a replication account on the main database 133:

Root@localhost: (none) 04:12:23 > create user repl@'192.168.3.%' identified by 'repl'; Query OK, 0 rows affected (0.00 sec) root@localhost: (none) 04:13:03 > grant all on. To repl@'192.168.3.%'; Query OK, 0 rows affected (0.00 sec)

-completion of the replication step on the slave library 134:

Root@localhost: (none) 04:16:09 > change master to master_host='192.168.3.133',master_port=3307,master_user='repl',master_password='repl',master_auto_position=1; Query OK, 0 rows affected, 2 warnings (0.00 sec) root@localhost: (none) 04:17:32 > start slave Query OK 0 rows affected (0.00 sec) root@localhost: (none) 04:17:35 > show slave status\ G * * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.3.133 Master_User: repl Master_Port: 3307 Connect _ Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 1193 Relay_Log_File: mgr3-relay-bin.000002 Relay_Log_Pos: 904 Relay_Master_Log_File: mysql-bin.000002 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: 1193 Relay_Log_Space: 1103 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No 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: 207Master_UUID: 3e75bf2f-6401-11ea-8995-000c29db65a6 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: 3e75bf2f-6401-11ea-8995-000c29db65a6:3-4 Executed_Gtid_Set: 3e75bf2f-6401-11ea-8995-000c29db65a6:1-4 Auto_Position: 1 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)

So far, the remote slave library has been successfully built through the clone plug-in, which is very simple and fast, and does not require mysqldump or xtrabackup. Online construction is successful and very fast. We can consider this convenient way in the future.

Thank you for reading! This article on "how to build a remote slave library through Clone in MySQL 8.0" ends here. I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it out for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.