MySQL

MySQL Optimisation, Performance, and Logging

====================================================================================

MySQL Optimisation

====================================================================================

MySQL Tuner Script

wget -O mysqltuner.pl mysqltuner.pl && perl mysqltuner.pl

------------------------------------------------------------------------------------------------------------------------------------------------

Check MySQL default config files
/usr/sbin/mysqld --verbose --help | grep -A 1 "Default options"

------------------------------------------------------------------------------------------------------------------------------------------------

MySQL Variables

innodb_buffer_pool_size Specifies the size of the memory buffer used by InnoDB to cache data and indexes. Setting this appropriately can significantly improve read/write performance for InnoDB tables.

Recommendation: For dedicated MySQL servers, set to 70-80% of available memory.
query_cache_size Controls the size of the query cache, which stores results of SELECT queries to speed up subsequent identical queries. Effective use depends on query patterns and workload.

Recommendation: Enable and configure it based on the workload if beneficial; otherwise, keep it disabled.
key_buffer_size Sets the size of the buffer used for index blocks for MyISAM tables. Adjusting this can enhance performance for MyISAM-based applications.

Recommendation: Set according to the size of your MyISAM indexes; for mixed storage engines, allocate more memory to InnoDB buffer pool.
max_connections Limits the number of simultaneous client connections allowed to the MySQL server. Properly setting this prevents resource exhaustion and maintains server stability.

tmp_table_size =

max_heap_table_size = 

 Define the maximum size of temporary tables created in memory. Optimizing these can reduce disk I/O and improve query performance.

sort_buffer_size=256K

 Buffer size for sorts.

max_connections=151

  Maximum number of simultaneous client connections.

Recommendation: Increase based on expected load and server capacity.

innodb_log_file_size

Size of each log file in the log group.

 

Recommendation: Increase to improve performance, particularly for write-heavy applications.

innodb_log_buffer_size

Size of the buffer for log data before it is written to disk.

Recommendation: Increase for high transaction throughput to reduce disk I/O.

 

Logging

slow_query_log Enables logging of queries that exceed a certain execution time.

Recommendation: Enable and use for identifying slow queries; configure long_query_time to set the threshold.
log_error = /var/log/mysql.log Path to the error log file.
general_log=/var/log/mysql.log Enables logging of all queries.

Recommendation: Use with caution due to potential performance impact and disk consumption; useful for debugging.

====================================================================================

MyISAM v InnoDB

InnoDB has row-level locking. MyISAM only has full table-level locking. 

Check table engine: 

mysql -e "SELECT TABLE_SCHEMA as DbName ,TABLE_NAME as TableName ,ENGINE as Engine FROM information_schema.TABLES WHERE ENGINE='MyISAM' AND TABLE_SCHEMA NOT IN('mysql','information_schema','performance_schema');"

Generate commands to swap table engine from MyISAM to InnoDB

mysql -e "SELECT CONCAT('ALTER TABLE \`', TABLE_SCHEMA,'\`.\`',TABLE_NAME, '\` ENGINE = InnoDB;') FROM information_schema.TABLES WHERE ENGINE='MyISAM' AND TABLE_SCHEMA NOT IN('mysql','information_schema','performance_schema');"

====================================================================================

Deadlocks

A deadlock is a situation where two or more transactions are unable to proceed because each one is waiting for a lock held by the other transaction. This creates a cycle of dependencies that cannot be resolved without external intervention.

------------------------------------------------------------------------------------------------------------------------------------------------

Check for deadlocks:

SHOW ENGINE INNODB STATUS;

SHOW STATUS LIKE 'Table_locks_%';

------------------------------------------------------------------------------------------------------------------------------------------------

Deadlock logging

This will cause MySQL to log all deadlock errors to the error log, which can then be examined for more details.

SET GLOBAL innodb_print_all_deadlocks = 1;

------------------------------------------------------------------------------------------------------------------------------------------------

MySQL Monitoring software such as NewRelic can provide further information on deadlocks and general MySQL performance issues.

Resolving Deadlocks

Once identified, you can resolve deadlocks by:

------------------------------------------------------------------------------------------------------------------------------------------------

Corruption and Repairs

====================================================================================

MySQL Check

