MySQL笔记

CentOS 7 手动安装 MySQL 5.6.38 笔记

环境说明

• 操作系统：CentOS 7

• MySQL 版本：5.6.38（二进制包）

• 安装位置：/opt/mysql

• 安装方式：手动初始化，非 yum/rpm 安装

1. 检查系统是否已安装 MySQL

# 检查命令是否存在
mysql –version
​
# 检查服务状态
systemctl status mysql
​
# 检查 RPM 包
rpm -qa | grep mysql

结论：系统未安装 MySQL 服务。

2. 发现已存在的 MySQL 二进制包

cd /opt
ll

输出包含：

• mysql-5.6.38-linux-glibc2.12-x86_64.tar.gz（安装包）

• mysql目录（已解压）

说明：二进制包已解压，但未完成初始化和服务配置。

3. 准备 MySQL 运行用户与目录权限

# 创建 mysql 用户（如果不存在）
useradd mysql
​
# 进入 MySQL 目录
cd /opt/mysql
​
# 修正目录所有权（递归）
chown -R mysql:mysql /opt/mysql

⚠️ 注意：chown -R mysql:mysql /opt/mysql会将所有文件属主改为 mysql，包括 bin 下的可执行文件。生产环境可更精细地控制，但实验环境无影响。

4. 编写配置文件 /opt/mysql/my.cnf

[client]
default-character-set=utf8
socket=/tmp/mysql.sock
​
[mysql]
default-character-set=utf8
​
[mysqld]
socket=/tmp/mysql.sock
basedir=/opt/mysql
datadir=/opt/mysql/data
symbolic-links=0
character_set_server=utf8
lower_case_table_names=1
log-error=/opt/mysql/mysql-err.log
pid-file=/opt/mysql/mysqld.pid
​
[mysqld_safe]
log-error=/opt/mysql/mysql-err.log
pid-file=/opt/mysql/mysqld.pid

注意：[mysqld_safe]段不能有 default-character-set，否则启动警告。

5. 初始化数据库

cd /opt/mysql
scripts/mysql_install_db –user=mysql –defaults-file=/opt/mysql/my.cnf –basedir=/opt/mysql –datadir=/opt/mysql/data

初始化成功输出：

Installing MySQL system tables…OK
Filling help tables…OK

提示设置 root 密码及启动方法。

初始化后会在 /opt/mysql/data下生成系统数据库文件。

6. 准备日志文件并启动 MySQL

# 创建错误日志文件
touch /opt/mysql/mysql-err.log
chown mysql:mysql /opt/mysql/mysql-err.log
​
# 手动启动
/opt/mysql/bin/mysqld_safe –defaults-file=/opt/mysql/my.cnf –user=mysql &

# 验证启动
ps aux | grep mysqld
/opt/mysql/bin/mysql -u root   # 初始无密码，可直接登录

7. 设置 root 密码

/opt/mysql/bin/mysqladmin -u root password '123'

⚠️ 警告：命令行明文密码不安全，仅实验环境使用。生产环境请用 mysql_secure_installation或交互式设置强密码。

测试登录：

/opt/mysql/bin/mysql -u root -p
# 输入密码: 123
​
mysql> SELECT VERSION();

8. 配置 systemd 开机自启

8.1 创建服务文件

vim /etc/systemd/system/mysql.service

内容如下：

[Unit]
Description=MySQL Server
After=network.target
​
[Service]
User=mysql
Group=mysql
Type=forking
ExecStart=/opt/mysql/bin/mysqld_safe –defaults-file=/opt/mysql/my.cnf –user=mysql
ExecStop=/opt/mysql/bin/mysqladmin -u root -p123 shutdown
Restart=on-failure
RestartSec=10
​
[Install]
WantedBy=multi-user.target

注意：ExecStop中的密码需与实际 root 密码一致。如果后续修改密码，需同步更新此文件。

8.2 加载并启用服务

systemctl daemon-reload
systemctl enable mysql     # 设置开机自启
systemctl start mysql       # 立即启动（测试）
systemctl status mysql     # 查看状态，应显示 active (running)

8.3 验证自启

重启虚拟机后，执行 systemctl status mysql或尝试连接数据库，确认服务已自动运行。

9. 可选：将 MySQL 加入 PATH

echo 'export PATH=$PATH:/opt/mysql/bin' >> ~/.bashrc
source ~/.bashrc

之后可直接执行：

mysql -u root -p

10. 常见问题及解决

重要提醒

# 防火墙开放3306端口示例
firewall-cmd –zone=public –add-port=3306/tcp –permanent
firewall-cmd –reload

本地连接 Linux 虚拟机 MySQL 完整笔记（含常见错误与解法）

一、前提条件

• Linux 虚拟机（如 CentOS）中已安装 MySQL。

