基础教程：7、MySQL/MariaDB安装
CentOS 7.x下可以直接通过yum命令安装MariaDB
7.1 安装MariaDB
代码语言：javascript复制[root@node1 ~]# yum install -y mariadb mariadb-server7.2 配置文件
（1）新建数据目录
代码语言：javascript复制[root@node1 ~]# mkdir -p /data/mysql
[root@node1 ~]# chown -R mysql:mysql /data/mysql
[root@node1 ~]# chmod 777 /data/mysql（2）修改配置文件
代码语言：javascript复制[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 开机启动
代码语言：javascript复制[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 无密码登录
代码语言：javascript复制[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）查询所有用户
代码语言：javascript复制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本地授权
代码语言：javascript复制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远程授权
代码语言：javascript复制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）删除多余用户
代码语言：javascript复制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）重新查看用户
代码语言：javascript复制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）退出
代码语言：javascript复制MariaDB [(none)]> exit
Bye
[root@node1 ~]#7.6 测试root登录
（1）root本地登录
代码语言：javascript复制[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远程登录
代码语言：javascript复制[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)
代码语言：javascript复制[root@node1 ~]# mysql -h192.168.60.101 -uroot -p123456
ERROR 1045 (28000): Access denied for user 'root'@'node1' (using password: YES)
[root@node1 ~]#解决办法：
代码语言：javascript复制[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)
代码语言：javascript复制[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 ~]# 查找原因
代码语言：javascript复制[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.验证，可以远程登录
代码语言：javascript复制[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 ~]# 
