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

傷城~ 2022-04-10 03:40 206阅读 0赞

短网址：[http://x.co/6nc88][http_x.co_6nc88]

## 基础教程：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）查询所有用户

（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）重新查看用户

（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 ~]#

[http_x.co_6nc88]: http://x.co/6nc88