2020/06/08 mysql主主+nginx反代mysql

1、环境描述及注意事项

角色	ip
主nginx,mysql-master1	10.211.7.11
备nginx,mysql-master2	10.211.7.12

注意事项：

确保selinux和firewalld，处于disable和关闭状态。
此部署搭建需要在没有安装mysql,nginx的机器上搭建，不然可能导致安装不成功。

安装mysql

由于节省时间，部分操作指定两台机器可以同步执行

2.检测系统是否自带mysql,两台机器可以同步操作

[root@localhost /]# rpm -qa | grep mysql
[root@localhost /]# rpm -qa | grep mariadb

3.如果安装过mysql，则使用下面命令进行删除,两台机器可以同步操作

[root@localhost /]# rpm -e --nodeps ‘上一步查找的名称’  　// 强力删除模式，如果使用上面命令删除时，提示有依赖的其它文件，则用该命令可以对其进行强力删除

4.删除成功后，查询所有Mysql对应的文件夹,删除查找的所有文件夹,两台机器可以同步操作

[root@localhost ~]# whereis mysql
mysql: /usr/bin/mysql /usr/local/mysql
[root@localhost ~]# find / -name mysql |xargs -n1 rm -rf
/run/lock/subsys/mysql
/etc/rc.d/init.d/mysql
/usr/bin/mysql
/usr/local/mysql
/usr/local/mysql/bin/mysql
/usr/local/mysql/include/mysql
/usr/local/mysql/data/mysql

5.下载安装包，一般放在/usr/local/src下,两台机器可以同步操作

[root@localhost ~]# wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz
[root@localhost ~]# tar xvf mysql-5.7.30-linux-glibc2.12-x86_64\ \(1\).tar.gz  

6.创建mysql用户,两台机器可以同步操作

[root@localhost ~]# groupadd mysql
[root@localhost ~]# useradd -r -g mysql -s /bin/false mysql

7.修改解压目录名称,两台机器可以同步操作

[root@localhost ~]# mv mysql-5.7.30-linux-glibc2.12-x86_64 /usr/local/mysql

8.安装依赖工具,两台机器可以同步操作

[root@localhost ~]# yum search libaio  # search for info
[root@localhost ~]# yum install libaio # install library

9.加入path变量，并设置开机自启,两台机器可以同步操作

[root@localhost ~]# vim /etc/profile
#添加下面一行
export PATH=$PATH:/usr/local/mysql/bin
[root@localhost ~]# source /etc/profile
[root@localhost ~]# chkconfig --add mysql 
[root@localhost ~]# chkconfig --list

Note: This output shows SysV services only and does not include native
      systemd services. SysV configuration data might be overridden by native
      systemd configuration.

      If you want to list systemd services use 'systemctl list-unit-files'.
      To see services enabled on particular target use
      'systemctl list-dependencies [target]'.

mysql           0:off   1:off   2:on    3:on    4:on    5:on    6:off
netconsole      0:off   1:off   2:off   3:off   4:off   5:off   6:off
network         0:off   1:off   2:on    3:on    4:on    5:on    6:off

10.目录下创建data目录和basedir，和其他需要的目录,两台机器可以同步操作

[root@localhost mysql]# mkdir -p /data/mysql/data
[root@localhost mysql]# mkdir -p /data/mysql/logs
[root@localhost mysql]# mkdir -p /data/mysql/mysql/tmp
[root@localhost mysql]# chown -R  mysql.mysql /data/mysql

11.编译安装并初始化mysql,确保/etc/my.cnf文件不存在，否则会出现问题,两台机器可以同步操作

