如有特殊需求需要编译安装 MySQL 请看 编译安装MySQL5.7

准备工作

卸载系统中的 MySQL 或 mariadb

1
2
3
rpm -qa | grep mysql
rpm -qa | grep mariadb
yum remove xxx

下载二进制安装包

MySQL官网

选择操作系统为 Linux-Generic

选择下载文件名为：mysql-5.7.39-linux-glibc2.12-x86_64.tar.gz

当然也可以用 wget 直接下载

1
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.39-linux-glibc2.12-x86_64.tar.gz

创建用户

1
useradd -M -s /sbin/nologin -r mysql

安装依赖包

为了避免执行 mysql 命令报错 ibncurses.so.5 或者 libnuma.so.1 找不到

1
yum install -y ncurses-devel numactl-libs

注意： CentOS 9 或 RHEL 9 如果找不到这个库，可以参考：解决MySQL 5.7在Redhat 9中启动报错：libncurses.so.5和libtinfo.so.5缺失问题

1
apt install -y libncurses5 libnuma1 libaio1

设置安装路径

1
2
export BASE_DIR="/usr/local/mysql"
export DATA_DIR="/data/mysql"

解压安装包

1
2
tar -xf mysql-5.7.39-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
ln -s ${BASE_DIR}-5.7.39-linux-glibc2.12-x86_64/ ${BASE_DIR}

初始化数据库

创建数据保存目录

1
2
3
4
5
6
7
mkdir -p ${DATA_DIR}/{binlog,data,logs,redolog,relaylog,tmp,undolog}
mkdir -p ${BASE_DIR}/etc
chown -R mysql.mysql ${DATA_DIR}
touch ${DATA_DIR}/logs/error_mysqld.log
chown -R mysql.mysql ${DATA_DIR}/logs/error_mysqld.log
chown -R mysql.mysql ${BASE_DIR}
chown -R mysql.mysql ${BASE_DIR}-5.7.39-linux-glibc2.12-x86_64/

initialize

1
2
${BASE_DIR}/bin/mysqld --initialize --user=mysql --basedir=${BASE_DIR} --datadir=${DATA_DIR}/data
# 控制台会输出一个临时密码，需要记录一下

配置文件与服务

创建最小化配置文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
cat > ${BASE_DIR}/etc/my.cnf <<-EOF
## MySQL 5.7 Configuration File

[mysqld]

## General
user                                   = mysql
bind_address                           = 0.0.0.0
port                                   = 3306
basedir                                = ${BASE_DIR}/
datadir                                = ${DATA_DIR}/data
tmpdir                                 = ${DATA_DIR}/tmp
socket                                 = ${DATA_DIR}/logs/mysql.sock
pid-file                               = ${DATA_DIR}/logs/mysqld.pid

character_set_server                   = utf8

skip-symbolic-links
skip_name_resolve                      = ON
skip_external_locking                  = ON

performance_schema                     = ON      # default ON
performance-schema-instrument          = 'memory/%=ON'
#lower_case_table_names                 = 0      # default 0


### Storage Engines
default_storage_engine                 = InnoDB

## InnoDB
innodb_log_group_home_dir              = ${DATA_DIR}/redolog/
innodb_log_file_size                   = 256M
innodb_log_files_in_group              = 2
innodb_log_buffer_size                 = 16M
innodb_rollback_segments               = 128     # defautl 128
innodb_undo_directory                  = ${DATA_DIR}/undolog/
innodb_open_files                      = 4000
innodb_thread_concurrency              = 32
innodb_flush_log_at_trx_commit         = 1
innodb_flush_log_at_timeout            = 1       # defautl 1, when innodb_flush_log_at_trx_commit = 0 or 2
innodb_purge_threads                   = 4
innodb_print_all_deadlocks             = ON
innodb_max_dirty_pages_pct             = 70
innodb_lock_wait_timeout               = 50
innodb_flush_method                    = O_DIRECT
innodb_old_blocks_time                 = 1000    # since 5.6.6 default 1000
innodb_io_capacity                     = 600     # default 200
innodb_io_capacity_max                 = 2000    # default 2000
innodb_lru_scan_depth                  = 1024    # default 1024
innodb_read_io_threads                 = 8
innodb_write_io_threads                = 8
innodb_buffer_pool_load_at_startup     = ON
innodb_buffer_pool_dump_at_shutdown    = ON
innodb_buffer_pool_filename            = ib_buffer_pool  # default ib_buffer_pool
innodb_sort_buffer_size                = 64M     # default 1M , 64K - 64M , for change index

