XF 2.0 Backup and Restore of Database Failing

[GoHerdIBG]

I am in the process of testing my dump/restore strategy and can't seem to get things to work.

The database I am dumping is:
mysqld version 5.6 (Linux)

I am using a hosting provider (KnownHost) and have used 3 methods for my database dumps:

1. In cPanel I use phpMyAdmin to export the database selecting the quick method (no options) to an .sql file. I believe this dumps everything including all databases and tables as the file is fairly large (100+M) for a new site with no real content.

2. In cPanel under Backup I "Download a MySQL Database Backup" and chose my xenforo database. This file is compressed but uncomrpressed is about 60M.

3. Using mysqldump with the command line:
mysqldump --opt xenforo --default-character-set=utf8mb4 > /tmp/xenforo.sql

On my local Linux server where I am trying to test my restore, the database is:
MariaDB version 5.5

I am restoring the database using mysql redirected:
mysql -u root -p --default-character-set=utf8mb4 xenforo < xenforo.sql

and also the non-root user (user in the application).

The import completes without any errors.

However, when I attempt to access the forums page, I get a 500 error and can not find the logs where the 500 error is being generated. access_log and error_log for apache are not showing anything.

I have tried dropping the xenforo database and recreating with the same results.

Any assistance would be appreciated.

 

[AndyB]

Do this all from your web host terminal. Be sure to change 'path' to the correct name for your server.

mysqldump --opt -uroot xenforo > /home/path/tmp/backup.sql

then create a new database on your web host called xenforo_test

mysql xenforo_test < /home/path/tmp/backup.sql

At this point you can create a test forum where the config.php points to the xenforo_test database.

 

[GoHerdIBG]

Do this all from your web host terminal. Be sure to change 'path' to the correct name for your server.

mysqldump --opt -uroot xenforo > /home/path/tmp/backup.sql

then create a new database on your web host called xenforo_test

mysql xenforo_test < /home/path/tmp/backup.sql

At this point you can create a test forum where the config.php points to the xenforo_test database.

Thanks for the reply. Still having issues. Here is what I did:

Ran the mysqldump command which did not ask for a password (I am not a database person so this surprised me).

Executed MySQL at the command line to manually create the new database:

create database xenforo_test CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

I pulled the above line from one of the posts on here so I am not sure if the CHARACTER SET or COLLATE are a problem.

Note: I also executed it like this: create database xenforo_test;

I then tried to execute the import as entered but got this:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

I had to enter the command as:

mysql -u root -p xenforo_test < /tmp/backup.sql

I was able to import the database but am still receiving the 500 error.

Thanks again.

 

[AndyB]

Executed MySQL at the command line to manually create the new database:

Do you have cPanel? It allows you to easily create a database with the correct permissions.

 

[GoHerdIBG]

Thanks for the reply again. I don't have cPanel at home on my test server. On my test server when I installed the software I created the database using the same name and user/password from the config.php on the production server - hoping that when I imported all would be well.

The weird part is I am not seeing any errors in my log files - just the 500 when the web server fails.

BTW: do you race motocross or just ride for fun?

Thanks again.

 

[GoHerdIBG]

For some further testing I changed the username/password to the root user and password just to check to see if permissions were an issue. No change. I was also able to log into the db as the xenforo user - just to make sure the user/pw combo was working.

Another funny thing is when I do a full re-install of the application in the browser I have to use the username/password from my production system - which tells me the web application is able to see something.

 

[Kirby]

Do this all from your web host terminal. Be sure to change 'path' to the correct name for your server.

mysqldump --opt -uroot xenforo > /home/path/tmp/backup.sql

then create a new database on your web host called xenforo_test

mysql xenforo_test < /home/path/tmp/backup.sql

At this point you can create a test forum where the config.php points to the xenforo_test database.

Do not follow these instructions or you might loose data

Public service announcement: Check your MySQL backup!