[root@localhost mysql]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/data/mysql/data --basedir=/usr/local/mysql
2020-06-03T02:35:19.965501Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-06-03T02:35:21.957991Z 0 [Warning] InnoDB: New log files created, LSN=45790
2020-06-03T02:35:22.081331Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2020-06-03T02:35:22.338628Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: df713c56-a542-11ea-93bd-000c29d94b11.
2020-06-03T02:35:22.339895Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2020-06-03T02:35:22.654337Z 0 [Warning] CA certificate ca.pem is self signed.
2020-06-03T02:35:22.778157Z 1 [Note] A temporary password is generated for root@localhost: (MeW%o3==Fsl  #这段是密码

12.开启ssl连接：

[root@localhost mysql]# bin/mysql_ssl_rsa_setup --datadir=/data/mysql/data

13.编辑10.211.7.11也就是mysql-master1配置文件my.cnf，添加配置如下

[client]
port            = 3306
socket          = /tmp/mysql.sock

[mysql]
#prompt="\\u@\\h [\\d]>"
#pager="less -i -n -S"
#tee=/opt/mysql/query.log
no-auto-rehash

[mysqld]
#misc
user = mysql
basedir = /usr/local/mysql
datadir = /data/mysql/data
port = 3306

socket = /tmp/mysql.sock
event_scheduler = 0

tmpdir = /data/mysql/tmp
#timeout
interactive_timeout = 300
wait_timeout = 300

#character set
character-set-server = utf8

open_files_limit = 65535
max_connections = 1000
max_connect_errors = 100000
lower_case_table_names =1
#file
#@secure-file-priv=/tmp
#symi replication

#rpl_semi_sync_master_enabled=1
#rpl_semi_sync_master_timeout=1000 # 1 second
#rpl_semi_sync_slave_enabled=1

#logs
log-output=file
slow_query_log = 1
slow_query_log_file = slow.log
log-error = error.log
log_warnings = 2
pid-file = mysql.pid
long_query_time = 1
#log-slow-admin-statements = 1
#log-queries-not-using-indexes = 1
log-slow-slave-statements = 1

#binlog
#binlog_format = STATEMENT
binlog_format = row
server-id = 1003306
auto_increment_offset=1
auto_increment_increment=2
log-bin = /data/mysql/logs/mysql-bin
max_binlog_size = 256M
sync_binlog = 0
expire_logs_days = 10
#procedure
log_bin_trust_function_creators=1
gtid_mode=ON
enforce-gtid-consistency=true

#file
secure_file_priv="/tmp"
#
gtid-mode = on
enforce-gtid-consistency=1


#relay log
skip_slave_start = 1
max_relay_log_size = 128M
relay_log_purge = 1
relay_log_recovery = 1
relay-log=relay-bin
relay-log-index=relay-bin.index
log_slave_updates

#slave-skip-errors=1032,1053,1062
#skip-grant-tables

#buffers & cache
table_open_cache = 2048
table_definition_cache = 2048
table_open_cache = 2048
max_heap_table_size = 96M
sort_buffer_size = 128K
join_buffer_size = 128K
thread_cache_size = 200
query_cache_size = 0
query_cache_type = 0
query_cache_limit = 256K
query_cache_min_res_unit = 512
thread_stack = 192K
tmp_table_size = 96M
key_buffer_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 32M

#myisam
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1

#innodb
innodb_buffer_pool_size = 10737418240
innodb_buffer_pool_instances = 1
innodb_data_file_path = ibdata1:1024M:autoextend
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 16M
innodb_log_file_size = 128M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 50
innodb_file_per_table = 1
innodb_rollback_on_timeout
innodb_io_capacity = 2000
transaction_isolation = READ-COMMITTED
innodb_flush_method = O_DIRECT

14.编辑10.211.7.12也就是mysql-master2配置文件my.cnf，添加配置如下

[client]
port= 3306
socket  = /tmp/mysql.sock
[mysql]
no-auto-rehash

[mysqld]
basedir=/usr/local/mysql
datadir=/data/mysql/data
user=mysql
port=3306
default-storage-engine=InnoDB

socket = /tmp/mysql.sock
event_scheduler = 0

tmpdir = /data/mysql/mysql/tmp
#timeout
#interactive_timeout = 300
#wait_timeout = 300

##character set
character-set-server = utf8

open_files_limit = 65535
max_connections = 3000
max_connect_errors = 6000
lower_case_table_names =1
#file
#@secure-file-priv=/tmp
#symi replication

#rp;-semi_sync_master_enabled=1
#rpl_semi_sync_master_timeout=1000 # 1 second
##rpl_semi_sync_slave_enabled=1

#logs
log-output=file
#slow_query_log = 1
-auto-rehash
slow_query_log_file = slow.log
log-error = error.log
log_warnings = 2
pid-file = /data/mysql/data/mysql.pid
long_query_time = 1
#log-slow-admin-statements = 1
##log-queries-not-using-indexes = 1
log-slow-slave-statements = 1

#binlog
#binlog_format = STATEMENT
binlog_format = row
auto_increment_offset=2
auto_increment_increment=2
server-id = 1003307
log-bin = /data/mysql/logs/mysql-bin
max_binlog_size = 256M
sync_binlog = 0
expire_logs_days = 10
#procedure
log_bin_trust_function_creators=1

##file
secure_file_priv="/tmp"
#
gtid-mode = on
enforce-gtid-consistency=1


#relay log
skip_slave_start = 1
max_relay_log_size = 128M
relay_log_purge = 1
relay_log_recovery = 1
relay-log=relay-bin
relay-log-index=relay-bin.index
log_slave_updates

#slave-skip-errors=1032,1053,1062

#buffers & cache
table_open_cache = 2048
table_definition_cache = 2048
table_open_cache = 2048
max_heap_table_size = 96M
sort_buffer_size = 128K
join_buffer_size = 128K
thread_cache_size = 200
query_cache_size = 0
query_cache_type = 0
query_cache_limit = 256K
query_cache_min_res_unit = 512
thread_stack = 192K
tmp_table_size = 96M
key_buffer_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 32M

#myisam
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1

#innodb
innodb_buffer_pool_size = 10737418240
innodb_buffer_pool_instances = 1
innodb_data_file_path = ibdata1:1024M:autoextend
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 16M
innodb_log_file_size = 128M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 50
innodb_file_per_table = 1
innodb_rollback_on_timeout
innodb_io_capacity = 2000
transaction_isolation = READ-COMMITTED
innodb_flush_method = O_DIRECT

15.运行mysql安全脚本,两台机器可以同步操作，密码是之前初始化产生的密码，每台机器都有各自的初始密码

[root@localhost mysql]# /usr/local/mysql/bin/mysql_secure_installation 
mysql_secure_installation: [ERROR] unknown variable 'prompt=\u@\h [\d]>'

Securing the MySQL server deployment.

Enter password for user root: 

The existing password for the user account root has expired. Please set a new password.

New password: 

Re-enter new password: 

配置双主模型

1.配置主服务器10.211.7.11

[root@localhost mysql]# grant replication slave on *.* to repl@'10.211.7.%' identified by '!tp!gNp667aPT';

#互相设置对方为主
CHANGE MASTER TO MASTER_HOST='10.211.7.11', MASTER_USER='repl',MASTER_PASSWORD='!tp!gNp667aPT',MASTER_LOG_FILE='mysql-bin.000008',MASTER_LOG_POS=2100;

#开始同步
start slave;

 show master logs;显示日志位置

2.配置主服务器10.211.7.12

CHANGE MASTER TO MASTER_HOST='10.211.7.12', MASTER_USER='repl',MASTER_PASSWORD='!tp!gNp667aPT',MASTER_LOG_FILE='mysql-bin.000004',MASTER_LOG_POS=491;

#开始同步
start slave;

搭建完主主后，要在两个数据库中插入有自增表的数据，观察ID。
如果不指定自增ID，他会按照配置文件中的自增规则来递增，也可以强制指定ID。如果两边同时插入同ID的数据，就会出现数据不同步的情况。

Nginx反代主主

1.选择你要安装的版本，http://tengine.taobao.org/download.html下载地址

[root@baiduyun-test ~]# wget http://tengine.taobao.org/download/tengine-2.3.0.tar.gz

2.删除以前残留的版本

[root@baiduyun-test ~]#rpm -qa |grep nginx |xargs -n1 rpm -e --nodeps 
[root@baiduyun-test ~]# find / -name nginx |xargs -n1 rm -rf
[root@baiduyun-test ~]# find / -name nginx 

3.解压文件

[root@baiduyun-test ~]#tar xf  tengine-2.3.0.tar.gz

4.安装依赖包

[root@baiduyun-test ~]# yum  groupinstall  "Development Tools" -y
[root@baiduyun-test ~]#  yum install -y gcc pcre pcre-devel openssl openssl-devel gd gd-devel

5.编译安装

[root@node2 tengine-2.3.0]# ./configure --prefix=/usr/local/nginx --with-http_stub_status_module --with-http_ssl_module --with-http_realip_module --with-http_flv_module --with-http_mp4_module --with-http_gzip_static_module --with-stream --with-stream_ssl_module
[root@node2 tengine-2.3.0]# make
[root@node2 tengine-2.3.0]# make install

6.默认安装下来的目录

[root@baiduyun-test tengine-2.3.0]#  find / -name nginx 
/usr/local/nginx
/usr/local/nginx/sbin/nginx
/root/tengine-2.3.0/objs/nginx

7.创建nginx用户

[root@baiduyun-test tengine-2.3.0]# groupadd nginx
[root@baiduyun-test tengine-2.3.0]# useradd nginx -g nginx -s /sbin/nologin -M
#-M不创建家目录

8.软连接

[root@baiduyun-test tengine-2.3.0]#ln -s /usr/local/nginx/sbin/nginx /usr/sbin/nginx 

9.配置文件路径

[root@baiduyun-test tengine-2.3.0] /usr/local/nginx/conf/nginx.conf

可以直接用nginx启动

[root@baiduyun-test tengine-2.3.0] nginx

10.编辑/usr/local/nginx/conf/nginx.conf,在最后一行添加如下

stream {
    server {
       listen 3336;
       proxy_pass db;    
    }
    upstream db {
       server 10.211.7.11:3306;
       server 10.211.7.12:3306;
    }
}

11. 配置完成后启动nginx

/usr/local/nginx/bin/nginx  -s reload

12.测试登录

mysql -uroot -p'QianFeng@123' -h 10.211.7.11 -P 3336
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.7.22-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.16 sec)

MySQL [(none)]> create database abc;
Query OK, 1 row affected (0.00 sec)