MariaDB的使用以及备份恢复

最新推荐文章于 2024-03-25 19:44:21 发布

匡夆

最新推荐文章于 2024-03-25 19:44:21 发布

阅读量870

点赞数 1

分类专栏： # Linux之mysql

本文链接：https://blog.csdn.net/weixin_46108954/article/details/106535830

版权

Linux之mysql 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

文章目录

- 使用 MariaDB 数据库管理系统

使用 MariaDB 数据库管理系统

安装

[root@mariadb ~]# yum install -y mariadb mariadb-server
[root@mariadb ~]# systemctl start mariadb
[root@mariadb ~]# systemctl enable mariadb
Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.

在确认 MariaDB 数据库软件程序安装完毕并成功启动后请不要立即使用。

为了确保数据库的安全性和正常运转，需要先对数据库程序进行初始化操作。

通常为五步：

1.设置 root 管理员在数据库中的密码值（注意，该密码并非 root 管理员在系统中的密码，这里的密码值默认应该为空，可直接按回车键）。

2.设置 root 管理员在数据库中的专有密码。

3.随后删除匿名账户，并使用 root 管理员从远程登录数据库，以确保数据库上运行的业务的安全性。

4.删除默认的测试数据库，取消测试数据库的一系列访问权限。

5.刷新授权列表，让初始化的设定立即生效。

[root@mariadb ~]# 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: 输入要为root管理员设置的数据库密码
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（禁止root管理员从远程登录）
 ... 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（删除test数据库并取消对它的访问权限）
 - 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!

数据库服务程序默认会占用 3306 端口，在防火墙策略中服务名称统一叫作 mysql：

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

登录数据库

[root@mariadb ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 5.5.65-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)]>

查看数据库表

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

创建用户

格式

CREATE USER 用户名@主机名 IDENTIFIED BY ‘密码’;

MariaDB [mysql]> CREATE USER alice@localhost IDENTIFIED BY '123';

查询

MariaDB [mysql]> use mysql
Database changed
MariaDB [mysql]> SELECT HOST,USER,PASSWORD FROM user WHERE USER="alice";
+-----------+-------+-------------------------------------------+
| HOST      | USER  | PASSWORD                                  |
+-----------+-------+-------------------------------------------+
| localhost | alice | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+-----------+-------+-------------------------------------------+
1 row in set (0.00 sec)

注：此时，用户alice仅仅是一个普通账户，没有数据库的任何操作权限。

GRANT 命令的常见格式以及解释

命令	作用
GRANT 权限ON 数据库.表单名称TO 账户名@主机名	对某个特定数据库中的特定表单给予授权
GRANT 权限 ON 数据库.*TO 账户名@主机名	对某个特定数据库中的所有表单给予授权
GRANT 权限 ON * .* TO 账户名@主机名	对所有数据库及所有表单给予授权
GRANT 权限1,权限2 ON 数据库.*TO 账户名@主机名	对某个数据库中的所有表单给予多个授权
GRANT ALL PRIVILEGES ON .TO 账户名@主机名	对所有数据库及所有表单给予全部授权（需谨慎操作）

账户的授权工作是需要数据库管理员来执行的。

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 GRANTS FOR alice@localhost;
+--------------------------------------------------------------------------------------------------------------+
| Grants for alice@localhost                                                                                   |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'alice'@'localhost' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
+--------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [mysql]> GRANT SELECT,UPDATE,DELETE,INSERT ON mysql.user TO alice@localhost;
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> SHOW GRANTS FOR alice@localhost;
+--------------------------------------------------------------------------------------------------------------+
| Grants for alice@localhost                                                                                   |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'alice'@'localhost' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `mysql`.`user` TO 'alice'@'localhost'                                |
+--------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

这时登录到alice用户可以看到user 表了，也可以对其增删改查。

删除权限

MariaDB [mysql]> REVOKE SELECT,UPDATE,DELETE,INSERT ON mysql.user FROM alice@localhost;
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> SHOW GRANTS FOR alice@localhost;
+--------------------------------------------------------------------------------------------------------------+
| Grants for alice@localhost                                                                                   |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'alice'@'localhost' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
+-----------------------------------------------------------------------------------------------

创建数据库与表单

用于创建数据库的命令以及作用

命令	作用
CREATE DATABASE 数据库名称	创建新的数据库
DESCRIBE 表单名称	描述表单
UPDATE 表单名称 SET attribute=新值 WHERE attribute>原始值	更新表单中的数据
USE 数据库名称	指定使用的数据库
SHOW databases	显示当前已有的数据库
SHOW tables	显示当前数据库中的表单
SELECT * FROM 表单名称	从表单中选中某个记录值
DELETE FROM 表单名 WHERE attribute=值	从表单中删除某个记录值

现在尝试创建一个名为test2020的数据库，然后再查看数据库列表，此时就能看到它了

