In this blog, we will explore a series of commands and actions executed to manage and configure MariaDB

In this blog, we will explore a series of commands and actions executed to manage and configure MariaDB, a popular open-source relational database management system (RDBMS). Each command and its purpose are explained to give you a clear understanding of their functionality.

1. Stopping, Starting, and Checking Status of MariaDB

- `sudo systemctl stop mariadb`: Stops the MariaDB service.

- `sudo systemctl start mariadb`: Starts the MariaDB service.

- `sudo systemctl status mariadb`: Checks the current status of the MariaDB service.

2. Reviewing Server Configuration and Status

- `cat /etc/my.cnf`: Displays the content of the MariaDB configuration file.

- `df -h`: Shows disk space usage.

- `cat /etc/fstab`: Displays the file system table.

- `sudo mount -a`: Mounts all file systems mentioned in the fstab file.

- `history`: Shows the command history.

- `sudo su root` (centOS) / `sudo su` (RedHat): Switches to the root user.

- `crontab -l`: Lists all cron jobs for the current user.

- `ls -l /scripts/`: Lists files in the `/scripts/` directory with details.

- `cat /script/<script name>`: Displays the content of a specific script.

- `cat /usr/bin/bash /scripts/checkbackup.bash`: Displays the content of the `checkbackup.bash` script.

- `ls -l /usr/bin/bash /scripts/`: Lists details of files in the specified directories.

- `ls -l /etc/holland`: Lists files in the `/etc/holland` directory.

- `cat /etc/holland/holland.conf`: Displays the content of the Holland configuration file.

- `ls -s /etc/holland/backupsets/`: Lists details of files in the backup sets directory.

- `cat /etc/holland/backupsets/default.conf`: Displays the content of the default backup set configuration file.

3. Reviewing and Managing Error Logs

- `tail -n 10 /var/log/mariadb/mariadb.log`: Displays the last 10 lines of the MariaDB error log.

4. Managing Journal Logs for Errors

- `journalctl -u mariadb | grep 'Access denied'`: Searches the journal logs for "Access denied" errors related to MariaDB.

- `journalctl | grep 'Access denied' | tail -n 100`: Searches the journal logs for "Access denied" errors and displays the last 100 lines.

- `journalctl | grep 'XXX-TST-DB01-adm' | tail -n 100`: Searches the journal logs for entries related to the user "XXX-TST-DB01-adm" and displays the last 100 lines.

- `journalctl | grep '192.168.0.102' | tail -n 100`: Searches the journal logs for entries related to the IP address "192.168.0.102" and displays the last 100 lines.

5. Logging into MariaDB

- `mysql -h 192.168.0.102 -u XXX-TST-DB01-adm -p`: Logs into the MariaDB server at the specified IP address with the specified user.

- `mysql -u XXX-TST-DB01-adm -p`: Logs into MariaDB with the specified user.

- `journalctl -n 100`: Displays the last 100 lines of the journal log.

- `journalctl -n 100 -x`: Displays the last 100 lines of the journal log with extended information.

- `mysql -u replicant -p`: Logs into MariaDB as the "replicant" user.

- `mysql -u replicant -p -h 192.168.1.164 -P 3306`: Logs into MariaDB at the specified IP address and port as the "replicant" user.

- `mysql -u root -p -h 192.168.1.102 -P 3306`: Logs into MariaDB at the specified IP address and port as the root user.

- `mysql -u XXX-TST-DB01 -p -h 192.168.1.102 -P 3306`: Logs into MariaDB at the specified IP address and port as the specified user.

- `mysql -u 6dgsupport -p -h 192.168.0.100 -P 3306`: Logs into MariaDB at the specified IP address and port as the specified user.

6. MariaDB Administrative Commands

- `SHOW SLAVE STATUS \G`: Shows the status of the MariaDB replication slave.

- `SELECT User, Host FROM mysql.user;`: Lists all users and their associated hosts in the MariaDB user table.

- `SHOW GRANTS;`: Displays the privileges granted to the current user.

- `SHOW GRANTS FOR CURRENT_USER;`: Displays the privileges granted to the current user.

7. Changing MariaDB Data Directory and Permissions

- `sudo mv /var/lib/mysql /var/lib/mysql.bak`: Moves the MariaDB data directory to a backup location.

- `sudo mv /var/lib/mysql.bak /var/lib/mysql`: Restores the MariaDB data directory from the backup location.

- `sudo cp /var/lib/mysql /var/lib/mysql/mysql2`: Copies the MariaDB data directory to a new location.

- `sudo rsync -av --progress /var/lib/mysql/ /var/lib/mysql/mysql2 --exclude mysql2`: Synchronizes the MariaDB data directory to a new location, excluding the specified directory.

- `chown -R mysql:mysql /var/lib/mysql/mysql2`: Changes the ownership of the new data directory to the MySQL user and group.

- `ls -l /var/lib/mysql/mysql2`: Lists the contents of the new data directory with details.

- `sudo mkdir /var/lib/mysql/mysql2`: Creates a new directory for the MariaDB data.

- `sudo cp -R /var/lib/mysql/* /var/lib/mysql/mysql2/`: Copies the contents of the MariaDB data directory to the new location.

- `cat /etc/my.cnf`: Displays the content of the MariaDB configuration file.

- `cat /etc/my.cnf.d/mariadb-server.cnf`: Displays the content of the MariaDB server configuration file.

8. Default and Custom Settings

- `[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock`: Default setting for the MariaDB data directory and socket.

- `[mysqld] datadir=/mnt/disk00/mysql socket=/mnt/disk00/mysql/mysql.socks`: Custom setting for restoring data to a different directory.

