【数据库】macos官网/brew安装mysql5.7/8/9，修改cnf配置/身份验证插件，sqldump数据库备份与恢复

【数据库】macos官网/brew安装mysql5.7/8/9，修改cnf配置/身份验证插件，sqldump数据库备份与恢复

1、mysql安装

homebrew 安装 mysql9.0

安装

# 安装
brew install mysql

# 启动
brew services list
sudo brew services start mysql

# 登录
mysql -u root -p

# 官方启动
mysql.server start
mysql.server stop
mysql.server restart

# 报错解决（打不开）
☁  ~  mysql ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

设置初始密码

mysql_secure_installation


xxx ~ % mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root: 
Error: Access denied for user 'root'@'localhost' (using password: YES)
xxx ~ % mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root: 

VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?

Press y|Y for Yes, any other key for No: y

There are three levels of password validation policy:

LOW    Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary                  file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 0
Using existing password for root.

Estimated strength of the password: 100
Change the password for root ? ((Press y|Y for Yes, any other key for No) : y

New password: 

Re-enter new password: 

Estimated strength of the password: 100
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
 ... Failed! Error: Your password does not satisfy the current policy requirements

New password: 

Re-enter new password: 

Estimated strength of the password: 100 
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : n

 ... skipping.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : 

 ... skipping.
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : 

 ... skipping.
All done! 

查看进程

ps aux|grep mysqld
lsof -i :3306

文件权限

sudo chown -R $(whoami):admin /opt/homebrew/var/mysql
sudo chown -R $(whoami):admin /opt/homebrew/opt/mysql
sudo chown -R $(whoami):admin ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist

官网安装mysql 5.7社区版-归档版

直接执行启动命令
/opt/homebrew/opt/mysql/bin/mysqld_safe --datadir=/opt/homebrew/var/mysql

最后解决方案，brew换成5.7（（（

# brew停用
brew services stop mysql

# 卸载新版本
brew uninstall mysql
Uninstalling /opt/homebrew/Cellar/mysql/9.0.1_6... (319 files, 309.8MB)
Error: Could not remove mysql keg! Do so manually:
  sudo rm -rf /opt/homebrew/Cellar/mysql/9.0.1_6

# 搜索旧版本安装
brew list mysql
brew search mysql
brew install mysql@5.7

提示Error: mysql@5.7 has been disabled because it is not supported upstream! It was disabled on 2024-08-01.
饶过一下,在安装命令前设置环境变量HOMEBREW_NO_INSTALL_FROM_API=1，告诉Homebrew使用编辑后的Formula而不是API中的版本:

# 安装停止维护版
HOMEBREW_NO_INSTALL_FROM_API=1 brew install mysql@5.7

救不了了，换官网下载
往下拉找到 MySQL Community (GPL) Downloads, MySQL Community Server
– MySQL官方宣布了MySQL 5.7.44是MySQL 5.7系列的最终版本，并于2023年10月31日起停止维护。

因此官网直接下载不了
去归档的页面找mysql5.7，成功下载dmg

默认安装完在 /usr/local/mysql/bin/（不一定有环境变量，可以ps aux找一下）
注意gui安装结束后弹窗有个临时密码记一下

结论

• 1、5.7的mysqldump最终可用
• 2、再次连上本地mysql

2、修改cnf配置/身份验证方式

起因：修改密码验证方式为mysql_native_password

起因是：新版 mysql 使用 mysqldump 旧版本的数据库会报错
具体原因是 MySQL 8.0 及更高版本中默认的身份验证插件从 mysql_native_password 变更为 caching_sha2_password ，而客户端可能不支持新的身份验证插件。

mysqldump: Got error: 2059: Authentication plugin 'mysql_native_password' cannot be loaded: dlopen(/opt/homebrew/Cellar/mysql/9.0.1_6/lib/plugin/mysql_native_password.so, 0x0002)

查看当前用户的身份验证方式

1、登录到 MySQL 数据库，查看plugin列的数据
mysql -u root -p 
SELECT * FROM mysql.user;

2、将用户的身份验证方式更改为 mysql_native_password（如果插件已安装的话）
ALTER USER '<USERNAME>'@'<HOST>' IDENTIFIED WITH mysql_native_password BY '<PASSWORD>';

3、如果插件未安装
SHOW PLUGINS;  # 查看插件
INSTALL PLUGIN mysql_native_password SONAME 'mysql_native_password';  # 尝试手动安装

尝试在 MySQL 配置文件中添加

[mysqld]
mysql_native_password=ON

加了mysql_native_password=ON 配置导致mysql启动不了

问题：如何查找mysql配置文件位置（详细）

通用

• 1、在基于 Debian/Ubuntu 的系统中，该文件位于 /etc/mysql/ 目录。推荐为此创建新文件，比如 /etc/mysql/conf.d/enable-mysql-native-password.cnf。
• 2、mac 通常：/usr/local/etc/my.cnf
• 3、如果你使用的是 Homebrew 安装的 MySQL，配置文件可能位于 /usr/local/etc/my.cnf，或者在 ~/.my.cnf（当前用户的主目录下）。如果这些位置都没有找到，你可能需要检查 Homebrew 的安装日志或者使用 Homebrew 的命令来查找配置文件的位置

一些可能的查找配置文件路径的方法：

1、
brew info mysql

2、
SHOW VARIABLES LIKE 'datadir'; # 数据目录的路径，但不会直接显示配置文件的路径。MySQL 的配置文件路径通常在数据目录的父目录中。
SHOW VARIABLES LIKE 'myisam_base_dir'; # MyISAM 表的基目录，这通常是配置文件所在的目录

mysql --help|grep 'my.cnf' 
order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /opt/homebrew/etc/my.cnf ~/.my.cnf 

ps aux | grep mysql
# -a 显示所有用户正在运行的进程，但会忽略没有命令行终端控制的进程，除非指定 -x 选项
# -u 显示特定用户用户的进程

3、Mysql导入与导出（数据备份）

基础概念：

• 备份，将当前已有的数据或记录另存一份；
• 还原，将数据恢复到备份时的状态。

数据备份与还原的方式有很多种，具体可以分为：

• 数据表备份
• 单表数据备份
• SQL备份和增量备份。

数据表备份 - innoDB存储引擎

• 想要进行数据表备份是有前提条件的，因为不同的存储引擎之间是有区别的。对于存储引擎，MySQL 主要使用两种，分别为： InnoDB 和 Myisam，两者均免费。
• MySQL5.5后默认使用的存储引擎，目前稳定的两个版本是5.7和8.0，因此都是innoDB即可。
• InnoDB：只有表结构，数据全部存储到ibd文件中。Myisam：表、数据和索引全部单独分开存储；

sqldump单表数据备份

sqldump备份工具

• mysqldump命令执行时，可以将数据库备份成一个文本文件，该文件中实际上包含多个create和insert语句，使用这些语句可以重新创建表和插入数据。
• 查出需要备份的标的结构，在文本文件中生成一个create语句
• 将表中的所有记录转换成一条insert语句。

# 命令
mysqldump -u 用户名称 -h 主机名称 -p密码 待备份的数据库名称[tbname,[tbname...]] > 备份文件名称.sql

# 备份整个数据库
mysqldump -u root -h host -p dbname > backdb.sql

# 备份数据库中的某个表
mysqldump -u root -h host -p dbname tbname1, tbname2 > backdb.sql

# 备份多个数据库
mysqldump -u root -h host -p --databases dbname1, dbname2 > backdb.sql

# 备份系统中所有数据库
mysqldump -u root -h host -p --all-databases > backdb.sql

sqldump数据恢复

使用mysqldump命令将数据库中的数据备份成一个文本文件，需要恢复时，可以使用mysql命令来恢复备份的数据。
mysql命令可以执行备份文件中的create语句和insert语句，通过create语句来创建数据库和表，通过insert语句来插入备份的数据。

注意，因为有DROP TABLE IF EXISTS xxx;
所以备份期间的数据都会成功删掉
建议执行数据恢复前，可以先rename一下原先的数据表
RENAME TABLE old_table_name TO new_table_name;

# 执行
# 其中，dbname参数表示数据库名称，该参数是可选参数。指定数据库名称时，表示还原该数据库下的表。此时需要确保MySQL服务器中已经创建了该名的数据库。不指定数据库名时，表示还原文件中所有的数据库。此时SQL文件中包含有create database语句，不需要MySQL服务器中已存在这些数据库。
mysql -u 用户名 -p [dbname] < 备份文件.sql

参考资料：1, 2, 3, 5，6， 7，8，9