mysql 开垦升级篇连串 30 数据库二进制包(安装钦赐路径,目录介绍卡塔尔国

by admin on 2019年12月15日

概述  

  对于二进制安装,优点是可以安装到任何路径下,灵活性好,一台服务器可以安装多个mysql。缺点是已经绎过编译,性能不如源码编译得好,不能灵活定制编译参数。如果用户即不想安装最简单却不够灵活的RPM包,又不想安装复杂费时的源码包,那么已编译好的二进制包将是最好的选择。

1. centos 7安装工作

  对于mysql二进制安装,我这里在使用一台新的centos系统。准备好VMware,Xftp-6.0,
Xshell-6.0。在VMware中网络使用桥接模式,分配20G硬盘,1个处理器4个内核。进入centos安装界面后选择中文,时区上海,配置分区/boot
200M, swap 2G, / 10G。
销毁格式选择接受更改。关闭kdump。手动配置以太网,共用windows上的dns和网关以及网段。最后设置好密码,等待安装完成。

  系统安装成功后,使用Xshell新建会话连接登录。
  图片 1
  再测试网络
  图片 2

  查看当前配置好的ip地址 172.168.18.201
  图片 3

  在windows端拼下新系统IP地址
  图片 4

  在windows使用xftp新建会话连接
  图片 5

  
  在centos的usr目录下新建一个tool文件夹,存放mysql安装包,如下图所示:

  图片 6

  图片 7

   查看如下:

   图片 8

一. 指定路径

  在上一篇里,二进制包解压后,全部放入在/usr/local/mysql目录下,在安装时指定了基础目录和数据目录,
–basedir=/usr/local/mysql –datadir=/usr/local/mysql/data 。

--有些文件在安装,是没有指定路径,安装后默认分布如下: 
    错误消息文件和字符集文件(share/mysql)。
    my.cnf(/etc/my.cnf)。
    socket=/var/lib/mysql/mysql.sock。
   --my.cnf里的配置
    [mysqld_safe]
    log-error=/var/log/mariadb/mariadb.log
    pid-file=/var/run/mariadb/mariadb.pid。

--网上查了资源,在安装时可以指定路径 如下所示:
[root@hsr bin]# ./mysqld  
--defaults-file=/usrl/local/mysql/etc/my.cnf 
--socket=/var/run/mysqld/mysqld.sock 
--log-error=/var/log/mariadb/mariadb.log  
--pid-file=/var/run/mariadb/mariadb.pid  
--initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

  Linux是建议第三方软件放在/opt下。官网文档 Initializing the Data
Directory Manually Using
mysqld

图片 9

二进制包安装MySQL数据库,二进制mysql数据库

1.1二进制包安装MySQL数据库

1.1.1 安装前准备(规范)

 

 1 [[email protected]_server ~]# mkdir -p /home/zhurui/tools  ##创建指定工具包存放路径
 2 [[email protected]_server ~]# wget http://ftp.ntu.edu.tw/pub/MySQL/Downloads/MySQL-5.5/mysql-5.5.32-linux2.6-x86_64.tar.gz  ##下载二进制包
 3 [[email protected]_server tools]# tar xf mysql-5.5.32-linux2.6-x86_64.tar.gz   ##解压Mysql包
 4 [[email protected]_server tools]# useradd -s /sbin/nologin -M mysql  ##创建mysql用户
 5 [[email protected]_server tools]# id mysql
 6 uid=501(mysql) gid=501(mysql) groups=501(mysql)
 7 [[email protected]_server ~]# mkdir /application/
 8 [[email protected]_server home]# mv /home/zhurui/tools/mysql-5.5.32-linux2.6-x86_64 /application/mysql-5.5.32  
 9 [[email protected]_server tools]# ll /application/
10 total 4
11 drwxr-xr-x. 13 root root 4096 Dec 13 14:31 mysql-5.5.32
12 [[email protected]_server tools]# ln -s /application/mysql-5.5.32/ /application/mysql  ##设置软链接
13 [[email protected]_server tools]# ll /application/
14 total 4
15 lrwxrwxrwx.  1 root root   26 Dec 13 14:39 mysql -> /application/mysql-5.5.32/
16 drwxr-xr-x. 13 root root 4096 Dec 13 14:31 mysql-5.5.32
17 [[email protected]_server tools]# ll /application/mysql/
18 total 76
19 drwxr-xr-x.  2 root root   4096 Dec 13 14:31 bin
20 -rw-r--r--.  1 7161 wheel 17987 Jun 19  2013 COPYING
21 drwxr-xr-x.  3 root root   4096 Dec 13 14:31 data
22 drwxr-xr-x.  2 root root   4096 Dec 13 14:30 docs
23 drwxr-xr-x.  3 root root   4096 Dec 13 14:31 include
24 -rw-r--r--.  1 7161 wheel  7470 Jun 19  2013 INSTALL-BINARY
25 drwxr-xr-x.  3 root root   4096 Dec 13 14:31 lib
26 drwxr-xr-x.  4 root root   4096 Dec 13 14:31 man
27 drwxr-xr-x. 10 root root   4096 Dec 13 14:31 mysql-test
28 -rw-r--r--.  1 7161 wheel  2496 Jun 19  2013 README
29 drwxr-xr-x.  2 root root   4096 Dec 13 14:31 scripts
30 drwxr-xr-x. 27 root root   4096 Dec 13 14:31 share
31 drwxr-xr-x.  4 root root   4096 Dec 13 14:31 sql-bench
32 drwxr-xr-x.  3 root root   4096 Dec 13 14:31 support-files

 

1.1.2 初始化数据库

 

 1 [[email protected]_server tools]# /application/mysql/scripts/mysql_install_db --basedir=/application/mysql/ --datadir=/application/mysql/data/ --user=mysql  ##初始化数据库
 2 Installing MySQL system tables...
 3 OK
 4 Filling help tables...
 5 OK
 6 [[email protected]_server tools]# ll /application/mysql/data/  ##查看初始化结果
 7 total 12
 8 drwx------. 2 mysql root  4096 Dec 13 14:45 mysql
 9 drwx------. 2 mysql mysql 4096 Dec 13 14:45 performance_schema
10 drwxr-xr-x. 2 mysql root  4096 Dec 13 14:31 test

 

1.1.3 授权Mysql管理数据库文件

1 [[email protected]_server ~]# chown -R mysql.mysql /application/mysql/
2 [[email protected]_server ~]# ll /application/mysql
3 lrwxrwxrwx. 1 mysql mysql 26 Dec 13 14:39 /application/mysql -> /application/mysql-5.5.32/

1.1.4 生成Mysql配置文件

1 [[email protected]_server tools]# \cp /application/mysql/support-files/my-small.cnf /etc/my.cnf

1.1.5 配置启动Mysql

1 [[email protected]_server tools]# sed -i 's#/usr/local/mysql#/application/mysql#g' /application/mysql/bin/mysqld_safe /application/mysql/support-files/mysql.server 
2 [[email protected]_server tools]# cp /application/mysql/support-files/mysql.server /etc/init.d/mysqld  ##将生成的启动脚本拷贝到init.d目录下
3 [[email protected]_server tools]# chmod +x /etc/init.d/mysqld 

1.1.6 启动Mysql

1 [[email protected]_server tools]# lsof -i :3306  ##查询Mysql服务是否开启
2 [[email protected]_server tools]# 
3 [[email protected]_server tools]# /etc/init.d/mysqld start  ##启动Mysql服务
4 Starting MySQL.... SUCCESS! 
5 [[email protected]_server tools]# lsof -i :3306
6 COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
7 mysqld  2235 mysql   10u  IPv4  22761      0t0  TCP *:mysql (LISTEN)
8 [[email protected]_server tools]# 

1.1.7 配置环境变量

方法1:

1 [[email protected]_server tools]# cp /application/mysql/bin/* /usr/local/sbin/ ##方法1

方法2:

 

1 [[email protected]_server tools]# tail -1 /etc/profile
2 PATH="/application/mysql/bin:$PATH"   ##尾部添加如下行
3 [[email protected]_server tools]# source /etc/profile    ##使得配置生效

 

1.1.8 设置及更改密码

1 [[email protected]_server tools]# mysqladmin -uroot password 123456   
2 [[email protected]_server tools]# mysqladmin -uroot -p123456 password zhurui

2.1 数据库管理

 

 1 [[email protected]_server tools]# mysql -uroot -p
 2 Enter password: 
 3 Welcome to the MySQL monitor.  Commands end with ; or \g.
 4 Your MySQL connection id is 8
 5 Server version: 5.5.32 MySQL Community Server (GPL)
 6 
 7 Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
 8 
 9 Oracle is a registered trademark of Oracle Corporation and/or its
10 affiliates. Other names may be trademarks of their respective
11 owners.
12 
13 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
14 
15 mysql> show databases;   ##查看数据库;
16 +--------------------+
17 | Database           |
18 +--------------------+
19 | information_schema |
20 | mysql              |
21 | performance_schema |
22 | test               |
23 +--------------------+
24 4 rows in set (0.00 sec)
25 
26 mysql> drop database test;  ##删除test库;
27 Query OK, 0 rows affected (0.00 sec)
28 
29 mysql> show databases;    
30 +--------------------+
31 | Database           |
32 +--------------------+
33 | information_schema |
34 | mysql              |
35 | performance_schema |
36 +--------------------+
37 3 rows in set (0.00 sec)
38 
39 mysql> select user,host from mysql.user;  ##查看用户列表;
40 +------+---------------+
41 | user | host          |
42 +------+---------------+
43 | root | 127.0.0.1     |
44 | root | ::1           |
45 |      | Mysql\_server |
46 | root | Mysql\_server |
47 |      | localhost     |
48 | root | localhost     |
49 +------+---------------+
50 6 rows in set (0.00 sec)
51 
52 mysql> drop user [email protected]'::1';  ##删除无用用户
53 Query OK, 0 rows affected (0.00 sec)
54 
55 mysql> drop user ''@'Mysql\_server';      ##对于大写、特殊字符删除不了,下面有介绍
56 Query OK, 0 rows affected (0.00 sec)
57 
58 mysql> drop user ''@'localhost';    
59 Query OK, 0 rows affected (0.01 sec)
60 
61 mysql> drop user 'root'@'Mysql\_server';  对于大写、特殊字符删除不了,下面有介绍
62 Query OK, 0 rows affected (0.00 sec)
63 
64 mysql> select user,host from mysql.user;
65 +------+---------------+
66 | user | host          |
67 +------+---------------+
68 | root | 127.0.0.1     |
69 |      | Mysql\_server |
70 | root | Mysql\_server |
71 | root | localhost     |
72 +------+---------------+
73 4 rows in set (0.00 sec)
74 对于大写、特殊字符删除不了解决办法:
75 mysql> delete from mysql.user where user="" and host="Mysql\_server";
76 Query OK, 1 row affected (0.02 sec)
77 
78 mysql> delete from mysql.user where user="root" and host="Mysql\_server";
79 Query OK, 1 row affected (0.00 sec)
80 
81 mysql> select user,host from mysql.user;  ##将无用用户删除以后,查看用户列表                               
82 +------+-----------+
83 | user | host      |
84 +------+-----------+
85 | root | 127.0.0.1 |
86 | root | localhost |
87 +------+-----------+
88 2 rows in set (0.00 sec)
89 mysql> flush privileges;  ##最后使得权限生效
90 Query OK, 0 rows affected (0.00 sec)

创建用户sql语句:

 

1 mysql> grant all on *.* to 'root'@'localhost' identified by '123456' with grant option;flush privileges;

 

1.1二进制包安装MySQL数据库 1.1.1 安装前准备(规范) 1
[[email protected]_server
~] # mkdir -p /home/zhurui/tools #…

一.概述

  上一章讲到了RPM安装后的文件目录,这章还是介绍下安装步骤。也便以后做参考吧。

  1. 移出centos 7系统自带的mysql库

  yum remove mysql-libs 

    图片 10

  2. 将下载的mysql包放到/home/hsr/tool
目录下(mysql-5.7.20-1.el7.x86_64.rpm-bundle.tar)

   图片 11
  3. 解压到mysql文件夹下的rpm包
    图片 12

  4.必备的4个rpm安装

  rpm -ivh mysql-community-common-5.7.20-1.el7.x86_64.rpm

    图片 13

rpm -ivh mysql-community-libs-5.7.20-1.el7.x86_64.rpm

    图片 14

rpm -ivh mysql-community-client-5.7.20-1.el7.x86_64.rpm

    图片 15

rpm -ivh mysql-community-server-5.7.20-1.el7.x86_64.rpm

    图片 16
  5. 查看服务状态
     图片 17
  6.查看初始密码

cat /var/log/mysqld.log | grep password

    图片 18
  7.复制出密码登录
    图片 19
  8. 修改密码
    登录成功后在创建库里会提示如下:
    图片 20

SET PASSWORD = PASSWORD('js*2015');

    图片 21

    上图Reset密码时:提示您当前的密码不符合策略要求,也就是太简单。把密码级别设到最低

set global validate_password_policy=0;

    图片 22
    再重置密码:

SET PASSWORD = PASSWORD('js*2015%');

    图片 23
  9.允许远程连接

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'js*2015%' WITH GRANT OPTION;  

    图片 24
  这样,我们就可以在windows电脑上用mysql客户端远程连接linux上的mysql服务了。如果在windows电脑上连接不了,先用telnet
来拼下ip和端口,检查防火墙

 

一.步骤1: 解压glib包

-- 在 /usr/local 下创建一个mysql文件夹,用来存放
[root@hsr local]# mkdir mysql
[root@hsr local]# ls
bin  etc  games  include  lib  lib64  libexec  mysql  sbin  share  src

-- 在原有/usr/tool目录将gz压缩包解压
[root@hsr tool]# tar -zxvf mysql-5.7.23-linux-glibc2.12-x86_64.tar.gz
mysql-5.7.23-linux-glibc2.12-x86_64/bin/myisam_ftdump
mysql-5.7.23-linux-glibc2.12-x86_64/bin/myisamchk
mysql-5.7.23-linux-glibc2.12-x86_64/bin/myisamlog
mysql-5.7.23-linux-glibc2.12-x86_64/bin/myisampack
mysql-5.7.23-linux-glibc2.12-x86_64/bin/mysql
mysql-5.7.23-linux-glibc2.12-x86_64/bin/mysql_client_test_embedded
mysql-5.7.23-linux-glibc2.12-x86_64/bin/mysql_config_editor
.....

--将解压的文件复制到/usr/local/mysql目录下
[root@hsr tool]# sudo cp -r mysql-5.7.23-linux-glibc2.12-x86_64 /usr/local/mysql

--在mysql-->mysql-5.7.23-linux-glibc2.12-x86_64路径下 解压的文件共9个 目录如下: 
[root@hsr mysql]# ls mysql-5.7.23-linux-glibc2.12-x86_64 bin COPYING docs include lib man README share support-files

  注意:mysql-5.7.23-linux-glibc2.12-x86_64目录层次要去掉,变为/usr/local/mysql
下的9个目录,在文章后面会去掉这层。

  图片 25

二 . MYSQL二进制安装目录  

二进制安装目录

rpm安装目录

二进制目录说明

bin客户端程序和mysqld服务器

/usr/bin(客户端程序和脚本)
/usr/sbin(mysqld服务器)

/usr/local/mysql/bin

在上一篇里的"七 登录mysql"里做了文件映射。

 ln -s /usr/local/mysql/bin/mysql /usr/bin

Data日志文件和数据文件

/var/lib/mysql(日志文件和数据库)

这里安装时自定义在/usr/local/mysql/data下。

Docs 文档和ChangeLog

 

/usr/local/mysql/docs

Include 包含(头)文件lib(库文件)

 

/usr/local/mysql/include,/usr/local/mysql/lib

share/mysql 错误消息文件和字符集文件

 

/usr/share/mysql (默认路径)

二. 步骤2:    

   2.1 添加mysql用户 useradd -r -g 用户名 用户组

[root@hsr mysql]# groupadd mysql
[root@hsr mysql]# useradd -r -g mysql mysql

  2.2 切换到 /usr/local/mysql 目录下,改变目录拥有者为mysql

[root@hsr mysql]# chown -R  mysql.mysql /usr/local/mysql

  2.3 新环境安装libaio包 mysql 依赖于libaio

[root@hsr mysqld]# yum search libaio

三. 重启系统后登录报错

-- 错误信息如下:
[root@hsr ~]#  mysql -u root -p
Enter password: 
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

  解决方法:

  1. 在my.cnf里指定socket=/tmp/mysql.sock

  2. 启动mysql

    [root@hsr ~]# service mysql start
    Starting MySQL.. SUCCESS!

  3.再登录mysql OK

    [root@hsr ~]#  mysql -u root -p

    Enter password:

    Welcome to the MySQL monitor.  Commands end with ; or \g.

    Your MySQL connection id is 2

    Server version: 5.7.23 MySQL Community Server (GPL)

三 步骤3:  

  安装mysql,使用 –initialize,basedir
基础目录,datadir 为数据目录。

[root@hsr ~]# cd /usr/local/mysql/mysql-5.7.23-linux-glibc2.12-x86_64/bin
[root@hsr bin]# ./mysqld  --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
2018-08-23T06:56:21.157088Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-08-23T06:56:21.157246Z 0 [ERROR] Can't find error-message file '/usr/local/mysql/share/errmsg.sys'. Check error-message file location and 'lc-messages-dir' configuration directive.
2018-08-23T06:56:26.287087Z 0 [Warning] InnoDB: New log files created, LSN=45790
2018-08-23T06:56:27.059913Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2018-08-23T06:56:27.138616Z 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: a7e28575-a6a1-11e8-af13-000c29affb65.
2018-08-23T06:56:27.154064Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2018-08-23T06:56:27.155635Z 1 [Note] A temporary password is generated for root@localhost: ro0ssOGT?ocf

四步骤4:

  4.1 创建RSA
private key。

[root@hsr bin]# bin/mysql_ssl_rsa_setup --datadir=/usr/local/mysql/data

  图片 26

  4.2 修改当前目录拥有者为 root 用户,修改data 目录拥有者为 mysql

[root@hsr bin]# chown -R root:root /usr/local/mysql
[root@hsr bin]# chown -R mysql:mysql /usr/local/mysql/data

 五.步骤5 配置mysql(mysql.server)和my.cnf文件

--检查 etc/my.cnf文件是否存在 (二进制安装,默认配置文件在/etc/my.cnf)
    [root@hsr etc]# find -name  my.cnf
    ./my.cnf

 --将support-files 目录下的mysql.server文件复制到etc/init.d下
    [root@hsr ~]# cd /usr/local/mysql/mysql-5.7.23-linux-glibc2.12-x86_64/support-files
    [root@hsr support-files]# cp mysql.server /etc/init.d/mysql

 -- 配置/etc/init.d/mysql服务文件 添加basedir和datadir
    [root@hsr support-files]# vim /etc/init.d/mysql

    图片 27

   修改my.cnf  配置以下四个参数(注意:chkconfig — level 35 mysqld on
不要加上,后面报错,又得去掉
)
    图片 28

 六.步骤6 启动mysql

[root@hsr bin]# service mysql start
/etc/init.d/mysql: line 239: my_print_defaults: command not found
    Starting MySQL ERROR! Couldn't find MySQL server (/usr/local/mysql/bin/mysqld_safe) 

--提示未找到路径,需要把"mysql-5.7.23-linux-glibc2.12-x86_64" 文件夹去掉,使用mv 将里面的文件移到/usr/locl/mysql下,共9个文件
[root@hsr bin]# mv /usr/local/mysql/mysql-5.7.23-linux-glibc2.12-x86_64/README /usr/local/mysql
[root@hsr bin]# mv /usr/local/mysql/mysql-5.7.23-linux-glibc2.12-x86_64/COPYING /usr/local/mysql
[root@hsr bin]# mv /usr/local/mysql/mysql-5.7.23-linux-glibc2.12-x86_64/support-files /usr/local/mysql
[root@hsr bin]# mv /usr/local/mysql/mysql-5.7.23-linux-glibc2.12-x86_64/share /usr/local/mysql
[root@hsr bin]# mv /usr/local/mysql/mysql-5.7.23-linux-glibc2.12-x86_64/man /usr/local/mysql
[root@hsr bin]# mv /usr/local/mysql/mysql-5.7.23-linux-glibc2.12-x86_64/lib /usr/local/mysql
[root@hsr bin]# mv /usr/local/mysql/mysql-5.7.23-linux-glibc2.12-x86_64/include /usr/local/mysql
[root@hsr bin]# mv /usr/local/mysql/mysql-5.7.23-linux-glibc2.12-x86_64/docs /usr/local/mysql
[root@hsr bin]# mv /usr/local/mysql/mysql-5.7.23-linux-glibc2.12-x86_64/bin /usr/local/mysql

-- 再启动
[root@hsr bin]# service mysql start
Starting MySQL.2018-08-24T01:06:20.545225Z mysqld_safe error: log-error set to '/var/log/mariadb/mariadb.log', however file don't exists. Create writable for user 'mysql'.
 ERROR! The server quit without updating PID file (/usr/local/mysql/data/hsr.pid).

-- 提示/var/log/mariadb/mariadb.log 不存在, 打开my.cnf 能看到定义的默认路径
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

--创建目录 给权限
[root@hsr bin]# mkdir /var/log/mariadb 
[root@hsr bin]# touch /var/log/mariadb/mariadb.log 
[root@hsr bin]# chown -R mysql:mysql  /var/log/mariadb/

-- 再启动
[root@hsr bin]# service mysql start
Starting MySQL.... ERROR! The server quit without updating PID file (/usr/local/mysql/data/hsr.pid).

-- 错误信息是hsr.pid进程出问题,先查看下日志
[root@hsr ~]# cat /var/log/mariadb/mariadb.log

  图片 29
   在my.cnf中注释上面参数(#chkconfig –level 35 mysqld on),再启动
  图片 30
  上图意思是不能创建mysql.sock.lock
文件,一般是权限不足,如下设置好权限,启动成功
  图片 31

七 登录mysql

[root@hsr ~]# mysql -u root -p
bash: mysql: 未找到命令

  未找到命令,是由于系统默认会查找/usr/bin下的命令,如果这个命令不在这个目录下,就会找不到命令,需要映射一个链接到/usr/bin目录下,相当于建立一个链接文件。

[root@hsr ~]#  ln -s /usr/local/mysql/bin/mysql /usr/bin
[root@hsr ~]# mysql -u -root -p
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

--错误信息是不能连接到本地的socket ,系统默认找到了/tmp目录下,需要设置链接文件
[root@hsr tmp]# ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock

[root@hsr ~]# mysql -u -root -p
Enter password:

 八. 设置密码  

  8.1 打开my.cnf文件,添加skip-grant-tables,来重置密码,如下所示
    图片 32
  8.2 启动服务,再次登录,在输入密码处按回车键进入。

[root@hsr ~]# vim /etc/my.cnf
[root@hsr ~]# service mysql restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 
[root@hsr ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.23

  8.3 进入mysql后,修改密码

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> update user set authentication_string=password('123456') where user='root';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

  退出mysql>quit;  编辑 my.cnf 注释掉#skip-grant-tables

 8.4 重启mysql服务,输入修改后的密码(123456)进入

[root@hsr ~]# service mysql restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 
[root@hsr ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.23

 九 远程登录

-- 登录到mysql后设置权限
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION; 
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
--  再设置密码
mysql> SET PASSWORD = PASSWORD('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)
-- 设置权限
mysql> ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER;
Query OK, 0 rows affected (0.00 sec)
-- 刷新权限 
mysql>  flush privileges;
Query OK, 0 rows affected (0.01 sec)
-- 设置远程登录权限
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION; 
Query OK, 0 rows affected, 1 warning (0.00 sec)

  – 在my.cnf中 添加端口,重启服务
  图片 33

-- 测试端口是否打开
[root@hsr ~]#  firewall-cmd    --query-port=3306/tcp
no
-- 防火墙设置
[root@hsr ~]# firewall-cmd --zone=public --add-port=3306/tcp --permanent
success
-- 重新加载
[root@hsr ~]# firewall-cmd --reload
success
-- 再次测试端口
[root@hsr ~]# firewall-cmd    --query-port=3306/tcp
yes

  – 在windows端拼通成功
  图片 34

  — 最后使用SQLyog连接成功
  图片 35
  图片 36

  

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图