ERROR 1790 - Restoring a MemSQL 7.1 Database Backup in a Multi-Server Cluster Config

Hi, I currently have a MemSQL 7.1.8 cluster setup in two self-managed CentOS 7 servers. Both of the servers are able to communicate to each other via SSH, as well as TCP ports 22, 3307, 3308, 3309, & 8080, and all database operations (SELECT, INSERT, UPDATE, etc) are verified fully working. Below is the config:

Server 1 - Local Network IP - 192.168.1.3 (13 TB disk space)

  • Master Aggregator Node - Port 3307

Server 2 - Local Network IP - 192.168.1.4: (5.76 TB disk space)

  • Aggregator Node - Port 3308
  • Leaf Node - Port 3309

I am attempting to restore a 7.1.3 database backup, taken from another system, onto this 2-server cluster and receiving ERROR 1790. This was after copying all of the database files from an external HD onto the /backups/memsql directory on Server #1 (192.168.1.3).

The database size is around 4 TB and those files were only copied to Server #1 since has much more hard disk capacity at 13 TB, and it contains the Master Aggregator node. The Leaf node is located on Server #2 with just 5.76 TB in total capacity, and figured it cannot host both backup files (of 4 TB in size) and a fully restored 4 TB server as that would utilize 8 TB out of an available 5.76 TB. However, I believe Server #2 may not be able to access the database backup files on Server #1 due to the below error in the restoration process:


RESTORE DATABASE db_data FROM "/192.168.1.3/backups/memsql/";

ERROR 1790 (HY000): Restore operation could not be completed because path '/192.168.1.3/backups/memsql/db_data.backup' on node '192.168.1.3:3307' is inaccessible or doesn't point to a MemSQL binary backup file. Error returned from source: Failed to open file: /192.168.1.3/backups/memsql/db_data.backup (2:No such file or directory)


I was able to confirm all database backup files do exist in the /backups/memsql directory, with the following properties across the folders and files allowing the ‘memsql’ user access:

  • chmod u=rwx,g=rx,o=rx /backups/memsql/*
  • chown memsql:memsql /backups/memsql/*

Would it be possible to restore a MemSQL 7.1 database backup, given a two server cluster configuration, with the database files on another server (and IP) from the leaf node?

Thanks

Hi,

You can not add IP address to the path of the backup. I think the problem is that your path to backup is not accessible from both servers. When you run something like

RESTORE DATABASE db_data FROM “/backups/memsql/”;

It means that when you do

ls /backups/memsql

on both servers you should get the same content. I understand that you only have enough disk space on the first server so you can not copy the same content on the second node, but you can solve this by using nfs (https://www.howtoforge.com/nfs-server-and-client-on-centos-7). Share the content of /backups/memsql from master node using nfs-server and on the second server mount the content also to /backups/memsql and then do

RESTORE DATABASE db_data FROM “/backups/memsql/”;

Hope it helps.

Thanks Tomas.

This is a wonderful solution for next time. I was able to resolve this by plugging the external hard drive to server #2 and mounting it to a directory using the same path-name as the backup on server #1. MemSQL 7.1 was then able to fully restore the database with that method.