• 宿主机（Windows / macOS）与虚拟机之间网络互通（能互相 ping 通）。

• 宿主机上已安装 MySQL 客户端（或使用 IDE 的数据库工具）。

二、MySQL 服务端配置（必做项）

1. 允许远程连接：修改绑定地址

MySQL 默认只监听 127.0.0.1，禁止远程访问。

配置文件：/etc/my.cnf 或 /opt/mysql/my.cnf（取决于安装位置）

修改：在 [mysqld] 段添加或修改：

ini

bind-address = 0.0.0.0

重启 MySQL：

bash

sudo systemctl restart mysql   # 或 mysqld

验证：

bash

netstat -tulnp | grep 3306

应显示 0.0.0.0:3306，而非 127.0.0.1:3306。

2. 授权远程用户

登录 MySQL：

bash

mysql -u root -p

执行授权（以 root 为例，密码设为 123）：

sql

— 方法1：创建或修改用户并授权
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123' WITH GRANT OPTION;
FLUSH PRIVILEGES;

注意：MySQL 5.6 中 GRANT … IDENTIFIED BY 可用，MySQL 8.0 需分开执行 CREATE USER 和 GRANT。

验证用户是否存在：

sql

SELECT host, user FROM mysql.user WHERE user='root';

MySQL 5.6 特别提醒：如果 authentication_string 为空，请检查 Password 列：

sql

SELECT host, user, Password FROM mysql.user WHERE user='root';

若 Password 为空，需更新：

sql

UPDATE mysql.user SET Password = PASSWORD('123') WHERE user='root' AND host='%';
FLUSH PRIVILEGES;

3. 开放防火墙端口

CentOS 7+ (firewalld)：

bash

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

Ubuntu (ufw)：

bash

sudo ufw allow 3306/tcp

旧版 iptables：

bash

sudo iptables -A INPUT -p tcp –dport 3306 -j ACCEPT
sudo service iptables save

4. 检查 SELinux（CentOS/RHEL）

如果 SELinux 阻止 MySQL 端口，可临时关闭测试：

bash

sudo setenforce 0

永久关闭需修改 /etc/selinux/config 中的 SELINUX=disabled。

三、宿主机连接命令与易错点

1. 正确连接命令

bash

mysql -h 虚拟机IP -u root -p密码

示例：

bash

mysql -h 192.168.21.100 -u root -p123

2. 常见错误及原因

3. 命令格式易错点

• ❌ mysql -h 192.168.21.100 -u root -p 123-p 和密码之间有空格 → MySQL 会把 123 当作数据库名，密码视为空。

• ✅ mysql -h 192.168.21.100 -u root -p123或 mysql -h 192.168.21.100 -u root -p 回车后再输入密码。

四、MySQL 5.6 版本特殊注意事项

• 密码列：mysql.user 表中实际存储密码的是 Password 列，而非 authentication_string。后者在 5.6 中无用。

• 查看密码：SELECT host, user, Password FROM mysql.user;

• 设置密码：UPDATE mysql.user SET Password = PASSWORD('新密码') WHERE user='root' AND host='%'; FLUSH PRIVILEGES;

• SET PASSWORD 命令：SET PASSWORD FOR 'root'@'%' = PASSWORD('123'); 也能工作（它会更新 Password 列），但有时不生效时请用 UPDATE。

五、调试步骤（连接不上时按顺序检查）

六、完整成功案例（基于之前操作）

虚拟机 (CentOS 7, MySQL 5.6 手动安装)：

• 路径：/opt/mysql

• 配置文件：/opt/mysql/my.cnf 中设置 bind-address=0.0.0.0

• 防火墙：开放 3306/tcp

• MySQL 用户：

  sql

  UPDATE mysql.user SET Password = PASSWORD('123') WHERE user='root' AND host='%';
  FLUSH PRIVILEGES;

  ​

  ​

宿主机 (Windows)：

• 命令：mysql -h 192.168.21.100 -u root -p123

• 结果：成功进入 MySQL 命令行。

七、总结口诀

text

ping 通服务开，绑定零全接。
防火墙放行，授权密码在。
MySQL 五和六，Password 列要查。
命令 -p 不空格，远程连上来。

按照以上笔记逐步排查，即可顺利从本地连接到 Linux 虚拟机中的 MySQL。

MySQL 运维与优化教案

本教案涵盖 MySQL 基础操作、索引优化、SQL 性能分析、视图与存储过程、锁机制、日志管理、主从复制及 MyCAT 读写分离。适合作为运维与开发人员的系统学习材料。

一、数据库与表基础操作

1.1 查看当前数据库

sql

SELECT DATABASE();

1.2 创建数据库并切换

sql

CREATE DATABASE itheima;
USE itheima;

1.3 创建表（示例 tb_user）

