Mariadb 数据库

最新推荐文章于 2020-07-24 16:00:10 发布

房东的喵

最新推荐文章于 2020-07-24 16:00:10 发布

阅读量199

点赞数

CC 4.0 BY-SA版权

分类专栏： RHCE

本文链接：https://blog.youkuaiyun.com/luxunlx123/article/details/99341772

RHCE 专栏收录该内容

5 篇文章

订阅专栏

本文介绍了MariaDB数据库的基础操作，包括查看数据库、切换数据库、查看表内容、安全初始化设置root用户密码。讨论了增删改查操作，如查询、插入、更新和删除数据，并涉及用户授权。此外，还讲解了如何创建测试数据库、数据表，以及数据库的备份和恢复方法，包括密码更改和破解流程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.什么是数据库

mariadb是mysql的分支  maria是数据库开发者的女儿
和mysql是完全兼容的

yum install -y mariadb-server
systemctl start mariadb
systemctl enable mariadb
systemctl status mariadb

#端口对外暴露不安全

[root@server ~]# netstat -antlpe | grep mysqld
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      27         79401      4279/mysqld         

vim /etc/my.cnf
skip-networking=1 
systemctl restart mariadb
[root@server ~]# netstat -antlpe | grep mysql

mysql -uroot

#数据库的查看

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

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

#切换到已经存在的数据库里

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

#查看数据库中的所有表

MariaDB [mysql]> SHOW TABLES;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| servers                   |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+

#查看一个表中的全部内容

MariaDB [mysql]> SELECT * FROM user;

#安全初始化(给root用户给一个密码)

[root@server ~]# mysql_secure_installation

密码的登陆方式：

[root@server ~]# mysql -uroot
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO
mysql -uroot -predhat
mysql -uroot -p #安全 比较推荐

[root@server ~]# mysql -uroot -predhat -e "SHOW DATABASES;"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+

#增删改查

1.查询

MariaDB [mysql]> desc user; #查看数据库中一个已经存在的表的结构
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field                  | Type                              | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host                   | char(60)                          | NO   | PRI |         |       |
| User                   | char(16)                          | NO   | PRI |         |       |
| Password               | char(41)                          | NO   |     |         |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Update_priv            | enum('N','Y')                     | NO   |     | N       |       |

#根据表的信息去查找相应的数据

MariaDB [mysql]> select Host,User,Password,Insert_priv from user;
+-----------+------+-------------------------------------------+-------------+
| Host      | User | Password                                  | Insert_priv |
+-----------+------+-------------------------------------------+-------------+
| localhost | root | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 | Y           |
| 127.0.0.1 | root | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 | Y           |
| ::1       | root | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 | Y           |
+-----------+------+-------------------------------------------+-------------+
3 rows in set (0.00 sec)

#提取更加相信的信息

MariaDB [mysql]> select Host,User,Password,Insert_priv from user where Host='localhost';
+-----------+------+-------------------------------------------+-------------+
| Host      | User | Password                                  | Insert_priv |
+-----------+------+-------------------------------------------+-------------+
| localhost | root | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 | Y           |
+-----------+------+-------------------------------------------+-------------+
1 row in set (0.00 sec)

MariaDB [mysql]> select Host,User,Password,Insert_priv from user where Host='::1';
+------+------+-------------------------------------------+-------------+
| Host | User | Password                                  | Insert_priv |
+------+------+-------------------------------------------+-------------+
| ::1  | root | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 | Y           |
+------+------+-------------------------------------------+-------------+
1 row in set (0.00 sec)

2.建立

MariaDB [(none)]> create database westos; #创建一个数据库
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| westos             | #查看是否创建成功
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]> use westos; #切换到新创建的数据库
Database changed
MariaDB [westos]> show tables; #查看数据库中的表 
Empty set (0.00 sec) #空的

#创建表
MariaDB [westos]> create table linux( #表名
    -> username varchar(10) not null,#表头信息
    -> password varchar(30) not null #表头信息
    -> );
Query OK, 0 rows affected (0.01 sec)

MariaDB [westos]> desc linux; #查看表结构
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(10) | NO   |     | NULL    |       |
| password | varchar(30) | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

#插入数据

MariaDB [westos]> insert into linux values('dd','123'); 
Query OK, 1 row affected (0.01 sec)

MariaDB [westos]> insert into linux values('pp',123);
Query OK, 1 row affected (0.01 sec)

#查询表中的信息

MariaDB [westos]> select * from linux;
+----------+----------+
| username | password |
+----------+----------+
| dd       | 123      |
| pp       | 123      |
+----------+----------+
2 rows in set (0.00 sec)

#按列查找信息

MariaDB [westos]> select username from linux;
+----------+
| username |
+----------+
| dd       |
| pp       |
+----------+
2 rows in set (0.00 sec)

#按行查找

