目录

二进制安装MySQL5.7

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

准备工作

卸载系统中的MySQL或mariadb

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直接下载

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

创建用户

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

安装依赖包

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

设置安装路径

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

解压安装包

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}

初始化数据库

创建数据保存目录

mkdir -p ${DATA_DIR}/{binlog,data,logs,redolog,relaylog,tmp,undolog}
mkdir -p ${BASE_DIR}/etc
chown -R mysql.mysql ${DATA_DIR}
chown -R mysql.mysql ${BASE_DIR}
chown -R mysql.mysql ${BASE_DIR}-5.7.39-linux-glibc2.12-x86_64/

initialize

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

配置文件与服务

创建最小化配置文件

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                   = 1G
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

ln -s ${BASE_DIR}/etc/my.cnf /etc/my.cnf

生成systemd配置

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

启动服务

systemctl enable --now mysql

优化工作

添加环境变量

echo "export PATH=\$PATH:${BASE_DIR}/bin" >> /etc/profile

修改root密码

alter user 'root'@'localhost' IDENTIFIED BY 'password';
CREATE USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'password';