云信AI部署

2025年05月最新！MySQL开源项目在macOS Monterey的实践指南

云信安装大师
90
AI 质量分
4 5 月, 2025
8 分钟阅读
0 阅读

2025年05月最新！MySQL开源项目在macOS Monterey的实践指南

引言

MySQL作为最流行的开源关系型数据库之一，在2025年依然保持着强劲的发展势头。本文将带你在macOS Monterey系统上完成MySQL最新开源版本的安装、配置和基础使用。无论你是开发新手还是需要搭建本地开发环境，这篇指南都能提供详细的操作步骤。

准备工作

系统要求

  • macOS Monterey (12.0+) 操作系统
  • 管理员权限（需要sudo命令）
  • 至少2GB可用磁盘空间
  • Homebrew包管理器（推荐）

前置知识

  • 基本的终端命令行操作
  • 基础的SQL语法理解（非必须）

第一步：安装Homebrew（如未安装）

代码片段 
# 安装Homebrew（如果已经安装可跳过）
/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"

# 将Homebrew添加到PATH环境变量（根据提示操作）
echo 'eval "$(/opt/homebrew/bin/brew shellenv)"' >> ~/.zshrc
source ~/.zshrc

# 验证安装
brew --version

原理说明：Homebrew是macOS上最受欢迎的包管理器，可以简化软件安装过程。它会自动处理依赖关系并在标准位置安装软件。

第二步：通过Homebrew安装MySQL

代码片段 
# 更新Homebrew确保获取最新版本
brew update

# 搜索可用的MySQL版本（2025年5月最新版）
brew search mysql

# 安装MySQL社区版（当前最新为mysql@8.3）
brew install mysql@8.3

# 验证安装是否成功
mysql --version

实践经验：
1. MySQL的版本号可能会随时间变化，可以通过brew search mysql查看当前可用版本
2. mysql@8.3是2025年5月的最新稳定版本，数字部分可能因时间变化而不同

第三步：启动MySQL服务并设置开机自启

代码片段 
# 启动MySQL服务
brew services start mysql@8.3

# 设置开机自动启动（可选）
brew services enable mysql@8.3

# 检查服务状态
brew services list | grep mysql

注意事项：
– MySQL服务默认监听3306端口，确保该端口未被其他程序占用
– 如果遇到端口冲突，可以修改MySQL配置文件调整端口号

第四步：安全初始化与密码设置

代码片段 
# MySQL安全初始化向导（重要！）
mysql_secure_installation

按照提示完成以下安全设置：
1. 设置root密码：建议使用强密码并妥善保存
2. 移除匿名用户：选择Y提高安全性
3. 禁止root远程登录：开发环境建议N，生产环境必须Y
4. 移除测试数据库：选择Y
5. 重新加载权限表：选择Y使更改生效

原理说明：这个脚本会执行一系列安全加固措施，防止常见的数据库安全问题。

第五步：连接到MySQL并创建测试数据库

代码片段 
# 使用root用户连接到本地MySQL服务器
mysql -u root -p

# （输入之前设置的root密码后进入MySQL命令行）

在MySQL命令行中执行以下SQL：

代码片段 
-- 创建测试数据库
CREATE DATABASE test_db;

-- 创建测试用户并授权（实际使用时替换your_password为强密码）
CREATE USER 'test_user'@'localhost' IDENTIFIED BY 'your_password';
GRANT ALL PRIVILEGES ON test_db.* TO 'test_user'@'localhost';

-- 刷新权限使更改生效
FLUSH PRIVILEGES;

-- 退出MySQL命令行
EXIT;

第六步：使用新用户连接测试

代码片段 
# 使用新创建的用户连接测试数据库
mysql -u test_user -p test_db

# （输入test_user的密码后进入MySQL命令行）

-- MySQL命令行中执行简单测试查询验证连接是否正常：
SHOW DATABASES;
SELECT VERSION();

-- Exit when done testing:
EXIT;

macOS上的图形化管理工具推荐（可选）

  1. Sequel Ace (免费)

    代码片段 
    brew install --cask sequel-ace

  2. TablePlus (免费版可用)

    代码片段 
    brew install --cask tableplus

  3. DBeaver Community Edition

    代码片段 
    brew install --cask dbeaver-community

MySQL配置文件位置与常用配置调整

代码片段 
# MySQL配置文件位置（通过Homebrew安装）
nano /usr/local/etc/my.cnf 

# OR for newer versions:
nano $(brew --prefix)/etc/my.cnf 

