当前位置:网站首页>centOS下mysql主从配置
centOS下mysql主从配置
2022-04-23 14:00:00 【白云碎里一蓑舟】
一、基础配置
在两台mysql中分别创建数据库(名称、结构、编码必须一致
二、主服务器配置
修改my.cnf文件
[mysqld]
slow_query_log=ON
long_query_time=5
pid-file=/var/run/mysqld/mysqld.pid
socket=/var/run/mysqld/mysqld.sock
datadir=/var/lib/mysql
server-id=001 #服务id必须唯一
read-only=0
log-bin=mysql-bin
log_bin_trust_function_creators=1
transaction-isolation=READ-COMMITTED
character-set-server = utf8
server-id=001
binlog-do-db=db1 #同步的数据库名称
binlog-do-db=db2 #同步的数据库名称
expire_logs_days=10
binlog_format=MIXED
max_allowed_packet=256M
max_binlog_size=1024m
innodb_log_file_size=2GB
group_concat_max_len=1024000
lower_case_table_names=1
max_connect_errors=1000
symbolic-links=0
log-error=/var/log/mysqld.log
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
登录mysql配置从服务器登录主服务器的账号授权
--授权操作
set global validate_password_policy=0;
set global validate_password_length=1;
grant replication slave on *.* to 'root'@'%' identified by '123456';
--刷新权限
flush privileges;
#重启mysql服务
service mysqld restart
三、配置从服务区
修改my.cnf文件
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[mysqld]
#端口号(不能重复
port=3301
# 设置mysql的安装目录
basedir=/usr/local/mysql
# 设置mysql数据库的数据的存放目录
datadir=/data/mysql_platform/data
#服务器的id(不能重复
server-id=101
socket=/data/mysql_platform/mysql.sock
# 错误日志存放目录
log_error=/data/mysql_platform/logs/mysql.log
# 二进制日志存放目录
log_bin=/data/mysql_platform/logs/mysql-bin
#二进制文件的格式
binlog-format=MIXED
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
#同步的数据库名称
replicate-do-db=db1
replicate-do-db=db2
#创建slave_master_info的表
master-info-repository = table
#创建mysql.slave_relay_info表来记录同步的位置信息
relay-log-info-repository = table
1.重启mysql服务
systemctl restart mysql_platform.service
2.登录主库查看相关信息
#登录mysql数据库
mysql -urep_db_user -p
#查看master的状态
show master status\G(注意:没有分号
3.登录从库并连接
#登录从库mysql
mysql -uroot -p -S /data/mysql_platform/mysql.sock
#连接主服务器
change master to master_host='192.168.100.195',master_user='root',master_password='123456',master_port=3306,master_log_file='m y-logbin.000067 ',master_log_pos=970;
(注意:断开连接后,master_log_file和master_log_pos 会发生改变,重连需重新查一遍)
4.启动slave
start slave
5.查看slave的状态
show slave status\G
注意:只有【Slave_IO_Running】和【Slave_SQL_Running】都是Yes,则同步是正常的。 如果是No或者Connecting都不行,可查看错误日志排查问题。
6、结束啦 ~~ 一起来试试看~~
版权声明
本文为[白云碎里一蓑舟]所创,转载请带上原文链接,感谢
https://blog.csdn.net/m0_49513507/article/details/123550807
边栏推荐
- Building MySQL environment under Ubuntu & getting to know SQL
- Reading notes: fedgnn: Federated graph neural network for privacy preserving recommendation
- elmo(BiLSTM-CRF+elmo)(Conll-2003 命名实体识别NER)
- JS 力扣刷题 102. 二叉树的层序遍历
- 生成随机高质量符合高斯分布的随机数
- Express ② (routage)
- Leetcode? The first common node of two linked lists
- 自动化的艺术
- The latest development of fed digital currency
- JUC interview questions about synchronized, ThreadLocal, thread pool and atomic atomic classes
猜你喜欢
scikit-learn構建模型的萬能模板
MySQL index [data structure + index creation principle]
Quartus Prime硬件实验开发(DE2-115板)实验一CPU指令运算器设计
Neuron and neural network
Express ② (routing)
编程旅行之函数
大专的我,闭关苦学 56 天,含泪拿下阿里 offer,五轮面试,六个小时灵魂拷问
Solution of discarding evaluate function in surprise Library
Un modèle universel pour la construction d'un modèle d'apprentissage scikit
Special test 05 · double integral [Li Yanfang's whole class]
随机推荐
3300万IOPS、39微秒延迟、碳足迹认证,谁在认真搞事情?
Dynamic subset division problem
网站_收藏
How does redis solve the problems of cache avalanche, cache breakdown and cache penetration
BUG_me
Strange bug of cnpm
Yarn online dynamic resource tuning
Crontab timing task output generates a large number of mail and runs out of file system inode problem processing
快捷键(多行)
Oracle alarm log alert Chinese trace and trace files
Basic knowledge learning record
2022年江西最新建筑八大员(质量员)模拟考试题库及答案解析
SQL learning window function
China creates vast research infrastructure to support ambitious climate goals
收藏博客贴
MySQL [acid + isolation level + redo log + undo log]
低频量化之明日涨停预测
Decentralized Collaborative Learning Framework for Next POI Recommendation
Interesting talk about network protocol
Get the attribute value difference between two different objects with reflection and annotation