Category: MySQL

There is no variables like memory_limit in MySQL my.cnf file – therefore you can’t explicitly set the memory limit for MySQL. You have to configure your my.cnf file based on a combination of variables. These then multiple together to form the MySQL memory limit.

I just upgraded MySQL from 5.5 to 5.6 and changed all the tables in the PHPBB3 database into InnoDB tables. I did this because InnoDB is faster if you have lots of concurrent read / writes and MySQL 5.6 finally support FULLTEXT searching using InnoDB. However, I made a mistake and did the conversion to InnoDB before upgrading to MySQL 5.6 and lost the fulltext indexes from the tables.

Ubuntu 14.04 comes with both MySQL 5.5 and MySQL 5.6 and by default uses MySQL 5.5. However, you can upgrade to MySQL 5.6 by doing the following steps:

Step 1 – Take a backup!

mysqldump -u root --all-databases > /home/me/add_databases.sql

Step 2 – Remove the old MySQL
I recommend using ‘apt-get purge’ rather than ‘apt-get remove’ to uninstall the old MySQL server. Don’t worry, this doesn’t mean all your data in /var/lib/mysql will be deleted. The purge command just removes all the configuration files in /etc/mysql/ . This is important because some old MySQL configurations aren’t supported in 5.6.

If you have an unsupport configuration option (for example: table_cache was renamed table_open_cache) MySQl doesn’t silently ignore these settings… it simply doesn’t start. Oh and it doesn’t show any warnings. So you really should remove any list in /etc/mysqal/conf.d . You can read the list of settings that might have changed.

So…

apt-get purge mysql-server-5.5 mysql-client-5.5
apt-get autoremove

Step 3 – Install the new MySQL 5.6

sudo apt-get install mysql-server-5.6 mysql-client-5.6

And you are done. Hurray.

p.s. For interest, you can see which repository these packages are in from using the ‘apt-cache policy’ command:

I’m just building a map of some ip addresses and need to geolocate them. I decided to use the MaxMind database. I found these two brilliant resource without which I could not have imported the databases properly.

I generally setup binary logs on a MySQL server where the data isn’t being replicated and I’m using daily mysql-dumps to backup the data. This allows me to recover the database to a specific point in time. However sometimes the binary logs can become very large if there are lots of changes occuring to the data. Then you can suddenly need to delete / purge / prune the mysql binary log files. Here is how I did it:

1 – Examine the binary logs to decide up to where you want to delete to:

ls -la /var/lib/mysql/

2 – Here the traffic to one server had suddenly increased and the server had been logging 101MB every few minutes for several days.

I rebooted an Ubuntu 12.04 server after upgrading the kernel and MySQL. However, I found that the new MySQL would you not start. When I tried:

sudo service start mysql

I got:

start: job failed to start

There were no errors appearing in the /var/log/mysql/error.log and so there was no information to help debug it. I found this post and tried every single one of the solutions. When I removed mysql-server-5.5 using:

apt-get –purge remove mysql-server
rm /etc/mysql/ -R

I found the when I reinstalled mysql-server the installation would fail saying:

Unable to set password for the MySQL “root” user An error occurred while setting the password for the MySQL administrative user. This may have happened because the account already has a password, or because of a communication problem with the MySQL server.

I then got stuck because I could no longer remove MySQL because it was only partially installed. Every time I tried to remove it, it would ask me to put in a new root password which I obviously could not do. The solution is to use dpkg to purge the partially installed package:

dpkg –purge mysql-server-5.5

Then I tried reinstalling again and got:

Can’t find file: ‘./mysql/host.frm’ (errno: 13)

Basically, the solution to these problems is a complete purge of MySQL and removing ALL of it and start again. I found what mysql related packages I have install by running: