Bacula Restores

Bacula DB Restore

https://kb.ukfast.net/Restore_MySQL_from_Bacula  

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

1. SSH onto backup server.

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

2. Initiate a restore

 Run the following commands to initiate a restore using the backup jobID:

Bconsole > restore > 3 > enter Job IDs, comma seperated. 

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

3.  Mark files to be restored

Once the directory tree has been built we need to mark the /var/lib/mysql directory, and initiate the restore:

mark /var/lib/mysql

Run 'done' once all required files have been marked

done

Select option 9 (where):

9

Once here, you need to enter the directory we're restoring TO:

/root/restore_TICKETNUMBER 

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

 4. Starting 2nd MySQL Instance

Once MySQL has been restored onto client server, we then need to start a 2nd instance of mysql so that we can dump the required databases.

The below command starts the 2nd MySQL instance (You need to replace /mnt/mysql with the path we've restored MySQL to; in my example this is /root/restore_TICKETNUMBER).

/usr/sbin/mysqld --socket=/tmp/mysql2.sock --datadir=/mnt/mysql --skip-networking --pid-file=/tmp/mysql2.pid --user=mysql --skip-grant-tables

Running this command will take over your session, meaning that you'll have to leave this running and open a fresh SSH session.

If you're encountering errors when attempting to start the 2nd instance, it would be worth having a google of the errors. If you're still having issues, please see below:

If you're unable to start the 2nd MySQL instance after troubleshooting, there's 2 main options:

To do this, add the following line into /etc/my.cnf: 

innodb_force_recovery = 1 

--innodb-force-recovery=1 

 
Innodb_force_recovery has 6 levels, start with 1 and work through to 6, hopefully one of these will start the mysql instance 

 

Ensure you notify the client that the database had to be restored using the force recovery feature and link to the following doc 

https://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html 

 

IMPORTANT! Ensure that you remove the innodb_force_recovery entry from /etc/my.cnf once finished 

 

If errors are being shown regarding the existing my.cnf, you can choose to launch the instance with a setting to ignore my.cnf: 

 

--no-defaults  

 

Example: 

/usr/sbin/mysqld --no-defaults --socket=/tmp/mysql2.sock --datadir=/home/restore_4450884/mysql --skip-networking --pid-file=/tmp/mysql2.pid --user=mysql --skip-grant-tables 

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

5. Dumping database

Now that we've got the restored MySQL instance running, we can look to validate the data we require is present, and dump out the database.

Connect to restored MySQL instance:

mysql -S /tmp/mysql2.sock

Check that the required database is present:

SHOW DATABASES;

Once we've confirmed that the databases we need is present, we can look to dump out the database into a file:

Run the following command, substituting 'databasename' with the name of your required database, and also updating 'database_restoreTICKETNUMBER' with the appropriate ticket number.

mysqldump -S /tmp/mysql2.sock databasename --events --triggers --routines > /root/database_restoreTICKETNUMBER.sql

Other options for dumping a database/s

All data and databases :

mysqldump -S /tmp/mysql2.sock --all-databases --events --triggers --routines > /root/database_restoreTICKETNUMBER.sql

To dump several but not all databases (substitute databasename1,databasename2 etc) :

mysqldump -S /tmp/mysql2.sock --databases databasename1 databasename2 --events --triggers --routines > /root/database_restoreTICKETNUMBER.sql

For a specific table only (substitute databasename and tablename)  

mysqldump -S /tmp/mysql2.sock databasename tablename > /root/database_restoreTICKETNUMBER.sql

If you're encountering errors when attempting to dump the required data, you can use the -f flag to ignore errors.

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

6. Cleaning up

Once the dump is complete, terminate the 2nd instance:  

mysqladmin -S /tmp/mysql2.sock shutdown

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