侧边栏壁纸
博主头像
coydone博主等级

记录学习，分享生活的个人站点

  • 累计撰写 306 篇文章
  • 累计创建 51 个标签
  • 累计收到 0 条评论
标签搜索

目 录CONTENT

文章目录
数据库 Linux运维

Linux下MySQL的安装与使用

coydone
coydone
2022-01-12 / 0 评论 / 0 点赞 / 451 阅读 / 10,074 字 / 正在检测是否收录...
温馨提示：
本文最后更新于 2022-04-07，若内容或图片失效，请留言反馈。部分素材来自网络，若不小心影响到您的利益，请联系我们删除。

Linux下MySQL的安装

准备

查看是否安装过MySQL

# 查看Linux服务器上是否安装过MySQL，如果是用rpm安装, 检查一下RPM PACKAGE
rpm -qa | grep -i mysql # 查询出所有mysql依赖包

# 检查mysql service
systemctl status mysqld.service

如果存在mysql-libs的旧版本包，显示如下：

MySQL的卸载

# 1、关闭MySQL服务
systemctl stop mysqld.service

# 2、查看当前MySQL安装状况
rpm -qa | grep -i mysql
  或  yum list installed | grep mysql

# 3、卸载上述命令查询出的已安装程序
yum remove mysql-xxx mysql-xxx mysql-xxx mysql-xxxx
#务必卸载干净，反复执行 rpm -qa | grep -i mysql 确认是否有卸载残留

# 4、删除 mysql 相关文件
find / -name mysql
# 查找相关文件
rm -rf xxx # 删除上述命令查找出的相关文件
rm -rf /etc/my.cnf # 删除 my.cnf

安装

Linux下安装软件

Linux系统下安装软件的常用三种方式：

方式1：rpm命令，使用rpm命令安装扩展名为".rpm"的软件包。

rpm包的一般格式：

方式2：yum命令需联网，从 互联网获取 的yum源，直接使用yum命令安装。

方式3：编译安装源码包针对 tar.gz 这样的压缩格式，要用tar命令来解压；如果是其它压缩格式，就使用其它命令。

官网下载rpm包

选择与之对应的 Red Hat Enterprise Linux，直接点Download下载RPM Bundle全量包。包括了所有下面的组件。不需要一个一个下载了。

解压后rpm安装包 （红框为抽取出来的安装包）

CentOS7下检查MySQL依赖

1、检查/tmp临时目录权限（必不可少）

由于MySQL安装过程中，会通过MySQL用户在/tmp目录下新建tmp_db文件，所以请给/tmp较大的权限。执行 ：chmod -R 777 /tmp

2、安装前，检查依赖

rpm -qa|grep libaio
rpm -qa|grep net-tools
#如果不存在需要到centos安装盘里进行rpm安装。如果linux带图形化界面，都是安装好的

MySQL安装过程

  1. 将安装程序拷贝到/opt目录下
# 在mysql的安装文件目录下执行：（必须按照顺序执行）
# -i , --install 安装软件包
# -v , --verbose 提供更多的详细信息输出
# -h , --hash 软件包安装的时候列出哈希标记 (和 -v 一起使用效果更好)，展示进度条
rpm -ivh mysql-community-common-8.0.25-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-plugins-8.0.25-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-8.0.25-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-8.0.25-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-8.0.25-1.el7.x86_64.rpm

注意：如在检查工作时，没有检查MySQL依赖环境在安装mysql-community-server会报错。

rpm是Redhat Package Manage缩写，通过RPM的管理，用户可以把源代码包装成以rpm为扩展名的文件形式，易于安装。

安装过程中可能的报错信息，使用命令 yum remove mysql-libs 解决，清除之前安装过的依赖即可。

查看MySQL版本

执行如下命令，如果成功表示安装MySQL成功。类似java -version如果打出版本等信息。

mysql --version 或 mysqladmin --version

服务的初始化

为了保证数据库目录与文件的所有者为MySQL登录用户，如果你是以 root 身份运行MySQL服务，需要执行下面的命令初始化：mysqld --initialize --user=mysql

说明： --initialize 选项默认以“安全”模式来初始化，则会为 root 用户生成一个密码并将该密码标记为过期 ，登录后你需要设置一个新的密码。生成的临时密码会往日志中记录一份。

