LHammonds Tutorials

Export User Grants/Revokes The Percona Toolkit comes with a handy utility called pt-show-grants which will allow you to export an SQL file of all the grants for all your databases into a single file that you can use to audit permissions or help with a restore or help with database migrations. This ...

NOTE: I have not tested most of these since I tend to use the command-line. However, Adminer is enjoying frequent updates and is a single php file. Web-based interfaces There are a few web-based tools you can get for a GUI front-end to MariaDB. It is not recommended to install a web server on your ...

Rename root For increased security, you can rename the root account to something else with the following commands: USE mysql; UPDATE USER SET User='NewSecretRoot' WHERE User='root'; FLUSH PRIVILEGES; Review User Accounts SELECT User,Host,Password FROM mysql.user; Make sure everyone has a password s...

Fail2Ban If you have Fail2Ban installed and protecting SSH as part of the base install. You can add these few changes to also watch for database login failures. vi /etc/fail2ban/jail.local Add the following to the bottom. If your data path is different, be sure to update "logpath" [DEFAUL...

Crontab Schedule I would not advise anyone to ever "edit" a live crontab schedule by typing "crontab -e" but rather edit a saved schedule file and then load the schedule file. This will allow you to make backups of the schedule so you can always go back to a known-good schedule ...

Migrate Database from Old Server You can use the full backup automatically made by the "db-backup.sh" script (/bak/mysql/db-all.sql) if you have that already running or you can create a manual backup. The steps below will document how to do it manually but you could just as easily use you...

Database Backup On Demand This script is designed to run every minute looking for key files. If a specific file shows up on the share folder, it will trigger an immediate backup of the specified database. This is helpful when scheduling the backup of an app server and coordinating the backup of the...

This script will let you pick an encrypted archive to be decrypted and extracted to the temp folder. Once extracted, you can manually restore the database(s)/table(s) however you see fit. Create the script: sudo touch /var/scripts/prod/db-decrypt.sh sudo chown root:root /var/scripts/prod/db-decrypt....

Database Backup Script This script is designed to perform a full backup of all databases while the server is online. It actually does several tasks which you can tailor to your needs: Extracts the system database, compresses it into a single archive and encrypts it. Extracts all user databases into...

Scripting Much of the solutions beyond this point involve scripts (programming snippets / automated commands). In particular, they are Bash Scripts. I chose this due to its popularity and the fact it comes with Ubuntu. I try to make use of what comes with the system without requiring additional sof...

Usage Examples Connect to the server using PuTTY. At the login prompt, login with your administrator account. Start a database session and create a database called wordpress and a user called u_wordpress with a password of " mydbpass " and grant the user access to the database: mysql -u r...

Reset lost root password If you ever find yourself in the position of not being able to login (locally) to your database with your root account, you will need to reset the password. For example, phpmyadmin fails to change the password correctly and neither the old nor the new password works. This p...

Relocate the Databases If you do not want to leave your databases in the default location (/var/lib/mysql), then you can follow these steps to move them somewhere else. In this example, we will move them to /opt/mysql At the login prompt, login with your administrator account At the console, type t...

Firewall Rules Edit the firewall script that was created during the initial setup of the server (if you followed my instructions ): sudo vi /var/scripts/prod/en-firewall.sh Add the following rule which allows connectivity from from anything on your local subnet: echo "Adding Database Server ru...

Secure Installation MariaDB comes with a script to tighten-down security for a production server. Perform these steps on the database server. Connect to the server using PuTTY. Start the secure install script: sudo mysql_secure_installation Enter current password for root: Just press ENTER here sin...

Install MariaDB Perform these steps on each database server. Install the database server and required dependencies by typing the following: sudo apt -y install mariadb-server When installation is completed, the database service should automatically start. It is also configured to automatically star...

Crontab Schedule We will be utilizing the crontab schedule of the root user. The scripts that need to run as the low-rights user (like Steam updates) will be automatically switched inside the script so there should not be any issue running these scripts with the higher privilege level. viewtopic.ph...

Scripting - Menu This is the main script that will call most of the other scripts and view their log files. You can download the script straight from my github using the below commands or manually create the file yourself by copying the code from this forum (which may get outdated compared to githu...

Scripting - Restore This script will walk you through restoring an instance from a prior backup archive. You can download the script straight from my github using the below commands or manually create the file yourself by copying the code from this forum (which may get outdated compared to github) ...

Scripting - Backup This script will backup all valid instances, configurations, logs and scripts to date-stamped archives. You can download the script straight from my github using the below commands or manually create the file yourself by copying the code from this forum (which may get outdated co...