运维 | Linux 系统中 MySQL 的安装与使用记录

Linux 系统中 MySQL 的安装与使用记录

介绍

基本信息

在 Linux 系统中，MySQL 是一种常用的关系型数据库管理系统。

MySQL 默认安装位置：/var/lib/mysql

检查默认安装位置：

which mysql

该命令将显示 MySQL 二进制可执行文件的路径。在大多数情况下，它将显示 /usr/bin/mysql，这意味着 MySQL 被正确安装并且可以在命令行中使用。

mysql_config --variable=pkglibdir

该命令将显示 MySQL 的库文件目录，通常是 /usr/lib/mysql

数据库安装完成后，自带了以下四个数据库，具体作用如下：

• mysql - 存储 MySQL 服务器正常运行所需要的各种信息（时区、主从、用户、权限等）
• information_schema - 提供了访问数据库元数据的各种表和视图，包含数据库、表、字段类型及访问权限等
• performance_schema - 为 MySQL 服务器运行时状态提供了一个底层监控功能，主要用于收集数据库服务器性能参数
• sys - 包含了一系列方便 DBA 和开发人员你用 performance_schema 性能数据库进行性能调优和诊断的视图

常用工具

mysql

这里的 mysql 不是指 mysqld 服务，而是指 mysql 的客户端工具

• 基本用法

语法 ：
    mysql [options] [database]
选项 ：
    -u, --user=name           # 指定用户名
    -p, --password[=name]     # 指定密码
    -h, --host=name           # 指定服务器 IP 或域名（不写则默认本地 ip）
    -P, --port=port           # 指定连接端口（不写则默认端口 3306）
    -e, --execute=name        # 执行 SQL 语句并退出

-e 选项可以在 MySQL 客户端执行 SQL 语句，而不用连接到 MySQL 数据库再执行，对于一些批处理脚本，这种方式尤其方便。

例如：

mysql -uroot -p1234 itcast -e "select * from stu"

mysqldump

mysqldump 客户端工具用来备份数据库或在不同数据库之间进行数据迁移。备份内容包含创建表及插入表的 SQL 语句。

• 基本用法

语法 ：
    mysqldump [options] db_name [tables]
    mysqldump [options] --database/-B db1 [db2 db3...]
    mysqldump [options] --all-databases/-A
 
连接选项 ：
    -u, --user=name               # 指定用户名
    -p, --password[=name]         # 指定密码
    -h, --host=name               # 指定服务器ip或域名
    -P, --port=                   # 指定连接端口
 
输出选项：
    --add-drop-database           # 在每个数据库创建语句前加上 drop database 语句
    --add-drop-table              # 在每个表创建语句前加上 drop table 语句 , 默认开启; 不开启 (--skip-add-drop-table)
    -n, --no-create-db            # 不包含数据库的创建语句
    -t, --no-create-info          # 不包含数据表的创建语句
    -d, --no-data                 # 不包含数据
    -T, --tab=name                # 自动生成两个文件：一个.sql文件，创建表结构的语句；一个.txt文件，数据文件

mysqladmin

mysqladmin 是一个执行管理操作的客户端程序。可以用它来检查服务器的配置和当前状态、创建并删除数据库等。

• 基本用法

语法:
    mysqladmin [options] command ...
选项:
    -u, --user=name             # 指定用户名
    -p, --password[=name]       # 指定密码
    -h, --host=name             # 指定服务器 IP 或域名
    -P, --port=port             # 指定连接端口

示例

# 删除指定的数据库
mysqladmin -uroot –p1234 drop 'test01'
 
# 查看数据库版本信息
mysqladmin -uroot –p1234 version

mysqlbinlog

由于服务器生成的二进制日志文件以二进制格式保存，所以如果想要检查这些文本的文本格式，就会使用到 mysqlbinlog 日志管理工具。

mysqlshow

mysqlshow 客户端对象查找工具，用来很快地查找存在哪些数据库、数据库中的表、表中的列或者索引。

快速使用

前提准备

• 查看系统版本

cat /etc/redhat-release

• 检测是否安装过 mysql

rpm -qa | grep mysql

• 检测是否安装过 mariadb，若存在请卸载（该软件与 MySQL 数据库有冲突，需要手动卸载）

rpm -qa | grep mariadb

卸载

rpm -e --nodeps mariadb-libs-5.5.68-1.el7.x86_64

开始安装

1. 访问 MySQL 官网，找到 MySQL Community Server 并选择对应版本压缩包文件进行下载，上传服务器。也可以使用 wget 命令行下载：

wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.31-1.el8.x86_64.rpm-bundle.tar

MySQL 版本与 Linux 系统相对应，如：centos7 -> el7 、centos8 -> el8

2. 解压缩文件

mkdir /usr/local/mysql

tar -xvf /usr/local/mysql-8.0.31-1.el8.x86_64.rpm-bundle.tar -C /usr/local/mysql

cd /usr/local/mysql

3. 使用 rpm 命令行安装

rpm -ivh mysql-community-common-8.0.31-1.el8.x86_64.rpm

rpm -ivh mysql-community-client-plugins-8.0.31-1.el8.x86_64.rpm

rpm -ivh mysql-community-libs-8.0.31-1.el8.x86_64.rpm

rpm -ivh mysql-community-client-8.0.31-1.el8.x86_64.rpm

rpm -ivh mysql-community-icu-data-files-8.0.31-1.el8.x86_64.rpm

rpm -ivh mysql-community-devel-8.0.31-1.el8.x86_64.rpm

rpm -ivh mysql-community-server-8.0.31-1.el8.x86_64.rpm

必须按照顺序执行命令，否则会出现依赖错误的报错

4. 检测是否安装成功

mysql -V
# or
mysql --version

常用命令

查看服务状态

systemctl status mysqld

开启服务

systemctl start mysqld

停止服务（暂时关闭）

systemctl stop mysqld

重启服务

systemctl restart mysqld

设置开机自启

systemctl enable mysqld

禁止服务（永久关闭）

systemctl disable mysqld

操作指南

获取初始化密码

第一次运行 MySQL 服务时，会进行初始化加载，同时会生成一个 root 用户的初始密码，可以通过查看日志文件 /var/log/mysqld.log 获取到 root 用户的初始密码，后续可手动修改密码

cat /var/log/mysqld.log | grep 'password'

连接数据库

1. 基本用法

用法：
	mysql [OPTIONS] [database]

参数：
	-h, - 连接地址
	-u, - 用户名（若存在）
	-p, - 密码（若存在）
	-P, - 端口（默认：3306）

2. 使用实例

• 连接默认数据库

mysql -u root -p

密码默认为空（与 MySQL 版本有关），若无法连接可尝试查看初始密码进行连接

• 连接本地数据库

mysql -h localhost -uroot -p123456

• 连接指定数据库

mysql -h localhost -uroot -p123456 [database]

修改用户密码

• 修改初始化密码

alter user 'root'@'localhost' identified by '123456';

• 修改校验密码策略（可选，一般不需要修改）

# 设置密码长度的最低位数
set global validate_password.length=4;

# 设置密码的安全等级，修改密码安全策略为低（只校验密码长度，至少8位）
set global validate_password.policy=LOW;

• 使用 mysqladmin 工具修改密码

mysqladmin -uroot -p'原密码' password '新密码';

• 使用 sql 语句更改密码

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('新密码');

• 忘记 root 用户密码使用 UPDATE 直接编辑 user 表

# 进入my.cnf文件
vim /etc/my.cnf 

# 添加语句，跳过授权表
skip-grant-tables

# 重新启动 mysql
systemctl restart mysql

# 登入 mysql，此时不需要密码验证
mysql -uroot -p

# 设置新密码
update mysql.user set authentication_string=password("新密码")where User="root" and Host="localhost";

# 刷新授权
flush privileges;

远程连接

• 开放端口（3306）

firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --reload
firewall-cmd --zone=public --list-port

• 开放远程访问权限

1. 连接数据库并切换到 mysql 数据库

mysql -u root -p

use mysql;

2. 查看用户访问权限

select host, user, plugin from user;

localhost - 只可以本地访问
% - 允许任意地方访问

3. 设置用户任意地方可以访问

update user set host='%' where user='root';

4. 刷新权限

flush privileges;

备份与还原

导入数据（还原）

1. 进入 mysql

mysql -uroot -p

2. 创建新的数据库

create database test;
show databases;

3. 使用目标数据库

use test;
show tables;

4. 运行指定路径的 SQL 文件

source /root/test.sql;

5. 检验是否导入成功

show tables; # 查看所有表
desc test_tb; # 查看某张表结构
select * from test_tb; # 查询某张表

导出数据（备份）

mysqldump 是在操作系统命令行下运行的，不是在 MySQL 命令行下运行的。

一般形式：mysqldump -h [IP] -u [用户名] -p [数据库名] > [导出的文件名]

若找不到 mysqldump 命令，可尝试在安装目录 mysql/bin 下运行

