初探MariaDB

安装MariaDB

yum install mariadb mariadb-server mariadb-devel
systemctl start mariadb.service
systemctl enable mariadb.service

配置MariaDB

[root@daik-vm daik]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.64-MariaDB MariaDB Server
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

选择数据库
安装好后有几个默认的数据库，其中有一个叫mysql，这个里面有一个用户表user，可以修改root用户的权限和密码

MariaDB [(none)]> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed

设置root密码

MariaDB [mysql]> update user set password=password("123456")where user='root';
Query OK, 4 rows

赋予权限

MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

退出

MariaDB [mysql]> exit
Bye

创建数据库

# mysql -u root -p
mysql> CREATE DATABASE Alerts_DB;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE USER 'MySQLadmin'@'192.168.130.131' IDENTIFIED BY 'secret1234';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT INSERT,SELECT,UPDATE,CREATE,DELETE,EXECUTE on Alerts_DB.* to 'MySQLadmin'@'192.168.130.131';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> quit;

创建表

mysql -u root -p Alerts_DB < /home/db/mysql.schema

附录

mysql.schema

/*
 * SQL Schema for global database
 * Copyright (C) 2020-2030, private Inc.
 * July 8, 2020.
 * Created by laughbug  
 */
 
--DROP TABLE IF EXISTS user;--
CREATE TABLE user (
    id VARCHAR(128) NOT NULL COMMENT '用户id',
    name VARCHAR(128) NOT NULL COMMENT '用户名称',
    ip INT UNSIGNED NOT NULL COMMENT '用户ip',
    os_arch VARCHAR(16) COMMENT '用户操作系统架构',
    os_major VARCHAR(16) COMMENT '用户操作系统主版本',
    os_minor VARCHAR(16) COMMENT '用户操作系统次版本',
    os_name VARCHAR(64) NOT NULL COMMENT '用户操作系统名称',
    os_platform VARCHAR(64) COMMENT '用户操作系统平台',
    os_uname VARCHAR(256) COMMENT '用户操作系统详细信息',
    os_version VARCHAR(32) NOT NULL COMMENT '用户操作系统版本',
    status VARCHAR(32) NOT NULL COMMENT '用户状态: active, dead等',
    dateAdd DATETIME NOT NULL COMMENT '用户注册时间',
    lastKeepAlive DATETIME NOT NULL COMMENT '用户最近一次保活时间',
    comefrom VARCHAR(128) COMMENT '用户来源',
    description TEXT COMMENT '用户描述',
    PRIMARY KEY (id),
    INDEX idx_name (name),
    INDEX idx_ip (ip),
    INDEX idx_status (status)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT '用户表';

shell脚本实现数据创建

#!/bin/bash

USERNAME="root"
PASSWORD="123456"

DBNAME="Alerts_DB"
NEWUSER="MySQLadmin"
NEWPWD="123456"

MYSQL_CMD="mysql -u${USERNAME} -p${PASSWORD}"

echo "create database ${DBNAME}"
create_db_sql="create database IF NOT EXISTS ${DBNAME}"
echo ${create_db_sql}  | ${MYSQL_CMD}
if [ $? -ne 0 ]
then
 echo "create databases ${DBNAME} failed ..."
 exit 1
fi

flush_privileges="flush privileges"
echo ${flush_privileges}  | ${MYSQL_CMD}
if [ $? -ne 0 ]
then
 echo "flush_privileges failed ..."
 exit 1
fi

echo "create user ${NEWUSER} for ${DBNAME}"
create_user_sql="grant INSERT,SELECT,UPDATE,CREATE,DELETE,EXECUTE on ${DBNAME}.* to '${NEWUSER}'@'%'  identified by '${NEWPWD}'"
echo ${create_user_sql}  | ${MYSQL_CMD}
if [ $? -ne 0 ]
then
 echo "create user ${NEWUSER} failed ..."
 exit 1
fi

echo "create tables for ${DBNAME}"
mysql -u${USERNAME} -p${PASSWORD} ${DBNAME} < mysql.schema
if [ $? -ne 0 ]
then
 echo "create tables failed ..."
 exit 1
fi

shell脚本实现删除数据库

#!/bin/bash

USERNAME="root"
PASSWORD="123456"

DBNAME="Alerts_DB"
NEWUSER="MySQLadmin"
NEWPWD="123456"

MYSQL_CMD="mysql -u${USERNAME} -p${PASSWORD}"

echo "drop database ${DBNAME}"
drop_db_sql="drop database ${DBNAME}"
echo ${drop_db_sql}  | ${MYSQL_CMD}
if [ $? -ne 0 ]
then
 echo "drop databases ${DBNAME} failed ..."
 exit 1
fi

echo "drop user ${NEWUSER}"
drop_user_sql="drop user ${NEWUSER}"
echo ${drop_user_sql}  | ${MYSQL_CMD}
if [ $? -ne 0 ]
then
 echo "drop user ${DBNAME} failed ..."
 exit 1
fi

查询表结构

describe（desc）<表名>
或者show columns from <表名>

MariaDB [AIAWARE]> desc agent;
+---------------+------------------+------+-----+---------+-------+
| Field         | Type             | Null | Key | Default | Extra |
+---------------+------------------+------+-----+---------+-------+
| id            | varchar(128)     | NO   | PRI | NULL    |       |
| name          | varchar(128)     | NO   | MUL | NULL    |       |
| ip            | int(10) unsigned | NO   | MUL | NULL    |       |
| os_arch       | varchar(16)      | YES  |     | NULL    |       |
| os_major      | varchar(16)      | YES  |     | NULL    |       |
| os_minor      | varchar(16)      | YES  |     | NULL    |       |
| os_name       | varchar(64)      | NO   |     | NULL    |       |
| os_platform   | varchar(64)      | YES  |     | NULL    |       |
| os_uname      | varchar(256)     | YES  |     | NULL    |       |
| os_version    | varchar(32)      | NO   |     | NULL    |       |
| status        | varchar(32)      | NO   | MUL | NULL    |       |
| version       | varchar(64)      | NO   |     | NULL    |       |
| dateAdd       | datetime         | NO   |     | NULL    |       |
| registerIP    | int(10) unsigned | NO   |     | NULL    |       |
| lastKeepAlive | datetime         | NO   |     | NULL    |       |
| comefrom      | varchar(128)     | YES  |     | NULL    |       |
| description   | text             | YES  |     | NULL    |       |
| configSum     | varchar(128)     | YES  |     | NULL    |       |
| manager       | varchar(128)     | YES  |     | NULL    |       |
+---------------+------------------+------+-----+---------+-------+
19 rows in set (0.00 sec)

查询建表语句

show create table <表名>

MariaDB [AIAWARE]> show create table agent;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+-------+---------------------------------------------------------------------------------------+
| agent | CREATE TABLE `agent` (
  `id` varchar(128) NOT NULL COMMENT '主机id',
  `name` varchar(128) NOT NULL COMMENT '主机名称',
  `ip` int(10) unsigned NOT NULL COMMENT '主机ip',
  `os_arch` varchar(16) DEFAULT NULL COMMENT '主机操作系统架构',
  `os_major` varchar(16) DEFAULT NULL COMMENT '主机操作系统主版本',
  `os_minor` varchar(16) DEFAULT NULL COMMENT '主机操作系统次版本',
  `os_name` varchar(64) NOT NULL COMMENT '操作系统名称',
  `os_platform` varchar(64) DEFAULT NULL COMMENT '操作系统平台',
  `os_uname` varchar(256) DEFAULT NULL COMMENT '操作系统详细信息',
  `os_version` varchar(32) NOT NULL COMMENT '操作系统版本',
  `status` varchar(32) NOT NULL COMMENT '主机状态: active, pending, disconnected等',
  `version` varchar(64) NOT NULL COMMENT '主机版本',
  `dateAdd` datetime NOT NULL COMMENT '注册时间',
  `registerIP` int(10) unsigned NOT NULL COMMENT '主机注册ip',
  `lastKeepAlive` datetime NOT NULL COMMENT '最近一次保活时间',
  `comefrom` varchar(128) DEFAULT NULL COMMENT '主机来源',
  `description` text COMMENT '主机描述',
  `configSum` varchar(128) DEFAULT NULL COMMENT '主机配置信息校验和',
  `manager` varchar(128) DEFAULT NULL COMMENT 'manager的名称',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`),
  KEY `idx_ip` (`ip`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='主机客户端' |
+-------+---------------------------------------------------------------------------------------+
1 row in set (0.00 sec)