Problems with Transfer of Wordpress database to New Jail

[NasKar]

I had a wordpress site working in my old setup. For several reasons I had to change to a different subnet on my freenas system. I've been able to get everything working except transfering my old wordpress database. The old jail with the wordpress server info still exists in my pool in jails. My new wordpress site in jails_2 has an empty database. My initial thought was to export the original DB with phpMyAdmin and then reimport the sql into the new server. As this is uncharted territory for my I have a few questions and any suggestions would be great.
1) Do I have to have phpMyAdmin in the same jail as the databases I want to work on or can I add a storage mount to the jail in I want to manipulate the DB on?
2) Where is the DB located? I can't seem to get phpMyAdmin to recognize it. I see the php files in the /usr/local/www/wordpress directory.
3) Is my best option to restore my PFSense router config to the old subnet, reboot FreeNAS with an old config, export the DB data and then re import it into my new wordpress after restoring the new subnet in PFSense and FreeNAS?

 

[Jailer]

Just dump the database, copy the dump over to the new jail and then restore the dump to the new jail, you don't need phpmyadmin.

1. Log into your original jails database mysql -u root -p and enter the database password when prompted.
2. Run the following command to dump the database mysqldump --databases name of database > /databasedump.sql
3. Copy database dump to new jail by running the following command from the system shell (not jail shell) rsync -av --delete /mnt/your pool name/jails/your old jail name/databasedump.sql /mnt/your pool name/jails/your new jail name. The path will be the absolute path of each mounted jail.
4. Log into your new jail and create a database with the same name as the old one.
5. Restore your dump by running source /databasedump.sql
6. Exit mysql quit;
7. Restart the mysql service service mysqlserver restart

https://dev.mysql.com/doc/refman/5.6/en/mysqldump-sql-format.html

 

[NasKar]

Jailer thanks for your help again. Can I login to my original jail database if it's not in my current config in freenas? I have access to the data in the pool but the jail doesn't run in my current config.

 

[Robert Trevellyan]

I suggest option 3, but use the Duplicator plugin.

 

[Jailer]

Jailer thanks for your help again. Can I login to my original jail database if it's not in my current config in freenas? I have access to the data in the pool but the jail doesn't run in my current config.

The old database has to be running for this to work.

 

[NasKar]

Getting very close. I was able to export the databasedump.sql file and restart pfsense in the new subnet and FreeNAS as well. I imported the sql file and got alot of

Code:

Query OK, 0 rows affected (0.00 sec)

I restarted the mysqlserver with service mysql-server restart but entering the http://jailIP/index.php and just get a blank white page. Not sure what's wrong. Tried to DROP DATABASE wordpress; and then CREATE DATABASE wordpress; USE wordpress; source databasedump.sql and quit; and restart the mysql-server but same result.

 

[Jailer]

There will be a lot of rows that will be unaffected and show that while it's processing. You should also see some rows processing
the changes. Did your database dump successfully? Are you running this locally or via a domain?

Tried to DROP DATABASE wordpress; and then CREATE DATABASE wordpress; USE wordpress

You shouldn't have to do any of that. As long as your femp/famp stack is installed and configured and your new database created with the same name as your old database. Your dump name should be the name of your database eg: wordpress.sql or whatever you named it. The examples I gave were just that, examples.

Did you copy all the files from your web root of your old jail to the web root of the new jail? You need to do that as well.

 

[NasKar]

I believe I created the dump sucessfully -rw-r--r-- 1 www www 1759488 Aug 23 20:58 wordpress.sql
Didn't realize it had to be the same name as the database I'm importing into so I renamed it as above.
Running it locally
I can get wordpress to work in the jail if I drop the database and recreate a new one with the same name and grant privileges to it and flush privileges. It starts as a new site. After logging off and did

Code:

mysql -u root -p
USE wordpress;
source wordpress.sql

quit and restart the mysql-server but sill get the white screen. Do I have to edit the wordpress.sql file as it's not on the same server anymore?
Had trouble getting the .sql in the root of the jail so have it in the /usr/loca/www/wordpress directory and that is where i'm executing the commands.

 

[NasKar]

Success. Got it working and the blog post are there. Getting an wordpress error about the theme which I fixed by changing to another theme Turns out the .sql file had to have a search and replace from the old jailIP to the new jailIP and old gateway to the new gateway. Thanks for your help. The more problems I encounter the more I learn <G>

Is it possible to create a script to do the mysqldump as a backup and run as a task?

 

[Jailer]

Turns out the .sql file had to have a search and replace from the old jailIP to the new jailIP and old gateway to the new gateway.

That's why I asked if you were running it locally.

Is it possible to create a script to do the mysqldump as a backup and run as a task?

It is and I do it. I'll check back later after work, don't have enough time to post right now.

Edit: Ok I think I can get this out before I have to leave.

You need to set up your user in mysql_config_editor with your database username and password. Log in to mysql in your jail and run the following command. mysql_config_editor set --login-path=local --host=localhost --user=your_database_username --password

You'll be prompted for your password, enter your database password.

Now create a new file called backup.sh in the root of your jail with the following contents.

Code:

/usr/local/bin/mysqldump --login-path=local --databases your_database_name > /your_database_name.sql

Now go back to the gui and create a cron task to be run with the user root:

Code:

jexec your_jail_name csh /backup.sh

Set it to run as often as you like and you should be all set.

https://dev.mysql.com/doc/refman/5.6/en/mysql-config-editor.html

 

[NasKar]

Had a problem with the plugins not working so I decided to redo the wordpress jail. Tried to export the database wordpress with mysqldump but it didn't work.
After
mysql -u root -p

in my jail and entering the password
mysqldump wordpress > /wordpress2.sql

Nothing happens it doesn't create wordpress2.sql in the root directory

 

[Jailer]

It should be:
mysqldump --databases wordpress > /wordpress2.sql

 

[NasKar]

It should be:
mysqldump --databases wordpress > /wordpress2.sql

that doesn't work either.

 

[Jailer]

Is wordpress the name of your database?

 

[NasKar]

yes

SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| wordpress |
+--------------------+
4 rows in set (0.00 sec)

 

[Jailer]

Ok lets try this again from the start. SSH in to your jail. For a manual dump your going to need to log in and dump in one command.

mysqldump -u root -p --databases wordpress > /wordpress2.sql

Enter your database password when prompted and it should dump into your root directory of the jail.

 

[NasKar]

That worked what was wrong with using the mysql -u root -p and mysqldump --databases wordpress > /wordpress2.sql

 

[Jailer]

You need to use the mysqldump utility as root not log into the database itself. Read the documentation I linked and it will explain it in more detail.

 

[NasKar]

Any idea what

Code:

2017/09/07 22:31:01 [error] 80210#102684: *222 FastCGI sent in stderr: "PHP message: PHP Fatal error:  Call to undefined function mb_internal_encoding() in /usr/local/www/wordpress/wp-content/plugins/dk-pdf/includes/mpdf60/mpdf.php on line 1519" while reading response $response header from upstream, client: 192.168.5.30, server: 192.168.5.56, request: "GET /2017/09/07/half-dome-sentinel-bridge/?pdf=172 HTTP/1.1", upstream: "fastcgi://unix:/var/run/php-fpm.sock:", host: "192.168.5.56", referrer: "http://192.168.5.56/2017/09/07/half-dome-sentinel-bridge/"

means in the nginx error log?
I get that error trying to create a PDF from the post with a plugin dk-pdf

Thought it would fix with the fresh install but it didn't.

 

[Jailer]

No clue at all.