linux/CentOS7安装mysql数据库

发布时间:2023年12月18日

安装

  • 由于CentOS 的yum源中没有mysql,需要到mysql的官网下载yum repo配置文件。
    下载命令:
wget https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
  • 然后进行repo的安装:
rpm -ivh mysql57-community-release-el7-11.noarch.rpm

执行完成后会在/etc/yum.repos.d/目录下生成两个repo文件mysql-community.repo mysql-community-source.repo

  • 使用yum命令即可完成安装
    注意:必须进入到 /etc/yum.repos.d/目录后再执行以下脚本

安装命令:

yum install mysql-server

启动msyql:

systemctl start mysqld #启动MySQL

如果出现下面问题:
mysql-community-libs-compat-5.7.37-1.el7.x86_64.rpm 的公钥尚未安装

rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022
yum install mysql-server
  • 启动成功后再配置后面内容,避免执行出错
systemctl start mysqld

配置和启动MySQL

  • 关闭和备份MySQL
# 关闭MySQL服务
sudo systemctl stop mysqld
  • 参考以下示例修改MySQL配置文件/etc/my.cnf
 vi /etc/my.cnf

文件内容可以直接复制下面示例

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
transaction-isolation = READ-COMMITTED
port = 3306
# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
symbolic-links = 0

#对于1G内存的机器,如果不使用MyISAM表,推荐值是16M(8-64M)
key_buffer_size = 32M
max_allowed_packet = 32M
#针对16G/32G的机器,一般设置 512K
thread_stack = 256K
thread_cache_size = 64
query_cache_limit = 8M
query_cache_size = 64M
query_cache_type = 1

max_connections = 550
#expire_logs_days = 10
#max_binlog_size = 100M

#log_bin should be on a disk with enough free space.
#Replace '/var/lib/mysql/mysql_binary_log' with an appropriate path for your
#system and chown the specified folder to the mysql user.
log_bin=/var/lib/mysql/mysql_binary_log

#In later versions of MySQL, if you enable the binary log and do not set
#a server_id, MySQL will not start. The server_id must be unique within
#the replicating group.
server_id=1

binlog_format = mixed

read_buffer_size = 2M
read_rnd_buffer_size = 16M
sort_buffer_size = 8M
join_buffer_size = 8M

# InnoDB settings
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 64M
innodb_buffer_pool_size = 4G
innodb_thread_concurrency = 8
innodb_flush_method = O_DIRECT
innodb_log_file_size = 512M

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

sql_mode=STRICT_ALL_TABLES

设置MySQL服务开机启动

sudo systemctl enable mysqld
  • 启动MySQL
sudo systemctl start mysqld

设置root密码及其他安全相关配置

获取安装时的临时密码(在第一次登录时就是用这个密码):

grep 'temporary password' /var/log/mysqld.log
sudo /usr/bin/mysql_secure_installation

参考以下示例操作

Set root password? [Y/n] Y
Newpassword:
Re-enter newpassword:
Remove anonymous users? [Y/n] Y
[...]
Disallow root login remotely? [Y/n] N
[...]
Remove testdatabaseandaccessto it [Y/n] Y
[...]
Reload privilege tablesnow? [Y/n] Y
All done!

数据库授权

  • 数据库没有授权,只支持localhost本地访问
mysql -u root -p
mysql>use mysql;
  • 不建议直接开放root用户访问
mysql> update user set host='%'where user='root';
// ERROR 1062 (23000): Duplicate entry '%-root' for key 'PRIMARY' 不予理会
mysql> flush privileges;
  • 修改策略之后再执行修改密码
set global validate_password_policy=0;
set global validate_password_policy=LOW;
  • 创建使用用户
mysql>CREATE USER 'fence'@'%' IDENTIFIED BY 'fence342@';
mysql>GRANT ALL ON *.* TO 'fence'@'%';
  • 修改最大连接数
mysql>set GLOBAL max_connections=1024;
mysql>show variables like '%max_connections%';
  • 修改密码过期策略
ALTER USER `fence`@`%` PASSWORD EXPIRE NEVER;
  • 关闭root用户的远程访问权限
update user set host = 'localhost'  where user =  'root' ;    
flush privileges;

设置数据库连接数

set global max_connect_errors=99999;
set GLOBAL max_connections = 6000;
flush hosts;

开放端口

firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --reload
文章来源:https://blog.csdn.net/FenceRain/article/details/135014816
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。