- `[mysqld] datadir=/var/lib/mysql/mysql2 socket=/var/lib/mysql/mysql2/mysql.sock`: Test setting for the MariaDB data directory and socket.

9. Editing Configuration Files

- `nano /etc/my.cnf`: Opens the MariaDB configuration file for editing using the nano text editor.

- `nano /etc/my.cnf.d/mariadb-server.cnf`: Opens the MariaDB server configuration file for editing using the nano text editor.

- `sudo rsync -av /var/lib/mysql /new_directory`: Synchronizes the MariaDB data directory to a new location.

- `sudo rsync -av /var/lib/mysql /mnt/disk00`: Synchronizes the MariaDB data directory to a specified mount point.

- `chown -R mysql:mysql /var/lib/mysql`: Changes the ownership of the MariaDB data directory to the MySQL user and group.

- `chown -R mysql:mysql /mnt/disk00/mysql`: Changes the ownership of the specified directory to the MySQL user and group.

Understanding and effectively using these commands allows for efficient management and configuration of MariaDB, ensuring smooth operation and maintenance of the database system. Whether you're stopping and starting services, reviewing logs, managing user access, or changing data directory settings, these commands are essential tools in a database administrator's toolkit.

################################################

Understanding the Script: A Breakdown of Commands and Actions

Disclaimer: While I can provide a general interpretation of the script, specific context and the exact environment in which it's executed are crucial for a full understanding.

Overview

The script appears to be a collection of commands used to manage a MariaDB database server, primarily focusing on:

• Starting, stopping, and checking the status of the MariaDB service

• Reviewing system logs and configuration files

• Accessing the MariaDB database

• Modifying the MariaDB data directory

Breakdown of Commands

Basic System Commands

• sudo: Executes commands with root privileges.

• systemctl: Manages system services.

  • systemctl stop mariadb: Stops the MariaDB service.

  • systemctl start mariadb: Starts the MariaDB service.

  • systemctl status mariadb: Checks the status of the MariaDB service.

• cat: Displays the contents of a file.

• df -h: Shows disk usage information in a human-readable format.

• mount -a: Mounts all filesystems listed in /etc/fstab.

• history: Displays a list of previously executed commands.

• su: Switches to a different user.

• crontab -l: Lists cron jobs.

• ls -l: Lists files in a long format, showing permissions, ownership, and size.

MariaDB-Specific Commands

• mysql: The MariaDB client.

  • mysql -h 192.168.0.102 -u XXX-TST-DB01-adm -p: Connects to the MariaDB server at IP 192.168.0.102 as user XXX-TST-DB01-adm.

• SHOW SLAVE STATUS \G: Displays the status of a MariaDB slave server.

• SELECT User, Host FROM mysql.user;: Queries the mysql.user table to list users and their hosts.

• SHOW GRANTS;: Shows the privileges granted to the current user.

Log Inspection and Troubleshooting

• tail -n 10 /var/log/mariadb/mariadb.log: Displays the last 10 lines of the MariaDB error log.

• journalctl -u mariadb | grep 'Access denied': Filters journal entries related to MariaDB for "Access denied" errors.

Data Directory Modification

• mv: Moves files or directories.

• cp: Copies files or directories.

• rsync: Synchronizes files and directories.

• chown: Changes file ownership.

Potential Use Cases

Based on the commands, the script might have been used for:

• Troubleshooting MariaDB issues: Checking logs, restarting the service, and reviewing configuration files.

• Migrating MariaDB data: Moving the data directory to a different location.

• Setting up MariaDB replication: Configuring slave servers and checking replication status.

• Securing MariaDB: Reviewing user privileges and access controls.

Note: Without more context, it's difficult to provide a more specific interpretation. If you have additional information about the environment or the specific goals of the script, please feel free to share it.

#########################################################

Use Case Examples for the Commands

Basic System Commands

• sudo:

  • Use Case: Install a new package: sudo apt install vim

• systemctl:

  • Use Case: Start the SSH service: sudo systemctl start ssh

• cat:

  • Use Case: View the contents of a configuration file: cat /etc/nginx/nginx.conf

• df -h:

  • Use Case: Check disk space usage: df -h

• mount -a:

  • Use Case: Mount all filesystems listed in /etc/fstab after a system reboot.

• history:

  • Use Case: Review past commands to recall a specific command.

• su:

  • Use Case: Switch to the root user to perform administrative tasks: sudo su

• crontab -l:

  • Use Case: List scheduled tasks: crontab -l

• ls -l:

  • Use Case: List files in a directory with detailed information: ls -l /var/log

MariaDB-Specific Commands

• mysql:

  • Use Case: Connect to a MariaDB database: mysql -u root -p

• SHOW SLAVE STATUS \G:

  • Use Case: Check the replication status of a MariaDB slave server.

• SELECT User, Host FROM mysql.user;:

  • Use Case: List all users and their hosts in the MariaDB database.

• SHOW GRANTS;:

  • Use Case: View the privileges granted to the current user.

Log Inspection and Troubleshooting

• tail -n 10 /var/log/mariadb/mariadb.log:

  • Use Case: Check the most recent errors in the MariaDB log.

• journalctl -u mariadb | grep 'Access denied':

  • Use Case: Find journal entries related to MariaDB access denied errors.

Data Directory Modification

• mv:

  • Use Case: Move a file or directory: mv old_file.txt new_file.txt

• cp:

  • Use Case: Copy a file or directory: cp file.txt backup_file.txt

• rsync:

  • Use Case: Synchronize files and directories between two systems: rsync -av /src/ /dest/

• chown:

  • Use Case: Change the owner of a file or directory: chown user:group file.txt

By understanding these use cases, you can effectively utilize these commands to manage your system and databases.