MySQL 高可用：主从配置或主主(双主)配置

最新推荐文章于 2024-08-06 10:01:49 发布
最新推荐文章于 2024-08-06 10:01:49 发布
阅读量9.4k 收藏 8
点赞数 1
分类专栏： mysql 文章标签： mysql

mysql 的主从复制可以实现mysql的多种高可用性，如数据库的读写分离 ，在线热备，负载均衡，数据分布 等。

 

mysql 主从同步原理：

1.  master 将操作记录到二进制日志(binary log)中；

2.  slave IO 线程 将master的binary log events读写到它的中继日志(relay log)；

3.  slave SQL进程读取中继日志，将重做记录数据到数据库中。

 

下面配置：【主从复制】

 

MySQL Version : 5.6.22
主库：centos152 / 192.168.1.152
从库：centos153 / 192.168.1.153

 

配置 mysql 文件：

【centos152 主库】
[root@centos152 ~]# vi /etc/my.cnf
[mysqld]
basedir =/usr/local/mysql
datadir =/usr/local/mysql/data
port = 3306
server_id = 1
log-bin= mysql-bin
binlog_format = mixed

read-only=0
#binlog-do-db=test
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
auto-increment-offset=1
auto-increment-increment=2

#添加后重启mysql服务
[root@centos152 ~]# service mysqld restart


【centos153 从库】
[root@centos153 ~]# vi /etc/my.cnf
[mysqld]
basedir =/usr/local/mysql
datadir =/usr/local/mysql/data
port = 3306
server_id = 2
log-bin= mysql-bin
binlog_format = mixed

read-only=0
#replicate-do-db=test
replicate-ignore-db=mysql
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
relay_log=mysql-relay-bin
log-slave-updates=on
#auto-increment-offset=2
#auto-increment-increment=2


#添加后重启mysql服务
[root@centos153 ~]# service mysqld restart

 

说明：

log-bin ：需要启用二进制日志
server_id : 用于标识不同的数据库服务器

binlog-do-db : 需要记录到二进制日志的数据库
binlog-ignore-db : 忽略记录二进制日志的数据库
auto-increment-offset :该服务器自增列的初始值。
auto-increment-increment :该服务器自增列增量。

replicate-do-db ：指定复制的数据库
replicate-ignore-db ：不复制的数据库
relay_log ：从库的中继日志，主库日志写到中继日志，中继日志再重做到从库。
log-slave-updates ：该从库是否写入二进制日志，如果需要成为多主则可启用。只读可以不需要。

如果为多主的话注意设置 auto-increment-offset 和 auto-increment-increment
如上面为双主的设置：
服务器 152 自增列显示为：1,3,5,7,……（offset=1，increment=2）
服务器 153 自增列显示为：2,4,6,8,……（offset=2，increment=2）

 

注意：文字图片来自《高性能 mysql》：

 

#在 [root@centos152 ~] 操作几笔数据后，数据有些变化。

 

[plain] view plain copy

  1. mysql> flush logs;  
  2. mysql> show master status;  
  3. +------------------+----------+--------------+---------------------------------------------+-------------------+  
  4. | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                            | Executed_Gtid_Set |  
  5. +------------------+----------+--------------+---------------------------------------------+-------------------+  
  6. | mysql-bin.000006 |      120 |              | mysql,information_schema,performance_schema |                   |  
  7. +------------------+----------+--------------+---------------------------------------------+-------------------+  


File :当前正在记录的二进制日志文件
Position ：记录偏移量，日志 mysql-bin.000006 所记录到的位置。
Binlog_Do_DB ：要记录日志的数据库
Binlog_Ignore_DB ：不记录日志的数据库
Executed_Gtid_Set ：已执行的事务ID

 

 

 

 

二进制日志情况：

 

[plain] view plain copy

  1. mysql> show variables like '%log_bin%';  
  2. +---------------------------------+---------------------------------------+  
  3. | Variable_name                   | Value                                 |  
  4. +---------------------------------+---------------------------------------+  
  5. | log_bin                         | ON                                    |  
  6. | log_bin_basename                | /usr/local/mysql/data/mysql-bin       |  
  7. | log_bin_index                   | /usr/local/mysql/data/mysql-bin.index |  
  8. | log_bin_trust_function_creators | OFF                                   |  
  9. | log_bin_use_v1_row_events       | OFF                                   |  
  10. | sql_log_bin                     | ON                                    |  
  11. +---------------------------------+---------------------------------------+  


日志位置：

 

 

