RHEL7 MariaDB测试-白红宇

RHEL7 MariaDB测试

阅读量：6906 次

发布时间：2019-06-27

本文共 9733 字，大约阅读时间需要 32 分钟。

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 scottcreate 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.56-MariaDB, for Linux (x86_64) using readline 5.1Connection id:          10Current database:Current user:           root@localhostSSL:                    Not in useCurrent pager:          stdoutUsing outfile:          ''Using delimiter:        ;Server:                 MariaDBServer version:         5.5.56-MariaDB MariaDB ServerProtocol version:       10Connection:             Localhost via UNIX socketServer characterset:    latin1Db     characterset:    latin1Client characterset:    utf8Conn.  characterset:    utf8UNIX socket:            /var/lib/mysql/mysql.sockUptime:                 18 hours 11 min 3 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=utf8datadir=/var/lib/mysqlsocket=/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 -pEnter password: Welcome to the MariaDB monitor.  Commands end with ; or \g.Your MariaDB connection id is 2Server version: 5.5.56-MariaDB MariaDB ServerCopyright (c) 2000, 2017, 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.56-MariaDB, for Linux (x86_64) using readline 5.1Connection id:          2Current database:Current user:           root@localhostSSL:                    Not in useCurrent pager:          stdoutUsing outfile:          ''Using delimiter:        ;Server:                 MariaDBServer version:         5.5.56-MariaDB MariaDB ServerProtocol version:       10Connection:             Localhost via UNIX socketServer characterset:    utf8Db     characterset:    utf8Client characterset:    utf8Conn.  characterset:    utf8UNIX socket:            /var/lib/mysql/mysql.sockUptime:                 13 secThreads: 1  Questions: 4  Slow queries: 0  Opens: 0  Flush tables: 2  Open tables: 26  Queries per second avg: 0.307--------------MariaDB [(none)]> show create database scott;+----------+------------------------------------------------------------------+| Database | Create Database                                                  |+----------+------------------------------------------------------------------+| scott    | CREATE DATABASE `scott` /*!40100 DEFAULT CHARACTER SET latin1 */ |+----------+------------------------------------------------------------------+1 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 # 查询不到任何监听端口

你可能感兴趣的文章

BZOJ 2141: 排队 [CDQ分治]