mysqlcheck -options database
MySQL Check Options:
-c Checks tables for errors. It performs a CHECK TABLE operation on each table in the specified databases.
-r Attempts to repair corrupted tables. It performs a REPAIR TABLE operation on each corrupted table.
-a Analyzes tables for optimal performance. It performs an ANALYZE TABLE operation on each table.
-o Optimizes tables to reduce fragmentation and reclaim unused space. It performs an OPTIMIZE TABLE operation on each table.
--databases db1 db2  Checks and repairs tables in multiple databases (db1 and db2).
-A Checks and repairs tables in all databases on the MySQL server.

------------------------------------------------------------------------------------------------------------------------------------------------

 When to use mysqlcheck -r

Best Practices and Considerations

  1. Backup: Always make a backup of your databases before attempting any repairs. While mysqlcheck -r is generally safe, there is a small risk of data loss if the repair process encounters unexpected issues.

  2. Table Locking: During repair, mysqlcheck will lock tables to ensure data consistency. Depending on the size and activity of your database, this can cause downtime or impact performance.

====================================================================================

User and Database Management

Command syntax will vary between MySQL Versions

====================================================================================

User Management

Show all users and hosts
SELECT user, host FROM mysql. user;
Add User
CREATE USER 'name'@'localhost' IDENTIFIED BY 'password.';
Granting Privileges

Available privileges:

CREATE Allows the user to create new databases and tables.
ALTER Allows the user to alter (modify) existing tables.
DROP Allows the user to drop (delete) databases and tables.
INSERT Allows the user to insert new rows into tables.
UPDATE Allows the user to update existing rows in tables.
DELETE Allows the user to delete rows from tables.
SELECT Allows the user to read (select) data from tables.
REFERENCES Allows the user to create foreign key constraints when defining tables.
RELOAD Allows the user to execute the FLUSH statement, which reloads various server configurations and clears or refreshes caches.

Grant specific privilege ( or multiple comma separated):

GRANT PRIVILEGETYPE ON database.table TO 'username'@'host';
FLUSH PRIVILEGES;

Grant all:

GRANT ALL ON database.table TO 'username'@'host';
FLUSH PRIVILEGES;
Delete a user
DROP USER 'name'@'host';

Change user password

ALTER USER 'user'@'host' IDENTIFIED BY 'NewPassword';
FLUSH PRIVILEGES;

====================================================================================

Database Management

Create a database
CREATE DATABASE name;

------------------------------------------------------------------------------------------------------------------------------------------------

Delete a database
DROP DATABASE name;

====================================================================================

Binary Logging

====================================================================================

Binary Logging

====================================================================================

What is Binary Logging?

Binary logging in MySQL is a crucial feature for data replication, backup, and recovery. It logs all changes made to the database, such as updates, deletions, and insertions, in a binary format.

Purpose of Binary Logging

How Binary Logging Works

====================================================================================

Binary Logging Configuration

------------------------------------------------------------------------------------------------------------------------------------------------

Enabling Binary Logging

To enable binary logging, you need to configure the MySQL server with the log_bin parameter in the my.cnf configuration file:

[mysqld]
log_bin = /var/log/mysql/mysql-bin.log

------------------------------------------------------------------------------------------------------------------------------------------------

Binary Logging Options

Bin log options that can also be set in the my.cnf file:

expire_logs_days = 7 Defines how many days to retain binary log files before automatic deletion.

binlog_format = format

format:

ROW
STATEMENT
MIXED

Determines the format of the binary logs. The available formats are:

ROW:Logs the actual changes at the row level.

STATEMENT: Logs each SQL statement that modifies data.
MIXED: Uses a combination of statement and row formats.
max_binlog_size = 100M Sets the maximum size of a binary log file before a new file is created.

------------------------------------------------------------------------------------------------------------------------------------------------

Binary Log Management

Viewing Binary Logs

Use the SHOW BINARY LOGS or SHOW MASTER LOGS command to list the binary log files.

SHOW BINARY LOGS;
Examining Binary Log Content

Use the mysqlbinlog utility to examine the content of binary log files

mysqlbinlog /var/log/mysql/mysql-bin.000001
Purging Binary Logs

To manually delete old binary logs and free up space, use the PURGE BINARY LOGS statement.

PURGE BINARY LOGS TO 'mysql-bin.000010';

Or, you can purge logs older than a specific date:

PURGE BINARY LOGS BEFORE '2024-01-01 00:00:00';

====================================================================================

Data Recovery From Binary Logs

------------------------------------------------------------------------------------------------------------------------------------------------

