上一篇文章,自己一顿操作完后,发现centos7之后用MySQL确实是一个问题,最终通过各种尝试虽然也勉强用上了,但是思前想后,了解一番后,尝试在centos上安装使用mariadb,以下是相关安装个过程,主要解决windows上使用navicat访问连接失败的问题。
实际上MariaDB的目的是完全兼容MySQL,包括API和命令行,使之能轻松成为MySQL的代替品。使用上基本兼容,性能上各有千秋。开始正题,centos7安装mariadb。
1.环境准备
我的系统环境如下。
[root@VM-4-6-centos ~]# uname -a
Linux VM-4-6-centos 3.10.0-1160.71.1.el7.x86_64 #1 SMP Tue Jun 28 15:37:28 UTC 2022 x86_64 x86_64 x86_64 GNU/Linux
1.1查看已安装的包
[root@VM-4-6-centos ~]# rpm -qa | grep mysql
mysql57-community-release-el7-10.noarch
1.2卸载已安装的MySQL相关包
命令:rpm -e --nodeps mariadb-xxxx。
[root@VM-4-6-centos ~]# rpm -e --nodeps mysql57-community-release-el7-10.noarch
[root@VM-4-6-centos ~]# rpm -qa | grep mysql
[root@VM-4-6-centos ~]# ^C
2、安装mariadb
2.1、安装
命令:yum install mariadb-server
在提示Is this ok [y/d/N]: y
2.2、 如果下载不成功,清理安装文件
命令:yum clean all
2.3、 确认下载安装
2.4、安装完成
此时执行命令rpm -qa | grep mariadb,可以查看安装的内容。
[root@VM-4-6-centos ~]# rpm -qa | grep mariadb
mariadb-server-5.5.68-1.el7.x86_64
mariadb-libs-5.5.68-1.el7.x86_64
mariadb-5.5.68-1.el7.x86_64
3、 数据库常用命令
1. 启动命令 systemctl start mariadb
2. 重启命令 systemctl restart mariadb
3. 关闭命令 systemctl stop mariadb
4. 开机自起 systemctl enable mariadb
5. 关闭自起 systemctl disable mariadb
4、初始化数据库
4.1、执行命令:mysql_secure_installation
如下所示,提示输入root用户密码, 安装后初次进入,密码为空,直接回车就可以 。
Enter current password for root (enter for none):
OK, successfully used password, moving on...
4.2、设置root用户密码
然后弹出是否设置root用户密码,输入y,然后回车
输入新密码,回车后在重复输入确认密码,回车完成密码设置,然后弹出是否删除匿名用户,输入y,回车完成匿名用户删除。
etting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.
Set root password? [Y/n] y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
... Success!
4.3、删除匿名用户
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.
大致翻译为:默认情况下,MariaDB安装具有匿名用户,允许任何人无需创建用户帐户即可登录MariaDB,这仅用于测试,并使安装更加顺利。您应该在进入生产环境之前删除它们。
Remove anonymous users? [Y/n] y
... Success!
4.4、允许root远程登录
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!
通常,只允许root从“localhost”连接。这确保有人无法从网络中猜出根密码。
输入y,回车,允许root远程登录,然后选择是否删除test测试库
4.5、删除test测试库
Remove test database and access to it? [Y/n] y
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!
输入y,回车,删除test库,然后选择是否新加载权限
4.6、重新加载权限
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!
入y,回车,安装完成!
4.7数据库登录
初始化MariaDB完成,接下来测试登录,如下所示,输入mysql -u root -p
命令和密码 登录进去。
[root@VM-4-6-centos ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 13
Server version: 5.5.68-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)]>
4.8基本操作
后面的基本操作就基本和MySQL完全类似了。
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
5.Navicat链接失败问题处理。
按照如下图所示,选择mariaDB或者腾讯云均可,输入正确的主机IP,用户名和口令,点击链接测试出现链接失败的提示。
需要分别从以下两步解决。
5.1云服务器防火墙添加放行数据库访问端口
具体更改见各云服务器官网云服务器-管理-防火墙-添加规则,放行3306端口
如果是在虚拟机上安装,则执行systemctl stop firewalld关闭防火墙。
5.2云服务器防火墙添加放行数据库访问端口
在控制台,输入输入mysql -u root -p进入MariaDB操作界面
[root@VM-4-6-centos sysconfig]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 17
Server version: 5.5.68-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.
输入\s查看MariaDB当前属性
MariaDB [(none)]> \s
--------------
mysql Ver 15.1 Distrib 5.5.68-MariaDB, for Linux (x86_64) using readline 5.1
Connection id: 17
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server: MariaDB
Server version: 5.5.68-MariaDB MariaDB Server
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 4 hours 10 min 17 sec
--------------
输入以下命令,为用户名为root,密码为'111111'的用户授予所有表全部权限
MariaDB [(none)]> grant all privileges on *.* to 'root'@'%' identified by '111111' with grant option;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]>
连接成功~ 可以在Windows端操作CentOS 7云服务器的MariaDB数据库。如下图所示。
经过上面一顿折腾,已经可以在自己电脑上用Navicat等工具连接数据库了。