• 导出所有表以及数据（不加 -d）

mysqldump -h localhost -u root -p test> ~/test.sql

• 导出所有表结构（加 -d）

mysqldump -h localhost -u root -p -d test> ~/test.sql

添加 --add-drop-table 参数：在每个 create 语句前增加一个 drop table

mysqldump -h localhost -u root -p -d  --add-drop-table test> ~/test.sql

• 导出某张表的表结构不含数据

mysqldump -h localhost -u root -p -d test test_tb> ~/test.sql

• 导出某张表的表结构和数据（不加 -d）

mysqldump -h localhost -u root -p test test_tb> ~/test.sql

• 备份多个数据库

mysqldump -h localhost -u root -p --databases test1 test2> ~/test.sql

• 备份所有的数据库

mysqldump -h localhost -u root -p --all -databases> ~/test.sql

• 导出为指定格式的数据的外部任意文件类型

一般形式：mysqldump -u root -p -T [目标目录] [dbname] [tablename] [option]

其中 option 与下文参数一样，只需要简单转换下，如：FIELDS TERMINATED BY '字符串' 更改为 --fields-terminated-by=字符

示例

mysqldump -u root -p -T G:\arcgisworkspace\zypdoc\ abc pollution "--fields-terminated-by=," 

不要任何多余的空格，也不用转移字符
-p 后面也不用写密码
注意目标目录是文件夹，文件名是表名，后缀是 .txt 文件

导出文本文件

一般形式：select [列名称] from tablename [where] into outfile '目标文件路径' [option]

参数说明

其中 option 参数常用的 5 个选项

• FIELDS TERMINATED BY '字符串'：设置字符串为字段的分割符，默认值为 \t
• FIELDS ENCLOSED BY '字符'：设置字符串括上 char varchar text 等字符型字段，默认值为 无任何符号
• FIELDS OPTIONALLY ENCLOSED BY '字符'：设置字符串括上字段的值，默认值为 无任何符号
• LINES STARTING BY '字符串'：设置每一行开头的字符，默认值为 无任何字符
• FIELDS ESCAPED BY '字符'：设置转义字符，默认值为 \
• LINES TERMINATED BY '字符串'：设置每行结束符，默认值为 \n

示例

select * from pollution into outfile 'G:\\arcgisworkspace\\zypdoc\\text2.csv'
FIELDS TERMINATED BY '\,' OPTIONALLY ENCLOSED BY '\"'
LINES STARTING BY '\>' TERMINATED BY '\r\n';

导入文本文件

1. 创建对应字段的数据表

create table csv_test2( 
id int(8) primary key, 
name varchar(64), 
value int(32) 
); 

2. 导入外部数据

LOAD DATA INFILE 'D:\\tjdata_metro\\test\\mysql_infile3.csv' 
INTO TABLE csv_test2 
FIELDS TERMINATED BY '\,' 
OPTIONALLY ENCLOSED BY '\"' 
LINES TERMINATED by'\r\n' 
ignore 1 lines 
(id,name,value); 

上面的 lines terminated by '\r\n' 是要求换行符号,为 windows 的换行

上面的 ignore 1 lines 是忽略第一行的标题行。

FAQ

安装 MySQL 失败可能存在的问题

• libc.so.6 缺失

大概率是因为下载的 MySQL 版本与 Linux 版本对应不上，可以检查一下 MySQL 压缩包后缀名里面的参数是否与系统版本匹配

• openssl 缺失

安装 openssl-devel

yum install openssl-devel -y

• perl 和 libaio 缺失

安装 perl

# 查看与 perl 相关的软件
yum list perl

# 选择安装
yum install -y perl.x86_64

安装 libaio

# 查看与 libaio 相关的软件
yum list libaio

# 选择安装
yum install -y libaio*

远程连接 MySQL 提示密码校验方式不被允许

如果提示 caching_sha2_password 加密方式不被允许，需要将其修改为 mysql_native_password

alter user 'root'@'%' identified with mysql_native_password by '123456';
flush privileges;

centos7 下登入 MySQL 出现 ERROR 1045 (28000): Access denied for user ‘root’@‘localhost’ (using password: YES)

1. 编辑配置文件 my.cnf

vim /etc/my.cnf

2. 在文件末尾添加 skip-grant-tables

# ...
skip-grant-tables
# ...

3. 重启 MySQL 服务

service mysqld restart

4. 重新连接 MySQL （不带密码，直接回车）

mysql -uroot -p

参考文献

• Mysql命令行导入sql数据