centos7 安装配置 mariadb 服务器

实验中atom是mysql服务器端，localhost是同一网段下的客户机

安装mariadb

[root@atom ~]#yum install mysql
...
---> Package mariadb.x86_64 1:5.5.68-1.el7 will be installed
--> Finished Dependency Resolution
...
Installed:
  mariadb.x86_64 1:5.5.68-1.el7                                                                                                                           

Complete!
[root@atom ~]#yum install mariadb  #使用这个也可以，均改到开源mysql-mariadb
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
 * base: mirrors.aliyun.com
 * epel: mirror.01link.hk
 * extras: mirrors.aliyun.com
 * updates: mirrors.aliyun.com
Package 1:mariadb-5.5.68-1.el7.x86_64 already installed and latest version
Nothing to do
[root@atom ~]#mysql             
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

[root@atom ~]#rpm -ql mariadb-5.5.68-1.el7.x86_64 
/etc/my.cnf.d/client.cnf      #<------只有client配置
/usr/bin/my_print_defaults
/usr/bin/mysql
/usr/bin/mysql_find_rows
/usr/bin/mysql_waitpid
/usr/bin/mysqlaccess
/usr/bin/mysqladmin
...
[root@atom ~]#systemctl start mariadb    
Failed to start mariadb.service: Unit not found.

这样服务是启动不起来的，只安装了client不能当mysql服务器给别人用，下面安装service。

[root@atom ~]#yum install  mariadb.service    #发现没有，打错了
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
 * base: mirrors.aliyun.com
 * epel: mirror.01link.hk
 * extras: mirrors.aliyun.com
 * updates: mirrors.aliyun.com
No package mariadb.service available.
Error: Nothing to do
[root@atom ~]#yum search mariadb          #search看一下
...
mariadb-libs.i686 : The shared libraries required for MariaDB/MySQL clients
mariadb-libs.x86_64 : The shared libraries required for MariaDB/MySQL clients
mariadb-server.x86_64 : The MariaDB server and related files    #要安装这个来启动mysql服务
anope-mysql.x86_64 : MariaDB/MySQL modules for Anope IRC services
mariadb.x86_64 : A community developed branch of MySQL
mariadb-test.x86_64 : The test suite distributed with MariaD
...
[root@atom ~]#yum install mariadb-server.x86_64        #直接写mariadb-server也可以
[root@atom ~]#systemctl start mariadb              #启动成功

本地mysql安装完成，可能安的不太全后面可以直接 install mariadb* 来安装。

---

mysql报错1045的话，一般用localhost下的root登入就好了，后面进入后可以更改root用户密码。

[root@atom ~]#mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@atom ~]#mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
...
MariaDB [(none)]> use mysql
MariaDB [mysql]> set password for 'root'@'localhost' = password('123456');

开启远程

[root@atom ~]#mysql -uroot -p
Enter password: 
...
MariaDB [(none)]> use mysql;
Database changed
MariaDB [mysql]> select user,host,plugin from mysql.user;
+------+-----------+--------+
| user | host      | plugin |
+------+-----------+--------+
| root | localhost |        |
| root | atom.2ll  |        |
| root | 127.0.0.1 |        |
| root | ::1       |        |
|      | localhost |        |
|      | atom.2ll  |        |
+------+-----------+--------+

MariaDB [mysql]> CREATE USER 'tonixtom'@'192.168.31.%' IDENTIFIED  BY '123456';		#创建一个固定网段的远程用户
MariaDB [mysql]> grant all privileges on *.* to 'tonixtom'@'192.168.31.%' identified by '123456' with grant option;    #这里给的最大权限
MariaDB [mysql]> show grants for 'tonixtom'@'192.168.31.%';    #show 权限看看
MariaDB [mysql]> flush privileges;
MariaDB [mysql]> select user,host,plugin from mysql.user;
+----------+--------------+--------+
| user     | host         | plugin |
+----------+--------------+--------+
| root     | localhost    |        |
| root     | atom.2ll     |        |
| root     | 127.0.0.1    |        |
| root     | ::1          |        |
|          | localhost    |        |
|          | atom.2ll     |        |
| tonixtom | 192.168.31.% |        |
+----------+--------------+--------+