Binary Logs are just transactional changes made to a database. They don't include a full copy of the database itself. In order to replay binary logs, you'll need a backup copy of the database to replay onto. See mysql backups.

1. Identify the Binary Logs to Use

Determine the range of binary logs that contain the transactions you need to replay. You can list all binary logs with:

SHOW BINARY LOGS;

2. Obtain a Backup

Obtain the newest backup/dump of the database prior to the time that we need to recover to.

3. Import the backup into MySQL

mysql -u root -p < /path/to/backup.sql

4. Apply Binary Logs

A. Identify the start and end points within the binary logs. You might need to apply all transactions or up to a specific point in time or transaction.

mysqlbinlog --start-datetime="2024-06-28 10:00:00" --stop-datetime="2024-06-29 14:00:00" /path/to/mysql-bin.000001 /path/to/mysql-bin.000002 | mysql -u root -p

====================================================================================

 

Database Monitoring (New Relic)

====================================================================================

What is New Relic?

In the context of MySQL, New Relic provides monitoring and performance management capabilities through its Application Performance Monitoring (APM) and Infrastructure Monitoring features. 

Benefits of Using New Relic with MySQL

------------------------------------------------------------------------------------------------------------------------------------------------

https://www.ans.co.uk/docs/monitoring/installnewrelic/ 

MySQL Encryption

Encryption Types in MySQL

  1. Data-at-Rest Encryption:

    • Tablespace Encryption: Encrypts the entire tablespace, including the InnoDB tables.
    • Column-Level Encryption: Encrypts specific columns in a table.

  2. Data-in-Transit Encryption:

    • SSL/TLS Encryption: Encrypts data transmitted between MySQL clients and servers using SSL/TLS.

====================================================================================

Data-at-Rest Encryption

MyISAM does not support encryption natively. Tables will need to be converted to InnoDB before encryption is implemented.

1. Enable the Keyring Plugin

The keyring plugin is used to store and manage encryption keys securely within MySQL.

Install the Keyring Plugin

[mysqld]
early-plugin-load = keyring_file.so
keyring_file_data = /var/lib/mysql-keyring/keyring

Create the directory for the keyring file if it doesn't exist

sudo mkdir /var/lib/mysql-keyring
sudo chown mysql:mysql /var/lib/mysql-keyring

Restart the MySQL server to load the plugin

Verify the Keyring Plugin is Enabled:

SHOW PLUGINS;

2. Enable InnoDB Tablespace Encryption

Enable InnoDB File-Per-Table:

Ensure that innodb_file_per_table is enabled, which is the default setting in MySQL 5.6 and later.

[mysqld]
innodb_file_per_table = 1

Enable InnoDB Encryption:

[mysqld]
innodb_encrypt_tables = ON
innodb_encrypt_log = ON
innodb_encryption_threads = 4

Restart MySQL.

systemctl restart mysql

3. Encrypt Existing Tables

Encrypt a Specific Table

ALTER TABLE mytable ENCRYPTION='Y';

4. Verify Encryption

Check Encryption Status

You can verify if a table is encrypted by querying the information_schema.tables table:

SELECT table_schema, table_name, create_options
FROM information_schema.tables
WHERE create_options LIKE '%ENCRYPTION="Y"%';

------------------------------------------------------------------------------------------------------------------------------------------------

Binary Log Encryption

You can replay unencrypted binary logs onto encrypted tables.

Enable Binary Log Encryption

Add the following configuration to your my.cnf

[mysqld]
binlog_encryption = ON

Verify Binary Log Encryption

SHOW VARIABLES LIKE 'binlog_encryption';

------------------------------------------------------------------------------------------------------------------------------------------------

Replaying encrypted binary logs

Replaying encrypted binary logs involves ensuring that the encrypted logs are decrypted and applied correctly on the MySQL server.

Use mysqlbinlog to Read Encrypted Binary Logs

The mysqlbinlog utility will handle the decryption transparently if the keyring plugin is properly configured.

mysqlbinlog /path/to/binlog.000001 | mysql -u username -p

The process for replaying binary logs whether encrypted or not is largely the same, providing that the keyring plugin is enabled. See here for more info.

====================================================================================

Data-in-Transit Encryption

Data-in-transit encryption refers to the protection of data as it moves between systems, such as between a client and a server, or between servers. This type of encryption ensures that data remains confidential and integral during transmission, preventing unauthorized access and tampering.

