Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

Configure the interactive operation method of master-slave synchronization in mysql database

2025-01-28 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

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

The following to understand the configuration of mysql database master-slave synchronous interactive operation, I believe you will benefit a lot after reading, the text in the essence is not much, hope to configure the mysql database master-slave synchronous interactive operation of this short content is what you want.

I. my.cnf file configuration

1. Modify the my.cnf configuration file. The log-bin,server-id cannot be opened for the main database 3306.

[root@mysql ~] # egrep "log-bin | server-id" / data/ {3306pr 3307} / my.cnf

/ data/3306/my.cnf:log-bin = / data/3306/mysql-bin

/ data/3306/my.cnf:server-id = 1

/ data/3307/my.cnf:#log-bin = / data/3307/mysql-bin

/ data/3307/my.cnf:server-id = 3

two。 Restart databases 3306 and 3307

[root@mysql ~] # / data/3306/mysql stop

[root@mysql ~] # / data/3306/mysql start

[root@mysql ~] # / data/3307/mysql stop

[root@mysql ~] # / data/3307/mysql start

3. Enter the main database 3306 and query whether log_bin is open and what the server_id is.

[root@mysql] # mysql-uroot-p123456-S / data/3306/mysql.sock

Mysql > show variables like "log_bin"

+-+ +

| | Variable_name | Value |

+-+ +

| | log_bin | ON |

+-+ +

1 row in set (0.01 sec)

Mysql > show variables like "server_id"

+-+ +

| | Variable_name | Value |

+-+ +

| | server_id | 1 | |

+-+ +

1 row in set (0.00 sec)

4. Create a user dedicated to synchronizing the database

Mysql > grant replicationslave on *. * to rep@'10.0.0.%' identified by '123456'

# *. * represents all libraries and all tables

Mysql > flush privileges

Mysql > select user,hostmysql.user

Mysql > show grants forrep@'10.0.0.%'

two。 Backup of main library 3306

Mysql > flush table with readlock; / / Lock the table, which is read-only and cannot be written. The mysql window cannot exit. If you exit, the locking table will fail. If you exceed the default locking time, the lock will be unlocked automatically.

Mysql > show variables like'timeout%'; / / View the default table locking time

Mysql > show master status; / / View binlog location

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql-bin.000004 | 328 | | |

+-+

1 row in set (0.00 sec)

[root@mysql] # mysqldump-uroot-p123456-S / data/3306/mysql.sock-A-B | gzip > / opt/bak_$ (date+%F). Sql.gz / / Open a new CRT window for backup

[root@mysql ~] # ls / opt

Bak_2017-06-28.sql.gz

After mysql > show master status; / / back up the data, check the binlog location again to make sure that no new data is written during this period

Mysql > unlock tables; / / unlock

three。 Restore the data backed up by the master database 3306 to the slave library 3307

1. Log in to 3307 and confirm that logbin is closed. Server id does not conflict with 3306.

[root@mysql] # mysql-uroot-p123456-S / data/3307/mysql.sock

Mysql > show variables like "log_bin"

+-+ +

| | Variable_name | Value |

+-+ +

| | log_bin | OFF |

+-+ +

1 row in set (0.01 sec)

Mysql > show variables like "server_id"

+-+ +

| | Variable_name | Value |

+-+ +

| | server_id | 3 | |

+-+ +

1 row in set (0.00 sec)

two。 Restore the data backed up by 3306 to 3307

[root@mysql ~] # cd / opt

[root@mysql opt] # ls

Bak_2017-06-28.sql.gz

[root@mysql opt] # gzip-dbak_2017-06-28.sql.gz

[root@mysql opt] # ls

Bak_2017-06-28.sql

[root@mysql opt] # mysql-uroot-p123456-S / data/3307/mysql.sock CHANGE MASTER TO

-> MASTER_HOST='10.0.0.20'

-> MASTER_PORT=3306

-> MASTER_USER='rep'

-> MASTER_PASSWORD='123456'

-> MASTER_LOG_FILE='mysql-bin.000004'

-> MASTER_LOG_POS=328

Mysql > start slave; / / Slave Library 3307 turns on the master-slave copy switch

Mysql > show slave status\ G / / check synchronization status

* * 1.row * *

Slave_IO_State: Waiting formaster to send event

Master_Host: 10.0.0.20

Master_User: rep

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000004

Read_Master_Log_Pos: 328

Relay_Log_File: relay-bin.000002

Relay_Log_Pos: 253

Relay_Master_Log_File: mysql-bin.000004

Slave_IO_Running:Yes / / confirm that these two entries are yes status

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB: mysql

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

Relay_Log_Space: 403

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 / / number of seconds that the slave database is delayed than the master library during replication

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

1 row in set (0.00 sec)

Or check the synchronization status like this

[root@mysql ~] # mysql-uroot-p123456-S / data/3307/mysql.sock-e "show slave status\ G" | egrep-I "running | _ behind" / /-e does not need to log in to mysql to interactively type the command

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Seconds_Behind_Master: 0

View thread status

[root@mysql] # mysql-uroot-p123456-S/data/3307/mysql.sock-e "show processlist\ G"

[root@mysql] # mysql-uroot-p123456-S/data/3306/mysql.sock-e "show processl

four。 test

Create a database on the main library 3306, and then log in to 3307 to see if it is synchronized properly. It has been tested that it has been synchronized properly ^ _ ^

After reading this article on how to configure the master-slave synchronous interactive operation of mysql database, many readers will want to know more about it. If you need more industry information, you can follow our industry information section.

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.

Views: 280

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report