docker+nginx负载均衡+mysql双机互为主从实现mysql数据库高可用

系统：centos7

1、安装docker

yum install docker-io

/bin/systemctl start docker.service

2、安装nginx

yum -y install zlib zlib-devel openssl openssl--devel pcre pcre-devel

wget https://github.com/acassen/keepalived/archive/v1.3.5.tar.gz

tar -xvf release-1.13.5.tar.gz

cd nginx-release-1.13.5

./auto/configure --with-stream

make

make install

配置/usr/local/nginx/conf/nginx.conf

events
{
worker_connections 1024;
}
stream
{
upstream cloudsocket {
hash $remote_addr consistent;
server 127.0.0.1:6600 max_fails=3 fail_timeout=30s;
server 127.0.0.1:6601 max_fails=3 fail_timeout=30s;
}

server {
listen 3308;
proxy_connect_timeout 30s;
proxy_timeout 120s;
proxy_pass cloudsocket;
}
}

运行nginx

/usr/local/nginx/sbin/nginx -c /usr/local/nginx/conf/nginx.conf

3、导入docker镜像

docker run --name mysql1 -p 6600:3306 -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:5.6.35

docker run --name mysql2 -p 6601:3306 -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:5.6.35

4、配置mysql容器

mysql1:

1）进入mysql1容器

docker exec -it mysql1 bash

2）修改my.cnf

[mysqld]
server_id = 1
log-bin= mysql-bin

3）进入mysq1

mysql -uroot -pmy-secret-pw

4）提升远程访问权限

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'my-secret-pw' WITH GRANT OPTION;
flush privileges;

5）设置主从关系

CHANGE MASTER TO
MASTER_HOST = '192.168.0.1',
MASTER_PORT = 6601,
MASTER_USER = 'root',
MASTER_PASSWORD = 'my-secret-pw',
MASTER_LOG_FILE = 'mysql-bin.000001',

MASTER_LOG_POS = 120;

6）开启同步

START SLAVE;

7）验证正确性

SHOW SLAVE STATUS;
SHOW MASTER STATUS;

8）退出mysql

quit

9）退出mysql2容器

exit

mysql2:

1）进入mysql1容器

docker exec -it mysql2 bash

2）修改my.cnf

[mysqld]
server_id = 2
log-bin= mysql-bin

3）进入mysql

mysql -uroot -pmy-secret-pw

4）提升远程访问权限

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'my-secret-pw' WITH GRANT OPTION;

flush privileges;