[plain] view plain copy

  1. [root@centos152 ~]# ll /usr/local/mysql/data/mysql-bin*  
  2. -rw-rw---- 1 mysql mysql 701 10-15 20:30 /usr/local/mysql/data/mysql-bin.000001  
  3. -rw-rw---- 1 mysql mysql 167 10-15 20:58 /usr/local/mysql/data/mysql-bin.000002  
  4. -rw-rw---- 1 mysql mysql 167 10-15 21:02 /usr/local/mysql/data/mysql-bin.000003  
  5. -rw-rw---- 1 mysql mysql 167 10-15 21:02 /usr/local/mysql/data/mysql-bin.000004  
  6. -rw-rw---- 1 mysql mysql 581 10-18 22:42 /usr/local/mysql/data/mysql-bin.000005  
  7. -rw-rw---- 1 mysql mysql 120 10-18 22:43 /usr/local/mysql/data/mysql-bin.000006  
  8. -rw-rw---- 1 mysql mysql 114 10-18 22:43 /usr/local/mysql/data/mysql-bin.index  


#现在禁止操作主数据库！防止日志有变化，保证主从数据初始状态一致！

 

[plain] view plain copy

  1. mysql> flush tables with read lock;  


备份主库(执行以下一行脚本即可)

 

[plain] view plain copy

  1. [root@centos152 ~]# mysqldump -u root -p test -F > /tmp/test_backup_`date +%Y_%m_%d_%H_%M_%S`.sql  
  2. [root@centos152 ~]# mysqldump -u root -p --databases test --lock-all-tables --flush-logs> /tmp/test_backup_`date +%Y_%m_%d_%H_%M_%S`.sql  


查看当前日志记录的位置：

 

 

 

 

[plain] view plain copy

  1. mysql> show master status \G;  
  2. *************************** 1. row ***************************  
  3.              File: mysql-bin.000009  
  4.          Position: 120  
  5.      Binlog_Do_DB:   
  6.  Binlog_Ignore_DB: mysql,information_schema,performance_schema  
  7. Executed_Gtid_Set:   
  8. 1 row in set (0.00 sec)  


备份完后，可以解锁了

 

 

 

 

[plain] view plain copy

  1. mysql> unlock tables;  


在主服务器拷贝备份到从服务器：

 

 

 

 

[plain] view plain copy

  1. [root@centos152 ~]# scp -r /tmp/test_backup_2015_10_18_23_33_30.sql root@192.168.1.153:/tmp  
  2.   
  3. #不行则先关闭主从服务器防火墙  
  4. /etc/init.d/iptables status  
  5. /etc/init.d/iptables stop  


在从库中还原数据库：

 

 

 

 

[plain] view plain copy

  1. [root@centos153 ~]# mysql -u root -p test< /tmp/test_backup_2015_10_18_23_33_30.sql  


主库创建用于连接到本地的用户：

 

 

 

 

[plain] view plain copy

  1. [root@centos152 ~]# grant replication slave on *.* to 'repl_user'@'192.168.1.153' identified by 'slave@153';  


在从库中连接到主库：

 

 

 

 

[plain] view plain copy

  1. mysql>   
  2. CHANGE MASTER TO   
  3.        MASTER_HOST='192.168.1.152',   
  4.        MASTER_USER='repl_user',   
  5.        MASTER_PASSWORD='slave@153',   
  6.        MASTER_LOG_FILE='mysql-bin.000009',   
  7.        MASTER_LOG_POS=120;  

change master to master_host='139.21x.1x.xx', master_user='xxx', master_password='xxxx*',master_port=3306, master_log_file='mysql-bin.000005', master_log_pos=28387862;
重启从库mysql服务
[root@centos153 ~]# service mysqld restart

 

 

 

 

查看从库同步信息：

 

[plain] view plain copy

  1. mysql> show slave status \G;  
  2. *************************** 1. row ***************************  
  3.                Slave_IO_State: Waiting for master to send event  
  4.                   Master_Host: 192.168.1.152  
  5.                   Master_User: repl_user  
  6.                   Master_Port: 3306  
  7.                 Connect_Retry: 60  
  8.               Master_Log_File: mysql-bin.000009  
  9.           Read_Master_Log_Pos: 120  
  10.                Relay_Log_File: mysql-relay-bin.000003  
  11.                 Relay_Log_Pos: 283  
  12.         Relay_Master_Log_File: mysql-bin.000009  
  13.              Slave_IO_Running: Yes  
  14.             Slave_SQL_Running: Yes  
  15.               Replicate_Do_DB:   
  16.           Replicate_Ignore_DB: mysql,information_schema,performance_schema  


