安装环境准备

• 清除旧版MySQL

如果之前操作系统存在MySQL，则使用本机二进制文件安装时可能会遇到问题。
因此在安装之前，确保系统中的MySQL已完全删除，以及任何其他文件（例如旧版本的数据文件）也已删除，另外 /etc/my.cnf 或 /etc/mysql 目录等配置文件并应将其删除。
[root@localhost ~]# rpm -qa | grep mysql
[root@localhost ~]# rpm -qa | grep mariadb
mariadb-libs-5.5.68-1.el7.x86_64
[root@localhost ~]# rpm -qa | grep percona
如果存在的话通过rpm -e 清除。
复制

• 检查依赖包环境

MySQL依赖于libaio库，如果未在本地安装此库，则数据目录初始化和后续服务器启动步骤将失败。
[root@localhost ~]# rpm -qa | grep libaio
libaio-0.3.109-13.el7.x86_64

对于MySQL 5.7.19及更高版本，对非统一内存访问(NUMA)的支持已添加到通用Linux构建中，该构建现在依赖于libnuma库。
[root@localhost ~]# yum search libnuma
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
 * base: mirrors.163.com
 * extras: mirrors.163.com
 * updates: ftp.sjtu.edu.cn
========================================================================= N/S matched: libnuma ==========================================================================
numactl-libs.i686 : libnuma libraries
numactl-libs.x86_64 : libnuma libraries
[root@localhost ~]# rpm -qa | grep numactl
numactl-libs-2.0.12-5.el7.x86_64

从MySQL 5.7.19开始，Linux通用压缩包格式是EL6而不是EL5，此时MySQL客户端 bin/mysql 需要libtinfo.so.5，解决方法如下：
[root@localhost lib64]# ln -s libtinfo.so.5.9 /lib64/libtinfo.so.5
复制

• 关闭防火墙

[root@localhost ~]# systemctl stop firewalld.service 
[root@localhost ~]# systemctl disable firewalld.service 
Removed symlink /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed symlink /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
复制

• 关闭SELinux

[root@localhost ~]# vi /etc/sysconfig/selinux 
SELINUX=disabled
[root@localhost ~]# setenforce 0
复制

• 修改资源限制参数

[root@localhost ~]# vi /etc/security/limits.conf

* soft nproc 65535
* hard nproc 65535
* soft nofile 65535
* hard nofile 65535
* soft stack 65535
* hard stack 65535

重启系统生效。
复制

MySQL安装

• 下载安装包
  下载链接：https://dev.mysql.com/downloads/mysql/

• 创建mysql目录

[root@localhost ~]# mkdir -p /mysql/data
[root@localhost ~]# mkdir -p /mysql/log
[root@localhost ~]# mkdir -p /mysql/app
复制

• 上传安装程序

sftp:/root> cd /mysql/app/
sftp:/mysql/app> put F:\system\mysql\Packages\mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz
Uploading mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz to remote:/mysql/app/mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz
sftp: sent 635 MB in 62.61 seconds
复制

• 创建mysql用户及组

[root@localhost ~]# groupadd mysql
[root@localhost ~]# useradd -r -g mysql -s /bin/false mysql
***参数解析***
-r 添加一个系统用户
-g 指定用户所属组
-s 指定用户登录shell，/bin/false表示禁止该用户登录，由于mysql数据库默认由root用户创建，创建mysql用户用于执行，为加强安全，禁止使用mysql用户登录
复制

• 解压安装程序包

[root@localhost ~]# cd /mysql/app/
[root@localhost app]# ls
mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz
[root@localhost app]# tar -zxvf mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz

注：
可为解压后的目录创建一个软链接，方便管理维护。
[root@localhost app]# ln -s mysql-5.7.37-linux-glibc2.12-x86_64 mysql
[root@localhost app]# ls -li
total 650984
102731847 lrwxrwxrwx. 1 root root        35 Jan 21 15:03 mysql -> mysql-5.7.37-linux-glibc2.12-x86_64
  4014927 drwxr-xr-x. 9 root root       142 Jan 21 15:03 mysql-5.7.37-linux-glibc2.12-x86_64
