1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

How to duplicate MYSQL database on CentOS VPS?

Discussion in 'Server Configuration and Hosting' started by dB9c1NznMl, Mar 18, 2015.

  1. dB9c1NznMl

    dB9c1NznMl Guest

    Hello, we have been trying to duplicate a MYSQL database on a CentOS VPS, sadly with no luck. We can't export it, as it is too big. Does anybody know how we would go about duplicating it? Thanks in advance!
  2. Brogan

    Brogan XenForo Moderator Staff Member

    How big is it and how are you exporting it?

    There shouldn't really be any problem via shell.
  3. dB9c1NznMl

    dB9c1NznMl Guest

    @Brogan We are exporting it using phpmyadmin. How would one go about it using shell?
  4. Itworx4me

    Itworx4me Well-Known Member

    First off you would need a ssh client:

    Then you would need to use theses commands to backup:
    mysqldump --opt -Q -u dbusername -p databasename > backupname.sql

    Or to create a backup in a separate directory (signified by /path/to/) type:

    mysqldump --opt -Q -u dbusername -p databasename > /path/to/backupname.sql

    Then use theses commands for restoring:

    mysql -u dbusername -p databasename < backupname.sql

    Or if you do not want to change directories and you know the path to where the backup is located, type in the following:

    mysql -u dbusername -p databasename < /path/to/backupname.sql

    Hope this helps....
    dB9c1NznMl likes this.
  5. Brogan

    Brogan XenForo Moderator Staff Member

    I prefer to use this for dumping:
    mysqldump -u -p --single-transaction --skip-lock-tables database_name > /path/to/backup/database_name_$(date +%d.%m.%y).sql
  6. WSWD

    WSWD Well-Known Member

    Where are you trying to export it to, your local computer?
  7. dB9c1NznMl

    dB9c1NznMl Guest

    WSWD likes this.
  8. Marcus

    Marcus Well-Known Member

    Google for mysql replication. After replication you can promote your replica to its own database. Its awesome.
    RoldanLT likes this.
  9. bwfcwalshy

    bwfcwalshy Member

    Hey, I tried that and no backup was made.

    [root@infernaldevelopers ~]# mysqldump -u root -p --single-transaction --skip-lock-tables xenforo > /var/www/html/backups/xenforobackup_$(date +%d.%m.%y).sql
    Enter password:
    I did that and entered my password but there was no message and no backup.
  10. Tracy Perry

    Tracy Perry Well-Known Member

    I'm assuming that you did create the location that the /path statements point to, correct? If running on a VPS it should have worked (either one) if the paths existed.
  11. bwfcwalshy

    bwfcwalshy Member

    Yes I did create it.
  12. Tracy Perry

    Tracy Perry Well-Known Member

    Be sure that after the -u that you place the DB username.

    I just did a simple copy/paste of Brogans code and placed the username for my DB by the -u and changed the paths and it worked fine.
  13. bwfcwalshy

    bwfcwalshy Member

  14. Tracy Perry

    Tracy Perry Well-Known Member

    Is what it took on mine.

    -u is the short version of --user


    I have found that using a space in between sometimes (for some reason) would not work, but putting the name adjacent to the -u always seemed to work.
  15. bwfcwalshy

    bwfcwalshy Member

    Same thing, no backup.
  16. Tracy Perry

    Tracy Perry Well-Known Member

    mysql --version returns what?
  17. TMC

    TMC Active Member

    Unrelated to the issue, but I would suggest storing the database backup in non public (not in /html/*) directory for security. Or at the very least protect the /backups folder from public access, if not done already.
  18. Slavik

    Slavik XenForo Moderator Staff Member

    did you change the "xenforo" in pauls command to the name of your database?
  19. bwfcwalshy

    bwfcwalshy Member

    The name of my database is xenforo

    It was only going to be there for a short period of time.

    mysql Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1
  20. Marcus

    Marcus Well-Known Member

    Mysql does need some time to take the database dump, it is totally possible that it takes a few minutes or much more, before you get any feedback.

    #1 you enter this:
    [root@infernaldevelopers ~]# mysqldump -u root -p --single-transaction --skip-lock-tables xenforo > /var/www/html/backups/xenforobackup_$(date +%d.%m.%y).sql

    #2 mysql asks you for your password
    Enter password:

    #3 nothing happens, you do not get a single character back from the system. This step might take a few minutes or half an hour.

    #4 after everything is done, mysql gives you a feedback message.

    And I totally agree with @TMC , if everyone can access your database on infernaldevelopers.com/backups/xenforobackup_currentdate.sql you are in huge trouble.

Share This Page