XF 2.1 Database Management

[FDudeH]

I have a backup of a clean database saved in a .sql file. This was created manually in mysql. If I want to restore this database and wipe out the existing one, what is the process for doing that? Do I simply go into the existing database in mysql and drop all the tables and then do a restore in mysql? Or am I supposed to use the Import function, and if so how do you import a .sql file?

Also, how difficult would it be to change the name of a database that Xenforo is using?

Is there any tutorial anywhere on managing databases?

 

[Paul B]

Do I simply go into the existing database in mysql and drop all the tables and then do a restore in mysql?

Yes, that's how to do it.

Also, how difficult would it be to change the name of a database that Xenforo is using?

Just update the src/config.php file.

Is there any tutorial anywhere on managing databases?

Not specifically on this site as it is a mysql/server level operation.

 

[FDudeH]

Ok thanks, I successfully did that, but there were and awful lot of table drops I had to do. Can I just drop the entire database instead and then recreate the whole database?

 

[Paul B]

Yes, that's another option and is likely quicker and easier if not using phpMyAdmin.

 

[FDudeH]

Just a couple notes I want to make in case anyone else tries this. I don't have phpMyAdmin installed, so I'm doing it all command line.

1. SHOW CREATE DATABASE <db_name> will show the original database create statement in mysql. You can then capture this and use it to recreate the database after you drop it. The database will need to already exist prior to extracting your .sql file

2. You may have to connect as ROOT to mysql to create a database, depending on the permissions of what mysql user you are logged in as, so if it doesn't work, log out of mysql and log back in as root. From the shell, you want to do this:
mysql -u<user> -p mydatabasename < db_backup.sql
<enter mysql users password>

3. I found I can actually have two separate databases and simply switch the config.php file back and forth to point to the one I want to use. This is handy for testing.