# Upgrade MySQL 5.1.70 to 5.6.12



## xy16644 (Jul 13, 2013)

The title says it all really...

How do I upgrade my MySQL 5.1.70 port to version 5.6.12? 

I upgraded all my ports today but when it came to MySQL it just upgraded the version I had to the latest 5.1.x version but I would like to upgrade to 5.6.12.

Thanks!


----------



## ShelLuser (Jul 13, 2013)

xy16644 said:
			
		

> How do I upgrade my MySQL 5.1.70 port to version 5.6.12?


There are probably more than one ways to solve this, but my advice would be to install ports-mgmt/portmaster and then use the -o parameter:


```
-o <new port dir in /usr/ports> <installed port>
         replace the installed port with a port from a different origin
```
So that would result in a command like: `# portmaster databases/mysql56-server mysql-server-5.1.70`.

Before doing all of this I'd seriously recommend making a backup of your databases first. Something in the likes of `# mysqldump --opt -A -u root p > mysqldump.sql` (or a similar command, I'm just writing from mind what I'd use here).

I have no doubt that it even if it doesn't work out no data will get lost, but better to be safe than sorry.

Hope this helps.


----------



## xy16644 (Jul 14, 2013)

Thanks for your help! I used portmaster with the -o option as you described and upgraded both the client and server to 5.6. At the end it said I needed to run the `mysql_upgrade` command but I cannot get the MySQL service to start.

When I run:

```
sudo /usr/local/etc/rc.d/mysql-server start
```

I get the following error in /var/log/messages:

```
/usr/local/etc/rc.d/mysql-server: WARNING: failed precmd routine for mysql
```

So I have a bit of a catch 22 here :e I need to run `mysql_upgrade` but in order to do that the MySQL service needs to be running first. Any ideas? I had a look around the Internet for any solutions but have come up empty handed so far.

What do I need to do to get MySQL running again so that I can run the `mysql_upgrade` step to complete the upgrade?

Thanks!

PS: I did find this:

ports/176224: MySQL Server 5.6 requires Perl, but does not depend on it but I had Perl installed beforehand.

PPS: I also tried `portmaster -r mysql-client` and `portmaster -r mysql-server` but this didn't help.


----------



## ShelLuser (Jul 14, 2013)

xy16644 said:
			
		

> When I run:
> 
> ```
> sudo /usr/local/etc/rc.d/mysql-server start
> ...


Is that really all there is to it or could there be more on the next line?

The reason I'm asking is because I'm slightly familiar with this type of error; it usually works in the form of: "failed ... routine for mysql. <Cause of error>".

Two things come to mind. As you mentioned yourself MySQL 5.6 depends on Perl these days. Have you updated Perl last month and when doing so, did you pay attention to /usr/ports/UPDATING? I've noticed that several people overlooked the requirement to rebuild all packages which depend on Perl, and that can result in weird behaviour.

So if you haven't done that already you should consider rebuilding your Perl port structure. For example by using portmaster: `# portmaster -r perl`.

Second; you might want to check the permission settings for /var/db/mysql. It doesn't sound logical because there's no reason why those would change, but it can't hurt either.


----------



## xy16644 (Jul 14, 2013)

ShelLuser said:
			
		

> Is that really all there is to it or could there be more on the next line?
> 
> The reason I'm asking is because I'm slightly familiar with this type of error; it usually works in the form of: "failed ... routine for mysql. <Cause of error>".
> 
> ...



I can't see anymore of this error I'm afraid in the logfile. What I have quoted is all that is there 

I did upgrade my version of Perl yesterday from 5.16.2 to 5.16.3 successfully. And yes, I did read /usr/ports/UPDATING and I did rebuild all my ports that depended on Perl (using `# portmaster -r perl`). This took ages but it has been done.

Permissions on /var/db/mysql are as follows:

```
drwx------  2 mysql  wheel        71 Jul 26  2009 mysql

OR:

-rw-rw----  1 mysql  wheel       117 Jul 26  2009 mysql-bin.000001
```

Any ideas?

PS: I've just had a thought! Now that I have upgraded MySQL, would I need to run `# portmaster -r perl` again for the new version of MySQL that is now installed?


----------



## ShelLuser (Jul 14, 2013)

xy16644 said:
			
		

> I did upgrade my version of Perl yesterday from 5.16.2 to 5.16.3 successfully. And yes, I did read /usr/ports/UPDATING and I did rebuild all my ports that depended on Perl (using `# portmaster -r perl`). This took ages but it has been done.


Very good. Then there's no need to try and rebuild MySQL.

You see; the reason you had to rebuild everything depending on Perl is to make sure that software doesn't still refer to the previous version while you already have a new version installed. But if you installed MySQL after you upgraded Perl then it would already refer to the new version.

(And if you installed MySQL before the Perl upgrade it would have been compiled as well since it depends on Perl.)



			
				xy16644 said:
			
		

> Permissions on /var/db/mysql
> 
> ----<SNIP
> 
> Any ideas?


Those look ok to me. Just to make sure check the directories inside the main mysql directory and make sure the directories and files in there are also owned by the mysql user.

In fact, to save time and effort: `# chown -R mysql /var/db/mysql` should fix any problems in there.

Right, weird error. A question I should have asked from the start, just to rule out other possible causes for problems: which FreeBSD version are you using?

After you stopped MySQL (using either `# service mysql-server stop` or `# /usr/local/etc/rc.d/mysql-server stop`) are you sure there aren't any stale processes left?

