Ubuntu Server 18.04安装、认识和使用Mysql 5.7数据库

一、在线安装mysql数据库

ubuntu安装mysql，对于可以联网的ubuntu主机或虚拟机，可以在线安装。

首先登录ubuntu的操作系统安装用户，使用sudo命令转换到root用户安装mysql。

sudo apt install mysql-server

sudo apt install mysql-client

sudo apt install libmysqlclient-dev

安装完成后可以通过下面的命令测试是否安装成功：

sudo netstat -tap | grep mysql

出现如下图1所示的信息，说明mysql的tcp监听已经启动，可以接受服务请求，数据库服务器安装成功。

首次安装后可以通过如下命令进入mysql服务：$Sudo mysql -uroot –p回车输入当前具备sudo权限的操作系统口令，要求输入数据库的root口令时，直接回车，即可进入mysql数据库。 如下图2所示表示安装成功。

卸载mysql数据库的步骤

1 sudo apt autoremove --purge mysql-server 删除mysql-server软件抱

2 sudo apt remove mysql-server

3 sudo apt autoremove mysql-server

4 sudo apt remove mysql-common

5 dpkg -l |grep ^rc|awk '{print $2}' |sudo xargs dpkg –P 清理残留数据

二、Mysql默认安装的目录结构

使用apt在线安装mysql数据库，不需要指定安装目录，安装的软件和创建的数据库到底在ubuntu操作系统的什么位置呢？

切换到root用户下，使用find ./ -name mysql –print 命令，可以查找Ubuntu主机上存在的名字为mysql的文件或文件夹，可以了解mysql大致的安装位置。结果如下图图3。

经分析和查询相关资料，apt安装的mysql服务器的目录结构大致如下表表一所示。

表一：Mysql主要目录及主要作用

下面截图说明上述目录包含的文件、目录和主要功能。

/var/lib/mysql/ 数据库存放目录见下图4，其中mysql、performance_schema、sys是文件夹，各文件下有对应该数据库的数据库对象文件，手工创建的数据库对应的文件目录也在此处。

/usr/share/mysql：字符集相关配置文件的存放位置，通过文件名或目录名，可以初步了解该目录的主要用途，有字符集，有不同国家的语言的文件等等。进入子目录可以看到更详细的信息。另外，众多的sql脚本完成数据库server的安装和配置。

/usr/bin：相关命令的存放目录，下图6是筛选出的mysql开头的命令，这些命令多数都是mysql数据库的管理命令，该目录下还有很多操作系统级的命令。

/etc/mysql目录： 数据库配置文件，如my.cnf等，后面要用到的配置mysql数据库是否允许远程访问的配置信息就在/etc/mysql/mysql.conf.d/mysqld.cnf文件中，里面绑定了本机地址，使root用户只能在本地连接数据库。目录结构见下图图7.

/var/run/mysqld 目录：/var/run 目录中存放的是自系统启动以来描系统信息的文件，msqld目录下的文件用于描述mysqld服务器程序的pid和相关锁定的信息，是mysql数据库运行期间使用的系统信息。目录结构见图8

三、mysql自带的4个数据库

系统初始安装后生成了4个数据库和4个数据库用户，4个数据库用户中root用户的authentication_string的访问密码串为空。下图9展示了通过root用户进入数据库后查询到的已经安装生成的4个数据库schema和4个数据库用户。

Mysql.user中authentication_string是加密过的密码，例如数据库用户debian-sys-maint的密码明文在debian.cnf文件中。如下图10所示。

可以使用debian-sys-maint的用户名和密码登录mysql数据库，如下图11所示。可以使用该用户紧急登录数据库，进行一些非常规的操作。

如图2所示，mysql默认安装后，系统生成了4个数据库：information_schema、mysql、performance_schema和sys。

Information_schema:存储了数据库的元数据，类似oracle的sys和system用户存储的数据字典的信息，例如有哪些数据库对象、有哪些表、每个表有哪些列、列的数据类型、列的主外键约束、schema权限、user权限、table权限、column权限、索引、事务控制机制、字符集等等。

