Centos6.8安装MySQL5.6 新建用户并授权

MySQL是一个关系型数据库管理系统，是目前最流行的关系型数据库管理系统之一，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，增加了速度并提高了灵活性。 mysql由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发都选择 MySQL 作为网站数据库。之前介绍了php的安装下面介绍mysql的安装。

查看相关环境

# cat /etc/redhat-release
CentOS release 6.8 (Final)
# uname -a
Linux linux-node1.syaving.com 2.6.32-642.el6.x86_64 #1 SMP Tue May 10 17:27:01 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux
# hostname
linux-node1.syaving.com
# hostname -I
192.168.1.110

否安装其他版本的MySQL

# yum list installed | grep mysql
mysql-libs.x86_64 5.1.73-7.el6 @anaconda-CentOS-201605220104.x86_64/6.8
# yum -y remove mysql-libs.x86_64

下载新版本MySQL

# wget http://repo.mysql.com/mysql-community-release-el6-5.noarch.rpm
--2017-11-17 10:23-- http://repo.mysql.com/mysql-community-release-el6-5.noarch.rpm
Resolving repo.mysql.com... 104.127.195.16
Connecting to repo.mysql.com|104.127.195.16|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 5824 (5.7K) [application/x-redhat-package-manager]
Saving to: “mysql-community-release-el6-5.noarch.rpm”
100%[=======================================================================================================>] 5,824 --.-K/s in 0s
2017-11-17 10:23 (124 MB/s) - “mysql-community-release-el6-5.noarch.rpm” saved [5824/5824]
# ll
total 8
-rw-r--r--. 1 root root 5824 Nov 12 2015 mysql-community-release-el6-5.noarch.rpm
# rpm -ivh mysql-community-release-el6-5.noarch.rpm 
Preparing... ########################################### [100%]
1:mysql-community-release########################################### [100%]
# yum list installed | grep mysql
mysql-community-release.noarch

安装MYSQL数据库

# yum install mysql-community-server -y

修改MySQL密码

# mysqladmin -uroot password '123456'
Warning: Using a password on the command line interface can be insecure.

进入MySQL

# mysql -uroot -p

查看MySQL版本

# mysql -V
mysql  Ver 14.14 Distrib 5.6.38, for Linux (x86_64) using  EditLine wrapper

新建用户并授权远程连接(登录mysql下)

mysql> use mysql; //切换数据库
mysql> select host,user,password from user; //查看远程用户及权限
mysql> create user test identified by '123456'; //test用户名123456密码
mysql> grant all privileges on *.* to 'test'@'%' identified by '123456' with grant option;

之后重启服务器就可以了。

删除用户：

mysql> delete from user where user='test' and host='localhost';

MySQL创建用户错误ERROR 1396 (HY000): Operation CREATE USER failed for 'XXXX'@'XXXX'问题解决

创建用户：

mysql> create user 'test'@'%' identified by '123456';

报错： 错误ERROR 1396 (HY000): Operation CREATE USER failed for 'test'@'%'

刷新权限

mysql> flush privileges;

之后再创建用户，还是报错ERROR 1396 (HY000): Operation CREATE USER failed for 'test'@'%'

尝试删除一次：

mysql> drop user 'test'@'%';
mysql> flush privileges;
mysql> create user 'test'@'%' identified by '123456';

成功。