Mysql settings

Use InnoDB table type for transactional behavior

Default installed with 4.x
use set-variable=default-table-type=InnoDB in my.ini to create InnoDb format by default
use show engines; to find out what engines are available and which one is default

Handle large attachements

use set-variable=max_allowed_packet=xM in my.ini to make sure that any attachment of up to x MB can be added/retrieved. As of 0.6.3 the maximum size of the blob column that stores an attachement is 1GB.

Creating tablespace per tables

This allows easier binary backup.

Before you can copy the database folder, you must make sure that the database is idle using show table status

use innodb_file_per_table in my.ini to turn one tablespace file per table.

Internationalization

Use default-character-set=utf8 in my.ini to turn on UTF8 storage mode.

Create users

From http://dev.mysql.com/doc/mysql/en/Adding_users.html

Needs both the following lines to have a user for both local and remote login:

GRANT ALL PRIVILEGES ON *.* TO 'xplanner'@'localhost' IDENTIFIED BY 'xp' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'xplanner'@'%' IDENTIFIED BY 'xp' WITH GRANT OPTION;

Reset root password

1. Shutdown mysql
2. Restart mysql without authentication: use --skip-grant-tables option
3. Reset password
   • Either through mysqladmin:
     mysqladmin -u root password mynewpassword
   • Or through mysql:
     1. Run mysql -u root mysql
     2. UPDATE user SET Password=PASSWORD('mynewpassword') WHERE User='root';
     3. FLUSH PRIVILEDGES;
4. Shutdown mysql
5. Restart mysql normally