天翼云ELB实现mysql双机高可用-天翼云开发者社区

天翼云本身有云数据库组件DRDS(分布式关系型数据库_价格详情_功能优势_解决方案 -天翼云 (ctyun.cn))和分布式关系数据库MYSQL(关系数据库MySQL版 CT-RDS_云数据库_数据库服务 - 天翼云 (ctyun.cn))等云产品可以满足使用，在天翼云上部署服务可以直接购买服务内容。

但是对于一些简单场景，小型厂商或者一些基础支撑服务，单独购买数据库服务的成本高略高，而可以使用ECS搭建一个mysql双机集群，实现主主模式的数据同步，并且使用天翼云免费的私网ELB搭建高可用集群。

Mysql + 私网ELB

10.0.0.21 mysql-1 master
10.0.0.22 mysql-2 backup

1.1 安装mysql

使用云主机安装mysql，并且配置主主复制模式
这里使用docker安装mysql并且启动.

配置文件路径：

master-1的my.cnf：

[mysqld]
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
datadir		= /var/lib/mysql
lower_case_table_names = 1
log-bin=/var/lib/mysql/mysql-bin
server-id=1     #backup这台设置2
binlog-ignore-db = mysql,information_schema       #忽略写入binlog日志的库
auto-increment-increment = 2             #字段变化增量值
auto-increment-offset = 1              #初始字段ID为1
slave-skip-errors = all                       #忽略所有复制产生的错误 
#log-error	= /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address	= 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

master-2的my.cnf：

[mysqld]
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
datadir		= /var/lib/mysql
lower_case_table_names = 1
log-bin=/var/lib/mysql/mysql-bin
server-id=2      #backup这台设置2
binlog-ignore-db = mysql,information_schema       #忽略写入binlog日志的库
auto-increment-increment = 2             #字段变化增量值
auto-increment-offset = 2              #初始字段ID为2
slave-skip-errors = all                       #忽略所有复制产生的错误 
#log-error	= /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address	= 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

注：master和backup只有server-id不同和 auto-increment-offset不同。

拉取docker镜像

docker pull mysql:8.0

在10.0.0.21和10.0.0.22都启动docker

docker run -p 3306:3306 --privileged=true --name mysql-master -v /opt/mysql/:/etc/mysql/ -v /opt/mysql/logs:/var/log/mysql -v /opt/mysql/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql:8.0

1.2 开始配置mysql的主主复制

mysql主主复制流程：

配置master-1的用户和权限赋值
配置master-2的用户和权限赋值
配置master-1和master-2的复制状态

配置主主模式：

进入mysql：

docker exec -it mysql-master bash 
mysql -uroot -P3306 -p123456

1.2.1 配置master-1的用户和权限赋值

root的是host是%的跳过

use mysql
ALTER user 'root'@'%' IDENTIFIED BY '123456' PASSWORD EXPIRE NEVER;
ALTER user 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
FLUSH PRIVILEGES;

创建slave用户并给slave1分配权限

create user 'slave'@'10.0.0.22' identified by '123456';
ALTER user 'slave'@'10.0.0.22' IDENTIFIED BY '123456' PASSWORD EXPIRE NEVER;
ALTER user 'slave'@'10.0.0.22' IDENTIFIED WITH mysql_native_password BY '123456';

赋权限

GRANT REPLICATION SLAVE ON *.* TO 'slave'@'10.0.0.22';

查看master状态

show master status;

记录一下，等会配置需要用到：

mysql> show master status \G
*************************** 1. row ***************************
             File: binlog.000002
         Position: 3597
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

1.2.2 配置master-2的用户和权限赋值

master-2的配置与master-1刚好相反，是从master-1获取数据

root的是host是%的跳过

ALTER user 'root'@'%' IDENTIFIED BY '123456' PASSWORD EXPIRE NEVER;
ALTER user 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
FLUSH PRIVILEGES;

创建slave用户并给slave1分配权限

create user 'slave'@'10.0.0.21' identified by '123456';
ALTER user 'slave'@'10.0.0.21' IDENTIFIED BY '123456' PASSWORD EXPIRE NEVER;
ALTER user 'slave'@'10.0.0.21' IDENTIFIED WITH mysql_native_password BY '123456';

赋权限

