fastest way to restore 5gb db

[bhanuprasad1981]

hello i am trying to restore an account with a mysql of 5+gb all sites are going down while this happens i max waited 6 hours to see cpanel still restoring that sql file, any fast way to restore it ? i have old server root login can we do some copy paste that sql folder or any way

[Miraenda]

There's a fast way and a better way. The better way is to use mysqldump to get the database:

mysqldump -u username -ppassword database_name > FILE.sql

If you run this as the root user in root SSH, you can take out the -u username and -ppassword fields and just use:

mysqldump database_name > FILE.sql

Let's say the username is myuser with database name dbtest and you want to save the file as myuser_dbtest.sql, you'd do:

mysqldump myuser_dbtest > myuser_dbtest.sql

You can then restore this database on the new machine with this command (again as root user running it to avoid the username and password details being needed):

mysql database_name < FILE.sql

As from my prior example, it would be the following:

mysql myuser_dbtest < myuser_dbtest.sql

If your file isn't in the location you are at, the path to the file must be provided so /home/myuser_dbtest.sql if you placed it into /home or whatever location.

Of note, the database must already exist to import it this way. If the database does not exist, please create it in the user's cPanel first.

Now, a faster way would be just to copy /var/lib/mysql/user_dbname folder and files to the new machine. The issue with doing this is that if the database is active, it could corrupt. Also, this will very likely corrupt if it has any InnoDB tables as they share the data into the /var/lib/mysql/ibdata1 file, so you can't just copy /var/lib/mysql/user_dbname folders like that (you must do a dump file to get the database instead).

To see which databases have InnoDB tables, you can run this command in mysql CLI (simply enter mysql in root SSH to go into MySQL CLI):

select TABLE_SCHEMA, table_name FROM INFORMATION_SCHEMA.tables where table_type='BASE TABLE' and engine = 'InnoDB';

This will output details like the following to show databases with InnoDB tables (this example is on my machine):

+---------------+-------------------+
| TABLE_SCHEMA  | table_name        |
+---------------+-------------------+
| roundcube     | cache             | 
| roundcube     | contacts          | 
| roundcube     | identities        | 
| roundcube     | messages          | 
| roundcube     | session           | 
| roundcube     | users             | 
+---------------+-------------------+
6 rows in set (0.06 sec)

[aymanfekri]

you can use mysqlhotcopy or illegal copy for /var/lib/mysql/databasename/ content
Thank you

[bhanuprasad1981]

thanks guys you guys helped a lot , i did copy files and it worked