【运维工程师学习五】数据库

    • 1、常用的关系型数据库
    • 2、C/S结构
    • 3、MariaDB图形客户端
    • 4、安装MariaDB
    • 5、启动MariaDB及验证启动是否成功
    • 6、验证启动——端口
    • 7、验证启动——进程
    • 8、MariaDB配置文件路径
      • 主配置文件解读:
    • 9、MariaDB的配置选项
    • 10、MariaDB客户端连接
      • 1、在本机,即使root密码为空,客户端一样可以连接的,使用命令`mysql –u root –p` 提示输入密码时直接回车即可进入数据库;
      • 2、修改数据库超管root的密码,除了上述方法,还有另外一种方法,就是进入数据库后通过数据库的更新操作(update)可以实现,具体方法,请看下节“mariadb修改root密码”;
      • 3、允许root用户远程连接(在业务环境中不会这样做,但现在是实验,为客户端连接linux的Mariadb做铺垫),使用命令行客户端进入数据库后执行命令
      • 4、刷新MySQL的系统权限相关表­
    • 11、图形客户端的使用(Navicat为例)
    • 12、默认安装后,系统自带3个数据库
    • 13、mariadb修改root密码
    • 14、客户端常用命令——数据库相关
      • (1)显示已有的数据库
      • (2)创建数据库
      • (3)删除数据库
    • 15、导入数据
    • 16、为业务数据库创建指定的用户
    • 17、客户端常用命令——表相关
    • 18、数据库备份
      • (1)备份指定数据库
      • (2)备份所有数据库
      • (3)备份指定数据库指定表(多个表以空格间隔)
      • (4)备份指定数据库排除某些表
    • 19、还原/导入数据库

1、常用的关系型数据库

db根据存储模型划分,数据库类型主要可分为:网状数据库(Network Database)、关系数据库(Relational Database)、树状数据库(Hierarchical Database)、面向对象数据库(Object-oriented Database)等。商业应用中主要是关系数据库,而关系数据库中常见的软件有比如Oracle、DB2、Sybase、MS SQL Server、Informax、MySQL等等。
实数据库系统几乎支持所有的操作系统,也就是上述数据库几乎与平台无关(当然除SQL Server和Access以外,因为这是微软的东西,平台依赖性很强的,只在windwos系统平台上运行)。

关系型数据库系统除了全面列出来的还有很多,但最常用的也不外乎:

  • Oracle #闭源、收费,金融行业或其它行业大型公司的选择
  • MySQL #开源、免费,互联网公司的最爱
  • MS SQL Server #闭源、收费,一些互联网公司及传统中小企的选择

注:MySQL已被Oracle公司收购。MySQL原创始人重新开发了一个分支,叫MariaDB,完全兼容MySQL,包括API和命令行,使之能轻松成为MySQL的代替品。CentOS7系统中已经将默认的数据库系统MySQL替换为MariaDB,因此接下来的关于数据库的安装及使用演示均是基于MariaDB。

2、C/S结构

数据库的软件体系结构是C/S(Client/Server,客户端/服务端),要访问或管理数据库必须通过对应的客户端。认清这个问题,有两个好处(以MariaDB为例):

下图中,初学者往往会mariadb.x86_64为数据库,实则它只是客户端;

  1. mariadb.x86_64数据库客户端
  2. mariadb-server.x86_64才是数据库服务端
  3. 选择最适合自己使用习惯的客户端,有利于提高认知及学习效率。mariadb.x86_64是命令行客户端,初期建议尝试几种图形客户端。

3、MariaDB图形客户端

  1. phpMyAdmin
  2. MySQL Workbench
  3. Adminer
  4. Navicat for MySQL
  5. OmniDB
  6. DataGrip(JetBrains旗)
  7. Dbeaver
  8. SQLyog

4、安装MariaDB

本次安装使用“软件仓库安装法”。同时安装服务端及客户端:

yum install -y mariadb-server mariadb

5、启动MariaDB及验证启动是否成功

  • 启动、设置开机自动启动、查看状态
systemctl start mariadb

6、验证启动——端口

netstat -utnlp

  • 3306是MySQL及MariaDB的默认端口。
  • 端口信息及进程信息均表示数据库MariaDB启动正常。

7、验证启动——进程