Data-in-transit encryption typically uses Transport Layer Security (TLS) or its predecessor, Secure Sockets Layer (SSL), to secure communications.

Implementing Data-in-Transit Encryption in MySQL

1. Generate or Obtain Certificates and Keys:

Generate self-signed certificates using OpenSSL or obtain them from a trusted CA. Certificate needs to cover the MySQL server hostname.

Example command to generate a self-signed certificate using OpenSSL:

openssl req -newkey rsa:2048 -nodes -keyout server-key.pem -x509 -days 365 -out server-cert.pem

2. Configure MySQL Server

Edit the MySQL configuration file (my.cnf or my.ini) to include the paths to the certificates and keys.

[mysqld]
ssl-ca = /path/to/ca-cert.pem
ssl-cert = /path/to/server-cert.pem
ssl-key = /path/to/server-key.pem

To force the use of SSL when connecting to a MySQL server, you can add the below to the my.cnf:
require_secure_transport = ON

3. Restart MySQL Server

systemctl restart mysql

4. Verify SSL/TLS Configuration

Verify that SSL/TLS is enabled on the server.

SHOW VARIABLES LIKE '%ssl%';

====================================================================================

MySQL Remote Access

====================================================================================

To configure remote access for MySQL, you need to ensure that MySQL is configured to accept remote connections and that your firewall and MySQL user permissions are set up correctly.

Configure MySQL Server to Allow Remote Connections

1. Bind Address

The bind-address option in the MySQL configuration file specifies which network interfaces MySQL will listen on for incoming connections. By default, this is set to bind to 127.0.0.1 (localhost), meaning that only local connections can be made.

The bind address can be set to 0.0.0.0 to listen on all interfaces.

bind-address = 0.0.0.0

You can also set the bind address to the IP of a specific interface, should you wish to limit where MySQL traffic comes from.

2. Ensure port 3306 is open inbound on firewall

Check for limitations on port 3306 inbound traffic on local/hardware firewalls.

3. MySQL User Configuration

Create new user:

You can either create a new user with the desired host or alter an existing user's host if necessary. However, creating a new entry is often the safest and most organized method.

Create a New Entry for the User:

CREATE USER 'example_user'@'203.0.113.1' IDENTIFIED BY 'user_password';
GRANT ALL PRIVILEGES ON database_name.* TO 'example_user'@'203.0.113.1';
FLUSH PRIVILEGES;

Alter existing user:

RENAME USER 'example_user'@'localhost' TO 'example_user'@'203.0.113.1';
FLUSH PRIVILEGES;

====================================================================================

 

Storage Engines

MySQL supports multiple storage engines, with InnoDB and MyISAM being the most commonly used.

------------------------------------------------------------------------------------------------------------------------------------------------

MyISAM

  1. Table-Level Locking:
    • Description:
      • MyISAM uses table-level locking, which means that the entire table is locked for the duration of a read or write operation.
    • Types of Locks:
      • Read Lock (Shared Lock):
        • Multiple read operations can occur simultaneously.
        • Write operations are blocked until all read locks are released.
      • Write Lock (Exclusive Lock):
        • Only one write operation can occur at a time.
        • All other read and write operations are blocked until the write lock is released.
    • Implications:
      • Advantages:
        • Simpler to implement and manage.
        • Can be faster for read-heavy workloads with fewer write operations.
      • Disadvantages:
        • Poor concurrency for write-heavy workloads.
        • Write operations can block reads, leading to potential bottlenecks.

------------------------------------------------------------------------------------------------------------------------------------------------

InnoDB

  1. Row-Level Locking:
    • Description:
      • InnoDB uses row-level locking, which means that only the specific rows involved in a transaction are locked.
    • Types of Locks:
      • Shared Lock (S Lock):
        • Allows multiple transactions to read the same rows simultaneously.
        • Prevents other transactions from writing to the locked rows.
      • Exclusive Lock (X Lock):
        • Prevents other transactions from reading or writing to the locked rows.
      • Intent Locks:
        • Intent Shared Lock (IS Lock):
          • Indicates that a transaction intends to read rows in a table.
          • Compatible with other IS locks and S locks, but not with IX or X locks.
        • Intent Exclusive Lock (IX Lock):
          • Indicates that a transaction intends to write rows in a table.
          • Compatible with other IX locks, but not with S or X locks.
    • Implications:
      • Advantages:
        • Higher concurrency as only specific rows are locked.
        • Better performance for mixed read/write workloads.
        • Supports transactions and ACID compliance.
      • Disadvantages:
        • More complex locking mechanism.
        • Slightly higher overhead compared to table-level locking.

