The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

fastest way to restore 5gb db

Discussion in 'E-mail Discussions' started by bhanuprasad1981, Jul 2, 2010.

  1. bhanuprasad1981

    bhanuprasad1981 Well-Known Member

    Joined:
    Aug 5, 2008
    Messages:
    222
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    India
    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 :(
     
  2. Miraenda

    Miraenda Well-Known Member

    Joined:
    Jul 28, 2004
    Messages:
    242
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Coralville, Iowa USA
    There's a fast way and a better way. The better way is to use mysqldump to get the database:

    Code:
    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:

    Code:
    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:

    Code:
    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):

    Code:
    mysql database_name < FILE.sql
    As from my prior example, it would be the following:

    Code:
    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):

    Code:
    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):

    Code:
    +---------------+-------------------+
    | TABLE_SCHEMA  | table_name        |
    +---------------+-------------------+
    | roundcube     | cache             | 
    | roundcube     | contacts          | 
    | roundcube     | identities        | 
    | roundcube     | messages          | 
    | roundcube     | session           | 
    | roundcube     | users             | 
    +---------------+-------------------+
    6 rows in set (0.06 sec)
     
    #2 Miraenda, Jul 2, 2010
    Last edited: Jul 2, 2010
  3. aymanfekri

    aymanfekri Registered

    Joined:
    Feb 26, 2007
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    you can use mysqlhotcopy or illegal copy for /var/lib/mysql/databasename/ content
    Thank you
     
  4. bhanuprasad1981

    bhanuprasad1981 Well-Known Member

    Joined:
    Aug 5, 2008
    Messages:
    222
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    India
    thanks guys you guys helped a lot , i did copy files and it worked :D
     
Loading...

Share This Page