Centos系统中yum和编译 安装 MariaDB 数据库

准备yum一个lamp环境，发现yum版本的mysql居然依然是5.1.x的版本，因此准备更换为MariaDB。

配置源：

[root@localhost ~]# cat /etc/yum.repos.d/MariaDB.repo
# MariaDB 10.0 CentOS repository list - created 2013-08-23 13:08 UTC
# http://mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.0/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

检查源：

[root@localhost ~]# yum search mariadb
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * base: ftp.sjtu.edu.cn
 * extras: mirrors.aliyun.com
 * updates: mirrors.aliyun.com
======================================================= N/S Matched: mariadb ========================================================
MariaDB-Galera-server.x86_64 : MariaDB: a very fast and robust SQL database server
MariaDB-Galera-test.x86_64 : MariaDB: a very fast and robust SQL database server
MariaDB-cassandra-engine.x86_64 : MariaDB: a very fast and robust SQL database server
MariaDB-client.x86_64 : MariaDB: a very fast and robust SQL database server
MariaDB-common.x86_64 : MariaDB: a very fast and robust SQL database server
MariaDB-compat.x86_64 : MariaDB: a very fast and robust SQL database server
MariaDB-connect-engine.x86_64 : MariaDB: a very fast and robust SQL database server
MariaDB-devel.x86_64 : MariaDB: a very fast and robust SQL database server
MariaDB-oqgraph-engine.x86_64 : MariaDB: a very fast and robust SQL database server
MariaDB-server.x86_64 : MariaDB: a very fast and robust SQL database server
MariaDB-shared.x86_64 : MariaDB: a very fast and robust SQL database server
MariaDB-test.x86_64 : MariaDB: a very fast and robust SQL database server
 
  Name and summary matches only, use "search all" for everything.

安装database：

[root@localhost ~]# yum install MariaDB-server MariaDB-client
[root@localhost ~]# yum install MariaDB-*

运行database：

[root@localhost ~]# /etc/init.d/mysql start
Starting MySQL. SUCCESS!
[root@localhost ~]# mysql -V
mysql  Ver 15.1 Distrib 10.0.17-MariaDB, for Linux (x86_64) using readline 5.1

编译版：

[root@Test tools]# wget http://mirrors.opencas.cn/mariadb//mariadb-10.1.13/source/mariadb-10.1.13.tar.gz
[root@Test tools]# tar -zxf mariadb-10.1.13.tar.gz
[root@Test tools]# cd mariadb-10.1.13
[root@Test mariadb-10.1.13]# cmake . -DCMAKE_INSTALL_PREFIX=/byrd/service/mariadb-10.1.13 -DMYSQL_DATADIR=/byrd/service/mariadb-10.1.13/data -DMYSQL_UNIX_ADDR=/byrd/service/mariadb-10.1.13/tmp/mysql.sock -DMYSQL_USER=mysql -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DENABLED_LOCAL_INFILE=ON -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_FEDERATED_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITHOUT_EXAMPLE_STORAGE_ENGINE=1
 
 
[root@Test mariadb-10.1.13]# cmake . -DCMAKE_INSTALL_PREFIX=/byrd/service/mysql-5.5.49 -DMYSQL_UNIX_ADDR=/byrd/service/mysql-5.5.49/tmp/mysql.sock -DMYSQL_DATADIR=/byrd/service/mysql-5.5.49/data -DWITH_DEBUG=0 -DEXTRA_CHARSETS=all -DWITH_READLINE=1 -DWITH_ZLIB=system -DWITH_SSL=system -DWITH_EMBEDDED_SERVER=1 -DENABLED_LOCAL_INFILE=1 -DMYSQL_USER=mysql -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci  -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_FEDERATED_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 -DWITH_ARCHIVE_STORAGE_ENGINE=1
 
