MySQL主主同步
发表于:2024-10-23 作者:千家信息网编辑
千家信息网最后更新 2024年10月23日,MySQL主主同步主主同步原理同主从,不过是双向而已1.修改1.4配置文件vim /etc/my.cnf#同时开始binlog和relay loglog-bin=mysql-binbinlog_for
千家信息网最后更新 2024年10月23日MySQL主主同步
MySQL主主同步
主主同步原理同主从,不过是双向而已
1.修改1.4配置文件
vim /etc/my.cnf#同时开始binlog和relay loglog-bin=mysql-binbinlog_format=mixedserver-id = 1sync_master_info = 1sync_binlog = 1 innodb_support_xa = ONrelay_log= relay-logrelay_log_index=relay-log.indexskip_slave_start = ONsync_relay_log = 1sync_relay_log_info = 1#定义偏移量auto_increment_offset=1auto_increment_increment=2
2.查看1.4日志POS点
MariaDB [(none)]> show master status\G*************************** 1. row *************************** File: mysql-bin.000002 Position: 662 Binlog_Do_DB: Binlog_Ignore_DB: 1 row in set (0.00 sec)
3.修改1.5配置文件
vim /etc/my.cnf#同时开始binlog和relay loglog-bin=mysql-binbinlog_format=mixedserver-id = 3sync_master_info = 1sync_binlog = 1innodb_support_xa = ONrelay_log= relay-logrelay_log_index=relay-log.indexskip_slave_start = ONsync_relay_log = 1sync_relay_log_info = 1#定义偏移量auto_increment_offset = 2
4.查看1.5日志POS点
MariaDB [mysql]> show master status\G*************************** 1. row *************************** File: mysql-bin.000001 Position: 1073 Binlog_Do_DB: Binlog_Ignore_DB: 1 row in set (0.00 sec)
5.在1.4上添加复制用户
MariaDB [(none)]> grant replication slave,replication client on *.* to 'repa'@'192.168.1.5' identified by 'slavepass'; Query OK, 0 rows affected (0.09 sec)
6.在1.5上添加复制用户
MariaDB [mysql]> grant replication slave,replication client on *.* to 'repb'@'192.168.1.4' identified by 'slavepass'; Query OK, 0 rows affected (0.07 sec)
7.在1.4上开始同步复制
change master to master_host='192.168.1.5',master_user='repb',master_password='slavepass',master_log_file='mysql-bin.000001',master_log_pos=1073;
8.在1.5上开始同步复制
change master to master_host='192.168.1.4',master_user='repa',master_password='slavepass',master_log_file='mysql-bin.000002',master_log_pos=662;
9.查看1.4同步状态
MariaDB [(none)]> show slave status\G*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.5 Master_User: repb Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 1073 Relay_Log_File: relay-log.000002 Relay_Log_Pos: 537 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: 1073 Relay_Log_Space: 829 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: 0Master_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: 3 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: conservative1 row in set (0.00 sec)
10.查看1.5同步状态
MariaDB [mysql]> show slave status\G*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.4 Master_User: repa Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 662 Relay_Log_File: relay-log.000002 Relay_Log_Pos: 537 Relay_Master_Log_File: mysql-bin.000002 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: 662 Relay_Log_Space: 829 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: 0Master_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_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: conservative1 row in set (0.00 sec)
11.在1.4上创建数据库及表
MariaDB [(none)]> create database prince;Query OK, 1 row affected (0.09 sec)MariaDB [(none)]> use prince;Database changedMariaDB [prince]> drop table Threek;Query OK, 0 rows affected (0.13 sec)
创建表:
MariaDB [prince]> create table Threek(id int auto_increment primary key,name varchar(50));Query OK, 0 rows affected (0.26 sec)
插入数据
MariaDB [prince]> insert into Threek (name) values('wukaka'),('ckl'),('love');Query OK, 3 rows affected (0.06 sec)Records: 3 Duplicates: 0 Warnings: 0
MariaDB [prince]> select * from Threek;+----+--------+| id | name |+----+--------+| 1 | wukaka || 3 | ckl || 5 | love |+----+--------+3 rows in set (0.00 sec)
12.在1.5上插入数据
MariaDB [(none)]> use prince;Database changed
查看表:
MariaDB [prince]> show tables;+------------------+| Tables_in_prince |+------------------+| Threek |+------------------+1 row in set (0.00 sec)
查看数据
MariaDB [prince]> select * from Threek;+----+--------+| id | name |+----+--------+| 1 | wukaka || 3 | ckl || 5 | love |+----+--------+3 rows in set (0.00 sec)
插入数据:
MariaDB [prince]> insert into Threek (name) values('stack'),('ayia'),('snow');Query OK, 3 rows affected (0.06 sec)Records: 3 Duplicates: 0 Warnings: 0MariaDB [prince]> select * from Threek; +----+--------+| id | name |+----+--------+| 1 | wukaka || 3 | ckl || 5 | love || 6 | stack || 7 | ayia || 8 | snow |+----+--------+6 rows in set (0.00 sec)
13.在1.4上查看数据
MariaDB [prince]> select * from Threek;+----+--------+| id | name |+----+--------+| 1 | wukaka || 3 | ckl || 5 | love || 6 | stack || 7 | ayia || 8 | snow |+----+--------+6 rows in set (0.00 sec)
同步
数据
同时
文件
日志
状态
用户
偏移
配置
主从
原理
双向
数据库
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
需求分析占软件开发的比例
杨浦区市场软件开发程序
小鸟云服务器可以等路bsoo
vb.net 添加数据库
浙商银行 软件开发怎么样
维普科技期刊全文数据库
网络技术在企业管理中的应用
环球期货软件开发
用数据库做服务器
服务器坏了怎么找回
完整的软件开发流程好处
互联网科技物联网微信群
案例分析计算机网络安全的应用
今年网络安全开了哪些会议
河北曙光服务器维修维保云主机
网络安全会议2017杭州
高中信息技术教材数据库
数据库安装路径忘了怎么办
邹平物流竞价软件开发报价
江苏嵌入式软件开发正规平台
黑游戏服务器教程
网络安全国防建设
唯壹软件开发
幻塔如何解决无法连接服务器
网络安全法高中生手抄报大全
做网络技术的是不是程序员
软件开发过程题库
网络安全大赛的好处
江苏联想服务器厂商
软件开发代码是什么意思