查看密码（root@localhost:后面就是初始化的密码）：cat /var/log/mysqld.log

启动MySQL

#加不加.service后缀都可以
启动：systemctl start mysqld.service
关闭：systemctl stop mysqld.service
重启：systemctl restart mysqld.service
查看状态：systemctl status mysqld.service
查看进程：ps -ef | grep -i mysql

mysqld 这个可执行文件就代表着 MySQL 服务器程序，运行这个可执行文件就可以直接启动一个服务器进程。

查看MySQL服务是否自启动：systemctl list-unit-files|grep mysqld.service，默认是enabled。如不是enabled可以运行命令设置自启动：systemctl enable mysqld.service，如果希望不进行自启动，运行命令设置：systemctl disable mysqld.service

MySQL登录

首次登录通过 mysql -hlocalhost -P3306 -uroot -p 进行登录，在Enter password：录入初始化密码。

修改密码：ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';，5.7版本之后（不含5.7），MySQL加入了全新的密码安全机制，设置新密码太简单会报错。

设置远程登录

1、确认网络，在远程机器上使用ping ip地址 保证网络畅通。

2、在远程机器上使用telnet命令保证端口号开放访问。

3、关闭防火墙或开放端口。

方式一：关闭防火墙

# centos6
service iptables stop
# centos7
systemctl start firewalld.service
systemctl status firewalld.service
systemctl stop firewalld.service
#设置开机启用防火墙
systemctl enable firewalld.service
#设置开机禁用防火墙
systemctl disable firewalld.service

方式二：开放端口

# 查看开放的端口号
firewall-cmd --list-all
# 设置开放的端口号
firewall-cmd --add-service=http --permanent
firewall-cmd --add-port=3306/tcp --permanent
# 重启防火墙
firewall-cmd --reload

Linux下修改配置

在Linux系统MySQL下测试：

use mysql;
select Host,User from user;

可以看到root用户的当前主机配置信息为localhost。

修改Host为通配符%，Host设置了“%”后便可以允许远程访问。

Host列指定了允许用户登录所使用的IP，比如user=root Host=192.168.1.1。这里的意思就是说root用户只能通过192.168.1.1的客户端去访问。 user=root Host=localhost，表示只能通过本机客户端去访问。而 %是个 通配符 ，如果Host=192.168.1.%，那么就表示只要是IP地址前缀为“192.168.1.”的客户端都可以连接。如果 Host=% ，表示所有IP都有连接权限。注意：在生产环境下不能为了省事将host设置为%，这样做会存在安全问题，具体的设置可以根据生产环境的IP进行设置。

update user set host = '%' where user ='root';
#Host修改完成后执行flush privileges使配置立即生效：

如果是 MySQL8 版本，连接时还会出现问题：配置新连接报错：错误号码 2058，分析是 MySQL密码加密方法变了。

解决方法：Linux下 mysql -u root -p 登录你的 mysql 数据库，然后 执行这条SQL：

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'abc123';

然后在重新配置SQLyog的连接，则可连接成功了，OK。

MySQL字符集

修改字符集

在MySQL 8.0版本之前，默认字符集为 latin1 ，utf8字符集指向的是 utf8mb3 。从MySQL8.0开始，数据库的默认编码将改为 utf8mb4。

# 查看默认使用的字符集
show variables like 'character%';
# 或者
show variables like '%char%';

# 修改字符集
vim /etc/my.cnf
在MySQL5.7或之前的版本中，在文件最后加上中文字符集配置：
character_set_server=utf8

#重新启动MySQL服务
systemctl restart mysqld

但是原库、原表的设定不会发生变化，参数修改只对新建的数据库生效。

已有库/表字符集的变更

# 修改已创建数据库的字符集
alter database dbtest1 character set 'utf8';
# 修改已创建数据表的字符集
alter table t_emp convert to character set 'utf8';

注意：原有的数据如果是用非’utf8’编码的话，数据本身编码不会发生改变。已有数据需要导出或删除，然后重新插入。

各级别的字符集

MySQL有4个级别的字符集和比较规则，分别是：服务器级别、数据库级别、表级别、列级别。

