MySQL Operations in Practice — From Optimization, Monitoring to Disaster Recovery

February 18, 2017 Database MySQL, Innodb, Database, Devops 2391 words 12 min read

Introduction

This article documents MySQL database operations practices, covering performance optimization, the monitoring system, Binlog management, dual-master high-availability deployment, and recovery from data file corruption. The cases are drawn from production environments.

InnoDB Performance Optimization in Practice

Baseline Status Analysis

Before optimizing InnoDB, you first need a comprehensive understanding of the current database runtime status:

sql
1
2
3
4
5
-- View InnoDB storage engine status
show engine innodb status \G;

-- View InnoDB-related configuration parameters
show variables like 'innodb%';

These commands help identify performance bottlenecks and provide data to guide subsequent optimization.

I/O Thread Optimization

Properly configuring the number of read/write threads can effectively utilize CPU resources:

ini
1
2
3
# Read/write thread configuration, reference: total = CPU cores - 2
innodb_read_io_threads = 8
innodb_write_io_threads = 12

Log Parameter Configuration

Important: Adjusting these configurations requires cleaning up ib_logfile files, otherwise MySQL will fail to start

ini
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
# Transaction log buffer, recommended 1M~8M
innodb_log_buffer_size = 16M

# Transaction log file size, recommended 1G~2G
innodb_log_file_size = 1G

# Number of transaction log files
innodb_log_files_in_group = 2

# Transaction log file directory
innodb_log_group_home_dir = /var/lib/mysql/

# Disk write strategy on transaction commit
innodb_flush_log_at_trx_commit = 1

Parameter Details:

innodb_log_buffer_size: Transaction log buffer, shaped like a “funnel,” continuously writes cached log records to disk. A larger buffer can temporarily hold complete transaction logs, reducing disk I/O.
innodb_log_file_size: Controls transaction log file size. Larger log files mean fewer checkpoints, saving disk I/O, but increase recovery time during database crashes.
innodb_flush_log_at_trx_commit: Controls the log write strategy on transaction commit. 1 means writing to disk on every commit, 2 means writing once per second, 0 means letting the OS control write timing.

Disk I/O Optimization

Key parameters for improving disk I/O utilization:

sql
1
2
-- Adjust based on disk type: 15K RPM SAS disks: 200 per block, SATA disks: 100 per block
set global innodb_io_capacity=300;

⚠️ Danger Warning: This parameter is risky — improper settings will directly impact disk I/O capability and degrade performance.

Cache Optimization

ini
1
2
3
4
5
# Buffer pool size, recommended 50%-70% of physical memory
innodb_buffer_pool_size = 1024M

# Number of buffer pool instances (recommended 1-8)
innodb_buffer_pool_instances = 3

Asynchronous I/O Support

On Linux systems, it is recommended to enable MySQL’s native asynchronous I/O support:

bash
1
2
# Install libaio library
yum -y install libaio

ini
1
2
# Enable native async I/O
innodb_use_native_aio = ON

Building a MySQL Monitoring System

Privilege Management Principles

Before building a monitoring system, establish sound privilege management principles:

Principle of Least Privilege: Grant only the minimum privileges needed to prevent misuse
Host Login Restrictions: When creating users, restrict login hosts — typically to specific IPs or internal IP ranges
Clean Up Default Users: After initializing the database, remove passwordless default users
Password Complexity: Set passwords that meet complexity requirements for every user
Regular Cleanup: Periodically remove unnecessary users, revoke privileges, or delete accounts

Monitoring Account Authorization

Grant privileges separately for local and remote monitoring:

sql
1
2
3
4
5
-- Remote monitoring account
GRANT USAGE,PROCESS,REPLICATION CLIENT,REPLICATION SLAVE ON *.* TO 'monitor'@'192.168.1.%' IDENTIFIED BY 'YOUR_PASSWORD';

-- Local monitoring account
GRANT USAGE,SUPER,PROCESS,REPLICATION CLIENT,REPLICATION SLAVE ON *.* TO 'monitor'@'localhost' IDENTIFIED BY 'YOUR_PASSWORD';

Monitoring Principles and Common Commands

MySQL monitoring primarily retrieves system status through the following commands:

sql
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
-- Global status overview
show global status;

-- Global variable settings overview
show global variables;

-- Master status overview
show master status;

-- Slave status overview
show slave status;

-- Binary log files overview
show binary logs;

-- InnoDB storage engine status
show engine innodb status \G;

-- MyISAM storage engine status
show engine myisam status \G;

You can also obtain detailed InnoDB storage engine information through the information_schema database.

Privilege Table Structure

MySQL privilege control is based on granular privilege levels:

