在centos7中使用yum安装mysql数据库并使用navicat连接

1.安装

1.查看yum列表，发现没有mysql
[root@server-mysql src]# yum list mysql
    已加载插件：fastestmirror
    Repodata is over 2 weeks old. Install yum-cron? Or run: yum makecache fast
    Loading mirror speeds from cached hostfile
     * base: centos.ustc.edu.cn
     * extras: centos.ustc.edu.cn
     * updates: centos.ustc.edu.cn
    错误：没有匹配的软件包可以列出

2.使用wget下载一个mysql的repo源
[root@server-mysql src]# wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm
    -bash: wget: 未找到命令

3.发现wget也没有，那就安装一个wget
[root@server-mysql src]# yum list wget
    已加载插件：fastestmirror
    Repodata is over 2 weeks old. Install yum-cron? Or run: yum makecache fast
    Loading mirror speeds from cached hostfile
     * base: centos.ustc.edu.cn
     * extras: centos.ustc.edu.cn
     * updates: centos.ustc.edu.cn
    可安装的软件包
    wget.x86_64       1.14-15.el7_4.1     base

[root@server-mysql src]# yum -y install wget.x86_64

......

    依赖关系解决
    ========================================================================================
     Package            架构                 版本                 源                     大小
    ========================================================================================
    正在安装:
     wget               x86_64          1.14-15.el7_4.1          base                  547 k

    事务概要
    ========================================================================================
    安装  1 软件包

    总下载量：547 k
    安装大小：2.0 M
    Downloading packages:
    wget-1.14-15.el7_4.1.x86_64.rpm                                        | 547 kB  00:00:10
    Running transaction check
    Running transaction test
    Transaction test succeeded
    Running transaction
      正在安装    : wget-1.14-15.el7_4.1.x86_64                                1/1
      验证中      : wget-1.14-15.el7_4.1.x86_64                                1/1

    已安装:
      wget.x86_64 0:1.14-15.el7_4.1

    完毕！

4.在次下载mysql的repo源
[root@server-mysql src]# wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm
    --2018-09-08 14:45:20--  http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm
    正在解析主机 repo.mysql.com (repo.mysql.com)... 104.124.241.153
    正在连接 repo.mysql.com (repo.mysql.com)|104.124.241.153|:80... 已连接。
    已发出 HTTP 请求，正在等待回应... 200 OK
    长度：6140 (6.0K) [application/x-redhat-package-manager]
    正在保存至: “mysql-community-release-el7-5.noarch.rpm”

    100%[========================================================>] 6,140       --.-K/s 用时 0.001s

    2018-09-08 14:45:20 (8.39 MB/s) - 已保存 “mysql-community-release-el7-5.noarch.rpm” [6140/6140])

5.用命令查看当前目录，已经下载好了
[root@server-mysql src]# ll | grep mysql
    -rw-r--r--. 1 root root 6140 11月 12 2015 mysql-community-release-el7-5.noarch.rpm

6.安装一下
[root@server-mysql src]# rpm -ivh mysql-community-release-el7-5.noarch.rpm
    准备中...                          ################################# [100%]
    正在升级/安装...
       1:mysql-community-release-el7-5    ################################# [100%]

7.再次查看yum列表，发现已经有mysql的包了
[root@server-mysql src]# yum list mysql
    已加载插件：fastestmirror
    mysql-connectors-community                               | 2.5 kB  00:00:00
    mysql-tools-community                                    | 2.5 kB  00:00:00
    mysql56-community                                        | 2.5 kB  00:00:00
    (1/3): mysql-connectors-community/x86_64/primary_db      |  26 kB  00:00:00
    (2/3): mysql-tools-community/x86_64/primary_db           |  45 kB  00:00:00

8.执行安装(会替换自带的mariadb库)
[root@server-mysql src]# yum -y install mysql-server
    已加载插件：fastestmirror
