How to set the memory limit for 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.

key_buffer_size + (read_buffer_size + sort_buffer_size) * max_connections = K bytes of memory

I hope this help someone.

phpBB3 – Can’t find FULLTEXT index matching the column list [1191] MySQL 5.6

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.

So, I got this error:

Can't find FULLTEXT index matching the column list [1191]

I found the query that was throwing the error:

mysql> SELECT SQL_CALC_FOUND_ROWS p.post_id FROM phpbb3_posts p WHERE MATCH (p.post_subject, p.post_text) AGAINST ('+search ' IN BOOLEAN MODE) ORDER BY p.post_time DESC LIMIT 250;

I ran it and got this error:

ERROR 1191 (HY000): Can't find FULLTEXT index matching the column list

To solve the probem is to add the index back in. Note is requires an index across two columns. Run this MySQL command on your database.

create fulltext index post_subject_text on phpbb3_posts (post_subject, post_text);

Hope this helps someone.

How to install MySQL 5.6 on Ubuntu 14.04

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:

sudo apt-cache policy mysql-server

mysql-server:
  Installed: 5.5.38-0ubuntu0.14.04.1
  Candidate: 5.5.38-0ubuntu0.14.04.1
  Version table:
 *** 5.5.38-0ubuntu0.14.04.1 0
        500 http://archive.ubuntu.com/ubuntu/ trusty-updates/main amd64 Packages
        500 http://security.ubuntu.com/ubuntu/ trusty-security/main amd64 Packages
        100 /var/lib/dpkg/status
     5.5.35+dfsg-1ubuntu1 0
        500 http://archive.ubuntu.com/ubuntu/ trusty/main amd64 Packages

sudo apt-cache policy mysql-server-5.6

mysql-server-5.6:
  Installed: (none)
  Candidate: 5.6.17-0ubuntu0.14.04.1
  Version table:
     5.6.17-0ubuntu0.14.04.1 0
        500 http://archive.ubuntu.com/ubuntu/ trusty-updates/universe amd64 Packages
        500 http://security.ubuntu.com/ubuntu/ trusty-security/universe amd64 Packages
     5.6.16-1~exp1 0
        500 http://archive.ubuntu.com/ubuntu/ trusty/universe amd64 Packages

How to import maxmind ip locations in mysql

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.

There are:

http://www.dbasquare.com/2012/06/01/implementing-efficient-geo-ip-location-system-in-mysql/

http://nickbartlett.com/wordpress/using-maxmind-geo-city-lite-database-on-your-website/

How to remove / purge / prune old mysql binary logs (safely)

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.

....
-rw-rw----  1 mysql mysql   104871967 Aug 24 00:01 BINLOG.015687
-rw-rw----  1 mysql mysql   104885618 Aug 24 00:04 BINLOG.015688
-rw-rw----  1 mysql mysql   104866713 Aug 24 00:06 BINLOG.015689
...

3 – You can either prune by date or by file. For simplicity, I pruned by file. You need to do this from the MySQL command line.
I connected to mysql and ran the following command:

PURGE BINARY LOGS TO 'BINLOG.015689';

But you could prune by date:

PURGE BINARY LOGS BEFORE '2013-08-24 00:06:00';

You can read the offical mysql documentation here.

How to rename a mysql database

There isn’t a simple command to rename a mysql database. You just need to create a new database and then rename all the tables like so:

RENAME TABLE old_db.table_name TO new_db.table_name

There is also an alternative method by dumping the old database and importing it again like so:

mysqldump -v oldDatabase > oldDump.sql
mysqladmin create newDatabase
mysql newDatabase < oldDump.sql

I have found the second option to be alot faster both in time and on the disk.

MySQL Problems – a complete reinstall

I rebooted an Ubuntu 12.04 server after upgrading the kernel and MySQL.  However, I found that the new MySQL would 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:

dpkg –get-selections | grep mysql

and found I had:

libdbd-mysql-perl
libmysqlclient18
mysql-client-5.5
mysql-client-core-5.5
mysql-common
mysql-server
mysql-server-5.5
mysql-server-core-5.5
mysqltuner
php5-mysql

I removed all them!

apt-get –purge remove mysql-server
apt-get –purge remove mysql-client
apt-get –purge remove mysql-common
…… etc …. etc…

Then cleared the apt-get cache and removed the mysql config and data directories (I have a backup of the data from the night before.)  Note – these commands will remove all your old MySQL data, so don’t run them if you don’t have a backup or you don’t care about the data.

apt-get autoremove
apt-get autoclean
rm /etc/mysql/ -R
rm /var/lib/mysql/ -R

Then reinstalling mysql-server and the other packages.

apt-get install mysql-server
etc….

What a massive waste of time!  Oh well – I hope this post helps someone else out of this pickle.