MySQL安装

安装

tar -zxvf mysql-5.7.43-el7-x86_64.tar.gz -C /usr/local/
cd /usr/local
mv mysql-5.7.43-el7-x86_64 mysql
cd /usr/lcoal/mysql
groupadd mysql
useradd -r -M -g mysql mysql
chown -R mysql:mysql ./

创建目录

mkdir /data
mkdir /data/mysql

备份并修改配置文件

cp /etc/my.cnf /etc/my.cnf.default.bak

vi /etc/my.cnf

修改配置文件

[mysqld]
socket=/usr/local/mysql/mysql.sock
user=mysql
symbolic-links=0
datadir=/data/mysql
pid-file=/usr/local/mysql/mysqld.pid
innodb_file_per_table=1

log-slave-updates = on

log-error=/usr/local/mysql/logs/mysql.err
log-bin=/usr/local/mysql/logs/mysql-bin
log-bin-index=/usr/local/mysql/logs/mysql-bin.index
relay-log=/usr/local/mysql/logs/relay-bin
relay-log-index=/usr/local/mysql/logs/relay-bin.index
binlog-do-db = zcms

innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 50

character-set-server=utf8
max_connections=20000

long_query_time = 1
slow_query_log=1
slow_query_log_file=/usr/local/mysql/logs/mysql-slow.log
expire_logs_days=7
query_cache_type = 1
query_cache_size = 10M

server-id=1
sync-binlog=1
lower_case_table_names=1

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[client]
port=3306
socket=/usr/local/mysql/mysql.sock

[mysqld_safe]
log-error=/usr/local/mysql/logs/mysqld.log
pid-file=/usr/local/mysql/mysqld.pid

初始化

./bin/mysqld --initialize --basedir=/usr/local/mysql --datadir=/data/mysql --user=mysql

报错

2023-12-26T08:27:15.600641Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2023-12-26T08:27:15.600750Z 0 [Warning] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2023-12-26T08:27:15.600758Z 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set.
2023-12-26T08:27:15.605611Z 0 [ERROR] Could not open file '/usr/local/mysql/logs/mysql.err' for error logging: No such file or directory
2023-12-26T08:27:15.605637Z 0 [ERROR] Aborting

新建文件夹

cd /usr/local/logs
mkdir logs
chown -R mysql:mysql ./

密码

cat /usr/local/mysql/logs/mysql.err

将mysql.server启动文件复制到/etc/init.d目录
/etc/init.d目录下的文件可以使用service命令操作

cp ./support-files/mysql.server /etc/init.d/mysqld
chmod +x /etc/init.d/mysqld
vi /etc/init.d/mysqld    ###调整启动脚本

修改内容

basedir=/usr/local/mysql
datadir=/data/mysql

启动

/etc/init.d/mysqld start

报错

Starting MySQL.2023-12-26T08:39:54.559617Z mysqld_safe error: log-error set to '/usr/local/mysql/logs/mysqld.log', however file don't exists. Create writable for user 'mysql'.
 ERROR! The server quit without updating PID file (/usr/local/mysql/mysqld.pid).

新建log文件

vi /usr/local/mysql/logs/mysqld.log #保存
chown -R mysql:mysql ./chown -R mysql:mysql /usr/local/mysql/logs/mysqld.log # 修改所有者

检查端口监听

ss -tnl|grep 3306

执行mysql安全加固脚本

./bin/mysql_secure_installation

Securing the MySQL server deployment.

Connecting to MySQL using a blank password.

VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?

Press y|Y for Yes, any other key for No: N   ###是否启用密码复杂度检查插件
Securing the MySQL server deployment.

Enter password for user root:    ###输入临时密码
New password:    ###输入新密码
Remove anonymous users? (Press y|Y for Yes, any other key for No) :Y   ###移除匿名用户
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : Y    ###禁止root远程登录
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : Y    ###移除测试数据库
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : Y    ###重载权限表

配置mysql环境变量

vi /etc/profile

添加并保存退出

export MYSQL_HOME=/usr/local/mysql
export PATH=$MYSQL_HOME/bin:$PATH

使修改的环境变量生效

source /etc/profile

Mysql操作：
启动mysql

service mysqld start

关闭mysql

service mysqld stop

重启mysql

service mysqld restart

查看mysql运行状态

service mysqld status

设置开机启动：
添加mysqld 到系统服务

chkconfig --add mysqld

查看mysqld添加到mysql的启动状态

chkconfig --list mysqld

mysqld 0:关闭 1:关闭 2:启用 3:启用 4:启用 5:启用 6:关闭
注意：0-6表示mysql的运行级别，可以在/etc/inittab中查看系统包含的运行级别。
0 - halt (Do NOT set initdefault to this)
1 - Single user mode
2 - Multiuser, without NFS (The same as 3, if you do not have networking)
3 - Full multiuser mode
4 - unused
5 - X11
6 - reboot (Do NOT set initdefault to this)
只需要3、5启动就行了，其它的不关心。
如果3、5运行级别没有设置开机启动，则需要添加3、5运行级别运行mysql

chkconfig --level 35 mysqld on

Mysql配置文件修改：
编辑配置文件
/etc/my.cnf
添加

[mysqld]
character_set_server=utf8   #修改数据库使用utf8编码
lower_case_table_names=1  #修改表名不区分大小写
max_allowed_packet = 1M   #主要影响insert内容的大小，适量修改
max_connections=1000      #mysql的最大连接数
innodb_file_per_table=1       #InnoDB引擎独立表空间

建立数据库应用帐号

mysql> grant all on zcms.* to "用户名"@"IP段.%" identified by "密码";