目录
7、修改主库mysql_master /etc/my.cnf 的配置文件
8、修改从库master /etc/my.cnf 的配置文件
10、登录主库mysql_master,创建主从连接帐号与授权
1、高可用,实时灾备,用于故障切换。比如主库挂了,可以切从库;
2、读写分离,提供查询服务,减少主库压力,提升性能;
3、备份数据,避免影响业务。
1、主库的更新SQL(update、insert、delete)被写到binlog;
2、从库发起连接,连接到主库;
3、此时主库创建一个binlog dump thread,把bin log的内容发送到从库;
4、从库启动之后,创建一个I/O线程,读取主库传过来的bin log内容并写入到中继日志relay log;
5、从库还会创建一个SQL线程,从relay log里面读取内容,从ExecMasterLog_Pos位置开始执行,读取到的更新事件,将更新内容写入到slave的db。
主从关系: Mysql_master为主,Mysql_slave为从
mysql_master ip:192.168.40.142
mysql_slave ip: 192.168.40.143
镜像:Centos7.9
虚机配置:4U4G 100G存储
/boot 800MB
/swap 4G
/ 95.2G虚拟机安装完成后配置网卡
[root@localhost ~]# cat /etc/sysconfig/network-scripts/ifcfg-ens33
TYPE=Ethernet
BOOTPROTO=dhcp
DEFROUTE=yes
NAME=ens33
DEVICE=ens33
ONBOOT=yes关闭防火墙
systemctl stop firewalld && systemctl disable firewalld关闭selinux
sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config下载并配置163的yum源
cd /etc/yum.repos.d && mkdir bak && mv CentOS* bak && curl http://mirrors.163.com/.help/CentOS7-Base-163.repo >163.repo下载常用工具
yum clean all && yum makecache && yum install net-tools vim wget lrzsz -y
vim /etc/yum.repos.d/163.repo
在最下方插入如下配置
[mysql-innovation-community]
name=MySQL5.7 Release Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/
enabled=1
gpgcheck=0
yum repolist all | grep mysql
yum -y install mysql-community-server --nogpgcheck
systemctl start mysqld
systemctl status mysqld
grep 'temporary password' /var/log/mysqld.log
mysql -uroot -p
ALTER USER 'root'@'localhost' IDENTIFIED BY 'Password@123';
设置mysql_master root密码为Password@123 mysql_slave root密码为Password@1234
master节点设置
slave节点设置
systemctl restart mysqld
CREATE USER 'slave'@'%' IDENTIFIED BY 'Password@12345';grant replication slave ON *.* TO 'slave'@'%';
flush privileges;show master status; #这里要记住 file和position,后面配置slave会用到
stop slave; #关闭slave
change master to master_host='192.168.40.142', master_user='slave', master_password='Password@12345',master_log_file='mysql-bin.000001',master_log_pos=749;
start slave; #开启同步
从库查看
show slave status \G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
表示主从配置成功。
show slave status \G;命令输出如下:
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.40.142
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 749
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 749
Relay_Log_Space: 531
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: aa5b3240-696c-11ee-9ab7-000c293bd2c8
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)ERROR:
No query specifiedmysql>
create database test;
use test;
create table emp(
id int auto_increment comment 'ID' primary key ,
name varchar(10) not null comment '姓名',
age int comment '年龄',
job varchar(20) comment '职位',
salary int comment '薪资',
entrydate date comment '入职时间',
managerid int comment '直属领导id',
dept_id int comment '部门id'
) CHARSET=utf8 comment '员工表';insert into emp (id, name, age, job, salary, entrydate, managerid, dept_id)
values (1,'金庸',45,'总裁',50000,'2000-10-12',null,5),
(2,'杨逍',33,'开发',20000,'2005-10-21',1,1),
(3,'张无忌',30,'项目经理',30000,'2008-11-12',1,1);
从库查询
use test;
select * from emp;
验证OK,主从搭建完成。
百度网盘获取搭建视频及文档:
链接:https://pan.baidu.com/s/1lqkN8DelnBjYCogoHcmWdg
提取码:4xpe
参考博客:https://blog.csdn.net/qq_39871711/article/details/123494048