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

Today, I will talk to you about how to create a slave database in the Mysql database, many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

First, refer to http://blog.itpub.net/28536251/viewspace-2138854/ to install Mysql on two nodes.

1. Install the backup software from the master library

[root@D2-wetestdb160 ~] # yum install-y perl-DBD-MySQL perl-DBI perl-Time-HiRes

[root@D2-wetestdb160] # rpm-ivh libev-4.15-6.el7.x86_64.rpm percona-xtrabackup-24-2.4.5-1.el7.x86_64.rpm

2. The main database backs up the database.

[root@D2-wetestdb160 ~] # mkdir / data/backup

[root@D2-wetestdb160] # innobackupex-- user=root-- password=123456 / data/backup

[root@D2-wetestdb160] # tar-cvzf mysqlbackup84106_0113.tar.gz / data/backup/2017-01-13 11-17-22 /

[root@D2-wetestdb160 ~] # scp mysqlbackup84106_0113.tar.gz root@10.163.89.245:/root

3. Restore from the library

[root@D2-stone245] # tar-xvzf mysqlbackup84106_0113.tar.gz-C / var/lib/mysql

[root@D2-stone245] # innobackupex-- apply-log / var/lib/mysql/data/backup/2017-01-13 11-17-22 /

[root@D2-stone245] # chown-R mysql:mysql / var/lib/mysql/data/backup/2017-01-13 11-17-22

4. Modify the configuration file from the library

[root@D2-stone245 ~] # / etc/init.d/mysqld stop

[root@D2-stone245 ~] # vim / etc/my.cnf

[mysqld]

Datadir=/var/lib/mysql/data/backup/2017-01-13 11-17-22 /

Server-id=89254

[root@D2-stone245 ~] # / etc/init.d/mysql.server start

5. Copy from library configuration

[root@D2-stone245] # cat / var/lib/mysql/data/backup/2017-01-13 11-17-22/xtrabackup_binlog_info

Mysql-bin.000010 100927559

Mysql > CHANGE MASTER TO MASTER_HOST='10.163.84.160',MASTER_PORT=3306,MASTER_USER='repl',MASTER_PASSWORD='replsafe',MASTER_LOG_FILE='mysql-bin.000010',MASTER_LOG_POS=100927559

Query OK, 0 rows affected, 2 warnings (0.01 sec)

Mysql > start slave

Query OK, 0 rows affected (0.00 sec)

Mysql > set global read_only=1

Query OK, 0 rows affected (0.00 sec)

Mysql > show global variables like "read_only%"

+-+ +

| | Variable_name | Value |

+-+ +

| | innodb_read_only | OFF |

| | read_only | ON |

| | super_read_only | OFF |

| | tx_read_only | OFF |

+-+ +

4 rows in set (0.01sec)

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 10.163.84.160

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000011

Read_Master_Log_Pos: 1719

Relay_Log_File: D2-stone245-relay-bin.000003

Relay_Log_Pos: 1932

Relay_Master_Log_File: mysql-bin.000011

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

Relay_Log_Space: 6430

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

Master_UUID: 9ee791f2-ad84-11e6-932d-0050568a6255

Master_Info_File: / var/lib/mysql/data/backup/2017-01-13 11-17-22/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)

After reading the above, do you have any further understanding of how to create a slave database in the Mysql database? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.