MySQL 二进制版安装与配置

wget https://dev.mysql.com/get/Downloads/MySQL-8.4/mysql-8.4.5-linux-glibc2.28-x86_64.tar.xz && \
sudo tar xvf mysql-8.4.5-linux-glibc2.28-x86_64.tar.xz -C /usr/local/src && \
sudo ln -s /usr/local/src/mysql-8.4.5-linux-glibc2.28-x86_64 /usr/local/mysql && \
export PATH=/usr/local/mysql/bin:$PATH

# On Debian:
sudo apt install -y libaio1 libnuma-dev libncurses-dev

# On Ubuntu:
sudo apt install -y libaio-dev libnuma-dev libncurses-dev
cd /usr/lib/x86_64-linux-gnu/
sudo ln -s libaio.so.1t64.0.2 libaio.so.1

# On Ubuntu/Debian:
sudo addgroup --system --gid 36 mysql && \
sudo adduser \
    --system \
    --home /var/lib/mysql \
    --shell /bin/false \
    --uid 36 \
    --gecos 'MySQL Server' \
    --gid 36 \
    --disabled-password \
    mysql

# On Fedora/RHEL/CentOS:
sudo groupadd --system mysql && \
sudo adduser \
   --system \
   --shell /bin/bash \
   --comment 'MariaDB server' \
   --gid mysql \
   --home-dir /var/lib/mysql \
   --create-home \
   mysql

sudo mkdir /var/log/mysql && \
sudo chown mysql:mysql /var/log/mysql

sudo mkdir /var/lib/mysql-files && \
sudo chown mysql:mysql /var/lib/mysql-files && \
sudo chmod 750 /var/lib/mysql-files

sudo mkdir /var/run/mysqld/ && \
sudo chown mysql:mysql /var/run/mysqld/

sudo mkdir -p /etc/mysql/conf.d/ && \
sudo chown -R mysql:mysql /etc/mysql/

sudo sh -c "cat > /etc/mysql/my.cnf" <<EOF
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.

!includedir /etc/mysql/conf.d/
EOF

sudo sh -c "cat > /etc/mysql/conf.d/client.cnf" <<EOF
# The MySQL database client configuration file
#
# Ref to https://dev.mysql.com/doc/refman/en/mysql-command-options.html

[mysql]
default-character-set=utf8mb4
socket=/var/run/mysqld/mysqld.sock
EOF

sudo sh -c "cat > /etc/mysql/conf.d/server.cnf" <<EOF
# The MySQL database server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# Here is entries for some specific programs
# The following values assume you have at least 8G ram

[mysqld]

# Basic Settings
user=mysql
bind-address=0.0.0.0
mysqlx-bind-address=0.0.0.0
port=3306
pid-file=/var/run/mysqld/mysqld.pid
socket=/var/run/mysqld/mysqld.sock
datadir=/var/lib/mysql
secure_file_priv=/var/lib/mysql-files
local_infile=OFF

# Character Settings
character-set-server=utf8mb4
character-set-filesystem=utf8mb4
collation-server=utf8mb4_general_ci

# InnoDB Settings
innodb_buffer_pool_size=2G

# Connection settings
max_connections=300

# Error Log
log_error=/var/log/mysql/error.log
log_error_verbosity=2

# General Query Log
general_log=0
general_log_file=/var/log/mysql/general_query.log

# Binary Log
log-bin=/var/log/mysql/mysql-bin
log-bin-index=/var/log/mysql/mysql-bin.index
binlog-do-db=mysql
binlog-ignore-db=test
binlog_expire_logs_seconds=604800
binlog_expire_logs_auto_purge=1
max_binlog_size=1G

# Slow Query Log
slow_query_log=1
slow_query_log_file=/var/log/mysql/slow_query.log
long_query_time=5.0
log_slow_admin_statements=1
log_queries_not_using_indexes=0
EOF

# 指定配置文件初始化
sudo /usr/local/mysql/bin/mysqld --defaults-file=/etc/mysql/my.cnf --initialize

# 不指定配置文件初始化
sudo /usr/local/mysql/bin/mysqld --initialize --user=mysql \
  --basedir=/usr/local/mysql \
  --datadir=/var/lib/mysql

sudo chmod 750 /var/lib/mysql && \
sudo chown mysql:mysql /var/lib/mysql

sudo vim /usr/lib/systemd/system/mysql.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

# Have mysqld write its state to the systemd notify socket
Type=notify

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

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

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

# Sets open_files_limit
LimitNOFILE = 10000

Restart=on-failure

RestartPreventExitStatus=1

# Set environment variable MYSQLD_PARENT_PID. This is required for restart.
Environment=MYSQLD_PARENT_PID=1

PrivateTmp=false

sudo mkdir /etc/sysconfig/ && \
sudo vim /etc/sysconfig/mysql

TZ=Asia/Shanghai
NO_INIT=true

sudo systemctl daemon-reload
sudo systemctl enable --now mysql.service
sudo systemctl disable mysql.service
systemctl status mysql
journalctl -u mysql.service

# 更改当前账号密码
mysql -u root -p
ALTER USER USER() IDENTIFIED BY 'password';

# 更改指定用户密码
ALTER USER 'myuser'@'localhost'
  IDENTIFIED BY 'password';

UPDATE mysql.user SET Host='%' WHERE Host='localhost' AND User='root';
FLUSH PRIVILEGES;
SELECT DISTINCT Host,User FROM mysql.user;

# 创建数据库
CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

# 创建用户
CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypasswd';

# 授权
GRANT ALL ON mydb.* TO 'myuser'@'localhost';

# 刷新生效
FLUSH PRIVILEGES;

mysqldump -u myuser -p mydb > mydb.sql
mysql -u myuser -p mydb < /var/lib/mysql-files/mydb.sql