GRANT REPLICATION SLAVE ON *.* TO 'slave'@'10.0.0.21';

查看master状态

show master status;

记录一下，等会配置需要用到：

mysql> show master status \G
*************************** 1. row ***************************
             File: binlog.000003
         Position: 2165
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

1.2.3 配置master-1和master-2的主主复制

配置master-1:

登录master-1的数据库，注意：binlog.000003和2165是master2的（show master status;）信息

CHANGE MASTER TO MASTER_HOST='10.0.0.22',MASTER_USER='slave',MASTER_PASSWORD='123456',MASTER_PORT=3306,MASTER_LOG_FILE='binlog.000003',MASTER_LOG_POS=2165;

启动slave

start slave;

查看slave状态

show slave status\G;

配置master-2:

登录master-2的数据库，注意：binlog.000002和3597是master-1的（show master status;）信息

CHANGE MASTER TO MASTER_HOST='10.0.0.22',MASTER_USER='slave',MASTER_PASSWORD='123456',MASTER_PORT=3306,MASTER_LOG_FILE='binlog.000002',MASTER_LOG_POS=3597;

启动slave

start slave;

查看slave状态

show slave status \G

至此，mysql的主从状态就配置成功了

我们创建表格用来测试一下：

create table test(
 name character varying(36) NOT NULL,
 age integer
);
insert into test(name, age) values('ctyun', 10);

2. 使用ELB构建高可用集群

1. 创建私网ELB，私网ELB是免费开启的

2. 配置后端主机

3. 配置端口

4. 配置监听器

5. 选择后端主机

至此，一个mysql双机高可用集群完成。可以通过telnet验证一下通过ELB的私网IP访问数据库

telnet有流量之后可以看到ELB页面健康检查为正常

Mysql + 私网ELB

10.0.0.21 mysql-1 master
10.0.0.22 mysql-2 backup

1.1 安装mysql

使用云主机安装mysql，并且配置主主复制模式
这里使用docker安装mysql并且启动.

配置文件路径：

master-1的my.cnf：

[mysqld]
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
datadir		= /var/lib/mysql
lower_case_table_names = 1
log-bin=/var/lib/mysql/mysql-bin
server-id=1     #backup这台设置2
binlog-ignore-db = mysql,information_schema       #忽略写入binlog日志的库
auto-increment-increment = 2             #字段变化增量值
auto-increment-offset = 1              #初始字段ID为1
slave-skip-errors = all                       #忽略所有复制产生的错误 
#log-error	= /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address	= 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

master-2的my.cnf：

[mysqld]
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
datadir		= /var/lib/mysql
lower_case_table_names = 1
log-bin=/var/lib/mysql/mysql-bin
server-id=2      #backup这台设置2
binlog-ignore-db = mysql,information_schema       #忽略写入binlog日志的库
auto-increment-increment = 2             #字段变化增量值
auto-increment-offset = 2              #初始字段ID为2
slave-skip-errors = all                       #忽略所有复制产生的错误 
#log-error	= /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address	= 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

注：master和backup只有server-id不同和 auto-increment-offset不同。

拉取docker镜像

docker pull mysql:8.0

在10.0.0.21和10.0.0.22都启动docker

docker run -p 3306:3306 --privileged=true --name mysql-master -v /opt/mysql/:/etc/mysql/ -v /opt/mysql/logs:/var/log/mysql -v /opt/mysql/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql:8.0

1.2 开始配置mysql的主主复制

mysql主主复制流程：

配置master-1的用户和权限赋值
配置master-2的用户和权限赋值
配置master-1和master-2的复制状态

配置主主模式：

进入mysql：

docker exec -it mysql-master bash 
mysql -uroot -P3306 -p123456

1.2.1 配置master-1的用户和权限赋值

root的是host是%的跳过

use mysql
ALTER user 'root'@'%' IDENTIFIED BY '123456' PASSWORD EXPIRE NEVER;
ALTER user 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
FLUSH PRIVILEGES;

创建slave用户并给slave1分配权限

create user 'slave'@'10.0.0.22' identified by '123456';
ALTER user 'slave'@'10.0.0.22' IDENTIFIED BY '123456' PASSWORD EXPIRE NEVER;
ALTER user 'slave'@'10.0.0.22' IDENTIFIED WITH mysql_native_password BY '123456';

