MySQL教程FG290-MySQL数据库自动化运维

内容简介：本文主要介绍MySQL存储过程参数与返回值的相关知识，包括参数类型、使用方法和最佳实践等内容。风哥教程参考MySQL官方文档MySQL Automation、MySQL Server Administration。

Part01-基础概念与理论知识

1.1 自动化运维的重要性

自动化运维可以提高效率、减少人为错误、确保操作一致性，是现代数据库管理的必备技能。 01 更多视频教程www.fgedu.net.cn

1.2 常用自动化工具

MySQL自动化运维常用工具包括： 02 学习交流加群风哥微信: itpux-com

# 常用自动化工具

1. Ansible – 配置管理和应用部署
2. Puppet – 基础设施自动化
3. Chef – 配置管理
4. SaltStack – 远程执行和配置管理
5. Shell脚本 – 自定义自动化任务
6. Python – 复杂自动化逻辑
7. MySQL Shell – MySQL专用自动化工具

Part02-生产环境规划与建议

2.1 Ansible安装配置

安装和配置Ansible： 03 学习交流加群风哥QQ113257174

# Ansible安装配置

1. 安装Ansible
yum install ansible -y

输出示例：
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
* base: mirrors.aliyun.com
* extras: mirrors.aliyun.com
* updates: mirrors.aliyun.com
Resolving Dependencies
–> Running transaction check
—> Package ansible.noarch 0:2.9.27-1.el7 will be installed
–> Processing Dependency: python2-cryptography for package: ansible-2.9.27-1.el7.noarch
…
Installed:
ansible.noarch 0:2.9.27-1.el7

Complete!

2. 验证安装
ansible –version

输出示例：
ansible 2.9.27
config file = /etc/ansible/ansible.cfg
configured module search path = [u’/root/.ansible/plugins/modules’, u’/usr/share/ansible/plugins/modules’]
ansible python module location = /usr/lib/python2.7/site-packages/ansible
executable location = /usr/bin/ansible
python version = 2.7.5 (default, Nov 16 2020, 22:23:17) [GCC 4.8.5 20150623 (Red Hat 4.8.5-44)]

3. 配置主机清单
vim /etc/ansible/hosts

[mysql_servers]
192.168.1.100 ansible_user=root ansible_ssh_pass=password
192.168.1.101 ansible_user=root ansible_ssh_pass=password
192.168.1.102 ansible_user=root ansible_ssh_pass=password

4. 测试连接
ansible mysql_servers -m ping

输出示例：
192.168.1.100 | SUCCESS => {
“ansible_facts”: {
“discovered_interpreter_python”: “/usr/bin/python”
},
“changed”: false,
“ping”: “pong”
}
192.168.1.101 | SUCCESS => {
“ansible_facts”: {
“discovered_interpreter_python”: “/usr/bin/python”
},
“changed”: false,
“ping”: “pong”
}
192.168.1.102 | SUCCESS => {
“ansible_facts”: {
“discovered_interpreter_python”: “/usr/bin/python”
},
“changed”: false,
“ping”: “pong”
}

2.2 Ansible Playbook

编写Ansible Playbook自动化MySQL部署： 04 风哥提示：

# Ansible Playbook示例

1. 创建Playbook目录
mkdir -p /ansible/mysql
cd /ansible/mysql

2. 创建主Playbook文件
vim deploy_mysql.yml

—
– name: Deploy MySQL 8.0
hosts: mysql_servers
become: yes
vars:
mysql_version: “8.0.36”
mysql_root_password: “MySecurePassword123!”
mysql_port: 3306

tasks:
– name: Install MySQL repository
yum:
name: “https://dev.mysql.com/get/mysql80-community-release-el7-11.noarch.rpm”
state: present

– name: Install MySQL server
yum:
name: “mysql-community-server-{{ mysql_version }}”
state: present

– name: Start MySQL service
service:
name: mysqld
state: started
enabled: yes