105108677 -rw-r--r--. 1 root root 666603757 Jan 21 14:14 mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz
复制

• 配置环境变量

[root@localhost ~]# vi .bash_profile

PATH=$PATH:/mysql/app/mysql/bin:$HOME/bin

source ~/.bash_profile
复制

• 修改目录权限

[root@localhost ~]# chown -R mysql:mysql /mysql/
[root@localhost ~]# chmod -R 750 /mysql/
复制

安装后设置

• 准备参数文件

数据库启动默认使用/etc/my.cnf参数文件，读取位置顺序如下：
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
一般使用自定义参数文件，数据库启动时指定自定义的参数文件即可。

vi /mysql/app/my.cnf

[client]
port=3306
socket	= /mysql/data/mysql.sock

[mysql]
no-beep
prompt="\u@mysqldb \R:\m:\s [\d]> "
#no-auto-rehash
auto-rehash
default-character-set=utf8


[mysqld]
########basic settings########
server-id=3306
port=3306
user = mysql
bind_address= 192.168.66.5
basedir=/mysql/app/mysql
datadir=/mysql/data
socket	= /mysql/data/mysql.sock
pid-file=/mysql/data/mysql.pid
character-set-server=utf8
skip-character-set-client-handshake=1
autocommit = 0
#skip_name_resolve = 1
max_connections = 800
max_connect_errors = 1000
default-storage-engine=INNODB
transaction_isolation = READ-COMMITTED
explicit_defaults_for_timestamp = 1
sort_buffer_size = 32M
join_buffer_size = 128M
tmp_table_size = 72M
max_allowed_packet = 16M
sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"
interactive_timeout = 1800
wait_timeout = 1800
read_buffer_size = 16M
read_rnd_buffer_size = 32M

query_cache_type = 1
query_cache_size=1M
table_open_cache=2000
thread_cache_size=768
myisam_max_sort_file_size=10G
myisam_sort_buffer_size=135M
key_buffer_size=32M
read_buffer_size=8M
read_rnd_buffer_size=4M

back_log=1024
#flush_time=0
open_files_limit=65536
table_definition_cache=1400

########log settings########
log-output=FILE
general_log = 0
general_log_file=/mysql/log/mysqldb-general.err
slow_query_log = ON
slow_query_log_file=/mysql/log/mysqldb-query.err
long_query_time=10
log-error=/mysql/log/mysqldb-error.err

log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1
log_throttle_queries_not_using_indexes = 10
expire_logs_days = 90
min_examined_row_limit = 100

########innodb settings########
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_buffer_pool_size = 12800M  
innodb_buffer_pool_instances = 8
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_lru_scan_depth = 2000
innodb_lock_wait_timeout = 5
#innodb_flush_method = O_DIRECT

innodb_log_file_size = 200M
innodb_log_files_in_group = 2 
innodb_log_buffer_size = 16M

innodb_undo_logs = 128
innodb_undo_tablespaces = 3
innodb_undo_log_truncate = 1
innodb_max_undo_log_size = 2G

innodb_flush_neighbors = 1
innodb_purge_threads = 4
innodb_large_prefix = 1
innodb_thread_concurrency = 64
innodb_print_all_deadlocks = 1
innodb_strict_mode = 1
innodb_sort_buffer_size = 64M
innodb_flush_log_at_trx_commit=1
innodb_autoextend_increment=64
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_open_files=65536
innodb_stats_on_metadata=0
innodb_file_per_table=1   
innodb_checksum_algorithm=0
innodb_data_file_path=ibdata1:200M;ibdata2:200M;ibdata3:200M:autoextend:max:5G
innodb_temp_data_file_path = ibtmp1:200M:autoextend:max:20G

