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 "密码";