Having moved servers today, we learned that our MySQL backups were subtly corrupted by an insidious issue that can be difficult to spot until it's too late. All emoji and high-ascii characters in our database dumps were invalid. This is due to a behaviour in mysqldump, which defaults the...

xenforo.com

 

[GoHerdIBG]

Do not follow these instructions or you might loose data

Public service announcement: Check your MySQL backup!

Having moved servers today, we learned that our MySQL backups were subtly corrupted by an insidious issue that can be difficult to spot until it's too late. All emoji and high-ascii characters in our database dumps were invalid. This is due to a behaviour in mysqldump, which defaults the...

xenforo.com

I have only been testing with a local instance at home - but will try and follow the steps in the linked post. I saw that post previously but didn't think it applied to my problem but will take another look.

Thanks.

 

[GoHerdIBG]

I did run a local export -> local import test. I exported the xenforo database from my local install, dropped the xenforo database, recreated it manually and granted permissions, then imported the .sql dump and all went well.

As mentioned above, it could be permissions as my user lists are different and my grant lists are different. I am also using MariaDB locally where the provider is using MySQL. I may try to install MySQL on my local instance to see if that allows me to import.

 

[Matthew S]

Silly question - can you do a plain old default installation on your server and it works?

 

[GoHerdIBG]

Silly question - can you do a plain old default installation on your server and it works?

Yes. That's usually how I start out. I perform a standard install on my local server and even use the same username/password/port information from my production server. In theory, my config.php is correct and should not need to be changed.

 

[GoHerdIBG]

I just tried a full export of the db which included the xenforo and MySQL databases. Still no luck. There has to be a way to do what I want to do - which is have a instance locally that I can use as my dev/test version to test things like upgrades to MySQL and PHP, etc. I am not a fan of doing this on a production server. What if I want to move my instance to a new hosting provider? That would be the same thing, right?

 

[Ozzy47]

Did you have a read through this guide,

How to duplicate a live site to use as a test site

These instructions explain how to create a test installation which is an exact duplicate of your live installation. On Your Server Create a new test database with a different database name, user name and password to your live installation...

xenforo.com

 

[GoHerdIBG]

No. Didn't see that post in my searches. I will give it a try. Appreciate the link and will let you know how it goes.

 

[Slavik]

I just tried a full export of the db which included the xenforo and MySQL databases. Still no luck. There has to be a way to do what I want to do - which is have a instance locally that I can use as my dev/test version to test things like upgrades to MySQL and PHP, etc. I am not a fan of doing this on a production server. What if I want to move my instance to a new hosting provider? That would be the same thing, right?

Are you running addons / styles on your live site, and are the relevant files on your local one?

 

[GoHerdIBG]

Are you running addons / styles on your live site, and are the relevant files on your local one?

I am running a few addons in production. I will install the addons on my local instance and see if that helps.

 

[GoHerdIBG]

I copied my entire web site directory and database to my local server and was able to get some things to work - but not everything.

 

[rosal]

Do this all from your web host terminal. Be sure to change 'path' to the correct name for your server.

mysqldump --opt -uroot xenforo > /home/path/tmp/backup.sql

then create a new database on your web host called xenforo_test

mysql xenforo_test < /home/path/tmp/backup.sql

At this point you can create a test forum where the config.php points to the xenforo_test database.

What is the best option to create backup bd and import to a new database test?
Some information i have cpanel, and SSH, xenforo 1.5 the database in use shows "utf8_general_ci".

What is the best this one or yours?
mysqldump -u<user> -p<password> --single-transaction --skip-lock-tables db_name > /path/to/backup/db_name_$(date +%d.%m.%y).sql

And to import to the new db what is the command?

 

[AndyB]

Note: if your database collation is uft8mb4 you will need to use this command:

mysqldump --opt --default-character-set=utf8mb4 -uroot database_name > /home/path/mysql_backups/backup.sql

 

[AndyB]

And to import to the new db what is the command?

Enter the following command to restore the backup.sql to your new database.

mysql new_database < /home/path/mysql_backups/backup.sql