Database engines
The key advantage of MySql database server is the ability of using different database engines (DBE), also known database storage engine. DBE does the work of storing and retrieving data from the underlying database tables, developed as modules and pluggable into MySql server.

Among the existing MySql DBEs, InnoDB and MyISAM are most widely used in active MySql databases nowadays. MyISAM however, does not support data integrity, i.e. integrity constrains based on primary key, foreign key and data relationship concepts are not handled. MyISAM also does not support record-level locking. Transactions of business level management systems such as banking, decision support and business intelligence systems are impossible with using MyISAM. This is where InnoDB DBE comes into play. InnDB is both data integrity and transaction safe with support for foreign keys along with commit, rollback and crash recovery capabilities. While InnoDB is novel and appropriate for future business management systems, MyISAM however still fits numerous active databases, particular for the read-intensive ones where SELECT and INSERT requests are used most of the time. A mixture of the two DBEs is just fine. However, MySql with InnoDB eats some extra RAM, usually 300MB at least. Therefore, it is worth to decide whether or not to use only MyISAM. The decision should be made with all the followings considered:

• Do we need to handle data integrity?
• Do you need to handle transactions properly?
• How often do the data records updated?

If at least one answer is YES, or the system’s mouth is full of RAM, then InnoDB is the way to go. Otherwise, for most applications such as blog, forum and web hit counter systems… MyISAM is absolutely fine and effective, even with systems having only 512MB of RAM, these applications can run extremely fast. If we choose to use MyISAM only, we need to turn off InnoDB using MySql configuration file: my.cnf. To check if the InnoDB DBE is turned on, use the following command, mysqladmin -u -p variables | grep have_innodb To disable InnoDB, in the “mysqld” section, add the following settings,

#skip-bdb # use this for MySql version 4.1 and the earlier
skip-innodb
default-storage-engine = MyISAM
#default-storage_engine = InnoDB

Restart MySql server then enjoy MySql, one of the most effective relational database management systems.

Remote access mode

MySql is one of the most popular database servers because of its ease of use and relatively high performance. Client accesses MySql server using MySql authentication protocol which includes the server domain name or IP address, and the authenticated username. To grant a server access, an account should be created on the server with an explicit username, and the client domain name/IP. If the client domain name/IP is omitted, the user can access to the server from any client host. If the client IP is provided as an IP range, i.e (xxx|%)*, then the user's access can be made from any client host of this range. Following are the MySql commands to grant a client access,

CREATE USER 'username'@'hostname/IP' IDENTIFIED BY 'user-password'
GRANT  ON [ ALL|databases[.*|tables] ] TO 'username'[@['hostname'/'IP']

In order to have MySql server accepted remote access, please be aware of some security issues with MySql (video), just add the following settings to my.cnf which is located in '/etc/mysql/' for DEB distros for example.

bind-address = server-host-IP-address
port = 3306  ## or any port of your choice.

On receiving client request, MySql server will look for the domain name of the host where the inbound connection comes from using the host IP, and check with its authentication database. This IP-to-domainname mapping process may be very time-consuming. If we make sure that, in the server database, all the client hosts are identified using IP address then we can add the following settings to "my.cnf" to prevent the server from looking for client host's domain name.

skip-host-cache
skip-name-resolve

Alternatively, we can update the host file content with the domain names or nick names of the client hosts that will be accessing the server.

To set the default to UTF-8, you want to add the following to my.cnf

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8

memory optimization

An awesome feature of MySql is that it can run on systems with very low memory configurations, even if just 16MB, as well as high memory ones. Using the settings in my.cnf file, we can optimize MySql memory usage accordingly so that it performs well on the system it is running.

The best MySql memory configurations are shipped along with the install packages. On having MySql installed, please check the folder: /usr/share/doc/mysql-server-`version-here`/examples/. Followings are the configurations provided,

• my-small.cnf.gz
  systems with extremely small memory (under 64MB!)
• my-medium.cnf.gz
  systems up to 128MB of memory
• my-large.cnf.gz
  systems with 512MB of memory
• my-huge.cnf.gz
  systems with 1-2GB of memory or more, as well as dedicated servers

To apply one of these configurations, please backup the current MySql configuration,

cp /etc/mysql/my.cnf /etc/mysql/my.bak 

Then extract the configuration of choice,

cd /usr/share/doc/mysql-server-`mysql-version`/examples/
gunzip < `the-configuration-name`.cnf.gz | cat > /etc/mysql/my.cnf

Please notice that MySql memory optimization does not help much with the systems having high CPU and disk IO speeds. Please refer this post for more information.

To change mysql data folder

Please follow the steps below:

1. sudo /etc/init.d/mysql stop
2. sudo cp -rp /var/lib/mysql (default folder) to "new place"
   "New place" should better a sub-folder of /home/
3. sudo vi /etc/apparmor.d/usr.sbin.mysqld
   change /var/lib/mysql/  to  "new place"
   sudo /etc/init.d/apparmor restart
4. In /etc/mysql/my.cnf (or included file)
   change: datadir = /var/lib/mysql
          to: datadir = "new place"
5. sudo /etc/init.d/mysql start