Privilege	Privilege Level	Description
CREATE	Database, table, or index	Create databases, tables, or indexes
DROP	Database or table	Drop databases or tables
GRANT OPTION	Database, table, or stored programs	Grant privileges option
REFERENCES	Database or table	Foreign key references
ALTER	Table	Alter table structure
DELETE	Table	Delete data
INDEX	Table	Index operations
INSERT	Table	Insert data
SELECT	Table	Query data
UPDATE	Table	Update data
CREATE VIEW	View	Create views
SHOW VIEW	View	Show views
ALTER ROUTINE	Stored procedure	Alter stored procedures
CREATE ROUTINE	Stored procedure	Create stored procedures
EXECUTE	Stored procedure	Execute stored procedures
FILE	File access on server host	File access
CREATE TEMPORARY TABLES	Server administration	Create temporary tables
LOCK TABLES	Server administration	Lock tables
CREATE USER	Server administration	Create users
PROCESS	Server administration	View processes
RELOAD	Server administration	Execute flush commands
REPLICATION CLIENT	Server administration	Replication client
REPLICATION SLAVE	Server administration	Replication slave
SHOW DATABASES	Server administration	Show databases
SHUTDOWN	Server administration	Shutdown database
SUPER	Server administration	Kill threads

Binlog Operations Guide

Binlog Fundamentals

MySQL’s binary log is one of the most important MySQL logs. It records all DDL and DML statements (except data queries) as events, including the execution time of each statement. MySQL’s binary log is transaction-safe.

Enabling binary logs incurs approximately 1% performance overhead. Its two main purposes are:

MySQL Replication: Enable binlog on the Master side; the Master passes its binary logs to slaves for data synchronization
Data Recovery: Recover data using the mysqlbinlog tool

Binary logs consist of two types of files:

Binary log index file (suffix .index): Records all binary log files
Binary log file (suffix .00000*): Records all DDL and DML statement events in the database

Enabling Binlog

Edit the MySQL configuration file:

bash
1
2
# Edit configuration file
vi /etc/my.cnf

Set in the [mysqld] section:

ini
1
2
3
[mysqld]
# Enable binary logging
log-bin=mysql-bin

Restart the MySQL service for the configuration to take effect:

bash
1
2
3
# Restart MySQL
pkill mysqld
/usr/local/mysql/bin/mysqld_safe --user=mysql &

Checking Binlog Status

Log into the MySQL server and confirm binary logging is enabled by checking variable configuration:

bash
1
2
# Log into MySQL
/usr/local/mysql/bin/mysql -uroot -p

sql
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
-- Check binary log status
mysql> show variables like 'log_%';
+----------------------------------------+---------------------------------------+
| Variable_name                          | Value                                 |
+----------------------------------------+---------------------------------------+
| log_bin                                | ON                                    |
| log_bin_basename                       | /usr/local/mysql/data/mysql-bin       |
| log_bin_index                          | /usr/local/mysql/data/mysql-bin.index |
| log_bin_trust_function_creators        | OFF                                   |
| log_bin_use_v1_row_events              | OFF                                   |
| log_error                              | /usr/local/mysql/data/martin.err      |
| log_output                             | FILE                                  |
| log_queries_not_using_indexes          | OFF                                   |
| log_slave_updates                      | OFF                                   |
| log_slow_admin_statements              | OFF                                   |
| log_slow_slave_statements              | OFF                                   |
| log_throttle_queries_not_using_indexes | 0                                     |
| log_warnings                           | 1                                     |
+----------------------------------------+---------------------------------------+

Common Binlog Commands

1. List All Binlog Files

sql
1
mysql> show master logs;

2. Check Current Master Status

sql
1
mysql> show master status;

3. Flush Logs

Flush the log — starting from this moment, a new numbered binlog file is generated:

sql
1
mysql> flush logs;

Note: When using mysqldump for backups, the -F option also flushes the log.

4. Reset All Binlog Files

sql
1
mysql> reset master;

MySQL Dual-Master High Availability Deployment

Deployment Architecture

The dual-master deployment architecture provides high availability — two MySQL instances serve as both master and slave to each other, ensuring service continuity during single-point failures.

Dual-Master Configuration Highlights

Server 1 Configuration (192.168.1.10):

ini
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
[mysqld]
server-id = 1
log-bin = mysql-bin
log_bin_index = mysql-bin.index
log_slave_updates = on
relay-log = mysql-relay-bin
auto_increment_increment = 2
auto_increment_offset = 1
slave_skip_errors = ddl_exist_errors

# Tables to ignore during replication
replicate-wild-ignore-table=information_schema.%
replicate-wild-ignore-table=performance_schema.%
replicate-wild-ignore-table=cmxt_mail_log.%
replicate-wild-ignore-table=cmxt_mx_log.%

