RHEL7 MariaDB测试

2023-07-27 22:17:04

MariaDB安装及启动：

yum groupinstall mariadb
启动mariadb
systemctl restart mariadb
systemctl enable mariadb
netstat -anplut | grep mysql #mysql端口3306
firewall-cmd --permanent --add-service=mysql
firewall-cmd --reload mysqld_db_t
#SElinux上下文
ll /usr/share/mysql/ #有4个规模的配置文件模板
vim /etc/my.cnf #配置文件
ll /var/lib/mysql #每个数据库会在此文件夹下建立一个单独目录

创建测试scott数据库的sql脚本

#vi /scott.sql

create database scott;

use scott

create table dept(

    -- 部门编号

    deptno      int unsigned auto_increment primary key,

    -- 部门名称

    dname       varchar(15)     ,

    -- 部门所在位置

    loc         varchar(50)

)engine = InnoDB;

create table emp(

    -- 雇员编号

    empno           int unsigned auto_increment primary key,

    -- 雇员姓名

    ename           varchar(15)     ,

    -- 雇员职位

    job             varchar(10)     ,

    -- 雇员对应的领导的编号

    mgr             int unsigned    ,

    -- 雇员的雇佣日期

    hiredate        date            ,

    -- 雇员的基本工资

    sal             decimal(7,2)    ,

    -- 奖金

    comm            decimal(7,2)    ,

    -- 所在部门

    deptno          int unsigned    ,

    foreign key(deptno) references dept(deptno)

)engine = innodb;

create table salgrade(

    -- 工资等级

    grade       int unsigned    ,

    -- 此等级的最低工资

    losal       int unsigned    ,

    -- 此等级的最高工资

    hisal       int unsigned

)engine=innodb;

create table bonus(

    -- 雇员姓名

    ename       varchar(10),

    -- 雇员职位

    job         varchar(9),

    -- 雇员工资

    sal         decimal(7,2),

    -- 雇员资金

    comm        decimal(7,2)

)engine=innodb;

INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK');

INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');

INSERT INTO dept VALUES (30,'SALES','CHICAGO');

INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON');

INSERT INTO emp VALUES    (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);

INSERT INTO emp VALUES    (7499,'ALLEN','SALESMAN',7698,'1981-2-20',1600,300,30);

INSERT INTO emp VALUES    (7521,'WARD','SALESMAN',7698,'1981-2-22',1250,500,30);

INSERT INTO emp VALUES    (7566,'JONES','MANAGER',7839,'1981-4-2',2975,NULL,20);

INSERT INTO emp VALUES    (7654,'MARTIN','SALESMAN',7698,'1981-9-28',1250,1400,30);

INSERT INTO emp VALUES    (7698,'BLAKE','MANAGER',7839,'1981-5-1',2850,NULL,30);

INSERT INTO emp VALUES    (7782,'CLARK','MANAGER',7839,'1981-6-9',2450,NULL,10);

INSERT INTO emp VALUES    (7788,'SCOTT','ANALYST',7566,'87-7-13',3000,NULL,20);

INSERT INTO emp VALUES    (7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);

INSERT INTO emp VALUES    (7844,'TURNER','SALESMAN',7698,'1981-9-8',1500,0,30);

INSERT INTO emp VALUES    (7876,'ADAMS','CLERK',7788,'87-7-13',1100,NULL,20);

INSERT INTO emp VALUES    (7900,'JAMES','CLERK',7698,'1981-12-3',950,NULL,30);

INSERT INTO emp VALUES    (7902,'FORD','ANALYST',7566,'1981-12-3',3000,NULL,20);

INSERT INTO emp VALUES    (7934,'MILLER','CLERK',7782,'1982-1-23',1300,NULL,10);

INSERT INTO salgrade VALUES (1,700,1200);

INSERT INTO salgrade VALUES (2,1201,1400);

INSERT INTO salgrade VALUES (3,1401,2000);

INSERT INTO salgrade VALUES (4,2001,3000);

INSERT INTO salgrade VALUES (5,3001,9999);

