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 客户端对象查找工具，用来很快地查找存在哪些数据库、数据库中的表、表中的列或者索引。

客户端工具

Navicat

下载地址：Navicat | 产品

Workbench Community

下载地址：MySQL :: Download MySQL Workbench

快速使用

在 Linux 上安装

前提准备

• 查看系统版本

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

在 MacOS 上安装

使用 Homebrew 安装

• 如果你还没有安装 Homebrew，先安装 Homebrew

/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"

• 更新 Homebrew 的配方库

brew update

• 开始安装

brew install mysql

• 安装完后，它会提示你 init database，并提供下面这两句让你执行

unset TMPDIR

mysql_install_db --verbose --user=`whoami` --basedir="$(brew --prefix mysql)" --datadir=/usr/local/var/mysql --tmpdir=/tmp

4. 启动服务

brew services start mysql

5. 安装完成后，你可能需要运行 mysql_secure_installation 脚本来设置 root 用户密码、移除匿名用户、禁止 root 用户远程登录等安全设置

/usr/local/opt/mysql/bin/mysql_secure_installation

6. 登录数据库

mysql -u root -p

使用程序包安装

1. 官网下载&安装 - MySQL :: Download MySQL Community Server
2. 配置环境变量（可选，一般会自动配好）

vim ~/.bash_profile

# -->
# 编辑内容
# export PATH=$PATH:/usr/local/mysql/bin
# 或
# export PATH=$PATH:/usr/local/mysql-8.0.39-macos14-x86_64/bin
# <--

source ~/.bash_profile

3. 登录数据库

mysql -uroot -p

卸载

• 检测应用是否正在运行

# 检查服务状态
brew services list

# 如果 MySQL 正在运行，可以使用以下命令停止（假设通过 Homebrew 安装）
brew services stop mysql

• 卸载应用

通过 Homebrew 安装的 MySQL 可以使用以下命令进行卸载：

brew uninstall mysql

如果你通过其他方式（例如下载的安装包）安装了 MySQL，可能需要手动删除安装目录。一般而言，MySQL 的安装目录位于 /usr/local/mysql。你可以运行以下命令：

sudo rm -rf /usr/local/mysql

• 删除相关文件

完成卸载后，最好还需清理一些残留的数据和配置文件。

以下文件和目录通常需要删除：

i. MySQL 数据目录（默认在 /usr/local/var/mysql）

ii. 配置文件（/etc/my.cnf 或 $HOME/.my.cnf）

iii. 在 Library/LaunchDaemons 中的启动项

你可以使用以下命令删除这些文件夹和文件：

# 数据目录
sudo rm -rf /usr/local/var/mysql
sudo rm -rf /usr/local/mysql
sudo rm -rf /usr/local/mysql*
sudo rm -rf /etc/mysql
sudo rm -rf /var/db/mysql

# 删除配置文件（可选）
sudo rm -rf /etc/my.cnf
sudo rm -rf $HOME/.my.cnf 

# 删除用户主目录下的隐藏文件夹（如果存在）
sudo rm -rf /Library/StartupItems/MySQLCOM 
sudo rm -rf /Library/PreferencePanes/MySQL* 
sudo rm -rf /Library/Receipts/mysql* 
sudo rm -rf /Library/Receipts/MySQL* 
sudo rm -rf /Library/LaunchDaemons/homebrew.mxcl.mysql.plist
sudo rm -rf /Library/Application\ Support/MySQL

• 检查环境变量

在终端中运行以下命令，查看当前的环境变量：

echo $PATH

如果 PATH 中依然有指向 MySQL 的路径，需要编辑 ~/.bash_profile 或 ~/.zshrc 文件，并移除相关行：

vim ~/.bash_profile
source ~/.bash_profile
# or
vim ~/.zshrc
source ~/.zshrc

• 验证卸载成功

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;

• 使用 mysqld_safe 跳过密码验证

以下主要是在 Macos 中验证，其它系统可自行尝试

# 1 - 关闭服务
sudo /usr/local/mysql/support-files/mysql.server stop
# 2 - 进入命令行目录
cd /usr/local/mysql/bin
# 3 - 使用管理员权限
sudo su
# 4 - 禁用验证模式
./mysqld_safe --skip-grant-tables
# 5 - 登入 MySQL
./mysql
# 6 - 修改密码
FLUSH PRIVILEGES;
ALTER user 'root'@'localhost' IDENTIFIED BY '123456';
# 5 - 重启服务
sudo /usr/local/mysql/support-files/mysql.server restart

添加用户

• 使用 CREATE USER 语句来添加用户。例如：

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

# 带密码验证
CREATE USER 'username'@'hostname' IDENTIFIED WITH mysql_native_password BY 'password';

