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

Question

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?

Answer 1

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.)

1. Optionally keep read access going to your 1 slave.
2. Stop the Master.
3. Bootstrap the Master into the first Galera Node.
4. Stop the Slave.
5. Turn the Slave into the second Galera Node.
6. 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.

Answer 2

So it turns out the problem is that I wasn't logging into the instance correctly and that's why I couldn't see the databases.

I was logging in with this:

mysql -u root -p --port=3309

When I should have been logging in with this:

mysql -u root -p -s -h 127.0.0.1 --port=3309

So it turns out the backup/restore worked, the only weirdness is that a show database; doesn't return the nicely formatted table, but it does show all the databases.