Shulou(Shulou.com)06/01 Report--

Two servers, the system is Redhat6.5,MySQL version 5.7.18.

1. On the main library, create a user for replication and grant replication slave permission. The user repl is created here, and you can connect from the host with IP 10.10.10.210.

Grant replication slave on *. * to 'repl'@'10.10.10.210' identified by' mysql'

2. Modify the configuration of the master server and add the following configuration:

Cat / etc/my.cnf

[mysqld]

Server-id=1

Log-bin=mysql-bin

Log-bin-index=mysql-bin.index

Binlog_format=mixed

3. Set a read lock on the main database to ensure that there is no data operation and get a consistent snapshot.

Flush tables with read lock

4. Then get the current binary log name and offset value on the main library, and the purpose of the change operation is to recover the data from this point after the library is started.

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | |

+-+

| | mysql-bin.000006 | 120 | |

+-+

5. Export the data using mysqldump and copy it to the slave server.

6. Backup of the main database is completed, and write operation is resumed.

Unlock tables

7. Modify the configuration file of the slave library and add the following parameters. Note that the server-id must be unique and cannot be the same as the master library. If there are multiple slave libraries, the server-id cannot be duplicated.

Cat / etc/my.cnf

[mysqld]

Server-id=2

8. On the slave library, start the database with-- skip-slave-start, so that the replication process on the slave library will not be started immediately, which is convenient for us to proceed to the next step of configuration.

. / bin/mysqld_safe-- skip-slave-start &

9. Configure the slave library, specify the user to be used for replication, the IP and port of the master library, and the log file and location where the replication starts:

Change master to

Master_host='10.10.10.200'

Master_port=3306

Master_user='real'

Master_password='mysql'

Master_log_file='mysql-bin.000006'

Master_log_pos=120

10. Start the slave thread on the slave library

Start slave

11. Check the slave status on the slave library

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 10.10.10.200

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000006

Read_Master_Log_Pos: 120

Relay_Log_File: mysql-relay-bin.000026

Relay_Log_Pos: 283

Relay_Master_Log_File: mysql-bin.000006

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

Relay_Log_Space: 619

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

Master_UUID: 4adfcd1d-4059-11e7-9532-080027d597f9

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 the slave I/O thread to update it

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

12. Perform DDL or DML tests in the master database, and check the data synchronization from the slave library.

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.