Performance_schema：提供了进程等待的详细信息，包括锁等待等；存储记录了数据库运行的历史性能参数；为判断和优化数据库性能提供帮助。类似oracle数据库中sys和system用户下的一些系统运行数据。

Sys schema：包含了一系列对象，主要是视图、存储过程等等，用来辅助解决性能问题和系统故障。这些视图或存储过程读取information_schem和performance_schema的数据，生成便于查找性能瓶颈和系统故障的数据报告。

Mysql schema：是mysql的核心数据库，最接近使用mysql数据库开发应用系统的人员，类似于sql server中的master表，主要负责存储数据库的用户、权限设置、关键字等mysql自己需要使用的控制和管理信息

Mysql的每一个schema是一个逻辑数据库，里面有表、视图、存储过程、索引等等数据库对象。上述4个数据库都是系统级数据库，创建具体应用系统需要创建自己的数据库schema。

四、mysql自带的4个数据库用户user

如上图图8所示，mysql默认安装后，生成了4个数据库用户：root、dibian-sys-maint、mysql.session、mysql.sys。

：用于 sys schema中对象的定义。该用户mysql系统自己使用，已被锁定，客户端无法连接。

：插件内部使用来访问服务器。该用户已被锁定，客户端无法连接。

root@localhost：这个就是mysql数据库的root账户（用户），该用户拥有所有权限，可执行任何操作，是MySQL的特权账号，使用或扩散使用该账户存在安全隐患。从图8中可以看到这个账号没有密码，故在localhost上登录mysql不需要密码。

Dibian-sys-maint@host：debian-sys-maint用户是mysql安装在dibian和buntu服务器上才会创建的数据库用户，是个Mysql安装之后自带的用户，用于MySQL的非常规维护。

上述4个数据库用户是系统自带的，对于应用开发来说，要根据需要创建自己的数据库用户，在应用创建的数据库schema上授予合适的权限，才能保证系统安全运行。

五、解除本机访问绑定，允许远程访问

使用apt在线安装mysql后， mysql数据库默认绑定了本机地址，bind-address = 127.0.0.1，只有本机可以访问。

这样远程主机不管是Mysql Workbench、navicat、toad、pl/sql等数据库客户端管理工具，还是编写服务远程连接使用mysql数据库，都无法获得数据库连接。

做为mysql数据库服务器，必须要要能让其他主机访问才有意义，如果要允许mysql被远程访问，需要修改文件/etc/mysql/mysql.conf.d/mysqld.cnf：

sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf

注释掉bind-address = 127.0.0.1：则mysql数据库解除和本机的绑定，其他主机也可以访问该数据库 ，见下图12所示.

六、尝试在其他主机上使用root用户登录mysql数据库

如上图图8所示，在localhost上使用root数据库用户连接数据库时，不需要密码，可以直接回车连接到数据库中。要想在非localhost使用root连接数据库时，除了上面提到的屏蔽bind-address以外，还必须进行下面的配置。

如下图图13所示，192.168.1.104使用apt安装mysql后，使用grant all on *.* to root@'%' identified by 'root' with grant option，设置root用户的密码为root，可以连接的主机为%，表示所有主机都可以使用root用户及root用户的密码root来连接192.168.1.104上的mysql数据库。

从下图12可以看出，上述命令执行后，在user用户表中增加了一条用户为root的记录，该记录的host字段值为%。该表的主键索引为host+user。

当然也可以Sql>grant all on *.* to root@'192.168.1.100' identified by '123456' with grant option；表示192.168.1.100可以使用root用户连接数据库，连接密码为123456.

Sql>flush privileges; 刷新数据库授权，将新的数据加载到内存中，使该访问权限和密码生效。重启数据库服务也能达到这样的效果，sudo service mysql restart，

修改root账号的设置后，可以使用flush privileges生效，可以不重启mysql服务，避免重启不成功的风险。该命令本质是将user和privilege表中的用户信息和权限信息从mysql数据库的内置库（mysql）中提取到内存中。但好象重启服务更好使。

