JiaHe's Blog

读万卷书,行万里路

MySQL

压缩包安装

下载 https://downloads.mysql.com/archives/community/
安装 https://dev.mysql.com/doc/refman/5.7/en/binary-installation.html

创建一个mysql用户和组
groupadd mysql
useradd -r -g mysql -s /bin/false mysql
/usr/local
cd /home
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz
tar zxf mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz
ln -s /home/mysql-5.7.34-linux-glibc2.12-x86_64 /usr/local/mysql
export PATH=$PATH:/usr/local/mysql/bin

数据目录初始化

echo "[mysqld]
basedir=/opt/mysql/mysql
datadir=/opt/mysql/mysql/data" > /opt/mysql/mysql/etc/my.cnf

# 使用 --initialize-insecure 来初始化数据目录,则无需密码即可以 root 身份连接到服务器
bin/mysqld --defaults-file=/opt/mysql/mysql/etc/my.cnf
--initialize-insecure --user=mysql

初始化后 root 密码分配

mysql -u root --skip-password

ALTER USER 'root'@'localhost' IDENTIFIED BY 'your-root-password';
CREATE USER 'root'@'127.0.0.1' IDENTIFIED BY 'your-root-password';
CREATE USER 'root'@'::1' IDENTIFIED BY 'your-root-password';
配置my.cnf
mkdir -p /usr/local/mysql/etc/

echo "[mysqld]
user=mysql
server-id=1
port = 3306
character_set_server=utf8mb4

datadir=/mnt/mysql/mysql_data
tmpdir=/mnt/mysql/mysql_tmp

default-storage-engine=INNODB

join_buffer_size = 512M
tmp_table_size = 1G
max_allowed_packet = 64M
interactive_timeout = 1800
# 默认8小时
wait_timeout = 1800
read_buffer_size = 128M
read_rnd_buffer_size = 256M
sort_buffer_size = 256M
key_buffer_size=512M
back_log=500
flush_time=0
open_files_limit=4161
table_definition_cache=1400
binlog_row_event_max_size=16M

# MySQL服务器最大连接数
max_connections=1000
max_connect_errors = 1000

# MySQL服务器线程缓存大小
thread_cache_size=500

# 日志缓冲刷新的频繁程度
innodb_flush_log_at_trx_commit=2
innodb_buffer_pool_size=2G
innodb_log_file_size=512M
innodb_log_buffer_size=256M
innodb_thread_concurrency=32
innodb_autoextend_increment=64
innodb_buffer_pool_instances=8
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_open_files=300
innodb_stats_on_metadata=0
innodb_file_per_table=1
innodb_checksum_algorithm=0
innodb_lock_wait_timeout = 50

# 瓶颈位,即使在并发量不高的时候也会出现,最好的选择是从一开始就禁用它
query_cache_size = 0

slow-query-log=1
long_query_time=10

lower_case_table_names=1
table_open_cache=4096

autocommit = 1
skip_name_resolve = 1
transaction_isolation = READ-COMMITTED
explicit_defaults_for_timestamp = 1

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0" >> /usr/local/mysql/etc/my.cnf

yum安装

https://dev.mysql.com/doc/refman/5.7/en/linux-installation-yum-repo.html

Docker安装MySQL

https://dev.mysql.com/doc/refman/5.7/en/linux-installation-docker.html

执行命令

# mysql数据存放位置
mkdir -p /home/docker/mysql/data

# 可以将用于数据库初始化的sql文件放到这个目录下,mysql安装时会进行执行。
mkdir -p /home/docker/mysql/init
将以下内容保存到 /home/docker/mysql/docker-compose.yml
/home/docker/mysql/docker-compose.yml
version: "3"
services:
mysql57:
container_name: mysql
image: mysql:5.7
restart: always
ports:
- 3306:3306
environment:
- TZ=Asia/Shanghai
- MYSQL_ROOT_PASSWORD=admin # 这里填写mysql密码
volumes:
- ./data:/var/lib/mysql
- ./my.cnf:/etc/mysql/mysql.conf.d/mysqld.cnf
- ./init:/docker-entrypoint-initdb.d
- ./healthcheck.sh:/home/healthcheck.sh
healthcheck:
test: ["CMD", "/bin/bash", "/home/healthcheck.sh"]
interval: 1m30s
timeout: 30s
retries: 3
将以下内容保存到 /home/docker/mysql/healthcheck.sh
/home/docker/mysql/healthcheck.sh
#!/bin/bash
result="$(service mysql status | grep 'is running')"
if [ "$result" ]; then
exit 0;
else
exit 1
fi
将以下内容保存到 /home/docker/mysql/my.cnf
/home/docker/mysql/my.cnf
[mysqld]
user=mysql

datadir = /var/lib/mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock

symbolic-links=0
default-storage-engine=INNODB
character_set_server=utf8mb4
collation-server=utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci'
default-time_zone='+8:00'
lower_case_table_names=1

[mysql]
default-character-set = utf8mb4

[mysql.server]
default-character-set = utf8mb4

[mysqld_safe]
default-character-set = utf8mb4

[client]
default-character-set = utf8mb4

执行命令

cd /home/docker/mysql
# 确保docker已经启动,若已经启动则忽略这条命令
systemctl restart docker
docker-compose up -d
docker exec -it mysql bash
# 配置mysql外部访问权限
egrep "mysql" /etc/group >& /dev/null
if [ $? -ne 0 ]
then
groupadd mysql
fi

id mysql >& /dev/null
if [ $? -ne 0 ]
then
useradd -g mysql mysql -s /usr/sbin/nologin
fi
一键安装
mkdir -p /home/docker/mysql/data
mkdir -p /home/docker/mysql/init

echo "
[mysqld]
user=mysql

datadir = /var/lib/mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock

symbolic-links=0
default-storage-engine=INNODB
character_set_server=utf8mb4
collation-server=utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci'
default-time_zone='+8:00'
lower_case_table_names=1

[mysql]
default-character-set = utf8mb4

[mysql.server]
default-character-set = utf8mb4

[mysqld_safe]
default-character-set = utf8mb4

[client]
default-character-set = utf8mb4" > /home/docker/mysql/my.cnf

echo "
#!/bin/bash
result=\"$(service mysql status | grep 'is running')\"
if [ \"$result\" ]; then
exit 0;
else
exit 1
fi" > /home/docker/mysql/healthcheck.sh

echo "
version: \"3\"
services:
mysql57:
container_name: mysql
image: mysql:5.7
restart: always
ports:
- 3306:3306
environment:
- TZ=Asia/Shanghai
- MYSQL_ROOT_PASSWORD=admin # 这里填写mysql密码
volumes:
- ./data:/var/lib/mysql
- ./my.cnf:/etc/mysql/mysql.conf.d/mysqld.cnf
- ./init:/docker-entrypoint-initdb.d
- ./sql:/opt/sql
- ./healthcheck.sh:/home/healthcheck.sh
healthcheck:
test: [\"CMD\", \"/bin/bash\", \"/home/healthcheck.sh\"]
interval: 1m30s
timeout: 30s
retries: 3
" > /home/docker/mysql/docker-compose.yml

cd /home/docker/mysql
systemctl restart docker
docker-compose up -d
docker exec -it mysql bash

egrep "mysql" /etc/group >& /dev/null
if [ $? -ne 0 ]
then
groupadd mysql
fi

id mysql >& /dev/null
if [ $? -ne 0 ]
then
useradd -g mysql mysql -s /usr/sbin/nologin
fi

exit