– name: Get temporary root password
shell: grep ‘temporary password’ /var/log/mysqld.log | awk ‘{print $NF}’ | tail -1
register: temp_password

– name: Change root password
shell: |
mysql -u root -p'{{ temp_password.stdout }}’ –connect-expired-password -e “ALTER USER ‘root’@’localhost’
IDENTIFIED BY ‘{{ mysql_root_password }}’;”
ignore_errors: yes

– name: Configure MySQL
template:
src: my.cnf.j2
dest: /etc/my.cnf
notify: restart mysql

– name: Open firewall port
firewalld:
port: “{{ mysql_port }}/tcp”
permanent: yes
state: enabled
immediate: yes

handlers:
– name: restart mysql
service:
name: mysqld
state: restarted

3. 创建配置文件模板
vim my.cnf.j2

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
port={{ mysql_port }}
bind-address=0.0.0.0
max_connections=500
innodb_buffer_pool_size=1G

4. 执行Playbook
ansible-playbook deploy_mysql.yml

输出示例：
PLAY [Deploy MySQL 8.0] ********************************************************

TASK [Gathering Facts] *********************************************************
ok: [192.168.1.100]
ok: [192.168.1.101]
ok: [192.168.1.102]

TASK [Install MySQL repository] ************************************************
changed: [192.168.1.100]
changed: [192.168.1.101]
changed: [192.168.1.102]

TASK [Install MySQL server] ****************************************************
changed: [192.168.1.100]
changed: [192.168.1.101]
changed: [192.168.1.102]

TASK [Start MySQL service] *****************************************************
changed: [192.168.1.100]
changed: [192.168.1.101]
changed: [192.168.1.102]

TASK [Get temporary root password] *********************************************
changed: [192.168.1.100]
changed: [192.168.1.101]
changed: [192.168.1.102]

TASK [Change root password] ****************************************************
changed: [192.168.1.100]
changed: [192.168.1.101]
changed: [192.168.1.102]

TASK [Configure MySQL] *********************************************************
changed: [192.168.1.100]
changed: [192.168.1.101]
changed: [192.168.1.102]

TASK [Open firewall port] ******************************************************
changed: [192.168.1.100]
changed: [192.168.1.101]
changed: [192.168.1.102]

RUNNING HANDLER [restart mysql] ************************************************
changed: [192.168.1.100]
changed: [192.168.1.101]
changed: [192.168.1.102]

PLAY RECAP *********************************************************************
192.168.1.100 : ok=8 changed=7 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0
192.168.1.101 : ok=8 changed=7 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0
192.168.1.102 : ok=8 changed=7 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0

Part03-生产环境项目实施方案

3.1 自动备份脚本

编写自动备份脚本： 05更多学习教程公众号风哥教程itpux_com

# 自动备份脚本

1. 创建备份脚本
vim /scripts/mysql_backup.sh

#!/bin/bash

# MySQL自动备份脚本
BACKUP_DIR=”/backup/mysql”
DATE=$(date +%Y%m%d_%H%M%S)
MYSQL_USER=”root”
MYSQL_PASSWORD=”password”
RETENTION_DAYS=7

# 创建备份目录
mkdir -p $BACKUP_DIR

# 备份所有数据库
echo “开始备份所有数据库…”
mysqldump -u$MYSQL_USER -p$MYSQL_PASSWORD –all-databases –single-transaction –flush-logs –master-data=2
> $BACKUP_DIR/full_backup_$DATE.sql

# 检查备份是否成功
if [ $? -eq 0 ]; then
echo “备份成功: $BACKUP_DIR/full_backup_$DATE.sql”
# 压缩备份文件
gzip $BACKUP_DIR/full_backup_$DATE.sql
echo “备份文件已压缩: $BACKUP_DIR/full_backup_$DATE.sql.gz”
else
echo “备份失败!”
exit 1
fi

# 删除过期备份
echo “清理过期备份…”
find $BACKUP_DIR -name “full_backup_*.sql.gz” -mtime +$RETENTION_DAYS -delete