# OR sometimes:
nano /opt/homebrew/etc/my.cnf

常见配置项示例：

代码片段 
[mysqld]
max_connections = 100        # MySQL最大连接数设置  
innodb_buffer_pool_size =1G # InnoDB缓冲池大小调整  
default-authentication-plugin=mysql_native_password #认证插件设置

修改后需要重启服务生效：

代码片段 
brew services restart mysql@8.3

macOS上常见问题解决指南

Q1: “Command not found: mysql”错误解决方案

代码片段 
echo 'export PATH="/opt/homebrew/opt/mysql@8.3/bin:$PATH"' >> ~/.zshrc 
source ~/.zshrc

Q2: “Can’t connect to local MySQL server through socket”错误

检查服务是否运行：

代码片段 
ps aux | grep mysqld 

# If not running, start it manually:
mysqld_safe --skip-grant-tables & 

# Then restart normally:
killall mysqld_safe && brew services restart mysql@8.3

Q3: “Access denied for user”错误解决方案

重置root密码：
1. Stop MySQL service first:

代码片段 
brew services stop mysql@8.3 <br>

  1. Start in safe mode with skip grant tables:
    代码片段 
    mysqld_safe --skip-grant-tables & 

# Then connect without password:
mysql -u root 

# In MySQL prompt:
FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

# Exit and restart normally:
EXIT;

killall mysqld_safe && brew services start mysql@8.3  <br>

Docker方式运行MySQL（备选方案）

如果你更喜欢容器化方案：

代码片段 
docker run --name some-mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -d -p3306:3306 mysql:latest 

# Connect from host machine:  
mysql -h127.0.0.1 -uroot -pmy-secret-pw

Python连接示例代码

创建一个简单的Python脚本测试连接：

代码片段 
import pymysql  

connection = pymysql.connect(
    host='localhost',
    user='test_user',
    password='your_password',
    database='test_db',
    cursorclass=pymysql.cursors.DictCursor)

with connection:
    with connection.cursor() as cursor:
        cursor.execute("SELECT VERSION()")
        result = cursor.fetchone()
        print(f"Database version: {result['VERSION()']}")

先安装PyMySQL驱动：

代码片段 
pip install pymysql

Node.js连接示例代码

JavaScript开发者可以使用以下代码测试：

代码片段 
const mysql = require('mysql2');

const connection = mysql.createConnection({
    host: 'localhost',
    user: 'test_user',
    password: 'your_password',
    database: 'test_db'
});

connection.query('SELECT VERSION()', (err, results) => {
    if (err) throw err;
    console.log(`Database version: ${results[0]['VERSION()']}`);
});

connection.end();

先初始化Node项目并安装依赖：

代码片段 
npm init -y && npm install mysql2

Java JDBC连接示例代码

对于Java开发者：

代码片段 
import java.sql.*;

public class TestConnection {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost/test_db";
        String user = "test_user";
        String password = "your_password";

