MySQL二进制安装

安装准备

建议先卸载mariadb

rpm -qa | grep "mariadb"

rpm -eV mariadbxxxxxx

yum remove mariadb-libs

安装步骤

#在系统中添加运行mysqld进程的用户mysql

groupadd mysql

useradd -M -g mysql -s /sbin/nologin mysql

mkdir -p /usr/local/mysql

cd /usr/local/mysql

tar -zxvf mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz

创建数据目录

mkdir /data

修改数据目录属主

chown -R mysql:mysql /data

chown -R mysql:mysql /usr/local/mysql

初始化

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

[root@pcc mysql]# bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
2021-05-21T08:42:02.828936Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-05-21T08:42:03.126486Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-05-21T08:42:03.165265Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-05-21T08:42:03.221273Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 6a658296-ba10-11eb-ac6a-000c298bd8dc.
2021-05-21T08:42:03.221979Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-05-21T08:42:03.783597Z 0 [Warning] CA certificate ca.pem is self signed.
2021-05-21T08:42:04.118911Z 1 [Note] A temporary password is generated for root@localhost: ;iUs18d;t8df

创建数据目录

mkdir -p /data/{log,binlogs,tmp}

编辑配置文件/etc/my.cnf

[client]
socket = /data/tmp/mysql.sock   #需要和[mysqld]的一致

[mysql]
#设置mysql客户端默认字符集 
default-character-set=utf8mb4

[mysqld]
user=mysql
#设置3306端口
port = 3306

# 设置mysql的安装目录
basedir = /usr/local/mysql               #安装目录
datadir = /data          #数据存放目录
tmpdir = /data/tmp                   #/tmp缓存目录
socket = /data/tmp/mysql.sock             #指定socket文件的位置
log_error = /data/log/mysql_error.log     #错误日志的位置
slow_query_log_file = /data/log/slow_warn.log  #慢日志查询
#pid_file = /data/tmp/mysqld.pid          #指定pid文件的位置，在/etc/init.d/mysqld文件中定义,此处无效


# 允许最大连接数
max_connections=50

# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=20

# 服务端使用的字符集默认为8比特编码的latin1字符集
character_set_server = utf8mb4
collation_server = utf8mb4_bin

#linux下要严格区分大小写，windows下不区分大小写
#1表示不区分大小写，0表示区分大小写。
lower_case_table_names = 1

# 默认使用“mysql_native_password”插件认证
default_authentication_plugin=mysql_native_password

#默认sql模式，严格模式
#sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,
#NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
#ONLY_FULL_GROUP_BY 
#NO_ZERO_IN_DATE 不允许年月为0
#NO_ZERO_DATE 不允许插入年月为0的日期
#ERROR_FOR_DIVISION_BY_ZERO 在INSERT或UPDATE过程中，如果数据被零除，则产生错误而非警告。如果未给出该模式，那么数据被零除时MySQL返回NULL
#NO_ENGINE_SUBSTITUTION 不使用默认的存储引擎替代
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

#开启查询缓存
explicit_defaults_for_timestamp=true

#可接收数据包大小
max_allowed_packet = 16M

#默认使用InnoDB存储引擎
default_storage_engine = InnoDB
innodb_buffer_pool_size = 64M
innodb_purge_threads = 1
innodb_log_buffer_size = 2M
innodb_log_file_size = 128M
innodb_lock_wait_timeout = 120

bulk_insert_buffer_size = 32M
myisam_sort_buffer_size = 8M

配置环境变量，并刷新

##第一种配置方式
echo 'export PATH=$PATH:/usr/local/mysql/bin'>>/etc/profile
source /etc/profile
##第二种配置方式
vim /etc/profile.d/mysql.sh
export PATH=/usr/local/mysql/bin:$PATH
. /etc/profile.d/mysql.sh　　//两种刷新方式，还可以使用source /etc/profile.d/mysql.sh

设置开机自启

systemctl enable mysqld

使用systemd管理mysql_supahero的博客-CSDN博客

测试，并修改root密码

mysql -uroot -p';iUs18d;t8df'
alter user 'root'@'localhost' identified by '新密码';
update user set host='%' where user = 'root';
select host,user from user;
flush privileges;

防火墙开放Mysql服务

firewall-cmd --permanent --add-service=mysql

firewall-cmd --reload

添加系统服务

vim /usr/lib/systemd/system/mysql.service

第一种：推荐

[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql

# Have mysqld write its state to the systemd notify socket
Type=forking

# Disable service start and stop timeout logic of systemd for mysqld service.
TimeoutSec=0

# Start main service
ExecStart=/usr/local/mysql/bin/mysqld --daemonize --pid-file=/data/tmp/mysql.pid 

# Use this to switch malloc implementation
EnvironmentFile=-/etc/sysconfig/mysql

# Sets open_files_limit
LimitNOFILE = 10000

Restart=on-failure

RestartPreventExitStatus=1

# Set environment variable MYSQLD_PARENT_PID. This is required for restart.
Environment=MYSQLD_PARENT_PID=1

PrivateTmp=false

第二种：不推荐

[Unit]
Description=Mysql service
After=syslog.target network.target remote-fs.target nss-lookup.target

[Service]
Type=forking
#PIDFile=/usr/local/mysql/mysql.pid
ExecStart=/usr/local/mysql/support-files/mysql.server start
ExecReload=/usr/local/mysql/support-files/mysql.server restart
ExecStop=/usr/local/mysql/support-files/mysql.server stop

[Install]
WantedBy=multi-user.target