Skip to content

MySQL Management

Introduction to MySQL

MySQL is a widely-used open-source relational database management system (RDBMS). It enables the efficient management of large amounts of data using structured query language (SQL). MySQL operates on a client-server model, where the database server stores data and responds to client requests. It is known for its speed, reliability, and ease of use, making it a popular choice for web applications and data-driven services.

MySQL Installation

A MySQL instance is required for the WCS-DCA. Below are the typical steps for installation in Linux environments.

1. Install MySQL Server

  • Linux: Use the package manager for your distribution (e.g., dnf for Oracle Linux, apt for Debian/Ubuntu, or yum for CentOS/RHEL). For example, on Ubuntu: 
    sudo apt update
sudo apt install mysql-server

2. Secure the MySQL Installation

Run the security script to set a root password, remove anonymous users, disallow root login remotely, remove the test database, and reload privilege tables: 

sudo mysql_secure_installation

3. Start the MySQL Service

Ensure the MySQL service is running. On most systems, you can use systemd to manage the MySQL service: 

sudo systemctl start mysql
sudo systemctl enable mysql

4. Log into MySQL

Use the MySQL client to log in as the root user: 

mysql -u root -p

5. Create a Database

Once logged in, create a WCS database. Make your database name unique to your organization: 

CREATE DATABASE wcs_myuniqorg_db;

6. Create a Database User

Create a new and unique user and grant them permissions on the new database. Note this user and password as they will be required to complete the WCS-DCA setup: 

CREATE USER 'my_uniq_user'@'localhost' IDENTIFIED BY 'my_password';
GRANT ALL PRIVILEGES ON wcs_myuniqorg_db.* TO 'my_uniq_user'@'localhost';
FLUSH PRIVILEGES;

7. Test the Setup

Log out from the root user and log in with the new user to ensure everything is set up correctly: 

mysql -u my_uniq_user -p wcs_myuniqorg_db

8. Connect to the Database

Once your database is set up, you will connect to it remotely via the WCS-DCA instance. Refer to the user and password you created earlier as they will be needed to complete the WCS-DCA installation process.

MySQL WCS Configuration

1. Edit your MySQL Config File

Typically named my.cnf for Linux MySQL instances, this file can be located in different directories depending on your Linux flavor or MySQL installation. Common locations include /etc/, /etc/mysql, or /etc/my.cnf.d/. In the latter case, the file may be named differently, such as mysql-server.conf vs. my.cnf. Note: You can eliminate the ANSIBLE BLOCKS or leave them commented out. 