[root@Test mariadb-10.1.13]# make &&　make install
[root@Test /]# mkdir /data/{3306,3307}/data -p
[root@Test /]# ln -s /byrd/service/mariadb-10.1.13 /usr/local/mariadb
[root@Test /]# chown -R mysql.mysql /data/ 
[root@Test /]# /usr/local/mariadb/scripts/mysql_install_db --basedir=/usr/local/mariadb --datadir=/data/3307/data --user=mysql 
[root@Test /]# cat /data/3307/my.cnf
[client]
port            = 3307
#socket          = /data/3307/mysql.sock
[mysql]
no-auto-rehash
[mysqld]
user    = mysql
port    = 3307
socket  = /data/3307/mysql.sock
basedir = /usr/local/mariadb
datadir = /data/3307/data
[root@Test /]# /usr/local/mariadb/bin/mysqld_safe --defaults-file=/data/3307/my.cnf &
[root@Test /]# /usr/local/mariadb/bin/mysql -uroot -p -S /data/3307/mysql.sock
MariaDB [(none)]> create database byrd3307 default character set utf8;
MariaDB [(none)]> show databases like '%byrd%';
+-------------------+
| Database (%byrd%) |
+-------------------+
| byrd3307          |
+-------------------+
 
[root@Test /]# mysql -usystem -p'admin123'
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| byrd               |
| dns                |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
5 rows in set (0.07 sec)

删除默认管理员：（2016年4月19日添加）

MariaDB [(none)]> select user,host from mysql.user;
+------+-----------+
| user | host      |
+------+-----------+
| root | 127.0.0.1 |
| root | ::1       |
|      | localhost |
| root | localhost |
|      | test      |
| root | test      |
+------+-----------+
 
MariaDB [(none)]> delete from mysql.user;
 
MariaDB [(none)]> grant all privileges on *.* to system@'localhost' identified by 'admin123' with grant option;
 
MariaDB [(none)]> show grants for system@'localhost'G
*************************** 1. row ***************************
Grants for system@localhost: GRANT ALL PRIVILEGES ON *.* TO 'system'@'localhost' IDENTIFIED BY PASSWORD '*01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C' WITH GRANT OPTION
 
MariaDB [(none)]> select user,host from mysql.user;                                                                               
+--------+-----------+
| user   | host      |
+--------+-----------+
| system | localhost |
+--------+-----------+
 
MariaDB [(none)]> SHOW CHARACTER SET like '%utf8%';
+---------+---------------+--------------------+--------+
| Charset | Description   | Default collation  | Maxlen |
+---------+---------------+--------------------+--------+
| utf8    | UTF-8 Unicode | utf8_general_ci    |      3 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci |      4 |
+---------+---------------+--------------------+--------+
 
MariaDB [(none)]> help character;
MariaDB [(none)]> help create database;
MariaDB [(none)]> create database byrd default character set utf8 collate utf8_general_ci;
 
MariaDB [(none)]> show create database byrd;
+----------+---------------------------------------------------------------+
| Database | Create Database                                               |
+----------+---------------------------------------------------------------+
| byrd     | CREATE DATABASE `byrd` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+---------------------------------------------------------------+
MariaDB [(none)]> quit
Bye
[root@Test ~]# mysql
MariaDB [(none)]> flush privileges;
 
[root@Test ~]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@Test ~]# mysql -usystem -p'admin123'
 
MariaDB [(none)]> prompt mysql>          
PROMPT set to 'mysql> '
mysql> drop database test;
mysql> insert into test(id,name) VALUES(1,赵云);
ERROR 1054 (42S22): Unknown column '赵云' in 'field list'
mysql> insert into test(id,name) VALUES(1,'赵云');
 
mysql> insert into test(id,name) VALUES(2,'zhangfei');
 
mysql> insert into test(id,name) VALUES(3,'liubei'),(4,'guanyu');
 
mysql> insert into test VALUES(5,'cc'),(6,'caopei');
 
mysql> system cat 7.sql
insert into test(id,name) VALUES(7,'孙策');
mysql> source 7.sql
mysql> system pwd
/root
mysql> update test set name='曹操' where id=5;
 
[root@Test ~]# mysql -usystem -p'admin123' -e "select * from byrd.test;"
+----+----------+
| id | name     |
+----+----------+
|  1 | 赵云     |
|  2 | zhangfei |
|  3 | liubei   |
|  4 | guanyu   |
|  5 | 曹操     |
|  6 | caopei   |
|  7 | 孙策     |
+----+----------+