echo “备份完成!”

2. 添加执行权限
chmod +x /scripts/mysql_backup.sh

3. 测试脚本
/scripts/mysql_backup.sh

输出示例：
开始备份所有数据库…
备份成功: /backup/mysql/full_backup_20260404_120000.sql
备份文件已压缩: /backup/mysql/full_backup_20260404_120000.sql.gz
清理过期备份…
备份完成!

4. 添加到定时任务
crontab -e

# 每天凌晨2点执行备份
0 2 * * * /scripts/mysql_backup.sh >> /var/log/mysql_backup.log 2>&1

5. 查看定时任务
crontab -l

输出示例：
0 2 * * * /scripts/mysql_backup.sh >> /var/log/mysql_backup.log 2>&1

3.2 健康检查脚本

编写数据库健康检查脚本： 06 from mysql视频:www.itpux.com

# 健康检查脚本

1. 创建检查脚本
vim /scripts/mysql_health_check.sh

#!/bin/bash

# MySQL健康检查脚本
MYSQL_USER=”root”
MYSQL_PASSWORD=”password”
LOG_FILE=”/var/log/mysql_health_check.log”
ALERT_EMAIL=”dba@fgedu.net.cn”

# 记录检查时间
echo “========================================” >> $LOG_FILE
echo “检查时间: $(date)” >> $LOG_FILE

# 检查MySQL进程
if ! pgrep -x “mysqld” > /dev/null; then
echo “[ERROR] MySQL进程未运行!” >> $LOG_FILE
echo “MySQL进程未运行” | mail -s “MySQL Alert” $ALERT_EMAIL
exit 1
fi
echo “[OK] MySQL进程运行正常” >> $LOG_FILE

# 检查连接数
CONNECTIONS=$(mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -e “SHOW STATUS LIKE ‘Threads_connected’;” | awk
‘NR==2{print $2}’)
MAX_CONNECTIONS=$(mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -e “SHOW VARIABLES LIKE ‘max_connections’;” | awk
‘NR==2{print $2}’)
CONNECTION_PERCENT=$((CONNECTIONS * 100 / MAX_CONNECTIONS))

echo “当前连接数: $CONNECTIONS / $MAX_CONNECTIONS ($CONNECTION_PERCENT%)” >> $LOG_FILE

if [ $CONNECTION_PERCENT -gt 80 ]; then
echo “[WARNING] 连接数超过80%!” >> $LOG_FILE
fi

# 检查慢查询数量
SLOW_QUERIES=$(mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -e “SHOW STATUS LIKE ‘Slow_queries’;” | awk
‘NR==2{print $2}’)
echo “慢查询数量: $SLOW_QUERIES” >> $LOG_FILE

# 检查复制状态
SLAVE_STATUS=$(mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -e “SHOW SLAVE STATUS\G” 2>/dev/null)
if [ ! -z “$SLAVE_STATUS” ]; then
IO_RUNNING=$(echo “$SLAVE_STATUS” | grep “Slave_IO_Running” | awk ‘{print $2}’)
SQL_RUNNING=$(echo “$SLAVE_STATUS” | grep “Slave_SQL_Running” | awk ‘{print $2}’)

if [ “$IO_RUNNING” != “Yes” ] || [ “$SQL_RUNNING” != “Yes” ]; then
echo “[ERROR] 复制异常! IO: $IO_RUNNING, SQL: $SQL_RUNNING” >> $LOG_FILE
else
echo “[OK] 复制运行正常” >> $LOG_FILE
fi
fi

