基础教程：7、MySQL/MariaDB安装，mysqlmariadb

基础教程：7、MySQL/MariaDB安装，mysqlmariadb

基础教程：7、MySQL/MariaDB安装

CentOS 7.x下可以直接通过yum命令安装MariaDB

7.1 安装MariaDB

[root@node1 ~]# yum install -y mariadb mariadb-server

7.2 配置文件

（1）新建数据目录

[root@node1 ~]# mkdir -p /data/mysql
[root@node1 ~]# chown -R mysql:mysql /data/mysql
[root@node1 ~]# chmod 777 /data/mysql

（2）修改配置文件

[root@node1 ~]# vi /etc/my.cnf
[root@node1 ~]# cat /etc/my.cnf
[mysqld]
character-set-server=utf8
datadir=/data/mysql
socket=/data/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

[client]
default-character-set=utf8
socket=/data/mysql/mysql.sock
[mysql]
default-character-set=utf8
[root@node1 ~]#

7.3 开机启动

[root@node1 ~]# systemctl start mariadb
[root@node1 ~]# systemctl enable mariadb
Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.
[root@node1 ~]# 

7.4 无密码登录

[root@node1 ~]# mysql -uroot
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
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)]> 

7.5 用户管理

（1）查询所有用户

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

MariaDB [(none)]>

（2）root本地授权

MariaDB [(none)]> grant all privileges on *.* to root@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> 

（3）root远程授权

MariaDB [(none)]> grant all privileges on *.* to root@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> 

（4）删除多余用户

MariaDB [(none)]> delete from mysql.user where user='';
Query OK, 2 rows affected (0.00 sec)

MariaDB [(none)]> delete from mysql.user where password='';
Query OK, 3 rows affected (0.00 sec)

（5）重新查看用户

MariaDB [(none)]> select Host,User,Password from mysql.user;
+-----------+------+-------------------------------------------+
| Host      | User | Password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| %         | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+------+-------------------------------------------+
2 rows in set (0.00 sec)

MariaDB [(none)]> 

（6）退出

MariaDB [(none)]> exit
Bye
[root@node1 ~]#

7.6 测试root登录

（1）root本地登录

[root@node1 ~]# mysql -uroot -p123456
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
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)]> exit
Bye
[root@node1 ~]#

（2）root远程登录

[root@node1 ~]# mysql -h192.168.60.101 -uroot -p123456
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 7
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)]> exit
Bye
[root@node1 ~]#

7.7 异常问题处理

（1）ERROR 1045 (28000): Access denied for user ‘root’@‘node1’ (using password: YES)

[root@node1 ~]# mysql -h192.168.60.101 -uroot -p123456
ERROR 1045 (28000): Access denied for user 'root'@'node1' (using password: YES)
[root@node1 ~]#

解决办法：

[root@node1 ~]# mysql -uroot -p123456
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 6
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)]> grant all privileges on *.* to root@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> exit
Bye
[root@node1 ~]# mysql -h192.168.60.101 -uroot -p123456
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 7
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)]> 

（2）ERROR 2003 (HY000): Can’t connect to MySQL server on ‘192.168.60.102’ (111)

[root@node1~]# mysql -h192.168.60.101  -uroot -p123456
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.60.102' (111)
[root@node1 ~]# mysql -hlocalhost  -uroot -pTpam1234
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 7
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> exit
Bye
[root@node1 ~]# 

查找原因

[root@node1 ~]# systemctl status mariadb
● mariadb.service - MariaDB database server
   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
   Active: failed (Result: exit-code) since Mon 2018-12-24 15:48:09 CST; 1s ago
  Process: 17348 ExecStartPost=/usr/libexec/mariadb-wait-ready $MAINPID (code=exited, status=0/SUCCESS)
  Process: 17347 ExecStart=/usr/bin/mysqld_safe --basedir=/usr (code=exited, status=1/FAILURE)
  Process: 17314 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir %n (code=exited, status=0/SUCCESS)
 Main PID: 17347 (code=exited, status=1/FAILURE)