可以看到， IO读写线程 Slave_IO_Running 和 SQL重做线程Slave_SQL_Running 都为 yes，表示正常执行！

 

 

 

 

现在测试同步情况：

 

[plain] view plain copy

  1. #主库创建测试表  
  2. create table tabdemo(   
  3. id int primary key auto_increment,  
  4. value int default 0  
  5. ) auto_increment= 1 engine=innodb default charset=utf8;   
  6.   
  7.   
  8. insert into tabdemo(value) values(1),(1),(1),(1),(1);  
  9.   
  10.   
  11. select * from tabdemo;  
  12. +----+-------+  
  13. | id | value |  
  14. +----+-------+  
  15. |  1 |     1 |  
  16. |  3 |     1 |  
  17. |  5 |     1 |  
  18. |  7 |     1 |  
  19. |  9 |     1 |  
  20. +----+-------+  


主库操作，主库和从库都有数据了！id 编号与上面所说的一样！~现在主从复制已经配置成功！~

 

 

 

 

==========================================================================
==========================================================================

下面配置：【主主复制】

 

更改配置文件：

【centos152 原主库】更改配置文件
[root@centos152 ~]# vi /etc/my.cnf
[mysqld]
basedir =/usr/local/mysql
datadir =/usr/local/mysql/data
port = 3306
server_id = 1
log-bin= mysql-bin
binlog_format = mixed

read-only=0
#binlog-do-db=test
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
auto-increment-offset=1
auto-increment-increment=2

#主主复制的从库设置（新增）
#replicate-do-db=test
replicate-ignore-db=mysql
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
relay_log=mysql-relay-bin
log-slave-updates=on


【centos153 原从库】更改配置文件
[root@centos153 ~]# vi /etc/my.cnf
[mysqld]
basedir =/usr/local/mysql
datadir =/usr/local/mysql/data
port = 3306
server_id = 2
log-bin= mysql-bin
binlog_format = mixed

read-only=0
#replicate-do-db=test
replicate-ignore-db=mysql
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
relay_log=mysql-relay-bin
log-slave-updates=on

#主主复制的主库设置（新增）
#binlog-do-db=test
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
auto-increment-offset=2
auto-increment-increment=2


添加后都重启mysql服务：
[root@centos153 ~]# service mysqld restart

 

原从库(153)中创建用于连接的用户：

 

[plain] view plain copy

  1. [root@centos153 ~]# grant replication slave on *.* to 'repl_user'@'192.168.1.152' identified by 'slave@152';  


原从库(153)查看当前日志记录的位置：

 

 

 

 

[plain] view plain copy

  1. mysql> show master status \G;  
  2. *************************** 1. row ***************************  
  3.              File: mysql-bin.000005  
  4.          Position: 120  
  5.      Binlog_Do_DB:   
  6.  Binlog_Ignore_DB: mysql,information_schema,performance_schema  
  7. Executed_Gtid_Set:   
  8. 1 row in set (0.00 sec)  


在主库(152)中连接到原从库(153)，之前的从库现在也将是主库：

 

 

 

 

[plain] view plain copy

  1. mysql>   
  2. CHANGE MASTER TO   
  3.        MASTER_HOST='192.168.1.153',   
  4.        MASTER_USER='repl_user',   
  5.        MASTER_PASSWORD='slave@152',   
  6.        MASTER_LOG_FILE='mysql-bin.000005',   
  7.        MASTER_LOG_POS=120;  


重启从库(152)mysql服务：
[root@centos152 ~]# service mysqld restart

 

 

 

 

查看从库(152)的同步信息：

 

[plain] view plain copy

  1. mysql> show slave status \G;  
  2. *************************** 1. row ***************************  
  3.                Slave_IO_State: Waiting for master to send event  
  4.                   Master_Host: 192.168.1.153  
  5.                   Master_User: repl_user  
  6.                   Master_Port: 3306  
  7.                 Connect_Retry: 60  
  8.               Master_Log_File: mysql-bin.000005  
  9.           Read_Master_Log_Pos: 120  
  10.                Relay_Log_File: mysql-relay-bin.000003  
  11.                 Relay_Log_Pos: 283  
  12.         Relay_Master_Log_File: mysql-bin.000005  
  13.              Slave_IO_Running: Yes  
  14.             Slave_SQL_Running: Yes  
  15.               Replicate_Do_DB:   
  16.           Replicate_Ignore_DB: mysql,information_schema,performance_schema  

 

 


现在已经配置好双主了！现在相互在两个数据库中都操作数据：

 

