October is pretty busy for me, lot of family birthdays, to include my own, my son (it will be his first birthday), my sister, my mother-in-law and niece. Add in my son's first Halloween, and buying candy for other kids and it gets pretty crazy. I finally got another chance to sit down and work on computer stuff.
So I decided to replace another Linux box. I selected my database server this time. Since so many little things hook into it. It was and older Debian box, as you know I refused to update to Jessie, so everything was still on Wheezy. So it was MySQL 5.5 from the standard Debian packages. It's mainly used for my email/webmail, phone system and my ticket system. Before you ask, I have a ticket system, so I can make myself tickets to remind me what I was doing, I use calendar events on my phone too, but ticket system can help me track long term projects. I even use it for non-computer related projects.
So I started by making a clone of my jail template. You can see Post 10 for more information on jails. Once I made the clone I adjusted a few values. Since this is my database and only other machines need to connect to it, and no users connect directly I moved it to my secondary subnet which has a strict firewall setup. It is important to know that since I'm not using VIMAGE that everything goes via a default route on the main server, but I do have multiple NICs. The separate subnet really just makes it easier for me to do my firewall rules.
I wanted to try to replace MySQL with MariaDB, and what do you know FreeBSD had some packages ready for me to use. I installed MariaDB 10. I found a few articles about upgrading the database schemas when you update locally on the same machine. Of course I wasn't doing that. And before we get too much farther, make sure you always have a backup of your data. What I did, which left me with a complete live backup as well as any archived ones I may have, was to do a dump of the databases. I basically dumped everything except the internal ones:
information_schema mysql performance_schema
MariaDB still has a lot of things that reference mysql by name. Of course by not migrating the mysql table I would not have any of the users setup. That was fine, it was time to re-do them and update some passwords anyways. I could have just taken the users table as well. But I really wanted to keep my new database cleaner.
So now that I had a good MySQL dump or export, It was time to setup MariaDB. I found the easier way was to install the package, copy a sample configuration from /usr/local/share/mysql/ (they have various sizes to choose from) to /usr/local/etc/my.cnf, make some minor changes to it, and then add the following to rc.conf:
mysql_enable="YES"
Then once you have it running, run:
/usr/local/bin/mysql_secure_installation
This will let you set a root password, limit root access to local host, remove anonymous access and test/temporary tables. You can select what ever options you like.
Once that was complete I ran:
mysql -u root -p < mydump.sql
That created all of my previous schemas. I then as I stated above added in the appropriate users, and changed everything to point to the new server.
My phone server, email server and ticket system are working without issue now.