下图图14是192.168.1.102做为客户端访问192.168.1.104上的mysql数据库服务的示意图。192.168.1.104上的mysql刚默认安装完成，或者仅仅解除了本机访问的绑定限制后，192.168.1.102访问192.168.1.104上的mysql是被拒绝的。

在192.168.1.104上的数据库mysql中执行grant all on *.* to root@'%' identified by 'root' ，使用flush 或重启mysql服务（见图6所示）后，192.168.1.102做为客户端使用root/root连接192.168.1.104的mysql数据库连接成功。root数据库账户要这样操作，其他数据库账户要远程操作也需要配置主机权限。

七、Mysql的字符集

Mysql数据库支持40多种字符集，如下图图15所示。每种字符集都有默认的collation，即核对规则。

字符集确定了字符的编码和存储方式，核对规则规定了字符排序和比较的标准。例如在mysql数据库中有VARCHAR、CHAR、TEXT等字符类的数据类型，对于字符串的比较和排序都依据该核对规则。简单来说，设定不同的collation对于数据库SQL语句的order by、group by、having、distinct都会产生影响。对字符类型的列创建索引时，也会影响索引的数据存储，只是我们感觉不到。凡是涉及到字符类型比较或排序的地方，都会和COLLATE有关。

字符集和核对规则collation是一对多的关系，每种字符集可以有多种collation核对规则提供选择，见下图16所示。

例如如果使用gb2312字符集，没有显示指定collation，则默认使用gb2312_chinese_ci做为核对规则。

字符集是一套文字符号的编码、比较规则的集合。 目前支持中文的常用的字符集有UTF-8、GBK、GB2312等，MYSQL服务器支持多种字符集和collation，在同一台服务器、同一个数据库或者同一个数据表中的不同字段都可以使用不同的字符集，从而也使用对应的collation。

mysql的字符集和校对规则有四个级别的默认设置：服务器级、数据库级、数据表级、字段级。它们分别在不同的地方设置，作用域也不相同 。

对于实际的应用程序来说，还存在客户端和服务器之间交互的字符集和核对规则的设置，这就是连接字符集和它的核对校对规则，例如http协议头部的相关编码信息等。

如果没有设置核对规则，那么mysql会使用当前字符集的默认核对规则；同样，如果仅仅设置了核对规则而没有指定字符集，那么mysql会使用与设置的核对规则相匹配的字符集 。

八、mysql的存储引擎

存储引擎是数据库管理系统创建、读取和更新数据的软件模块。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎还可以获得特定的功能。现在许多数据库管理系统都支持多种不同的存储引擎。

下图图17是mysql5.7支持的存储引擎，分为两类：事务性和非事务性。

在 MySQL 中，不需要在整个服务器中使用同一种存储引擎，针对具体的要求，可以对每一个表使用不同的存储引擎。

MySQL 5.7默认的存储引擎是InnoDB，是事务性的存储引擎，能够保证数据的完整性和一致性。当前默认的存储引擎是全局于mysql服务器的，对mysql服务器下的数据库都有效。

SQL>show variables like '%storage_engine%; 则列出系统变量名中含有'strorage_engine'的系统变量和对应的值，从下图18可以看出MYSQL服务器当前默认的存储引擎。

InnoDB是一种支持事务的引擎。所有的数据存储在一个或者多个数据文件中，支持类似于Oracle的锁机制。一般在OLTP应用中使用较广泛。安装mysql服务器时，在mysql数据目录下创建一个名为ibdata1的自动扩展数据文件，以及两个名为ib_logfile0和ib_logfile1的日志文件。ibdata1为10m大小的自动扩展数据文件，ib_logfile0和 ib_logfile1为5MB大小的日志文件，这些文件构成mysql服务器级别的全局数据表空间，在参数InnoDB_file_per_table为OFF时，新建的表会存储在该数据文件中。

MyISAM是一种非事务性的引擎，提供高速存储和检索，以及全文搜索能力，适合数据仓库等查询频繁的应用。MyISAM中，一个table实际保存为三个文件，.frm存储表定义，.MYD存储数据,.MYI存储索引。不支持事务。

