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.

SSH command to copy all databases to a new location

Discussion in 'Database Discussions' started by mambovince, Nov 10, 2010.

  1. mambovince

    mambovince Well-Known Member

    Joined:
    Jan 15, 2005
    Messages:
    192
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    London, UK
    Hi,
    Noob question alert:

    Is the following SSH command correct please?
    # copy all databases to a new location
    cp -r /var/lib/mysql /var/lib/mysql.bak

    Many thanks,

    - Vince
     
  2. cPanelJared

    cPanelJared Technical Analyst
    Staff Member

    Joined:
    Feb 25, 2010
    Messages:
    1,842
    Likes Received:
    18
    Trophy Points:
    38
    Location:
    Houston, TX
    cPanel Access Level:
    Root Administrator
    That will work, but it is not the best way to back up MySQL databases. The best way is to use mysqldump. mysqldump outputs the commands that would be used to create the database again from scratch.

    You can even use mysqldump to back up all databases. The following command:

    Code:
    # mysqldump -uroot -p --alldatabases < alldatabases.sql
    will dump all of your databases to a file named alldatabases.sql, which will be a text file. You can then import that file into MySQL to restore all databases.

    You will probably want to specify an individual database, though, so you can restore only one database if needed, not all of them. The following command will dump one database:

    Code:
    # mysqldump -uroot -p dbname < dbname.sql
    This will dump the database named dbname to a file named dbname.sql.

    In my command examples above, you will be prompted for the MySQL root password. This may be different from, the server's root password.

    Copying /var/lib/mysql directories can work, but it can be problematic when you try to restore data. Using mysqldump is the most reliable way to make a good database backup that you can rely on to restore the database later.

    You can find complete information for the mysqldump command in the MySQL documentation, here:

    MySQL :: MySQL 5.1 Reference Manual :: 4.5.4 mysqldump — A Database Backup Program
     
  3. mambovince

    mambovince Well-Known Member

    Joined:
    Jan 15, 2005
    Messages:
    192
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    London, UK
    Hi jared,
    Appreciate your reply.
    Just to make sure I clarify what I'm doing.

    I have one last server still running MySQL 4 and therefore before running the cPanel upgrade to MySQL 5 I just wanted to make a backup of all client databases in case it all goes wrong and I have to revert.

    The method you proposed would be the 'normal' way I'd create a MySQL DB backup, but not sure it's appropriate or convenient in this case with 100 client accounts each having avg 2-3 databases?

    Best wishes,

    - Vince
     
Loading...

Share This Page