.......

    已安装:
      mysql-community-libs.x86_64 0:5.6.41-2.el7                                        mysql-community-server.x86_64 0:5.6.41-2.el7

    作为依赖被安装:
      mysql-community-client.x86_64 0:5.6.41-2.el7           mysql-community-common.x86_64 0:5.6.41-2.el7           net-tools.x86_64 0:2.0-0.22.20131004git.el7
      perl-Compress-Raw-Bzip2.x86_64 0:2.061-3.el7           perl-Compress-Raw-Zlib.x86_64 1:2.061-4.el7            perl-DBI.x86_64 0:1.627-4.el7
      perl-Data-Dumper.x86_64 0:2.145-3.el7                  perl-IO-Compress.noarch 0:2.061-2.el7                  perl-Net-Daemon.noarch 0:0.48-5.el7
      perl-PlRPC.noarch 0:0.2020-14.el7

    替代:
      mariadb-libs.x86_64 1:5.5.56-2.el7

    完毕！

2.初始化

1.尝试进入mysql，  报错了，是没有权限
[root@server-mysql src]# mysql -u root
    ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

2.查看mysql所属的用户，发现是mysql
[root@server-mysql src]# ll /var/lib/ | grep mysql
    drwxr-xr-x. 2 mysql   mysql      6 6月  15 21:36 mysql
    drwxr-x---. 2 mysql   mysql      6 6月  15 21:36 mysql-files

3.将其更改为当前用户(root)
[root@server-mysql src]# chown -R root:root /var/lib/mysql*
[root@server-mysql src]# ll /var/lib/ | grep mysql
    drwxr-xr-x. 2 root    root       6 6月  15 21:36 mysql
    drwxr-x---. 2 root    root       6 6月  15 21:36 mysql-files

4.重启mysql
[root@server-mysql src]# systemctl restart mysqld

5.再次进入数据库，并查看已有数据库
[root@server-mysql src]# mysql -u root
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 3
    Server version: 5.6.41 MySQL Community Server (GPL)

    Copyright (c) 2000, 2018, 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> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    +--------------------+
    3 rows in set (0.00 sec)

3.设置/修改密码

方式一：
    1.登陆到mysql中，将密码修改为root
    mysql> set password for root@localhost = password('root');
        Query OK, 0 rows affected (0.00 sec)

    2.退出mysql
    mysql> exit;
        Bye

    3.再次登陆需要使用密码登陆
    [root@server-mysql src]# mysql -u root -p
        Enter password: root

方式二：
    1.登陆到mysql中，切换到mysql库
    mysql> use mysql;

    2.直接更新user表
    mysql> update user set password=password('root') where user='root' and host='localhost';  
    
    3.刷新权限表
    mysql> flush privileges;

方式三： 使用navicat修改，需要使用navicat连接上这个数据库，见文章最后有图示

4.修改远程连接权限

1.切换到mysql库
mysql> use mysql;
    Database changed

2.查看用户和能连接的主机, 发现root用户只能在本机连接(host中地址都是代表本机)，不能远程用navicat连接
mysql> select user, host from user where user = 'root';
    +------+--------------+
    | user | host         |
    +------+--------------+
    | root | 127.0.0.1    |
    | root | ::1          |
    | root | localhost    |
    | root | server-mysql |
    +------+--------------+
    4 rows in set (0.00 sec)

3.修改用户访问的地址
mysql> Grant all privileges on *.* to root@'%' identified by 'root' with grant option;
    Query OK, 0 rows affected (0.00 sec)
说明：*.*指对数据的所有权限(增删改查), root指连接上来的用户名, 
    ‘%’指所有外部ip地址都可以连接上来，如果要指定地址可以这样写 ‘100.100.100.100’,
    identified by 后跟的是连接上来的密码 

4.再次查看, 发现root用户多了一个主机地址（%）
mysql> select user, host from user where user = 'root';
    +------+--------------+
    | user | host         |
    +------+--------------+
    | root | %            |
    | root | 127.0.0.1    |
    | root | ::1          |
    | root | localhost    |
    | root | server-mysql |
    +------+--------------+
    5 rows in set (0.00 sec)

5.刷新一下权限表
mysql> flush privileges;
    Query OK, 0 rows affected (0.01 sec)

6.开放端口
[root@server-mysql src]# firewall-cmd --add-port=3306/tcp --permanent
    success
[root@server-mysql src]# firewall-cmd --reload
    success