How to save data

Questions regarding the Database Server
MySQL, PostgreSQL, MariaDB, Percona Server, phpMyAdmin, phpPgAdmin
mpjoke
Posts: 4
Joined: Wed Oct 11, 2017 7:22 am

How to save data

Postby mpjoke » Mon Nov 06, 2017 9:36 am

Hello.
I have files with database and files of the site.
BUT i cant restore database complitely...
Where i have a mistake?

I install vestaCP on Debian 8, then add some sites and create some empty databases (my_db).
Then i copy the same named files above empty databese and go to phpmyadmin.

I can see there for about 150 tables but i cant open it (wrong rights).
So i have changed rights to this folder and files to "chmode -R mysql:mysql /var/lib/mysql/my_db"
After if i can see only 30-37 tables and have access to half of them..

But, for example, in phpmyadmin search i can see all tables... But cant search in them...

HOW can i restore database complitely??



P.S.
When i get debian and install apache +php5+mysql+phpmyadmin and go to the phpmyadmin...
Create database and copy my database above empty i get the same result..

mehargags
Moderators
Posts: 787
Joined: Sat Sep 06, 2014 9:58 pm
Contact:

Re: How to save data

Postby mehargags » Mon Nov 06, 2017 9:59 am

you cannot restore a DB straight from the MySQL dbstore /var/lib/mysql/my_db

On the source server, you need to EXPORT your DBs with mysqldump command

Code: Select all

mysqldump dbname > ./exportedfile.sql

Copy exportedfile.sql on the VestaCP server, then import it using

Code: Select all

mysql dbname < ./exportedfile.sql

mpjoke
Posts: 4
Joined: Wed Oct 11, 2017 7:22 am

Re: How to save data

Postby mpjoke » Mon Nov 06, 2017 7:07 pm

I understand and know about Import/Export but the trouble is that i cant run old server beacouse of some errors on disk.. I can only copy files from it..

mehargags
Moderators
Posts: 787
Joined: Sat Sep 06, 2014 9:58 pm
Contact:

Re: How to save data

Postby mehargags » Mon Nov 06, 2017 9:32 pm

oh... well, will be a bit tedious. You will need to copy the entire /var/lib/mysql/ directory to the new server and then copy it back to a fresh server with MySQL installed.

In Depth article here

in nutshell

Backup Entire MySQL Data Directory /var/lib/mysql/ (on Crashed Server)

Code: Select all

systemctl stop mysql

tar -cvf mysql.tar /var/lib/mysql/

transfer it to the new server


Restoring an Entire DBMS From Backup (On new server)

Code: Select all

systemctl stop mysql

mv /var/lib/mysql/* /someplacesafe

extract copied tarbal to

Code: Select all

/var/lib/mysql/

Code: Select all

chown -R mysql:mysql /var/lib/mysql

systemctl restart mysql


additionally

Code: Select all

mysqlcheck --all-databases -o
mysqlcheck --all-databases --auto-repair
mysqlcheck --all-databases --analyze


Return to “Database Server”



Who is online

Users browsing this forum: No registered users and 5 guests