MySQL数据库

MySQL 管理和备份指南

---

1. 查看和管理进程

查看当前进程：SHOW PROCESSLIST

使用 SHOW PROCESSLIST 命令可以查看 MySQL 服务器上当前运行的线程信息。

示例：

mysql> SHOW PROCESSLIST;
+----+-----------+-----------+-----------+---------+------+-------+------------------+
| Id | User      | Host      | db        | Command | Time | State | Info             |
+----+-----------+-----------+-----------+---------+------+-------+------------------+
| 34 | monddprod | localhost | monddprod | Sleep   | 1328 |       | NULL             |
| 43 | root      | localhost | NULL      | Query   |    0 | NULL  | SHOW PROCESSLIST |
+----+-----------+-----------+-----------+---------+------+-------+------------------+
2 rows in set (0.00 sec)

可以使用 mysqladmin processlist 查看相同的信息。

---

终止进程：KILL

使用 KILL 命令终止特定的进程，例如一个占用资源较多的查询：

mysql> KILL 342;
Query OK, 0 rows affected (0.00 sec)

命令行替代方法：

mysqladmin kill 342

---

2. 数据备份和恢复

使用 mysqldump 备份

示例：

mysqldump --opt -h 192.168.2.105 -u john -p'****' mybase | gzip > mybase-`date +%Y%m%d`.sql.gz

说明：

• --opt：启用一组通用优化选项（默认启用），包括：
  • --add-drop-table：在每个表之前添加 DROP TABLE 语句。
  • --lock-tables：锁定表以确保一致性。
  • --quick：逐行读取数据以减少内存使用。
• 如果不指定数据库名称，将备份所有数据库。

---

自动化备份：结合 logrotate

配置示例（每日备份，保留 60 天）：

/dumps/mybase.sql.gz {
    rotate 60
    dateext
    dateyesterday
    daily
    nocompress
    nocopytruncate
    postrotate
      HOME=/root mysqldump --opt mybase | gzip > /dumps/mybase.sql.gz
    endscript
}

多数据库备份示例：

/dumps/*/*.sql.gz {
    daily
    rotate 20
    dateext
    dateyesterday
    nocompress
    sharedscripts
    create
    postrotate
        export HOME=/root
        for i in $(mysql --batch --skip-column-names -e 'SHOW DATABASES' | grep -vE '^information_schema|performance_schema$'); do
            if [ ! -e /dumps/$i ]; then mkdir -m 700 /dumps/$i; fi
            mysqldump --events $i | gzip -c > /dumps/$i/$i.sql.gz
        done
    endscript
}

---

通过 CGI 远程备份

使用 CGI 脚本获取 MySQL 数据库的备份：

#!/bin/sh

echo "Content-Type: application/x-tar"
echo "Content-Encoding: x-gzip"
echo ""

mysqldump --host=mysql.hosting.com --user=john --password=XXXXX my_base | gzip 2>&1

通过浏览器或 wget 获取：

wget -O- --quiet http://localhost/~user/test.cgi > backup-`date +%Y%m%d`.sql.gz

---

3. 二进制日志管理

二进制日志（binary log）记录了 MySQL 服务器上的所有事件，可用于：

• 故障恢复
• 主从复制

日志通常存储在 /var/log/mysql/mysql-bin.0*。

查看日志内容：

mysqlbinlog /var/log/mysql/mysql-bin.000001

备份示例：

同时备份数据和刷新日志：

mysqldump -A --master-data --flush-logs | gzip > all.sql.gz

恢复数据：

(zcat all.sql.gz && mysqlbinlog /var/log/mysql/mysql-bin.0*) | mysql

---

4. 常用管理工具

• Web 界面：

  • phpMyAdmin：广泛使用的 MySQL 管理工具。
  • eSKUeL：phpMyAdmin 的替代工具。

• 桌面 GUI：

  • MySQL Administrator：提供图形界面的管理工具，但不建议用于生产环境备份。

---

5. 安全性建议

• 使用防火墙（如 iptables）限制访问。
• 避免以明文形式存储密码。
• 使用强密码生成工具（如 pwgen）提高账户安全性。