本文共 9929 字,大约阅读时间需要 33 分钟。
短网址:
CentOS 7.x下可以直接通过yum命令安装MariaDB
[root@node1 ~]# yum install -y mariadb mariadb-server
(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=utf8datadir=/data/mysqlsocket=/data/mysql/mysql.sock# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-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.logpid-file=/var/run/mariadb/mariadb.pid## include all files from the config directory#!includedir /etc/my.cnf.d[client]default-character-set=utf8socket=/data/mysql/mysql.sock[mysql]default-character-set=utf8[root@node1 ~]#
[root@node1 ~]# systemctl start mariadb[root@node1 ~]# systemctl enable mariadbCreated symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.[root@node1 ~]#
[root@node1 ~]# mysql -urootWelcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection id is 2Server version: 5.5.60-MariaDB MariaDB ServerCopyright (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)]>
(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)]> exitBye[root@node1 ~]#
(1)root本地登录
[root@node1 ~]# mysql -uroot -p123456Welcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection id is 3Server version: 5.5.60-MariaDB MariaDB ServerCopyright (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)]> exitBye[root@node1 ~]#
(2)root远程登录
[root@node1 ~]# mysql -h192.168.60.101 -uroot -p123456Welcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection id is 7Server version: 5.5.60-MariaDB MariaDB ServerCopyright (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)]> exitBye[root@node1 ~]#
(1)ERROR 1045 (28000): Access denied for user ‘root’@‘node1’ (using password: YES)
[root@node1 ~]# mysql -h192.168.60.101 -uroot -p123456ERROR 1045 (28000): Access denied for user 'root'@'node1' (using password: YES)[root@node1 ~]#
解决办法:
[root@node1 ~]# mysql -uroot -p123456Welcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection id is 6Server version: 5.5.60-MariaDB MariaDB ServerCopyright (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)]> exitBye[root@node1 ~]# mysql -h192.168.60.101 -uroot -p123456Welcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection id is 7Server version: 5.5.60-MariaDB MariaDB ServerCopyright (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 -p123456ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.60.102' (111)[root@node1 ~]# mysql -hlocalhost -uroot -pTpam1234Welcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection id is 7Server version: 5.5.56-MariaDB MariaDB ServerCopyright (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)]> exitBye[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 existsDec 24 15:48:09 node1 systemd[1]: mariadb.service: main process exited, code=exited, status=1/FAILUREDec 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 mysqldroot 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-networkingmysql 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.sockroot 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 mysqldroot 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/mysqlDec 24 15:50:54 node1 systemd[1]: Started MariaDB database server.
验证,可以远程登录
[root@node1 ~]# mysql -h192.168.60.101 -uroot -p123456Welcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection id is 2Server version: 5.5.56-MariaDB MariaDB ServerCopyright (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)]> exitBye[root@node1 ~]#
转载地址:http://prvab.baihongyu.com/