mysql5.7 二进制安装包安装

• 下载二进制安装包(加速地址)

 wget  http://qiniudns.2fei.top/mysql/mysql-5.7.25-linux-glibc2.12-x86_64.tar.gz


root@VM-0-3-ubuntu:~# cd /usr/local/
root@VM-0-3-ubuntu:/usr/local# wget  http://qiniudns.2fei.top/mysql/mysql-5.7.25-linux-glibc2.12-x86_64.tar.gz

安装准备

解压前 md5值校验

#官网5.7.25 MD5值
(mysql-5.7.25-linux-glibc2.12-x86_64.tar.gz)	MD5: d241f5dd6527cf1f9ff39449538c1eb1 
#下载后校验
[root@VM_0_3_centos local]# md5sum mysql-5.7.25-linux-glibc2.12-x86_64.tar.gz
d241f5dd6527cf1f9ff39449538c1eb1  mysql-5.7.25-linux-glibc2.12-x86_64.tar.gz

注意:
安装前检查 /etc/my.cnf or the /etc/mysql

*   If you have previously installed MySQL using your operating system native package management system, such as Yum or APT, you may experience problems installing using a native binary. Make sure your previous MySQL installation has been removed entirely (using your package management system), and that any additional files, such as old versions of your data files, have also been removed. You should also check for configuration files such as `/etc/my.cnf` or the `/etc/mysql` directory and delete them.

    For information about replacing third-party packages with official MySQL packages, see the related [APT guide](https://dev.mysql.com/doc/mysql-apt-repo-quick-guide/en/) or [Yum guide](https://dev.mysql.com/doc/refman/5.7/en/replace-third-party-yum.html "2.5.2 Replacing a Third-Party Distribution of MySQL Using the MySQL Yum Repository").

*   MySQL has a dependency on the `libaio` library. Data directory initialization and subsequent server startup steps will fail if this library is not installed locally. If necessary, install it using the appropriate package manager. For example, on Yum-based systems:

• 依赖
• 5.7.19 以后 依赖 libnuma 库

#----------- 官网提供脚本---------------
#yum 方式
shell> yum search libaio 
shell> yum install libaio
#apt 方式
shell> apt-cache search libaio
shell> apt-get install libaio1
 
For MySQL 5.7.19 and later: Support for Non-Uniform Memory Access (NUMA) has been added to the generic Linux build, which has a dependency now on the libnuma library; if the library has not been installed on your system, use you system's package manager to search for and install it (see the preceding item for some sample commands).

SLES 11: As of MySQL 5.7.19, the Linux Generic tarball package format is EL6 instead of EL5. As a side effect, the MySQL client bin/mysql needs libtinfo.so.5.

开始安装(参考官网)

shell> groupadd mysql
shell> useradd -r -g mysql -s /bin/false mysql
shell> cd /usr/local
#解压
shell> tar -zxvf mysql-5.7.25-linux-glibc2.12-x86_64.tar.gz
#软连接 (别名)
shell> ln -s mysql-5.7.25-linux-glibc2.12-x86_64 mysql
shell> cd mysql
shell> mkdir mysql-files
shell> chown mysql:mysql mysql-files
shell> chmod 750 mysql-files
#注意生成的临时密码  A temporary password is generated for root@localhost: mj)A.o%ey4n7
# 初始化不指定密码 bin/mysqld --initialize-insecure --user=mysql
shell> bin/mysqld --initialize --user=mysql 
#生成ssl
shell> bin/mysql_ssl_rsa_setup   
#   mysqld_safe 方式启动        
shell> bin/mysqld_safe --user=mysql &
# Next command is optional (可选 )
#
shell> cp support-files/mysql.server /etc/init.d/mysql.server

• 添加环境变量

vim /etc/profile
# 追加path
export PATH=/usr/local/mysql/bin:$PATH
#重启
source /etc/profile
root@VM-0-3-ubuntu:/usr/local# source /etc/profile

• 启动mysql(启动/重启/停止)

/etc/init.d/mysql.server start / restart / stop

root@VM-0-3-ubuntu:/usr/local# /etc/init.d/mysql.server start
[ ok ] Starting mysql.server (via systemctl): mysql.server.service.

#查看mysql 后台进程
root@VM-0-3-ubuntu:/usr/local# ps -ef|grep mysqld
root      9422     1  0 10:35 ?        00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/data/VM-0-3-ubuntu.pid
mysql     9521  9422  0 10:35 ?        00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=VM-0-3-ubuntu.err --pid-file=/usr/local/mysql/data/VM-0-3-ubuntu.pid
root     10210  7484  0 10:40 pts/2    00:00:00 grep --color=auto mysqld
root@VM-0-3-ubuntu:/usr/local# 

• 登陆mysql

--修改密码

mysql> set password=password('root');

--设置root账户的host地址（修改了才可以远程连接）

mysql>grant all privileges on *.* to 'root'@'%' identified by 'root';
mysql>flush privileges;

mysql root密码忘记了？

1. 挂维护页
2. 关数据库
root@VM-0-3-ubuntu:~# /etc/init.d/mysql.server stop
3. 跳过授权启动
root@VM-0-3-ubuntu:~# mysqld_safe --skip-grant-tables --skip-networking &
--skip-grant-tables : 连接层关闭验证模块，所有验证表不加载。
--skip-networking ：连接层关闭TCP/IP协议，禁止远程访问。
4. 改密码
mysql> alter user root@'localhost' identified by '456';
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
mysql>
mysql>
mysql>
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> alter user root@'localhost' identified by '456';
Query OK, 0 rows affected (0.00 sec)
mysql>exit;
5.正常开启业务

##关于初始化的新特性（5.6 和 5.7 ）

5.6 版本的初始化程序：
/data/mysql/scripts/mysql_install_db --user=mysql --basedir=/data/mysql --datadir=/data/mysql/data
初始化完成后：无密码和无安全策略的

5.7 版本的初始化程序：
（1）
/data/mysql/bin/mysqld --initialize --user=mysql --basedir=/data/mysql --datadir=/data/mysql/data
（2）
/data/mysql/bin/mysqld --initialize-insecure  --user=mysql --basedir=/data/mysql --datadir=/data/mysql/data

提示：5.7以后初始化命令变为bin/mysqld命令,提供了两种初始化方式
第一种初始化方式：开启临时密码和安全策略
第二种初始化方式和之前版本一样，是无密码无安全策略。
演示：

2019-04-03T03:40:15.595883Z 1 [Note] A temporary password is generated for root@localhost: **MWht)!4%sa,3**
安全策略？
密码的复杂度进行了要求，对于密码的过期时间设置了限制