MariaDB [westos]> select * from linux where username='pp';
+----------+----------+
| username | password |
+----------+----------+
| pp       | 123      |
+----------+----------+
1 row in set (0.00 sec)

MariaDB [westos]> select * from linux where username='dd';
+----------+----------+
| username | password |
+----------+----------+
| dd       | 123      |
+----------+----------+
1 row in set (0.00 sec)

MariaDB [westos]> select password from linux;
+----------+
| password |
+----------+
| 123      |
| 123      |
+----------+
2 rows in set (0.00 sec)

#按照条件查找

MariaDB [westos]> select username from linux where username='dd';
+----------+
| username |
+----------+
| dd       |
+----------+
1 row in set (0.00 sec)

MariaDB [westos]> select username from linux where username='pp';
+----------+
| username |
+----------+
| pp       |
+----------+
1 row in set (0.00 sec)

##更改

MariaDB [westos]> alter table linux add calss varchar(10); #添加表头
Query OK, 2 rows affected (0.01 sec)               
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [westos]> desc linux;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(10) | NO   |     | NULL    |       |
| password | varchar(30) | NO   |     | NULL    |       |
| calss    | varchar(10) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

MariaDB [westos]> alter table linux add age varchar(4) after password; #在password字段之后添加
Query OK, 2 rows affected (0.02 sec)               
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [westos]> desc linux;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(10) | NO   |     | NULL    |       |
| password | varchar(30) | NO   |     | NULL    |       |
| age      | varchar(4)  | YES  |     | NULL    |       |
| calss    | varchar(10) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

#更改表头的信息

MariaDB [westos]> alter table linux change age age varchar(5) not null;
Query OK, 2 rows affected, 2 warnings (0.01 sec)   
Records: 2  Duplicates: 0  Warnings: 2

MariaDB [westos]> use westos;
Database changed
MariaDB [westos]> desc linux;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(10) | NO   |     | NULL    |       |
| password | varchar(30) | NO   |     | NULL    |       |
| age      | varchar(5)  | NO   |     | NULL    |       |
| calss    | varchar(10) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)


MariaDB [westos]> alter table linux drop age;  #删除表头信息
Query OK, 2 rows affected (0.02 sec)               
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [westos]> desc linux;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(10) | NO   |     | NULL    |       |
| password | varchar(30) | NO   |     | NULL    |       |
| calss    | varchar(10) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

MariaDB [westos]> alter table linux rename users; #重命名表名
Query OK, 0 rows affected (0.00 sec)

MariaDB [westos]> desc linux;
ERROR 1146 (42S02): Table 'westos.linux' doesn't exist
MariaDB [westos]> desc users;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(10) | NO   |     | NULL    |       |
| password | varchar(30) | NO   |     | NULL    |       |
| calss    | varchar(10) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

MariaDB [westos]>

##删除

MariaDB [westos]> select * from users;
+----------+----------+-------+
| username | password | calss |
+----------+----------+-------+
| dd       | 123      | NULL  |
| pp       | 123      | NULL  |
+----------+----------+-------+
2 rows in set (0.00 sec)
MariaDB [westos]> delete from users where username='pp';
Query OK, 1 row affected (0.01 sec)

MariaDB [westos]> select * from users;
+----------+----------+-------+
| username | password | calss |
+----------+----------+-------+
| dd       | 123      | NULL  |
+----------+----------+-------+
1 row in set (0.00 sec)

MariaDB [westos]> show tables
    -> ;
+------------------+
| Tables_in_westos |
+------------------+
| users            |
+------------------+
1 row in set (0.00 sec)

MariaDB [westos]> drop table users;
Query OK, 0 rows affected (0.01 sec)

MariaDB [westos]> show tables
    -> ;
Empty set (0.00 sec)

MariaDB [westos]> drop database westos;
Query OK, 0 rows affected (0.00 sec)

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

MariaDB [(none)]>

##用户的授权
1.创建测试数据库和数据表

MariaDB [(none)]> create database userdata;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> use userdata;
Database changed
MariaDB [userdata]> create table usertb(
    -> username varchar(10) not null,
    -> password varchar(50) not null
    -> );
Query OK, 0 rows affected (0.01 sec)

MariaDB [userdata]> desc usertb;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(10) | NO   |     | NULL    |       |
| password | varchar(50) | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

MariaDB [userdata]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| userdata           |
+--------------------+
2.MariaDB [userdata]> create user westos@localhost identified by'dd';
Query OK, 0 rows affected (0.00 sec)

MariaDB [userdata]> exit
Bye
[root@server ~]# mysql -uwestos -pdd
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 20
Server version: 5.5.35-MariaDB MariaDB Server

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

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

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+

3.[root@server ~]# mysql -uroot -predhat
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 21
Server version: 5.5.35-MariaDB MariaDB Server

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

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

