MariaDB的相关知识
MySQL是一种最常见的关系型数据库，在经典的LAMP架构中代表M。
在被甲骨文公司收购之后，MySQL的作者重新起了一个开源项目，叫MariaDB。
所以在很多新的Linux发行版系统中，可以通过安装MariaDB来使用这个数据库。
安装MariaDB的三种方式：
- 官方提供的二进制包
- 官方提供的yum软件源
- cmake编译安装
系统默认镜像已经提供MariaDB的安装，为什么还需要安装MariaDB？
1.因为系统发行版由于更注重于软件的稳定，提供的MariaDB版本还在5.5。注：2016年
2.由于MariaDB在5.5以后已经有许多性能上的优化提升，生产环境需要更高版本的MariaDB。
3.因为cmake的方式与传统的编译安装并不一样且繁琐，所以一般前两种方法较为常用。
除了MariaDB，其实Tomcat也是这种情况：大家需要新版本，但是编译很麻烦。
官方就为大家提供他们编译并打包好好的二进制程序给大家下载。‘
配置官方yum源，并安装MariaDB
(1)首先我们需要在官方网站寻找并配置一个与系统合适的镜像。
| Name | Release Date | Release Status |
|---|---|---|
| 10.1.19 | 2016-11-07 | Stable |
本文使用的系统版本为CentOS7.1
注意：系统已经关闭了selinux，以及iptables。
因为我们此处要使用yum源安装MariaDB，所以我们选用下面的这种方式。
而且现在MariaDB在已经有清华镜像，所以我们不用担心官方镜像安装特别慢的问题。
File Name | Package Type | OS / CPU | Size | Meta
:---:|:---:|:---:|:---:|
Red Hat, Fedora, and CentOS Packages |RPM Package | RedHat/CentOS/Fedora (x86, x86_64, ppc64, ppc64le)||Signature Instructions
(2)找到合适的镜像之后新建一个文件，如 /etc/yum.repos.d/mariadb.repo
配置它为yum源，内容如下：
# MariaDB 10.1 CentOS repository list
# http://downloads.mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDBbaseurl = https://mirrors.tuna.tsinghua.edu.cn/mariadb/mariadb-10.1.19/yum/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
通过命令查看MariaDB-server的信息，我们可以看到update镜像提供5.5.50的版本。
而我们刚才配置的mariadb镜像则是10.1.19
注意：在开始的时候也说过，MySQL被甲骨文收购了。
因为MariaDB说MySQL有的功能他们都有，但是为了不让人误会，所以跳出了5系列的循环。
也就是MySQL5.6和5.7其实对应了MariaDB的10.0和10.1。并不是已经跨越了5个版本。
[root@bc ~]# yum list MariaDB-server
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
* base: mirrors.163.com
* epel: ftp.cuhk.edu.hk
* extras: mirrors.tuna.tsinghua.edu.cn
* updates: mirrors.163.com
Available Packages
MariaDB-server.x86_64 10.1.19-1.el7.centos mariadb
mariadb-server.x86_64 1:5.5.50-1.el7_2 update
(3开始安装MariaDB吧！
[root@bc ~]# yum install MariaDB-server.x86_64 MariaDB-client.x86_64 -y
安装的这两个组件，MariaDB-server提供数据存储功能，MariaDB-client提供数据库的链接功能。
yum安装软件的好处在于便捷，但是经常由于没有人维护和更新最新版的rpm包，所以有的时候，我们也只能另辟蹊径。
(4)查看已经安装的MariaDB的版本
[root@bc ~]# mysql --version
mysql Ver 15.1 Distrib 10.1.19-MariaDB, for Linux (x86_64) using readline 5.1
已经成功安装10.1.19的MariaDB了。
使用官方编译好的二进制程序安装MariaDB
(1)首先我们依然是在官方网站寻找一个稳定的版本。STABLE
| Name | Release Date | Release Status |
|---|---|---|
| 10.0.28 | 2016-10-28 | Stable |
官方会提示，请不要在生产环境使用beta和alpha版本的数据库。
他们的主要功能一般是用来测试和排错，所以不稳定。
这里我们选择安装一个10.0系列的Stable的数据库。
我们打算在安装之后，连接刚才10.1的数据库并插入语句。
(2)选择二进制格式的安装方式
File Name | Package Type | OS / CPU | Size | Meta
:---:|:---:|:---:|:---:|
mariadb-10.0.28-linux-x86_64.tar.gz|gzipped tar file|Linux x86_64|317.3 MB|MD5 SHA1 Signature Instructions
根据CPU和系统,我们选择安装这个包。
官方编译好的二进制包都是tar.gz格式的，不过注意不要安装到源码包。
(3)解压缩文件并创建软连接。
[root@node2 ~]# tar -xf mariadb.tar.gz -C /usr/local/
[root@node2 ~]# cd /usr/local/
[root@node2 local]# ln -sv mariadb-10.0.28-linux-x86_64/ mysql
‘mysql’ -> ‘mariadb-10.0.28-linux-x86_64/’
为什么要做软连接呢？
一是因为以后我们以后安装其他版本的时候方便一点点。
二是名字够简短，容易辨认。
(4)把mysql的执行文件路径导出到工作路径
[root@node2 mysql]# vim /etc/profile.d/mariadb.sh
export PATH=/usr/local/mysql/bin:$PATH
[root@node2 mysql]# chmod +x !$
[root@node2 ~]# . /etc/profile.d/mariadb.sh
(5)创建用户，修改文件权限，并创建数据存放目录
[root@node2 mysql]# useradd -r mysql
[root@node2 mysql]# id mysql
uid=996(mysql) gid=994(mysql) groups=994(mysql)
[root@node2 mysql]# chown mysql.mysql /usr/local/mysql/* -R
[root@node2 mysql]# mkdir /datadir
[root@node2 mysql]# chown mysql.mysql -R /datadir/
创建用户和数据存放目录是为了一会儿初始化的时候准备。
而且mysql有一个常见问题就是，权限不对就启动不成功，所以要注意。
(6)初始化数据库
[root@node2 ~]# /usr/local/mysql/scripts/mysql_install_db --user=mysql --datadir=/datadir --basedir=/usr/local/mysql
Installing MariaDB/MySQL system tables in '/datadir' ...
161210 8:43:23 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release.
161210 8:43:23 [Note] /usr/local/mysql/bin/mysqld (mysqld 10.0.28-MariaDB) starting as process 3139 ...
161210 8:43:23 [Note] InnoDB: Using mutexes to ref count buffer pool pages
161210 8:43:23 [Note] InnoDB: The InnoDB memory heap is disabled
161210 8:43:23 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
161210 8:43:23 [Note] InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
161210 8:43:23 [Note] InnoDB: Compressed tables use zlib 1.2.3
161210 8:43:23 [Note] InnoDB: Using Linux native AIO
161210 8:43:23 [Note] InnoDB: Using CPU crc32 instructions
161210 8:43:23 [Note] InnoDB: Initializing buffer pool, size = 128.0M
161210 8:43:23 [Note] InnoDB: Completed initialization of buffer pool
161210 8:43:23 [Note] InnoDB: Highest supported file format is Barracuda.
161210 8:43:23 [Note] InnoDB: Log scan progressed past the checkpoint lsn 49463
161210 8:43:23 [Note] InnoDB: Database was not shutdown normally!
161210 8:43:23 [Note] InnoDB: Starting crash recovery.
161210 8:43:23 [Note] InnoDB: Reading tablespace information from the .ibd files...
161210 8:43:23 [Note] InnoDB: Restoring possible half-written data pages
161210 8:43:23 [Note] InnoDB: from the doublewrite buffer...
InnoDB: Doing recovery: scanned up to log sequence number 1606610
161210 8:43:23 [Note] InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
161210 8:43:24 [Note] InnoDB: 128 rollback segment(s) are active.
161210 8:43:24 [Note] InnoDB: Waiting for purge to start
161210 8:43:24 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.32-79.0 started; log sequence number 1606610
161210 8:43:24 [Note] Recovering after a crash using mysql-bin
161210 8:43:24 [Note] Starting crash recovery...
161210 8:43:24 [Note] Crash recovery finished.
161210 8:43:24 [Warning] Failed to load slave replication state from table mysql.gtid_slave_pos: 1146: Table 'mysql.gtid_slave_pos' doesn't exist
161210 8:43:24 [Note] InnoDB: FTS optimize thread exiting.
161210 8:43:24 [Note] InnoDB: Starting shutdown...
161210 8:43:25 [Note] InnoDB: Waiting for page_cleaner to finish flushing of buffer pool
161210 8:43:27 [Note] InnoDB: Shutdown completed; log sequence number 1622831
OK
Filling help tables...
161210 8:43:27 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release.
161210 8:43:27 [Note] /usr/local/mysql/bin/mysqld (mysqld 10.0.28-MariaDB) starting as process 3169 ...
161210 8:43:27 [Note] InnoDB: Using mutexes to ref count buffer pool pages
161210 8:43:27 [Note] InnoDB: The InnoDB memory heap is disabled
161210 8:43:27 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
161210 8:43:27 [Note] InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
161210 8:43:27 [Note] InnoDB: Compressed tables use zlib 1.2.3
161210 8:43:27 [Note] InnoDB: Using Linux native AIO
161210 8:43:27 [Note] InnoDB: Using CPU crc32 instructions
161210 8:43:27 [Note] InnoDB: Initializing buffer pool, size = 128.0M
161210 8:43:27 [Note] InnoDB: Completed initialization of buffer pool
161210 8:43:27 [Note] InnoDB: Highest supported file format is Barracuda.
161210 8:43:27 [Note] InnoDB: 128 rollback segment(s) are active.
161210 8:43:27 [Note] InnoDB: Waiting for purge to start
161210 8:43:27 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.32-79.0 started; log sequence number 1622831
161210 8:43:27 [Note] InnoDB: FTS optimize thread exiting.
161210 8:43:27 [Note] InnoDB: Starting shutdown...
161210 8:43:28 [Note] InnoDB: Waiting for page_cleaner to finish flushing of buffer pool
161210 8:43:30 [Note] InnoDB: Shutdown completed; log sequence number 1622841
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 MariaDB root USER !
To do so, start the server, then issue the following commands:
'/usr/local/mysql/bin/mysqladmin' -u root password 'new-password'
'/usr/local/mysql/bin/mysqladmin' -u root -h node2.bc.com password 'new-password'
Alternatively you can run:
'/usr/local/mysql/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 MariaDB Knowledgebase at http://mariadb.com/kb or the
MySQL manual for more instructions.
You can start the MariaDB daemon with:
cd '/usr/local/mysql' ; /usr/local/mysql/bin/mysqld_safe --datadir='/datadir'
You can test the MariaDB daemon with mysql-test-run.pl
cd '/usr/local/mysql/mysql-test' ; perl mysql-test-run.pl
Please report any problems at http://mariadb.org/jira
The latest information about MariaDB is available at http://mariadb.org/.
You can find additional information about the MySQL part at:
http://dev.mysql.com
Support MariaDB development by buying support/new features from MariaDB
Corporation Ab. You can contact us about this at sales@mariadb.com.
Alternatively consider joining our community based development effort:
http://mariadb.com/kb/en/contributing-to-the-mariadb-project/
其实都是一些输出信息。但是这里仔细看的话，他们有教我们下一步怎么做。
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 MariaDB root USER !
To do so, start the server, then issue the following commands:
'/usr/local/mysql/bin/mysqladmin' -u root password 'new-password'
'/usr/local/mysql/bin/mysqladmin' -u root -h node2.bc.com password 'new-password'
Alternatively you can run:
'/usr/local/mysql/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 MariaDB Knowledgebase at http://mariadb.com/kb or the
MySQL manual for more instructions.
You can start the MariaDB daemon with:
cd '/usr/local/mysql' ; /usr/local/mysql/bin/mysqld_safe --datadir='/datadir'
You can test the MariaDB daemon with mysql-test-run.pl
cd '/usr/local/mysql/mysql-test' ; perl mysql-test-run.pl
(7)设置开机启动
[root@node2 mysql]# cp /usr/local/mysql/support-files/mysql.server /etc/rc.d/init.d/mysqld
[root@node2 mysql]# chkconfig --add mysqld
[root@node2 ~]# chkconfig mysqld on
(8)修改配置文件，加入几个常用参数。
[root@node2 mysql]# cp /usr/local/mysql/support-files/my-large.cnf /etc/my.cnf
[root@node2 ~]# vim /etc/my.cnf
[mysqld]
datadir=/datadir
user=mysql
skip_name_resolve
innodb_file_per_table
(9)启动数据库
[root@node2 ~]# service mysqld start
Starting MySQL.161210 08:44:14 mysqld_safe Logging to '/datadir/node2.bc.com.err'.
SUCCESS!
启动成功！
注意的是，先启动MySQL是无法进行下一步安全设置的。
因为你想想不开服务，怎么改密码和清除示例。
(10)初始化安全设置
[root@node2 ~]# mysql_secure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.
Enter current password for root (enter for none):
OK, successfully used password, moving on...
Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.
Set root password? [Y/n] Y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
... Success!
By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n] Y
... Success!
Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] n
... skipping.
By default, MariaDB comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n] Y
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n] Y
... Success!
Cleaning up...
All done! If you've completed all of the above steps, your MariaDB
installation should now be secure.
Thanks for using MariaDB!
(11)进入数据库！
[root@node2 ~]# mysql -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 10.0.28-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
