GuoLi
3/13/2018 - 3:19 AM

MariaDB安装设置(CentOS)

MariaDB安装设置(CentOS),mysql, mariadb

一、设置yum源

参考官网源配置工具, 新建一个MariaDB.repo文件,

$ sudo vim /etc/yum.repos.d/MariaDB.repo

复制官网生成的源信息

# MariaDB 10.2 CentOS repository list - created 2018-03-13 03:28 UTC
# http://downloads.mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.2/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

[mysql57-community]
name=MySQL 5.7 Community Server
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql57-community-el7/
#enabled=1
#gpgcheck=1
#gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

官方源比较慢,可以使用国内的镜像

[mariadb]
name = MariaDB
baseurl = http://mirrors.ustc.edu.cn/mariadb/yum/10.2/centos7-amd64/
gpgkey = http://mirrors.ustc.edu.cn/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck=1

[mysql57-community]
name=MySQL 5.7 Community Server
baseurl=http://mirrors.ustc.edu.cn/mysql-repo/yum/mysql-5.7-community/el/7/$basearch/
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

二、安装数据库

# 卸载旧数据库
$ rpm -qa | grep -i mariadb
$ sudo yum remove -y mariadb*

# 更新repo源
$ sudo yum clean all
$ sudo yum makecache fast

# 安装新的数据库
sudo yum install -y MariaDB-server MariaDB-client

三、查看数据库服务

# 查看数据库是否启动
$ systemctl status mariadb

# 启动数据库
$ sudo systemctl start mariadb

# 查看是否开机启动
$ systemctl is-enabled mariadb

# 设置开机启动
$ sudo systemctl enable mariadb

四、初始化安全配置

$ sudo mysql_secure_installation
Enter current password for root (enter for none):  输入当前的root密码(默认空),直接回车
Set root password? [Y/n] 设置新密码,y
Remove anonymous users? [Y/n] 移除匿名用户,y
Disallow root login remotely? [Y/n] 禁止root用户远程登录,n
Remove test database and access to it? [Y/n] 移除测试数据库,y
Reload privilege tables now? [Y/n] y

五、登录数据库

# 数据库信息
$ mysqladmin -u root -p version

# 使用root用户登录
$ mysql -u root -p
或
$ mysql -u root -p -h localhost -P 3306

六、配置数据库

参考官网设置字符集和排序规则

  • 修改server端字符集
