创建时间: 2019/03/14 17:50
内容: linux安装mariadb
作者: yangyidong
linux安装mariadb
[TOC]
##环境
> CentOS7.2
10.1.38-MariaDB
全部删除MySQL/MariaDB(新机器直接跳过此步)
MySQL 已经不再包含在 CentOS 7 的源中,而改用了 MariaDB;
查看rpm已经安装的
[root@localhost logs]# rpm -qa | grep Maria*
MariaDB-common-10.1.38-1.el7.centos.x86_64
MariaDB-server-10.1.38-1.el7.centos.x86_64
MariaDB-shared-10.1.38-1.el7.centos.x86_64
MariaDB-client-10.1.38-1.el7.centos.x86_64
删除所有
rpm -e MariaDB-common-10.1.38-1.el7.centos.x86_64
[root@cdh-1 mysql]# rpm -e MariaDB-shared-10.1.38-1.el7.centos.x86_64
error: Failed dependencies:
libmysqlclient.so.18()(64bit) is needed by (installed) MySQL-python-1.2.5-1.el7.x86_64
libmysqlclient.so.18(libmysqlclient_18)(64bit) is needed by (installed) MySQL-python-1.2.5-1.el7.x86_64
#强制卸载,因为没有--nodeps
rpm -e --nodeps MariaDB-client-5.5.49-1.el7.centos.x86_64
#卸载数据库:
[root@localhost logs]# yum -y remove mari*
#删除数据库文件:
[root@localhost logs]# rm -rf /var/lib/mysql/*
增加mariaDB的yum源
[root@VM_0_4_centos ~]# cd /etc/yum.repos.d/
[root@VM_0_4_centos ~]# cp CentOS-Base.repo CentOS-Base.repo.bak
[root@VM_0_4_centos ~]# vim CentOS-Base.repo
#国外官网:https://downloads.mariadb.org/mariadb/repositories/#mirror=neusoft
#科大源:http://mirrors.ustc.edu.cn/help/mariadb.html
#插入一下内容,第二个我换成了国内源中科大的(有问题可查看,网址),(系统及版本选择(国外官网),选择安装你需要的版本
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.1/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
[mariadb]
name = MariaDB
baseurl = https://mirrors.ustc.edu.cn/mariadb/yum/10.1/centos7-amd64/
gpgkey=https://mirrors.ustc.edu.cn/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck=1
#网址:https://mirrors.ustc.edu.cn/mariadb/yum/
#系统及版本选择,你可以根据这个网址(国内)去选择你想安装centos7amd64位其他版本号的版本,网址。直接修改baseurl中的版本号即可。安装Mariadb之前,你可以先导入GPG key
[root@VM_0_4_centos ~]# rpm --import https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
运行安装命令安装MariaDB(安装过程可能会比较漫长,使用中科大的就快了)
yum -y install MariaDB-server MariaDB-client
要配置的地址
vi /etc/my.cnf v5.x
vi /etc/my.cnf.d/server.cnf v10+
配置MariaDB
使用vim /etc/my.cnf.d/server.cnf命令编辑server.cnf文件,在[mysqld]标签下添加:
[mysqld] 部分新增
lower_case_table_names=1
collation-server = utf8_general_ci
character-set-server = utf8
transaction-isolation = READ-COMMITTED
key_buffer = 16M
key_buffer_size = 32M
max_allowed_packet = 32M
thread_stack = 256K
thread_cache_size = 64
query_cache_limit = 8M
query_cache_size = 64M
query_cache_type = 1
max_connections = 550
#expire_logs_days = 10
#max_binlog_size = 100M
#log_bin should be on a disk with enough free space.
#Replace '/var/lib/mysql/mysql_binary_log' with an appropriate path for your
#system and chown the specified folder to the mysql user.
log_bin=/var/lib/mysql/mysql_binary_log
#In later versions of MariaDB, if you enable the binary log and do not set
#a server_id, MariaDB will not start. The server_id must be unique within
#the replicating group.
server_id=1
binlog_format = mixed
read_buffer_size = 2M
read_rnd_buffer_size = 16M
sort_buffer_size = 8M
join_buffer_size = 8M
# InnoDB settings
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 64M
innodb_buffer_pool_size = 4G
innodb_thread_concurrency = 8
innodb_flush_method = O_DIRECT
innodb_log_file_size = 512M
配置项说明
1. 防止死锁,设置隔离级别为READ-COMMITTED
transaction_isolation = READ-COMMITTED
2. 由于大量写,推荐硬盘直写,不经过系统缓存
innodb_flush_method = O_DIRECT
3. 设置最大连接数max_connections,原则上小于50主机,每个数据库100连接,并最后结果+50连接
例如Cloudera Manager Server, Activity Monitor, Reports Manager, Cloudera Navigator, and Hive metastore
5个数据库,设置最大连接550
如果大于50主机,那么数据库应该拆分到多个主机上
max_connections = 550
4. 二进制日志部分不是必须的,依据数据库管理策略进行设置
配置初始化完成,重启Mariadb服务,并设为开机自启。
systemctl start mariadb #启动服务
systemctl enable mariadb #设置开机启动
systemctl restart mariadb #重新启动
systemctl stop mariadb.service #停止MariaDB
数据库设置
/usr/bin/mysql_secure_installation
首先是设置密码,会提示先输入密码
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] <– 是否重新加载权限表,回车
All done!
之后进入Mariadb,查看字符集。
show variables like "%character%";show variables like "%collation%";
添加用户,设置权限
创建用户命令(用户名,密码请自行修改)
create user username@localhost identified by 'password';
授予外网登陆权限
grant all privileges on *.* to username@'%' identified by 'password';
select host,user,password from user;
其他
授予部分权限只需把all privileges改为select,insert,update,delete,create,drop,index,alter,grant,references,reload,shutdown,process,file其中一部分即可