MySQL : Best Practices

Introduction

Nowadays, MySQL is among the most popular and beloved databases for applications. But, managing and designing is a crucial and important task when selecting a database for your application. Let’s dig into what are the best practices to manage the database.

MySQL Best Practices:

• • Schema Design
  • Indexing
  • Performance Optimization
  • Backup and Recovery
  • Security

Schema Design :

• • Follow normalization principles to reduce data redundancy and improve data integrity. The main goals of normalization are to eliminate duplicate data, ensure data dependencies make sense, and make the database more efficient. Keep this in mind, when the application is built for heavy reads or reporting and analysis then do not go with the normalization and prefer denormalization instead.
  • Choose the smallest data type that can hold your data to save space and improve performance. Avoid unnecessary byte allocation for columns that are not required. e.g. If you want to Store “Country + area + number separately” then use VARCHAR(20) and do not go blindly for length.

Indexing :

• • Use Indexes for Frequently Queried Columns, Identify columns that are often used in SELECT, WHERE, ORDER BY, JOIN, and GROUP BY clauses, and create indexes on those columns.
  • While indexes improve read performance, they can degrade write performance (INSERT, UPDATE, DELETE operations) due to the overhead of maintaining the index. Only create indexes that are necessary.
  • When creating composite indexes, consider the order of the columns based on the query patterns. The most frequently used columns in conditions should come first.
  • Indexing columns with low cardinality (few unique values, such as Boolean or gender columns) may not provide significant performance benefits.

Performance Optimization:

• • Break complex queries into simpler subqueries or temporary tables to reduce the workload on the optimizer. Select only the columns you need instead of using SELECT *, which fetches all columns unnecessarily.
  • While using joins start with the smallest or most selective table first. This can reduce the amount of data processed in subsequent joins. Use the appropriate join type (e.g. INNER JOIN, LEFT JOIN) based on the required data set.
  • Subqueries can be less efficient than joins, especially in the WHERE clause. Replace subqueries with joins where possible.
  • In a WHERE clause, place the most selective conditions (those that filter out the most rows) first. Avoid using functions on indexed columns in the WHERE clause (e.g., WHERE date(column) = ‘2023-01-01’). This can prevent the use of indexes. Use range conditions like BETWEEN, >=, <= carefully, as they may not fully utilize indexes.
  • There are a few optimizations on configuration levels that can help if you take care of  :

1. 1. 1. 1. Buffer Pool Size : The innodb_buffer_pool_size setting determines the amount of memory allocated for caching InnoDB tables and indexes. It should be set to 70-80% of available memory for most workloads.
         2. Query Cache : If your workload involves many identical queries, enabling the query cache can improve performance. But, for frequently changing data, the query cache might cause overhead.
         3. Connection Pooling: Use connection pooling to manage database connections. Opening and closing connections repeatedly can degrade performance.
         4. Thread Pooling : Adjust thread pool settings to handle multiple connections efficiently without overwhelming the server.
         5. Disk I/O : Use fast SSDs for data storage to reduce I/O latency. Ensure that the innodb_flush_log_at_trx_commit setting is configured based on your durability requirements.

Backup and Recovery:

Backup and recovery in MySQL are critical processes to ensure data integrity, availability, and disaster recovery. Here’s a guide to best practices and techniques for backup and recovery in MySQL:

• • Logical Backups : Logical backups consist of SQL statements that recreate the database structure and data. mysqldump is one of the tools that can export databases to a .sql file containing CREATE TABLE, INSERT, and other SQL statements. mysqlpump is also a faster alternative to mysqldump, especially for large databases. Supports parallel processing.
  • Physical Backups : Physical backups involve copying the actual database files, including the data files, logs, and configuration files. mysqlbackup is one of the most popular tools for physical backup you can use. This tool is belongs to MySQL Enterprise Backup and not open source. But there are some open-source tools available like Percona XtraBackup. 

        Backup Strategies :

• • Full Backups :  A complete backup of the entire database.  Use this method on a regular basis or a weekly basis, it depends on your costing/database size. Also, you should use these before migration/upgrades / or any major database deployment.
  • Incremental Backups : Backs up only the data that has changed since the last full or incremental backup. Daily backups in between weekly full backups. If you are keeping full backup daily then you can set up hourly incremental backup to reduce the data loss during recovery. (mysqlbinlog)
  • Differential Backups : Backs up data that has changed since the last full backup. It’s an alternative to incremental backups.

        Backup Best Practice :

