Linux MariaDB数据库安装 开机自启动 远程连接(服务器架设篇)

为什么安装MariaDB而不是Mysql

更多请看(www.omob.cc

本人在工作历程中几家公司都是使用的Mysql5.7以及以前版本,在自己的阿里云上也部署的是最新的Mysql8。个人业余开发研究中喜欢MariaDB,保持技术的好奇是一个程序员的自我修养,不是么?MariaDB作为MySQL的一个主要分支目前是兼容MySql5.x的,以后可能会逐渐和MySql分道扬镳,这就是很久以后的事了,当前我们使用MariaDB还是可无缝切换的。

MariaDB是MySQL源代码的一个分支,主要由开源社区在维护,采用GPL授权许可。开发这个分支的原因之一是:甲骨文公司收购了MySQL后,有将MySQL闭源的潜在风险,因此社区采用分支的方式来避开这个风险。MariaDB是完全兼容MySQL,包括API和命令行,使之能轻松成为MySQL的代替品。在存储引擎方面,使用XtraDB来代替MySQL的InnoDB,XtraDB完全兼容InnoDB,创建一个InnoDB表内部默认会转换成XtraDB。

Percona XtraDB 是InnoDB存储引擎的增强版,用来更好地发挥最新的计算机硬件系统性能,同时还包含一些在高性能环境下的新特性。XtraDB 存储引擎是完全的向下兼容,在 MariaDB 中,XtraDB 存储引擎被标识为”ENGINE=InnoDB”,这与 InnoDB 是一样的,所以可以直接用XtraDB 替换掉 InnoDB,而不会产生任何问题。XtraDB 在 InnoDB 的基础上构建,使 XtraDB 具有更多的特性,更多的参数指标和更多的扩展。从实践的角度来看,XtraDB 在CPU多核的条件下更有效的使用内存,并且性能更高。从 MariaDB 5.1 开始就默认使用 XtraDB 存储引擎。

MariaDB由MySQL的创始人Michael (Monty) Widenius主导开发,他早前曾以10亿美元的价格,将自己创建的公司MySQL AB卖给了SUN,此后,随着SUN被甲骨文收购,MySQL的所有权也落入Oracle的手中。MariaDB名称来自Michael (Monty) Widenius的女儿Maria的名字。

安装MariaDB

我们采用MariaDB官网上的安装指导(MairaDB Cent os7 x86 yum 安装版 指南)

安装演示

先按指南创建yum文件
[root@thinkcent my_name]# vim  /etc/yum.repos.d/MariaDB.repo


# 文件里面填入以下内容后保存
# MariaDB 10.3 CentOS repository list - created 2018-07-14 08:03 UTC
# http://downloads.mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.3/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

# 开始安装 国内下载比较慢,大家要等待它自己安装完
[root@thinkcent my_name]# yum install MariaDB-server MariaDB-client


# 安装完成后出现如下提示:
Installed:
  MariaDB-client.x86_64 0:10.3.8-1.el7.centos         MariaDB-server.x86_64 0:10.3.8-1.el7.centos        

Dependency Installed:
  MariaDB-common.x86_64 0:10.3.8-1.el7.centos         MariaDB-compat.x86_64 0:10.3.8-1.el7.centos       
  boost-program-options.x86_64 0:1.53.0-27.el7        galera.x86_64 0:25.3.23-1.rhel7.el7.centos        
  lsof.x86_64 0:4.87-5.el7                            rsync.x86_64 0:3.1.2-4.el7                        

Complete!

启动MariaDB,开机自启动,初始化配置

启动数据库

# 启动
[root@thinkcent my_name]# systemctl start mariadb
# 设置开机自启动
[root@thinkcent my_name]# systemctl enable mariadb
# 查看状态 显示active (running)表示运行中
[root@thinkcent my_name]# systemctl status mariadb
● mariadb.service - MariaDB 10.3.8 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 Sat 2018-07-14 17:32:02 CST; 12s ago
     Docs: man:mysqld(8)
           https://mariadb.com/kb/en/library/systemd/
 Main PID: 2173 (mysqld)
   Status: "Taking your SQL requests now..."
   CGroup: /system.slice/mariadb.service
           └─2173 /usr/sbin/mysqld

初始化设置

# 开始安全配置
[root@thinkcent my_name]# 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.
# 需要root密码,第一次安装,我们没有root密码,默认为空,直接按Enter通过去
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.
## 设置root密码
Set 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.
# 禁止root远程登陆
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.
# 移除test数据库
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!

修改Maria监听端口

我这里以端口123代指要设置的目标端口,大家按自己想要的端口修改

# 防火墙开放MariaDB监听端口123
[root@thinkcent my_name]# firewall-cmd --zone=public --add-port=123/tcp --permanent
success
# 防火墙重启
[root@thinkcent my_name]# firewall-cmd --reload
success
# Selinux开放开放MariaDB监听端口123 注意这步,很多人的SElinx开启状态下不放行MariaDB监听这个端口导致配置失败
[root@thinkcent my_name]# semanage port -a -t mysqld_port_t -p tcp 123
# 修改配置文件 在[mysqld]下一行加入 port=123
[root@thinkcent my_name]# vim /etc/my.cnf.d/server.cnf
# 重启MariaDB
[root@thinkcent my_name]# systemctl restart mariadb


登陆MariaDB后查看监听端口看到端口由3306变成123

MariaDB [(none)]> show global variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 123   |
+---------------+-------+
1 row in set (0.002 sec)

创建数据库并添加用户

我这里以my_name,my_db代指要设置的用户和数据库,大家按自己想要的修改


# root登陆后查看有那些数据库
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.001 sec)
# root登陆后创建数据库
MariaDB [(none)]> create database my_db;
Query OK, 1 row affected (0.001 sec)
# root登陆后创建数据库成功
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| my_db           |
+--------------------+
4 rows in set (0.001 sec)
# root登陆后创建用户并设置密码
MariaDB [(none)]> CREATE USER 'my_name'@'%' IDENTIFIED BY 'my_password';
Query OK, 0 rows affected (0.001 sec)

# root登陆后为创建的用户赋予my_db的所有权限
MariaDB [(none)]> GRANT ALL ON my_db.* TO 'my_name'@'%';
Query OK, 0 rows affected (0.001 sec)
# root登陆后刷新配置
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.001 sec)
# root退出
MariaDB [(none)]> Bye
# 用新用户登陆
[my_name@thinkcent install]$ mysql -u my_name -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.8-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)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| my_db           |
+--------------------+
2 rows in set (0.001 sec)

我们上文中早已配置my_name可以远程访问my_db,通过123端口,上面我么验证了my_name的本地登陆成功后,再验证远程登陆是否成功

大家可以使用datagrip或者navicat这类windows段数据库可视化工具登陆验证,我简单附一个MariaDB的远程登陆命令

mysql -h my_hostip -P my_port -u my_name -p 
展开阅读全文

没有更多推荐了,返回首页