show variables like 'character%';

  • character_set_server：服务器级别的字符集。

  • character_set_database：当前数据库的字符集。

  • character_set_client：服务器解码请求时使用的字符集。

  • character_set_connection：服务器处理请求时会把请求字符串从character_set_client转为character_set_connection。

  • character_set_results：服务器向客户端返回数据时使用的字符集。

1、服务器级别

我们可以在启动服务器程序时通过启动选项或者在服务器程序运行过程中使用 SET 语句修改这两个变量的值。比如我们可以在配置文件中这样写：

[server]
character_set_server=gbk # 默认字符集
collation_server=gbk_chinese_ci #对应的默认的比较规则

当服务器启动的时候读取这个配置文件后这两个系统变量的值便修改了。

2、数据库级别

我们在创建和修改数据库的时候可以指定该数据库的字符集和比较规则，具体语法如下：

CREATE DATABASE 数据库名
	[[DEFAULT] CHARACTER SET 字符集名称]
	[[DEFAULT] COLLATE 比较规则名称];
ALTER DATABASE 数据库名
	[[DEFAULT] CHARACTER SET 字符集名称]
	[[DEFAULT] COLLATE 比较规则名称];

3、表级别

我们也可以在创建和修改表的时候指定表的字符集和比较规则，语法如下：

CREATE TABLE 表名 (列的信息)
	[[DEFAULT] CHARACTER SET 字符集名称]
	[COLLATE 比较规则名称]]
ALTER TABLE 表名
	[[DEFAULT] CHARACTER SET 字符集名称]
	[COLLATE 比较规则名称]

如果创建和修改表的语句中没有指明字符集和比较规则，将使用该表所在数据库的字符集和比较规则作为该表的字符集和比较规则。

4、列级别

对于存储字符串的列，同一个表中的不同的列也可以有不同的字符集和比较规则。我们在创建和修改列定义的时候可以指定该列的字符集和比较规则，语法如下：

CREATE TABLE 表名(
	列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称],
	其他列...
);
ALTER TABLE 表名 MODIFY 列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称];

对于某个列来说，如果在创建和修改的语句中没有指明字符集和比较规则，将使用该列所在表的字符集和比较规则作为该列的字符集和比较规则。

字符集

utf8 与 utf8mb4

utf8字符集表示一个字符需要使用1～4个字节，但是我们常用的一些字符使用1～3个字节就可以表示了。而字符集表示一个字符所用的最大字节长度，在某些方面会影响系统的存储和性能，所以设计MySQL的设计者偷偷的定义了两个概念：

  • utf8mb3 ：阉割过的 utf8 字符集，只使用1～3个字节表示字符。

  • utf8mb4 ：正宗的 utf8 字符集，使用1～4个字节表示字符。

在MySQL中utf8是utf8mb3的别名，所以之后在MySQL中提到utf8就意味着使用1~3个字节来表示一个字符。如果大家有使用4字节编码一个字符的情况，比如存储一些emoji表情，那请使用utf8mb4。

此外，通过如下指令可以查看MySQL支持的字符集：

SHOW CHARSET; 或者 SHOW CHARACTER SET;

比较规则

MySQL版本一共支持41种字符集，其中的 Default collation 列表示这种字符集中一种默认的比较规则，里面包含着该比较规则主要作用于哪种语言，比如 utf8_polish_ci 表示以波兰语的规则比较， utf8_spanish_ci 是以西班牙语的规则比较， utf8_general_ci 是一种通用的比较规则。

后缀表示该比较规则是否区分语言中的重音、大小写。具体如下：

常用操作

#查看GBK字符集的比较规则
SHOW COLLATION LIKE 'gbk%';
#查看UTF-8字符集的比较规则
SHOW COLLATION LIKE 'utf8%';

#查看服务器的字符集和比较规则
SHOW VARIABLES LIKE '%_server';
#查看数据库的字符集和比较规则
SHOW VARIABLES LIKE '%_database';
#查看具体数据库的字符集
SHOW CREATE DATABASE dbtest1;
#修改具体数据库的字符集
ALTER DATABASE dbtest1 DEFAULT CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

