解压安装

1
2
3
4
5
6
7
8
9
10
#先查找并卸载已经存在的软件包
rpm -qa mysql mysql-server mariadb mariadb-server
rpm -e 包名
cd /app/software
#解压并编译安装
https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.9-linux-glibc2.5-x86_64.tar.gz
tar -zxvf mysql-5.7.9-linux-glibc2.5-x86_64.tar.gz -C /app/service
mv /app/service/mysql-5.7.9-linux-glibc2.5-x86_64 /app/service/mysql
#创建日志文件夹
mkdir -pv /app/logs/mysql

使用以下命令添加环境变量：

1
2
3
4
echo "export MYSQL_HOME=/app/service/mysql" >> /etc/profile
echo "export PATH=\$PATH:\$MYSQL_HOME/bin" >> /etc/profile
#加载环境变量，使其生效
source /etc/profile

配置文件

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
vi /app/service/mysql/my.cnf

[mysqld]
user = app
sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

basedir = /app/service/mysql
datadir = /app/data/mysql
port = 3306
socket = /tmp/mysql.sock
#设置 utf8mb4 编码
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
skip-character-set-client-handshake
#INNODB
default-storage-engine=INNODB
innodb_buffer_pool_size=8G
innodb_log_file_size=256M
innodb_flush_method=O_DIRECT
#不区分大小写
lower_case_table_names = 1
#跳过 DNS 反查询
skip-name-resolve
default_password_lifetime=0
#跳过授权表，忘记密码时使用
#skip-grant-tables
#允许最大连接数
max_connections = 2048
open_files_limit = 65535
max-connect-errors = 100000
#错误日志
log-error = /app/logs/mysql/mysql-error.log
#慢查询
slow_query_log = 1
long_query_time = 10
#min_examined_row_limit = 1000
slow_query_log_file = /app/logs/mysql/mysql_slow_query.log
#日志时间戳格式
log_timestamps = SYSTEM
[mysql]
default-character-set = utf8mb4
socket = /tmp/mysql.sock

[client]
default-character-set = utf8mb4
socket = /tmp/mysql.sock

[mysqldump]
socket = /tmp/mysql.sock
  
[mysqladmin]
socket = /tmp/mysql.sock

输入以下命令初始化数据库服务

1
2
3
4
/app/service/mysql/bin/mysqld --defaults-file=/app/service/mysql/my.cnf --initialize --user=app --basedir=/app/service/mysql --datadir=/app/data/mysql
#初始化后从日志中查询初始化密码，记录下来方便后续使用或者修改密码
grep 'temporary password' /app/logs/mysql/mysql-error.log
/app/service/mysql/bin/mysqld_safe --defaults-file=/app/service/mysql/my.cnf &

使用 ALTER USER 和 SET PASSWORD 修改密码都报错

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

直接按忘记密码的方式进行重置：

1
2
3
4
5
6
7
8
#关闭服务，修改配置文件，跳过授权表，再启动服务
#/app/service/mysql/bin/mysql -uroot
UPDATE user SET authentication_string=password('BZ5mulg8PM') , password_expired='N' WHERE user='root';
SELECT authentication_string , password_expired FROM user WHERE user='root';
#再关闭服务，将配置文件中跳过授权表的部分注释掉，重新启动服务
#/app/service/mysql/bin/mysql -uroot -p'BZ5mulg8PM'
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'BZ5mulg8PM' WITH GRANT OPTION;
FLUSH PRIVILEGES;

相关命令

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
#拷贝命令（root 权限运行）
cp /app/service/mysql/support-files/mysql.server /app/service/mysql_service
#编辑脚本（root 权限运行）
vi /etc/init.d/mysql

basedir=/app/service/mysql
datadir=/app/data/mysql
conf=/app/service/mysql/my.cnf

#启动数据库
/etc/init.d/mysql start
#重启数据库
/etc/init.d/mysql restart
#停止数据库
/etc/init.d/mysql stop

其他命令

1
2
3
4
5
6
#添加相关命令的软连接（root 权限运行）
ln -s /app/service/mysql/bin/mysql* /usr/bin/
#本地登录
mysql -uroot -p'fjphty:BI96t'
#修改密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'kGXSu4fjF' PASSWORD EXPIRE NEVER;

修改日志的时间戳

1
2
SHOW GLOBAL VARIABLES LIKE 'log_timestamps';
SET GLOBAL log_timestamps = SYSTEM;