无密码登录到mariadb
mysql #没设置root密码之前可以直接登录
select database(); #查看当前进入的数据库
select user(); #查看当前登录用户
show databases; #查看数据库
use test #进入test数据库
show tables; #查看此数据库的表，test没表，可以看其他数据库的
desc emp; #查看表结果
show variables like 'innodb%'; #查看环境变量

设置root密码
mysqladmin -u root password '123456'
mysql -u root -p #输入密码即可进入

调用系统资源
system ls -l /root #system+系统指令，可以直接显示系统指令执行结果
source /scott.sql #执行sql脚本

status #查看mariadb属性 

MariaDB [(none)]> status

--------------

mysql  Ver 15.1 Distrib 5.5.-MariaDB, for Linux (x86_64) using readline 5.1

Connection id:

Current database:

Current user:           root@localhost

SSL:                    Not in use

Current pager:          stdout

Using outfile:          ''

Using delimiter:        ;

Server:                 MariaDB

Server version:         5.5.-MariaDB MariaDB Server

Protocol version:

Connection:             Localhost via UNIX socket

Server characterset:    latin1

Db     characterset:    latin1

Client characterset:    utf8

Conn.  characterset:    utf8

UNIX socket:            /var/lib/mysql/mysql.sock

Uptime:                  hours  min  sec

Server characterset: latin1 #服务器的字符集
Db characterset: latin1 #数据库的字符集
Client characterset: utf8 #客户端的字符集
Conn. characterset: utf8 #连接的字符集
客户端的字符集要和数据库的字符集一致，不一致有可能乱码,一般企业都使用UTF-8，建议修改mariadb字符集。

修改字符集：

vim /etc/my.cnf
character-set-server=utf8 #增加到[mysqld]字段

[mysqld]

character-set-server=utf8

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

.......

systemctl restart mariadb
MariaDB [(none)]> status #重启后变更为新字符集
Server characterset: utf8 Db
characterset: utf8
Client characterset: utf8 Conn.
characterset: utf8
查看数据库的字符集
MariaDB [(none)]> show create database scott;

[root@rhel2 ~]# systemctl restart mariadb

[root@rhel2 ~]# mysql -u root -p

Enter password:

Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MariaDB connection id is

Server version: 5.5.-MariaDB MariaDB Server

Copyright (c) , , Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> status

--------------

mysql  Ver 15.1 Distrib 5.5.-MariaDB, for Linux (x86_64) using readline 5.1

Connection id:

Current database:

Current user:           root@localhost

SSL:                    Not in use

Current pager:          stdout

Using outfile:          ''

Using delimiter:        ;

Server:                 MariaDB

Server version:         5.5.-MariaDB MariaDB Server

Protocol version:

Connection:             Localhost via UNIX socket

Server characterset:    utf8

Db     characterset:    utf8

Client characterset:    utf8

Conn.  characterset:    utf8

UNIX socket:            /var/lib/mysql/mysql.sock

Uptime:                  sec

Threads:   Questions:   Slow queries:   Opens:   Flush tables:   Open tables:   Queries per second avg: 0.307

--------------

MariaDB [(none)]> show create database scott;

+----------+------------------------------------------------------------------+

| Database | Create Database                                                  |

+----------+------------------------------------------------------------------+

| scott    | CREATE DATABASE `scott` /*!40100 DEFAULT CHARACTER SET latin1 */ |

+----------+------------------------------------------------------------------+

 row in set (0.00 sec)

MariaDB [(none)]>

scott数据库是在更改字符集之前建立，所以字符集是latin

drop database scott; #删除数据库
source /scott.sql #使用脚本重建数据库
show create database scott; #在更改字符集之后建立，所以是字符集是utf8
show create table emp;
更改字符集
alter database scott charset utf8;
alter table emp charset utf8;

========================================

导出表数据到外部文件：
MariaDB [scott]> select * from emp into outfile '/var/lib/mysql/scott/scott.emp.txt' fields terminated by ',' lines terminated by '\n';

导入外部文件到表：
MariaDB [scott]> load data infile '/var/lib/mysql/scott/scott.emp.txt' into table lxjtest fields terminated by ',' lines terminated by '\n';