MariaDB [(none)]> grant select on userdata.* to westos@localhost; #授予westos用户select  userdata的权限
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show grants for westos@localhost;
+---------------------------------------------------------------------------------------------------------------+
| Grants for westos@localhost                                                                                   |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'westos'@'localhost' IDENTIFIED BY PASSWORD '*2F3B76898A59E6BB293D056656D934848B96A444' |
| GRANT SELECT ON `userdata`.* TO 'westos'@'localhost'                                                          |
+---------------------------------------------------------------------------------------------------------------+
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

[root@server ~]# mysql -uwestos -pdd
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 22
Server version: 5.5.35-MariaDB MariaDB Server

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

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

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| userdata           |
+--------------------+
2 rows in set (0.00 sec)

MariaDB [(none)]> use userdata;
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 [userdata]> show tables;
+--------------------+
| Tables_in_userdata |
+--------------------+
| usertb             |
+--------------------+
1 row in set (0.00 sec)

MariaDB [userdata]> select * from usertb;
Empty set (0.00 sec)

MariaDB [userdata]> desc usertb;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(10) | NO   |     | NULL    |       |
| password | varchar(50) | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

MariaDB [userdata]> insert into usertb values('dd','123');
ERROR 1142 (42000): INSERT command denied to user 'westos'@'localhost' for table 'usertb'
MariaDB [userdata]> quit
Bye

MariaDB [(none)]> revoke select on userdata.* from westos@localhost; #收回本地普通用用户的权限
Query OK, 0 rows affected (0.00 sec)

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

MariaDB [(none)]> exit


MariaDB [(none)]> 
MariaDB [(none)]> drop user westos@localhost; #删除用户

##备份和恢复

mysqldump -uroot -predhat --all-data #备份数据库
mysqldump -uroot -predhat --all-data>file #重定向到file中去
mysqldump -uroot -predhat --all-data --no-data #不备份表的内容 只备份表格

1.备份

[root@server ~]# mysqldump -uroot -predhat userdata >/mnt/userdata.sql
[root@server ~]# mysql -uroot -predhat -e "drop database userdata;"
[root@server ~]# mysql -uroot -predhat -e "show databases;"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+

2.恢复方式1

[root@server ~]# mysql -uroot -predhat </mnt/userdata.sql 
ERROR 1046 (3D000) at line 22: No database selected
[root@server ~]# vim /mnt/userdata.sql 
	 21 CREATE DATABASE userdata;
     22 USE userdata;

[root@server ~]# mysql -uroot -predhat </mnt/userdata.sql 
[root@server ~]# mysql -uroot -predhat
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 37
Server version: 5.5.35-MariaDB MariaDB Server

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

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

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

MariaDB [(none)]> select * from userdata.usertb
    -> ;
Empty set (0.00 sec)

恢复方式2

mysql -uroot -predhat -e "drop database userdata;"
mysql -uroot -predhat -e "create database userdata;"
vim /mnt/userdata.sql  #21 22行删除
mysql -uroot -predhat userdata </mnt/userdata.sql

#密码更改和破解

1.正常的更改密码(在知道之前的密码的情况下)

[root@server ~]# mysqladmin -uroot -predhat password dd
[root@server ~]# mysql -uroot -predhat
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@server ~]# mysql -uroot -pdd
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 49
Server version: 5.5.35-MariaDB MariaDB Server

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

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

MariaDB [(none)]>

破解root用户的密码
systemctl stop mariadb
mysqld_safe --skip-grant-tables &
'这个命令的执行原理是：默认数据库启动做初始化时是加载了授权表及认证表的，在执行此命令时先关掉数据库，再执行这个命令，这个命令因为在启动时添加了参数，就跳过了授权表和认证表（相当于linux系统改密码时打断系统引导的那个环境，登录后改了密码，关闭mysql_safe进程，再正常启动mysql，就会重新加载授权表和认证表，恢复正常）'
mysql #无密码登陆
update mysql.user set Password='redhat' where User='root';
select * from mysql.user; #密码是明文的形式 不安全
update mysql.user set Password=password('redhat') where User='root';
select * from mysql.user; #密码是加密的形式 推荐使用

[root@server ~]# ps aux | grep mysql
root      7226  0.0  0.1 113248  1608 pts/1    S    15:13   0:00 /bin/sh /usr/bin/mysqld_safe --skip-grant-tables
mysql     7381  0.0  9.0 859068 87696 pts/1    Sl   15:13   0:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --skip-grant-tables --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/run/mariadb/mariadb.pid --socket=/var/lib/mysqlmysql.sock
root      7423  0.0  0.1 112640   980 pts/1    R+   15:15   0:00 grep --color=auto mysql

kill -9 pid #kill掉mysql的进程

正常启动mariadb
systemctl start mariadb
systemctl status mariadb
mysql -uroot -predhat