• 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?

D

dB9c1NznMl

Guest
#1
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!
 

Itworx4me

Well-known member
#4
@Brogan We are exporting it using phpmyadmin. How would one go about it using shell?
First off you would need a ssh client:
http://www.putty.org/

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....
 
#9
I prefer to use this for dumping:
Code:
mysqldump -u -p --single-transaction --skip-lock-tables database_name > /path/to/backup/database_name_$(date +%d.%m.%y).sql
Hey, I tried that and no backup was made.

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

Tracy Perry

Well-known member
#10
Hey, I tried that and no backup was made.

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

TMC

Active member
#17
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.
 
#19
did you change the "xenforo" in pauls command to the name of your database?
The name of my database is xenforo

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.
It was only going to be there for a short period of time.

mysql --version returns what?
mysql Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1
 

Marcus

Well-known member
#20
I did that and entered my password but there was no message and no backup.
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.