MariaDB数据库管理系统部署
- MariaDB简介
- MariaDB的安装和部署
- 安装主程序和服务端程序
- 数据库程序初始化
- 设置防火墙并初始访问
- 登录数据库管理系统
- 修改账户密码
- 数据库账户管理及授权
- 创建数据库管理账户
- 账户授权
- **查看当下数据库管理系统中存在多少数据库。**
- **使用新创建的chrisgoudan账号,查看chrisgoudan能查看多少数据库。**
- **账户查询示范**
- **为chrisgoudan用户赋权**
- **切换到chrisgoudan用户查看数据库**
- **查看数据库用户的权限**
- 移除用户权限
- 创建数据库与表单
- 实验
- 管理表单及数据
- 增加表单数据
- 删除一条数据
- 查询一条数据
- 修改一条数据
- 数据库备份
- 数据库备份
- 数据库恢复
- 总结
MariaDB简介
Oracle公司收购sun公司，将MySQL由开源软件转变为闭源软件。MySQL项目创始者重新研发MariaDB的全新数据库管理系统。MariaDB完全兼容MySQL。
数据库： 按照某些特定结构存储数据资料的数据仓库数据库管理系统：一种能够对数据库中存放的数据进行建立、修改、删除、查找、维护等操作的软件程序MariaDB优势： 对微妙级别的支持、线程池、子查询优化、进程报告
MariaDB的安装和部署
安装主程序和服务端程序
[root@Mail ~]# yum install mariadb mariadb-server
.....................省略输出.....................................数据库程序初始化
- 设置root管理员再数据库中的密码值（默认为空、可直接回车）
- 设置root管理员在数据库中的专有密码
- 删除匿名账户，并使用root管理员从远程登录数据库，确保数据库上运行的业务安全
- 删除默认的测试数据库，取消测试数据库的一系列访问权限
- 刷新授权列表，让初始化的设定立即生效
[root@Mail ~]# 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 <<<<<设置数据库root账户密码
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 <<<<<<<禁止 root用户远程登录，如果在站库分离的网站中需要root用户远程登录则设置为no
... 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!设置防火墙并初始访问
数据库服务程序默认占用3306端口，在防火墙策略中服务名称同一称为mysql。
[root@Mail ~]# firewall-cmd --permanent --zone=internal --add-service=mysql
success
[root@Mail ~]# netstat -pan | grep 3306
tcp6 0 0 :::3306 :::* LISTEN 6813/mysqld
[root@Mail ~]# firewall-cmd --reload <<<<<刷新防火墙
success在添加防火墙服务后，无需再添加端口号。注意添加permanent参数，并且刷新防火墙。
登录数据库管理系统
-
-u参数：指定用户身份 -
-p参数：验证用户再数据库中的密码值
[root@Mail ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 17
Server version: 10.3.17-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)]>修改账户密码
- 在初次登录数据库系统后，必须要修改账户密码。否则在下一次登录时会出现访问失败。数据库的账户管理策略和linux中的账户管理策略差不多。
- 在使用数据库命令时，在命令后要使用分号才能正常工作
MariaDB [(none)]> SET password =PASSWORD('chrisgoudan'); <<<< 分号， 重新设置密码
Query OK, 0 rows affected (0.032 sec)
MariaDB [(none)]> exit <<<<退出
Bye
[root@Mail ~]# mysql -u root -p <<<<再次登录验证
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 18
Server version: 10.3.17-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)]>数据库账户管理及授权
在实际生产环境中其他用户需要使用数据库管理系统。为确保数据库系统的安全性，为每位用户创建专用的账户同时分配适当权限。
创建数据库管理账户
命令： CREATE USER 用户名@主机名 IDENTIFIED BY '密码';
用户名@主机名 ： 当用户需要通过其他系统远程登录数据库管理系统时，就需要使用到主机名来以示区别。表明当下用户来自哪个主机上的哪个用户。
MariaDB [(none)]> CREATE USER chrisgoudan@localhost IDENTIFIED BY 'chrisgoudan';
Query OK, 0 rows affected (0.057 sec)数据库用户账户信息在mysql数据库中，使用use mysql 命令进入到mysql数据库中。
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='chrisgoudan';
+-----------+-------------+-------------------------------------------+
| HOST | USER | PASSWORD |
+-----------+-------------+-------------------------------------------+
| localhost | chrisgoudan | *FEF7A5F55F5B71BBAEF6713AA5CFDB7A00F3EA60 |
+-----------+-------------+-------------------------------------------+
1 row in set (0.002 sec)此时创建的账户对数据库还没有任何的操作权限。
账户授权
GRANT命令：
------------------------------------------------GRANT命令常见格式---------------------------------------------------------
命令 | 作用 |
GRANT 权限 ON 数据库.表单名称 TO 账户名@主机名 | 对某个特定数据库中的特定表单给予授权 |
GRANT 权限 ON 数据库.* TO 账户名@主机名 | 对某个数据库中的所有表单给予授权 |
GRANT 权限 ON *.* TO 账户名@主机名 | 对所有数据库中的所有表单进行授权 |
GRANT 权限1，权限2 ON 数据库.* TO 账户名@主机名 | 对某个数据库中的所有表单给予多个授权 |
GRANT ALL PRIVILEGES ON *.* TO 账户名@主机名 | 对所有数据库所有表单给予全部授权 |
查看当下数据库管理系统中存在多少数据库。
[root@Mail ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 30
Server version: 10.3.17-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 |
+--------------------+
3 rows in set (0.000 sec)使用新创建的chrisgoudan账号,查看chrisgoudan能查看多少数据库。
[root@Mail ~]# mysql -u chrisgoudan -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 31
Server version: 10.3.17-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 |
+--------------------+
1 row in set (0.001 sec)账户查询示范
[root@Mail ~]# 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.17-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 |
+--------------------+
3 rows in set (0.000 sec)
MariaDB [(none)]> use mysql <<<进入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; <<<<显示mysql数据库中的所有表单
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| column_stats |
| columns_priv |
| db |
| event |
| func |
| general_log |
| gtid_slave_pos |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| index_stats |
| innodb_index_stats |
| innodb_table_stats |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| roles_mapping |
| servers |
| slow_log |
| table_stats |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| transaction_registry |
| user |
+---------------------------+
31 rows in set (0.000 sec)
MariaDB [mysql]> show columns from user; <<<<<<显示mysql数据库中user表单中的所有属性列
+------------------------+-----------------------------------+------+-----+----------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+----------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(80) | 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 | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_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 | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_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 | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| Delete_history_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) | NO | | 0 | |
| plugin | char(64) | NO | | | |
| authentication_string | text | NO | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
| is_role | enum('N','Y') | NO | | N | |
| default_role | char(80) | NO | | | |
| max_statement_time | decimal(12,6) | NO | | 0.000000 | |
+------------------------+-----------------------------------+------+-----+----------+-------+
47 rows in set (0.001 sec)为chrisgoudan用户赋权
[root@Mail ~]# 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.17-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)]> GRANT SELECT,UPDATE,DELETE,INSERT ON mysql.user TO chrisgoudan@localhost
-> ;
Query OK, 0 rows affected (0.005 sec)切换到chrisgoudan用户查看数据库
[root@Mail ~]# mysql -u chrisgoudan -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 36
Server version: 10.3.17-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 |
+--------------------+
2 rows in set (0.000 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 |
+-----------------+
| user |
+-----------------+
1 row in set (0.000 sec)
MariaDB [mysql]>因为赋予了chrisgoudan用户增删查改的权限，所以mysql数据库对Chrisgoudan用户可见，同时可以对mysql数据库中的user表单进行增删查改操作。但是对于其他表单没有操作权限。
查看数据库用户的权限
[root@Mail ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 37
Server version: 10.3.17-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 GRANTS FOR chrisgoudan@localhost;
+--------------------------------------------------------------------------------------------------------------------+
| Grants for chrisgoudan@localhost |
+--------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'chrisgoudan'@'localhost' IDENTIFIED BY PASSWORD '*FEF7A5F55F5B71BBAEF6713AA5CFDB7A00F3EA60' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `mysql`.`user` TO 'chrisgoudan'@'localhost' |
+--------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.001 sec)移除用户权限
MariaDB [(none)]> REVOKE SELECT,UPDATE,DELETE,INSERT ON mysql.user FROM chrisgoudan@localhost;
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> SHOW GRANTS FOR chrisgoudan@localhost;
+--------------------------------------------------------------------------------------------------------------------+
| Grants for chrisgoudan@localhost |
+--------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'chrisgoudan'@'localhost' IDENTIFIED BY PASSWORD '*FEF7A5F55F5B71BBAEF6713AA5CFDB7A00F3EA60' |
+--------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)创建数据库与表单
数据库管理系统中，一个数据库可以存放多个数据表单。可以根据需求自定义数据库表结构，在其中合理地存放数据，以便后期轻松维护和修改。
--------------------------------------------创建数据库命令以及作用-----------------------------------------------------------
命令 | 作用 |
CREATE DATABASE 数据库名称 | 创建数据库 |
DESCRIBE 表单名称 | 描述表单 |
UPDATE 表单名称 SET attribute=新值 WHERE attribute > 原始值 | 更行表单中的数据 |
USE 数据库名称 | 指定使用的数据库 |
SHOW databases | 显示当前已有的数据库 |
SHOW tables | 显示当前已有数据库的表单 |
SELECT * FROM 表单名称 | 从表单中选中某个记录值 |
DELETE FROM 表单名 WHERE attribute=值 | 从表单中删除某个记录值 |
实验
创建一个学生数据库，并在该数据库中添加一个学生表单。
[root@Mail ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 41
Server version: 10.3.17-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 |
+--------------------+
3 rows in set (0.000 sec)
MariaDB [(none)]> create database student; <<<<<创建学生数据库
Query OK, 1 row affected (0.001 sec)
MariaDB [(none)]> show databases; <<<<<<<<查看是否创建成功
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| student |
+--------------------+
4 rows in set (0.000 sec)
MariaDB [(none)]> use student; <<<<<<<使用创建的学生数据库
Database changed
MariaDB [student]> create table student (sno int,sname char(15),age int); <<<<<<<<创建学生数据库表单
Query OK, 0 rows affected (0.049 sec)
MariaDB [student]> show tables; <<<<<<<显示表单
+-------------------+
| Tables_in_student |
+-------------------+
| student |
+-------------------+
1 row in set (0.003 sec)
MariaDB [student]> DESCRIBE student; <<<<<<描述学生表单
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| sno | int(11) | YES | | NULL | |
| sname | char(15) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.004 sec)
MariaDB [student]>管理表单及数据
表单增删查改
增加表单数据
insert
MariaDB [student]> INSERT INTO student(sno,sname,age) VALUES('1','chrisgoudan','58'); <<<增加一条指定数据
Query OK, 1 row affected (0.010 sec)
MariaDB [student]> insert into student(sno,sname,age) values('2','johnerzhu','66');
Query OK, 1 row affected (0.003 sec)
MariaDB [student]> insert into student(sno,sname,age) values('3','pauljianguo','250');
Query OK, 1 row affected (0.001 sec)
MariaDB [student]> select * from student; <<<<<查询表单中的所有数据
+------+-------------+------+
| sno | sname | age |
+------+-------------+------+
| 1 | chrisgoudan | 58 |
| 2 | johnerzhu | 66 |
| 3 | pauljianguo | 250 |
+------+-------------+------+
3 rows in set (0.004 sec)
MariaDB [student]> select age from student where sname='pauljianguo'; <<<<查询指定用户的年龄
+------+
| age |
+------+
| 250 |
+------+
1 row in set (0.002 sec)删除一条数据
delete
MariaDB [student]> delete from student where sno='3'; <<<<删除pauljianguo的一行数据
Query OK, 1 row affected (0.004 sec)
MariaDB [student]> select * from student; <<<<<查看表单中的所有数据
+------+-------------+------+
| sno | sname | age |
+------+-------------+------+
| 1 | chrisgoudan | 58 |
| 2 | johnerzhu | 66 |
+------+-------------+------+
2 rows in set (0.000 sec)
MariaDB [student]>查询一条数据
select
MariaDB [student]> select * from student; <<<<查询指定表单中所有数据
+------+-------------+------+
| sno | sname | age |
+------+-------------+------+
| 1 | chrisgoudan | 58 |
| 2 | johnerzhu | 66 |
+------+-------------+------+
2 rows in set (0.000 sec)
MariaDB [student]> select * from student where sno='1'; <<<<<<查询指定用户的所有数据
+------+-------------+------+
| sno | sname | age |
+------+-------------+------+
| 1 | chrisgoudan | 58 |
+------+-------------+------+
1 row in set (0.004 sec)
MariaDB [student]> select age from student; <<<<<<查询指定列的所有数据
+------+
| age |
+------+
| 58 |
| 66 |
+------+
2 rows in set (0.001 sec)
MariaDB [student]> select age from student where sno='2'; <<<<<<<查询某个用户的指定数据
+------+
| age |
+------+
| 66 |
+------+
1 row in set (0.000 sec)
MariaDB [student]>修改一条数据
update
MariaDB [student]> select * from student;
+------+-------------+------+
| sno | sname | age |
+------+-------------+------+
| 1 | chrisgoudan | 58 |
| 2 | johnerzhu | 66 |
+------+-------------+------+
2 rows in set (0.001 sec)
MariaDB [student]> update student set age='123' where sno='2'; <<<<<<修改student表单中某个用户的指定数据值
Query OK, 1 row affected (0.011 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [student]> select * from student;
+------+-------------+------+
| sno | sname | age |
+------+-------------+------+
| 1 | chrisgoudan | 58 |
| 2 | johnerzhu | 123 |
+------+-------------+------+
2 rows in set (0.000 sec)--------------------------------------------------------where命令参数及作用--------------------------------------------------
参数 | 作用 |
= | 等于 |
!= | 不等于 |
> | 大于 |
< | 小于 |
>= | 大于等于 |
<= | 小于等于 |
between | 在某个范围内 |
like | 搜索一个例子 |
in | 在列中搜索多个值 |
数据库备份
mysqldump 命令： 用于备份数据库数据
格式： mysqldump [参数] [数据库名称]
-
-u 参数：用于定义登录数据库的账户名称 -
-p 参数：代表密码提示符
数据库备份
查看当下数据库中数据
MariaDB [(none)]> use student;
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 [student]> show tables;
+-------------------+
| Tables_in_student |
+-------------------+
| student |
+-------------------+
1 row in set (0.000 sec)
MariaDB [student]> select * from student;
+------+-------------+------+
| sno | sname | age |
+------+-------------+------+
| 1 | chrisgoudan | 58 |
| 2 | johnerzhu | 123 |
+------+-------------+------+
2 rows in set (0.007 sec)备份数据库到指定文件夹
[root@Mail ~]# mysqldump -u root -p student > /home/DBback_up/student.dump; <<<<<备份数据库到指定文件夹
Enter password: <<<<<<输入root账户密码
[root@Mail ~]# cd /home/DBback_up/
[root@Mail DBback_up]# ll
总用量 4
-rw-r--r--. 1 root root 1924 8月 31 15:44 student.dump <<<<<备份成功
MariaDB [(none)]> show databases; <<<<<<<显示当前存在的数据库文件
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| student |
+--------------------+
4 rows in set (0.001 sec)
MariaDB [(none)]> drop database student; <<<<<删除指定数据库
Query OK, 1 row affected (0.016 sec)
MariaDB [(none)]> show databases; <<<<<<<显示当前存在的数据库
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.000 sec)数据库恢复
MariaDB [(none)]> create database student; <<<<,备份前应先创建该数据库
Query OK, 1 row affected (0.002 sec)
MariaDB [(none)]> show databases; <<<<显示当前的数据库
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| student |
+--------------------+
4 rows in set (0.000 sec)
MariaDB [(none)]> use student; <<<<<<进入student数据库
Database changed
MariaDB [student]> show tables; <<<<<查看当前数据库中存在的表单
Empty set (0.000 sec) <<<<数据库中的表单为空
[root@Mail ~]# mysql -u root -p student < /home/DBback_up/student.dump <<<<<恢复数据库数据
Enter password: <<<<输入管理员账户密码
[root@Mail ~]# mysql -u root -p <<<<进入数据库管理系统
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 48
Server version: 10.3.17-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 student;
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 [student]> show tables;
+-------------------+
| Tables_in_student |
+-------------------+
| student |
+-------------------+
1 row in set (0.000 sec)
MariaDB [student]> select * from student; <<<<数据恢复成功
+------+-------------+------+
| sno | sname | age |
+------+-------------+------+
| 1 | chrisgoudan | 58 |
| 2 | johnerzhu | 123 |
+------+-------------+------+
2 rows in set (0.000 sec总结
数据库管理系统的部署操作比较简单，关键在于数据库管理系统的操作与编程。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