====================================================================================

MySQL Replication

MySQL replication is a process that allows data from one MySQL database server (the primary or source server) to be copied automatically to one or more MySQL database servers (replica or slave servers). Replication is used to improve data availability, load balancing, and for backup and failover purposes.

------------------------------------------------------------------------------------------------------------------------------------------------

Types of MySQL Replication

Asynchronous Replication:

Semi-Synchronous Replication:

Synchronous Replication (Group Replication):

====================================================================================

Replication Setups

See here for more info, and the ANS KB for setup. The below example is from my own solution.

Master - Master

In a Master-Master replication setup, two MySQL servers are configured to replicate data to each other. Both servers can accept write operations, and changes are propagated bidirectionally.

Both servers act as master and slave to each other, allowing writes on both nodes.

If one server fails, the other can continue to serve both read and write requests.

Data is duplicated on both servers, providing a backup in case one fails.

Advantages:

  1. Increased Availability:

    • Both servers can handle writes, providing high availability.

  2. Load Balancing:

    • Distribute read and write operations across both servers.

  3. Redundancy:

    • Data is replicated on both servers, providing a failover option.

Disadvantages:

  1. Complexity:

    • More complex to set up and maintain compared to Master-Slave.

  2. Conflict Resolution:

    • Potential for conflicts if the same data is modified on both servers simultaneously.

  3. Data Consistency:

    • Possible data inconsistency issues if replication lag occurs.

------------------------------------------------------------------------------------------------------------------------------------------------

Example Configuration

Master - Slave (asynchronous) configuration.

Configure Server A (master)

1. Set the my.cnf file for Server A

[mysqld]
server_id = 1
log_bin = /var/log/mysql/mysql-bin.log

2. Create a user for replication on Server A

CREATE USER 'replication'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

As you can see here CREATE USER 'replication'@'%' IDENTIFIED BY 'password';, I've set the host as wildcard (%), You'll ideally want to create 2 users here, one with the internal IP of Server A, and one with the internal IP of server B (internal IF on same network). For example:

CREATE USER 'replication'@'server_a_ip' IDENTIFIED BY 'password';
CREATE USER 'replication'@'server_b_ip' IDENTIFIED BY 'password';

3. Lock all tables to get MySQL into a consistent state on server A

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

Note down the File and Position values. You will use these in Server A's configuration.

4. Create a database dump

For replication to work, we'll first need to import a dump of the locked databases from the master onto the slave.

mysqldump -u root -p --all-databases --master-data > dbdump.sql
--master-data

Adds CHANGE MASTER TO Statement: At the beginning of the dump file (dbdump.sql in your case), --master-data adds a comment containing the CHANGE MASTER TO statement. This statement includes the following information:

  • MASTER_LOG_FILE: The name of the binary log file that was being written to when the dump was initiated.
  • MASTER_LOG_POS: The position within that binary log file where the dump started.
  • This information helps in setting up a slave server to start replication from the exact point where the dump was taken, ensuring consistency between the master and the slave.
Configure Server B (Slave)

1. Set the my.cnf file for Server B

[mysqld]
server_id = 2
log_bin = /var/log/mysql/mysql-bin.log

2. Import database dump from Server A

mysql -u root -p < dbdump.sql

3. Configure replication on Server B using the below MySQL command:

CHANGE MASTER TO
    MASTER_HOST='server_A_ip',
    MASTER_USER='repl',
    MASTER_PASSWORD='password',
    MASTER_LOG_FILE='mysql-bin.000001',  -- use the File value from Server A
    MASTER_LOG_POS=120;                  -- use the Position value from Server A
START SLAVE;

3. Verify Replication Status:

Ensure that both servers are replication to each other without errors. You should see Slave_IO_Running and Slave_SQL_Running set to Yes on both servers.

SHOW SLAVE STATUS\G;

4. Unlock the tables on both servers

UNLOCK TABLES;

Ensure to test once set up.

------------------------------------------------------------------------------------------------------------------------------------------------

Master - Slave

In a Master-Slave replication setup, one MySQL server (the master) is responsible for all write operations, and one or more slave servers replicate the data from the master. Slave servers are typically read-only.