[plain] view plain copy

  1. #当前在原来的从库(153)操作数据。  
  2.   
  3. insert into tabdemo(value) values(2),(2),(2),(2),(2);  
  4.   
  5. #数据同步正常了！  
  6. select * from tabdemo;  
  7. +----+-------+  
  8. | id | value |  
  9. +----+-------+  
  10. |  1 |     1 |  
  11. |  3 |     1 |  
  12. |  5 |     1 |  
  13. |  7 |     1 |  
  14. |  9 |     1 |  
  15. | 10 |     2 |  
  16. | 12 |     2 |  
  17. | 14 |     2 |  
  18. | 16 |     2 |  
  19. | 18 |     2 |  
  20. +----+-------+  
  21.   
  22. 在原来的主库(152)操作数据。  
  23. insert into tabdemo(value) values(3),(3),(3),(3),(3);  
  24.   
  25. #也正常！  
  26. select * from tabdemo;  
  27. +----+-------+  
  28. | id | value |  
  29. +----+-------+  
  30. |  1 |     1 |  
  31. |  3 |     1 |  
  32. |  5 |     1 |  
  33. |  7 |     1 |  
  34. |  9 |     1 |  
  35. | 10 |     2 |  
  36. | 12 |     2 |  
  37. | 14 |     2 |  
  38. | 16 |     2 |  
  39. | 18 |     2 |  
  40. | 19 |     3 |  
  41. | 21 |     3 |  
  42. | 23 |     3 |  
  43. | 25 |     3 |  
  44. | 27 |     3 |  
  45. +----+-------+ 