# InnoDB optimization
innodb_buffer_pool_size = 2G
innodb_log_buffer_size = 15M
innodb_io_capacity = 3000

Server 2 Configuration (192.168.1.11):

ini
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
[mysqld]
server-id = 2
log-bin = mysql-bin
log_bin_index = mysql-bin.index
log_slave_updates = on
relay-log = mysql-relay-bin
auto_increment_increment = 2
auto_increment_offset = 2
slave_skip_errors = ddl_exist_errors

# Tables to ignore during replication
replicate-wild-ignore-table=information_schema.%
replicate-wild-ignore-table=performance_schema.%
replicate-wild-ignore-table=cmxt_mail_log.%
replicate-wild-ignore-table=cmxt_mx_log.%

# InnoDB optimization
innodb_buffer_pool_size = 2G
innodb_log_buffer_size = 15M
innodb_io_capacity = 3000

Establishing Master-Slave Relationships

1. Create Replication Users

sql
1
2
3
4
5
-- On Server 1
mysql> grant replication slave on *.* to 'replication'@'192.168.1.11' identified by 'YOUR_PASSWORD';

-- On Server 2
mysql> grant replication slave on *.* to 'replication'@'192.168.1.10' identified by 'YOUR_PASSWORD';

2. Configure Master-Slave Relationships

Server 1 Configuration (pointing to Server 2):

sql
1
2
3
4
5
6
7
mysql> change master to 
    -> master_host='192.168.1.11',
    -> master_user='replication',
    -> master_password='YOUR_PASSWORD',
    -> master_port=3308,
    -> MASTER_LOG_FILE='mysql-bin.000001',
    -> MASTER_LOG_POS=107;

Server 2 Configuration (pointing to Server 1):

sql
1
2
3
4
5
6
7
mysql> change master to 
    -> master_host='192.168.1.10',
    -> master_user='replication',
    -> master_password='YOUR_PASSWORD',
    -> master_port=3308,
    -> MASTER_LOG_FILE='mysql-bin.000001',
    -> MASTER_LOG_POS=107;

3. Start Replication

sql
1
mysql> start slave;

Data Synchronization

1. Initial Data Synchronization

Export data from the master server:

bash
1
2
3
4
5
# Export business database
mysqldump --opt --master-data=2 -uapp -pYOUR_PASSWORD -h192.168.1.10 -P3308 cmxt > cmxt_backup.sql

# Export log database
mysqldump --opt --master-data=2 -uapp -pYOUR_PASSWORD -h192.168.1.10 -P3308 cmxt_log > cmxt_log_backup.sql

Import to the standby server:

bash
1
2
3
4
5
# Import business database
mysql -uapp -pYOUR_PASSWORD -h192.168.1.11 -P3308 cmxt < cmxt_backup.sql

# Import log database
mysql -uapp -pYOUR_PASSWORD -h192.168.1.11 -P3308 cmxt_log < cmxt_log_backup.sql

2. Application Access Authorization

sql
1
2
3
4
5
6
-- Authorize business system access
GRANT ALL PRIVILEGES ON cmxt_mail.* TO 'app'@'192.168.1.%' IDENTIFIED BY 'YOUR_PASSWORD' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON cmxt_mail_log.* TO 'app'@'192.168.1.%' IDENTIFIED BY 'YOUR_PASSWORD' WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON cmxt_mx.* TO 'app'@'192.168.1.%' IDENTIFIED BY 'YOUR_PASSWORD' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON cmxt_mx_log.* TO 'app'@'192.168.1.%' IDENTIFIED BY 'YOUR_PASSWORD' WITH GRANT OPTION;

Status Checks

1. Check Replication Status

sql
1
mysql> show slave status\G;

Key indicators:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: Should be 0 or a small value

2. Monitor Replication Lag

sql
1
2
3
4
5
-- Check replication position
mysql> show master status;

-- Check slave status
mysql> show slave status\G;

Emergency Recovery from Data File Corruption

Fault Diagnosis

When database file corruption is detected, quickly assess the extent and impact:

sql
1
2
3
4
5
-- Check table status
mysql> check table table_name;

-- Check engine status
mysql> show engine innodb status;

Emergency Repair Steps

1. Temporary Fix

If storage anomalies cause the partition to become read-only, add the following configuration in my.cnf:

ini
1
2
[mysqld]
innodb_force_recovery=2

⚠️ Important Reminders:

Try not to set this parameter above 3, otherwise it may cause permanent data loss
Make a full backup before proceeding
innodb_force_recovery can be set from 1-6, where larger numbers include the effects of all smaller numbers