Dec 24 15:48:09 node1 systemd[1]: Starting MariaDB database server...
Dec 24 15:48:09 node1 mariadb-prepare-db-dir[17314]: Database MariaDB is probably initialized in /tpdata/mysql already, nothing is done.
Dec 24 15:48:09 node1 mariadb-prepare-db-dir[17314]: If this is not the case, make sure the /tpdata/mysql is empty before running mariadb-prepare-db-dir.
Dec 24 15:48:09 node1 systemd[1]: Started MariaDB database server.
Dec 24 15:48:09 node1 mysqld_safe[17347]: 181224 15:48:09 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
Dec 24 15:48:09 node1 mysqld_safe[17347]: 181224 15:48:09 mysqld_safe A mysqld process already exists
Dec 24 15:48:09 node1 systemd[1]: mariadb.service: main process exited, code=exited, status=1/FAILURE
Dec 24 15:48:09 node1 systemd[1]: Unit mariadb.service entered failed state.
Dec 24 15:48:09 node1 systemd[1]: mariadb.service failed.
[root@elastic1 ~]#

[root@node1 ~]# ps -aux | grep mysqld
root     15927  0.0  0.0 113304  1612 pts/0    S    15:31   0:00 /bin/sh /usr/bin/mysqld_safe --user=mysql --skip-grant-tables --skip-networking
mysql    16112  0.0  0.5 842928 88908 pts/0    Sl   15:31   0:00 /usr/libexec/mysqld --basedir=/usr --datadir=/tpdata/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --skip-grant-tables --skip-networking --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/run/mariadb/mariadb.pid --socket=/tpdata/mysql/mysql.sock
root     17513  0.0  0.0 112704   952 pts/0    S+   15:49   0:00 grep --color=auto mysqld
[root@node1 ~]# kill -9 15927
[root@node1 ~]# kill -9 16112
[1]+  Killed                  mysqld_safe --user=mysql --skip-grant-tables --skip-networking
[root@node1 ~]# ps -aux | grep mysqld
root     17549  0.0  0.0 112704   956 pts/0    S+   15:50   0:00 grep --color=auto mysqld

[root@node1 ~]# systemctl restart mariadb
[root@node1 ~]# systemctl status mariadb
● mariadb.service - MariaDB database server
   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
   Active: active (running) since Mon 2018-12-24 15:50:54 CST; 5s ago
  Process: 17590 ExecStartPost=/usr/libexec/mariadb-wait-ready $MAINPID (code=exited, status=0/SUCCESS)
  Process: 17556 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir %n (code=exited, status=0/SUCCESS)
 Main PID: 17589 (mysqld_safe)
    Tasks: 20
   CGroup: /system.slice/mariadb.service
           ├─17589 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
           └─17765 /usr/libexec/mysqld --basedir=/usr --datadir=/tpdata/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/run/mariadb/ma...

Dec 24 15:50:52 node1 systemd[1]: Starting MariaDB database server...
Dec 24 15:50:52 node1 mariadb-prepare-db-dir[17556]: Database MariaDB is probably initialized in /tpdata/mysql already, nothing is done.
Dec 24 15:50:52 node1 mariadb-prepare-db-dir[17556]: If this is not the case, make sure the /tpdata/mysql is empty before running mariadb-prepare-db-dir.
Dec 24 15:50:52 node1 mysqld_safe[17589]: 181224 15:50:52 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
Dec 24 15:50:53 node1 mysqld_safe[17589]: 181224 15:50:53 mysqld_safe Starting mysqld daemon with databases from /tpdata/mysql
Dec 24 15:50:54 node1 systemd[1]: Started MariaDB database server.

验证，可以远程登录

[root@node1 ~]# mysql -h192.168.60.101  -uroot -p123456
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> exit
Bye
[root@node1 ~]#