CentOS7.X安装MariaDB数据库之YUM源安装

48 阅读 0 评论 32 点赞

我是靠谱客的博主谦让高山，最近开发中收集的这篇文章主要介绍CentOS7.X安装MariaDB数据库之YUM源安装，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

一、安装数据库前准备：

1、查看虚机版本

[root@ywl0115 ~]# cat /etc/centos-release
CentOS Linux release 7.3.1611 (Core)

2、查看虚机是否有自带MariaDB数据库

[root@ywl0115 ~]# rpm -qa | grep mariadb
mariadb-libs-5.5.52-1.el7.x86_64

3、如果有MariaDB数据库，卸载自带的

[root@ywl0115 ~]# rpm -e mariadb-libs-5.5.52-1.el7.x86_64

二、添加yum源，安装数据库

1、添加MariaDB.repo文件

MaraDB数据官网yum连接

https://downloads.mariadb.org/mariadb/repositories/#mirror=tuna&distro=CentOS&distro_release=centos7-amd64--centos7&version=10.2

选中版本后把红色框的文字拷贝到MariaDB.repo

[root@ywl0115 ~]# cd /etc/yum.repos.d/
[root@ywl0115 yum.repos.d]# ls
CentOS-Base.repo  CentOS-Debuginfo.repo  CentOS-Media.repo    CentOS-Vault.repo
CentOS-CR.repo    CentOS-fasttrack.repo  CentOS-Sources.repo
[root@ywl0115 yum.repos.d]# vim MariaDB.repo 
[root@ywl0115 yum.repos.d]# ls
CentOS-Base.repo  CentOS-Debuginfo.repo  CentOS-Media.repo    CentOS-Vault.repo
CentOS-CR.repo    CentOS-fasttrack.repo  CentOS-Sources.repo  MariaDB.repo
[root@ywl0115 yum.repos.d]# cat MariaDB.repo 
# MariaDB 10.2 CentOS repository list - created 2019-01-18 09:12 UTC
# http://downloads.mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.2/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
[root@ywl0115 yum.repos.d]#

2、使用YUM 安装数据库 MariaDB

(1)、yum安装

yum -y install MariaDB-client MariaDB-server MariaDB-devel
参数 -y 是确认，不用提示，不输时安装过程中会出现提示需要选择

MariaDB-server：服务器端
MariaDB-client ：客户端，用于连接并操作Mysql服务器
MariaDB-devel：包含开发首要的文件和一些静态库，可以不安装，如果你想要编译其它MySQL客户端，例如Perl模块，则需要安装该RPM包

（2）、启动数据库

[root@ywl0115 ~]# systemctl start mariadb ##开启
[root@ywl0115 ~]# systemctl status mariadb ## 查看状态
● mariadb.service - MariaDB 10.2.21 database server
   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
  Drop-In: /etc/systemd/system/mariadb.service.d
           └─migrated-from-my.cnf-settings.conf
   Active: active (running) since Fri 2019-01-18 17:49:39 CST; 8s ago
     Docs: man:mysqld(8)
           https://mariadb.com/kb/en/library/systemd/
  Process: 2598 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
  Process: 2553 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ]   && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS)
  Process: 2551 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
 Main PID: 2565 (mysqld)
   Status: "Taking your SQL requests now..."
   CGroup: /system.slice/mariadb.service
           └─2565 /usr/sbin/mysqld

Jan 18 17:49:39 ywl0115.novalocal mysqld[2565]: 2019-01-18 17:49:39 140654635174016 [Note] Pl...d.
Jan 18 17:49:39 ywl0115.novalocal mysqld[2565]: 2019-01-18 17:49:39 140653568702208 [Note] In...39
Jan 18 17:49:39 ywl0115.novalocal mysqld[2565]: 2019-01-18 17:49:39 140654635174016 [Note] Se...'.
Jan 18 17:49:39 ywl0115.novalocal mysqld[2565]: 2019-01-18 17:49:39 140654543447808 [Warning]...de
Jan 18 17:49:39 ywl0115.novalocal mysqld[2565]: 2019-01-18 17:49:39 140654543447808 [Warning]...de
Jan 18 17:49:39 ywl0115.novalocal mysqld[2565]: 2019-01-18 17:49:39 140654635174016 [Note] Re...ed
Jan 18 17:49:39 ywl0115.novalocal mysqld[2565]: 2019-01-18 17:49:39 140654635174016 [Note] Ad...le
Jan 18 17:49:39 ywl0115.novalocal mysqld[2565]: 2019-01-18 17:49:39 140654635174016 [Note] /u...s.
Jan 18 17:49:39 ywl0115.novalocal mysqld[2565]: Version: '10.2.21-MariaDB'  socket: '/var/lib...er
Jan 18 17:49:39 ywl0115.novalocal systemd[1]: Started MariaDB 10.2.21 database server.
Hint: Some lines were ellipsized, use -l to show in full.

如果看到：

Active: active (running) since Fri 2019-01-18 17:49:39 CST; 8s ago

