Maridb数据库

MariaDB数据库：


一、MariaDB数据库的安装及其基本配置


1.[root@server101 ]# yum install mariadb mariadb-server             ##安装mariadb和maridb-server组件
2.[root@server101 ]# systemctl start mariadb                                 ##启动mariadb服务
3.[root@server101 ]# netstat -antlpe | grep mysql                           ##查看mariadb的监听端口
LISTEN     0      50                        *:3306                     *:*      users:(("mysqld",4125,14))
4.[root@server101]# vim /etc/my.cnf                                                ##修改mysql的配置文件
添加    此时只允许通过套接字文件进行本地连接,阻断所有来自网络的tcp/ip连接
5.改完之后重启mariadb服务
[root@server101]# systemctl start mariadb
现在我们可以再次查看mariadb的监听端口
[root@server101]# netstat -antlpe | grep mysql
可以发现mysql的端口没有开放


二、MariaDB数据库安全设置

[root@server101]# mysql_secure_installation


只有下面这个需要我们输入密码，其他的都直接回车

Set root password? [Y/n]

New password:

Re-enter new password:

Password updated successfully!Reloading privilege tables..

... Success!

设置过密码后，还可以用这个命令来修改root用户在数据库的密码


测试：[root@server101]# MySQL -uroot -p

三、数据库的基本操作

首先进入数据库：
[root@server101]# mysql -uroot -p
1.SHOW DATABASES;        显示现在所有的数据库
MariaDB [(none)]> SHOW DATABASES;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || wuwu               |+--------------------+4 rows in set (0.00 sec)


2.CREATE DATABASE database_name;    创建新的数据库

MariaDB [(none)]> CREATE DATABASE westos;

Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> SHOW DATABASES;

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

| Database           |

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

| information_schema |

| westos               |

| mysql              |

| performance_schema |

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

4 rows in set (0.00 sec)

3.USE database_name;    进入某个数据库

MariaDB [(none)]> USE mysql;

Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -A

Database changed

4.SHOW tables;        显示库里的表

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                      |

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

24 rows in set (0.00 sec)

5.CREATE TABLE table_name (name VARCHAR(20), sex CHAR(1));    创建表

MariaDB [mysql]> CREATE TABLE happy (name VARCHAR(20), sex CHAR(1));Query OK, 0 rows affected (0.46 sec)6.SELECT * FROM table_name;                             查看某一表中的所有列
7.INSERT INTO table_name VALUES ('wxh','M');    向某一表中插入以行，插入内容时括号中的
MariaDB [mysql]> SELECT * FROM happy;Empty set (0.00 sec)MariaDB [mysql]> INSERT INTO happy VALUES ('wxh','M');Query OK, 1 row affected (0.39 sec)MariaDB [mysql]> SELECT * FROM happy;+------+------+| name | sex  |+------+------+| wxh  | M    |+------+------+1 row in set (0.00 sec)8.UPDATE table_name SET attribute=value WHERE attribute > value;        更新表中信息
MariaDB [mysql]> UPDATE happy SET sex='man' WHERE name='wxh';Query OK, 1 row affected, 1 warning (0.44 sec)Rows matched: 1  Changed: 1  Warnings: 19.DELETE FROM table_name WHERE attribute = value;                              删除表中的某些行
MariaDB [mysql]> DELETE FROM happy WHERE name='wxh';Query OK, 1 row affected (0.37 sec)10.DROP TABLE table_name;
10.DROP TABLE table_name;                          删除表
MariaDB [mysql]> DROP TABLE happy;Query OK, 0 rows affected (0.03 sec)
11.DROP DATABASE database_name;            删除数据库
MariaDB [mysql]> DROP DATABASE haha;Query OK, 0 rows affected (0.00 sec)

12.DESCRIBE table_name;    查看表结构

MariaDB [wuwu]> DESCRIBE happy;+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| name  | varchar(20) | YES  |     | NULL    |       || age   | char(10)    | YES  |     | NULL    |       |+-------+-------------+------+-----+---------+-------+2 rows in set (0.01 sec)

13.ALTER TABLE table_name add sex CHAR(1)##添加字段age

MariaDB [wuwu]> ALTER TABLE happy add sex CHAR(1);Query OK, 0 rows affected (0.46 sec)               Records: 0  Duplicates: 0  Warnings: 0


还可以不进入数据库就可以对他进行操作：

#mysql -uroot -predhat -e "DROP DATABASES westos"

#mysql -uroot -predhat -e "SHOW DATABASES"

################# 基本操作整理 ################

创建：

create database westos;

use westos;

create table linux (

                    username varchar(50) not null,

                    password varchar(50) not null,

                    age varchar(4)   );

insert into linux values ( 'haoge' , '1234' , '21' );

查询：

show databases;

use mysql;

show tables;

desc user;

select * from user;

select * from user where HOST= ' 127.0.0.1 ' ;

修改：

alter table linux rename hahaha;

alter table linux add class varchar(50);

alter table linux add class varchar(50) after password

alter table linux drop class;

update linux set class= ' linux ' ;

update linux set class= ' web ' where username= ' hao ' ;

删除：

delete from linux where username= ‘ hao ’ and class= ‘ web ’ ；

drop table linux；

drop database westos；

四、数据库的用户和其访问权限

1.创建用户
MariaDB [(none)]> CREATE USER hao@localhost identified by 'redhat';

[root@server101]# mysql -uhao -p

Enter password:

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

2.用户授权

MariaDB [(none)]> SHOW GRANTS FOR hao@localhost;           ##查看用户权限

MariaDB [(none)]> GRANT SELECT on *.* to hao@localhost;       ##给hao授权，root用户才可以

3.重载授权表

MariaDB [(none)]>FLUSH PRIVILEGES；

4.撤销用户权限

MariaDB [(none)]>REVOKE DELETE，UPDATE，INSERT ON *.* from hao@localhost

五、数据库的备份与恢复
1.备份数据库：
[root@server101]# mysqldump -uroot -predhat --all-database              ##备份数据库里的所有内容
[root@server101]# mysqldump -uroot -predhat --no-data --all-database         ##仅仅备份数据库结构
[root@server101]# mysqldump -uroot -predhat mysql > /mnt/mysql.sql           ##备份某一个库到一个指定目录下
2.恢复数据库：
# mysqladmin -uroot -predhat create db2# mysql -uroot -predhat db2 < westos.dump
[root@server101]# mysqladmin -uroot -predhat create wuwu                 ##恢复数据库
[root@server101]# mysqladmin -uroot -predhat password lee               ##修改用户密码

用户密码忘记怎么办？

1.[root@server101]# systemctl stop mariadb.service

2.[root@server101]# mysqld_safe --skip-grants-tables &

进入数据库更新密码，更新完之后 kill -9 数据库进程

然后重启数据库，就可以使用你新设定的密码进入了。

六.   数据库图形管理工具