赋权限

GRANT REPLICATION SLAVE ON *.* TO 'slave'@'10.0.0.22';

查看master状态

show master status;

记录一下，等会配置需要用到：

mysql> show master status \G
*************************** 1. row ***************************
             File: binlog.000002
         Position: 3597
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

1.2.2 配置master-2的用户和权限赋值

master-2的配置与master-1刚好相反，是从master-1获取数据

root的是host是%的跳过

ALTER user 'root'@'%' IDENTIFIED BY '123456' PASSWORD EXPIRE NEVER;
ALTER user 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
FLUSH PRIVILEGES;

创建slave用户并给slave1分配权限

create user 'slave'@'10.0.0.21' identified by '123456';
ALTER user 'slave'@'10.0.0.21' IDENTIFIED BY '123456' PASSWORD EXPIRE NEVER;
ALTER user 'slave'@'10.0.0.21' IDENTIFIED WITH mysql_native_password BY '123456';

赋权限

GRANT REPLICATION SLAVE ON *.* TO 'slave'@'10.0.0.21';

查看master状态

show master status;

记录一下，等会配置需要用到：

mysql> show master status \G
*************************** 1. row ***************************
             File: binlog.000003
         Position: 2165
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

1.2.3 配置master-1和master-2的主主复制

配置master-1:

登录master-1的数据库，注意：binlog.000003和2165是master2的（show master status;）信息

CHANGE MASTER TO MASTER_HOST='10.0.0.22',MASTER_USER='slave',MASTER_PASSWORD='123456',MASTER_PORT=3306,MASTER_LOG_FILE='binlog.000003',MASTER_LOG_POS=2165;

启动slave

start slave;

查看slave状态

show slave status\G;

配置master-2:

登录master-2的数据库，注意：binlog.000002和3597是master-1的（show master status;）信息

CHANGE MASTER TO MASTER_HOST='10.0.0.22',MASTER_USER='slave',MASTER_PASSWORD='123456',MASTER_PORT=3306,MASTER_LOG_FILE='binlog.000002',MASTER_LOG_POS=3597;

启动slave

start slave;

查看slave状态

show slave status \G

至此，mysql的主从状态就配置成功了

我们创建表格用来测试一下：

create table test(
 name character varying(36) NOT NULL,
 age integer
);
insert into test(name, age) values('ctyun', 10);

2. 使用ELB构建高可用集群

1. 创建私网ELB，私网ELB是免费开启的

2. 配置后端主机

3. 配置端口

4. 配置监听器

5. 选择后端主机

至此，一个mysql双机高可用集群完成。可以通过telnet验证一下通过ELB的私网IP访问数据库

telnet有流量之后可以看到ELB页面健康检查为正常

应用商城

合作伙伴

开发者

支持与服务

了解天翼云

天翼云ELB实现mysql双机高可用

1.1 安装mysql

1.2 开始配置mysql的主主复制

1.2.1 配置master-1的用户和权限赋值

1.2.2 配置master-2的用户和权限赋值

1.2.3 配置master-1和master-2的主主复制

配置master-1:

配置master-2:

2. 使用ELB构建高可用集群

天翼云ELB实现mysql双机高可用

1.1 安装mysql

1.2 开始配置mysql的主主复制

1.2.1 配置master-1的用户和权限赋值

1.2.2 配置master-2的用户和权限赋值

1.2.3 配置master-1和master-2的主主复制

配置master-1:

配置master-2:

2. 使用ELB构建高可用集群

活动

应用商城

合作伙伴

开发者

支持与服务

了解天翼云

天翼云ELB实现mysql双机高可用

1.1 安装mysql

1.2 开始配置mysql的主主复制

1.2.1 配置master-1的用户和权限赋值

1.2.2 配置master-2的用户和权限赋值

1.2.3 配置master-1和master-2的主主复制

配置master-1:

配置master-2:

2. 使用ELB构建高可用集群

天翼云ELB实现mysql双机高可用

1.1 安装mysql

1.2 开始配置mysql的主主复制

1.2.1 配置master-1的用户和权限赋值

1.2.2 配置master-2的用户和权限赋值

1.2.3 配置master-1和master-2的主主复制

配置master-1:

配置master-2:

2. 使用ELB构建高可用集群