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

This article mainly shows you "how to use xtrabackup tools to create slave nodes", the content is easy to understand, clear, hope to help you solve your doubts, the following let Xiaobian lead you to study and learn "how to use xtrabackup tools to create slave nodes" this article.

How to install and backup xtrabackup, please refer to my other blog http://blog.itpub.net/20893244/viewspace-2133530/ first.

Environment:

Master ip:192.168.3.171

Slave ip: 192.168.3.173

Mysql version:

(root@localhost) [gldb] > select version ()

+-+

| | version () |

+-+

| | 5.7.17-log |

+-+

1 row in set (0.00 sec)

It takes seven steps to create a slave node with the xtrabackup tool. Let's do it step by step.

one。 Full backup of data at the master node

Innobackupex-- defaults-file=/etc/my.cnf-- host=oracle11gtest-- user=xtrabk-- parallel=4-password=onlybackupgl-- extra-lsndir=/alidata1/mysqlbackup/mysql_full-- stream=tar / tmp | gzip > / alidata1/mysqlbackup/mysql_full/xtra_fullbak_2017-02-20.tar.gz

two。 Copy and prepare backup sets

1. Copy the backup set to the server

Scp-r / alidata1/mysqlbackup/mysql_full root@192.168.3.173:/alidata1/mysqldata/mysqlbackup

two。 Extract the backup set on the server

Tar-xzvf xtra_fullbak_2017-02-20.tar

3. Prepare the data and execute the innobackupex command to append-- apply-log parameter

Innobackupex-apply-log / alidata1/mysqldata/mysqlbackup/mysql_full

three。 Create a dedicated account for the replication environment and grant permissions

Create user repl@'192.168.3.173'

Grant replication slave on *. * to repl identified by 'replmysql'

four。 Configure initialization parameters for slave nodes

Copy from master to server, or use the scp command

Modify the initialization file on the slave side to change the server_id to a non-zero value

Start the database

Mysqld_safe-- defaults-file=/etc/my.cnf &

five。 Configure the slave node replication environment

View the data in the xtrabackup_binlog_info file

[root@mysqltest mysql_full] # more xtrabackup_binlog_info

Mysql-bin.000016 1884

Execute the change masger command

Change master to

Master_host='192.168.3.171'

Master_port=3306

Master_user='repl'

Master_password='replmysql'

Master_log_file='mysql-bin.000016'

Master_log_pos=1884

Execute the start slave command

Mysql > start slave

six。 Check

(root@localhost) [(none)] > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.3.171

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000016

Read_Master_Log_Pos: 3631

Relay_Log_File: mysqltest-relay-bin.000002

Relay_Log_Pos: 2067

Relay_Master_Log_File: mysql-bin.000016

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: 3631

Relay_Log_Space: 2278

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: 2

Master_UUID: 659e33c7-f1ef-11e6-8e3e-00163e3225da

Master_Info_File: / alidata1/mysqldata/3306/data/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:

1 row in set (0.00 sec)

seven。 test

Create a table on the masterside

(root@localhost) [(none)] > use gldb

Database changed

(root@localhost) [gldb] > create table gl (abcd varchar (20))

Query OK, 0 rows affected (0.51sec)

Query whether it passed successfully on the slave side.

(root@localhost) [(none)] > use gldb

Database changed

(root@localhost) [gldb] > show create table gl

+-+ +

| | Table | Create Table |

+-+ +

| | gl | CREATE TABLE `gl` (

`abcd` varchar (20) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

+-+ +

1 row in set (0.00 sec)

Synchronization succeeded ~ ~

The above is all the content of the article "how to create slave nodes with xtrabackup tool". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.