当前位置:网站首页>MySQL master-slave replication details
MySQL master-slave replication details
2022-07-21 21:42:00 【Rocky000000】
1. Introduction and principle of master-slave replication
Master slave copy ( Also known as AB Copy ) It refers to a server acting as the primary database server , Another or more servers act as slave database servers , Data from the master server is automatically copied to the slave server . For multi-level replication , The database server can act as a host , It can also act as a slave .MySQL Asynchronous replication is used by default .
The process and principle of master-slave replication can be summarized as follows :
①、master The server records changes in data in binary binlog journal , When master When the data on changes , Write its changes to the binary log .
②、slave The server will respond to master Binary log to detect if it changes , If there is a change , Then start with a I/OThread request master Binary events .
③、 At the same time, the primary node is each I/O Thread starts a dump Threads , Used to send binary events to , And save it to the relay log local to the slave node , The slave node will start SQL Thread reads binary log from relay log , Replay... Locally , Make its data consistent with that of the primary node .
2. Configure master-slave replication based on binary file location
Master-slave replication based on binary file location can also be called traditional replication , That is, the slave server depends on the master server binlog file location , When the data in the main database changes ,binlog pos Loci will grow , Changes will be sensed from the library to complete synchronization .
Configure master-slave replication , We must first prepare at least two MySQL example , One serves as the main server 、 One acts as a slave server . Because master-slave replication depends on binlog, So the main database must be opened binlog, And the master and slave should be configured with different server_id, The configuration process is shown below :
2.1 Confirm the configuration parameters of master and slave libraries
MySQL The following configurations are recommended for master-slave servers , You can confirm , If not configured , You need to modify the configuration file and restart .
Configuration of main database parameters Have the following parameters
vim /etc/my.cnf
[mysqld]
log-bin = binlog // Enable binary logging
server-id = 105 // The server is unique ID, The default value is 1, Generally set as IP The last digit of the address
binlog_format = row //bilog Set to row Pattern Prevent replication errors
It is recommended to configure the following parameters from the library
vim /etc/my.cnf
[mysqld]
relay-log = relay-bin
server-id = 103
2.2 Determine the binary location of the main library , Create a sync account
If the master and slave libraries have just been initialized , And there is no operation in the main library , The slave database does not need to synchronize the data of the master database , Directly determine the binlog Just place .
# Check the main library binlog file location
show master status;
# The master database creates a synchronous account
create user 'repl'@'%' identified by '123456';
grant replication slave on *.* to 'repl'@'%';
If the main library has been running for some time , There's business data in , The slave library has just been initialized , At this time, you need to back up the data of the main database , Then import from the library , Make master-slave data consistent .
# The master database creates a synchronous account
set global validate_password_policy = 0;
create user 'repl'@'%' identified by '123456';
grant replication slave on *.* to 'repl'@'%';
# Full master database data
mysqldump -uroot -pxxxx -A -R -E --single-transaction --master-data=2 > all_db.sql
# This can also be backed up
sudo mysqldump -u root -p --all-databases > allbackup20211112.sql
# Recover from the library side
mysql -uroot -pxxxx < all_db.sql
# From the backup file, you can find the main library binlog Location
2.3 Enter slave Library , Enable master-slave replication
Find the location of the binary file of the master library and complete the master-slave data consistency , We can officially start master-slave replication .
# Enter slave Library MySQL Command line perform change master Statement to connect to the main library
# Binary file name and pos The position is obtained from the above steps
CHANGE MASTER TO MASTER_HOST='MySQL master server IP Address ',
MASTER_PORT=3306,
MASTER_USER='repl',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='binlog.000002',
MASTER_LOG_POS=154;
# Enable master-slave replication And stick to it
start slave;
show slave status \G // see slave state Make sure Slave_IO_Running: Yes Slave_SQL_Running: Yes
边栏推荐
- 【机器学习】两分钟论文导读:基于深度学习的3D地形生成器
- Invalid bound statement (not found): com. example. demo1.mapper. UserMapper. XXX
- Flutter2的新功能
- 量子力学奇妙之旅-前置准备
- Past and present life of signal reflection
- 有小伙伴说看不懂 LiveData、Flow、Channel,跟我走
- 數據存儲方案(二)-SQLite數據庫存儲
- 信号反射的前世今生
- 【在Kotlin中创建生日祝福语】
- Data storage scheme (I) - file storage and SharedPreferences storage
猜你喜欢
Flutter实战-自定义键盘(一)
国内首批!阿里云云原生数据湖产品通过信通院评测认证
Suse12 installing SAP Hana 2.0 in memory database
Overview of quantum measurement, quantum computation and quantum communication
Past and present life of signal reflection
直流角度看电源完整性
一个菜鸟管理的学习和思考(二)
高通Camera 软件工程师的成长之路
Use jdbcutil to complete database addition, deletion, modification and query
有小伙伴说看不懂 LiveData、Flow、Channel,跟我走
随机推荐
IDC:阿里云获2021中国数据治理平台市场份额第一
Basic concepts and internal principles of dart
Past and present life of signal reflection
[2019][]自适应学习平台的关键技术与典型案例
量子力学奇妙之旅-算符/薛定谔方程/概率流密度
Camera摄像头工作原理
Configure alarm messages for SQL Server
数据存储方案(二)-SQLite数据库存储
量子力学奇妙之旅-算符和电子自旋
手把手带你自定义 Gradle 插件 —— Gradle 系列(2)
Oracle RAC image recovery single instance database redo log incremental extraction error: ora-01291 & delete log group error: ora-01567
Flutter's learning path - Summary
IMPALA-SHELL导出Kudu上的900多万级别表(下)_完成传输
Broadcasts
The growth path of Qualcomm camera software engineers
再突破!阿里云进入Gartner云AI开发者服务挑战者象限
Common commands for PostGIS spatial data (geometry) management
For sensors and ISPs, precautions for crop and downscale of output images
Suse12 installing SAP Hana 2.0 in memory database
Understand the problem of static library / dynamic library link in one article