MariaDB 資料庫管理系統是 MySQL 的一個分支,主要由開源社群在維護,采用 GPL 授權許可。開發這個分支的原因之一是:甲骨文公司收購了 MySQL 後,有将 MySQL 閉源的潛在風險,是以社群采用分支的方式來避開這個風險。MariaDB完全相容mysql,使用方法也是一樣的
有的centos7已經預設安裝了Mariadb,可以檢視自己的有沒有安裝,沒有安裝的再進行安裝,已經安裝了可以不用安裝也可以解除安裝了重裝。解除安裝指令 yum remove mariadb-server
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsIiZpdmLrN2bsJEZlR3YhJHdu92Qvw1cy9GdhNWak5WSn5WaulGb0V3TvwVbvNmLzd2bsJmbj5ycldWYtl2Lc9CX6MHc0RHaiojIsJye.gif)
1 [root@mini ~]# cat /etc/redhat-release
2 CentOS Linux release 7.5.1804 (Core)
3 [root@mini ~]#
我的系統環境
1、安裝MariaDB
通過yum安裝就行了。簡單快捷,安裝mariadb-server,預設依賴安裝mariadb,一個是服務端、一個是用戶端。
[root@mini ~]# yum install mariadb-server
2、配置MariaDB
1)安裝完成後首先要把MariaDB服務開啟,并設定為開機啟動
[root@mini ~]# systemctl start mariadb # 開啟服務
[root@mini ~]# systemctl enable mariadb # 設定為開機自啟動服務
2)首次安裝需要進行資料庫的配置,指令都和mysql的一樣
[root@mini ~]# mysql_secure_installation
3)配置時出現的各個選項
Enter current password for root (enter for none): # 輸入資料庫超級管理者root的密碼(注意不是系統root的密碼),第一次進入還沒有設定密碼則直接回車
Set root password? [Y/n] # 設定密碼,y
New password: # 新密碼
Re-enter new password: # 再次輸入密碼
Remove anonymous users? [Y/n] # 移除匿名使用者, y
Disallow root login remotely? [Y/n] # 拒絕root遠端登入,n,不管y/n,都會拒絕root遠端登入
Remove test database and access to it? [Y/n] # 删除test資料庫,y:删除。n:不删除,資料庫中會有一個test資料庫,一般不需要
Reload privilege tables now? [Y/n] # 重新加載權限表,y。或者重新開機服務也許
4)測試是否能夠登入成功,出現 MariaDB [(none)]> 就表示已經能夠正常登入使用MariaDB資料庫了
[root@mini ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8
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)]>
3、設定MariaDB字元集為utf-8
1)/etc/my.cnf 檔案
在 [mysqld] 标簽下添加
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
2)/etc/my.cnf.d/client.cnf 檔案
在 [client] 标簽下添加
default-character-set=utf8
3)/etc/my.cnf.d/mysql-clients.cnf 檔案
在 [mysql] 标簽下添加
default-character-set=utf8
4)重新開機服務
[root@mini ~]# systemctl restart mariadb
5)進入mariadb檢視字元集
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsIiZpdmLrN2bsJEZlR3YhJHdu92Qvw1cy9GdhNWak5WSn5WaulGb0V3TvwVbvNmLzd2bsJmbj5ycldWYtl2Lc9CX6MHc0RHaiojIsJye.gif)
MariaDB [(none)]> show variables like "%character%";show variables like "%collation%";
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | utf8_general_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)
MariaDB [(none)]>
未配置字元集前
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsIiZpdmLrN2bsJEZlR3YhJHdu92Qvw1cy9GdhNWak5WSn5WaulGb0V3TvwVbvNmLzd2bsJmbj5ycldWYtl2Lc9CX6MHc0RHaiojIsJye.gif)
MariaDB [(none)]> show variables like "%character%";show variables like "%collation%";
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
+----------------------+-----------------+
| Variable_name | Value |
+----------------------+-----------------+
| collation_connection | utf8_unicode_ci |
| collation_database | utf8_unicode_ci |
| collation_server | utf8_unicode_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)
MariaDB [(none)]>
配置字元集後
4、遠端連結mariadb資料庫
mariadb預設是拒絕 root 遠端登入的。這裡用的是 navicat 軟體連接配接資料庫
1)關閉防火牆
① 關閉防火牆 systemctl stop firewalld
[root@mini ~]# systemctl stop firewalld
② 在不關閉防火牆的情況下,允許某端口的外來連結。步驟如下,開啟3306端口,重新開機防火牆
[root@mini ~]# firewall-cmd --query-port=3306/tcp # 檢視3306端口是否開啟
no
[root@mini ~]# firewall-cmd --zone=public --add-port=3306/tcp --permanent # 開啟3306端口
success
[root@mini ~]# firewall-cmd --reload # 重新開機防火牆
success
[root@mini ~]# firewall-cmd --query-port=3306/tcp # 檢視3306端口是否開啟
yes
2)先檢視mysql資料庫中的user表
[root@mini ~]# mysql -u root -p # 先通過本地連結進入資料庫
MariaDB [(none)]> use mysql;
MariaDB [mysql]> select host, user from user;
+-----------+------+
| host | user |
+-----------+------+
| 127.0.0.1 | root |
| ::1 | root |
| mini | root |
+-----------+------+
3 rows in set (0.00 sec)
3)将與主機名相等的字段改為 "%" ,我的主機名為mini,
MariaDB [mysql]> update user set host='%' where host='mini';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [mysql]> select host, user from user;
+-----------+------+
| host | user |
+-----------+------+
| % | root |
| 127.0.0.1 | root |
| localhost | root |
+-----------+------+
3 rows in set (0.00 sec)
4)重新整理權限表,或重新開機mariadb服務,一下二選一即可
MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
[root@mini ~]# systemctl restart mariadb
注意:重新整理權限表是在資料庫中,重新開機服務是在外部指令行中
6)重新遠端連結mariadb