What does `ps ax | grep sql` tell you? Most likely nothing, but if it does return any results then that could be a problem (unless it returned this same command, that's obviously not important).


----------



## Savagedlight (Jul 14, 2013)

You should always read the MySQL documentation for upgrade routines between versions.

Upgrading from MySQL 5.1 to 5.5
Upgrading from MySQL 5.5 to 5.6

You might have to reinstall MySQL 5.1 and follow the upgrade procedure. Read the documentation and find out.


----------



## xy16644 (Jul 14, 2013)

I already ran `# chown -R mysql /var/db/mysql` shortly after my most recent post so the permissions are now:

```
-rw-rw----  1 mysql  mysql     29408 Jul 14 07:39 mysql-bin.000112
```

I am currently running FreeBSD 9.1 RELEASE i386 patch 2. I tried stopping MySQL but both said MySQL wasn't running. Running `ps ax | grep sql` gives:

```
82111  1  S+        0:00.00 grep sql
```


----------



## xy16644 (Jul 14, 2013)

Savagedlight said:
			
		

> You should always read the MySQL documentation for upgrade routines between minor (and major) versions.
> 
> Upgrading from MySQL 5.1 to 5.5
> Upgrading from MySQL 5.5 to 5.6
> ...



Fair enough. I thought upgrading it using the ports method would have done the trick though? Now that I have 5.6 installed I'm not sure what I can do? Downgrade to 5.1? Is that possible?


----------



## xy16644 (Jul 14, 2013)

Ok now I am really confused!

I downgraded to 5.1 and was successful in doing that as I could start MySQL and see my databases. I have now just finished upgrading to 5.5 "successfully" but I am back to square one in that I cannot start the MySQL service. No errors in the logs that I can see that I have checked. 

Anything else I can try?


----------



## xy16644 (Jul 14, 2013)

The adventure continues... I ended up uninstalling the MySQL server and client. I then renamed: /var/db/mysql to /var/db/mysql.old. I then installed the latest MySQL (5.6.12) (client and server). Both installed fine but I *still* can't start the MySQL service!

I also can't run:

```
mysql_install_db --basedir=/usr/local --user=mysql
```
and get:

```
2013-07-14 16:21:32 9955 [ERROR] /usr/local/libexec/mysqld: unknown option '--skip-locking'
2013-07-14 16:21:32 9955 [ERROR] Aborting

2013-07-14 16:21:32 9955 [Note] Binlog end
2013-07-14 16:21:32 9955 [Note] InnoDB: FTS optimize thread exiting.
2013-07-14 16:21:32 9955 [Note] InnoDB: Starting shutdown...
2013-07-14 16:21:34 9955 [Note] InnoDB: Shutdown completed; log sequence number 1600697
2013-07-14 16:21:34 9955 [Note] /usr/local/libexec/mysqld: Shutdown complete
```

I've tried researching the error messages but have had no luck so far. I just don't understand why a fresh/clean install doesn't work either? Is there anything else I can do to troubleshoot? I basically want to start with a fresh install of MySQL 5.6.12 and then I'll take it from there!


----------



## ShelLuser (Jul 14, 2013)

The only thing I can come up with is your my.cnf configuration file. Did you create or use one, and if so; what does it contain?

Even better; if you have such a configuration set up have you tried to start MySQL without it?


----------



## xy16644 (Jul 14, 2013)

I tried deleting the my.cnf but this didn't make any difference. I did have a my.cnf with 5.1 but the only option in it was to disable networking. I did try starting MySQL without it but it still didn't start the service. I've had zero luck today trying to upgrade to 5.5 or 5.6 and I have rolled back to 5.1 and all my databases are working.

I've run out of ideas but am open to options.


----------



## ShelLuser (Jul 15, 2013)

Well, this stuff is intriguing enough for me to see if I can reproduce this issue (tomorrow). It's just the kind of stuff why I'm keeping a backup server around (apart from providing plain storage), and since I'm going to be playing "code monkey" for most of the day anyway its a perfect side-task.

While it may not be directly comparable with your setup I think it should provide some matches. I'll keep you informed.


----------



## ShelLuser (Jul 17, 2013)

In the meantime I tried to look into MySQL 5.6, even though this wasn't an upgrade but a fresh install, but unfortunately there wasn't anything which was noticeable different.

Well, apart from auto.cnf in the main MySQL data directory, but that's merely different behaviour in comparison to the previous MySQL versions.

However, re-reading this thread again did make me notice something else; the --skip-locking option is definitely something you'd expect to be specified during boot. Normally it's something you'd set up in your my.cnf file, but the style in which its used in your codeblock would suggest that this is somehow used as a commandline parameter.

So you might want to focus your attention to the rc.d script as well, just in case. Even though I can't imagine what could be wrong with it.


----------



## ikbendeman (Oct 11, 2013)

*As per oracle instructions...*

After upgrading make sure mysqld is stopped. 
Run 
	
	



```
service mysql --skip-grant-tables --user=mysql
mysql_upgrade
```
then stop and start the service normally.


----------



## madsinzurich (Oct 23, 2013)

Gentlemen,

Did you ever succeed in doing this upgrade? I am asking because I ran into a similar issue when upgrading from MySQL 5.1 to 5.5. I have been reading this thread with great interest, as my errors were similar. 

I had to go back to 5.1 for now, but wanted to know if you actually were successful. I will have a window to give it another shot next week and then provide some hard data here.

Should I open a new thread for this? 

Thanks for any input on this.

/Mads


----------



## Savagedlight (Oct 24, 2013)

Backup your data directory, do a mysqldump of all the databases, uninstall MySQL, clear (rename) the database directory, install MySQL 5.5 (upgrade to 5.6 later if desired), restore your mysqldumps. Additionally, please see the earlier linked upgrade documentation.


----------

