Email this page
Send a link

MySQL

Table of contents



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


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


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.


toggle Advanced Search Help
  • + : A leading plus sign indicates that this word must be present in every object returned.
  • - : A leading minus sign indicates that this word must not be present in any row returned.
  • By default (when neither plus nor minus is specified) the word is optional, but the object that contain it will be rated higher.
  • < > : These two operators are used to change a word's contribution to the relevance value that is assigned to a row.
  • ( ) : Parentheses are used to group words into subexpressions.
  • ~ : A leading tilde acts as a negation operator, causing the word's contribution to the object relevance to be negative. It's useful for marking noise words. An object that contains such a word will be rated lower than others, but will not be excluded altogether, as it would be with the - operator.
  • * : An asterisk is the truncation operator. Unlike the other operators, it should be appended to the word, not prepended.
  • " : The phrase, that is enclosed in double quotes ", matches only objects that contain this phrase literally, as it was typed.

Menu

Search

in:

Help