本文详细介绍MySQL数据库的安装配置、安全设置、基本操作、数据表管理、用户权限管理及数据库备份等内容。
mysql为关系型数据库，这种所谓的“关系型”可以理解为“表格”的概念，一个关系型数据库由一个或者数个表格组成，如图所示的一个表格：
表头（header）:每一列的名称；
列（row）: 具有相同数据类型的数据集合；
行（col）:每一行用来描述某个人或物的具体信息；
值(value)：行的具体信息，每个值必须与该列的数据类型相同；
键(key):表中用来识别某个特定的人/物的方法，键的值在当前列中具有唯一性；
MySQL中的数据类型
MySQL有三大类数据类型, 分别为数字、日期\时间、字符串, 这三大类中又更细致的划分了许多子类型:
· 数字类型
· 整数: tinyint、smallint、mediumint、int、bigint
· 浮点数: float、double、real、decimal
· 日期和时间: date、time、datetime、timestamp、year
· 字符串类型
· 字符串: char、varchar
· 文本: tinytext、text、mediumtext、longtext
· 二进制(可用来存储图片、音乐等): tinyblob、blob、mediumblob、longblob
1.配置网络以及yum源， yum search mariadb 查询我们需要安装的软件；
2.yum install -y mariadb-server.x86_64 mariadb.x86_64 ####安装我们的数据库
3.重新启动mariadb服务,设置开机自启动；
4.数据库是非常重要的地方，我们需要对数据库的安全方面考虑，以下几点是我们要考虑的地方：
a.关于selinux 我们可以设置为permissive 警告状态；也可以设置为关闭状态；
b.在系统默认的情况下，mysql会在我们的系统打开端口方便其他客户端链接，我们需要禁用这个端口：（扩展：端口不是独立存在的，它是依附于进程的。某个进程开启，那么它对应的端口就开启了，进程关闭，则该端口也就关闭了。下次若某个进程再次开启，则相应的端口也再次开启。而不要纯粹的理解为关闭掉某个端口，不过可以禁用某个端口。）
通过查询我们知道数据库开启的端口是3306，现在我们需要关闭端口：
禁用mysql的端口号完成；
c.我们还需要设定禁止其他用户禁止网上访问：
重新启动mysql服务就可以；
5.mysql用户设置密码：
回车建进入如下设置：
两次输入密码设置完成；
在进入主页面之前，mysql会提示你是否设置一些相关内容，因为数据库相当重要，所以我们必须以安全为主：
6.让防火墙通过mysql服务
7.使用超级用户身份进入数据库
8.查看mysql数据库里面含有那些数据库
这个就是我们进入mysql数据库，里面有3个数据库；
9.创建westos数据库
注意：数据库里面的命令写完都需要加分号；
使用mysqladmin创建数据库
[root@server130 ~]# mysqladmin -uroot -p create westos;
Enter password:
[root@server130 ~]# mysql -uroot -p
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| westos |
+--------------------+
5 rows in set (0.00 sec)
进入数据库使用create命令创建数据库；
10.进入westos数据库 ；创建linux表添加username.passwd等；
11.查看新建表格信息
mysql支持多种类型，大致可以分为：数值，日期、时间和i字符串；
关键字INI是INTEGER的同义词，关键字DEC是DECIMAL的同义词。
表示时间值的日期和时间类型为DATETIME,DATE,TIMESTAMP,TIME和YEAR。
字符串类型：含有char,varchar,binary,varbinary,blob,text,enum,set;
char和varchar类型相似，但是他们保存和检索的方式不同。最大长度和是否尾部空格被保留等方面不同。存储和检索过程不进行大小写转换；
查看数据表的属性，属性类型，主键信息，是否为null，默认值等其他信息；
MariaDB [mysql]> show columns from db;
+-----------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
+-----------------------+---------------+------+-----+---------+-------+
22 rows in set (0.01 sec)
显示数据表的信息索引信息，包括主键；
MariaDB [mysql]> show index from db;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| db | 0 | PRIMARY | 1 | Host | A | NULL | NULL | NULL | | BTREE | | |
| db | 0 | PRIMARY | 2 | Db | A | NULL | NULL | NULL | | BTREE | | |
| db | 0 | PRIMARY | 3 | User | A | 2 | NULL | NULL | | BTREE | | |
| db | 1 | User | 1 | User | A | 1 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)
12.向表中插入数据
insert 语句可以向表格里面添加一行或者多行数据到数据库
insert [into] 表名 [(列名1，列名2....)] values(值1，值2.....); #[]内容可选
a.追加用户lee,密码123到linux数据库表
b.有时我们需要插入部分数据，或者不按照列表的顺序进行插入
insert into 表名 （列名2，列名4，列名1） values("值2"，"值4"，"值1")
13.查询表中的数据
a. select 语句常用来根据一定的规则查询规则到数据库表获取数据
select 列名称 from 表名称 [查询条件]
* 表示查询表中所有的内容
b.按照特定条件查询：
where 关键字用于查询指定条件
select 列名称 from 表名称 where 条件；
示例：select * from stedent where sex="女"；
select * from student where id<5 and age>20;
where 子句不仅仅支持 "where 列名 = 值" 这种名等于值的查询形式, 对一般的比较运算的运算符都是支持的, 例如 =、>、<、>=、<、!= 以及一些扩展运算符 is [not] null、in、like 等等。 还可以对查询条件使用 or 和 and 进行组合查询。
14.更新表中的数据
update 语句用来修改表中的数据
update 表名称 set 列名称=新值 where 更新条件；
示例：将用户lee的密码更改为456；
15.删除表中的数据
delete 语句用于删除表中的数据
delete from 表名 where 删除条件 ##该语句用于删除表中的数据，如果没有指定条件将删除数据表的所有数据；
MariaDB [redhat]> select * from runoob_tbl;
+-----------+--------------+---------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+--------------+---------------+-----------------+
| 121 | working | pp | 2018-11-26 |
| 123 | drinking | ww | NULL |
+-----------+--------------+---------------+-----------------+
2 rows in set (0.01 sec)
MariaDB [redhat]> delete from runoob_tbl;
Query OK, 2 rows affected (0.03 sec)
MariaDB [redhat]> select * from runoob_tbl;
Empty set (0.00 sec)
16.表的修改
alter table 用于对创建后的表格修改
添加列：
alter table 表名 add 列名 列数据类型 [ after 插入位置]
#不指定位置，默认追加到最后位置
追加用户其他信息到指定位置
17.修改列
alter table 表名 change 列名称 列新的名称 新的数据类型
将passwd列给名为password
18.删除列
alter table 表名 drop 列名；
19.重新名命表名称
alter table 表名 rename 新的表明
20.删除整张表
drop tabale 表名；
21.删除整个数据库
drop database 数据库名
使用mysqladmin命令删除数据库
[root@server130 ~]# mysqladmin -uroot -p drop westos;
Enter password:
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.
Do you really want to drop the 'westos' database [y/N] y
Database "westos" dropped
22.修改root用户密码
a.使用mysqladmin方式修改数据库密码：
如果没有密码直接回车密码修改成功，否则输入旧密码回车数据库密码修改完成；
23.对mysql用户授权
a.创建可以远程登录的用户，授权westos数据库所有表的select的查询权限，更新授权；
查看授权获得成功：
b. 对本地用户进行授权：
创建本地用户的身份
本地用户授权数据库westo所有文件的插入，删除权限
用户的授权进行刷新
查询授权是否成功：
c.取消某些用户的某些授权指令：
revoke delete on westos.* from lee@localhost #取消本地用户对数据库westos所有内容的删除权限；
24.数据库的备份
A:数据库的备份：
mysqldump是MySQL用于转存储数据库的实用程序。它主要产生一个SQL脚本，其中包含从头重新创建数据库所必需的命令
CREATE TABLE INSERT等。使用mysqldump导出数据需要使用 --tab 选项来指定导出文件指定的目录，该目标必须是可写的；
[root@server2 tmp]# mysqldump -uroot -p mysql user > /tmp/user1.sql
Enter password:
[root@server2 tmp]# more user1.sql
-- MySQL dump 10.14 Distrib 5.5.60-MariaDB, for Linux (x86_64)
--
-- Host: localhost Database: mysql
-- ------------------------------------------------------
-- Server version 5.5.60-MariaDB
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `user`
--
DROP TABLE IF EXISTS `user`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `user` (
`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
`User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
`Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
`Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',
`ssl_cipher` blob NOT NULL,
`x509_issuer` blob NOT NULL,
`x509_subject` blob NOT NULL,
`max_questions` int(11) unsigned NOT NULL DEFAULT '0',
`max_updates` int(11) unsigned NOT NULL DEFAULT '0',
`max_connections` int(11) unsigned NOT NULL DEFAULT '0',
`max_user_connections` int(11) NOT NULL DEFAULT '0',
`plugin` char(64) CHARACTER SET latin1 NOT NULL DEFAULT '',
`authentication_string` text COLLATE utf8_bin NOT NULL,
PRIMARY KEY (`Host`,`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `user`
--
LOCK TABLES `user` WRITE;
/*!40000 ALTER TABLE `user` DISABLE KEYS */;
INSERT INTO `user` VALUES ('localhost','root','*84BB5DF4823DA319BBF86C99624479A198E6EEE9','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y
','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0,'',''),('localhost.localdomain','root'
,'','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',
0,0,0,0,'',''),('127.0.0.1','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y
','Y','Y','Y','Y','Y','','','','',0,0,0,0,'',''),('::1','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y
','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0,'',''),('localhost','','','N','N','N','N','N','N','N','N','N',
'N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0,0,'',''),('localhost.localdomain',
'','','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','
',0,0,0,0,'',''),('localhost','zabbix','*DEEF4D7D88CD046ECA02A80393B7780A63E7E789','N','N','N','N','N','N','N','N','N','N','N','N','
N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0,0,'','');
/*!40000 ALTER TABLE `user` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2018-12-06 18:21:39
将数据库表导出成为csv格式：
MariaDB [mysql]> select * from user into outfile '/tmp/1.txt'
-> fields terminated by ',' enclosed by '"'
-> lines terminated by '\r\n';
Query OK, 7 rows affected (0.00 sec)
##查看导出的数据、
[root@server2 tmp]# more 1.txt
"localhost","root","*84BB5DF4823DA319BBF86C99624479A198E6EEE9","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","
Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","","","","","0","0","0","0","",""
"localhost.localdomain","root","","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y
","Y","Y","Y","Y","","","","","0","0","0","0","",""
"127.0.0.1","root","","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y
","Y","","","","","0","0","0","0","",""
"::1","root","","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y",
"","","","","0","0","0","0","",""
"localhost","","","N","N","N","N","N","N","N","N","N","N","N","N","N","N","N","N","N","N","N","N","N","N","N","N","N","N","N","N","N
","","","","","0","0","0","0","",""
"localhost.localdomain","","","N","N","N","N","N","N","N","N","N","N","N","N","N","N","N","N","N","N","N","N","N","N","N","N","N","N
","N","N","N","","","","","0","0","0","0","",""
"localhost","zabbix","*DEEF4D7D88CD046ECA02A80393B7780A63E7E789","N","N","N","N","N","N","N","N","N","N","N","N","N","N","N","N","N"
,"N","N","N","N","N","N","N","N","N","N","N","N","","","","","0","0","0","0","",""
B:mysql数据库完全复制数据表
如果我们需要完全的复制MySQL的数据表，包括表的结构，索引，默认值等。 如果仅仅使用CREATE TABLE ... SELECT 命令，是无法实现的。
完整复制mysql数据库表的步骤如下：
1.使用 show create table 命令获取创建数据表语句，该语句包含原数据表的结构，索引等。
2.复制以下命令显示的sql语句，修改数据表名，并执行sql语句，通过以上命令将完全复制数据表结构；
3.如果你想复制表的内容，使用insert into .....select 语句实现；
MariaDB [redhat]> show tables;
+------------------+
| Tables_in_redhat |
+------------------+
| employee_tbl |
| runoob_tbl |
+------------------+
2 rows in set (0.00 sec)
MariaDB [redhat]> show create table employee_tbl \G;
*************************** 1. row ***************************
Table: employee_tbl
Create Table: CREATE TABLE `employee_tbl` (
`id` int(11) NOT NULL,
`name` char(10) NOT NULL DEFAULT '',
`date` datetime NOT NULL,
`singin` tinyint(4) NOT NULL DEFAULT '0' COMMENT '登录次数',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
ERROR: No query specified
MariaDB [redhat]> CREATE TABLE `employee` (
-> `id` int(11) NOT NULL,
-> `name` char(10) NOT NULL DEFAULT '',
-> `date` datetime NOT NULL,
-> `singin` tinyint(4) NOT NULL DEFAULT '0' COMMENT '登录次数',
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8
-> ;
Query OK, 0 rows affected (0.00 sec)
MariaDB [redhat]> show tables;
+------------------+
| Tables_in_redhat |
+------------------+
| employee |
| employee_tbl |
| runoob_tbl |
+------------------+
3 rows in set (0.01 sec)
MariaDB [redhat]> select * from employee; #以上步骤将创建复制表的相同结构；
Empty set (0.00 sec)
#以下步骤将复制表的数据复制到克隆的数据表里面
MariaDB [redhat]> insert into employee(id,name,date,singin) select id,name,date,singin from employee_tbl;
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
MariaDB [redhat]> select * from employee;
+----+--------+---------------------+--------+
| id | name | date | singin |
+----+--------+---------------------+--------+
| 1 | 小明 | 2016-04-22 15:25:33 | 1 |
| 2 | 小王 | 2016-04-20 15:25:47 | 3 |
| 3 | 小丽 | 2016-04-19 15:26:02 | 2 |
| 4 | 小王 | 2016-04-07 15:26:14 | 4 |
| 5 | 小明 | 2016-04-11 15:26:40 | 4 |
| 6 | 小明 | 2016-04-04 15:26:54 | 2 |
+----+--------+---------------------+--------+
6 rows in set (0.00 sec)
MariaDB [redhat]> select * from employee_tbl;
+----+--------+---------------------+--------+
| id | name | date | singin |
+----+--------+---------------------+--------+
| 1 | 小明 | 2016-04-22 15:25:33 | 1 |
| 2 | 小王 | 2016-04-20 15:25:47 | 3 |
| 3 | 小丽 | 2016-04-19 15:26:02 | 2 |
| 4 | 小王 | 2016-04-07 15:26:14 | 4 |
| 5 | 小明 | 2016-04-11 15:26:40 | 4 |
| 6 | 小明 | 2016-04-04 15:26:54 | 2 |
+----+--------+---------------------+--------+
6 rows in set (0.00 sec)
C.数据库导出数据；
1.使用select .... into outfile 语句简单的导出数据到文本文件中：
MariaDB [zabbix]> 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
MariaDB [mysql]> select * from user into outfile '/tmp/user.sql';
Query OK, 7 rows affected (0.00 sec)
MariaDB [mysql]> select * from user into outfile '/tmp/user.txt';
Query OK, 7 rows affected (0.00 sec)
##以上命令指示将数据库表的数据导出，不导出数据的表结构
##对导出的数据进行查看
[root@server2 tmp]# more user.sql
localhost root *84BB5DF4823DA319BBF86C99624479A198E6EEE9 Y Y Y Y Y Y Y 0
localhost.localdomain root Y Y Y Y Y Y Y Y Y Y Y 0
127.0.0.1 root Y Y Y Y Y Y Y Y Y Y Y Y 0
::1 root Y Y Y Y Y Y Y Y Y Y Y Y Y 0
localhost N N N N N N N N N N N N 0
localhost.localdomain N N N N N N N N N N N 0
localhost zabbix *DEEF4D7D88CD046ECA02A80393B7780A63E7E789 N N N N N N N 0
[root@server2 tmp]# more user.txt
localhost root *84BB5DF4823DA319BBF86C99624479A198E6EEE9 Y Y Y Y Y Y Y 0
localhost.localdomain root Y Y Y Y Y Y Y Y Y Y Y 0
127.0.0.1 root Y Y Y Y Y Y Y Y Y Y Y Y 0
::1 root Y Y Y Y Y Y Y Y Y Y Y Y Y 0
localhost N N N N N N N N N N N N 0
localhost.localdomain N N N N N N N N N N N 0
localhost zabbix *DEEF4D7D88CD046ECA02A80393B7780A63E7E789 N N N N N N N 0
25.忘记超级用户的密码，可以通过以下步骤改正密码：
跳过密码命令; 输入mtsql直接进入数据库；
更改数据库的超级用户的密码；
密码更改完成，我们输入跳过密码命令的程序进程还在，所以需要我们结束进程才可以密码登录，否则不用密码直接登录；
查询跳过密码命令的进程
强制结束进程，重新启动mariadb服务；可以看到进程已经结束；
扫一扫
2272
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
