centos7部署mariadb

原创

容容. 2024-01-28 20:35:55 ©著作权

文章标签 mysql 数据库旧版 文章分类 运维

©著作权归作者所有：来自51CTO博客作者容容.的原创作品，请联系作者获取转载授权，否则将追究法律责任

基础环境配置

Mariadb是mysql数据库的最佳替代产品 postgreSQL也非常不错

检查环境

如果是新环境或者确定没有Mariadb的话可以跳过

1、检查环境有没有旧版Mariadb旧版本

[root@localhost ~]# rpm -qa | grep maeiadb
mariadb-libs-5.5 64-1.el7.x86_64

2、删除旧版本Mariadb

[root@localhost ~]# yum remove mariadb-libs -y

一、部署Mariadb

查询官方资料：https://mariadb.org

1、配置阿里云mariadb YUM仓库

[root@localhost ~]# cd /etc/yum.repos.d
[root@localhost yum.repos.d]# vi mariadb.repo
# MariaDB 10.11 CentOS repository list - created 2023-11-01 09:12 UTC
# https://mariadb.org/download/
[mariadb]
name = MariaDB
# rpm.mariadb.org is a dynamic mirror if your preferred mirror goes offline. See https://mariadb.org/mirrorbits/ for details.
# baseurl = https://rpm.mariadb.org/10.11/centos/$releasever/$basearch
baseurl = https://mirrors.aliyun.com/mariadb/yum/10.11/centos/$releasever/$basearch
module_hotfixes = 1
# gpgkey = https://rpm.mariadb.org/RPM-GPG-KEY-MariaDB
gpgkey = https://mirrors.aliyun.com/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck = 1

##更新yum缓存
[root@localhost yum.repos.d]# yum makecache

2、安装mariadb

可能需要EPEL存储库来满足galera的依赖性。pv

[root@localhost yum.repos.d]# yum install MariaDB-server MariaDB-client -y
## 安装过程中可能会报错出现这个报错后
错误：软件包：MariaDB-server-10.11.5-1.el7.centos.x86_64 (mariadb)
          需要：pv
 您可以尝试添加 --skip-broken 选项来解决该问题
 您可以尝试执行：rpm -Va --nofiles --nodigest
## 使用下面这条命令进行安装
[root@localhost yum.repos.d]# yum install epel-release -y
[root@localhost yum.repos.d]# yum install pv -y
[root@localhost yum.repos.d]# yum install mariadb mariadb-server -y

3、启动mariadb服务

## 启动mariadb
[root@localhost yum.repos.d]# systemctl start mariadb.service
## 设置开机自启
[root@localhost yum.repos.d]# systemctl enable mariadb.service
Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.
## 验证数据库3306端口是否开启
[root@localhost yum.repos.d]# ss -lntp | grep 3306
LISTEN     0      80           *:3306                     *:*                   users:(("mariadbd",pid=2290,fd=20))
LISTEN     0      80          :::3306                    :::*                   users:(("mariadbd",pid=2290,fd=21))

4、安全初始化

[root@localhost yum.repos.d]# mariadb-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
haven't set the root password yet, you should just press enter here.

Enter current password for root (enter for none):   ##默认空密码 回车即可
OK, successfully used password, moving on...

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

You already have your root account protected, so you can safely answer 'n'.

Switch to unix_socket authentication [Y/n] n  ##选择n
 ... skipping.

You already have your root account protected, so you can safely answer 'n'.

Change the root password? [Y/n] y  ##设置mariadb服务root新密码
New password:000000
Re-enter new password:000000
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  ## 选择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] n   ##选择n 后面会进行配置
 ... skipping.

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  ##选择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   ##选择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!

5、测试登录mariadb

[root@localhost yum.repos.d]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.11.5-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


MariaDB [(none)]> select user,host,plugin from mysql.user;
+-------------+-----------+-----------------------+
| User        | Host      | plugin                |
+-------------+-----------+-----------------------+
| mariadb.sys | localhost | mysql_native_password |
| root        | localhost | mysql_native_password |
| mysql       | localhost | mysql_native_password |
| PUBLIC      |           |                       |
+-------------+-----------+-----------------------+
4 rows in set (0.001 sec)

[root@localhost yum.repos.d]#

root账号没有远程登录的权限

二、Mariadb数据库的创建、删除

## 查询原有数据库
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.000 sec)

## 创建一个名字叫yuchunle的数据库
MariaDB [(none)]> CREATE DATABASE yuchunle;
Query OK, 1 row affected (0.000 sec)

## 查询创建结果
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| yuchunle           |
+--------------------+
5 rows in set (0.000 sec)

##删除一个名字叫yuchunle的数据库
MariaDB [(none)]> DROP DATABASE yuchunle;
Query OK, 0 rows affected (0.001 sec)

## 查询删除结果
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.000 sec)

三、Mariadb用户授权

root账号@添加远程登录权限及操作所有数据库的权限

## 创建账号
## create user: 这个语句用于创建一个新的用户
## 'root'@'%': 这定义了用户的用户名和允许连接的主机。在这种情况下，用户名是'root'，'%'表示允许从任何主机连接
## identified by 'root': 这指定了用户的密码，即'root'
MariaDB [(none)]> create user 'root'@'%' identified by 'root';

## 账号赋权
## grant all privileges: 这个语句用于授予用户所有权限
## on *.*: 这指定了授权的范围，*.*表示所有数据库和所有表。
## to 'root'@'%': 这定义了被授予权限的用户和允许连接的主机。在这种情况下，用户是'root'，'%'表示允许从任何主机连接
MariaDB [(none)]> grant all privileges on *.* to 'root'@'%';