Centos7笔记之MariaDB安装

 

一、目标

centos7下MariaDB服务器搭建

二、平台

centos7.6,

三、解析
1.mariadb默认系统日志文件：/var/log/mariadb/mariadb.log
2.mariadb数据库默认存放目录：/var/lib/mysql/
3.mariadb主配置文件默认位置：/etc/my.cnf

四、MariaDB安装设置

1.安装mariadb和相关组件

[root@xserver ~]# yum groupinstall MariaDB mariadb-client -y

2.启动mariadb服务，并设置其开机自启

[root@xserver ~]# systemctl start mariadb
[root@xserver ~]# systemctl enable mariadb

五、MariaDB初始化密码设置
1.执行命令MariaDB安全设置工具命令

[root@xserver ~]# mysql_secure_installation

 2.回车，按提示输入两次MariaDB的root密码回车确认。

3.剩下来的一路回车到底即可。

[root@xserver ~]# 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): 
OK, successfully used password, moving on...

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] 
 ... 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] 
 ... 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] 
 - 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] 
 ... 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!
[root@xserver ~]# 

六、本机登录mariaDB

1.执行命令登录mysql，输入刚才创建的root密码并回车。

[root@xserver ~]# mysql -u root -p

2.登录成功后，可以看下mysql自带的有哪些库，用命令show databases; 

七、远程访问MariaDB数据库

1.防火墙放行mysql数据库端口3306

[root@xserver ~]# firewall-cmd --permanent --add-port=3306/tcp
success
[root@xserver ~]# firewall-cmd --reload 
success

2.修改mariadb配置文件，放开外部访问的ip
如果配置文件中有些bind ip或bind address之类的东西的话，就把他们后面相应的ip写成0.0.0.0，代表允许所有主机访问。

3.修改数据库的root权限，让root有从外部ip访问数据库的权限

3.1查看数据库系统用户和权限关联的表

[root@xserver ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 11
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 [(none)]> select User,host from mysql.user;
+------+-----------+
| User | host      |
+------+-----------+
| root | 127.0.0.1 |
| root | ::1       |
| root | localhost |
+------+-----------+
3 rows in set (0.00 sec)

MariaDB [(none)]> 

3.2修改root权限，让root能从外部任何主机访问
解释下面的命令：授权所有特权 ON对 *.*所有数据库所有表  TO给 'root'用户 '%'本机上的，IDENTIFIED BY身份验证 'kahnPWD'是我设置的mysql的root密码。
提醒：生产环境不建议写'root'@'%'，可以写成自己网段内的主机能访问，如：'root'@'192.37.21.%'。
挖槽，那要是多个网段咋弄啊？那就一个网段执行一条命令，多个网段就分开执行多条命令就行了。（执行一条，root权限表就增加一条）

MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'kahnPWD' WITH GRANT OPTION;

3.3刷新权限，让刚执行的命令生效。

MariaDB [(none)]> flush privileges;

3.4验证一下刚才修改的root的权限。（发现root又多了一行%了，代表所有主机）

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

MariaDB [(none)]> 

4.使用Navicat for MySQL远程连接一下MariaDB试试看

测试ok。

2019年9月6日23:49:50