创建table时可以通过engine关键字指定使用的存储引擎，如果省略则使用系统默认的存储引擎：CREATE TABLE t (i INT) ENGINE = MyISAM;

InnoDB可以将它的表和索引放在一个逻辑表空间中， MyISAM表中每个表被存放在分离的文件中。InnoDB表可以是任何尺寸，即使在文件尺寸被限制为2GB的操作系统上

InnoDB表/MyISAM的表修改存储引擎为MyISAM/InnoDB时，修改表的存储引擎会引起数据文件的搬家。

Memory存储引擎的每个表对应一个磁盘文件，文件的文件名与表名相同，类型为frm类型。该文件中只存储表的结构。而其数据文件，都是存储在内存中，这样有利于数据的快速处理，提高整个表的效率。值得注意的是，服务器需要有足够的内存来维持MEMORY存储引擎的表的使用。如果不需要了，可以释放内存，甚至删除不需要的表。

Memory存储引擎的表的数据存储在内存中，如果内存出现异常就会影响数据。如果重启或者关机，所有数据都会消失。因此，基于MEMORY的表的生命周期很短，一般是一次性的。MEMORY表在所由客户端之间共享（就像其他任何非TEMPORARY表）

存储引擎的选择：同一个数据库也可以使用多种存储引擎的表。如果一个表要求比较高的事务处理，可以选择InnoDB；对查询要求比较高的表选择MyISAM存储；如果该数据库需要一个用于查询的临时表，可以选择MEMORY存储引擎。

九、为应用创建数据库

CREATE DATABASE [IF NOT EXISTS] <需要创建的数据库名> [ [DEFAULT] CHARACTER SET [=] <字符集名>] [ [DEFAULT] COLLATE [=] <校对规则名>]

创建数据库时指定数据库名，可以指定字符集，可以指定字符集的核对规则，没有其他选项。

mysql>create database if not exists mycrmdb default character set =utf8mb4 default collate=utf8mb4_general_ci;

从下图可以看到，创建mycrmdb数据库后，在MySQL的数据库目录中增加了一个目录名mycrmdb，该目录名和创建的数据库同名，故创建数据库时指定的数据库名称要服务操作系统目录名称的规定

新创建mycrmdb数据库后，在mysql的数据库目录mycrmdb中增加了一个文件：db.opt，打开db.opt后，发现里面只有存储着创建数据库时指定的字符集和核对规则信息，没有其他信息。见下图图19所示。

十、为应用创建数据库用户

可以使用 CREATE USER 语句来创建MySQL 账户（用户），并设置相应的口令，可以使用CREATE USER <用户名> [ IDENTIFIED ] BY [ PASSWORD ] <口令>这样简单的语法创建数据库用户。例如，create user mycrmaccount identified by 'mycrmaccount'，这样创建的账号没有分配权限。创建数据库用户的命令参数比较复杂，可以使用图形界面的客户端工具创建用户，这样比较方便。

下面使用mysql workbench客户端创建一个名为mycrmaccount账号。具体创建过程如下：

第一步：见下图图20，设置账户名称、密码和可以登录的主机，登录的主机中%是通配符。

第二步：见下图图21，设置该数据库账户登录数据库的次数、查询的次数、更新的次数及该账户可以并行登录数据库的数量进行限制。

第三步：见下图图22，设置该数据库账户的数据库对象的权限。

第四步：见下图图23，为数据库账户绑定数据库schema。

第五步：见下图图24，设定数据库账户对数据库schema的数据库对象权限。

经过上述配置，mycrmaccount账户就配置完成了，可以正常使用了。

十一、配置mysql操作系统用户

由表一 "Mysql主要目录及主要作用" 可以看出，mysql数据库安装后形成的很多目录和文件的属主和属组都是mysql，所以有必要设置mysql用户，以便登录操作系统，查看、使用和修改相关文件。

可以使用usermod命令和passwd命令配置操作系统用户。

