当前位置:网站首页>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
边栏推荐
- Golang's pen test questions & interview questions 01
- 解读机器人创造出来的艺术
- 解析性能良好的机器人使用守则
- Precautions for PCB
- QT 64 bit static version display gif
- Résumé de la relation entre GPU, cuda et cudnn
- tensorflow常用的函数
- Siri gave the most embarrassing social death moment of the year
- Interpreting the art created by robots
- 第四章 为IM 启用填充对象之启用和禁用列(IM-4.3 第三部分)
猜你喜欢
Study notes of C [8] SQL [1]
力扣-70.爬楼梯
微型机器人的认知和研发技术
Nacos Foundation (9): Nacos configuration management from single architecture to microservices
qt 64位静态版本显示gif
Interpretation of 2022 robot education industry analysis report
让中小学生在快乐中学习的创客教育
kettle复制记录到结果和从结果获取记录使用
一文详解头部位姿估计【收藏好文】
MQ is easy to use in laravel
随机推荐
论坛系统数据库设计
全网最细的短网址系统设计与实战
简易投票系统数据库设计
The way to change children's programming structure
RebbitMQ的初步了解
力扣-70.爬楼梯
Database design of forum system
Laravel admin form validation
项目实训-火爆辣椒
第四章 为物化视图启用和禁用IM列存储(IM 4.6)
TclError: no display name and no $DISPLAY environment variable
Resolution due to AMD not found_ ags_ x64. DLL, unable to continue code execution. Reinstallation of the program may solve this problem, Forza horizon 5
IM表达式的目的(IM 5.2)
远程访问家里的树莓派(上)
MySQL 的主从复制配置
配电房远程综合监控系统在10kV预制舱项目中的应用
解析幼儿教育中steam教育的融合
laravel-admin表单验证
Nacos Basics (5): getting started with Nacos configuration
第四章 为IM 启用填充对象之在NO INMEMORY表上指定INMEMORY列属性:示例(IM-4.4 第四部分)