Now you have a database backup. In the event that something horrible happens (knock on wood) and the database needs to be restore...
This assumes that the database is located in the default MySQL installation location: /var/lib/mysql/yourdbname
0. Login as root on the database server
1. Stop the mysqld service
service mysqld stop
2. Backup the current corrupted database (in case forensic analysis is needed later)
cp /var/lib/mysql/yourdbname /var/lib/mysql/yourdbname_backup
3. Empty the files from the database.
cd /var/lib/mysql/yourdbname
rm -rf *
4. Copy the backup to the empty database shell
cp /path/to/your/backup/* /var/lib/mysql/yourdbname/
*make sure that the folder /var/lib/mysql/yourdbname/ only contains MYI, MYD, frm and opt files ONLY.
5. Ensure that permissions are correctly set.
cd /var/lib/mysql/yourdbname/
chown mysql:mysql *
chmod 771 *
6. Start the Mysql Service.
service mysqld start
7. Run a Check/Repair/Optimize on the restored database.
This is to ensure consistency of the data you just restored.
There is no need to login to mysql, simply execute from the terminal.
mysqlcheck yourdbname -c -o -a -r -f -h localhost -u yourdbusername -p yourdbpassword
Note: If the database for the Moodle install is large, a weekly or nightly optimization can be configured to minimize the chances of irrepairable corruption.
This can be done similarly to the cron job in my earlier post.
7.1 Create script
cd /temp/scripts
touch database_check.run
chmod +x database_check.run
7.2. Edit the script
nano database_check.run
Add the following (I chose to log each time the script runs)
mysqlcheck yourdbname -c -o -a -r -f -h localhost -u yourdbusername -p yourdbpassword > /temp/logs/database_check_$(date +%Y%m%d)_$(date +%H%M).txt
The log folder needs to be created so the script does not throw an error!
cd /temp
mkdir logs
7.3 Test
./temp/scripts/database_check.run
check the latest logfile for output
cat /temp/logs/database_check_xxxxxx.txt
You should see some output with the table names and status next to it - e.g
mdl_config OK
-n