# 检查磁盘空间
DISK_USAGE=$(df -h /var/lib/mysql | awk ‘NR==2{print $5}’ | sed ‘s/%//’)
echo “磁盘使用率: $DISK_USAGE%” >> $LOG_FILE

if [ $DISK_USAGE -gt 85 ]; then
echo “[WARNING] 磁盘使用率超过85%!” >> $LOG_FILE
fi

echo “检查完成” >> $LOG_FILE

2. 添加执行权限
chmod +x /scripts/mysql_health_check.sh

3. 测试脚本
/scripts/mysql_health_check.sh

输出示例：
查看日志文件
tail -20 /var/log/mysql_health_check.log

========================================
检查时间: Fri Apr 4 12:00:00 CST 2026
[OK] MySQL进程运行正常
当前连接数: 45 / 500 (9%)
慢查询数量: 12
[OK] 复制运行正常
磁盘使用率: 45%
检查完成

Part04-生产案例与实战讲解

4.1 Python连接MySQL

使用Python连接和操作MySQL：

# Python连接MySQL

1. 安装MySQL连接库
pip install mysql-connector-python

输出示例：
Collecting mysql-connector-python
Downloading mysql_connector_python-8.0.36-cp39-cp39-manylinux1_x86_64.whl (25.0 MB)
|████████████████████████████████| 25.0 MB 12.3 MB/s
Collecting protobuf<=3.20.3,>=3.11.0
Downloading protobuf-3.20.3-cp39-cp39-manylinux_2_5_x86_64.manylinux1_x86_64.whl (1.0 MB)
|████████████████████████████████| 1.0 MB 45.6 MB/s
Installing collected packages: protobuf, mysql-connector-python
Successfully installed mysql-connector-python-8.0.36 protobuf-3.20.3

2. 创建Python脚本
vim mysql_automation.py

#!/usr/bin/env python3
import mysql.connector
from mysql.connector import Error

def connect_to_mysql():
try:
connection = mysql.connector.connect(
host=’localhost’,
database=’test_db’,
user=’root’,
password=’password’
)
if connection.is_connected():
db_info = connection.get_server_info()
print(f”成功连接到MySQL服务器，版本: {db_info}”)
cursor = connection.cursor()
cursor.execute(“SELECT DATABASE();”)
record = cursor.fetchone()
print(f”当前数据库: {record[0]}”)
return connection
except Error as e:
print(f”连接MySQL时出错: {e}”)
return None

def get_database_stats(connection):
try:
cursor = connection.cursor(dictionary=True)

# 获取连接数
cursor.execute(“SHOW STATUS LIKE ‘Threads_connected’;”)
result = cursor.fetchone()
print(f”当前连接数: {result[‘Value’]}”)

# 获取数据库大小
cursor.execute(“””
SELECT table_schema,
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS size_mb
FROM information_schema.tables
GROUP BY table_schema;
“””)
results = cursor.fetchall()
print(“\n数据库大小:”)
for row in results:
print(f” {row[‘table_schema’]}: {row[‘size_mb’]} MB”)

except Error as e:
print(f”获取统计信息时出错: {e}”)

if __name__ == “__main__”:
conn = connect_to_mysql()
if conn:
get_database_stats(conn)
conn.close()
print(“\nMySQL连接已关闭”)

3. 执行Python脚本
python3 mysql_automation.py

输出示例：
成功连接到MySQL服务器，版本: 8.0.36
当前数据库: test_db
当前连接数: 5

数据库大小:
information_schema: 0.0 MB
mysql: 8.52 MB
performance_schema: 0.0 MB
sys: 0.02 MB
test_db: 156.78 MB

MySQL连接已关闭

Part05-风哥经验总结与分享

5.1 自动化原则

MySQL自动化运维的最佳实践：

# 自动化运维最佳实践

1. 版本控制
– 所有脚本和配置文件使用Git管理
– 记录变更历史和版本信息
– 代码审查和测试流程

2. 安全性
– 敏感信息加密存储
– 使用密钥管理工具
– 最小权限原则

3. 可重复性
– 脚本幂等性设计
– 错误处理和回滚机制
– 详细的日志记录

4. 监控和告警
– 自动化任务监控
– 失败告警机制
– 性能指标收集

5. 文档化
– 脚本使用说明
– 配置参数文档
– 故障处理手册