RHEL8系统学习打卡#day19#

第18章 使用MariaDB数据库管理系统

18.1 安装

dnf install -y mariadb mariadb-server    安装
systemctl start  mariadb                 启动
systemctl enable mariadb                 加入启动项

18.2 初始化

[root@localhost ~]# mysql_secure_installation 

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

Set root password? [Y/n] y
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] y
 ... Success!

By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

设置防火墙

[root@linuxprobe ~]# firewall-cmd --permanent --add-service=mysql
[root@linuxprobe ~]# firewall-cmd --reload

18.3 基本使用

[root@localhost ~]# mysql -u root -p   //登陆
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 29
Server version: 10.3.11-MariaDB MariaDB Server

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

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

MariaDB [(none)]> help       //查看帮助

General information about MariaDB can be found at
http://mariadb.org

List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
?         (\?) Synonym for `help'.
clear     (\c) Clear the current input statement.
connect   (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
edit      (\e) Edit command with $EDITOR.
ego       (\G) Send command to mysql server, display result vertically.
exit      (\q) Exit mysql. Same as quit.
go        (\g) Send command to mysql server.
help      (\h) Display this help.
nopager   (\n) Disable pager, print to stdout.
notee     (\t) Don't write into outfile.
pager     (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print     (\p) Print current command.
prompt    (\R) Change your mysql prompt.
quit      (\q) Quit mysql.
rehash    (\#) Rebuild completion hash.
source    (\.) Execute an SQL script file. Takes a file name as an argument.
status    (\s) Get status information from the server.
system    (\!) Execute a system shell command.
tee       (\T) Set outfile [to_outfile]. Append everything into given outfile.
use       (\u) Use another database. Takes database name as argument.
charset   (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings  (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.

For server side help, type 'help contents'

MariaDB [(none)]> SHOW databases;    //查看当前有哪些数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.000 sec)


MariaDB [(none)]> SET password = PASSWORD('password123');    //修改密码
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> CREATE USER zhangsan@localhost IDENTIFIED BY 'zhangsan';    //创建数据库用户
Query OK, 0 rows affected (0.001 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]> SELECT HOST,USER,PASSWORD FROM user WHERE USER="zhangsan";
+-----------+----------+-------------------------------------------+
| HOST      | USER     | PASSWORD                                  |
+-----------+----------+-------------------------------------------+
| localhost | zhangsan | *D550CDE8CF0F249C0520BF8CFC424D082D87FEF9 |
+-----------+----------+-------------------------------------------+
1 row in set (0.000 sec)

MariaDB [mysql]> GRANT SELECT,UPDATE,DELETE,INSERT ON mysql.user TO zhangsan@localhost;    //给普通用户设置权限
Query OK, 0 rows affected (0.000 sec)


MariaDB [mysql]> SHOW GRANTS FOR zhangsan@localhost;  //查看设置的权限
+-----------------------------------------------------------------------------------------------------------------+
| Grants for zhangsan@localhost                                                                                   |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'zhangsan'@'localhost' IDENTIFIED BY PASSWORD '*D550CDE8CF0F249C0520BF8CFC424D082D87FEF9' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `mysql`.`user` TO 'zhangsan'@'localhost'                                |
+-----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

MariaDB [mysql]> REVOKE SELECT,UPDATE,DELETE,INSERT ON mysql.user FROM zhangsan@localhost;   //删除权限
Query OK, 0 rows affected (0.001 sec)

MariaDB [mysql]> SHOW GRANTS FOR zhangsan@localhost;
+-----------------------------------------------------------------------------------------------------------------+
| Grants for zhangsan@localhost                                                                                   |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'zhangsan'@'localhost' IDENTIFIED BY PASSWORD '*D550CDE8CF0F249C0520BF8CFC424D082D87FEF9' |
+-----------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [mysql]> DROP user zhangsan@localhost;  //删除用户
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> CREATE DATABASE shujuku;   //创建数据库
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> SHOW databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| shujuku            |
+--------------------+
4 rows in set (0.000 sec)

MariaDB [(none)]> use shujuku；
Database changed

MariaDB [shujuku]> CREATE TABLE class(name char(10),ages int,height int);    //创建字段
Query OK, 0 rows affected (0.006 sec)

MariaDB [shujuku]> DESCRIBE class;
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| name   | char(10) | YES  |     | NULL    |       |
| ages   | int(11)  | YES  |     | NULL    |       |
| height | int(11)  | YES  |     | NULL    |       |
+--------+----------+------+-----+---------+-------+
3 rows in set (0.001 sec)

MariaDB [shujuku]> INSERT INTO class(name,ages,height) VALUES('Tom',15,168);    //增加数据
Query OK, 1 row affected (0.001 sec)

MariaDB [shujuku]> SELECT * from class;   //查看
+------+------+--------+
| name | ages | height |
+------+------+--------+
| Tom  |   15 |    168 |
+------+------+--------+
1 row in set (0.000 sec)

MariaDB [shujuku]> UPDATE class SET ages=17;   //修改
Query OK, 1 row affected (0.003 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [shujuku]> SELECT * from class;
+------+------+--------+
| name | ages | height |
+------+------+--------+
| Tom  |   17 |    168 |
+------+------+--------+
1 row in set (0.000 sec)

MariaDB [shujuku]> INSERT INTO class(name,ages,height) VALUES('Bob',16,180);
Query OK, 1 row affected (0.002 sec)

MariaDB [shujuku]> INSERT INTO class(name,ages,height) VALUES('Lili',17,156);
Query OK, 1 row affected (0.002 sec)

MariaDB [shujuku]> SELECT * from class;
+------+------+--------+
| name | ages | height |
+------+------+--------+
| Tom  |   17 |    168 |
| Bob  |   16 |    180 |
| Lili |   17 |    156 |
+------+------+--------+
3 rows in set (0.000 sec)

MariaDB [shujuku]> UPDATE class SET ages=18 where name='Tom';  //利用where指定修改某一条数据
Query OK, 1 row affected (0.004 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [shujuku]> SELECT * from class;
+------+------+--------+
| name | ages | height |
+------+------+--------+
| Tom  |   18 |    168 |
| Bob  |   16 |    180 |
| Lili |   17 |    156 |
+------+------+--------+
3 rows in set (0.000 sec)

MariaDB [shujuku]> SELECT * FROM class WHERE  height>160;
+------+------+--------+
| name | ages | height |
+------+------+--------+
| Tom  |   18 |    168 |
| Bob  |   16 |    180 |
+------+------+--------+
2 rows in set (0.000 sec)


MariaDB [shujuku]> SELECT * FROM class WHERE  ages=16 AND height=180;
+------+------+--------+
| name | ages | height |
+------+------+--------+
| Bob  |   16 |    180 |
+------+------+--------+
1 row in set (0.000 sec)

MariaDB [shujuku]> DELETE FROM class;   //删除
Query OK, 3 rows affected (0.001 sec)

MariaDB [shujuku]> SELECT * FROM class;
Empty set (0.000 sec)

18.4数据库的备份与恢复
1.创建备份

[root@localhost ~]# mysqldump -u root -p shujuku > /root/shujukuDB.dump
Enter password:   输入管理员的数据库密码

2.删除

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 33
Server version: 10.3.11-MariaDB MariaDB Server

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

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

MariaDB [(none)]> DROP DATABASE shujuku;
Query OK, 1 row affected (0.004 sec)

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


MariaDB [(none)]> CREATE DATABASE shujuku;
Query OK, 1 row affected (0.000 sec)

3.恢复备份

[root@localhost ~]# mysql -u root -p shujuku < /root/shujukuDB.dump
Enter password: 

4.验证查看

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 35
Server version: 10.3.11-MariaDB MariaDB Server

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

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

MariaDB [(none)]> use shujuku
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 [shujuku]> SHOW tables;
+-------------------+
| Tables_in_shujuku |
+-------------------+
| class             |
+-------------------+
1 row in set (0.000 sec)

MariaDB [shujuku]> describe class;
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| name   | char(10) | YES  |     | NULL    |       |
| ages   | int(11)  | YES  |     | NULL    |       |
| height | int(11)  | YES  |     | NULL    |       |
+--------+----------+------+-----+---------+-------+
3 rows in set (0.001 sec)

MariaDB [shujuku]> SELECT * FROM class;
+------+------+--------+
| name | ages | height |
+------+------+--------+
| Tom  |   18 |    168 |
| Bob  |   16 |    180 |
| Lili |   17 |    156 |
+------+------+--------+
3 rows in set (0.000 sec)