Mysql8创建用户与授权

MySQL 8.0中默认的身份认证插件是caching_sha2_password，替代了之前的mysql_native_password。
所以当你进行navicat进行登录的时候可能会导致连接不上，你可以用控制器进行登录，
然后
设置密码永不过期，不然到一定的时间会过期

alter user 'root'@'localhost' identified by 'root' password expire never;

设置加密规则为mysql_native_password ，重要的是这步，改了加密规则

alter user 'root'@'localhost' identified with mysql_native_password by 'root';

一. 创建用户

命令:

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

说明：

• username：你将创建的用户名
• host：指定该用户在哪个主机上可以登陆，如果是本地用户可用localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符%
• password：该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器

例子：

CREATE USER 'dog'@'localhost' IDENTIFIED BY '123456';
CREATE USER 'pig'@'192.168.1.101_' IDENDIFIED BY '123456';
CREATE USER 'pig'@'%' IDENTIFIED BY '123456';
CREATE USER 'pig'@'%' IDENTIFIED BY '';
CREATE USER 'pig'@'%';

二. 授权:

命令:

GRANT privileges ON databasename.tablename TO 'username'@'host'

说明:

• privileges：用户的操作权限，如SELECT，INSERT，UPDATE等，如果要授予所的权限则使用ALL
• databasename：数据库名
• tablename：表名，如果要授予该用户对所有数据库和表的相应操作权限则可用表示，如.*
  例子:
  GRANT SELECT, INSERT ON test.user TO ‘pig’@‘%’;
  GRANT ALL ON . TO ‘pig’@‘%’;
  GRANT ALL ON maindataplus.* TO ‘pig’@‘%’;

注意:

用以上命令授权的用户不能给其它用户授权，如果想让该用户可以授权，用以下命令:

GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;

三.设置与更改用户密码

命令:

SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');

如果是当前登陆用户用:

SET PASSWORD = PASSWORD("newpassword");

例子:

SET PASSWORD FOR 'pig'@'%' = PASSWORD("123456");

四. 撤销用户权限

命令:

REVOKE privilege ON databasename.tablename FROM 'username'@'host';

说明:

privilege, databasename, tablename：同授权部分

例子:

REVOKE SELECT ON *.* FROM 'pig'@'%';

注意:

假如你在给用户’pig’@‘%‘授权的时候是这样的（或类似的）：GRANT SELECT ON test.user TO ‘pig’@’%’，则在使用REVOKE SELECT ON . FROM ‘pig’@‘%’;命令并不能撤销该用户对test数据库中user表的SELECT操作。相反，如果授权使用的是GRANT SELECT ON . TO ‘pig’@‘%’;则REVOKE SELECT ON test.user FROM ‘pig’@‘%’;命令也不能撤销该用户对test数据库中user表的Select权限。

具体信息可以用命令SHOW GRANTS FOR ‘pig’@‘%’; 查看。

五.删除用户

命令:

DROP USER 'username'@'host';