mysql二进制安装规范及多实例配置

目录

• 1.一些安装规范
• 2.二进制安装
  • 2.1环境配置
  • 2.2配置配置文件
  • 2.3初始化
  • 2.4启动
  • 2.5配置开机自启
    • service方式
    • centos7方式
• 3.配置文件参数解释
  • 3.1 binglog的三种格式
• 4.多实例配置
  • 多实例的部署
    • 1.准备配置文件
    • 2.初始化
    • 3.配置systemd管理多实例
    • 4.启动测试
• 通用配置文件示例

1.一些安装规范

• MySQL安装方式：二进制安装（源码编译安装、yum 、rpm）

• MySQL运行用户：

  mysql:mysql 注意该用户是虚拟用户，只是用于mysql进程运行使用，不允许登录、不创建家目录
  
  useradd -s /sbin/nologin -M mysql
  

• MySQL目录规范：

  数据以单独分区存放，如/data
  
  解压目录     /opt/mysql/mysql-xx.xx
  软连接       ln -s /opt/mysql/mysql-xx.xx /usr/local/mysql
  数据目录     /data/mysql/mysql+port/{data,logs}
  配置文件     /data/mysql/mysql+port/my+port.cnf
  

• MySQL版本选择

  企业版：Enterprise , 一般不做考虑
  社区版：这才是我们需要的，毕竟不要钱
  包的选择：使用通用的二进制包
  版本号选择： ga,尽量选择版本号最后一位是偶数
  

2.二进制安装

MySQL版本为5.7.30，系统为centos7最小化安装

2.1环境配置

#安装mysql相关的依赖包
yum -y install libaio libaio-devel numactl ncurses-devel 
rpm -e --nodeps mariadb-libs

#创建mysql运行使用用户
useradd -s /sbin/nologin -M mysql

#创建目录
mkdir -p /opt/mysql
mkdir -p /data/mysql/mysql3306/{data,logs}

#上传二进制包
[root@localhost ~]# ls /opt/mysql/
mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz

#解压（或者直接解压到目录，然后mv重命名）
[root@localhost ~]# tar xf /opt/mysql/mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz -C /usr/local/

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

2.2配置配置文件

#使用简易配置文件
vim /data/mysql/mysql3306/my3306.cnf
[mysqld]
user=mysql
basedir = /usr/local/mysql
datadir=/data/mysql/mysql3306/data
socket = /data/mysql/mysql3306/mysql.sock
log_error=/data/mysql/mysql3306/logs/mysql.log
server_id=1
port = 3306
bind-address=0.0.0.0
pid-file = /data/mysql/mysql3306/mysql.pid
user = mysql
skip-name-resolve


#参考
vim /data/mysql/mysql3306/my3306.cnf


