百度网站怎么申请注册,网站服务器ip地址怎么查,江苏住房和城乡建设部网站,wordpress社交平台主题MySQL 主从同步是一种数据库复制技术#xff0c;它通过将主服务器上的数据更改复制到一个或多个从服务器#xff0c;实现数据的自动同步。
主从同步的核心原理是将主服务器上的二进制日志复制到从服务器#xff0c;并在从服务器上执行这些日志中的操作。
MySQL主从同步是基…MySQL 主从同步是一种数据库复制技术它通过将主服务器上的数据更改复制到一个或多个从服务器实现数据的自动同步。
主从同步的核心原理是将主服务器上的二进制日志复制到从服务器并在从服务器上执行这些日志中的操作。
MySQL主从同步是基于Bin Log实现的而Bin Log记录的是原始SQL语句。
当主库数据发生变更时写入本地Bin Log文件
从库IO线程发起dump主库Bin Log文件的请求主库IO线程推送Bin Log文件到从库中从库IO线程把Bin Log内容写入本地的Relay Log文件中从库SQL线程读取Relay Log文件内容从库SQL线程重新执行一遍SQL语句 首先保持实验环境干净关闭防火墙 selinux克隆的机器是没有下载过MySQL的
准备克隆了三台机子ip分别为192.168.1.130 192.168.1.133 192.168.1.134
同时下载 yum install mysql-server -y选取ip为192.168.1.130作为主布机子
首先主库配置 vim /etc/my.cnfgrant replication slave on *.* to rep192.168.1.%;启动 systemctl enable --now mysqld.service在主库里授权一个用户 create user rep192.168.1.% identified with mysql_native_password by 123456;查看binlog文件名要用到 show master status;接着配置另外两个从库
vim /etc/my.cnf查看配置内容 #
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]#
# This group is read by the server
#
[mysqld]
server_id133#
# include all files from the config directory
#
!includedir /etc/my.cnf.d#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]#
# This group is read by the server
#
[mysqld]
server_id134
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d接着启动 systemctl enable --now mysqld.service 设置
change master to master_host192.168.1.130, master_userrep, master_log_filebinlog.000001, master_log_pos678;查看状态
show slave status \G*************************** 1. row ***************************Slave_IO_State: Waiting for source to send eventMaster_Host: 192.168.1.130Master_User: repMaster_Port: 3306Connect_Retry: 60Master_Log_File: binlog.000001Read_Master_Log_Pos: 678Relay_Log_File: localhost-relay-bin.000002Relay_Log_Pos: 323Relay_Master_Log_File: binlog.000001Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Master_Log_Pos: 678Relay_Log_Space: 537Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_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: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id: 130Master_UUID: 3928f497-d672-11ef-8e26-000c294d2ac6Master_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Replica has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set:Executed_Gtid_Set:Auto_Position: 0Replicate_Rewrite_DB:Channel_Name:Master_TLS_Version:Master_public_key_path:Get_master_public_key: 0Network_Namespace:
1 row in set, 1 warning (0.00 sec)连接成功 进行测试
在主库创建库 create database jj1;创建表
create table jj(id int);插入数据
insert into jj values(1),(2),(4);连个从库中查看
