Cpanel Database restore: copying and restoring databases MYSQLDUMP

You can't just copy mysql files on a active server, you will mess up your tables. And phpmyadmin often doesn't work and has limitations. The best way to copy or restore a database is from the command line itself using root SSH.

Syntax:

We recommend first SSH'ing into your server via root (Goto directory /var/lib/mysql): this is where all of your raw mysql files and directories are. You will need to know the database name, database username and database password of the database you want to copy or backup. Make sure the MYSQL directory you are copying from has a 777 permission, else it may not copy.

Use the following command and insert username, password and database names where needed

mysqldump -u username -p databasename > dumpfile.sql
the system should ask you for your database password

This will create a dump file containing your database information and it will be stored in the directory you are currently in which should be /var/lib/mysql. Ok, next thing we need to do is restore this dumpfile to your new location. Create a "BLANK" database in the cpanel interface and note its username, password, name, etc. We will be taking the dumpfile we just created and loading it into it. Make sure that the new database you created has a 777 directory in /var/lib/mysql else it may not copy in. You then use the following commands:

mysql
use NEWdatabasename;
source dumpfile.sql


Allow it to churn a little while. You should see tables being installed. Type quit to get our of the mysql interface. You are done!



Tutorial by
WEBSTER COMPUTING SERVICES (webcs.com)
Dedicated server hosting and server maintenance