MariaDB數據庫管理系統是MySQL的一個分支，MariaDB跟MySQL在絕大多數方面是兼容的，對于開發者來說，幾乎感覺不到任何不同。目前MariaDB是發展最快的MySQL分支版本，新版本發布速度已經超過了Oracle官方的MySQL版本。那么在Centos等Linux系統中除了安裝Mysql，還可以安裝MariaDB數據庫了。
安裝過程
1）在/etc/yum.repos.d/目錄創建MariaDB.repo文件，內容如下：
# MariaDB 5.5 CentOS repository list - created 2014-03-04 11:20 UTC
# http://mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/5.5/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
2）在Shell環境下執行命令
# yum install MariaDB-server MariaDB-client
Loaded plugins: fastestmirror, security
base | 3.7 kB 00:00
base/primary_db | 4.4 MB 01:11
extras | 3.4 kB 00:00
extras/primary_db | 19 kB 00:00
mariadb | 1.9 kB 00:00
mariadb/primary_db | 15 kB 00:00
updates | 3.4 kB 00:00
updates/primary_db | 2.1 MB 00:22
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package MariaDB-client.x86_64 0:5.5.36-1.el6 will be obsoleting
--> Processing Dependency: MariaDB-common for package: MariaDB-client-5.5.36-1.el6.x86_64
---> Package MariaDB-server.x86_64 0:5.5.36-1.el6 will be obsoleting
---> Package mysql.x86_64 0:5.1.71-1.el6 will be obsoleted
---> Package mysql-server.x86_64 0:5.1.71-1.el6 will be obsoleted
--> Running transaction check
---> Package MariaDB-common.x86_64 0:5.5.36-1.el6 will be installed
--> Processing Dependency: MariaDB-compat for package: MariaDB-common-5.5.36-1.el6.x86_64
--> Running transaction check
---> Package MariaDB-compat.x86_64 0:5.5.36-1.el6 will be obsoleting
---> Package mysql-libs.x86_64 0:5.1.71-1.el6 will be obsoleted
--> Finished Dependency Resolution
Dependencies Resolved
==================================================================================================================
Package Arch Version Repository Size
==================================================================================================================
Installing:
MariaDB-client x86_64 5.5.36-1.el6 mariadb 10 M
replacing mysql.x86_64 5.1.71-1.el6
MariaDB-compat x86_64 5.5.36-1.el6 mariadb 2.7 M
replacing mysql-libs.x86_64 5.1.71-1.el6
MariaDB-server x86_64 5.5.36-1.el6 mariadb 42 M
replacing mysql-server.x86_64 5.1.71-1.el6
Installing for dependencies:
MariaDB-common x86_64 5.5.36-1.el6 mariadb 23 k
Transaction Summary
==================================================================================================================
Install 4 Package(s)
Total download size: 55 M
Is this ok [y/N]: y
Downloading Packages:
(1/4): MariaDB-5.5.36-centos6-x86_64-client.rpm | 10 MB 04:45
(2/4): MariaDB-5.5.36-centos6-x86_64-common.rpm | 23 kB 00:00
(3/4): MariaDB-5.5.36-centos6-x86_64-compat.rpm | 2.7 MB 01:02
(4/4): MariaDB-5.5.36-centos6-x86_64-server.rpm | 42 MB 17:44
------------------------------------------------------------------------------------------------------------------
Total 40 kB/s | 55 MB 23:34
warning: rpmts_HdrFromFdno: Header V4 DSA/SHA1 Signature, key ID 1bb943db: NOKEY
Retrieving key from https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
Importing GPG key 0x1BB943DB:
Userid: "Daniel Bartholomew (Monty Program signing key) <[email protected]>"
From : https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
Is this ok [y/N]: y
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Installing : MariaDB-compat-5.5.36-1.el6.x86_64 1/7
Installing : MariaDB-common-5.5.36-1.el6.x86_64 2/7
Error in PREIN scriptlet in rpm package MariaDB-server-5.5.36-1.el6.x86_64
******************************************************************
A MySQL or MariaDB server package (mysql-server-5.1.71-1.el6.x86_64) is installed.
The current MariaDB server package is provided by a different
vendor (CentOS) than Monty Program AB. Some files may be installed
to different locations, including log files and the service
startup script in /etc/init.d/.
Upgrading directly from MySQL 5.1 to MariaDB 5.5 may not
be safe in all cases. A manual dump and restore using mysqldump is
recommended. It is important to review the MariaDB manual's Upgrading
section for version-specific incompatibilities.
A manual upgrade is required.
- Ensure that you have a complete, working backup of your data and my.cnf
files
- Shut down the MySQL server cleanly
- Remove the existing MySQL packages. Usually this command will
list the packages you should remove:
rpm -qa | grep -i '^mysql-'
You may choose to use 'rpm --nodeps -ev <package-name>' to remove
the package which contains the mysqlclient shared library. The
library will be reinstalled by the MariaDB-shared package.
- Install the new MariaDB packages supplied by Monty Program AB
- Ensure that the MariaDB server is started
- Run the 'mysql_upgrade' program
This is a brief description of the upgrade process. Important details
can be found in the MariaDB manual, in the Upgrading section.
******************************************************************
error: %pre(MariaDB-server-5.5.36-1.el6.x86_64) scriptlet failed, exit status 1
error: install: %pre scriptlet failed (2), skipping MariaDB-server-5.5.36-1.el6
Installing : MariaDB-client-5.5.36-1.el6.x86_64 4/7
Erasing : mysql-5.1.71-1.el6.x86_64 5/7
Erasing : mysql-libs-5.1.71-1.el6.x86_64 6/7
Verifying : MariaDB-common-5.5.36-1.el6.x86_64 1/7
Verifying : MariaDB-compat-5.5.36-1.el6.x86_64 2/7
Verifying : MariaDB-client-5.5.36-1.el6.x86_64 3/7
Verifying : mysql-libs-5.1.71-1.el6.x86_64 4/7
Verifying : MariaDB-server-5.5.36-1.el6.x86_64 5/7
mysql-server-5.1.71-1.el6.x86_64 was supposed to be removed but is not!
Verifying : mysql-server-5.1.71-1.el6.x86_64 6/7
Verifying : mysql-5.1.71-1.el6.x86_64 7/7
Installed:
MariaDB-client.x86_64 0:5.5.36-1.el6 MariaDB-compat.x86_64 0:5.5.36-1.el6
Dependency Installed:
MariaDB-common.x86_64 0:5.5.36-1.el6
Replaced:
mysql.x86_64 0:5.1.71-1.el6 mysql-libs.x86_64 0:5.1.71-1.el6
Failed:
MariaDB-server.x86_64 0:5.5.36-1.el6 mysql-server.x86_64 0:5.1.71-1.el6
Complete!
3、啟動MariaDB
# /etc/init.d/mysqld start
Initializing MySQL database: WARNING: The host 'Cloud-Q2' could not be looked up with resolveip.
This probably means that your libc libraries are not 100 % compatible
with this binary MySQL version. The MySQL daemon, mysqld, should work
normally with the exception that host name resolving will not work.
This means that you should use IP addresses instead of hostnames
when specifying MySQL privileges !
Installing MySQL system tables...
OK
Filling help tables...
OK
To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h Cloud-Q2 password 'new-password'
Alternatively you can run:
/usr/bin/mysql_secure_installation
which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.
See the manual for more instructions.
You can start the MySQL daemon with:
cd /usr ; /usr/bin/mysqld_safe &
You can test the MySQL daemon with mysql-test-run.pl
cd /usr/mysql-test ; perl mysql-test-run.pl
Please report any problems with the /usr/bin/mysqlbug script!
[ OK ]
Starting mysqld: [ OK ]
4、為MariaDB設置密碼
# /usr/bin/mysqladmin -u root password 'adminadmin'
登陸MariaDB
MySQL [(none)]> use mysql;
MySQL [mysql]> update user set host='%' where user='root' and host='127.0.0.1';
MySQL [mysql]> grant all privileges on *.* to 'root'@'%' identified by 'adminadmin' with grant option;
MySQL [mysql]> flush privileges;
5、讓防火墻通過3306端口
# vi /etc/sysconfig/iptables
添加如下內容：
-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT
保存并退出編輯
重啟防火墻服務
# service iptables restart
Centos 使用YUM安裝MariaDB
遠程連接檢查，一切OK！centos安裝mariadb
說明: 首先必須能鏈接外網. 如果不能直接訪問,那也可以設置代理,請參考: 在內網機器上設置yum代理
使用 yum 的權限要求是 root 用戶,如果你不是,那么可以需要 在 shell命令之前加上 sudo, 或者 su root 切換到 super 管理員進行操作. 并可能需要輸入密碼.
1. 添加 yum 數據源;
建議命名為 MariaDB.repo 類似的名字：
cd /etc/yum.repos.d/
vim /etc/yum.repos.d/MariaDB.repo
然后,寫入文件內容:
# /etc/yum.repos.d/MariaDB.repo
# MariaDB 5.5 CentOS repository list - created 2013-12-23 10:27 UTC
# http://mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/5.5/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
該文件的內容是參考官網,并從官網上生成的，設置安裝源倉庫的 具體的地址為: https://downloads.mariadb.org/mariadb/repositories/
選擇好操作系統版本之后既可以查看，其他操作系統的安裝源也可以在此處查看并設置。
如果服務器不支持https協議，或者gpgkey 保錯，確保沒問題的話，可以將 gpgcheck=1 修改為 gpgcheck=0,則不進行校驗.
2. 安裝數據庫
# yum remove MariaDB-server MariaDB-client
yum -y install MariaDB-server MariaDB-client
如果要刪除舊的數據庫可以使用remove, 參數 -y 是確認，不用提示。此處，安裝的是服務器和客戶端，一般來說安裝這兩個就可以了。
3. 啟動數據庫
如果不用進行其他的操作，則現在就可以直接啟動數據庫，并進行測試了。
# 查看mysql狀態;關閉數據庫
# service mysql status
# service mysql stop
# 啟動數據庫
service mysql start
4. 修改root密碼
# 修改root密碼
mysqladmin -u root password 'root'
因為安裝好以后的root密碼是空,所以需要設置; 如果是測試服務器,那么你可以直接使用root,不重要的密碼很多時候可以設置為和用戶名一致，以免忘記了又想不起來。
如果是重要的服務器，請使用復雜密碼，例如郵箱，各種自由組合的規則的字符等。
5. 登錄數據庫
mysql -u root -p
如果是本機,那可以直接使用上面的命令登錄，當然，需要輸入密碼. 如果是其他機器，那么可能需要如下的形式:
mysql -h 127.0.0.1 -P 3306 -u root -p
6. 簡單SQL測試
>
-- 查看MySQL的狀態
status;
-- 顯示支持的引擎
show engines;
-- 顯示所有數據庫
show databases;
-- 切換數據庫上下文,即設置當前會話的默認數據庫
use test;
-- 顯示本數據庫所有的表
show tables;
-- 創建一個表
CREATE TABLE t_test (
id int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
userId char(36),
lastLoginTime timestamp,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 插入測試數據
insert into t_test(userId)
values
('admin')
,('haha')
;
-- 簡單查詢
select * from t_test;
select id,userId from t_test where userId='admin' ;
7. 修改數據存放目錄
mysql, MariaDB 的默認數據存放在 /var/lib/mysql/ 目錄下,如果不想放到此處,或者是想要程序和數據分離，或者是磁盤原因,需要切換到其他路徑,則可以通過修改 datadir系統變量來達成目的.
# 停止數據庫
service mysql stop
# 創建目錄,假設沒有的話
mkdir /usr/local/ieternal/mysql_data
# 拷貝默認數據庫到新的位置
# -a 命令是將文件屬性一起拷貝,否則各種問題
cp -a /var/lib/mysql /usr/local/ieternal/mysql_data
# 備份原來的數據
cp -a /etc/my.cnf /etc/my.cnf_original
# 其實查看 /etc/my.cnf 文件可以發現
# MariaDB 的此文件之中只有一個包含語句
# 所以需要修改的配置文件為 /etc/my.cnf.d/server.cnf
cp /etc/my.cnf.d/server.cnf /etc/my.cnf.d/server.cnf_original
vim /etc/my.cnf.d/server.cnf
然后 按 i 進入編輯模式,可以插入相關內容.使用鍵盤的上下左右鍵可以移動光標, 編輯完成以后，按 ESC 退出編輯模式(進入命令模式), 然后輸入命令:wq 保存并退出
# 在文件的 mysqld 節下添加內容
[mysqld]
datadir=/usr/local/ieternal/mysql_data/mysql
socket=/var/lib/mysql/mysql.sock
#default-character-set=utf8
character_set_server=utf8
slow_query_log=on
slow_query_log_file=/usr/local/ieternal/mysql_data/slow_query_log.log
long_query_time=2
其中,也只有 datadir 和 socket 比較重要; 而 default-character-set 是 mysql 自己認識的,而 mariadb5.5 就不認識,相當于變成了 character_set_server
7.1 創建慢查詢日志文件
既然上面指定了慢查詢日志文件，我后來看了下MariaDB的err日志，發現MariaDB不會自己創建該文件，所以我們需要自己創建,并修改相應的文件權限(比如 mysql 采用 mysql用戶，可能我們使用 root用戶創建的文件,此時要求慢查詢日志文件對mysql用戶可讀可寫就行。)
touch /usr/local/ieternal/mysql_data/slow_query_log.log
chmod 666 /usr/local/ieternal/mysql_data/slow_query_log.log
然后重新啟動MySQL.
service mysql start
新聞熱點
疑難解答
