Mariadb 介绍

MySQL之父Widenius先生离开了Sun之后，觉得依靠Sun/Oracle来发展MySQL，实在很不靠谱，于是决定另开分支，这个分支的名字叫做MariaDB。主要由开源社区在维护，采用GPL授权许可。
MariaDB的目的是完全兼容MySQL，包括API和命令行，使之能轻松成为MySQL的代替品。
MariaDB跟MySQL在绝大多数方面是兼容的，对于开发者来说，几乎感觉不到任何不同。

基础环境

• CentOS 7，详见《IT基础设施：CentOS安装》

安装过程

使用包管理器进行安装，过程非常简单，主要是在进行初始化，和远程授权访问的地方需要注意一下。

安装并启动服务

直接使用yum安装，这样安装得到的是5.5.60版本

yum -y install mariadb mariadb-server
systemctl start mariadb
systemctl enable mariadb

防火墙放开端口

如果不需要外部管理，可以忽略此步，因为对外开放数据库连接将增加主机的风险性。

firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --reload

初始化数据库

输入mysql_secure_installation开始进行数据库初始化

[root@dotnet203 /]# 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密码，如果是新安装，则是空，直接回车。

Enter current password for root (enter for none): 
OK, successfully used password, moving on...

第二步：设置root密码

Setting 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!

第三步：移除匿名用户

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!

第四步：允许远程登录，如果只是本机使用，不需要外部登录，可以输入n拒绝

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 -u root -p，再输入刚才设置的密码，我们就进入了mariadb控制台，控制台上显示了当前的服务器版本为5.5.60，而提示符标识也显示为MariaDB[(none)]，有别于MySql

[root@dotnet203 /]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 5.5.60-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)]> 

远程连接

根据上面的初始化设置，我已经允许了远程连接，Mariadb可以使用Oracle Mysql Workbench进行管理，我们配置一下。

image.png

点击Test Connection测试是否可以连上

无法连接

回到控制台，进入mysql数据库查看用户配置

首先切换当前使用的数据库为mysql，这是一个系统数据库

MariaDB [(none)]> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mysql]> 

查询user表中的root用户配置

MariaDB [mysql]> select host,user from user;
+-----------+------+
| host      | user |
+-----------+------+
| 127.0.0.1 | root |
| ::1       | root |
| localhost | root |
+-----------+------+
3 rows in set (0.00 sec)

MariaDB [mysql]> 

如上面显示，发现当前root帐号是只允许本地连接的（127.0.0.1/localhost/::1均代表本机），我们需要增加一个远程连接，允许192.168.0.0网段的连接，密码为root。

MariaDB [mysql]> GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.%.%' IDENTIFIED BY 'root' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> 

连接成功

数据导入导出

通过mysqldump ，我们可以很方便地进行数据的导出和导入，但由于是生成SQL方式的导出导入，不适合大数据量的操作。

导出

mysqldump -u root -prootabcd$@32 webdb>/home/webdb_20181201.sql

导入

mysql -u root -prootabcd$@32 webdb</home/webdb_20181201.sql

PS

Mysql 8.0以后的用户授权

CREATE USER `root`@`%` IDENTIFIED BY '123456Ba^';
GRANT ALL ON *.* TO `root`@`%` WITH GRANT OPTION;