mysql56 and phpbb3/nextcloud

fullauto2012

fullauto2012

Code: 
root@kif:/var/db/mysql # uname -a
FreeBSD kif 10.3-RELEASE FreeBSD 10.3-RELEASE #0 r297264: Fri Mar 25 02:10:02 UTC 2016     root@releng1.nyi.freebsd.org:/usr/obj/usr/src/sys/GENERIC  amd64

Had databases/mysql80-server installed with www/nextcloud running. Wanted to add a www/phpbb3 instance on the same machine. After setting up the new security/py-certbot, a split-horizon DNS so my internal network didn't panic when requesting the SSL certs, and updating the /usr/local/etc/apach24/extra/httpd-vhosts.conf file to reflect both the pages on the same HTTPD, I installed the PHPBB3 instance.

Turns out phpBB3 3.1 (the version I chose) didn't play well with databases/mysql80-server. It was running, and I had the BB built and such, but I was advised to downgrade to databases/mysql56-server, which I did.

What I was left with, after the downgrade was an error from the index.php saying that the password for 'root'@'localhost' wasn't accepted. I copied the temporary pass from /root/.mysql_secret and changed the password to what it was. After that, the error phpBB3 was spitting out was the "database phpbb3" was not found. I did some snooping and and in /var/db/mysql I have a file called mysql.old containing all my original DBs.

I made a backup of /var/db/mysql/mysql.old into my /root to ensure I didn't break anything further that I couldn't come back to. I then tried:
mv ./mysql ./mysql.ORG; cp -r ./mysql.old ./mysql
which did nothing I needed.
I restored the dirs to their original, and created a database in mysql called 'phpbb3', and tried replacing the new phpbb3 directory with the one in mysql.old, which gives me the error :"Table 'phpbb3.phpbb_config' doesn't exist [1146]"

All I need to know is how to import the mysql.old information into my new mysql instance.

All I see is information of mysqldump, which I cannot use as my databases (phpbb3 and nextcloud) are not recognized by databases/mysql56-server.

Thank you ahead of time.
 
fullauto2012 said:
What I was left with, after the downgrade was an error from the index.php saying that the password for 'root'@'localhost' wasn't accepted.
Click to expand...
Never, ever, run a web application on the MySQL root account! If there's a bug in the web application that allows for SQL injection you just gave them the keys to the kingdom. ALWAYS create a specific account for the application and apply just enough permissions for it to work. I'm pretty sure this is documented in the phpBB documentation.
 
It's hard to tell what's actually contained in the mysql.old directory. I do know you can't just move those files around, especially not with InnoDB databases. MyISAM was fairly easy to move but this is not the case for InnoDB.

But I would copy the files to another machine to play around with. Install MySQL and before starting anything copy the files from mysql.old to /var/db/mysql. Then try to start MySQL. Hopefully things have remained intact enough for it to start. Once MySQL is started use mysqldump(1) to dump the databases. You can import those on the new MySQL server.
 
Allready did that. the server wouldnt start. The mysql.old dir contains the contents of the origial mysql dir before I installed mysql56. I figured this was a common problem and a solution would be easy to find. Not so. I've been reading forums and stack exchange for several days.
 
I have been told that it is possible to 'mount' the /var/db/mysql/mysql.old for use in the new instance of mysql. I was told to edit /usr/local/my.cnf and set the "datadirectory" to /var/db/mysql/mysql.old and restart the server. Once that was accomplished, use mysqldump to dump a backup, then change the datadirectory back, restart the server once more, and use mysqldump again to import the database.

I tried and editing my /usr/local/my.cnf had no effect on anything. I ensured this was true by creating a database called 'dummy' in mysql and confirming that dir had been created in the original /usr/local/my.cnf dir.

What am I missing?

Code: 
root@kif:/var/db # pkg list | grep mysql56-server
/usr/local/libdata/ldconfig/mysql56-server
/usr/local/share/licenses/mysql56-server-5.6.37_1/GPLv2
/usr/local/share/licenses/mysql56-server-5.6.37_1/LICENSE
/usr/local/share/licenses/mysql56-server-5.6.37_1/catalog.mk

Code: 
root@kif:/var/db # ls -la | grep mysql
drwxr-xr-x   5 mysql      mysql          512 Nov 24 22:13 mysql
drwxr-x---   8 mysql      mysql         1024 Nov 24 19:08 mysql.OLD
drwxr-x---   2 mysql      mysql          512 Nov 15 21:06 mysql_secure
drwxr-x---   2 mysql      mysql          512 Nov 24 18:55 mysql_tmpdir

Code: 
root@kif:/usr/local # cat ./my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
#basedir = .....
datadir = /var/db/mysql.OLD
# port = .....
# server_id = .....
# socket = .....

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

Code: 
root@kif:/var/db # service mysql-server start
Starting mysql.
root@kif:/var/db # mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.37 Source distribution

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@localhost [(none)]> show databases
    -> ;
+------------------------+
| Database                    |
+------------------------+
| information_schema   |
| mysql                         |
| performance_schema |
| test                            |
+------------------------+
4 rows in set (0.00 sec)
 
Last edited by a moderator:
The datadir is overruled on the command line when the service starts. Use this instead:
Code: 
mysql_enable="YES"
mysql_dbdir="/var/db/mysql/mysql.old"
 
I appreciate the help. But, now i'm plagued with different errors... Errors, as it turns out, far to complicated to fix than it is worth to fix considering the project and what my plans in the future are...
I'm just going to chalk this up as experience, and reinstall the system as a whole... I've been wanting to upgrade this box for a while now with 6 4TB drives and ZFS, so now seems the opportune time to do so. And, I get to solidify my skills...
 
You must log in or register to reply here.
Back
Top