Mariadb – Took a full backup of a running mariadb server and restored, but none of the databases are visible

backupgaleramariadbrestore

I'm setting up a mariadb galera cluster to ultimately switch production to. However, I don't have the ability to stop the production instance so I'm trying to set this up using various workarounds I've come across. The current setup relies on a pair of mariadb servers as a master/slave pair. I've added a 3rd mariadb server and I'm restoring a full backup to third, empty server.

To backup the server I executed the following:

mysqldump -u root -p --all-databases --master-data=2 | gzip > backup.sql.gz

once the new server was ready, I restored the database:

zcat backup.sql.gz  | mysql -u root --port=3309 --socket=/var/run/mysqld/mysqld400.sock

When I log into mysql and do a show databases, I don't see anything other than the default db's (information, performance, and mysql). I stopped and restarted mariadb and it still the same. Is there another step I need to point to the data that was just restored?

Best Answer

No matter what approach you take there will be at least a brief downtime.

You have M->S, and want G-G-G, correct?

To get another slave from M->S, stop the slave, clone the disk, change server_id, start them up, let replication catch up. The master does not go down, but you will have S<-M->S. But that is still not what you need.

I think (but am not sure) that this is the approach. (There is not a lot of Galera help here, so I will give you more than nothing.)

Optionally keep read access going to your 1 slave.
Stop the Master.
Bootstrap the Master into the first Galera Node.
Stop the Slave.
Turn the Slave into the second Galera Node.
If you have the other Slave do 4&5 to it. If not give Galera an empty machine and let it build the 3rd node.

Step 6 could be replaced with setting up a "garbd" node -- This is probably a lot faster since no data is needed. You could do a Step 7 to build a 'real' node and get rid of the garbd.

Related Solutions

Mysql – Does a whole-server backup guarantee a reliable backup of running databases

For a filesystem backup to be consistent MySQL requires that the DB be stopped http://dev.mysql.com/doc/mysql-backup-excerpt/5.0/en/backup-methods.html but as you said there are ways of using the DB provided tools to create consistent online backups.

Other DB engines such as Oracle have a mode you can put the DB files into prior to a known time of a filesystem copy to ensure that what's on disk at that time is consistent but you have to know the exact time the backup will start and finish.

there are other DB formats that are also consistent by virtue of the fact that their files are 100% append only such as CouchDB.

But for the most part backups of a running DB require some kind of intervention using the DB engine itself.

SQL Server – Implement Ola Hallengren Backup Plan for Different Databases

No you don't need to install the Ola's solution again since the initially installed commandexecute.sql and databasebackup.sql would have been saved on master DB as SP.

You just need to create a new job , as one created early but this time for 5 left over DB;s and schedule it weekly per you're timings.

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',-- Name of those 5 DB's would come here
@Directory = 'C:\Backup',-- You're backup path
@BackupType = 'FULL',
@Verify = 'Y',
@Compress = 'Y',
@CheckSum = 'Y'

enter image description here You can use above script while creating the T-SQL job and schedule it accordingly.

More details on how to do above refer to schedule job

Also, just to make sure both, initial backup and this newly created backup job not to run on same time you can put a condition or per the analysis when you're daily backup job completes, you can schedule this new weekly/nightly backup job for remaining 5 DB's

Best Answer

Related Solutions

Mysql – Does a whole-server backup guarantee a reliable backup of running databases

SQL Server – Implement Ola Hallengren Backup Plan for Different Databases

Related Question