如下图所示，安装mysql数据库后，在passwd文件中增加了mysql一行，mysql用户的主目录位置为/nonexistent，用户没有配置密码和主目录，无法登录主机的操作系统。通过usermod命令或者直接编辑配置文件，可以设置用户的主目录、shell等内容，如下图图25所示。

shadow文件存储操作系统用户的用户密码，初始apt install 安装mysql后，在shadow文件中增加了mysql一行，但没有设置密码。如下图图26所示，方框中的内容是使用#passwd mysql为mysql用户设置密码后形成的密码密文。

有了账户、密码、主目录、shell后，账户myql就可以正常登录了。

另，登录到mysql操作系统用户后，有时想执行sudo操作，可以将mysql用户加入到sudoers文件中，也可以使用usermod -aG sudo mysql的命令使mysql具备sudo权限。直接在/etc/sudoers文件中加入最好。mysql ALL=(ALL:ALL),当然实际在用的时候不要使用ALL，允许它使用啥就配置啥。如下图27所示。

上述设置完成后，可以在Ubuntu本机直接登录操作系统，并能使用sudo命令了。

usermod命令的用法如下：

usermod [-LU][-c <备注>][-d <登入目录>][-e <有效期限>][-f <缓冲天数>][-g <群组>][-G <群组>][-l <帐号名称>][-s <shell>][-u <uid>][用户帐号]

-c<备注> 　修改用户帐号的备注文字。

-d登入目录> 　修改用户登入时的目录。

-e<有效期限> 　修改帐号的有效期限。

-f<缓冲天数> 　修改在密码过期后多少天即关闭该帐号。

-g<群组> 　修改用户所属的群组。

-G<群组> 　修改用户所属的附加群组。

-l<帐号名称> 　修改用户帐号名称。

-L 　锁定用户密码，使密码无效。

-s<shell> 　修改用户登入后所使用的shell。

-u<uid> 　修改用户ID。

-U 　解除密码锁定。

十二、认识Mysql的数据文件

表二：mysql的主要数据文件说明

先说说系统表空间：ibdata1是Mysql服务器innoDB存储引擎使用的全局数据文件，又称为系统表空间。系统表空间存储数据的数据文件是ibdata1,用于控制事务处理的文件是：ib_logfile0、ib_logfile1、ibtmp1。ibdata1是一个自扩展的文件，系统表空间在Mysql服务器中只有一份,存储innoDB存储类型的表和索引.

在Mysql5.5.7-5.6.6版本之间，所有innoDB类型的数据库的表和索引都存储在系统表空间ibdata1中，

在Mysql5.7中用户建立的表默认建在独立的表空间文件中，位于数据库目录下，后缀名ibd。当然也可以指定参数使用系统表空间。mysql服务器系统表空间见下图图28所示。

再说说独立表空间：innoDB形式的表，也可以使用独立的表空间存储数据和索引，一张表对应两个文件：表名.frm 和表名.ibd.

在上面新创建的数据库mycrmdb sechema中建立innoDB类型的表emp，由于Mysql5.7默认使用独立表空间，故emp表的数据和索引都存储在mycrmdb/emp.ibd中，表结构存储在mycrmdb/emp.frm中。使用shell脚本给emp加载大约40多万条数据，发现独立表空间的数据文件不断增大，系统表空间的文件一直不会变化，验证了Mysql5.7的innoDB的表默认使用独立表空间，如下图图29所示。

InnoDB和MyISAM存储类型的相互转换。在满足一定的条件下，可以修改表的存储引擎。例如 ALTER TABLE emp ENGINE = MyISAM ，则可以将emp表的innoDB存储类型改成MyISAM类型，瞬间完成。将原来的数据和索引从emp.ibd文件拆到emp.MYD和emp.MYI两个文件中，拆出后两个文件大小之和比原来的文件要小不少呢，见下图图30所示。

再尝试将改到MyISAM类型的表改回InnoDB类型，ALTER TABLE `mycrmdb`.`emp` ENGINE = InnoDB ，则发现原来的emp.MYI和emp.MYD文件不见了，新建了一个emp.ibd文件接收了原来的数据和索引，见下图图31所示。

