MTSQL主主同步方案
** MySQL主主+Keepalived
**MySQL+DRBD+Heartbeat
在企业中,数据库高可用一直是企业的重中之重,中小企业很多都是使用mysql主主方案,一主多从,读写分离等,但是单主存在单点故障,从库切换成主库需要作改动。因此,如果是双主或者多主,就会增加mysql入口,增加高可用。不过多主需要考虑自增长ID问题,这个需要特别设置配置文件,比如双主,可以使用奇偶,总之,主之间设置自增长ID相互不冲突就能完美解决自增长ID冲突问题。
主主方案实现思路
1、 两台mysql都可读写,互为主备。默认只使用一台masterA负责数据的写入,另一台masterB备用处于备用状态;
2、 masterA是masterB的主库,masterB又是masterA的主库,它们互为主从;
3、 两台主库之间做高可用,可以采用keepalived等方案,使用VIP对外提供服务;
4、所有提供服务的从服务器与masterB进行主从同步(双主多从);
5、建议采用高可用策略的时候,masterA或masterB均不因宕机恢复后而抢占VIP(非抢占模式);
这样做可以在一定程度上保证主库的高可用,在一台主库down掉之后,可以在极短的时间内切换到另一台主库上,尽可能减少主库宕机对业务造成的影响,减少了主从同步给生产主库带来的压力;
但是也有几个不足的地方:
- masterB可能会一直处于空闲状态(可以用它当从库,负责部分查询);
- 主库后面提供服务的从库要等masterB先同步完了数据后才能去masterB上去同步数据,这样可能会造成一定程度的同步延时;
设备两台MARIADB机器:
masterA:IP为192.168.200.124
masterB:IP为192.168.200.125
masterA:
[root@localhost ~]# yum install mariadb mariadb-server -y
[root@localhost ~]#systemc restart mariadb
1:修改主配置文件
[root@localhost ~]#vim /etc/my.cnf
server-id=
log-bin=mysql-binlog #打开二进制功能
log-slave-updates=true
max_binlog_size=1024M #binlog单文件最大值
auto_increment_offset =
auto_increment_increment = #奇数ID replicate-ignore-db = information_schema #忽略不同步主从的数据库
replicate-ignore-db = performance_schema
replicate-ignore-db = test
replicate-ignore-db = mysql max_connections =
max_connect_errors = skip-character-set-client-handshake #忽略应用程序想要设置的其他字符集
init-connect='SET NAMES utf8' #连接时执行的SQL
character-set-server=utf8 #服务端默认字符集
wait_timeout= #请求的最大连接时间
interactive_timeout= #和上一参数同时修改才会生效
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES #sql模式 relay-log=relay-log-bin #开启中继日志
relay-log-index=slave-relay-bin.index
MariaDB [(none)]> grant replication slave on *.* to 'repl'@'192.168.200.125' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show master status;
+---------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------------+----------+--------------+------------------+
| mysql-binlog.000001 | 911 | | |
+---------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
2:测试环境,可以保证没数据写入。否则需要的步骤是:先masterA锁表-->masterA备份数据-->masterA解锁表 -->masterB导入数据-->masterB设置主从-->查看主从
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
MariaDB [(none)]> change master to master_host='192.168.200.125',master_port=3306,master_user='repl',master_password='123456',master_log_file='mysql-binlog.000004',master_log_pos=670;
Query OK, 0 rows affected (0.04 sec)
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.200.125
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-binlog.000004
Read_Master_Log_Pos: 670
Relay_Log_File: relay-log-bin.000002
Relay_Log_Pos: 624
Relay_Master_Log_File: mysql-binlog.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: information_schema,performance_schema,test,mysql
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: 670
Relay_Log_Space: 916
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: 2
1 row in set (0.00 sec)
ERROR: No query specified
masterB:
[root@localhost ~]# yum install mariadb mariadb-server -y
[root@localhost ~]#systemc restart mariadb
1:修改主配置文件
[root@localhost ~]#vim /etc/my.cnf
server-id =
log-bin=mysql-binlog
log-slave-updates=true
max_binlog_size=1024M
auto_increment_offset =
auto_increment_increment = #偶数ID replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
replicate-ignore-db = test
replicate-ignore-db = mysql max_connections =
max_connect_errors = skip-character-set-client-handshake
init-connect='SET NAMES utf8'
character-set-server=utf8
wait_timeout=
interactive_timeout=
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES relay-log=relay-log-bin
relay-log-index=slave-relay-bin.index
MariaDB [(none)]> grant replication slave on *.* to 'repl'@'192.168.200.124' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show master status;
+---------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------------+----------+--------------+------------------+
| mysql-binlog.000004 | 670 | | |
+---------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
2:测试环境,可以保证没数据写入。否则需要的步骤是:先masterA锁表-->masterA备份数据-->masterA解锁表 -->masterB导入数据-->masterB设置主从-->查看主从
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> change master to master_host='192.168.200.111',master_port=3306,master_user='repl',master_password='123456',master_log_file='mysql-binlog.000001',master_log_pos=911;
Query OK, 0 rows affected (0.13 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.200.124
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-binlog.000001
Read_Master_Log_Pos: 911
Relay_Log_File: relay-log-bin.000002
Relay_Log_Pos: 624
Relay_Master_Log_File: mysql-binlog.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: information_schema,performance_schema,test,mysql
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: 911
Relay_Log_Space: 916
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
1 row in set (0.00 sec)
ERROR: No query specified
masterA测试:
MariaDB [(none)]> create database test01;
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| client |
| cmx |
| db_test |
| liuxiang |
| mydb |
| mysql |
| performance_schema |
| shiqi |
| shiqishou |
| shuifei |
| test01 |
| test02 |
| var |
| yg |
| yqx |
+--------------------+
16 rows in set (0.00 sec)
masterB测试:
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| cmx |
| db_test |
| liuxiang |
| mysql |
| performance_schema |
| shiqi |
| shiqishou |
| test01 |
| yqx |
+--------------------+
11 rows in set (0.00 sec)
MariaDB [(none)]> create database test02;
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| cmx |
| db_test |
| liuxiang |
| mysql |
| performance_schema |
| shiqi |
| shiqishou |
| test01 |
| test02 |
| yqx |
MYSQL主主高可用方案:
masterA:
[root@localhost ~]# yum -y install keepalived
[root@localhost ~]# vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived global_defs { router_id LVS_MASTER-A } vrrp_script mysql { script "/opt/mysql.sh" interval weight - fall rise } vrrp_instance VI_1 { state BACKUP interface ens33 virtual_router_id priority nopreempt advert_int authentication { auth_type PASS auth_pass } track_script { mysql } virtual_ipaddress { 192.168.200.254 } }
[root@localhost ~]# vim /opt/mysql.sh
#!/bin/bash counter=$(netstat -na|grep "LISTEN"|grep ""|wc -l) if [ "${counter}" -eq ]; then systemctl stop keepalived fi
[root@localhost ~]# chmod +x /opt/mysql.sh
[root@localhost ~]# systemctl start keepalived
[root@localhost ~]# ip a | grep ens33
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
inet 192.168.200.124/24 brd 192.168.200.255 scope global ens33
inet 192.168.200.254/32 scope global ens33
masterB:
[root@localhost ~]# yum -y install keepalived
[root@localhost ~]# vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived global_defs { router_id LVS_MASTER-B } vrrp_script mysql { script "/opt/mysql.sh" interval weight - fall rise } vrrp_instance VI_1 { state BACKUP interface ens33 virtual_router_id priority advert_int authentication { auth_type PASS auth_pass } track_script { mysql } virtual_ipaddress { 192.168.200.254 } }
[root@localhost ~]# vim /opt/mysql.sh
#!/bin/bash counter=$(netstat -na|grep "LISTEN"|grep ""|wc -l) if [ "${counter}" -eq ]; then systemctl stop keepalived fi
[root@localhost ~]# chmod +x /opt/mysql.sh
[root@localhost ~]# systemctl start keepalived
[root@localhost ~]# ip a | grep ens33
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
inet 192.168.200.125/24 brd 192.168.200.255 scope global ens33
inet 192.168.200.254/32 scope global ens33
测试VIP的漂移:
masterA:
[root@localhost ~]# systemctl stop mariadb
[root@localhost ~]# ip a | grep ens33
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
inet 192.168.200.124/24 brd 192.168.200.255 scope global ens33
masterB:
[root@localhost ~]# ip a | grep ens33
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
inet 192.168.200.125/24 brd 192.168.200.255 scope global ens33
inet 192.168.200.254/32 scope global ens33
远程客户端测试:
mysql服务器授权
[root@localhost ~]#systemctl start mariadb
[root@localhost ~]# mysql -uroot -p123123
MariaDB [(none)]> grant all on *.* to 'root'@'192.168.200.%' identified by '123456'; #授权200网段的客户机通过密码登入mysql
MariaDB [(none)]> flush privileges; #刷新授权列表
通过vip登陆测试:masterA与masterB都需要关闭防火墙
[root@localhost ~]# systemctl stop firewalld
[root@localhost ~]# iptables -F
[root@localhost ~]# setenforce 0
[root@localhost ~]# mysql -uroot -p123456 -h 192.168.200.254 #通过授权用户及密码登录Mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 5796
Server version: 5.5.56-MariaDB MariaDB Server
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| test01 |
| test02 |
+--------------------+
6 rows in set (0.00 sec)
最新文章
- 推荐21款最佳 HTML5 网页游戏
- saltstsck执行sls配置
- Spring4 与 Hibernate4 整合过程中的问题记录
- OC和Java的比较
- HDU 4386
- 基于Ubuntu 15.04 LTS编译Android5.1.0源代码 (转)
- Eratosthenes筛选法
- JS中escape 方法和C#中的对应
- javascript 倒计时天、小时、分钟、描述的倒计时代码
- 开发一个Swing功能时的一点总结
- 一个例子理解break和continue的区别
- PHPStorm 最新版 去掉参数提示 parameter name hints
- 【python学习笔记】2.列表和元组
- 怎么给PDF去除页眉页脚
- mybatis使用oracle的nulls first/nulls last
- vscode断点调试简单的服务端文件
- js异步编程终级解决方案 async/await
- Dot &; cross product
- 如何使用C语言的面向对象
- ubuntu上安装mysql的正确步骤
热门文章
- linux异常 - 网卡故障
- C#获取当前不同网卡对应的iP
- 解决 jmeter 压测Non HTTP response code: java.net.NoRouteToHostException/Non HTTP response message: Cannot assign requested address (Address not available)
- Leetcode 面试题 01.01. 判定字符是否唯一
- 一个仿tp5分页组件的实现
- 如何查看mac多少位的操作系统?
- 需要再次删除清空部署才能用rancher部署成功的是docker有问题
- deepin linux 安装之后 引导错误 出现 grub>;
- Js选择器总结
- 【visio】数据可视化 - 链接到外部数据