mysql 5.7 nginx负载+mysql双主实现

一台不够再加一台
nginx配置
- - 解压
  - 执行configuration
- 启动nginx
  - 配置nginx代理数据库
  - 我的nginx.conf文件

一台不够再加一台

现在先写个一台nginx的，后续修改为热nginx

准备工作

mysql 5.7 安装包

mysql-5.7.31-linux-glibc2.12-x86_64.tar

 # 百度云：不推荐，学习下载安装工具，肯定是选找新的来，我是迫于无奈，公司主用的是5.7的
https://pan.baidu.com/s/1zIDvIEouGQSpAfPFR8hSnQ 
1234 
# 推荐清华镜像站，感谢清华，虽说拒绝了去读书，但是依然为我这个废柴提供了镜像服务
https://mirrors.tuna.tsinghua.edu.cn/mysql/downloads/MySQL-5.7/

三台服务器

 nginx：172.165.165.121
mysqlA：172.165.165.131
mysqlB：172.165.165.132

nginx安装：
数据库安装：https://blog.csdn.net/qq_37809967/article/details/109396043

mysql配置文件

mysql A 配置文件 /etc/my.cnf

 [client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
# 跳过登录密码校验，用于重置密码时使用
# skip-grant-tables
 
user=root
 
basedir = /usr/local/mysql
 
port = 3306
 
socket = /tmp/mysql.sock
 
datadir = /data/mysql
 
pid-file = /data/mysql/mysql.pid
 
log-error = /data/mysql/mysql.err
 
 
server-id = 1
 
auto_increment_offset = 1
 
auto_increment_increment = 2                                            #奇数ID
 
 
log-bin = mysql-bin                                                     #打开二进制功能,MASTER主服务器必须打开此项
 
binlog-format=ROW
 
log-slave-updates=true
 
gtid-mode=on
 
enforce-gtid-consistency=true
 
master-info-repository=TABLE
 
relay-log-info-repository=TABLE
 
sync-master-info=1
 
slave-parallel-workers=0
 
sync_binlog=0
 
binlog-checksum=CRC32
 
master-verify-checksum=1
 
slave-sql-verify-checksum=1
 
binlog-rows-query-log_events=1
 
 
expire_logs_days=5
 
max_binlog_size=1024M                                                   #binlog单文件最大值
 
 
replicate-ignore-db = mysql                                             #忽略不同步主从的数据库
 
replicate-ignore-db = information_schema
 
replicate-ignore-db = performance_schema
 
replicate-ignore-db = test
 
replicate-ignore-db = zabbix
 
 
max_connections = 3000
 
max_connect_errors = 30
 
explicit_defaults_for_timestamp=true
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
 
skip-character-set-client-handshake                                     #忽略应用程序想要设置的其他字符集
 
init-connect='SET NAMES utf8'                                           #连接时执行的SQL
 
character-set-server=utf8                                               #服务端默认字符集
 
wait_timeout=1800                                                       #请求的最大连接时间
 
interactive_timeout=1800                                                #和上一参数同时修改才会生效
 
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES                     #sql模式
 
max_allowed_packet = 10M
 
bulk_insert_buffer_size = 8M
 
query_cache_type = 1
 
query_cache_size = 128M
 
query_cache_limit = 4M
 
key_buffer_size = 256M
 
read_buffer_size = 16K
 
 
skip-name-resolve
 
slow_query_log=1
 
long_query_time = 6
 
slow_query_log_file=slow-query.log
 
innodb_flush_log_at_trx_commit = 2
 
innodb_log_buffer_size = 16M
 
 
[mysql]
 
no-auto-rehash
 
 
[myisamchk]
 
key_buffer_size = 20M
 
sort_buffer_size = 20M
 
read_buffer = 2M
 
write_buffer = 2M
 
 
[mysqlhotcopy]
 
interactive-timeout
 
 
[mysqldump]
 
quick
 
max_allowed_packet = 16M
 
 
[mysqld_safe]

mysql B 配置文件 /etc/my.cnf

 [client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
 
# 跳过登录密码校验，用于重置密码时使用
# skip-grant-tables
 
user=root
 
basedir = /usr/local/mysql
 
port = 3306
 
socket = /tmp/mysql.sock
 
datadir = /data/mysql
 
pid-file = /data/mysql/mysql.pid
 
log-error = /data/mysql/mysql.err
 
server-id = 2
 
auto_increment_offset = 2
 
auto_increment_increment = 2                                            #奇数ID
 
 
log-bin = mysql-bin                                                     #打开二进制功能,MASTER主服务器必须打开此项
 
binlog-format=ROW
 
log-slave-updates=true
 
gtid-mode=on
 
enforce-gtid-consistency=true
 
master-info-repository=TABLE
 
relay-log-info-repository=TABLE
 
sync-master-info=1
 
slave-parallel-workers=0
 
sync_binlog=0
 
binlog-checksum=CRC32
 
master-verify-checksum=1
 
slave-sql-verify-checksum=1
 
binlog-rows-query-log_events=1
 
 
expire_logs_days=5
 
max_binlog_size=1024M                                                   #binlog单文件最大值
 
 
replicate-ignore-db = mysql                                             #忽略不同步主从的数据库
 
replicate-ignore-db = information_schema
 
replicate-ignore-db = performance_schema
 
replicate-ignore-db = test
 
replicate-ignore-db = zabbix
 
 
max_connections = 3000
 
max_connect_errors = 30
 
explicit_defaults_for_timestamp=true
 
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
 
skip-character-set-client-handshake                                     #忽略应用程序想要设置的其他字符集
 
init-connect='SET NAMES utf8'                                           #连接时执行的SQL
 
character-set-server=utf8                                               #服务端默认字符集
 
wait_timeout=1800                                                       #请求的最大连接时间
 
interactive_timeout=1800                                                #和上一参数同时修改才会生效
 
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES                     #sql模式
 
max_allowed_packet = 10M
 
bulk_insert_buffer_size = 8M
 
query_cache_type = 1
 
query_cache_size = 128M
 
query_cache_limit = 4M
 
key_buffer_size = 256M
 
read_buffer_size = 16K
 
 
skip-name-resolve
 
slow_query_log=1
 
long_query_time = 6
 
slow_query_log_file=slow-query.log
 
innodb_flush_log_at_trx_commit = 2
 
innodb_log_buffer_size = 16M
 
 
[mysql]
 
no-auto-rehash
 
 
[myisamchk]
 
key_buffer_size = 20M
 
sort_buffer_size = 20M
 
read_buffer = 2M
 
write_buffer = 2M
 
[mysqlhotcopy]
 
interactive-timeout
 
 
[mysqldump]
 
quick
 
max_allowed_packet = 16M
 
 
[mysqld_safe]

主要配置都是一样的，注意的是broker-id 不同，自增初始id不同，

mysqA与mysqlB 配置从权限

A 与 B 均执行以下指令

 # 进入数据库
/usr/mysql/bin/mysql -uroot -proot
# 设置从用户 'slave'@'%' 密码为 'slave'
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED  BY 'slave';
# 刷新权限
flush privileges;

区别开始

mysql A 查看master状态

 show master status;

在这里插入图片描述

mysql B 查看master状态

 show master status;

在这里插入图片描述

在mysql A上执行指令

 change master to master_host = '172.165.165.132',
master_user = 'slave',
master_password = 'slave',
master_log_file = 'mysql-bin.000012',
master_log_pos = 1167;
# 开始从操作，从主库同步数据
start slave;
# 查看从状态
show slave status \G

在这里插入图片描述

在mysqlB上执行指令

 change master to master_host = '172.165.165.131',
master_user = 'slave',
master_password = 'slave',
master_log_file = 'mysql-bin.000017',
master_log_pos = 68153006;
# 开始从操作，从主库同步数据
start slave;

在这里插入图片描述

nginx配置

下载地址：http://nginx.org/en/download.html

安装过程极其简单，主要注意的是要有make安装环境就行，安装结束后，nginx默认是在 /usr/local/nginx 文件夹下

解压

在这里插入图片描述

执行configuration

在这里插入图片描述

强迫症患者看的难受的话可以在.configuration的时候指定openssl地址，一般没影响，大部分服务器都具备基础环境的，这只是没有显示的指出地址，找不到，又不是不能用。
更多安装详情请看官网说明：http://nginx.org/en/docs/configure.html，
其实大部分软件基础使用，官方都会给出最为权威的文档，一定要看官网

在这里插入图片描述

找到openssl地址，在官方案例上指定openssl库源路径配置nginx
open

 ./configure\
    --sbin-path=/usr/local/nginx/nginx\
    --conf-path=/usr/local/nginx/nginx.conf\
    --pid-path=/usr/local/nginx/nginx.pid\
    --with-http_ssl_module\
    --with-stream_ssl_module;

进入nginx安装包文件夹，执行make && make install 指令进行安装
这里我脑子抽了，看到别人的教程，没注意，以为是有个make文件，执行make脚本进行安装。硬是要找make文件，其实make是一个安装环境，一帮的服务器都有的，没有就执行
yum install gcc automake autoconf libtool make

在这里插入图片描述

启动nginx

nginx默认使用80端口演示案例

如果无法访问八成是防火墙的原因，请移步Linux常用指令操作防火墙篇

配置nginx代理数据库

在 /usr/local/nginx/conf/nginx.cnf文件里面添加流配置
重启nginx、配置防火墙
/usr/local/nginx/sbin/nginx -s reload

 stream {
    server {
       listen 3306;
       proxy_pass db;
    }
	upstream db {
		server 172.165.165.131:3306 weight=2 max_fails=2 fail_timeout=20;
		server 172.165.165.132:3306 weight=3 max_fails=2 fail_timeout=30;
	}
}

中间有些小问题，

警告缺失openssl，这个不装其实也没事，没用到就可以不装，

安装openssl又报缺失 perl 5

mysql代理设置后启动nginx、异常缺失流模块，官方有说明，配置的时候加上这个，然后在nginx.conf文件在stream使用前加上一行
load_module '/usr/local/nginx/modules/ngx_stream_module.so'; 就行
反正缺什么装什么就是了

在这里插入图片描述

我的nginx.conf文件

 load_module '/usr/local/nginx/modules/ngx_stream_module.so';
#user  nobody;
worker_processes  1;
 
#error_log  logs/error.log;
#error_log  logs/error.log  notice;
#error_log  logs/error.log  info;
 
#pid        logs/nginx.pid;
stream {
    server {
       listen 9000;
       proxy_pass db;
    }
    upstream db {
        server 172.165.165.131:3306 weight=2 max_fails=2 fail_timeout=20;
        server 172.165.165.132:3306 weight=3 max_fails=2 fail_timeout=30;
    }
}
 
events {
    worker_connections  1024;
}
 
 
http {
    include       mime.types;
    default_type  application/octet-stream;
 
    #log_format  main  '$remote_addr - $remote_user [$time_local] "$request" '
    #                  '$status $body_bytes_sent "$http_referer" '
    #                  '"$http_user_agent" "$http_x_forwarded_for"';
 
    #access_log  logs/access.log  main;
 
    sendfile        on;
    #tcp_nopush     on;
 
    #keepalive_timeout  0;
    keepalive_timeout  65;
 
    #gzip  on;
 
    server {
        listen       80;
        server_name  localhost;
 
        #charset koi8-r;
 
        #access_log  logs/host.access.log  main;
 
        location / {
            root   html;
            index  index.html index.htm;
        }
 
        #error_page  404              /404.html;
 
        # redirect server error pages to the static page /50x.html
        #
        error_page   500 502 503 504  /50x.html;
        location = /50x.html {
            root   html;
        }
 
        # proxy the PHP scripts to Apache listening on 127.0.0.1:80
        #
        #location ~ \.php$ {
        #    proxy_pass   http://127.0.0.1;
        #}
 
        # pass the PHP scripts to FastCGI server listening on 127.0.0.1:9000
        #
        #location ~ \.php$ {
        #    root           html;
        #    fastcgi_pass   127.0.0.1:9000;
        #    fastcgi_index  index.php;
        #    fastcgi_param  SCRIPT_FILENAME  /scripts$fastcgi_script_name;
        #    include        fastcgi_params;
        #}
 
        # deny access to .htaccess files, if Apache's document root
        # concurs with nginx's one
        #
        #location ~ /\.ht {
        #    deny  all;
        #}
    }
 
 
    # another virtual host using mix of IP-, name-, and port-based configuration
    #
    #server {
    #    listen       8000;
    #    listen       somename:8080;
    #    server_name  somename  alias  another.alias;
 
    #    location / {
    #        root   html;
    #        index  index.html index.htm;
    #    }
    #}
 
 
    # HTTPS server
    #
    #server {
    #    listen       443 ssl;
    #    server_name  localhost;
 
    #    ssl_certificate      cert.pem;
    #    ssl_certificate_key  cert.key;
 
    #    ssl_session_cache    shared:SSL:1m;
    #    ssl_session_timeout  5m;
 
    #    ssl_ciphers  HIGH:!aNULL:!MD5;
    #    ssl_prefer_server_ciphers  on;
 
    #    location / {
    #        root   html;
    #        index  index.html index.htm;
    #    }
    #}
 
}

posted @ 2021-08-08 16:13 临渊不羡渔阅读(402) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

阅读排行：
· 震惊！C++程序真的从main开始吗？99%的程序员都答错了
· 【硬核科普】Trae如何「偷看」你的代码？零基础破解AI编程运行原理
· 单元测试从入门到精通
· 上周热点回顾（3.3-3.9）
· winform 绘制太阳，地球，月球运作规律

公告

昵称：临渊不羡渔
园龄： 7年7个月
粉丝： 8
关注： 9

+加关注

2025年3月

日

一

二

三

四

五

六

临渊不羡渔

mysql 5.7 nginx负载+mysql双主实现

一台不够再加一台

准备工作

mysql配置文件

mysqA与mysqlB 配置从权限

mysql A 查看master状态

mysql B 查看master状态

nginx配置

解压

执行configuration

启动nginx

配置nginx代理数据库

我的nginx.conf文件

公告

搜索

常用链接

我的标签

随笔分类

随笔档案

文章分类

阅读排行榜

评论排行榜

推荐排行榜

最新评论

	# 百度云：不推荐，学习下载安装工具，肯定是选找新的来，我是迫于无奈，公司主用的是5.7的
	https://pan.baidu.com/s/1zIDvIEouGQSpAfPFR8hSnQ
	1234
	# 推荐清华镜像站，感谢清华，虽说拒绝了去读书，但是依然为我这个废柴提供了镜像服务
	https://mirrors.tuna.tsinghua.edu.cn/mysql/downloads/MySQL-5.7/

	nginx：172.165.165.121
	mysqlA：172.165.165.131
	mysqlB：172.165.165.132

	[client]
	port = 3306
	socket = /tmp/mysql.sock
	[mysqld]
	# 跳过登录密码校验，用于重置密码时使用
	# skip-grant-tables

	user=root

	basedir = /usr/local/mysql

	port = 3306

	socket = /tmp/mysql.sock

	datadir = /data/mysql

	pid-file = /data/mysql/mysql.pid

	log-error = /data/mysql/mysql.err


	server-id = 1

	auto_increment_offset = 1

	auto_increment_increment = 2 #奇数ID


	log-bin = mysql-bin #打开二进制功能,MASTER主服务器必须打开此项

	binlog-format=ROW

	log-slave-updates=true

	gtid-mode=on

	enforce-gtid-consistency=true

	master-info-repository=TABLE

	relay-log-info-repository=TABLE

	sync-master-info=1

	slave-parallel-workers=0

	sync_binlog=0

	binlog-checksum=CRC32

	master-verify-checksum=1

	slave-sql-verify-checksum=1

	binlog-rows-query-log_events=1


	expire_logs_days=5

	max_binlog_size=1024M #binlog单文件最大值


	replicate-ignore-db = mysql #忽略不同步主从的数据库

	replicate-ignore-db = information_schema

	replicate-ignore-db = performance_schema

	replicate-ignore-db = test

	replicate-ignore-db = zabbix


	max_connections = 3000

	max_connect_errors = 30

	explicit_defaults_for_timestamp=true
	sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

	skip-character-set-client-handshake #忽略应用程序想要设置的其他字符集

	init-connect='SET NAMES utf8' #连接时执行的SQL

	character-set-server=utf8 #服务端默认字符集

	wait_timeout=1800 #请求的最大连接时间

	interactive_timeout=1800 #和上一参数同时修改才会生效

	sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES #sql模式

	max_allowed_packet = 10M

	bulk_insert_buffer_size = 8M

	query_cache_type = 1

	query_cache_size = 128M

	query_cache_limit = 4M

	key_buffer_size = 256M

	read_buffer_size = 16K


	skip-name-resolve

	slow_query_log=1

	long_query_time = 6

	slow_query_log_file=slow-query.log

	innodb_flush_log_at_trx_commit = 2

	innodb_log_buffer_size = 16M


	[mysql]

	no-auto-rehash


	[myisamchk]

	key_buffer_size = 20M

	sort_buffer_size = 20M

	read_buffer = 2M

	write_buffer = 2M


	[mysqlhotcopy]

	interactive-timeout


	[mysqldump]

	quick

	max_allowed_packet = 16M


	[mysqld_safe]

	# 进入数据库
	/usr/mysql/bin/mysql -uroot -proot
	# 设置从用户 'slave'@'%' 密码为 'slave'
	GRANT REPLICATION SLAVE ON . TO 'slave'@'%' IDENTIFIED BY 'slave';
	# 刷新权限
	flush privileges;

	change master to master_host = '172.165.165.132',
	master_user = 'slave',
	master_password = 'slave',
	master_log_file = 'mysql-bin.000012',
	master_log_pos = 1167;
	# 开始从操作，从主库同步数据
	start slave;
	# 查看从状态
	show slave status \G

	./configure\
	--sbin-path=/usr/local/nginx/nginx\
	--conf-path=/usr/local/nginx/nginx.conf\
	--pid-path=/usr/local/nginx/nginx.pid\
	--with-http_ssl_module\
	--with-stream_ssl_module;

	stream {
	server {
	listen 3306;
	proxy_pass db;
	}
	upstream db {
	server 172.165.165.131:3306 weight=2 max_fails=2 fail_timeout=20;
	server 172.165.165.132:3306 weight=3 max_fails=2 fail_timeout=30;
	}
	}

	load_module '/usr/local/nginx/modules/ngx_stream_module.so';
	#user nobody;
	worker_processes 1;

	#error_log logs/error.log;
	#error_log logs/error.log notice;
	#error_log logs/error.log info;

	#pid logs/nginx.pid;
	stream {
	server {
	listen 9000;
	proxy_pass db;
	}
	upstream db {
	server 172.165.165.131:3306 weight=2 max_fails=2 fail_timeout=20;
	server 172.165.165.132:3306 weight=3 max_fails=2 fail_timeout=30;
	}
	}

	events {
	worker_connections 1024;
	}


	http {
	include mime.types;
	default_type application/octet-stream;

	#log_format main '$remote_addr - $remote_user [$time_local] "$request" '
	# '$status $body_bytes_sent "$http_referer" '
	# '"$http_user_agent" "$http_x_forwarded_for"';

	#access_log logs/access.log main;

	sendfile on;
	#tcp_nopush on;

	#keepalive_timeout 0;
	keepalive_timeout 65;

	#gzip on;

	server {
	listen 80;
	server_name localhost;

	#charset koi8-r;

	#access_log logs/host.access.log main;

	location / {
	root html;
	index index.html index.htm;
	}

	#error_page 404 /404.html;

	# redirect server error pages to the static page /50x.html
	#
	error_page 500 502 503 504 /50x.html;
	location = /50x.html {
	root html;
	}

	# proxy the PHP scripts to Apache listening on 127.0.0.1:80
	#
	#location ~ \.php$ {
	# proxy_pass http://127.0.0.1;
	#}

	# pass the PHP scripts to FastCGI server listening on 127.0.0.1:9000
	#
	#location ~ \.php$ {
	# root html;
	# fastcgi_pass 127.0.0.1:9000;
	# fastcgi_index index.php;
	# fastcgi_param SCRIPT_FILENAME /scripts$fastcgi_script_name;
	# include fastcgi_params;
	#}

	# deny access to .htaccess files, if Apache's document root
	# concurs with nginx's one
	#
	#location ~ /\.ht {
	# deny all;
	#}
	}


	# another virtual host using mix of IP-, name-, and port-based configuration
	#
	#server {
	# listen 8000;
	# listen somename:8080;
	# server_name somename alias another.alias;

	# location / {
	# root html;
	# index index.html index.htm;
	# }
	#}


	# HTTPS server
	#
	#server {
	# listen 443 ssl;
	# server_name localhost;

	# ssl_certificate cert.pem;
	# ssl_certificate_key cert.key;

	# ssl_session_cache shared:SSL:1m;
	# ssl_session_timeout 5m;

	# ssl_ciphers HIGH:!aNULL:!MD5;
	# ssl_prefer_server_ciphers on;

	# location / {
	# root html;
	# index index.html index.htm;
	# }
	#}

	}