备份数据库及表：
#mysqldump -u root -p scott > /scott.dump ---备份scott整个库
#mysqldump -u root -p scott emp > /scott_emp.dump --备份scott库中的emp表
# mysqldump -u root -p scott dept salgrade > /scott.dept_salgrade.dump --备份多个表

还原：
# mysql -u root -p scott < /scott
# mysql -u root -p scott < /scott_emp.dump

===========================

mariadb 的用户登录包括用户名和登录位置两部分。
早期版本%就可以代表所有的连接，后期版本localhost表示本地登录，%表示tcp/ip的远程登录的所有ip。
%也可以写具体主机或网段，比如192.168.100.2、192.168.100.% 或者192.168.100.0/255.255.255.0，其他格式不识别。

如果登录主机符合多个连接条件，默认连接第一个身份，设置权限的时候需要注意
select user(); #查看当前登录用户
select host,user,password from user; #查看数据库所有登录范围、用户和密码，4个root不是同一个用户，可以单独设置密码
create user rusky@'localhost' identified by '123456'; #建立可以本地登录的用户rusky并设置密码123456
create user rusky@'%' identified by '123456'; #建立可以远程登录的用户rusky并设置密码123456
create user rusky2@'localhost' identified by '123456'; #建立可以本地登录的用户rusky2并设置密码123456
create user rusky2@'192.168.100.2' identified by '123456'; #建立可以从192.168.100.2主机登录的用户rusky2并设置密码123456
grant all on *.* to rusky@'localhost'; #授权本地登录的用户rusky对所有数据库的所有表有所有权限
grant select on scott. to rusky2@'localhost'; #授权本地登录的用户rusky2可以读取scott数据库的所有表 --帮助提示，输入help grant，有示例。
grant all on *.* to ruskyrusky2@'%' identified by '123456'; #可以建立用户、授权、设置密码一起做
flush privileges; #刷新授权表
show privileges; #查看所有权限
show grants for rusky@'localhost'; #查看用户权限
show grants for rusky2@'localhost';
revoke select on scott. from rusky2@'192.168.100.2'; #收回权限

修改密码
在linux系统中修改
#mysqladmin -u root -p password '123456' #需输入root原始密码
在数据库中修改
MariaDB [mysql]>set password=password('redhat'); #对当前用户设置密码，立刻生效
MariaDB [mysql]>set password for rusky@'localhost'=password('123456'); #对任意用户设置密码
MariaDB [(none)]>use mysql
MariaDB [mysql]>update user set password=password('redhat') where user='root' and host='localhost';
#修改密码后需要flush privileges;刷新权限表，或重启服务

mariadb忘记root密码
方法一 systemctl stop mairadb
vi /etc/my.cnf skip-grant-tables #增加到[mysqld]字段，进入单用户模式
#systemctl start mariadb 输入mysql命令无需密码直接进入数据库
MariaDB [(none)]>use mysql
MariaDB [mysql]>update user set password=password('redhat') where user='root' and host='localhost';
停止mariadb服务，删除配置文件的skip-grant-tables选项，重启mariadb服务，使用新密码进入

方法二 systemctl stop mariadb
mysqld_safe --skip-grant-tables

先停止mariadb，输入参数进入单用户模式光标将停住，新建窗口输入mysql可直接进入数据库并修改密码，完成后退出终端，重启mariadb服务，使用新密码登录
http://www.cnblogs.com/rusking/p/4602051.html
----

#### 提高mariadb的安全性

mysql_secure_installation Enter current password for root (enter for none): #输入当前root的密码，密码为空直接回车即可 Change the root password? [Y/n] #是否修改root的密码 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] #是否重新加载授权信息 systemctl restart mariadb

不通过firewalld，mariddb本身就可以禁止网络访问数据库 vim /etc/my.cnf skip-networking=1 #添加在[mysqld]字段

有时需重启系统才生效

systemctl restart mariadb netstat -anplut | grep mysql # 查询不到任何监听端口

上一篇：深入理解SQL注入绕过WAF与过滤机制


下一篇：SQL Injection（Blind）