MySQL安装部署

MySQL二进制方式安装

1. 安装依赖包

yum install -y libaio numactl-libs ncurses-compat-libs

2. 创建服务运行用户

groupadd -g 27 -o -r mysql
useradd -M -N -g mysql -o -r -d /home/hadoop/mysql -s /bin/false -c "MySQL Server" -u 27 mysql

3. 下载解压Mysql 二进制安装包

# https://downloads.mysql.com/archives/community/
# 下载解压安装包
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz
tar xfz mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz -C /usr/local

# 配置/读取环境变量
ln -sf /usr/local/mysql-5.7.38-linux-glibc2.12-x86_64 /usr/local/mysql
echo 'export PATH=$PATH:/usr/local/mysql/bin' >> /etc/profile
source /etc/profile

4. 创建数据目录，配置文件，初始化

# 创建数据目录
mkdir -p /home/hadoop/mysql
chown -Rf mysql:mysql /home/hadoop/mysql

# 创建配置文件
cat <<EOF   > /etc/my.cnf
[mysqld]
port=3306
server-id=1

# 启用binlog
log-bin=master

# binlog 保留天数
expire_logs_days=3

# 软件安装目录
basedir=/usr/local/mysql

# 数据存储目录
datadir=/home/hadoop/mysql

# 日志文件
log-error=/var/log/mysqld.log

# socket 文件
socket=/home/hadoop/mysql/mysql.sock

# pid 文件
pid-file=/home/hadoop/mysql/mysqld.pid

# 表名存储在磁盘是小写的,但是比较的时候是不区分大小写
lower_case_table_names=1

# 慢查询日志
slow_query_log=on
slow_query_log_file=/var/log/mg-slow.log

# 慢查询的阀值 1 秒
long_query_time=1

# 日志时区
log_timestamps=SYSTEM

# 字符编码
character_set_server=utf8

# 禁用符号链接 
skip_symbolic_links=yes

# 交互式连接超时时间
interactive_timeout=86400

# 非交互式连接超时时间
wait_timeout=86400

# 锁超时时间
lock_wait_timeout=86400

# 禁用反向dns解析
skip_name_resolve=1

# 允许宽松模式的语法
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

# 最大连接数
max_connections=10000

# 最大连接错误数量
max_connect_errors=100000

# 最大打开文件限制
open_files_limit=65535

# TIMESTAMP列处理方式
explicit_defaults_for_timestamp=true


[mysqld_safe]
character_set_server=utf8
log-error=/var/log/mysqld.log
pid-file=/home/hadoop/mysql/mysqld.pid


[client]
port=3306
default-character-set=utf8
socket=/home/hadoop/mysql/mysql.sock


[mysql]
default-character-set=utf8
socket=/home/hadoop/mysql/mysql.sock


[mysql.server]
character_set_server=utf8
socket=/home/hadoop/mysql/mysql.sock


[mysqld.server]
character_set_server=utf8
socket=/home/hadoop/mysql/mysql.sock
EOF

# 创建日志文件并授权
touch /var/log/{mysqld.log,mg-slow.log}
chown mysql:mysql /var/log/{mysqld.log,mg-slow.log}

# 创建 pid 目录,授权
mkdir -p /var/run/mysqld
chown -Rf mysql:mysql /var/run/mysqld

# 初始化mysql
/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf \
           --initialize-insecure --user=mysql --datadir=/home/hadoop/mysql --ssl

5. 配置 mysql 用户打开文件限制

grep -q mysql /etc/security/limits.conf || cat <<EOF  >> /etc/security/limits.conf

# mysql
mysql hard nofile 65535
mysql soft nofile 65535
EOF

6. 配置 mysql 服务

cat <<EOF   > /usr/lib/systemd/system/mysqld.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(7)
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

Type=forking
PIDFile=/home/hadoop/mysql/mysqld.pid

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

# Use this to switch malloc implementation
EnvironmentFile=-/etc/sysconfig/mysql