2. Recovery Parameter Details

ini
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
# 1: Skip corrupted pages detected during checks
innodb_force_recovery=1

# 2: Block the main thread from running (e.g., full purge operations that would cause a crash)
innodb_force_recovery=2

# 3: Do not perform transaction rollback operations
innodb_force_recovery=3

# 4: Do not perform insert buffer merge operations
innodb_force_recovery=4

# 5: Do not check redo logs; InnoDB treats uncommitted transactions as committed
innodb_force_recovery=5

# 6: Do not perform roll-forward operations
innodb_force_recovery=6

3. Complete Recovery Procedure

Step 1: Stop Write Operations

bash
1
2
# Stop all business applications to prevent further damage
systemctl stop business_system

Step 2: Export Data

bash
1
2
3
4
# Export business databases
for db in cmxt cmxt_log; do
    mysqldump -uapp -pYOUR_PASSWORD -h127.0.0.1 -P3308 --opt --hex-blob ${db} > ${BACKUP_DIR}/${db}_$(date +%Y%m%d).sql
done

Step 3: Backup Existing Data

bash
1
2
# Backup the entire MySQL directory
mv /data/mysql /data/mysql_bak_$(date +%Y%m%d)

Step 4: Rebuild the Database

bash
1
2
# Re-initialize the database
mysql_install_db --defaults-file=/opt/app/conf/my.cnf --datadir=/data/mysql --initialize

Step 5: Skip Grant Tables on Startup

ini
1
2
[mysqld]
skip-grant-tables

Step 6: Set Password and Privileges

bash
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
# Start the database
systemctl start mysql

# Set root password
mysqladmin -u root password 'YOUR_PASSWORD'

# Authorize business system user
mysql -u root -p
mysql> GRANT ALL PRIVILEGES ON cmxt.* TO 'app'@'localhost' IDENTIFIED BY 'YOUR_PASSWORD';
mysql> FLUSH PRIVILEGES;

Step 7: Import Data

bash
1
2
3
4
# Restore business databases
for db in cmxt cmxt_log; do
    mysql -uapp -pYOUR_PASSWORD ${db} < ${BACKUP_DIR}/${db}_$(date +%Y%m%d).sql
done

Step 8: Restart Business Systems

bash
1
2
# Start business systems
systemctl start business_system

4. Set Up Regular Backups

bash
1
2
# Add crontab task
0 2 * * * /opt/app/scripts/backup_mysql.sh

bash
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
#!/bin/bash
# backup_mysql.sh

BACKUP_DIR="/data/mysql_backup"
DATE=$(date +%Y%m%d)

# Create backup directory
mkdir -p ${BACKUP_DIR}

# Backup databases
mysql -uapp -pYOUR_PASSWORD -e "SHOW DATABASES;" | grep -v "information_schema" | grep -v "performance_schema" | xargs -I {} mysqldump -uapp -pYOUR_PASSWORD --opt --hex-blob {} > ${BACKUP_DIR}/mysql_backup_${DATE}.sql

# Compress backup
gzip ${BACKUP_DIR}/mysql_backup_${DATE}.sql

# Delete backups older than 7 days
find ${BACKUP_DIR} -name "mysql_backup_*.sql.gz" -mtime +7 -delete

Summary

This article covered practical MySQL database operations experience from multiple dimensions:

Performance Optimization Highlights

I/O Optimization: Properly configure read/write thread counts to improve disk I/O utilization
Cache Optimization: Reasonably set buffer pool size and instance count
Log Optimization: Adjust transaction log parameters to balance performance and data safety
Async I/O: Enable Linux native async I/O support to boost I/O performance

Monitoring System Development

Privilege Management: Follow the principle of least privilege to establish a comprehensive privilege system
Monitoring Accounts: Distinguish between local and remote monitoring, configure privileges appropriately
Status Checks: Monitor system status in real time through various commands
Privilege Tables: Understand MySQL privilege levels and privilege types

High Availability Deployment

Dual-Master Architecture: Achieve high availability, ensuring service continuity
Configuration Synchronization: Sound dual-master configuration parameters
Data Synchronization: Complete initial data synchronization workflow
Status Monitoring: Real-time monitoring of replication status and lag

Disaster Recovery

Emergency Response: Rapid response when data files are corrupted
Recovery Strategy: Complete process from temporary fix to full recovery
Backup Mechanism: Establish a comprehensive regular backup mechanism
Preventive Measures: Prevent failures through proper parameter configuration

MySQL operations requires holistic consideration across architecture design, performance optimization, monitoring and maintenance, and disaster recovery. Parameter configuration and backup mechanisms are the key levers for preventing failures.