[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid
# BEGIN ANSIBLE MANAGED BLOCK
explicit_defaults_for_timestamp=1
sql_mode=NO_ENGINE_SUBSTITUTION
character_set_server=latin1
collation_server=latin1_swedish_ci
log_bin_trust_function_creators=1
slow_query_log=1
slow_query_log_file=/data/mysql/slow_query.log
# END ANSIBLE MANAGED BLOCK

2. Restart MySQL

Once the configuration is updated, a restart is required for the changes to become active. 

sudo systemctl restart mysqld

MySQL Backup

Backing up a MySQL server in a Linux environment involves using the mysqldump utility, which is a built-in tool for creating logical backups of MySQL databases. Here's a high-level overview of the steps involved:

1. Prepare the Environment

  • Ensure you have sufficient disk space on the server where the backup will be stored.
  • Verify that you have access to the MySQL server with a user that has appropriate privileges (e.g., a user with SELECT, LOCK TABLES, and SHOW VIEW privileges).

2. Perform the Backup Using mysqldump

Use the mysqldump command to create a backup of a single database or all databases. The command generates a SQL script that can recreate the database(s). - For a single database: 

mysqldump -u [username] -p[password] [database_name] > [backup_file.sql]
- For all databases: 
mysqldump -u [username] -p[password] --all-databases > [backup_file.sql]

3. Schedule Regular Backups

Use cron jobs to automate the backup process. Edit the crontab file with crontab -e and add an entry to schedule the backup. For example, to run a backup every day at 2 AM: 

0 2 * * * mysqldump -u [username] -p[password] [database_name] > /path/to/backup/backup_file_$(date +\%F).sql

4. Compress the Backup File

To save disk space, you can compress the backup file using tools like gzip or bzip2. For example: 

gzip [backup_file.sql]

5. Verify the Backup

Periodically verify that the backup files are not corrupted and can be restored. You can do this by restoring the backup to a test server: 

mysql -u [username] -p[password] [database_name] < [backup_file.sql]

6. Secure the Backup

Ensure the backup files are stored in a secure location with restricted access. Consider encrypting the backups for added security.

Example Backup Script

Here's an example of a shell script to back up a MySQL database and compress the backup file:

#!/bin/bash

# Variables
USER="backup_user"
PASSWORD="your_password"
DATABASE="your_database"
BACKUP_DIR="/path/to/backup"
DATE=$(date +%F)
BACKUP_FILE="$BACKUP_DIR/${DATABASE}_backup_$DATE.sql"
COMPRESSED_FILE="$BACKUP_FILE.gz"

# Perform the backup
mysqldump -u $USER -p$PASSWORD $DATABASE > $BACKUP_FILE

# Compress the backup file
gzip $BACKUP_FILE

# Log the backup
echo "Backup completed: $COMPRESSED_FILE"

Backup Strategies

  • Logical Backups: Use mysqldump for logical backups. 
    mysqldump -u root -p wcs_myuniqorg_db > backup.sql
  • Physical Backups: Use Percona XtraBackup for physical backups without downtime. 
    xtrabackup --backup --target-dir=/path/to/backup

Recovery Processes

Restoring from Logical Backup

To restore a database from a mysqldump backup: 

mysql -u root -p wcs_myuniqorg_db < backup.sql

Restoring from Physical Backup

  1. Prepare the backup: 
    xtrabackup --prepare --target-dir=/path/to/backup
  2. Restore the backup: 
    xtrabackup --copy-back --target-dir=/path/to/backup

Point-in-Time Recovery

For point-in-time recovery, ensure binary logging is enabled in MySQL. To restore up to a specific point: 1. Restore the most recent full backup. 2. Apply the binary logs up to the desired point in time: 

mysqlbinlog /var/lib/mysql/binlog.000001 | mysql -u root -p wcs_myuniqorg_db

Additional Considerations

  • Security: Regularly update MySQL to the latest version and apply security patches. Ensure your MySQL server is configured to be accessible only from trusted networks.
  • Monitoring: Use monitoring systems like NetData or Prometheus to keep an eye on database performance and health. The latest WCS-DCA supports these two platforms as well, enabling you to monitor not just system health, but also security events within your entire environment where agent endpoints are installed.
  • Incremental Backups: For large databases, consider using incremental backups with binary logs.
  • Remote Backups: Store backups on a remote server or cloud storage to prevent data loss in case of hardware failure.
  • Backup Retention: Implement a retention policy to manage the storage of backup files, deleting older backups as necessary.
  • Performance Tuning: Adjust MySQL configuration (my.cnf) for better performance based on your workload.
  • Scalability and High Availability: To ensure your MySQL database is scalable and highly available, consider the following:
  • Replication: Use MySQL Replication to create multiple copies of your database for read scalability and high availability. Learn more about MySQL Replication.
  • Clustering: Implement MySQL InnoDB Cluster or Galera Cluster for high availability and automatic failover. More information on MySQL InnoDB Cluster and Galera Cluster.
  • Load Balancing: Use a load balancer like ProxySQL to distribute database traffic and ensure continuous availability. Check out ProxySQL.
  • Backup Solutions: Use backup solutions like Percona XtraBackup for hot backups without downtime. Details available at Percona XtraBackup.
  • Cloud Services: Consider using cloud database services such as Amazon RDS or Google Cloud SQL, which provide built-in high availability and scalability options. See Amazon RDS for MySQL and Google Cloud SQL for MySQL.

Common highly available and scalable MySQL database architectures.

Master-Master Replication

flowchart TD
    A["WCS DCA"] -- Round Robin Request --> E["Load Balancer / Reverse Proxy"] & H["Load Balancer / Reverse Proxy"]
    G["WCS DCA"] -- Round Robin Request --> E & H
    E -- Read/Write --> I["MySQL"] & J["MySQL"]
    H -- Read/Write --> I & J
    I --> nv["Backup"]
    I <--> n7["Bi-Directional Replication"]
    J --> nu["Backup"]
    J <--> n7

Master-Slave Replication

flowchart TD
    A["WCS DCA"] -- Round Robin Request --> E["Load Balancer / Reverse Proxy"] & H["Load Balancer / Reverse Proxy"]
    G["WCS DCA"] -- Round Robin Request --> E & H
    E -- Read/Write --> I["MySQL Primary / Master"]
    E -. Read/Write .-> J["MySQL Secondary / Slave"]
    H -- Read/Write --> I
    H -. Read/Write .-> J
    I --> nv["Backup"]
    I --- n7["Failover Master / Slave Replication"]
    J --> nu["Backup"]
    J --- n7