Email this page
Send a link

MySQL



Installing from ports

make WITH_CHARSET=utf8 WITH_COLLATION=utf8_general_ci BUILD_OPTIMIZED=yes


Upgrading with portupgrade

portupgrade -m "WITH_CHARSET=utf8 WITH_COLLATION=utf8_general_ci BUILD_OPTIMIZED=yes" databases/mysql50-client
portupgrade -m "WITH_CHARSET=utf8 WITH_COLLATION=utf8_general_ci BUILD_OPTIMIZED=yes" databases/mysql50-server


mysqldump and restore utf8

Apparently there is a known issue when you want to use mysqldump on databases with utf8 encoding.

The fix seems to be forcing latin1 when dumping and restoring, to avoid mysql(dump) to do a double conversion.
mysqldump -u username -p --default-character-set=latin1 -N database > backup.sql
mysql -u username -p --default-character-set=latin1 database < backup.sql

It seems odd that MySQL did not address this issue long ago.
Also see http://docforge.com/wiki/Mysqldump(external link) for further notes.

Set a root password

The default password for root on a new installation is empty. Use this to set a password.
mysqladmin password


Manually handling passwords

mysql> select password('MySecret');
+-------------------------------------------+
| password('MySecret')                      |
+-------------------------------------------+
| *B151980ABDC2F1E1824EB14EF4A83C4F66452367 |
+-------------------------------------------+
1 row in set (0.09 sec)

mysql> GRANT all ON database.* TO 'username'@'localhost' IDENTIFIED BY PASSWORD '*B151980ABDC2F1E1824EB14EF4A83C4F66452367';
Query OK, 0 rows affected (0.12 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.14 sec)

GRANT all ON database.* TO 'user'@'localhost' IDENTIFIED BY 'secret' <WITH GRANT OPTION>


Host not allowed to connect?


Have you ever seen this error?
Host 'example.com' is not allowed to connect to this MySQL server


I struggled with this for a few hours before finding out I had to grant the user from the IP, and not the hostname.

Somehow MySQL was confused, and the error log was NOT helpful.

Lower minimum length for searching fulltext

[mysqld]
ft_min_word_len=3


PS: Remember to remake all FULLTEXT indexes after this!

Forcing UTF-8 on all connections


Background: When importing my old utf8_general_ci database everything looked fine… but reading it from my website showed characters wrong. Writing new entries into the database (from web) made something unreadable to the human eye for special chars like æøåöëü etc.

my.cnf
[mysqld]
init_connect='SET collation_connection = utf8_general_ci'
init_connect='SET NAMES utf8'
default-character-set=utf8
character-set-server=utf8
collation-server=utf8_general_ci


Default path for my.cnf is /var/db/mysql/my.cnf (to orverride values)

Remember to restart MySQL server after this change to file my.cnf

Thanks to http://www.saiweb.co.uk/mysql/mysql-forcing-utf-8-compliance-for-all-connections(external link) for this pointer!

Quoting Buzz
Every client connection will now default to utf-8 encoding and not latin-1, removing the need to add a SET NAMES call on every connection.

This will work for PHP, C++, ruby etc… as the client encoding is now handeled server side, rather that waiting on the client to issue a SET NAMES command.