Centos6.X安装mysql 5.5.48的方法，mysql创建用户及权限控制

最新推荐文章于 2023-06-23 18:31:27 发布

原创最新推荐文章于 2023-06-23 18:31:27 发布 · 616 阅读

CC 4.0 BY-SA版权

工作中经常用到mysql，所以避免不了安装mysql，所以就简单记录一下，以便用到时，能够快速的安装

centos6.X basic server 版本默认安装了mysql5.1，所以先卸载mysql5.1，这种方法会卸载依赖，可以用查找mysql服务的方式用rpm卸载

yum -y remove mysql-libs-5.1*

下载对应的MySQL安装包rpm文件，可以去MySQL官方网站找到对应版本，一般需要下载3个文件

wget http://dev.mysql.com/get/Downloads/MySQL-5.5/MySQL-server-5.5.48-1.el6.x86_64.rpm --no-check-certificate
wget http://dev.mysql.com/get/Downloads/MySQL-5.5/MySQL-client-5.5.48-1.el6.x86_64.rpm --no-check-certificate
wget http://dev.mysql.com/get/Downloads/MySQL-5.5/MySQL-devel-5.5.48-1.el6.x86_64.rpm --no-check-certificate

之后执行rpm安装三个服务

rpm -ivh MySQL-server-5.5.48-1.el6.x86_64.rpm
rpm -ivh MySQL-client-5.5.48-1.el6.x86_64.rpm
rpm -ivh MySQL-devel-5.5.48-1.el6.x86_64.rpm

此后开启一下mysql服务

service mysql start

之后输入mysql即可登录，初次登入mysql没有密码

[root@h202 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.48 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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>

首先要为root设置密码，生产中需要复杂密码

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 password=password("123456") where user='root';
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

其他用户权限控制

1、新建用户 
创建test用户，密码是1234。

    MySQL -u root -p 
    CREATE USER 'test'@'localhost'  IDENTIFIED BY '123456'; #本地登录 
    CREATE USER 'test'@'%' IDENTIFIED BY '123456'; #远程登录 
    quit 
    mysql -utest -p #测试是否创建成功

2、为用户授权

a.授权格式：grant 权限 on 数据库.* to 用户名@登录主机 identified by '密码';　

b.登录MYSQL，这里以ROOT身份登录：

    mysql -u root -p

c.为用户创建一个数据库(testDB)：

    create database testDB; 
    create database testDB default charset utf8 collate utf8_general_ci;

d.授权test用户拥有testDB数据库的所有权限：

    grant all privileges on testDB.* to "test"@"%" identified by "123456"; 
    flush privileges; #刷新系统权限表

e.指定部分权限给用户:

    grant select,update on testDB.* to “test”@”localhost” identified by “1234”; 
    flush privileges; #刷新系统权限表

f.授权test用户拥有所有数据库的某些权限： 　

    grant select,delete,update,create,drop on . to test@”%” identified by “1234”; #”%” 表示对所有非本地主机授权，不包括localhost

3、删除用户

    mysql -u root -p 
    Delete FROM mysql.user Where User=”test” and Host=”localhost”; 
    flush privileges; 
    drop database testDB;

删除账户及权限：

    drop user 用户名@’%’; 
    drop user 用户名@ localhost;

4、修改指定用户密码

    mysql -u root -p 
    update mysql.user set authentication_string=password(“新密码”) where User=”test” and Host=”localhost”; 
    flush privileges;