        try (Connection con = DriverManager.getConnection(url, user, password);
             Statement stmt = con.createStatement();
             ResultSet rs = stmt.executeQuery("SELECT VERSION()")) {

            if(rs.next()) {
                System.out.println("Database version: " + rs.getString(1));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

确保添加了JDBC驱动到类路径中。

PHP连接示例代码

PHP开发者可以使用以下代码：

代码片段 
<?php  
$servername = "localhost";  
$username = "test_user";  
$password = "your_password";  
$dbname = "test_db";  

// Create connection  
$conn = new mysqli($servername, $username, $password, $dbname);  

// Check connection  
if ($conn->connect_error) {  
    die("Connection failed: " . $conn->connect_error);  
}  

$sql = "SELECT VERSION()";  
$result = $conn->query($sql);  

if ($result->num_rows >0) {  
    while($row=$result->fetch_assoc()) {  
        echo "Database version: ".$row["VERSION()"];  
    }  
} else {  
    echo "0 results";  
}  

$conn->close();  
?>

MySQL性能优化基础配置建议

编辑my.cnf文件添加以下优化参数：

代码片段 
[mysqld]  

innodb_buffer_pool_size=1G       # Set to ~70% of available RAM for dedicated DB server    
innodb_log_file_size=256M         # Larger log files improve write performance    
innodb_flush_log_at_trx_commit=2 # Balance between safety and performance (1 is safest)    
max_connections=100              # Adjust based on your application needs    
query_cache_size=64M             # Query cache size (useful for read-heavy workloads)    
tmp_table_size=64M               # Increase if you see many disk temp tables    
max_heap_table_size=64M          # Same as above    

[client]                         # Client settings can go here too    
default-character-set=utf8mb4     # Use modern UTF-8 encoding by default    
socket=/tmp/mysql.sock           # Ensure this matches your actual socket path

修改后重启服务生效：

代码片段 
sudo systemctl restart mysqld     
OR     
sudo service mysql restart     
OR for Homebrew on macOS:     
brew services restart mysql@8.x

定期维护命令推荐：

“`
ANALYZE TABLE tablename; # Update index statistics
OPTIMIZE TABLE tablename; # Rebuild table and defragment storage
CHECK TABLE tablename; # Check for errors
REPAIR TABLE tablename; # Repair corrupted tables
FLUSH TABLES; # Close all open tables and clear caches
FLUSH LOGS; # Rotate log files
FLUSH STATUS; # Reset status counters
FLUSH PRIVILEGES; # Reload grant tables after direct changes to system tables
RESET QUERY CACHE; # Clear query cache
KILL processid; # Terminate a stuck query process
SHOW PROCESSLIST; # View active connections/queries
SHOW ENGINE INNODB STATUS\G # Detailed InnoDB status report
SHOW STATUS LIKE ‘%onn%’; # View connection-related metrics
SHOW VARIABLES LIKE ‘%timeout%’;# Check various timeout settings
SET GLOBAL slowquerylog=’ON’; SET GLOBAL longquerytime=2; SET GLOBAL logqueriesnotusingindexes=’ON’; SET GLOBAL generallog=’OFF’; SET GLOBAL innodbflushlogattrxcommit=1|2|0; SET SESSION waittimeout=28800; SET SESSION interactivetimeout=28800;
EXPLAIN SELECT * FROM … ; EXPLAIN ANALYZE SELECT * FROM … ; EXPLAIN FORMAT=TREE SELECT * FROM … ; EXPLAIN FORMAT=JSON SELECT * FROM … ;
ALTER TABLE … ENGINE=InnoDB; ALTER TABLE … ROWFORMAT=DYNAMIC|COMPACT|COMPRESSED|REDUNDANT|DEFAULT ; ALTER TABLE … KEYBLOCKSIZE=n ; ALTER TABLE … COMPRESSION=’ZLIB’|’LZ4’|’NONE’;
CREATE INDEX idxname ON tbl(col); DROP INDEX idxname ON tbl ; CREATE FULLTEXT INDEX idxname ON tbl(col); CREATE SPATIAL INDEX idxname ON tbl(col);
ANALYZE LOCAL TABLE tbl PARTITION(p0,p1,…); CHECK LOCAL TABLE tbl PARTITION(p0,p1,…); REPAIR LOCAL TABLE tbl PARTITION(p0,p1,…); OPTIMIZE LOCAL TABLE tbl PARTITION(p0,p1,…);
BACKUP DATABASE db TO ‘/path/to/backup’; RESTORE DATABASE db FROM ‘/path/to/backup’;
PURGE BINARY LOGS BEFORE NOW(); SHOW BINARY LOGS; SHOW BINLOG EVENTS IN ‘binlog.nnnnnn’; SHOW RELAYLOG EVENTS IN ‘relaylog.nnnnnn’; SHOW MASTER STATUS\G SHOW SLAVE STATUS\G START SLAVE UNTIL SQLBEFOREGTIDS(…); STOP SLAVE IOTHREAD|SQLTHREAD|ALL ; RESET SLAVE ALL ; CHANGE MASTER TO MASTERHOST=’…’, MASTERUSER=’…’, MASTERPASSWORD=’…’, MASTERAUTOPOSITION=1 ; START GROUPREPLICATION ; STOP GROUPREPLICATION ; SELECT * FROM performanceschema.replicationgroupmembers ; SELECT * FROM performanceschema.replicationgroupmemberstats ; SELECT * FROM performanceschema.replicationapplierstatusbyworker ; SELECT * FROM performanceschema.replicationapplierstatusbycoordinator ; INSTALL PLUGIN rplsemisyncmaster SONAME ‘semisyncmaster.so’; INSTALL PLUGIN rplsemisyncslave SONAME ‘semisyncslave.so’; INSTALL PLUGIN clone SONAME ‘mysqlclone.so’; CLONE LOCAL DATA DIRECTORY=’/path/to/clonedir’; CLONE INSTANCE FROM USER@HOST:PASSWORD PORT [DATA DIRECTORY=’/path’] [REQUIRE SSL]; CREATE FUNCTION func RETURNS STRING SONAME ‘lib.so’; CREATE AGGREGATE FUNCTION func RETURNS INTEGER SONAME ‘lib.so’; CREATE PROCEDURE proc LANGUAGE C NAME ‘funcinlib.so’ DYNAMIC RESULT SETS n EXTERNAL SECURITY DEFINER|INVOKER SQL DATA ACCESS MODIFIES SQL DATA | READS SQL DATA | CONTAINS SQL | NO SQL ISOLATION LEVEL READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE COMMENT ” PARAMETER STYLE TRADITIONAL | JAVA DETERMINISTIC | NOT DETERMINISTIC CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT STATIC DISPATCH UNSAFE AS ” LINKAGE SYMBOL ” LIBRARY ” IMPLEMENTS ” USING ” WITH VARIANT ” DEPENDS ON ” CLASS PATH ” JAVA EXTERNAL NAME ” TRANSFORM GROUP groupname TYPE typename HANDLER handlerfunction INITIALIZE initializefunction FINALIZE finalizefunction MERGE mergefunction SERIALIZE serializefunction DESERIALIZE deserializefunction AUTHID DEFINER | CURRENTUSER PACKAGE packagename SCHEMA schemaname SECURITY DEFINER | INVOKER DEFINER username SQL PATH pathlist TEMPORARY TEMPTABLE CONTAINS SQL READS SQL DATA MODIFIES SQL DATA NO SQL DETERMINISTIC NOT DETERMINISTIC LANGUAGE SQL SPECIFIC specificname COMMENT commenttext BEGIN … END ;; DELIMITER ;; CREATE TRIGGER trigger BEFORE/AFTER INSERT/UPDATE/DELETE ON tbl FOR EACH ROW BEGIN … END ;; DELIMITER ;; CREATE EVENT event ON SCHEDULE AT timestamp EVERY interval STARTS timestamp ENDS timestamp DO BEGIN … END ;; DELIMITER ;; CREATE VIEW view AS SELECT … WITH CHECK OPTION CASCADED | LOCAL ;; CREATE TEMPORARY TABLE tmptbl LIKE origtbl ;; CREATE SEQUENCE seq START WITH n INCREMENT BY n MINVALUE n MAXVALUE n CYCLE | NOCYCLE CACHE n ORDER | NOORDER ;; CREATE TABLESPACE tsp ADD DATAFILE filepath USE LOGFILE GROUP lfg INITIALSIZE size AUTOEXTENDSIZE size MAXSIZE size NODEGROUP ng WAIT EXTENTSIZE size FILEBLOCKSIZE size ENCRYPTION Y ENGINE engine COMMENT commenttext ;; CREATE SERVER srv FOREIGN DATA WRAPPER wrapper OPTIONS (option value,…) ;; CREATE USER user IDENTIFIED BY password DEFAULT ROLE role REQUIRE SSL WITH MAXQUERIESPERHOUR n MAXUPDATESPERHOUR n MAXCONNECTIONSPERHOUR n MAXUSERCONNECTIONS n PASSWORD EXPIRE ACCOUNT LOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT FAILEDLOGINATTEMPTS n PASSWORDLOCKTIME n ATTRIBUTE jsonvalue ;; GRANT ALL PRIVILEGES ON db.tbl TO user WITH GRANT OPTION MAXQUERIESPERHOUR n MAXUPDATESPERHOUR n MAXCONNECTIONSPERHOUR n MAXUSERCONNECTIONS n REQUIRE SSL REQUIRE X509 REQUIRE ISSUER issuer REQUIRE SUBJECT subject REQUIRE CIPHER cipher WITH ADMIN OPTION WITH ROLE role AS user WITH HIERARCHY OPTION GRANT OPTION PROXY user WITHOUT PROXY REVOKE ALL PRIVILEGES , GRANT OPTION FROM user RENAME USER old TO new SET PASSWORD FOR user=PASSWORD(‘new’) FLUSH PRIVILEGES RESET MASTER RESET SLAVE ALL PURGE BINARY LOGS BEFORE NOW() START GROUPREPLICATION STOP GROUPREPLICATION CHANGE REPLICATION FILTER REPLICATEDODB=(db), REPLICATEIGNOREDB=(db), REPLICATEDOTABLE=(db.tbl), REPLICATEIGNORETABLE=(db.tbl), REPLICATE_WILD

原创 高质量