innodb_buffer_pool_dump_pct = 40
innodb_page_cleaners = 4
innodb_purge_rseg_truncate_frequency = 128
binlog_gtid_simple_recovery=1
log_timestamps=system
show_compatibility_56=on
复制

• 初始化mysql

[root@localhost ~]# mysqld --defaults-file=/mysql/app/my.cnf --initialize --user=mysql --basedir=/mysql/app/mysql --datadir=/mysql/data

mysql初始化后会生成一个临时的数据库初始密码，记录在error日志中：
[root@localhost ~]# cat /mysql/log/mysqldb-error.err | grep password
2022-01-24T13:14:21.224471+08:00 1 [Note] A temporary password is generated for root@localhost: tojhE.9kkIxZ
复制

• 启动mysql

[root@localhost ~]# mysqld_safe --defaults-file=/mysql/app/my.cnf --user=mysql &
[1] 3446
2022-01-24T05:20:40.882440Z mysqld_safe Logging to '/mysql/log/mysqldb-error.err'.
2022-01-24T05:20:41.017304Z mysqld_safe Starting mysqld daemon with databases from /mysql/data

[root@localhost init.d]# ps -ef | grep mysql
root      7326  1895  0 14:41 pts/0    00:00:00 /bin/sh /mysql/app/mysql/bin/mysqld_safe --defaults-file=/mysql/app/my.cnf --user=mysql
mysql     8463  7326  7 14:42 pts/0    00:00:01 /mysql/app/mysql/bin/mysqld --defaults-file=/mysql/app/my.cnf --basedir=/mysql/app/mysql --datadir=/mysql/data --plugin-dir=/mysql/app/mysql/lib/plugin --user=mysql --log-error=/mysql/log/mysqldb-error.err --open-files-limit=65536 --pid-file=/mysql/data/mysql.pid --socket=/mysql/data/mysq.sock --port=3306
root      8495  1895  0 14:42 pts/0    00:00:00 grep --color=auto mysql
复制

• 登录mysql

[root@localhost ~]# mysql -uroot -p
Enter password: 
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

socket文件用于连接数据库使用，mysql数据库连接默认使用/tmp/mysql.sock，但是我们在参数文件中指定的socket文件在/mysql/data/路径下，因此报错，可使用以下两种方式解决：
1、为socket文件创建一个软链接：
[root@localhost ~]# ln -s /mysql/data/mysql.sock /tmp/mysql.sock
[root@localhost ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.37-log

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
2、登录mysql时指定socket文件：
[root@localhost ~]# mysql -uroot -ptojhE.9kkIxZ -S /mysql/data/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.37-log

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
复制

• 修改root密码

mysql> set password='rootPWD123';
Query OK, 0 rows affected (0.00 sec)
复制

• 配置远程登录

mysql> grant all privileges on *.* to 'root'@'%' identified by 'rootPWD123' with grant option;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
复制

• 配置mysql启动服务

1、配置启动脚本
[root@localhost ~]# cp /mysql/app/mysql/support-files/mysql.server /mysql/app/mysql/support-files/mysql
[root@localhost ~]# vi /mysql/app/mysql/support-files/mysql
添加basedir和datadir文件路径：
basedir=/mysql/app/mysql
datadir=/mysql/data
添加pid-file文件路径：
mysqld_pid_file_path=/mysql/data/mysql.pid
在start脚本处添加默认参数文件：
$bindir/mysqld_safe --defaults-file=/mysql/app/my.cnf --user=mysql $other_args >/dev/null &

2、将编辑好的mysql文件拷贝到/etc/init.d/路径下
[root@localhost ~]# cp /mysql/app/mysql/support-files/mysql /etc/init.d/mysql

3、运行命令启停mysql
[root@localhost support-files]# service mysql stop
Shutting down MySQL.. SUCCESS! 
[root@localhost support-files]# service mysql start
Starting MySQL.... SUCCESS!
复制