服务器配置mysql

刺骨的言语ヽ痛彻心扉 2022-05-29 23:46 213阅读 0赞

1、配置YUM源

下载mysql源安装包

[root@localhost~]#wget http://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm

安装mysql源

[root@localhost~]# yum localinstall mysql57-community-release-el7-8.noarch.rpm 
    提示【Is this ok [y/d/N]: y  输入y回车】
    检查MySQL源是否安装成功 
    [root@localhost ~]# yum repolist enabled | grep “mysql.-community.”

2、安装MySQL

[root@localhost ~]# yum install mysql-community-server

注意：安装过程中提示【Is this ok \[y/d/N\]: y 输入y回车】   
出现以下表示安装成功：

Replaced:
      mariadb-libs.x86_64 1:5.5.56-2.el7                                                                                                          
    
    Complete!

3、启动MySQL服务

[root@localhost ~]# systemctl start mysqld 
    查看MySQL的启动状态 
    [root@localhost ~]# systemctl status mysqld

4.开机启动

[root@localhost ~]# systemctl enable mysqld
    [root@localhost ~]# systemctl daemon-reload

1.  查看mysql下root账号的默认密码   
    mysql5.7安装完成之后，在/var/log/mysqld.log文件中给root生成了一个默认密码。通过下面的方式找到root默认密码，然后登录mysql。   
    命令：grep ‘temporary password’ /var/log/mysqld.log

[root@localhost /]# grep 'temporary password' /var/log/mysqld.log
    2017-10-17T08:07:03.797098Z 1 [Note] A temporary password is generated for root@localhost: 3prjY9ktM,eL
    [root@localhost /]# 
    默认的密码是：3prjY9ktM,eL

1.  修改配置文件   
    6.1. 默认配置文件路径   
    配置文件：/etc/my.cnf   
    日志文件：/var/log//var/log/mysqld.log   
    服务启动脚本：/usr/lib/systemd/system/mysqld.service   
    socket文件：/var/run/mysqld/mysqld.pid   
    6.2. 修改my.cnf文件   
    6.2.1. 修改密码策略   
    mysql的密码策略分为三种：   
    0或LOW：Length   
    1或MEDIUM：Length; numeric, lowercase/uppercase, and special characters   
    2或STRONG：Length; numeric, lowercase/uppercase, and special characters; dictionary file

在my.cnf文件中增加如下设置

# 如果不需要密码策略，禁用密码策略 #

validate\_password = off

# 密码选择策略 0-LOW，1-MEDIUM，2-STRONG需要提供密码字典文件 #

validate\_password\_policy = 0

6.2.2. 修改字符编码为utf8   
在\[mysqld\]下增加如下配置   
character\_set\_server = utf8   
init\_connect = ‘SET NAMES utf8’

例：
    [root@localhost /]# cd /etc/
    [root@localhost etc]# vi my.cnf
    validate_password = off# For advice on how to change settings please see
    # http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
    
    [mysqld]
    #
    # Remove leading # and set to the amount of RAM for the most important data
    # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
    # innodb_buffer_pool_size = 128M
    #
    # Remove leading # to turn on a very important data integrity option: logging
    # changes to the binary log between backups.
    # log_bin
    #
    # Remove leading # to set options mainly useful for reporting servers.
    # The server defaults are faster for transactions and fast SELECTs.
    # Adjust sizes as needed, experiment to find the optimal values.
    # join_buffer_size = 128M
    # sort_buffer_size = 2M
    # read_rnd_buffer_size = 2M
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    
    # Disabling symbolic-links is recommended to prevent assorted security risks
    symbolic-links=0
    
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid
    validate_password = off  #设置禁用密码策略
    
    character_set_server = utf8  #修改字符编码为utf8
    init_connect = 'SET NAMES utf8'

6.2.3. 保存my.cnf并重启mysql服务是配置生效   
命令：systemctl restart mysqld

**登录mysql**

命令：mysql -uroot -p   
输入密码：默认为刚才查到的密码”3prjY9ktM,eL”

[root@localhost etc]# mysql -uroot -p
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 4
    Server version: 5.7.20
    
    Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    mysql>

修改密码   
命令：ALTER USER 'root'@'localhost' IDENTIFIED BY '12345678';   
或命令：set password for 'root'@'localhost'=password('12345678');

说明：   
1. 如果之前没有设置密码策略，则密码12345678则不会通过验证，密码修改会失败

grant all privileges on \*.\* to 'root'@'0.0.0.0'identified by '123456' with grant option;

1.  密码修改不成功时则部分功能也无法使用，例如查看密码策略 show variables like ‘%password%’;
2.  查看密码策略   
    命令：show variables like '%password%';   
    此处为 validate\_password = off 设置后的结果
3.  查看字符编码   
    命令：show variables like '%character%';
4.  添加远程账户   
    命令：
    
        grant all privileges on *.* to 'root'@'%'identified by '123456' with grant option;
    
      
    命令：FLUSH PRIVILEGES; 使设置生效 
5.  如果你想允许用户myuser从ip为0.0.0.0所有的主机连接到mysql服务器，并使用mypassword作为密码  
6.  刷新权限  
    FLUSH PRIVILEGES;
7.  至此，可以远程连接并操作数据库啦！