[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/mysql/mysql3306/data
socket = /data/mysql/mysql3306/mysql.sock
server_id = 1
port = 3306
secure-file-priv=/tmp
log_error=/data/mysql/mysql3306/logs/error.log
log_bin=/data/mysql/mysql3306/logs/mysql-bin
binlog_format=row
gtid_mode=on
enforce_gtid_consistency=true
log_slave_updates=1
max_connections=1024
wait_timeout=60
sort_buffer_size=2M
max_allowed_packet=32M
join_buffer_size=2M
innodb_buffer_pool_size=128M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=32M
innodb_log_file_size=128M
innodb_log_files_in_group=2
binlog_cache_size=2M
max_binlog_cache_size=8M
max_binlog_size=512M
expire_logs_days=7
slow_query_log=on
slow_query_log_file=/data/mysql/mysql3306/logs/slow.log
long_query_time=0.5
log_queries_not_using_indexes=1

2.3初始化

#更改mysql相关目录属主
chown -R mysql:mysql /data/*
chown -R mysql:mysql /usr/local/mysql*

#配置环境变量
echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
. /etc/profile.d/mysql.sh

#初始化
/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf --initialize-insecure --user=mysql --basedir=/usr/local/mysql  --datadir=/data/mysql/mysql3306/data 

2.4启动

mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf &

[root@localhost ~]# ss -tanlp | grep 3306
LISTEN     0      80        [::]:3306                  [::]:*                   users:(("mysqld",pid=93803,fd=20))
[root@localhost ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.30 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

2.5配置开机自启

service方式

cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld

#修改/etc/init.d/mysqld中的下面两行
basedir=/usr/local/mysql
datadir=/data/mysql/mysql3306/data

[root@localhost ~]# service mysqld start
Starting MySQL. SUCCESS!
[root@localhost ~]# service mysqld status
 SUCCESS! MySQL running (99795)
[root@localhost ~]# service mysqld stop
Shutting down MySQL.. SUCCESS!
[root@localhost ~]# ss -tanl
State       Recv-Q Send-Q Local Address:Port               Peer Address:Port           
LISTEN      0      100     127.0.0.1:25                          *:*
LISTEN      0      128             *:22                          *:*
LISTEN      0      100         [::1]:25                       [::]:*
LISTEN      0      128          [::]:22                       [::]:*
[root@localhost ~]# service mysqld start
Starting MySQL. SUCCESS!
[root@localhost ~]# ss -tanl
State       Recv-Q Send-Q Local Address:Port               Peer Address:Port           
LISTEN      0      100     127.0.0.1:25                          *:*
LISTEN      0      128             *:22                          *:*
LISTEN      0      100         [::1]:25                       [::]:*
LISTEN      0      80           [::]:3306                     [::]:*
LISTEN      0      128          [::]:22                       [::]:*

#添加开机自启
[root@localhost ~]# chkconfig --add mysqld
[root@localhost ~]# chkconfig --list | grep mysqld

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]'.

mysqld          0:off   1:off   2:on    3:on    4:on    5:on    6:off
#删除开机自启
[root@localhost ~]# chkconfig --del mysqld
[root@localhost ~]# chkconfig --list | grep mysqld

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]'.

[root@localhost ~]#

centos7方式

vim /usr/lib/systemd/system/mysqld.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql


#PIDFile=/data/mysqldata/mysql.pid

# Disable service start and stop timeout logic of systemd for mysqld service.
TimeoutSec=0

# Execute pre and post scripts as root
PermissionsStartOnly=true
# Needed to create system tables
#ExecStartPre=/usr/bin/mysqld_pre_systemd

# Start main service
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf --pid-file=/data/mysql/mysql3306/data/mysql3306.pid
# Use this to switch malloc implementation
#EnvironmentFile=-/etc/sysconfig/mysql

# Sets open_files_limit
LimitNOFILE = 5000
#Restart=on-failure
#RestartPreventExitStatus=1
#PrivateTmp=false




systemctl daemon-reload
systemctl enable mysqld

3.配置文件参数解释

关于配置文件，没有能完美适应所有场景的“最佳配置”

mysql可配置性太强在这种情况来说非常不好，所以在一些基础配置上针对使用场景略作优化即可。没有十足的把握，不要去随意配置自己不明白的参数。而且mysql很多参数默认的值已经是最佳配置了，一般常见的优化只会在innodb或是日常维护管理相关的参数上。

纸上得来终觉浅，多做测试，然后用于生产环境，才是正道，不要过多纠结于某些参数配置，没有太多意义。

示例：

[client]
port = 3306
socket = /data/mysql/mysql3306/mysql.sock

[mysql]
prompt="\u@db \R:\m:\s [\d]> "
no-auto-rehash

[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/mysql/mysql3306/data
socket = /data/mysql/mysql3306/mysql.sock
server_id = 1
port = 3306
secure-file-priv=/tmp
log_error=/data/mysql/mysql3306/logs/error.log
log_bin=/data/mysql/mysql3306/logs/mysql-bin
binlog_format=row
gtid_mode=on
enforce_gtid_consistency=true
log_slave_updates=1
max_connections=1024
wait_timeout=60
sort_buffer_size=2M
max_allowed_packet=32M
join_buffer_size=2M
innodb_buffer_pool_size=128M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=32M
innodb_log_file_size=128M
innodb_log_files_in_group=2
binlog_cache_size=2M
max_binlog_cache_size=8M
max_binlog_size=512M
expire_logs_days=7
slow_query_log=on
slow_query_log_file=/data/mysql/mysql3306/logs/slow.log
long_query_time=0.5
log_queries_not_using_indexes=1

参数解析：

[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/mysql/mysql3306/data
socket = /data/mysql/mysql3306/mysql.sock
server_id = 1
port = 3306
secure-file-priv=/tmp
log_error=/data/mysql/mysql3306/logs/error.log
log_bin=/data/mysql/mysql3306/logs/mysql-bin
binlog_format=row    #对于复杂集群环境，尽量统一row格式有好处，但记录的日志量会略大
#binlog_format=MIXED  #日志记录使用混合模式，根据执行的每一条具体的 SQL 语句来选择是statement 和 row
gtid_mode=on
enforce_gtid_consistency=true
log_slave_updates=1	#让从库从主库复制数据时可以写入到binlog日志，为了让从库作为其他从库的主库
max_connections=1024
wait_timeout=60
#autocommit=0  #事务不自动提交
sort_buffer_size=2M	#connection级参数，默认256k（范围在256k-2m），每个连接第一次需要这个buffer时，一次性分配的内存大小,建议不配保持默认，因为高并发可能导致性能下降
max_allowed_packet=32M	#设置一结果集缓存的最大值
join_buffer_size=2M	#默认256k,如果多次使用join查询导致cost过高，可调至8-16m(尽量在session级别上调整)
innodb_buffer_pool_size=128M	#innodb缓冲区大小
innodb_flush_log_at_trx_commit=1	#控制Innodb事务日志写入的过程，0,1（默认）,2；每次提交都会把log buffer写入log file,并刷到磁盘
innodb_log_buffer_size=32M	# redo log 的写缓存，设置大一点能减少写操作,也不能设置过大
innodb_log_file_size=128M	#控制事务日志ib_logfile的大小,范围5MB~4G
innodb_log_files_in_group=2	#指定有及格日志组，一般2-3个
binlog_cache_size=2M	#binlog缓存在内存的大小
max_binlog_cache_size=8M	#binlog 能够使用的最大cache 内存大小，不足会报错ERROR 1197
max_binlog_size=512M	#如果在开启了bin-log的机器，如主从等，导入大sql文件时要关闭sql_log_bin,让其不写入bin_log,也就不会让从库执行
expire_logs_days=7	#日志保留天数
slow_query_log=on	#启用慢查询日志
slow_query_log_file=/data/mysql/mysql3306/logs/slow.log	#慢查询日志路径
long_query_time=0.5	#慢查询阈值，单位秒，执行时间超过这个值的将被记录为慢查询日志中
log_queries_not_using_indexes=1	#没有使用索引的sql也将被记录到慢查询日志中

配置文件中也可以配置prompt来改变登录后的默认提示符

#例如
[mysql]
prompt = "\\u@\\h> "

常用的：
\d: 当前数据库
\h: 服务器地址
\u: 用户名
\r:\m:\s:显示当前时间
更多参数详见官方文档：https://dev.mysql.com/doc/refman/5.7/en/mysql-commands.html

3.1 binglog的三种格式

三种binlog格式：

• statement：基于语句的复制（也称为逻辑复制）主要是指，在主数据库上执行的SQL语句，在从数据库上会重复执行一遍 。MySQL默认采用的就是这种复制，效率比较高。但是也是有一定的问题的，如果SQL中使用uuid()、rand()等函数，那么复制到从库的数据就会有偏差
• row：基于行的复制，指将更新处理后的数据复制到从数据库，而不是执行一边语句 。从MySQL5.1的版本才被支持
• MIXED：混合复制，默认采用语句复制，当发现语句不能进行精准复制数据时（例如语句中含有uuid()、rand()等函数），采用基于行的复制

4.多实例配置

mysql实例由进程+线程+预分配内存组成，mysql是单进程多线程的结构

线程：Master thread、purge thread 、page cleaner thread、io thread

预分配内存：buffer pool、insert buffer、change buffer、double write

为什么要用单机多实例（进程+线程+预分配内存）

• 单机各种资源有余量：cpu、mem、iops
• 主从结构中，从库清闲
• 业务高峰期不同

多实例的部署

共用系统部分，实例之间具有不同的datadir、server_id、port、日志文件、socket文件

与之前配置一样，无非就是多个配置文件、多初始化几次，然后启动等

当然，也能一个配置文件中写多个配置，但这样不方便管理

但官网的工具mysqld_multi可以便捷的管理一个配置文件中的多个mysql配置

1.准备配置文件

以添加两个示例为例：

mkdir -p /data/mysql/{mysql3307/{data,logs},mysql3308/{data,logs}}
chown -R mysql.mysql /data/mysql/
#注意文件目录和server_id
cat > /data/mysql/mysql3307/my3307.cnf <<EOF
[mysqld]
user=mysql
basedir = /usr/local/mysql
datadir=/data/mysql/mysql3307/data
socket = /data/mysql/mysql3307/mysql.sock
log_error=/data/mysql/mysql3307/logs/mysql.log
server_id=7
port = 3307
bind-address=0.0.0.0
pid-file = /data/mysql/mysql3307/mysql.pid
user = mysql
skip-name-resolve
EOF


cat > /data/mysql/mysql3308/my3308.cnf <<EOF
[mysqld]
user=mysql
basedir = /usr/local/mysql
datadir=/data/mysql/mysql3308/data
socket = /data/mysql/mysql3308/mysql.sock
log_error=/data/mysql/mysql3308/logs/mysql.log
server_id=8
port = 3308
bind-address=0.0.0.0
pid-file = /data/mysql/mysql3308/mysql.pid
user = mysql
skip-name-resolve
EOF

#配置文件调整时，记得备份

2.初始化

mysqld --defaults-file=/data/mysql/mysql3307/my3307.cnf --initialize-insecure --user=mysql --basedir=/usr/local/mysql  --datadir=/data/mysql/mysql3307/data 

mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf --initialize-insecure --user=mysql --basedir=/usr/local/mysql  --datadir=/data/mysql/mysql3308/data 

3.配置systemd管理多实例

#根据之前上面安装配置的.service文件稍稍更改即可
#需要更改的只有下面这一行，把3306改成需要的就行了
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf --pid-file=/data/mysql/mysql3306/data/mysql3306.pid


#直接cp过去更改，或是EOF生成
cp /usr/lib/systemd/system/mysqld.service /usr/lib/systemd/system/mysql3307.service
cp /usr/lib/systemd/system/mysqld.service /usr/lib/systemd/system/mysql3308.service

#将其中的ExecStart修改即可
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3307/my3307.cnf --pid-file=/data/mysql/mysql3307/data/mysql3307.pid
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf --pid-file=/data/mysql/mysql3308/data/mysql3308.pid


#重载配置
systemctl daemon-reload

4.启动测试

systemctl start mysql3307
systemctl start mysql3308

[root@localhost ~]# ss -tanl
State       Recv-Q Send-Q Local Address:Port                Peer Address:Port
LISTEN      0      80                 *:3306                           *:*
LISTEN      0      80                 *:3307                           *:*
LISTEN      0      80                 *:3308                           *:*
LISTEN      0      128                *:22                             *:*
LISTEN      0      128             [::]:22                          [::]:*


[root@localhost ~]# mysql -S /data/mysql/mysql3307/mysql.sock -e "select @@server_id"
+-------------+
| @@server_id |
+-------------+
|           7 |
+-------------+
[root@localhost ~]# mysql -S /data/mysql/mysql3308/mysql.sock -e "select @@server_id"
+-------------+
| @@server_id |
+-------------+
|           8 |
+-------------+

通用配置文件示例

[mysqld]
user=mysql
basedir = /usr/local/mysql
datadir=/data/mysql/mysql3307/data
socket = /data/mysql/mysql3307/mysql.sock
log_error=/data/mysql/mysql3307/logs/mysql.log
secure-file-priv=/tmp
server_id=1
port = 3306
bind-address=0.0.0.0
pid-file = /data/mysql/mysql3307/mysql.pid
user = mysql
skip-name-resolve

log_bin=/data/mysql/mysql3307/logs/mysql-bin
#binlog_format=row
binlog_format=MIXED
gtid_mode=on
enforce_gtid_consistency=true
log_slave_updates=1
max_connections=1024
wait_timeout=60
sort_buffer_size=2M
max_allowed_packet=32M
join_buffer_size=2M
innodb_buffer_pool_size=128M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=32M
innodb_log_file_size=128M
innodb_log_files_in_group=2
binlog_cache_size=2M
max_binlog_cache_size=8M
max_binlog_size=512M
expire_logs_days=7
slow_query_log=on
slow_query_log_file=/data/mysql/mysql3306/logs/slow.log
long_query_time=0.5
log_queries_not_using_indexes=1