CentOS7安装配置MariaDB

---

title: CentOS7安装配置MariaDB
categories: 数据库
tags:
- MariaDB
- MySQL
timezone: Asia/Shanghai
date: 2019-01-06

环境

[root@localhost mysql]# cat /etc/centos-release
CentOS Linux release 7.5.1804 (Core) 

[root@localhost mysql]# mysql -V
mysql  Ver 15.1 Distrib 5.5.60-MariaDB, for Linux (x86_64) using readline 5.1

（一）.安装mysql

yum install mariadb mariadb-server mariadb-libs mariadb-devel -y

检查安装结果
rpm -qa |grep mariadb
	[root@localhost ~]# rpm -qa |grep mariadb
	mariadb-5.5.60-1.el7_5.x86_64
	mariadb-server-5.5.60-1.el7_5.x86_64
	mariadb-libs-5.5.60-1.el7_5.x86_64
	mariadb-devel-5.5.60-1.el7_5.x86_64

启动并设置开机自动启动

systemctl start mariadb
systemctl status mariadb
systemctl enable mariadb

（二）.初始化数据库设置

• 输入root密码，没有密码直接回车

    [root@localhost ~]# 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...
  

• 提示设置root user密码，Y

    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!
  

• 删除系统创建的默认匿名用户：Y

    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!
  

• 禁止root用户远程登录：Y

    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
     ... Success!
  

• 是否删除系统创建的test数据库，生产环境建议删除

    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] n
     ... skipping.
  

• 重载权限表：Y

    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!
  

（三）.mariadb的简单应用

mariadb命令后边一定要加;结尾

登录mariadb

mysql -u root -p

查看都有哪些数据库表

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql的密码都在mysql这个库里面，有一张表叫user，这里管理了可以登陆数据库的用户

常用命令及示例！

use mysql;      ##切换到mysql数据库
show tables;    ##显示本数据库有哪些表
SELECT User,Host,Password FROM mysql.user;  ##查询mysql数据库的user表
desc mysql.user;    ##查询user表结构
flush privileges;   ##刷新数据库

创建一个新的数据库并对这个表做相应操作

以下操作包含了:创建一个数据库、切换到某数据库、创建一个表、插入表的两种方法、更新一行数据、删除一行数据

create database test;
use test; 
create table linux(username varchar(15) not null,password varchar(15) not null);
insert into linux values ('XiaoMing', 'xiaoliu');
insert into linux values ('XiaoHong', '12346'), ('HongHong', '12346');
update linux set `password` = '112233' where username = 'honghong'
delete from linux where `password`='112233'

高级命令，慎用@@@@

#删除数据库
drop database <数据库名>;
#删除表
DROP TABLE 表名;

安装命令自动补全工具 MYCLI

yum -y install epel-release python-pip python-devel
yum clean all
pip install mycli

命令使用：
mycli   #连接本机可以直接输入命令后回车
mycli -h localhost -u root
mycli -h 192.168.0.61 -u root

开启服务器的远程访问

这里改的密码跟本地登录的密码不是一个，虽然是一个账户

允许所有IP使用root用户远程登录，并将密码改为my-new-password

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'my-new-password' WITH GRANT OPTION;

只允许192.168.0.*段的IP地址使用root用户远程登录，并将密码改为my-new-password

GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.0.%' IDENTIFIED BY 'my-new-password' WITH GRANT OPTION;

使用脚本备份 数据库，将以下部分存为.sh并+x权限执行即可

#!/bin/bash

# mysql用户名
db_user="root"

# mysql密码
db_passwd="xiaoliu"

# 要备份的数据库名称
db_name="test"

# 备份位置
backup_dir="/home/mysqlbak"

# 文件名设置为时间格式
time="$(date +"%Y%m%d%H%M%S")"

mysqldump -u$db_user -p$db_passwd $db_name > "$backup_dir/$db_name"_"$time.sql"


#备份test库
#mysqldump -uroot -pxiaoliu test > "/home/test"_"$(date +"%Y%m%d%H%M%S").sql"

#备份所有数据库    
#mysqldump -uroot -pxiaoliu --opt --all-databases > "/home/mysqlbak/all-databases"_"$(date +"%Y%m%d%H%M%S").sql"