sql

CREATE TABLE `tb_user` (
`id` int NOT NULL COMMENT '编号',
`name` varchar(20) NOT NULL COMMENT '姓名',
`phone` varchar(11) DEFAULT NULL COMMENT '手机号',
`email` varchar(50) DEFAULT NULL COMMENT '邮箱',
`profession` varchar(30) DEFAULT NULL COMMENT '专业',
`age` int DEFAULT NULL COMMENT '年龄',
`gender` tinyint DEFAULT NULL COMMENT '性别（1男 2女）',
`status` int DEFAULT NULL COMMENT '状态（0正常 其他数值自定义）',
`createtime` datetime DEFAULT NULL COMMENT '入职时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

1.4 插入测试数据（见笔记中 INSERT 语句，此处略）

二、索引管理与优化

2.1 查看索引

sql

SHOW INDEX FROM tb_user;

2.2 创建普通索引、唯一索引、联合索引

sql

CREATE INDEX idx_use_name ON tb_user(name);
CREATE UNIQUE INDEX idx_use_phone ON tb_user(phone);
CREATE INDEX idx_use_profession_age_status ON tb_user(profession, age, status);

2.3 删除索引

sql

DROP INDEX idx_use_profession_age_status ON tb_user;

2.4 通过 ALTER 添加索引

sql

ALTER TABLE tb_user ADD INDEX idx_use_email(email);

2.5 前缀索引（提高长字符串字段的效率）

sql

CREATE INDEX idx_use_name_prefix ON tb_user(name(3));
— 适用场景：WHERE name LIKE '张%' 等前缀匹配

2.6 索引失效常见情况

• 对索引列使用函数或运算

• 字符串类型字段不加引号

• 模糊查询以 % 开头（如 LIKE '%张'）

• OR 连接的列中有未索引的列

2.7 索引设计原则

• 数据量大的表才考虑索引

• 选择区分度高的列

• 联合索引遵循最左前缀原则

• 避免过多索引，维护成本高

三、SQL 性能分析工具

3.1 查看 SQL 执行频率

sql

SHOW GLOBAL STATUS LIKE 'Com_______';

3.2 慢查询日志

sql

— 查看状态
SHOW VARIABLES LIKE 'slow_query_log';
— 开启
SET GLOBAL slow_query_log = 1;
— 设置阈值（秒）
SET GLOBAL long_query_time = 1;

3.3 Profile 分析

sql

— 查看是否支持
SELECT @@have_profiling;
— 开启
SET profiling = 1;
— 查看 SQL 耗时
SHOW PROFILES;
— 查看详细 CPU 等耗时
SHOW PROFILE CPU FOR QUERY 41;

3.4 EXPLAIN 执行计划

sql

EXPLAIN SELECT * FROM tb_user WHERE id = 1;
— 重点关注 type、key、rows、Extra 等字段

四、视图、存储过程与变量

4.1 视图

sql

— 创建/替换视图
CREATE OR REPLACE VIEW stu_v_1 AS SELECT id,name,age FROM tb_user WHERE id <= 10;
— 查询视图
SELECT * FROM stu_v_1 WHERE id > 5;
— 删除视图
DROP VIEW stu_v_1;
— 带检查选项的视图
CREATE OR REPLACE VIEW stu_v_1 AS SELECT id,name,age FROM tb_user WHERE id >= 0 WITH CASCADED CHECK OPTION;

4.2 存储过程

sql

— 创建
CREATE PROCEDURE proc_1()
BEGIN
SELECT COUNT(*) FROM tb_user;
END;
— 调用
CALL proc_1();
— 查看存储过程
SHOW PROCEDURE STATUS;
— 删除
DROP PROCEDURE proc_1;

4.3 变量

• 系统变量：SHOW SESSION VARIABLES; / SHOW GLOBAL VARIABLES;

• 用户自定义变量：SET @myname = 'itcast'; SELECT @myage := 18;

• 局部变量（存储过程内）：DECLARE myname VARCHAR(20);

五、锁机制

5.1 全局锁

sql

FLUSH TABLES WITH READ LOCK; — 全局只读
UNLOCK TABLES; — 解锁

5.2 表锁

sql

LOCK TABLES tb_user READ; — 读锁
LOCK TABLES tb_user WRITE; — 写锁
UNLOCK TABLES;

5.3 行锁（InnoDB 自动加）

• 共享锁：SELECT … LOCK IN SHARE MODE;

• 排他锁：SELECT … FOR UPDATE;

• INSERT/UPDATE/DELETE 自动加排他锁

5.4 注意事项

• 行锁基于索引，无索引可能升级为表锁

• 可重复读隔离级别下，范围查询会加间隙锁

• 避免长事务，减少锁等待

六、日志管理

6.1 错误日志

sql

SHOW VARIABLES LIKE '%log_error%';
— 系统命令查看最后50行
— tail -50 /var/log/mysql/error.log

6.2 二进制日志（binlog）

sql

— 查看是否开启
SHOW VARIABLES LIKE 'log_bin';
— 查看日志文件列表
SHOW BINARY LOGS;
— 查看当前正在写入的文件
SHOW MASTER STATUS;
— 查看 binlog 事件
SHOW BINLOG EVENTS IN 'mysql-bin.000001';
— 删除所有 binlog
RESET MASTER;
— 删除指定编号之前的日志
PURGE MASTER LOGS TO 'mysql-bin.000010';
— 删除7天前的日志
PURGE MASTER LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY);

