linux下mariadb设置权限,Centos7 下Mariadb 安装及权限设置

安装

rpm -qa|grep mariadb

查看是否已经安装mariadb

rpm -qa | grep mysql

查看是否已经安装mysql

yum -y remove XXX

如果有则卸载

su - root

yum -y install mariadb mariadb-server

安装成功

systemctl start mariadb

设置开机启动

systemctl enable mariadb

接下来进行MariaDB的相关简单配置

mysql_secure_installation

首先是设置密码，会提示先输入密码

Enter current password for root (enter for none):

设置密码

Set root password? [Y/n]

New password:

Re-enter new password:

其他配置

Remove anonymous users? [Y/n]

Disallow root login remotely? [Y/n]

Remove test database and access to it? [Y/n]

Reload privilege tables now? [Y/n]

初始化MariaDB完成，接下来测试登录

mysql -uroot -ppassword

完成。

2、配置MariaDB的字符集

文件/etc/my.cnf

vi /etc/my.cnf

在[mysqld]标签下添加

init_connect='SET collation_connection = utf8_unicode_ci'

init_connect='SET NAMES utf8'

character-set-server=utf8

collation-server=utf8_unicode_ci

skip-character-set-client-handshake

文件/etc/my.cnf.d/client.cnf

vi /etc/my.cnf.d/client.cnf

在[client]中添加

default-character-set=utf8

文件/etc/my.cnf.d/mysql-clients.cnf

vi /etc/my.cnf.d/mysql-clients.cnf

在[mysql]中添加

default-character-set=utf8

全部配置完成，重启mariadb

systemctl restart mariadb

之后进入MariaDB查看字符集

mysql> show variables like "%character%";show variables like "%collation%";

显示为

+--------------------------+----------------------------+

| Variable_name | Value |

+--------------------------+----------------------------+

| character_set_client | utf8 |

| character_set_connection | utf8 |

| character_set_database | utf8 |

| character_set_filesystem | binary |

| character_set_results | utf8 |

| character_set_server | utf8 |

| character_set_system | utf8 |

| character_sets_dir | /usr/share/mysql/charsets/ |

+--------------------------+----------------------------+

8 rows in set (0.00 sec)

+----------------------+-----------------+

| Variable_name | Value |

+----------------------+-----------------+

| collation_connection | utf8_unicode_ci |

| collation_database | utf8_unicode_ci |

| collation_server | utf8_unicode_ci |

+----------------------+-----------------+

3 rows in set (0.00 sec)

字符集配置完成。

注意: 这个时候,用其他服务器是不能登录数据库的,需要先设置权限

3、添加用户，设置权限

创建用户命令

mysql>create user username@localhost identified by 'password';

直接创建用户并授权的命令

mysql>grant all privileges on *.* to username@'%' identified by 'password';

授予权限

如果只授予部分权限把 其中 all privileges 改为select,insert,update,delete,create,drop,index,alter,grant,references,reload,shutdown,process,file其中一部分。

例如:

登录数据库后

执行:

GRANT ALL PRIVILEGES ON *.* TO 'hive'@'10.211.55.7' IDENTIFIED BY '1234';

切换到mysql数据库

use mysql

执行

MariaDB [mysql]> SELECT User, Host FROM user;

+----------+-------------+

| User | Host |

+----------+-------------+

| hive | 10.211.55.7 |

| root | 127.0.0.1 |

| root | ::1 |

| root | localhost |

| username | localhost |

+----------+-------------+

忘记密码怎么办

停止mysql进程

systemctl stop mariadb.service

以不检查权限的方式重新启动MySQL

mysqld_safe --skip-grant-tables &

修改mysql密码

mysql -u root

update mysql.user set password=PASSWORD('newpassword') where User='root’;

flush privileges;

quit

关闭MySQL

systemctl stop mariadb.service

重新启动

systemctl start mariadb.service

—— 注意:

当关闭不了mysql进程的时候,请使用kill .