mysql 5.7 nginx负载+mysql双主实现

目录

• 一台不够再加一台
  • 准备工作
  • mysql配置文件
  • mysqA与mysqlB 配置从权限
    • mysql A 查看master状态
    • mysql B 查看master状态
• 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;
	}
}

中间有些小问题，

1. 警告缺失openssl，这个不装其实也没事，没用到就可以不装，
2. 安装openssl又报缺失 perl 5
3. 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;
    #    }
    #}

}