ps aux | grep mariadb

8、MariaDB配置文件路径

主配置文件:/etc/my.cnf #MySQL也是这个,所以说MariaDB完全兼容性!

vi /etc/my.cnf 

[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.socksymbolic-links=0## This group is read both both by the client and the server# use it for options that affect everything#[mysqld_safe]log-error=/var/log/mariadb/mariadb.logpid-file=/var/run/mariadb/mariadb.pid[client-server]## include all files from the config directory#!includedir /etc/my.cnf.d

主配置文件解读:

  1. [***]表示配置分组,如[mysqld]表示从下一行开始至出现 [***]的行,均为服务端的配置行。本文件为默认配置,服务端只设置了两个选项,一个是datadir数据保存路径),一个是socket套接字文件路径);
  2. !includedir表示其它附加的配置文件路径。本例中包含的/etc/my.cnf.d文件夹中凡是以.cnf结尾的文件都会被当数据库的配置文件的一部分。如果这个文件夹中的配置文件定义的选项与主配置文件中有重复,则以该文件夹的配置为准。
  3. 配置文件引入“包含”的概念,一般是为了实现配置文件的分类,提高配置文件的可读性。例如可以把关于服务端的配置选项全部放在server.cnf中,将客户端的配置选项放在client.cnf中。

9、MariaDB的配置选项

注:
1、关于搜索到的配置举例,请不要盲目复制,因为很多选项都是基于特定的硬件(内存大小及CPU性能);
2、配置的优化是根据自身的业务环境而定,并且是在有性能监控数据及遇到的问题总结做支撑,例如:遇到错误提示“超出连接数”那就要修改最大连接数、遇到有大量请求在等待那就可能要修改最大并发数、发现内存及CPU占用过高那就可能要修改缓存大小,等等;
3、早期不用太热衷于玩弄各个高级配置项,基本配置都够你玩半年(配合web练习),天才除外;

10、MariaDB客户端连接

mysql -u root -p

使用yum安装的mariadb,默认管理员root的密码为空,并且只允许本机连接,不允许远程连接。因此,我们需要为root用户配置密码,方法如下:

1、在本机,即使root密码为空,客户端一样可以连接的,使用命令mysql –u root –p 提示输入密码时直接回车即可进入数据库;

2、修改数据库超管root的密码,除了上述方法,还有另外一种方法,就是进入数据库后通过数据库的更新操作(update)可以实现,具体方法,请看下节“mariadb修改root密码”;

3、允许root用户远程连接(在业务环境中不会这样做,但现在是实验,为客户端连接linux的Mariadb做铺垫),使用命令行客户端进入数据库后执行命令

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'IDENTIFIED BY '123456' WITH GRANT OPTION;

123456为密码,上述命令的意思是:允许密码为123456的root用户具有数据库中所有数据库的所有权限。

4、刷新MySQL的系统权限相关表­

FLUSH PRIVILEGES;

11、图形客户端的使用(Navicat为例)

需要先关闭虚拟机的防火墙,才能够连接成功(先前文章14、15节说过了这里不再重复,自行通过下方链接查阅)
https://blog.csdn.net/weixin_43576565/article/details/131376844


连接成功

12、默认安装后,系统自带3个数据库

  • information_schema #此数据库提供了访问数据库元数据的方式
  • mysql #核心数据库,存储用户的权限信息等,不可以随意修改其内容
  • performance_schema #此数据库为数据库性能优化提供重要的参考信息,默认关闭
  • test #(有些版本会带)顾名思义,测试数据库,没有内容,可删除

13、mariadb修改root密码

具体方法:
1、进入etc目录下

cd /etc

2、编辑my.cnf文件

vi my.cnf

3、在my.ini,[mysqld]下添加一行,使其登录时跳过权限检查

[mysqld]skip-grant-tables 

4、重启数据库

service mysqld restart

5、直接无密码登录数据库

mysql -uroot -p

6、查看数据库并使用mysql数据库表

mysql> show databases;mysql> use mysql;

7、更新密码

mysql> update user set password=passworD("123456") where user='root';

#注意如果Mysql的版本是5.7及以上的话update语句如下:

update user set authentication_string=passworD("123456") where user='root';

#刷新数据库的系统权限相关表­