#查看表的字符集
show create table employees;
#查看表的比较规则
show table status from atguigudb like 'employees';
#修改表的字符集和比较规则
ALTER TABLE emp1 DEFAULT CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

utf8_unicode_ci和utf8_general_ci对中、英文来说没有实质的差别。utf8_general_ci校对速度快，但准确度稍差。utf8_unicode_ci 准确度高，但校对速度稍慢。

一般情况，用utf8_general_ci就够了，但如果你的应用有德语、法语或者俄语，请一定使用utf8_unicode_ci。

请求到响应过程中字符集的变化

这几个系统变量的默认值如下（不同操作系统的默认值可能不同）：

开发中通常把 character_set_client 、 character_set_connection、character_set_results 这三个系统变量设置成和客户端使用的字符集一致的情况，这样减少了很多无谓的字符集转换。为了方便我们设置，MySQL提供了一条非常简便的语句：

set names 字符集名  #set names utf8
# 效果等同于
SET character_set_client = 字符集名;
SET character_set_connection = 字符集名;
SET character_set_results = 字符集名;

另外，如果你想在启动客户端的时候就把character_set_client、character_set_connection、character_set_results这三个系统变量的值设置成一样的，那我们可以在启动客户端的时候指定一个叫default-character-set的启动选项，比如在配置文件里可以这么写：

[client]
default-character-set=utf8

它起到的效果和执行SET NAMES utf8是一样的，都会将那三个系统变量的值设置成utf8。

SQL大小写规范

Windows和Linux平台区别

在 SQL 中，关键字和函数名是不用区分字母大小写的，比如 SELECT、WHERE、ORDER、GROUP BY 等关键字，以及 ABS、MOD、ROUND、MAX 等函数名。不过在 SQL 中，你还是要确定大小写的规范，因为在 Linux 和 Windows 环境下，你可能会遇到不同的大小写问题。 windows系统默认大小写不敏感 ，但是 linux系统是大小写敏感的。

通过如下命令查看：SHOW VARIABLES LIKE '%lower_case_table_names%'，Windows下值为1和Linux下值为0。

lower_case_table_names参数值的设置：

  • 默认为0，大小写敏感 。

  • 设置1，大小写不敏感。创建的表，数据库都是以小写形式存放在磁盘上，对于sql语句都是转换为小写对表和数据库进行查找。

  • 设置2，创建的表和数据库依据语句上格式存放，凡是查找都是转换为小写进行。

两个平台上SQL大小写的区别具体来说：

MySQL在Linux下数据库名、表名、列名、别名大小写规则是这样的：

  1. 数据库名、表名、表的别名、变量名是严格区分大小写的；

  2. 关键字、函数名称在 SQL 中不区分大小写；

  3. 列名（或字段名）与列的别名（或字段别名）在所有的情况下均是忽略大小写的；

MySQL在Windows的环境下全部不区分大小写。

Linux下大小写规则设置

当想设置为大小写不敏感时，要在 my.cnf 这个配置文件 [mysqld] 中加入lower_case_table_names=1 ，然后重启服务器。

但是要在重启数据库实例之前就需要将原来的数据库和表转换为小写，否则将找不到数据库名。

此参数适用于MySQL5.7。在MySQL8下禁止在重新启动 MySQL 服务时将lower_case_table_names 设置成不同于初始化 MySQL 服务时设置的lower_case_table_names 值。如果非要将MySQL8设置为大小写不敏感，具体步骤为：

1、停止MySQL服务；
2、删除数据目录，即删除 /var/lib/mysql 目录；
3、在MySQL配置文件（ /etc/my.cnf ）中添加 lower_case_table_names=1；
4、启动MySQL服务。

SQL编写建议

1、关键字和函数名称全部大写；

2、数据库名、表名、表别名、字段名、字段别名等全部小写；

3、SQL 语句必须以分号结尾。

数据库名、表名和字段名在 Linux MySQL 环境下是区分大小写的，因此建议你统一这些字段的命名规则，比如全部采用小写的方式。虽然关键字和函数名称在 SQL 中不区分大小写，也就是如果小写的话同样可以执行。但是同时将关键词和函数名称全部大写，以便于区分数据库名、表名、字段名。

sql_mode的合理设置

