linux/CentOS7安装mysql数据库

安装

• 由于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