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?

  • Did you verify that contents of backup.sql.gz are what you think they are? – mustaccio Jun 4 '19 at 18:10
  • yes, everything is as it should be. – misterjones Jun 4 '19 at 19:12
  • I’d wager that either the restore didn’t complete as expected (add -vvv to the mysql command to confirm) or you’re running more than 1 instance of mysqld on that host and you’re accessing the wrong instance to check the data exists. – eroomydna Jun 5 '19 at 5:43

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.

  • Yes, that's what we have now. The idea is to flip from M>S to G-G-G by running it in parallel. Right now the production instance is on 3306, there's a test instance on 3307, and I have Galera set up on 3309. What I've done as of now is restored the full backup to the new 3rd node. This was a new server with a clean install of mariadb and it's on this node that I'm having difficulty with the restore. Everything looks to be in order until I log into the db and execute a show databases and only the default db's are there. – misterjones Jun 5 '19 at 16:41

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.