$ sudo vim /etc/my.cnf.d/server.cnf
[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8
  • 修改server端大小写敏感
[mysqld]
lower_case_table_names = 1
  • lower_case_table_names = 0时,mysql会根据表名直接操作,大小写敏感。
  • lower_case_table_names = 1时,mysql会先把表名转为小写,再执行操作。

注意:lower_case_table_names=0时使用大小写混用创建表名,再设置lower_case_table_names=1后,原创建的表使用时会提示不存在。如果要将lower_case_table_names从0修改为1,应先对旧数据表的表名进行处理,把所有数据库的表名先改为小写,最后再设置lower_case_table_names为1,否则会出现上述的问题。

  • 修改client端字符集
$ sudo vim /etc/my.cnf.d/mysql-clients.cnf
[mysql]
default-character-set=utf8
  • 重启mariadb服务
$ sudo systemctl restart mariadb

七、测试数据库

MariaDB [(none)]>
-- 帮助信息
help;
help show;
help use;
-- 查看MySQL的状态
STATUS;
-- 显示支持的引擎
SHOW ENGINES;
-- 显示支持的字符集
SHOW CHARACTER SET;
-- 显示支持的排序规则
SHOW COLLATION;
-- 显示数据库当前字符集
SHOW VARIABLES LIKE '%character%';
-- 显示数据库当前排序规则
SHOW VARIABLES LIKE '%collation%';
-- 显示所有数据库
SHOW DATABASES;
-- 切换数据库上下文,即设置当前会话的默认数据库
USE mysql;
MariaDB [(mysql)]>
-- 显示本数据库所有的表
SHOW TABLES FROM information_schema;
-- 显示user表中所有字段
SHOW COLUMNS FROM information_schema.tables;
DESCRIBE information_schema.tables;
-- 创建一个表
CREATE TABLE tab (
  id int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  userId char(36),
  lastLoginTime timestamp,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 插入测试数据
INSERT INTO tab(userId)
VALUES ('admin'), ('haha');

-- 更新表
UPDATE tab SET userId=1 WEHRE id=1;

-- 清空表
DELETE FROM tab [WHERE expression];
TRUNCATE TABLE tab;

-- 常用查询
-- 查询数据库中所有用户
SELECT user, host, grant_priv FROM mysql.user;

-- 查询数据库中的表信息
SELECT table_name FROM information_schema.TABLES
WHERE table_schema='information_schema';

-- 查询数据库中的字段信息
SELECT column_name AS Field, column_type AS Type, is_nullable AS 'Null'
FROM information_schema.columns 
WHERE table_schema='information_schema' AND table_name='tables';

-- 查询mysql数据库中所有表和字段信息
SELECT table_name as 'Table', column_name as Field, column_type as Type
FROM information_schema.columns
WHERE TABLE_SCHEMA='mysql'

-- 查询数据库版本
SELECT version();

-- 查询当前连接的用户名
SELECT user();
SELECT current_user();

-- 以列的方式显示结果
SELECT * FROM mysql.user WHERE user='root' AND host='localhost'\G

八、创建数据库

$ mysql -u root -p -h localhost -P 3306
MariaDB [(none)]>

CREATE DATABASE hive;
SHOW DATABASES;

九、创建用户及数据库

$ mysql -u root -p -h localhost -P 3306

命令: CREATE USER 'username'@'host' IDENTIFIED BY 'password';
说明:

  • username - 将要创建的用户名
  • host - 指定该用户在哪个主机上可以登陆,如果是本地用户可用localhost,如果想让该用户可以从任意远程主机登陆,可以使用通配符%.
  • password - 该用户的登陆密码,密码可以为空,如果为空则该用户可以不需要密码登陆服务器。
MariaDB [(none)]>

CREATE USER 'hive'@'%' IDENTIFIED BY 'hive';
flush privileges;

十、对用户授权

命令: GRANT privileges ON database.table TO 'user'@'host' IDENTIFIED BY 'password' WITH GRANT OPTION;
说明:

  • privileges - 用户的操作权限,如SELECT,INSERT,UPDATE等,如果要授予所的权限则使用ALL,show privileges可以查看系统可以使用的权限;
  • database - 数据库名
  • table - 表名,如果要授予该用户对所有数据库和表的相应操作权限则可用*表示
  • password - 给用户设置密码

命令:SHOW GRANTS [FOR user]; 查看[当前]用户所有权限

SHOW GRANTS FOR 'root'@'localhost'
MariaDB [(none)]>

-- 给root权限
GRANT all ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;

GRANT all ON hive.* TO 'hive'@'%';
flush privileges;

使用操作系统口令直接登录mysql shell

-- 安装UNIX_SOCKET plugin,该插件在ubuntu 15.0以后的系统是默认安装的
INSTALL PLUGIN unix_socket SONAME 'auth_socket';
CREATE USER serg IDENTIFIED VIA unix_socket;
-- ALTER USER在MariaDB 10.2.0以后才可用
ALTER USER root@localhost identified via unix_socket;
-- 或者用GRANT命令
GRANT ALL ON *.* TO root@localhost IDENTIFIED VIA unix_socket;

-- 重置为以密码方式登录mysql shell
ALTER USER root@localhost identified with 'mysql_native_password';
SET PASSWORD = password('foo');

十一 DML语句

-- 从文件加载数据到表
LOAD DATA [LOCAL] INFILE '/tmp/tab.csv' [REPLACE] INTO TABLE tab FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

LOAD DATA LOCAL INFILE 'abc.csv' INTO TABLE abc
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(col1, col2, col3, col4, col5...);

-- 导出表数据到csv文件
SELECT * FROM tab INTO OUTFILE '/tmp/tab.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';