XF 1.3 How can I copy a DB dump to my localhost ?

[rhodes]

Hello,

I made a database dump of my xf1.3 production environment and tried to restore the dump to my local db using mysql on the command line (Max OSX). Everything seems fine, no error, but when I tried to start my local xf1.3 (develop.dev/community) only a white screen appears.

What is my mistake? How can I synchronize production and development data?

Thank you, rhodes.

 

[Paul B]

View the page source, that may show the actual error.

When I update my local I do the following:

1. Take a dump of the live database using mysqldump
   1. mysqldump -u<user> -p<password> --single-transaction --skip-lock-tables db_name > /path/to/backup/db_name_$(date +%d.%m.%y).sql
   2. tar -czf db_name_$(date +%d.%m.%y).tar.gz /path/to/backup/db_name_$(date +%d.%m.%y).sql
2. Zip the entire installation directory
   1. tar -czf files_$(date +%d.%m.%y).tar.gz /path/to/install
3. Download the zipped database dump and files
4. Delete all existing files from my localhost installation directory
5. Extract the downloaded zipped files to my localhost installation directory
6. Update config.php with the localhost db details - db name, user, password
7. Drop the tables from the localhost db
8. Import the live db using
   1. mysql -uroot -proot db_name_dev <db.sql

 

[rhodes]

Thank you @Brogan , I will try this exactly as you did.

 

[Paul B]

There's also a guide here: http://xenforo.com/community/resources/how-to-duplicate-a-live-site-to-use-as-a-test-site.2792/

 

[rhodes]

Thank you for the link.

Unfortunately it does not work as expected.

When I try to import the xf1.3 DB into my test environment

ERROR: ASCII '\0' appeared in the statement, but this is not allowed unless option --binary-mode is enabled and mysql is run in non-interactive mode.

Hm, I'm not sure where this error comes from. Any ideas?

 

[Paul B]

That sounds like a configuration issue with your local installation.

You need to resolve this: "but this is not allowed unless option -- binary-mode is enabled and mysql is run in non-interactive mode".

 

[TPerry]

Are you sure the file is in UTF-8 format
http://stackoverflow.com/questions/18538142/unable-to-restore-a-mysql-backup-to-a-new-database
http://errors-quick-fix.blogspot.com/2014/01/error-ascii-0-appeared-in-statement-but.html

 

[woei]

You can also try with mysqlhotcopy. I find that better then using mysqldump. Much faster. Don't forget to stop mysql when copying the backup to the live environment to be on the safe side. Also with some advanced commands you can easily automate the whole process

See example commands here: http://www.thegeekstuff.com/2008/07/backup-and-restore-mysql-database-using-mysqlhotcopy/

 

[rhodes]

Thank you, @woei