MariaDB [mysql]> CREATE DATABASE test2020;
Query OK, 1 row affected (0.00 sec)

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

要想创建数据表单，需要先切换到某个指定的数据库中。

创建表单book

MariaDB [mysql]> use test2020;
Database changed
MariaDB [test2020]> CREATE TABLE book (name char(15),price int, pages int);
Query OK, 0 rows affected (0.00 sec)

MariaDB [test2020]> DESCRIBE book;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(15) | YES  |     | NULL    |       |
| price | int(11)  | YES  |     | NULL    |       |
| pages | int(11)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.01 sec)

管理表单及数据

插入一条图书信息，其中书名为 linux，价格和页数分别是 66 元和 666 页。

MariaDB [test2020]> INSERT INTO book (name,price,pages) VALUES ('Linux','66','666');
Query OK, 1 row affected (0.10 sec)

MariaDB [test2020]> SELECT * FROM book;
+-------+-------+-------+
| name  | price | pages |
+-------+-------+-------+
| Linux |    66 |   666 |
+-------+-------+-------+
1 row in set (0.00 sec)

更新将书名为 linux，价格改为 66.6 元

MariaDB [test2020]> UPDATE book SET price=66.6 WHERE name="Linux";
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
MariaDB [test2020]> SELECT price FROM book WHERE name="Linux";
+-------+
| price |
+-------+
|    67 |
+-------+
1 row in set (0.00 sec)

这里数据库不支持小数啊。。。

删除操作

MariaDB [test2020]> DELETE FROM book;
Query OK, 1 row affected (0.09 sec)

MariaDB [test2020]> SELECT *FROM book;
Empty set (0.00 sec)

准备数据

MariaDB [test2020]> SELECT *FROM book;
+---------+-------+-------+
| name    | price | pages |
+---------+-------+-------+
| Linux   |    66 |   666 |
| Windows |    77 |   777 |
| Centos  |    88 |   888 |
| Ubuntu  |    99 |   999 |
+---------+-------+-------+
4 rows in set (0.00 sec)

where 命令中使用的参数以及作用

参数	作用
=	相等
<>或!=	不相等
>	大于
<	小于
>=	大于或等于
<=	小于或等于
BETWEEN	在某个范围内
LIKE	搜索一个例子
IN	在列中搜索多个值

在 book 表单中查找出价格大于 66 元的图书；价格不等于88元的图书；在66到88之间的图书

MariaDB [test2020]> SELECT * FROM book WHERE price>66;
+---------+-------+-------+
| name    | price | pages |
+---------+-------+-------+
| Windows |    77 |   777 |
| Centos  |    88 |   888 |
| Ubuntu  |    99 |   999 |
+---------+-------+-------+
3 rows in set (0.00 sec)

MariaDB [test2020]> SELECT * FROM book WHERE price!=88;
+---------+-------+-------+
| name    | price | pages |
+---------+-------+-------+
| Linux   |    66 |   666 |
| Windows |    77 |   777 |
| Ubuntu  |    99 |   999 |
+---------+-------+-------+
3 rows in set (0.00 sec)

MariaDB [test2020]> SELECT * FROM book WHERE price BETWEEN 66 AND 88;
+---------+-------+-------+
| name    | price | pages |
+---------+-------+-------+
| Linux   |    66 |   666 |
| Windows |    77 |   777 |
| Centos  |    88 |   888 |
+---------+-------+-------+
3 rows in set (0.00 sec)

数据库的备份及恢复

mysqldump 命令用于备份数据库数据，格式为“mysqldump [参数] [数据库名称]”

[root@mariadb ~]# mysqldump -u root -p test2020 > /root/test2020DB.dump 
Enter password: 
[root@mariadb ~]# ls
test2020DB.dump

删除数据库test2020

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

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

MariaDB [(none)]>

要想恢复数据，先创建数据库

MariaDB [(none)]> CREATE DATABASE test2020;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> exit
Bye

导入刚才的备份

[root@mariadb ~]# mysql -u root -p test2020 < /root/test2020DB.dump 
Enter password: 
[root@mariadb ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 17
Server version: 5.5.65-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)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test2020           |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]> use test2020;
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 [test2020]> SHOW tables;
+--------------------+
| Tables_in_test2020 |
+--------------------+
| book               |
+--------------------+
1 row in set (0.00 sec)

MariaDB [test2020]> DESCRIBE book;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(15) | YES  |     | NULL    |       |
| price | int(11)  | YES  |     | NULL    |       |
| pages | int(11)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

MariaDB [test2020]> SELECT * FROM book;
+---------+-------+-------+
| name    | price | pages |
+---------+-------+-------+
| Linux   |    66 |   666 |
| Windows |    77 |   777 |
| Centos  |    88 |   888 |
| Ubuntu  |    99 |   999 |
+---------+-------+-------+
4 rows in set (0.00 sec)

数据恢复完成