InnoDB类型的表转变成MEMORY型的表：ALTER TABLE emp EGINE = MEMORY ;

alter table emp engine=MEMORY,将innoDB类型的emp表给成内存表后，存储数据和索引的emp.ibd文件没有了，但任何客户端还是可以查询到修改前的数据的，因为修改后将数据和索引放到了内存中，数据文件删掉了，见下图图32所示。

再将内存表转换成InnoDB型的表，可以转换，但内存表的数据和索引不会带回到InnoDB表中。例如 ALTER TABLE `mycrmdb`.`emp` ENGINE = InnoDB ; 将MEMORY类型的表改成InnoDB类型的表，这时内存中的数据不会带过来。

使用独立表空间的表，建表、插入数据、truncate 数据，都能很明显的从数据文件中看出变化。系统表空间中存储了多个数据库用户的多张表和多个索引，可以验证一下系统表空间文件的变化情况。

SQL>set global innodb_file_per_table=off; 关闭独立表空间参数，采用系统表空间存储表的数据和索引。

上面将MEMORY型的emp表修改成了InnoDB型的表，向emp表插入90万条数据，看看系统表空间文件的变化。

ibdata1表空间文件确实增大了，表空间文件每次扩展应该都有一个固定的值，新插入的数据放到扩展的空间中，扩展空间没有用满时，新插入数据不会导致表空间文件继续扩展。如下图图33所示。

SQL.>set global innodb_file_per_table=on/off指令，只在当前数据库server运行期间有效，重启数据库server或者重启主机后，原来的设置即失效。例如Mysql5.7默认innodb_file_per_table=on,如果通过set命令设置为off，则系统重启后会自动设置该参数为on，仍然使用表级的独立表空间文件。如下图，使用set命令修改的参数都是临时生效的，不是永久的，下图图34是查询和设置innodb_file_per_table参数的数据库命令。

若想长期有效，可以在配置文件中设置。mysql5.7的/etc/mysql/mysql.conf.d/mysqld.cnf文件中增加一行，通过配置文件配置innodb_file_per_table参数，如下图图35所示。

修改innodb_file_per_table 参数不影响已经存在的表，已经存在的表原来数据和索引在系统表空间ibdata1中的，新更新的数据还在ibdata1中，原来再独立表空间表名.ibd的，更新的数据孩在表名.ibd文件中。这样一个数据库中的InnoDB类型的表，有的可能在系统表空间中，有的可能在独立的表空间中，跨系统表空间和独立表空间，可以做多表联合查询。

truncate table 时，发现使用独立表空间的数据文件会回收，数据文件体积变小，但使用全局的系统表空间在truncate talbe后不会变小。delete from table 独立表空间也不回收。

十三、实际应用中表和存储引擎的选择

一般联机事务处理OLTP应用程序，还是老老实实使用InnoDB类型的表，保证事务的完整性和一致性；数据文件建议使用Mysql5.7的默认方式，使用独立的表空间，即一个表一套数据文件。

基于Mysql提供的特性，出于测试、验证的目的，可按下面的规划构建系统，进行测试验证。

1. 应用初始化时，装载静态参数文件到普通的内存表，供应用的各个不同的用户连接查询使用，仅供查询，相当于将一个表cach到内存中了，提高数据的存取速度。普通的内存表是使用如下的命令建立的： CREATE TABLE (…) ENGINE=MEMORY

2. 经常使用查询、插入、修改、删除，需要严格保证数据一致性的表，采用InnoDB存储引擎，使用innodb_file_per_table=ON的参数，使用独立的表空间。

3. 对于仅存在插入和查询的流水表，可以尝试使用MyISAM类型的表。

4. 对于数据操作过程中，可以使用局部于会话连接的临时表，临时表可以是MEMORY形式，也可以是其他的形式，例如打印报表时可以使用临时表暂存临时汇总数据，暂存打印结果数据。会话结束后临时表自动消亡。临时表是使用CREATE TEMPORY TABLE建立的表。