服务器端mariadb运行良好

[root@atom ~]#systemctl status mariadb.service     #查看mariadb是运行中
[root@atom ~]#netstat -lnp | grep mysql				#全网段监听
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      2113/mysqld         
unix  2      [ ACC ]     STREAM     LISTENING     29843    2113/mysqld          /var/lib/mysql/mysql.sock

修改成功后，先在服务器本机上试一下，连接测试中的tonixtom被匿名用户给顶了，登不上，问题不大。

[root@atom ~]#mysql -utonixtom -h192.168.31.177 -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'tonixtom'@'atom.2ll' (using password: YES)

在远程客户端试试，发现问题。

[root@localhost ~]#mysql -utonixtom -h192.168.31.177 -p
Enter password: 
ERROR 2003 (HY000): Can`t connect to MySQL server on '192.168.31.177' (113)
[root@localhost ~]#
[root@localhost ~]#telnet  192.168.31.177 3306        #没tel上，3306端口没开
Trying 192.168.31.177...
telnet: connect to address 192.168.31.177: No route to host
[root@localhost ~]#telnet  192.168.31.177 27017   #相反地，我的mongo运行得很好
Trying 192.168.31.177...
Connected to 192.168.31.177.
Escape character is '^]'.
^]
telnet> quit
Connection closed.

[root@atom ~]#systemctl stop firewalld.service    #关了mysql服务器端的防火墙，后面添加放行
[root@localhost ~]#telnet  192.168.31.177 3306		#3306通了
Trying 192.168.31.177...
Connected to 192.168.31.177.
Escape character is '^]'.
R
5.5.68-MariaDBL5ct>w[
[root@localhost ~]#mysql -utonixtom -h192.168.31.177  -p      #远程连接成功
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.

最后发现大多数结果都是防火墙的问题

sudo firewall-cmd --add-port=3306/tcp --permanent					#放行3306端口	
sudo firewall-cmd --reload

未开放端口

[root@localhost ~]#mysql -utonixtom -h192.168.31.177 -p
Enter password: 
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.31.177' (113)

权限设置问题

这里我弄错了通配符释意，以为*代表多主机，mysql里%代表区间内任意网段，切记。

MariaDB [mysql]> CREATE USER 'tonixtom'@'192.168.31.*' IDENTIFIED  BY '123456';		
MariaDB [mysql]> select user,host,plugin from mysql.user;  
+----------+--------------+--------+
| user     | host         | plugin |
+----------+--------------+--------+
| root     | localhost    |        |
| root     | atom.2ll     |        |
| root     | 127.0.0.1    |        |
| root     | ::1          |        |
|          | localhost    |        |
|          | atom.2ll     |        |
| tonixtom | 192.168.31.* |        |      #这是错误的设置
+----------+--------------+--------+


[root@atom ~]#mysql -utonixtom -h192.168.31.177  -p		#会权限不匹配
Enter password: 
ERROR 1130 (HY000): Host '192.168.31.177' is not allowed to connect to this MariaDB server

删除匿名用户

有时候匿名用户可能引发一些莫名的问题，可以提前作删除处理。

MariaDB [mysql]> SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
+----------------------------------+
| query                            |
+----------------------------------+
| User: 'root'@'127.0.0.1';        |
| User: 'tonixtom'@'192.168.31.%'; |
| User: 'root'@'::1';              |
| User: ''@'atom.2ll';             |  #这个就是 
| User: 'root'@'atom.2ll';         |
| User: 'root'@'localhost';        |
+----------------------------------+

MariaDB [mysql]> REVOKE ALL PRIVILEGES ON *.* FROM ''@'localhost';      #回收权利 
MariaDB [mysql]> drop user ''@'localhost';						#先回收后删除
MariaDB [mysql]> flush privileges;

---

#简单小的建表sql
create table test_user (    
	id int(20) not null primary key,
	name varchar(30) default null,
	pwd varchar(30) default null
) engine=innodb default charset=utf8;