6.3 binlog 格式

• STATEMENT：记录 SQL 语句（日志小，不安全）

• ROW：记录行变化（安全，日志略大，推荐）

• MIXED：混合模式

sql

SHOW VARIABLES LIKE 'binlog_format';
SET GLOBAL binlog_format = 'ROW';

6.4 慢查询日志

sql

— 查看状态及文件位置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
— 开启并设置阈值
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 2;
— 测试慢查询
SELECT SLEEP(3);

七、MySQL 常用管理工具

八、主从复制与读写分离（基于 MyCAT）

8.1 环境

• 主库 Master：192.168.21.100，root 密码 123

• 从库 Slave：192.168.21.122，root 密码 123

• MyCAT 中间件：部署于主库机器，端口 8066

8.2 主库配置

编辑 /opt/mysql/my.cnf：

ini

[mysqld]
server-id = 1
log-bin = /opt/mysql/data/mysql-bin
binlog_format = ROW

重启 MySQL，创建复制用户：

sql

CREATE USER 'repl'@'192.168.21.%' IDENTIFIED BY 'repl123';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.21.%';
FLUSH PRIVILEGES;
SHOW MASTER STATUS; — 记录 File 和 Position

8.3 从库配置

编辑 /opt/mysql/my.cnf：

ini

[mysqld]
server-id = 2
relay-log = /opt/mysql/data/mysql-relay-bin

重启从库，并配置主库连接：

sql

CHANGE MASTER TO
MASTER_HOST = '192.168.21.100',
MASTER_USER = 'repl',
MASTER_PASSWORD = 'repl123',
MASTER_LOG_FILE = 'mysql-bin.000009',
MASTER_LOG_POS = 512;
START SLAVE;
SHOW SLAVE STATUS\\G — 确保 Slave_IO_Running 和 Slave_SQL_Running 均为 Yes

8.4 MyCAT 读写分离配置

schema.xml

xml

<schema name="db_01" checkSQLschema="true" sqlMaxLimit="100">
<table name="tb_order" dataNode="dn1" />
</schema>
<dataNode name="dn1" dataHost="dhost1" database="db01" />
<dataHost name="dhost1" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="192.168.21.100:3306" user="root" password="123">
<readHost host="hostS1" url="192.168.21.122:3306" user="root" password="123" />
</writeHost>
</dataHost>

• balance=1：读请求分发到 writeHost 和 readHost，实现读写分离。

server.xml

xml

<user name="root" defaultAccount="true">
<property name="password">123</property>
<property name="schemas">db_01</property>
</user>

8.5 MyCAT 启动与自启

bash

# 创建 systemd 服务
cat > /etc/systemd/system/mycat.service <<EOF
[Unit]
Description=MyCAT Server
After=network.target
[Service]
Type=forking
ExecStart=/usr/local/mycat/bin/mycat start
ExecStop=/usr/local/mycat/bin/mycat stop
ExecReload=/usr/local/mycat/bin/mycat restart
User=root
Group=root
Restart=on-failure
[Install]
WantedBy=multi-user.target
EOF

systemctl daemon-reload
systemctl start mycat
systemctl enable mycat

8.6 测试读写分离

九、分库分表知识点（概念层面）

十、常见问题及解决

10.1 远程连接拒绝：Access denied for user 'root'@'boot'

• 原因：MySQL 主机名解析干扰

• 解决：在 /opt/mysql/my.cnf 添加 skip-name-resolve，重启并重置 'root'@'%' 密码

10.2 My CAT 启动失败，日志报 注释中不允许出现字符串 "–"

• 原因：XML 注释中包含连续短横线

• 解决：删除或修改注释，避免 —

10.3 MyCAT 执行 SQL 报 Invalid DataSource:0

• 原因：My CAT 无法连接后端 MySQL

• 解决：检查主从库是否可远程连接，确保物理数据库 db01 存在

10.4 表不存在 Table 'db01.tb_order' doesn't exist

• 原因：表名大小写不一致或表未创建

• 解决：统一使用小写表名，在后端主库创建表