MySQL【部署 02】Linux 非 root 用户部署 mysql-5.7.28 设置开机启动及问题汇总（含云盘资源）_mysql 不用root用户启动

之前分享了《Linux环境root用户安装 mysql-5.7.28》，很多时候我们部署的应用或者数据库的服务器是客户方的，我们可能没有root权限，所有这里分享一下非root权限如何安装MySQL数据库。

• mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz

链接：https://pan.baidu.com/s/1DmavSo3kCKOPZtmd-FE8mw
提取码：0v5l

安装

（首先也要检查一下环境，这里不再赘述）这里使用的是【非root用户】admin用户来安装：

1.上传并解压

tar -zxvf mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz 
mv mysql-5.7.28-linux-glibc2.12-x86_64 mysql-5.7.28
cd mysql-5.7.28
1
2
3

2.新增配置文件my.cnf

配置文件内容大家一看便知，内容如下：

vi my.cnf

[mysqld]
port=3306
basedir=/data/mysql-5.7.28
datadir=/data/mysql-5.7.28/data
pid-file=/data/mysql-5.7.28/mysql.pid
#socket=/data/mysql-5.7.28/mysql.sock
log_error=/data/mysql-5.7.28/logs/error.log
server-id=100
max_connections=200
default-storage-engine=INNODB
lower_case_table_names=1
max_allowed_packet=64M
skip-name-resolve
sql-mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE"

# binlog
log-bin = /data/mysql-5.7.28/logs/mysql-bin.log
expire-logs-days = 14
max-binlog-size= 500M
server-id = 1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

3.安装

这里是不同于root用户安装MySQL的地方，我们没有创建MySQL用户组和用户，使用的是admin用户下的bin/mysqld命令和我们自己编写的my.cnf文件初始化数据库：

cd /data/mysql-5.7.28
# 这几个文件夹要创建
mkdir binlog data scripts logs
# 使用my.cnf来初始化数据库 初始化的用户名是admin【这个用户就是当前非root用户】
bin/mysqld --defaults-file=/data/mysql-5.7.28/my.cnf --initialize --user=admin --basedir=/data/mysql-5.7.28 --datadir=/data/mysql-5.7.28/data
1
2
3
4
5

4.启动

bin/mysqld_safe --defaults-file=/data/mysql-5.7.28/my.cnf  --user=admin &
1

5.获取密码

克隆会话打开另一个窗口，进入admin用户的mysql目录：

cd /data/mysql-5.7.28
cat logs/error.log | grep root@localhost
2021-07-15T09:04:55.479616Z 1 [Note] A temporary password is generated for root@localhost: +oUisNmwk6:e
1
2
3

6.登陆

输入从error.log拿到的密码： +oUisNmwk6:e

bin/mysql -u root -p
# 这里输入
1
2

7.修改密码

set password for 'root'@'localhost' = password('123456');
grant all privileges on *.* to root@'%' identified by '123456';
flush privileges;
quit;
1
2
3
4

以下是mysql8.0以后：

alter user 'root'@'localhost' identified by 'root';
flush privileges;
1
2

8.关闭数据库

ps -ef |grep mysql
# 这里kill掉相应的进程
1
2

9.添加防火墙端口

生产环境的防火墙还是很有用的：

sudo /sbin/iptables -I INPUT -p tcp --dport 3306 -j ACCEPT
sudo /etc/rc.d/init.d/iptables save
sudo /etc/rc.d/init.d/iptables restart
sudo /etc/init.d/iptables status
1
2
3
4

10.添加普通用户

特别注意： 以下操作需要在服务器上执行，否则赋权无效：

CREATE USER 'customUser'@'%' IDENTIFIED BY 'customUser'; 
GRANT ALL PRIVILEGES ON *.* TO 'customUser'@'%' IDENTIFIED BY 'customUser' WITH GRANT OPTION;
flush privileges;
quit;
1
2
3
4

11.设置开机启动

vim scripts/is_start_status_mysql.sh
# 内容如下：
#!/bin/bash
#
# Author: Created by lww
# filename: /data/mysql/scripts/is_start_status_mysql.sh
# Date: 2019-05-14
# Description: This script is used to start mysql server.
# Version:1.1
#
#####################################################################################
is_start_status=`ps -ef|grep -Ew 'mysqld|mysqld_safe' | grep -vw 'grep' |  wc -l`

if [[ "$is_start_status" -ne 2 ]]; then
    sleep 10
    /data/mysql-5.7.28/bin/mysqld_safe --defaults-file=/data/mysql-5.7.28/my.cnf &
fi
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

vim /etc/rc.local
# 内容如下：
#!/bin/sh
#
# This script will be executed *after* all the other init scripts.
# You can put your own initialization stuff in here if you don't
# want to do the full Sys V style init stuff.

touch /var/lock/subsys/local
/usr/bin/nohup /usr/bin/iostat -dxkt 1  > /tmp/iostat_output 2>/dev/null &
ulimit -SHn 65535

#auto start mysql
su - admin -c /data/mysql-5.7.28/scripts/is_start_status_mysql.sh
1
2
3
4
5
6
7
8
9
10
11
12
13
14

问题汇总

文件权限问题：

1. mysqld_safe error: log-error set to ‘/var/log/mariadb/mariadb.log’, however file don’t exists. Create writable for user ‘mysql’.
2. /usr/local/mysql/bin/mysqld: Can’t create/write to file ‘/var/run/mariadb/mariadb.pid’ (Errcode: 2 - No such file or directory)

# mysqld_safe error: log-error set to '/var/log/mariadb/mariadb.log', however file don't exists. Create writable for user 'mysql'.

# 解决
mkdir /var/log/mariadb
touch /var/log/mariadb/mariadb.log
chown -R mysql:mysql /var/log/mariadb/

# /usr/local/mysql/bin/mysqld: Can't create/write to file '/var/run/mariadb/mariadb.pid' (Errcode: 2 - No such file or directory)

# 文件权限问题，一样的问题，没有创建该目录和权限
mkdir /var/run/mariadb/
chown -R mysql:mysql /var/run/mariadb/
1
2
3
4
5
6
7
8
9
10
11
12

其他问题：
3. SSL error: Unable to get private key from ‘server-key.pem’
4. Can’t start server: Bind on TCP/IP port: Address already in use

# [ERROR] SSL error: Unable to get private key from 'server-key.pem'

# 解决
find / -name server-key.pem
chown -R mysql:mysql /var/lib/mysql/

# [ERROR] Can't start server: Bind on TCP/IP port: Address already in use

# 解决 关闭占用的端口即可
netstat -nat |grep ":port"
1
2
3
4
5
6
7
8
9
10