Mariadb

地址: Mariadb官网

1.配置数据库yum源

# cd /etc/init.d/
# vim MariaDB.repo

复制

# MariaDB 10.3 CentOS repository list - created 2018-03-08 15:17 UTC
# http://mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.3/centos7-ppc64le
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

至MariaDB.repo文件,保存文件并退出

安装数据库:

# yum install mariadb-server mariadb-client -y

2.Mariadb服务

启动服务:

# systemctl start mariadb.service

停止服务:

# systemctl stop mariadb.service

重启服务:

# systemctl restart mariadb.service

查看状态

# systemctl status mariadb.service

3.配置MariaDB数据库

# mysql_secure_installation

显示支持数据库支持的编码

MariaDB [(none)]> SHOW CHARACTER SET;

配置数据库的编码

# cp /etc/my.cnf.d/client.cnf /home/client.cnf.back
# vim /etc/my.cnf.d/client.cnf
[client]
default-character-set=utf8

:wq!

# cp /etc/my.cnf.d/server.cnf /home/server.cnf.back
# vim /etc/my.cnf.d/server.cnf
# this is only for the mysqld standalone daemon
[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8

Example: Changing the default character set to UTF-8 To change the default character set from latin1 to UTF-8, the following settings should be specified in the my.cnf configuration file.

[client]
...
default-character-set=utf8mb4
...
[mysql]
...
default-character-set=utf8mb4
...
[mysqld]
...
collation-server = utf8mb4_unicode_ci
init-connect='SET NAMES utf8mb4'
character-set-server = utf8mb4
...

[mysqldump]
quick
max_allowed_packet=16M

[mysql]
no-auto-rehash
loose-abort-source-on-error

4. 用户管理

创建用户 命令:CREATE USER 'username'@'host' IDENTIFIED BY 'password';

MariaDB [(none)]> CREATE OR REPLACE USER 'test'@'%' IDENTIFIED BY 'test';

MariaDB [(none)]> SHOW WARNINGS;
MariaDB [(none)]> FLUSH PRIVILEGES;

用户授权 命令:GRANT privileges ON databasename.tablename TO 'username'@'host'

MariaDB [(none)]> GRANT SELECT,INSERT,UPDATE,DELETE ON *.* TO 'test'@'%' Identified by 'test';

MariaDB [(none)]> GRANT CREATE,ALTER,DROP ON *.* TO 'test'@'%' Identified by 'test';
MariaDB [(none)]> GRANT INDEX ON *.* TO 'test'@'%' Identified by 'test';

设置与更改用户密码 命令:SET PASSWORD FOR 'username'@'host'= PASSWORD('newpassword'); 如果是当前登陆用户用SET PASSWORD = PASSWORD("newpassword");

MariaDB [(none)]> SET PASSWORD FOR 'test'@'%'= PASSWORD('test');

撤销用户权限 命令: REVOKE privilege ON databasename.tablename FROM 'username'@'host';

MariaDB [(none)]> REVOKE SELECT ON *.* FROM 'test'@'%';

查看用户权限

MariaDB [(none)]> SHOW GRANTS FOR 'test'@'%';

删除用户 命令: DROP USER 'username'@'host';

MariaDB [(none)]> DROP USER 'test'@'%';

5.附录:MySQL中的操作权限