qin_weilong
关注
专栏目录
K8s（二十一）：在 Kubernetes 集群中部署 MySQL8.0 高可用集群（1主2从）
✨ 欢迎来到【Seal ^_^ 的CSDN博客】！这里是我记录技术心得、分享经验的地方。✨
04-16 1万+
🔴 K8s（二十一）：在 Kubernetes 集群中部署 MySQL8.0 高可用集群（1主2从）
MySQL高可用主从集群（一）——MySQL主从集群部署
爱新觉罗ZBC
02-20 1248
MySQL主从集群部署 ProxySQL中间件部署并实现MySQL主从集群代理和读写分离 Orchestrator集群部署并实现MySQL主从集群故障切换，保证集群的高可用性
MySQL主从配置高可用
xf827102的博客
12-04 323
数据库 两个mysql数据库同步 1、编写二进制日志文件 1.1主库配置 vim /etc/my.cnf 进入my.cnf文件后,添加配置 server-id=1 log-bin=mysql-bin 修改完后，保存退出，重启数据库，去MySQL目录下查询是否生成mysql-bin.index和mysql-bin.000001文件 副库与主库一样配置，server-id不一样即可 1.2配置主从关系 在主库输入，记录主库的file和Position SHOW MASTER STAT
MySQL主从
最新发布
2201_75912662的博客
08-06 737
在现代企业中，数据显得尤为重要，而存储数据的数据库选择又五花八门，但无论是何种数据库，均存在着一种隐患。想几个问题：用一台数据库存放数据，若此数据库服务器宕机了导致数据丢失怎么办？业务量大了，数据多了，访问的人多了，一台数据库无法保证服务质量了怎么办？1.主从作用实时灾备，用于故障切换读写分离，提供查询服务备份，避免影响业务2. 主从形式一主一从主主复制一主多从---扩展系统读取的性能，因为读是在从库读取的多主一从---5.7开始支持3.主从复制原理。
mysql 单机主从_《高可用MySQL》2 – 单机版MySQL主从配置
weixin_32379547的博客
01-27 134
这里搭建的主从是最简单的单一主从复制(即一个Master和一个slave，没有任何的热机备份和多slave)，该主从结构的基本拓扑图如下所示：建立基本的主从复制可以总结为以下三个基本步骤：配置master服务器配置slave服务器将slave连接到master在配置完成之后，我们会做一个简单的测试，测试复制是否正常。本文所涉及的master和slave服务器均位于同一台单机上，使用不同的端口号区分...
MySQL高可用配置
zjjcchina的博客
05-06 489
MySQL中存储着Hive所有表格的元数据信息，一旦MySQL中的数据丢失或损坏，会对整个数据仓库系统造成不可挽回的损失，为避免这种情况的发生，我们可以选择每天对元数据进行备份，进而实现MySQL HA（High Availability高可用）。 MySQL的HA方案不止一种，本文介绍较为常用的一种——基于keepalived的MySQL HA。 MySQL的HA离不开其主从复制的技术。主从复制是指一台服务器充当主数据库服务器（master），另一台或多台服务器充当从数据库服务器（slave），从服务
mysql:高可用配置
Ryougi_Shiki_uio的博客
07-20 257
MHA介绍 MHA（Master High Availability）目前在MySQL高可用方面是一个相对成熟的解决方案，MHA是由日本人开发，是一套优秀的MySQL故障切换和主从复制的高可用软件 在MySQL故障切换的过程中，MHA能做到0~30秒之内自动完成数据库的故障切换操作，并且在进行故障切换的过程中，MHA能够最大程度上保证数据库的一致性，以达到真正意义上的高可用 MHA由perl语言编写，该工具仅仅使用于MySQLReplication环境，目的在于维持Master主库的高可用性 MHA是基于标
CentOs7下mysql5.7的下载安装以及主从同步、双主多从配置详情
03-07
双主多从是指将两个 Mysql 服务器配置为主服务器，并将多个从服务器连接到这两个主服务器上，以实现高可用性和负载均衡。配置双主多从需要在 my.cnf 文件中添加以下内容： [mysqld] server-id = 1 log-bin = /var/...
mysql 数据库主从配置
07-26
mysql 数据库主从配置 mysql 数据库主从配置是指将一个 mysql 服务器设置为主服务器，另一个 mysql 服务器设置为从服务器，并...* 高可用性：mysql 主从配置可以用来提高数据库的可用性，以免出现数据库不可用的情况。
mysql双主配置
09-06
mysql双主配置操作说明
mysql 的高可用配置_MySQL高可用配置
weixin_36323390的博客
01-31 219
openstack高可用配置文档介绍利用两台物理主机实现高可用的openstack云平台，这里主要说明mysql和rabbitmq的高可用配置。l 安装openstack云平台(两台主机上都部署openstack I版)安装centos操作系统，由于以active-passive模式配置mysql等服务的高可用时，需要单独的硬盘分区来装载和同步mysql等服务的数据，所以安装系统时，在系统镜像配...
MySQL配置高可用
zhangyu_sing的博客
07-30 325
预实现主从复制 配置四台主机 172.25.41.1----主节点Server1 172.25.41.2----从节点Server2 172.25.41.3----从节点Server3 以上这三台主机做主从复制，这三台主机均可以作为主节点，以便之后可以实现节点切换 添加复制用户用户如下 CREATE USER ‘octopus’@‘172.25.41.%’ IDENTIFIED BY ‘Zhang...
MySQL数据库高可用安装配置
从入门到放弃
04-13 888
文章目录一、分析二、基础环境1、修改主机名及hosts2、修改系统进程打开最大文件数3、关闭防火墙及selinux三、MySQL1、卸载mariadb数据库2、依赖检查安装3、解压安装MySQL4、修改配置文件5、初始化并启动数据库6、修改密码及权限添加四、主主互备1、主机mysql1配置2、主机mysql2配置3、mysql1配置4、mysql2配置5、查看主主互备状态五、Keepalived1...
Mysql主主复制高可用解决方案
经典鸡翅
08-17 489
前言 最近做一个项目，项目考虑了一些风险，其中就有mysql宕机的风险，mysql是申请了两台服务器。于是打算搞个主主复制，用keepalived进行漂移实现高可用。 方案可行性 首先主主复制可以保证两台机器数据同步，keeplalived通过虚拟ip，保证我们的mysql可以快速切换。方案可行。 搭建步骤 首先我们准备好两台已经搭建好的mysql服务器。 ip分别为15.1.1.46，...
Mysql主从高可用
weixin_45615047的博客
12-14 279
Mysql主从高可用 服务器 角色 192.168.1.10 主 192.168.1.20 从 keepalived----vip 环境准备 Keepalived-2.0.13.tar.gz mysql-5.5.56-linux-glibc2.5-x86_64.tar.gz 依赖安装 yum -y install kernel-devel openssl-devel popt-devel yum install -y libaio 注：此部署中的高可用采用源码部署 部署（
MySQL 双主配置
Tongyao
07-31 411
同样，当Slave_IO_Running: Yes Slave_SQL_Running: Yes 都为yes表示同步成功。在130中创建数据库， 在132查看数据库生成了，在132的库中插入表 ，130中有数据表了。两台MySQL服务器双方Ping通，且3306端口telnet互通。1、操作基本相同，查看132 MySQL的状体信息。1、先查看130 MySQL的状体信息。2、在第二台机器（132）上操作。2、在第二台机器（130）上操作。1、登录MySQL后配置账户。2、查看MySQL用户信息。
MySQL双主配置
=死神=
09-28 267
MySQL 双主配置
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值