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

Specific steps to configure non-interactive master-slave synchronization of mysql database

2025-04-07 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

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

The following mainly brings you the concrete steps of configuring mysql database master-slave synchronization and non-interactive. I hope that the specific steps of configuring mysql database master-slave synchronization non-interactive can bring you practical use. this paper takes a mysql database multi-instance 3306 and 3308 as an example, 3306 as the master database and 3308 as the slave database (multiple single instance configuration is the same as a multi-instance configuration).

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 3308} / my.cnf

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

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

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

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

two。 Restart databases 3306 and 3308

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

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

[root@mysql ~] # / data/3308/mysqlstop

[root@mysql ~] # / data/3308/mysqlstart

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。 Master library 3306 backup data, import slave library 3308

1. Back up data on the main library 3306

[root@mysql] # mysqldump-uroot-p123456-S / data/3306/mysql.sock-A-- events-B-x-- master-data=1 | gzip > / opt/$ (date +% F) .sql.gz

-A: all libraries-x: global locking table

two。 Import data from library 3308

[root@mysql ~] # ls / opt

2017-07-03.sql.gz

[root@mysql ~] # cd / opt

[root@mysql opt] # gzip-d2017-07-03.sql.gz

[root@mysql opt] # ls

2017-07-03.sql

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

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

*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