# 安装MySQL
[root@fgedu-db1 ~]# yum install -y mysql-server
[root@fgedu-db1 ~]# systemctl enable mysqld –now

# 配置主服务器
[root@fgedu-db1 ~]# cat > /etc/my.cnf.d/server.cnf << 'EOF' [mysqld] server-id = 1 log-bin = mysql-bin binlog-format = ROW gtid-mode = ON enforce-gtid-consistency = ON log-slave-updates = ON binlog-cache-size = 1M max-binlog-size = 500M expire-logs-days = 7 sync-binlog = 1 innodb_flush_log_at_trx_commit = 1 EOF [root@fgedu-db1 ~]# systemctl restart mysqld # 创建复制用户 [root@fgedu-db1 ~]# mysql -u root -p mysql> CREATE USER ‘repl’@’%’ IDENTIFIED BY ‘Repl@123456’;
Query OK, from PG视频:www.itpux.com0 rows affected (0.01 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO ‘repl’@’%’;
Query OK, 0 rows affected (0.01 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

# 查看主服务器状态
mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 154
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

# 配置从服务器
[root@fgedu-db2 ~]# cat > /etc/my.cnf.d/server.cnf << 'EOF' [mysqld] server-id = 2 relay-log = relay-bin read-only = ON gtid-mode = ON enforce-gtid-consistency = ON log-slave-updates = ON EOF [root@fgedu-db2 ~]# systemctl restart mysqld # 配置复制 [root@fgedu-db2 ~]# mysql -u root -p mysql> CHANGE MASTER TO
-> MA更多学习教程公众号风哥教程itpux_comSTER_HOST=’192.168.1.10′,
-> MASTER_USER=’repl’,
-> MASTER_PASSWORD=’Repl@123456′,
-> MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> START SLAVE;
Query OK, 0 rows affected (0.01 sec)

# 查看从服务器状态
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.10
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 527

# 安装Percona XtraBackup
[root@fgedu-db1 ~]# yum install -y percona-xtrabackup-80

# 创建全量备份
[root@fgedu-db1 ~]# xtrabackup –backup –target-dir=/backup/full –user=root –password=MySQL@123
xtrabackup: recognized server arguments: –backup=1 –target-dir=/backup/full
xtrabackup: recognized client arguments:
240404 23:00:00 version_check Connecting to MySQL server with DSN ‘dbi:mysql:;mysql_read_default_group=xtrabackup’ as ‘root’ (using password: YES).
240404 23:00:00 version_check Connected to MySQL server
240404 23:00:00 version_check Starting a version check
…
240404 23:00:05 completed OK!

# 创建增量备份
[root@fgedu-db1 ~]# xtrabackup –backup –target-dir=/backup/inc1 –incremental-basedir=/backup/full –user=root –password=MySQL@123
…
240404 23:00:10 completed OK!

# 准备备份
[root@fgedu-db1 ~]# xtrabackup –prepare –target-dir=/backup/full
…
240404 23:00:15 completed OK!

# 创建自动备份脚本
[root@fgedu-db1 ~]# cat > /usr/local/bin/mysql-backup.sh << 'EOF' #!/bin/bash # mysql-backup.sh # from:www.itpux.com.qq113257174.wx:itpux-com # web: http://www.fgedu.net.cn BACKUP_DIR="/backup/mysql" MYSQL_USER="root" MYSQL_PASS="MySQL@123" DATE=$(date +%Y%m%d) RETENTION_DAYS=7 mkdir -p $BACKUP_DIR/$DATE echo "=== MySQL备份开始 ===" echo "备份时间: $(date)" # 全量备份 xtrabackup --backup --target-dir=$BACKUP_DIR/$DATE/full \ --user=$MYSQL_USER --password=$MYSQL_PASS \ --compress --compress-threads=4 # 备份binlog mysql -u$MYSQL_USER -p$MYSQL_PASS -e "FLUSH LOGS" BINLOG_DIR=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -e "SHOW VARIABLES LIKE 'log_bin_basename'" | tail -1 | awk '{print $2}') cp ${BINLOG_DIR%/*}/mysql-bin.* $BACKUP_DIR/$DATE/ # 清理旧备份 find $BACKUP_DIR -type d -mtime +$RETENTION_DAYS -exec rm -rf {} \; echo "备份完成: $BACKUP_DIR/$DATE" echo "=== 备份结束 ===" EOF [root@fgedu-db1 ~]# chmod +x /usr/local/bin/mysql-backup.sh # 配置定时备份 [root@fgedu-db1 ~]# cat >> /etc/crontab << 'EOF' 0 2 * * * root /usr/local/bin/mysql-backup.sh >> /var/log/mysql-backup.log 2>&1
EOF

# 安装MySQL Exporter
[root@fgedu-db1 ~]# wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.15.0/mysqld_exporter-0.15.0.linux-amd64.tar.gz
[root@fgedu-db1 ~]# tar xzf mysqld_exporter-0.15.0.linux-amd64.tar.gz
[root@fgedu-db1 ~]# mv mysqld_exporter-0.15.0.linux-amd64/mysqld_exporter /usr/local/bin/

# 创建监控用户
[root@fgedu-db1 ~]# mysql -u root -p
mysql> CREATE USER ‘exporter’@’localhost’ IDENTIFIED BY ‘Exporter@123’;
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO ‘exporter’@’localhost’;
Query OK, 0 rows affected (0.01 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

# 配置Exporter
[root@fgedu-db1 ~]# cat > /etc/.mysqld_exporter.cnf << 'EOF' [client] user=exporter password=Exporter@123 EOF # 创建Systemd服务 [root@fgedu-db1 ~]# cat > /etc/systemd/system/mysqld_exporter.service << 'EOF' [Unit] Description=MySQL Exporter After=network.target [Service] Type=simple ExecStart=/usr/local/bin/mysqld_exporter \ --config.my-cnf=/etc/.mysqld_exporter.cnf \ --web.学习交流加群风哥微信: itpux-comlisten-address=:9104 [Install] WantedBy=multi-user.target EOF [root@fgedu-db1 ~]# systemctl daemon-reload [root@fgedu-db1 ~]# systemctl enable mysqld_exporter --now # 创建监控脚本 [root@fgedu-db1 ~]# cat > /usr/local/bin/mysql-monitor.sh << 'EOF' #!/bin/bash # mysql-monitor.sh # from:www.itpux.com.qq113257174.wx:itpux-com # web: http://www.fgedu.net.cn MYSQL_USER="root" MYSQL_PASS="MySQL@123" echo "=== MySQL监控报告 ===" echo "监控时间: $(date)" echo "" echo "1. 连接数统计" echo "----------------------------------------" mysql -u$MYSQL_USER -p$MYSQL_PASS -e "SHOW STATUS LIKE 'Threads_connected'" mysql -u$MYSQL_USER -p$MYSQL_PASS -e "SHOW STATUS LIKE 'Max_used_connections'" echo "" echo "2. 慢查询统计" echo "----------------------------------------" mysql -u$MYSQL_USER -p$MYSQL_PASS -e "SHOW STATUS LIKE 'Slow_queries'" echo "" echo "3. 缓冲池状态" echo "----------------------------------------" mysql -u$MYSQL_USER -p$MYSQL_PASS -e "SHOW STATUS LIKE 'Innodb_buffer_pool%'" echo "" echo "4. 主从状态" echo "----------------------------------------" mysql -u$MYSQL_USER -p$MYSQL_PASS -e "SHOW SLAVE STATUS\G" 2>/dev/null | grep -E “Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master”

echo “”
echo “5.更多视频教程www.fgedu.net.cn 锁等待”
echo “—————————————-”
mysql -u$MYSQL_USER -p$MYSQL_PASS -e “SELECT * FROM information_schema.INNODB_LOCK_WAITS”

echo “”
echo “=== 监控完成 ===”
EOF

[root@fgedu-db1 ~]# chmod +x /usr/local/bin/mysql-monitor.sh

# 优化MySQL配置
[root@fgedu-db1 ~]# cat > /etc/my.cnf.d/optimization.cnf << 'EOF' [mysqld] # 基本配置 max_connections = 1000 max_connect_errors = 1000 wait_timeout = 28800 interactive_timeout = 28800 # 缓冲池配置 innodb_buffer_pool_size = 4G innodb_buffer_pool_instances = 4 innodb_log_buffer_size = 64M innodb_log_file_size = 512M # I/O配置 innodb_flush_method = O_DIRECT innodb_io_capacity = 2000 innodb_io_capacity_max = 4000 innodb_read_io_threads = 8 innodb_write_io_threads = 8 # 查询缓存 query_cache_type = 0 query_cache_size = 0 # 慢查询配置 slow_query_log = 1 slow_query_log_file = /var/log/mysql/slow.log long_query_time = 2 log_queries_not_using_indexes = 1 # 临时表配置 tmp_table_size = 64M max_heap_table_size = 64M # 排序缓冲 sort_buffer_size = 2M join_buffer_size = 2M read_buffer_size = 1M read_rnd_buffer_size = 1M # 表缓存 table_open_cache = 4000 table_definition_cache = 2000 EOF [root@fgedu-db1 ~]# systemctl restart mysqld # 分析慢查询 [root@fgedu-db1 ~]# mysqldumpslow -s t -t 10 /var/log/mysql/slow.log Reading mysql slow query log from /var/log/mysql/slow.log Count: 50 Time=5.00s (250s) Lock=0.00s (0s) Rows=1000.0 (50000), root[root]@localhost SELECT * FROM orders WHERE status = 'S' Count: 30 Time=3.00s (90s) Lock=0.00s (0s) Rows=500.0 (15000), root[root]@localhost SELECT * FROM products WHERE category_id = 'S' # 优化表 [root@fgedu-db1 ~]# mysql -u root -p -e "SELECT CONCAT('OPTIMIZE TABLE ', table_schema, '.', table_name, ';') FROM information_schema.tables WHERE table_schema='fgedu_db'" | mysql -u root -p