# Start main service
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --daemonize \$MYSQLD_OPTS 

# Sets open_files_limit
LimitNOFILE = 65535

Restart=always
RestartSec=10

RestartPreventExitStatus=1 

PrivateTmp=false
EOF

7. 启动服务,跟随系统启动

systemctl daemon-reload
systemctl enable --now mysqld
systemctl restart mysqld

8. 初始化密码

# 配置密码(仅本机访问)
mysql -e "ALTER USER 'root'@'localhost' IDENTIFIED BY 'mypasswd'"

# 配置密码(指定远程IP访问)
mysql -e "ALTER USER 'root'@'192.168.100.2' IDENTIFIED BY 'mypasswd'"

# 配置密码(允许所有IP访问)
mysql -e "ALTER USER 'root'@'%' IDENTIFIED BY 'mypasswd'"

RPM包安装(centos-7)

1. 安装依赖包

yum install -y wget libaio perl

2. 下载、解压安装包

# 下载安装包
mysql_ver=5.7.38
wget https://cdn.mysql.com/archives/mysql-5.7/mysql-${mysql_ver}-1.el7.x86_64.rpm-bundle.tar

# 解压安装包
mkdir -p mysql-${mysql_ver}
tar xf mysql-5.7.38-1.el7.x86_64.rpm-bundle.tar -C mysql-${mysql_ver}

3. 卸载 mariadb-libs

yum remove -y mariadb-libs

4. 安装 mysql

cd mysql-${mysql_ver}
rpm -ivh mysql-community-common-${mysql_ver}-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-${mysql_ver}-1.el7.x86_64.rpm 
rpm -ivh mysql-community-libs-compat-${mysql_ver}-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-${mysql_ver}-1.el7.x86_64.rpm
rpm -ivh mysql-community-devel-${mysql_ver}-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-${mysql_ver}-1.el7.x86_64.rpm

5. 初始化 mysql

mysqld --defaults-file=/etc/my.cnf --initialize-insecure --user=mysql --ssl

6. 启动 mysql 服务

systemctl enable --now mysqld
systemctl restart mysqld

7. 初始化密码

# 配置密码(仅本机访问)
mysql -e "ALTER USER 'root'@'localhost' IDENTIFIED BY 'mypasswd'"

# 配置密码(指定远程IP访问)
mysql -e "ALTER USER 'root'@'192.168.100.2' IDENTIFIED BY 'mypasswd'"

# 配置密码(允许所有IP访问)
mysql -e "ALTER USER 'root'@'%' IDENTIFIED BY 'mypasswd'"

Docker 容器部署 MySQL

version: '3'
services:

  mysql:
    image: mysql:5.7.38
    restart: always
    hostname: mysql
    container_name: mysql
    ports:
      - 3306:3306
    command:
      - mysqld
      - --server-id=1
      - --log_timestamps=SYSTEM
      - --symbolic-links=0
      - --skip-host-cache
      - --skip-name-resolve
      - --wait_timeout=30000
      - --max_connections=3600
      - --interactive_timeout=30000
      - --lower_case_table_names=1
      - --character_set_server=utf8
      - --default-storage-engine=INNODB
      - --explicit_defaults_for_timestamp
      - --disable-partition-engine-check
      - --log_bin_trust_function_creators=1
      - --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
    environment:
      - MYSQL_ROOT_PASSWORD=mypasswd
    volumes:
      - ./mysql-data:/var/lib/mysql
      - /etc/localtime:/etc/localtime:rw
      # - ./conf/mysql_init.sql:/docker-entrypoint-initdb.d/mysql_init.sql
    deploy:
      resources:
        limits:
          cpus: "2"
          memory: 2048M
    healthcheck:
      test: mysqladmin ping -h 127.0.0.1 --silent || exit 1
      start_period: 30s
      interval: 30s
      timeout: 5s
      retries: 3