介绍

sql_mode 会影响MySQL支持的SQL语法以及它执行的数据验证检查。通过设置sqL_mode,可以完成不同严格程度的数据校验，有效地保障数据准确性。

MySQL服务器可以在不同的SQL模式下运行，并且可以针对不同的客户端以不同的方式应用这些模式，具体取决于sql_mode系统变量的值。

MySQL5.6和MySQL5.7默认的sql_mode模式参数是不一样的：

  • 5.6的mode默认值为空（即：NO_ENGINE_SUBSTITUTION），其实表示的是一个空值，相当于没有什么模式设置，可以理解为宽松模式。在这种设置下是可以允许一些非法操作的，比如允许一些非法数据的插入。

  • 5.7的mode是STRICT_TRANS_TABLES，也就是严格模式。用于进行数据的严格校验，错误数据不能插入，报error（错误），并且事务回滚。

宽松模式 vs 严格模式

宽松模式：如果设置的是宽松模式，那么我们在插入数据的时候，即便是给了一个错误的数据，也可能会被接受，并且不报错。

举例 ：在创建一个表时，该表中有一个字段为name，给name设置的字段类型时 char(10) ，如果在插入数据的时候，其中name这个字段对应的有一条数据的 长度超过了10 ，例如’1234567890abc’，超过了设定的字段长度10，那么不会报错，并且取前10个字符存上，也就是说你这个数据被存为了’1234567890’，而’abc’就没有了。但是，我们给的这条数据是错误的，因为超过了字段长度，但是并没有报错，并且mysql自行处理并接受了，这就是宽松模式的效果。

应用场景 ：通过设置sql_mode为宽松模式，来保证大多数sql符合标准的sql语法，这样应用在不同数据库之间进行迁移时，则不需要对业务sql进行较大的修改。

严格模式：出现上面宽松模式的错误，应该报错才对，所以MySQL5.7版本就将sql_mode默认值改为了严格模式。所以在生产等环境中，我们必须采用的是严格模式，进而开发、测试环境 的数据库也必须要设置，这样在开发测试阶段就可以发现问题。并且我们即便是用的MySQL5.6，也应该自行将其改为严格模式。

开发经验 ：MySQL等数据库总想把关于数据的所有操作都自己包揽下来，包括数据的校验，其实开发中，我们应该在自己开发的项目程序级别将这些校验给做了，虽然写项目的时候麻烦了一些步骤，但是这样做之后，我们在进行数据库迁移或者在项目的迁移时，就会方便很多。

改为严格模式后可能会存在的问题：

若设置模式中包含了 NO_ZERO_DATE ，那么MySQL数据库不允许插入零日期，插入零日期会抛出错误而不是警告。例如，表中含字段TIMESTAMP列（如果未声明为NULL或显示DEFAULT子句）将自动分配DEFAULT '0000-00-00 00:00:00'（零时间戳），这显然是不满足sql_mode中的NO_ZERO_DATE而报错。

模式查看和设置

# 查看当前的sql_mode
select @@session.sql_mode
select @@global.sql_mode
#或者
show variables like 'sql_mode';

临时设置方式：设置当前窗口中设置sql_mode
SET GLOBAL sql_mode = 'modes...'; #全局
SET SESSION sql_mode = 'modes...'; #当前会话

如：
#改为严格模式。此方法只在当前会话中生效，关闭当前会话就不生效了。
set SESSION sql_mode='STRICT_TRANS_TABLES';
#改为严格模式。此方法在当前服务中生效，重启MySQL服务后失效。
set GLOBAL sql_mode='STRICT_TRANS_TABLES';

永久设置方式：在/etc/my.cnf中配置sql_mode。

在my.cnf文件（windows系统是my.ini文件），新增：

[mysqld]
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR
_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

然后重启MySQL 。当然生产环境上是禁止重启MySQL服务的，所以采用临时设置方式 + 永久设置方式来解决线上的问题，那么即便是有一天真的重启了MySQL服务，也会永久生效了。

0
MySQL
  1. qrcode alipay
  2. qrcode weixin
版权归属： coydone
本文链接： https://www.coydone.com/archives/135
许可协议： 本文使用《署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0)》协议授权

评论区