Restore mysql database backup to new Sentora

[kevwebbie]

Hi all,
I've been using Sentora for a long time and love it. Recently one of my servers which is hosting zpanel got corrupted. I needed to find a way to get the database files onto a new Sentora install. So far I have copied the databases via scp from the old server to the new server. I have also copied them into /var/lib/mysql on the new server so they are safe. BUT I can't find a way to make them show in Sentora so I can set up the websites again. I also can't copy the data into a new database which I created in Sentora.

Does anybody have any suggestions please? I'd be very grateful of any help. I am 95% of the way there. Just need to get these backups into Sentora.

[kevwebbie]

Anyone have any input on how I can do this? ANY help would be appreciated.

[kevwebbie]

Anyone have any input on how I can do this? ANY help would be appreciated.

Okay, so I've managed to get this far:

(1) Copy the databases from the old server onto the new one into /var/lib/mysq/ by using:
$scp -r /var/lib/mysql/databasename root@serverip:/var/lib/mysql/  <-- Done on the old corrupt server onto the new server.
Then

(2) I changed privileges on the database to give mysql full access to same by logging into mysql:
$ mysql -u root -p *pass*
mysql> use database name;
mysql> show tables; <-- shows error due to privileges
mysql> GRANT ALL PRIVILEGES ON mydatabase.* TO 'mysql';
mysql> exit;
$ sudo service mysql restart
- Log into mysql once more
$ mysql -u root -p *pass*
mysql> use database name;
mysql> show tables; <-- Now mysql will show the tables

(3) So when I log into Sentora, I can log in to phpmyadmin as "root" (with root pass) and I will can the databases I've copied to /var/lib/mysql. I can also see the tables of the databases that I've granted permissions to mysql but not those of root.

Now I am left with attempting to view the databases within the "zadmin" login of my Sentora installation. I presume this will be done with the actual Sentora database? Maybe I need to grant privileges to the "zadmin" dbase user?

I'm hoping to get one of you Sentora exerts to answer the rest of my question maybe?

---

Update on this
Okay, I should have seen this earlier (Faceplant!). So now that I can connect to the databases via phpmyadmin in Sentora by logging in as root, I'm just going to export the databases to my local computer and then import them back into Sentora via phpmyadmin. Boom! I'm done! Simples!

Maybe someone else can use this info if they get locked out of a server or it gets corrupt.

[kevwebbie]

Just a quick point on the above which I forgot to post. IF you can, it will be best to use mysqldump to dump the database into a .sql file on the first server, then move or download it to the next server or localhost. This will help maintain the consistency of the database. Moving the database files without using the dump can cause structural issues in the database with the ibdata, ibdata1 files. This can cause tables not to show. Use the mysqldump syntax shown below if you can access your server with root access.

$ mysqldump -u root -p *database-name* > *sqlbackupname*.sql
$ Enter mysql password: *your-mysql-password*

The above will create a database dump(backup) to a file called *sqlbackupname*.sql.
Then open your next server or your localhost and type the below to restore the database onto the server you want to move to:

$ mysql -u root -p *database-you-want-to-restore-to* < *sqlbackupname*.sql

I hope this helps someone in the future.