How to duplicate MYSQL database on CentOS VPS?

[dB9c1NznMl]

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!

 

[Paul B]

How big is it and how are you exporting it?

There shouldn't really be any problem via shell.

 

[dB9c1NznMl]

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

 

[Itworx4me]

@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....

 

[Paul B]

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

 

[WSWD]

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

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

 

[dB9c1NznMl]

@Itworx4me @Brogan Thanks so much!

@WSWD No, just to the server.

 

[Marcus]

Google for mysql replication. After replication you can promote your replica to its own database. Its awesome.

 

[bwfcwalshy]

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

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.

 

[TPerry]

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.

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.

 

[bwfcwalshy]

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.

Yes I did create it.

 

[TPerry]

Yes I did create it.

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.

 

[bwfcwalshy]

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.

-u root

 

[TPerry]

-uroot
Is what it took on mine.

-u is the short version of --user

http://dev.mysql.com/doc/refman/5.6/en/mysql.html

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.

 

[bwfcwalshy]

-uroot
Is what it took on mine.

Same thing, no backup.

 

[TPerry]

mysql --version returns what?

 

[TMC]

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.

 

[Slavik]

Same thing, no backup.

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

 

[bwfcwalshy]

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]

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.