使用该语句可以在 MySQL 中创建一个名为 ‘username’ 的用户（只能在本地访问），并设置密码为 ‘password’。

• 使用 GRANT 语句来添加用户并分配权限。例如：

GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

该语句将授权给用户 ‘username’ 在 localhost 上访问指定的数据库（database）的全部权限，并设置密码为 ‘password’。最后一行的 FLUSH PRIVILEGES 用于刷新权限。

• 使用 INSERT 语句向 MySQL 的 ‘user’ 表中插入新用户的记录。例如：

INSERT INTO mysql.user (User,Host,authentication_string) VALUES('username', 'localhost', PASSWORD('password'));
FLUSH PRIVILEGES;

这种方法需要在 MySQL 服务器当中直接插入记录，而且密码字段需要使用函数 PASSWORD() 进行加密。

MySQL > 8 中可尝试使用以下命令添加用户
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'localhost' WITH GRANT OPTION;

远程连接

• 开放端口（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;

查看数据库端口

• 使用 SHOW VARIABLES 命令

SHOW VARIABLES LIKE '%port%';

• 检查配置文件

MySQL 配置文件通常位于 /etc/mysql/my.cnf（Linux）或 /usr/local/etc/my.cnf（macOS）。在配置文件中，搜索以下行：

port = <port_number></port_number>

• 使用 netstat 命令

netstat -ant | grep mysql

配置 my.cnf 文件

• 查看是否使用指定目录下的配置文件

ps aux | grep mysql |grep 'my.cnf'

如果没有输出，则说明没有使用指定目录下的 my.cnf

• 查看 mysql 默认读取 my.cnf 的目录

如果没有设置使用指定目录的my.cnf，mysql启动时会读取安装目录根目录及默认目录下的my.cnf文件。查看mysql启动时读取配置文件的默认目录

mysql --help | grep 'my.cnf'

• my.cnf 配置示例

[client]
port = 3306
socket = /tmp/mysql.sock
default-character-set = utf8

[mysqld]
collation-server = utf8_unicode_ci
character-set-server = utf8
init-connect ='SET NAMES utf8'
max_allowed_packet = 64M
bind-address = 127.0.0.1
port = 3306
socket = /tmp/mysql.sock
innodb_file_per_table=1

[mysqld_safe]
timezone = '+0:00'

备份与还原

导入数据（还原）

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

在 MacOS 中常见问题及解决办法

MySQL8 如何启用 mysql_native_password 插件

• 确认插件是否被安装

SHOW PLUGINS;

INSTALL PLUGIN mysql_native_password SONAME 'mysql_native_password';

如果已经安装，该命令会显示插件已经存在。

• 修改配置文件my.cnf或my.ini，在[mysqld]部分添加以下行

mysql_native_password=ON

注意不要添加default_authentication_plugin=mysql_native_password，否则可能会导致MySQL无法启动

• 重启MySQL服务
• 查看用户使用的插件

SELECT user, host, plugin FROM mysql.user;

• 修改密码认证方式，例如，为root用户设置mysql_native_password

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your password'; FLUSH PRIVILEGES;

系统偏好中无法关闭 MySQL 服务

• 方法一

# 查看 pid
ps -ef | grep mysqld

# 杀死进程 kill -9 <PID>
sudo kill <PID>

• 方法二

sudo /usr/local/mysql/support-files/mysql.server [start|stop|restart]

忘记 MySQL 登入密码如何处理

• 停止服务

sudo /usr/local/mysql/support-files/mysql.server stop

• 进入安全模式

sudo mysqld_safe --skip-grant-tables

• 连接 MySQL 服务

mysql -uroot -p

• 修改密码

use mysql;
update user set authentication_string=PASSWORD('123456');
flush privileges;

• 重启服务

sudo /usr/local/mysql/support-files/mysql.server restart

• 重新连接 MySQL 服务，验证是否可以登入成功

mysql -uroot -p

在 Linux 中常见问题及解决方法

安装 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*

在 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 提示密码校验方式不被允许

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

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

修改密码 ALTER USER ‘root’@‘localhost’ IDENTIFIED BY ‘123456’; 时，报错 ERROR 1396 (HY000): Operation ALTER USER failed for root@localhost

mysql 报错 ERROR 1396 (HY000): Operation ALTER USER failed for root@localhost 解决方案_error 1396(hy000)-CSDN博客

• 连接权限数据库

mysql> use mysql;

• 查看 user 主机名

mysql> select user, host from user;

• 修改密码命令

ALTER USER 'root'@'%' IDENTIFIED BY '123456';

参考文献

• Mysql命令行导入sql数据
• macOS下mysql 8.0.16 安装配置图文教程_Mysql_脚本之家