源码安装mariadb

参考的施老师的文章https://www.shiluowei.cn/2017/05/09/mariadb-install/

数据库，是任何一个网站架构中需要重点关注的地方，对数据库的介绍（mariab）先从它的安装开始。

mariadb官方网站

系统

CentOS 7.2

yum安装mariadb

安装过程

安装命令

yum -y install mariadb mariadb-server

启动MariaDB

1
2

systemctl start mariadb
systemctl enable mariadb #设置开机启动

MariaDB的相关简单配置

执行如下命令

mysql_secure_installation

1
2
3
4
5
6
7
8
9
10

首先是设置密码，会提示先输入密码
Enter current password for root (enter for none):<–初次运行直接回车设置密码
Set root password? [Y/n] <– 是否设置root用户密码，输入y并回车或直接回车
New password: <– 设置root用户的密码
Re-enter new password: <– 再输入一次你设置的密码
其他配置
Remove anonymous users? [Y/n] <– 是否删除匿名用户，回车
Disallow root login remotely? [Y/n] <–是否禁止root远程登录,回车,
Remove test database and access to it? [Y/n] <– 是否删除test数据库，回车
Reload privilege tables now? [Y/n] <– 是否重新加载权限表，回车

测试登录

mysql -uroot -p
等待输入密码
完成。

配置字符集

mariadb继承mysql，默认系统的字符集是拉丁，为解决将来前端和数据库之间的字符集兼容问题，我们这里需要将字符集设置为utf8.

默认字符集

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

MariaDB [(none)]> show variables like "%character%";
+--------------------------+----------------------------+
| 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.00 sec)
MariaDB [(none)]> show variables like "%collation%";
+----------------------+-------------------+
| 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主配置文件：/etc/my.cnf

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

vim /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
vim /etc/my.cnf.d/client.cnf
在[client]中添加
default-character-set=utf8
vim /etc/my.cnf.d/mysql-clients.cnf
在[mysql]中添加
default-character-set=utf8

全部配置完成，重启mariadb

systemctl restart mariadb

验证字符集

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

mysql> 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)
字符集配置完成。

配置用户权限

添加用户，设置权限

创建用户

1
2
3
4

mysql>grant all privileges on *.* to username@localhost identified by 'password' with grant option;
mysql>grant all privileges on *.* to username@'%' identified by 'password' with grant option;
'password' 为用户自定义密码

权限最小化思想

只授予部分权限，将all privileges或者all改为select,insert,update,delete,create,drop,index,alter,grant,references,reload,shutdown,process,file其中一部分。

二进制包进行免编译安装mariadb

安装过程

安装依赖包

yum install libaio* -y

添加mysql用户和创建数据目录

1
2
3
4
5
6
7
8

groupadd -r mysql
useradd -s /sbin/nologin -g mysql -r mysql
mkdir -pv /mydata/sock
mkdir -pv /mydata/log
mkdir -pv /mydata/bin_log
mkdir -pv /mydata/pid
mkdir -pv /mydata/data
chown -R mysql:mysql /mydata

解压并初始化mysql

1
2
3
4
5
6
7
8

tar xfz mariadb-10.1.19-linux-x86_64.tar.gz -C /usr/local/
cd /usr/local/
ln -sv mariadb-10.1.19-linux-x86_64 mysql
cd mysql/
chown -R root:mysql .
scripts/mysql_install_db --user=mysql --datadir=/mydata/data/
两个OK表示初始化成功

提供配置文件

1
2
3
4

上传 basemy.cnf 到 /etc/ 并重命名 my.cnf
提供脚本文件
cd /usr/local/mysql/
cp support-files/mysql.server /etc/init.d/mysqld

设置启动方式

1
2
3

chkconfig --add mysqld
chkconfig mysqld on
systemctl start mysqld

输出mysql的头文件至系统头文件路径

ln -sv /usr/local/mysql/include  /usr/include/mysql

输出mysql的库文件给系统库查找路径

echo '/usr/local/mysql/lib' > /etc/ld.so.conf.d/mysql.confldconfig

配置mysql命令路径

1
2
3
4
5
6
7
8
9
10
11
12
13

vim .bash_profile
MYSQL_HOME='/usr/local/mysql'
PATH=${MYSQL_HOME}/bin:$PATH:$HOME/bin
source .bash_profile
或者
vim /etc/profile
#mysql
MYSQL_HOME="/usr/local/mysql"
export PATH=$PATH:$MYSQL_HOME/bin
------------------------------
source /etc/profile

添加root密码

mysqladmin -u root password

修剪mysql 用户及test数据库

1
2
3
4

delete from mysql.user where user="";
delete from mysql.user where host="::1";
delete from mysql.user where host="mysql1";
#这里mysql的主机名为mysql1

创建一个基本的数据库和表

1
2
3
4
5

CREATE DATABASE newCisco DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
show create database newCisco\G 这里不需要再加;
use newCisco;
create table user(userid int(4) primary key not null auto_increment,username varchar(16) not null,userpassword varchar(32) not null);
Query OK, 0 rows affected (0.06 sec)

查看一下字符集是否为utf-8

1
2

show variables like '%character%';
show variables like "%collation%";

和杨哥的文章 http://blog.csdn.net/yanggd1987/article/details/51671954 注意mariadb应该用x86_64的包。注意替换路径。不知为何，用mysql包安装后服务一直起不来，提示找不到pid。