FLUSH PRIVILEGES;

8、去my.cnf配置文件中删除使其登录时跳过权限检查的配置
9、重启mysql实现密码登录

service mysqld restart

14、客户端常用命令——数据库相关

(1)显示已有的数据库

show databases;


命令以;结尾
注意database后面有个s,英文中表示复数的意思
整个命令顾名思议就是显示所有数据库,默认共4个数据库,各个数据库的意义前面已作基本介绍。

注:客户端中的命令不支持tab补全;表名及列名可以使用tab补全,但这个功能默认不开启,欲尝试自行搜索我的博客,篇幅问题这里不展开说。

(2)创建数据库

create database mytest;#mytest为数据库名

(3)删除数据库

drop database mytest;#mytest为数据库名

注意这里的database没有加s
已经成功创建mytest数据库
上面只是最简单的创建数据库命令,如果需要指定数据库的字符集则需要加相关参数。

15、导入数据

source / sql-dftables.txt# /root/ sql-dftables.txt为数据库脚本文件

需要输入完整路径。如果脚本中只有表结构,则只会在数据库中创建表,如果脚本中既有表结构又有数据内容,则都会导入。数据库脚本文件其实就是数据库的各种命令的集合。

导入之前先使用use命令连接指定的数据库,即后面的导入操作就会导入至该数据库。

注:包括后面介绍关于数据库表的操作命令都需要先使用use命令,指定数据库。使用source命令,最后不用;结尾

16、为业务数据库创建指定的用户

为某数据库创建指定用户,基于安全考虑,不使用root用户进行业务操作,建议为业务数据库创建指定的用户,命令如下:

GRANT ALL PRIVILEGES ON mytest.* TO 'test2'@'localhost'IDENTIFIED BY '12345qwert';

GRANT SELECT,INSERT,UPDATE,DELETE ON mytest.* TO 'test2'@'%'IDENTIFIED BY '12345qwert';

命令解读如下:

  • GRANT后面跟的是权限范围 ALL PRIVILEGES
    表示允许所有权限,而SELECT,INSERT,UPDATE,DELETE表明只允许查、增、改、删四个权限,其它不允许。
  • ON 后面跟的数据库名 mytest.* 表示mystest的所有表都被允许,TO后面@之前表示的是用户名(不带引号)
    @后面跟的是客户端位置(localhost表示仅本机可连,%表示任意远程主机,还可以指定某IP或IP段) BY后面跟的是该用户的密码

注:客户端中的命令不区分大小写,只是大家习惯将命令大写,内容小写提高整条命令的可读性。

17、客户端常用命令——表相关

显示数据库中的所有表:

show tables;

关于表的操作命令还有很多,如增(INSERT)、删(DELETE)、改(UPDATE)、查(SELECT)不再演示,请自行搜索,如搜索“mysql常用命令”

18、数据库备份

数据库备份方法有很多,这里仅演示自带的备份工具备份完整的数据库(表结构及内容),命令如下:

(1)备份指定数据库

mysqldump -u root -p mytest > /root/mytest_bak_20230711.sql

mysqldump是一个独立的程序,因此这里看到的是系统命令行环境中。

  • -u root是使用root命令登陆数据库来操作备份动作
  • -p是后面要输入密码,与mysql客户端类似
  • mytest是要备份数据库名
  • root/mytest_bak_20230711.sql是备份的目标文件,这是一个文本文件,只是习惯使用.sql作为文件后缀标识,方便识别。
  • mysqldump还有很多用法,欲了解更多请自行搜索。
  • 备份的文本文件的部分内容。
  • 还原则使用前面介绍的source即可。

注意这一步需要推出数据库,不能在数据库内执行。

(2)备份所有数据库

mysqldump -u root -p --all-databases > /root/alldatabases_bak_20230711.sql

(3)备份指定数据库指定表(多个表以空格间隔)

mysqldump -u root -p mysql db user event > /root/mysql_bak_20230711.sql

(4)备份指定数据库排除某些表

mysqldump -uroot -p mysql --ignore-table=mysql.db --ignore-table=mysql.user > /root/123.sql

19、还原/导入数据库

mysql > use db_name #输入需要还原/导入的数据库名mysql > source /backup/mysqldump/db_name.db #输入还原/导入的数据库路径