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

This article mainly explains "the deployment process of mysql traditional master-slave replication". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "the deployment process of mysql traditional master-slave replication".

Deployment process

1. Ensure that the data and structure of Master library and Slave library are consistent.

You can complete the Master library through mysqldump or XtraBackup tools, and then transfer the backup files to the Slave library, and the Slave library can be restored using the modified backup to make the two consistent.

My Slave library here is generated by copying the Master library directly, so there is no need to do this step.

Configure the parameter files of the two libraries

The Master library needs to open the binary log

The Master library needs to set a different server-id from the Slave library

[root@potato data] vi / etc/my.cnf

Binlog_format = mixed

Server-id = 203306

Log-bin = mybinlog

The Slave library only needs to be configured with server-id

[root@tomato data] vi / etc/my.cnf

Server-id = 203307

Third, create and empower users for replication

The user must be given replication slave permission to specify the host address of the Slave library

Click (here) to collapse or open

Root@localhost:mysql.sock 05:02:32 [(none)] > grant replication slave on *. * to repl@'192.168.161.128' identified by 'repl'

4. Change the Slave library to the Master library

View the log location of the Master library at this time

Click (here) to collapse or open

Root@localhost:mysql.sock 05:02:32 [(none)] > show master status

+-+

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

+-+

| | mybinlog.000004 | 331 |

+-+

1 row in set (0.00 sec)

Change from the library to the master library

Click (here) to collapse or open

Root@localhost:mysql.sock 04:29:15 [(none)] > change master to

-> master_host='192.168.161.128'

-> master_port=3306

-> master_user='repl'

-> master_password='repl'

-> master_log_file='mybinlog.000004'

-> master_log_pos=331

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id: 2

Current database: * * NONE * *

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

5. The Slave library executes the start copy command

Click (here) to collapse or open

Root@localhost:mysql.sock 04:40:11 [(none)] > start slave

At this point, master-slave replication has been configured.

6. Test the built environment

The main library starts inserting data testing

Click (here) to collapse or open

Root@localhost:mysql.sock 04:40:11 [(none)] > use lala

Root@localhost:mysql.sock 04:51:23 [(lala)] > create table (id int)

Root@localhost:mysql.sock 04:51:23 [(lala)] > insert into values (1)

Whether the library receives data or not

Click (here) to collapse or open

Root@localhost:mysql.sock 04:52:55 [(none)] > select * from lala.

+-+

| | id |

+-+

| | 1 |

+-+

1 row in set (0.00 sec)

View Slave status

Click (here) to collapse or open

Root@localhost:mysql.sock 04:57:23 [(none)] > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.161.128

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mybinlog.000004

Read_Master_Log_Pos: 620

Relay_Log_File: mysql-relay-bin.000003

Relay_Log_Pos: 571

Relay_Master_Log_File: mybinlog.000004

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

Relay_Log_Space: 744

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

Master_UUID: af3609cd-b426-11e6-a997-000c29d55626

Master_Info_File: / data/mysql/mytest_3306/data/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

1 row in set (0.00 sec)

Master-slave replication deployment test was successful

Thank you for your reading. the above is the content of "the deployment process of traditional master-slave replication of mysql". After the study of this article, I believe you have a deeper understanding of the deployment process of traditional master-slave replication of mysql, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.