启动成功。

（3）、MariaDB安全配置（这是和MySQL最大的不同）

输入命令：
    mysql_secure_installation

①、首先是设置密码，会提示先输入密码
Enter current password for root (enter for none):<–初次运行直接回车
②、设置密码
Set root password? [Y/n] <– 是否设置root用户密码，输入y并回车或直接回车
	New password: <– 设置root用户的密码
	Re-enter new password: <– 再输入一次你设置的密码
Remove anonymous users? [Y/n] <– 是否删除匿名用户，回车
Disallow root login remotely? [Y/n] <–是否禁止root远程登录,回车,
Remove test database and access to it? [Y/n] <– 是否删除test数据库，回车
Reload privilege tables now? [Y/n] <– 是否重新加载权限表，回车

[root@ywl0115 ~]# mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none): 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

You already have a root password set, so you can safely answer 'n'.

Change the root password? [Y/n] te^H^Hy
You already have a root password set, so you can safely answer 'n'.

Change the root password? [Y/n] y
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] y
 ... Success!

By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

注：若后续想修改安全设置，使用命令：

mysql_secure_installation

（4）、初始化MariaDB完成，接下来测试登录

mysql -uroot -p123456 <- 123456设的密码

（5）、配置MairaDB的字符集

# 编辑/etc/my.cnf

vim /etc/my.cnf

配置【my.cnf】前：

# 在[mysqld]标签下添加下面内容，没有自己添加

[mysqld]
# 默认存储引擎
default-storage-engine = innodb

# 独立表空间
innodb_file_per_table

# 设置最大连接（用户）数
max_connections = 4096

# 排序规则 
collation-server = utf8_general_ci

# 服务器字符集
character-set-server = utf8



[mysqld_safe]

log-error=/var/log/mariadb/mariadb.log

pid-file=/var/rn/mariadb/mariadb.pid

配置【my.cnf】后

# 编辑/etc/my.cnf.d/client.cnf(mariadb没有这个文件，可不配置)

vim /etc/my.cnf.d/client.cnf

# 在[client]标签下添加下面内容

default-character-set=utf8

# 编辑/etc/my.cnf.d/mysql-clients.cnf

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

# 在[mysql]标签下添加下面内容

default-character-set=utf8

配置完成后 systemctl restart mariadb 重启服务。

Show variables like “%character%”;

三、用户基本操作

（1）、创建用户命令

create user username@localhost identified by 'password';

（2）、新用户创建完成，但是此刻如果以此用户登陆的话，会报错，因为我们还没有为这个用户分配相应权限，分配权限的命令如下：

# 授予username用户在所有数据库上的所有权限：

GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password';

#授予username只能执行 select 和 update 命令：

GRANT SELECT, UPDATE ON wordpress.* TO 'username'@'localhost' IDENTIFIED BY 'password';


格式：
    ALL PRIVILEGES ：表示所有权限
    *.* ： 表示所有 数据库 所有表
    'username'@'localhost' 表示从本地库主机登陆的username用户
    用户地址可以是localhost，也可以是ip地址、机器名字、域名。也可以用’%'表示从任何地址连接。
    identified by 'password'  表示 username用户的密码
    with grant option  表示该用户可以将这些权限赋予其它用户

例子：
    mysql> grant all privileges on *.*  to 'root'@'%' identified by '123456' with grant option;

（3）、如果此时发现刚刚给的权限太大了，如果我们只是想授予它在某个数据库上的权限，那么需要切换到root 用户撤销刚才的权限，重新授权：

REVOKE ALL PRIVILEGES ON *.* FROM 'username'@'localhost';
GRANT ALL PRIVILEGES ON wordpress.* TO 'username'@'localhost' IDENTIFIED BY 'password';

(4）、每当调整权限后，通常需要执行以下语句刷新权限

FLUSH PRIVILEGES;

权限列表:
ALTER: 修改表和索引。
CREATE: 创建数据库和表。
DELETE: 删除表中已有的记录。
DROP: 抛弃(删除)数据库和表。
INDEX: 创建或抛弃索引。
INSERT: 向表中插入新行。
REFERENCE: 未用。
SELECT: 检索表中的记录。
UPDATE: 修改现存表记录。
FILE: 读或写服务器上的文件。
PROCESS: 查看服务器中执行的线程信息或杀死线程。
RELOAD: 重载授权表或清空日志、主机缓存或表缓存。
SHUTDOWN: 关闭服务器。
ALL: 所有权限，ALL PRIVILEGES同义词。
USAGE: 特殊的 "无权限" 权限。
用 户账户包括 "username" 和 "host" 两部分，后者表示该用户被允许从何地接入。tom@'%' 表示任何地址，默认可以省略。还可以是 "tom@192.168.1.%"、"tom@%.abc.com" 等。数据库格式为 db@table，可以是 "test.*" 或 "*.*"，前者表示 test 数据库的所有表，后者表示所有数据库的所有表。
子句 "WITH GRANT OPTION" 表示该用户可以为其他用户分配权限。