Data flows from the master to the slave(s) only.

Slaves can handle read operations, offloading the master.

Advantages:

  1. Simplicity:

    • Easier to set up and maintain compared to Master-Master replication.

  2. Read Scalability:

    • Offload read operations to slaves, improving performance.

  3. Data Safety:

    • Slaves can be used for backups and disaster recovery without affecting the master.

Disadvantages:

  1. Single Point of Failure:

    • The master is a single point of failure for write operations.

  2. Replication Lag:

    • Potential for replication lag, which can lead to stale reads on slaves.

  3. Limited Write Scalability:

    • All writes are handled by the master, which can become a bottleneck.

====================================================================================

XtraBackup

 

 


 


 


 


 


  1.  

  2.  

  3.  

MySQL Backups - mysqldump and xtrabackup

===================================================================================

Percona XtraBackup

Percona XtraBackup is a widely used open-source tool for MySQL and MariaDB database backups. It is developed by Percona and is specifically designed to perform hot backups of MySQL databases without interrupting database operations.

------------------------------------------------------------------------------------------------------------------------------------------------

Key Features of XtraBackup:

Hot Backups XtraBackup allows you to take backups while the database is running and serving requests, minimizing downtime and maintaining data availability.
Consistency It ensures that backups are consistent, capturing all changes up to the point when the backup process begins.
Incremental Backups XtraBackup supports incremental backups, which only include changes made since the last backup. This feature reduces backup time and storage requirements.
Compressed Backups Backups can be compressed to save disk space and reduce storage costs.
Encrypted Backups XtraBackup supports encryption, which helps secure backup data both at rest and in transit.
Streaming Backups The tool can stream backups to another server, useful for remote storage or cloud integration.

------------------------------------------------------------------------------------------------------------------------------------------------

Percona XtraBackup vs mysqldump & bin logs

Category Percona XtraBackup mysqldump and binary logging
Performance

Non-Blocking Backups: XtraBackup performs hot backups without locking tables, meaning the database remains available for reads and writes during the backup process. This is crucial for maintaining application availability.

Incremental Backups: XtraBackup supports incremental backups, which capture only the changes since the last backup. This reduces the time and storage space needed for backups compared to full dumps every time.

Blocking: mysqldump can lock tables (depending on options used), which can cause significant downtime, especially on large databases.

Performance Impact: mysqldump can put a considerable load on the server, impacting performance.
Scalability

Efficient for Large Databases: XtraBackup is designed to handle large datasets efficiently. It directly copies the data files, making it much faster and less resource-intensive than logical backups.

Optimized Storage: Incremental and compressed backups help in managing storage effectively.

Time-Consuming: mysqldump can be very slow for large databases because it converts the entire database to SQL statements.

Storage Intensive: Frequent full dumps consume a lot of storage space.
Consistency

Point-in-Time Recovery: XtraBackup captures a consistent snapshot of the database and applies the transaction logs to ensure data consistency. This is crucial for point-in-time recovery.

Consistency Across Storage Engines: XtraBackup supports InnoDB and other storage engines, ensuring that backups are consistent across different types of tables.

Potential Inconsistencies: With mysqldump, obtaining a consistent snapshot requires careful management of transactions and locks, which can be complex and error-prone.

Manual Effort: Ensuring consistent backups with binary logs often requires combining multiple tools and scripts, adding to administrative overhead.
Features

Compression and Encryption: XtraBackup supports compressed and encrypted backups out of the box, providing security and storage efficiency.

Streaming: Backups can be streamed to another server, facilitating remote storage and integration with cloud services.

Lack of Built-in Compression/Encryption: mysqldump does not provide native support for compression or encryption. These need to be managed separately.

Complexity: Handling streaming, compression, and encryption typically requires additional scripts and tools, increasing complexity.
Recovery Speed

Fast Recovery: Restoring from XtraBackup is generally faster because it involves copying data files back to the data directory and applying logs, rather than replaying SQL statements.

Prepared Backups: XtraBackup's --prepare phase ensures that the backups are ready to be restored quickly and efficiently.

Slower Recovery: Restoring from mysqldump involves replaying all SQL statements, which can be very slow for large datasets.

Manual Log Replay: Combining binary logs with a mysqldump backup for point-in-time recovery can be complex and time-consuming.

------------------------------------------------------------------------------------------------------------------------------------------------