• • Automate Backups: Use cron jobs or other scheduling tools to automate the backup process. This ensures regular backups without manual intervention.
  • Offsite Storage : Store backups in a different physical location or use cloud storage (e.g., AWS S3, Google Cloud Storage) to protect against site-specific disasters.
  • Encrypt Backups: Protect sensitive data by encrypting backups, especially if they are stored offsite or in the cloud.
  • Compress Backups: Use compression to save storage space, especially for logical backups. e.g. gzip
  • Verify Backups: Regularly test backups by restoring them in a non-production environment to ensure they are reliable.

       Recovery Procedure : 

• • Restoring from Logical Backups : Ensure the database exists or create it before restoring. mysql -u [username] -p [database_name] < backup.sql9   mysqlpump -u [username] -p [database_name] < backup.sql

• • Restoring from Physical Backups : mysqlbackup –backup-dir=/path/to/backup –datadir=/path/to/datadir copy-back  ( this command copies the backup files back to the MySQL data directory)   innobackupex –apply-log /path/to/backup | innobackupex –copy-back /path/to/backup   (--apply-log prepares the backup for restoration, and –copy-back restores the backup to the MySQL data directory)

• •  Point-In-Time Recovery (PITR)   : Restores the database to a specific point in time, typically by applying binary logs after restoring a backup. Ensure binary logging is enabled (log_bin = ON). Apply binary logs using the mysqlbinlog tool shown below. You can specify a –stop-datetime  or –stop-position to recover up to a specific point.:  mysqlbinlog /path/to/binlogs.000001 | mysql -u [username] -p [database_name]

• • Disaster Recovery Planning  : Regularly test your disaster recovery plan to ensure that backups can be restored within the required time frame. Implement monitoring to ensure backups are completed successfully and without errors. Consider using MySQL replication, clustering, or other HA solutions to minimize downtime in the event of a failure.

Security:

Security is a critical aspect of managing MySQL databases. Ensuring that your MySQL server is secure involves implementing a combination of best practices. Here’s a detailed guide to MySQL security:

User Management and Authentication :

• • Only grant users the minimum permissions necessary to perform their tasks. Avoid using the root or other highly privileged accounts for application access.
  • Use the CREATE USER statement to create specific users for different applications or roles. Below are the commands to create user, assign privileges, and revoke privileges.
    CREATE USER ‘app_user’@’localhost’ IDENTIFIED BY ‘strong_password’;
  • Assign only the necessary privileges to users.  GRANT SELECT, INSERT ON database_name.* TO ‘app_user’@’localhost’;
  • Regularly review and revoke unnecessary privileges. REVOKE INSERT ON database_name.* FROM ‘app_user’@’localhost’;   
  • Ensure all user accounts use strong, complex passwords. Enforce password policies using validate_password plugin.  INSTALL PLUGIN validate_password SONAME ‘validate_password.so’;  SET GLOBAL validate_password_policy=STRONG;
  • Set password expiration policies to require users to change passwords regularly.  ALTER USER ‘app_user’@’localhost’ PASSWORD EXPIRE INTERVAL 90 DAY;

Database Encryption: 

• • Encrypting Data in Transit : Use SSL/TLS to encrypt data transmitted between the MySQL server and clients.
  • Encrypting Data at Rest : Encrypt InnoDB tablespaces to secure data at rest. Also, encrypt binary logs to prevent unauthorized access to replication data. Encrypt backups using tools like mysqlbackup or third-party tools like Percona XtraBackup.

Auditing & Logging :

• • Binary Logging: Enable binary logging to maintain a record of all changes to the database.
  • Row-Based Logging: Use row-based binary logging for more accurate replication and auditing.
  • General Query Log: Logs all SQL queries received by the server by enabling. Make sure logs should be stored in a different location than the database otherwise, storage issues can occur as general logs take lots of storage.
  • Slow Query Log : Logs queries that exceed a certain execution time, which can indicate performance issues.
  • Enable MySQL Enterprise Audit : Provides advanced auditing capabilities, including tracking of specific user actions and queries.

Conclusion

Implementing these MySQL best practices ensures a robust, secure, and well-maintained database environment, safeguarding your data against unauthorized access, vulnerabilities, and breaches, while maintaining optimal performance and compliance with industry standards.