CentOS6 mariadb二进制安装

MariaDB 是一个采用Maria 存储引擎的MySQL分支版本，是由原来 MySQL 的作者Michael Widenius创办的公司所开发的免费开源的数据库服务器。MariaDB是目前最受关注的MySQL数据库衍生版，也被视为开源数据库MySQL的替代品。除了使用Linux 各发行版供应商的程序包安装，也可以选择基于二进制格式的程序包进行安装。具体安装步骤如下：

1.下载二进制源码

#

官网下载地址http://downloads.mariadb.org

2.创建系统用户

#

[root@Centos6 ~]# groupadd -r -g 36 mysql
[root@Centos6 ~]# useradd -r -u 36 -g 36 mysql

3.准备二进制程序

#

解压缩包到/usr/local

[root@Centos6 ~]# tar xf /root/mariadb-5.5.57-linux-x86_64.tar.gz -C /usr/local/

创建软链接并修改目录属组为mysql

[root@Centos6 local]#cd /usr/local
[root@Centos6 local]#ln -sv  mariadb-5.5.57-linux-x86_64/ mysql
	`mysql' -> `mariadb-5.5.57-linux-x86_64/'
[root@Centos6 local]# chown -R root:mysql /usr/local/mysql/ 

4.准备mysql数据存储目录

#

建议把mysql数据存在基于逻辑卷的单独分区

[root@Centos6 local]#lvcreate -L 20G -n mydata vg_centos6
[root@Centos6 local]#mkfs.ext4 /dev/vg_centos6/mydata 

设置开机自动挂载逻辑卷mydata到/mydata

[root@Centos6 local]mkdir /mydata
[root@Centos6 local]vim /etc/fstab
	/dev/vg_centos6/mydata /mydata                  ext4    defaults        0 0
[root@Centos6 local]mount -a

创建存储目录/mydata/data并修改属主属组为mysql

[root@Centos6 local]chown mysql:mysql /mydata/data

5.创建数据库文件

#

安装包提供了自动生成数据库的脚本/usr/local/mysql/scripts/mysql_install_db,在/usr/local/mysql目录下运行该脚本

[root@Centos6 mysql]# ./scripts/mysql_install_db --user=mysql --datadir=/mydata/data   
[root@Centos6 mysql]# ./scripts/mysql_install_db --help              				  <-- 可以查看脚本帮助
[root@Centos6 mysql]# ls /mydata/data
aria_log.00000001  aria_log_control  mysql  performance_schema  test     

6.准备mysqld程序配置文件

#

​ 配置文件查找次序: /etc/my.cnf – > /etc/mysql/my.cnf – > -- default-extrafile=/PATH/TO/CONF_FILE(第5步中脚本选项指定的配置文件) – > ~/. my.cnf

安装包提供了几种不同配置的模板配置文件,位于目录/usr/local/mysql/suport-files/;可以根据数据库的大小及服务器配置等选择合适的模板进行修改

[root@Centos6 mysql]# mkdir /etc/mysql
[root@Centos6 mysql]# cp support-files/my-large.cnf /etc/mysql/my.cnf

[root@Centos6 mysql]# vim /etc/mysql/my.cnf
datadir = /mydata/data					   <--指定数据库文件存储目录
innodb_file_per_table = on				   <--数据库中各表格以单个文件存储
skip_name_resolve = on					   <--禁止主机名解析

7.准备日志文件

#

Centos6–>/var/log/mysqld.log

注意在Centos7里自动生成，不用手动创建–>/var/log/mariadb/mariadb.log

[root@Centos6 mysql]# touch /var/log/mysqld.log
[root@Centos6 mysql]# chown mysql:mysql /var/log/mysqld.log

8.准备服务脚本,并启动服务

#

[root@Centos6 mysql]# cp support-files/mysql.server /etc/rc.d/init.d/mysqld
[root@Centos6 mysql]# chkconfig --add mysqld
[root@Centos6 mysql]# chkconfig --list mysqld
[root@Centos6 ~]# vi /etc/profile.d/my.sh  						<--创建系统配置文件,将可执行程序mysql路径加入PATH变量
export PATH=/usr/local/mysql/bin/:$PATH
[root@Centos6 mysql]#service mysqld start

9.运行mysql命令–>交互式客户端程序

#

[root@Centos6 ~]#mysql                     		<-- 默认空密码登录
MariaDB [mysql]> use mysql
Database changed
MariaDB [mysql]> select user,host,password from user;
+------+-----------+----------+
| user | host      | password |
+------+-----------+----------+
| root | localhost |          |
| root | centos6.9 |          |
| root | 127.0.0.1 |          |
| root | ::1       |          |
|      | localhost |          |    			 <-- 表示允许匿名登录
|      | centos6.9 |          |
+------+-----------+----------+
6 rows in set (0.01 sec)

mysql用户账号由两部分组成：‘USERNAME’@‘HOST’

HOST用于限制此用户可通过哪些远程主机连接mysql服务(限制客户端)

HOST支持CIDR IP表示法；也支持使用通配符：

​ % 匹配任意长度的任意字符 eg: 192.168.%.%

​ _ 匹配任意单个字符

10.安全初始化

#

从文章第9步可以看出，数据库默认是允许匿名登录及无密码登录，这是非常不安全的，因此，我们还需要进行安全初始化

[root@Centos6 ~]# /usr/local/mysql/bin/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:					<-- 设置root密码
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] y        <-- 禁止root远程登录
 ... Success!

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] 
 - 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!

[root@Centos6 ~]# mysql					<-- 无密码登录已经禁止
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@Centos6 ~]# mysql -uroot -p			<-- 正确登入
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 17
Server version: 5.5.57-MariaDB MariaDB Server

Copyright (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)]> use mysql
Database changed
MariaDB [mysql]> select user,host,password from user;
+------+-----------+-------------------------------------------+
| user | host      | password                                  |
+------+-----------+-------------------------------------------+
| root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root | 127.0.0.1 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root | ::1       | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)