innodb_buffer_pool_dump_pct            = 40
innodb_page_cleaners                   = 16
innodb_undo_log_truncate               = ON
innodb_max_undo_log_size               = 2G
innodb_purge_rseg_truncate_frequency   = 128

## Gtid
#gtid_mode                              = ON
#enforce_gtid_consistency               = ON
#binlog_gtid_simple_recovery            = 1

## Replication
server_id                              = 101
log_bin                                = ${DATA_DIR}/binlog/mysql_bin
expire_logs_days                       = 7
binlog_format                          = ROW
binlog_row_image                       = noblob  # default full
#innodb_autoinc_lock_mode               = 2      # default 1
binlog_rows_query_log_events           = 1
max_binlog_size                        = 500M
binlog_cache_size                      = 1M
sync_binlog                            = 1
master_info_repository                 = TABLE
relay_log_info_repository              = TABLE
skip-slave-start                       = 1
relay_log                              = ${DATA_DIR}/relaylog/relay_log
max_relay_log_size                     = 500M   # default 0, use max_binlog_size
log_slave_updates                      = ON     # default OFF
slave_transaction_retries              = 128

## MTS
relay_log_recovery                     = 1

## Logging
log_output                             = FILE
slow_query_log                         = ON
slow_query_log_file                    = ${DATA_DIR}/logs/slow_mysqld.log
log_queries_not_using_indexes          = OFF     # default OFF
log_throttle_queries_not_using_indexes = 10      # default 0
min_examined_row_limit                 = 0       # default 0
log_slow_admin_statements              = ON
log_slow_slave_statements              = ON
long_query_time                        = 1
#log-short-format                       = 0
log_error                              = ${DATA_DIR}/logs/error_mysqld.log
general_log                            = OFF
general_log_file                       = ${DATA_DIR}/logs/general_mysqld.log
log_timestamps                         = system

## Index
ft_min_word_len                        = 4

[mysqld_safe]
open_files_limit                       = 65535

[mysql]
no_auto_rehash
prompt                                 = "MySQL [\\d] > "

[mysqldump]
quick
max_allowed_packet                     = 256M

[mysqlhotcopy]
interactive_timeout

[client]
socket                                 = ${DATA_DIR}/logs/mysql.sock

EOF

做软链接

1
2
mv /etc/my.cnf{,.bak}
ln -s ${BASE_DIR}/etc/my.cnf /etc/my.cnf

生成 systemd 配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
cat > /usr/lib/systemd/system/mysql.service <<-EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=https://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
ExecStart=${BASE_DIR}/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 5000
EOF

启动服务

1
systemctl enable --now mysql

优化工作

添加环境变量

1
2
3
4
5
6
7
if [ "$( grep "${BASE_DIR}/bin" /etc/profile | wc -l )" -eq 0 ]; then
  echo "export PATH=\$PATH:${BASE_DIR}/bin" >> /etc/profile
else
  echo "PATH变量已添加"
fi

source /etc/profile

修改 root 密码

1
2
# 第一次登录需要使用root@localhost登录，密码为上面生成的临时密码
mysql -uroot -hlocalhost -p
1
2
3
ALTER USER 'root'@'localhost' IDENTIFIED BY 'Vb6CAEJqqtcmKndiAkEl';
CREATE USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'Vb6CAEJqqtcmKndiAkEl';
Grant all privileges on *.* to 'root'@'%' with grant option;