XF 2.0 Backup and Restore of Database Failing

G

GoHerdIBG

New member
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.
 
Sort by date Sort by votes
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.
 
Upvote 0 Downvote
AndyB said:
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.
Click to expand...

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.
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
AndyB said:
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.
Click to expand...
Do not follow these instructions or you might loose data

xenforo.com

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 xenforo.com
 
Upvote 0 Downvote
Kirby said:
Do not follow these instructions or you might loose data

xenforo.com

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 xenforo.com
Click to expand...

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.
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
Matthew S said:
Silly question - can you do a plain old default installation on your server and it works?
Click to expand...

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.
 
Upvote 0 Downvote
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?
 
Upvote 0 Downvote
GoHerdIBG said:
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?
Click to expand...

Are you running addons / styles on your live site, and are the relevant files on your local one?
 
Upvote 0 Downvote
AndyB said:
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.
Click to expand...

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?
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
You must log in or register to reply here.

Similar threads

D
  • Question Question
RM 2.2 Backup and restore resources manager
Replies
0
Views
162
Dangerousdave26
D
Tati Booyakah
  • Question Question
XF 2.2 Backup and reinstall user backups on new forum?
Replies
5
Views
618
Jeremy P
Jeremy P
Q
  • Question Question
XF 2.2 Forum Backup and Restore
Replies
5
Views
4K
Quartzite
Q
A
  • Question Question
XF 2.2 problem with thousands of database queries
Replies
6
Views
473
albertc.s
A
developr
  • Question Question
XF 2.0 Backup and restore permissions (usergroup, nodes)
Replies
1
Views
1K
developr
developr
Back
Top Bottom