当前位置:网站首页>Master slave replication configuration of MySQL
Master slave replication configuration of MySQL
2022-04-23 11:39:00 【you and me】
Abstract
Large projects pay special attention to backup , Generally, there is dual computer backup , Hot standby and cold standby , Remote disaster recovery and so on … Today, let's talk about... On two servers MySQL Master and slave copy backup , The requirements are simple : Slave data to be synchronized with master , But not too often , keep 15 Minutes of data difference , It means every 15 Minutes to synchronize once modified data .
Text
Our environment here is two Linux The server , One is on the public network , One is on the intranet , The public network is the master , Intranet is from , The databases are MySQL 5.7( Use LNMP install ).
Main warehouse operation
First, let's create a new data synchronization user , Let users use this from the library to synchronize data
CREATE USER 'slave'@'%' IDENTIFIED BY ' password ';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';
The percent sign indicates that the user can use any number of IP Sign in .
Then we need to back up the main database , If your library is not accessed at this time , Then you don't have to lock the watch , But it's best to lock the table first and then back up
mysql> flush tables with read lock;
Use mysqldump Back up libraries that need to be synchronized , I'm syncing here mikublog This library
mysqldump -uroot -p --databases mikublog | gzip > mysql_bak.$(date + %F)sql.gz
After the backup, record the current location , remember File and Position Value
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000014 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
Last unlock table
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
Operation from the library
Copy the compressed package just backed up , And extract the :gzip -d Compressed package name , And then put sql Brush it into the database .
modify my.cnf, Generally, you only need to change two values , hold server-id Change to unique value
[mysqld]
log-bin=mysql-slave-bin
server-id = 2
After the change, restart the database
service mysql restart
Set the master-slave configuration
mysql> change master to master_host='192.168.100.110', master_user='slave', master_password='mypassword', master_port=3306, master_log_file='mysql-bin.000014', master_log_pos= 154, master_delay=900, master_connect_retry=30;
Meaning of each parameter :
master_host : The address of the main library
master_port: The port number of the main library
master_user: Users for data synchronization
master_password: Password of the user for synchronization
master_log_file: Appoint Slave From which log file to start copying data , As mentioned above File Value of field
master_log_pos: From which Position Start reading , As mentioned above Position Value of field
master_delay: Delayed replication configuration , Set the delay from the database N Seconds later , And then synchronize with the master database
master_connect_retry: If the connection fails , Retry interval , The unit is seconds , The default is 60 second
Enable master-slave replication
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
View synchronization status
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
...
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
1 row in set (0.00 sec)
SlaveIORunning and SlaveSQLRunning All are Yes Indicates that master-slave replication has been turned on .
The end
That's all MySQL The content of the master-slave replication configuration , Welcome friends to exchange and discuss .
版权声明
本文为[you and me]所创,转载请带上原文链接,感谢
https://yzsam.com/2022/04/202204231136322971.html
边栏推荐
- 微型机器人的认知和研发技术
- SOFA Weekly | 年度优秀 Committer 、本周 Contributor、本周 QA
- 2022 love analysis · panoramic report of industrial Internet manufacturers
- 解析幼儿教育中steam教育的融合
- Interprocess communication -- message queue
- nacos基础(6):nacos配置管理模型
- Castle.DynamicProxy实现事务单元控制
- C# F23.StringSimilarity库 字符串重复度、文本相似度、防抄袭
- Maker education for primary and middle school students to learn in happiness
- Study notes of C [8] SQL [1]
猜你喜欢
配电房远程综合监控系统在10kV预制舱项目中的应用
力扣-1137.第N个泰波那契数
Nacos Foundation (6): Nacos configuration management model
MQ is easy to use in laravel
解决由于找不到amd_ags_x64.dll,无法继续执行代码。重新安装程序可能会解决此问题,地平线(Forza Horizon 5)
激活函数之阶跃函数
Change exchange II - [leetcode]
Advanced file IO of system programming (13) -- IO multiplexing - Select
科创人·派拉软件CEO谭翔:零信任本质是数字安全,To B也要深研用户心智
Redis学习之五---高并发分布式锁实战
随机推荐
How does QT turn qwigdet into qdialog
qt5. 8. You want to use SQLite in the 64 bit static library, but the static library has no method to compile the supporting library
力扣-1137.第N个泰波那契数
Summary of QT semaphore unresolved errors
Redis optimization series (II) redis master-slave principle and master-slave common configuration
The fourth chapter is to enable the filling object of IM and enable ADO for im column storage (IM 4.8)
IFLYTEK's revenue in 2021 was 18.3 billion yuan: a year-on-year increase of 41% and a net profit of 1.556 billion yuan
微型机器人的认知和研发技术
探究机器人教育的器材与教学
ES6 learning notes II
Understanding of MQ
解读2022机器人教育产业分析报告
Precautions for PCB
RebbitMQ的初步了解
C# F23.StringSimilarity库 字符串重复度、文本相似度、防抄袭
QT 64 bit static version display gif
Chapter 4: enable and disable im column storage for materialized view (IM 4.6)
The fourth chapter is the enable and disable columns of IM enabled fill objects (Part III of im-4.3)
MQ is easy to use in laravel
nacos基础(7):配置管理