This howto need you to be logged as root user. This can be done with the following command :

su -

Installation

First, you need to install the MySQL server and a password generator :

DEBIAN_FRONTEND='noninteractive' apt-get install mysql-server apg

We create the root user password for MySQL :

MYSQL_PWD=`apg -q -a  0 -n 1 -M NCL`

We print the created password :

echo "Your password for the root account of MySQL will be : '$MYSQL_PWD'."

Warning: Write down this password and don't loose it !

We setup the new password :

echo "SET PASSWORD FOR 'root'@'${HOSTNAME}' = PASSWORD('${MYSQL_PWD}')" | mysql --user=root
mysqladmin -u root password "$MYSQL_PWD"

Your MySQL server is now ready to be used.

Note: If you want your MySQL server to be available for other hosts than the server's one, you need to change one setting in the configuration file, and restart MySQL:
WARNING: This setting can open a security breach. If you don't know what you are doing, don't use it !

/bin/sed -i -e 's/^bind-address.*/#\0/' /etc/mysql/my.cnf
/etc/init.d/mysql restart

Database creation

We will now create a database with a user able to manage it. First, setup the name of your database :

MYSQL_DB=MY_DATABASE

We create the database :

echo "CREATE DATABASE IF NOT EXISTS \`$MYSQL_DB\` DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci" \
    | mysql --user=root --password

Note: The asked password is the one for your MySQL root account.

We configure the user allowed to manage our newly created database :

MYSQL_USERNAME="`echo $MYSQL_DB|tr '[A-Z]' '[a-z]'`"
MYSQL_USERPWD=`apg -q -a  0 -n 1 -M NCL`

And we print the account name and password of the user for the newly create database :

echo "The user account allowed to manage the '$MYSQL_DB' database is '$MYSQL_USERNAME' with password '$MYSQL_USERPWD'."

Warning: Write down the printed informations and don't loose them !

Last, but not the least, we create the user on MySQL :

echo "GRANT ALL PRIVILEGES ON \`$MYSQL_DB\`.*
        TO \`$MYSQL_USERNAME\`@localhost
        IDENTIFIED BY '$MYSQL_USERPWD';" | mysql --user=root --password

You can now create tables and use your database with this user. We can go further by creating a MySQL user account that will not be able to create or drop tables, but will be allowed to modify existing table contents.

Note: If this user need to access the database from other hosts than the server's one, you need to apply some more settings. Please supply the other hosts by hostname or IP address in a space separated list (replace the bold example values):

OUTSIDERS="computer1 10.0.20.2 192.168.0.1 my.other.hostname"

And use this command line to set up MySQL properly:

SQL_QUERY=""
for OTHER_HOST in $OUTSIDERS; do
SQL_QUERY="$SQL_QUERY 
GRANT ALL PRIVILEGES ON \`$MYSQL_DB\`.*
        TO \`$MYSQL_USERNAME\`@\`$OTHER_HOST\`
        IDENTIFIED BY '$MYSQL_USERPWD';"
done
echo $SQL_QUERY | mysql --user=root --password

Backups

It is very important to regularly backup your databases, in order to be able to get your datas back if your server crash. I encourage you to set this up with the tool Backup Manager. In order to use it, you can follow my Backup Manager installation guide:

Install and configure Backup Manager on Debian 4.0 Etch

We now configure Backup Manager to backup all our MySQL databases. In order to do this, we create a "backup" MySQL user that will own the necessary rights to use the mysqldump command line:

MYSQL_BACKUPPWD=`apg -q -a  0 -n 1 -M NCL`
echo "GRANT SELECT, LOCK TABLES ON *.*
        TO backup@localhost
        IDENTIFIED BY '$MYSQL_BACKUPPWD';" \
    | mysql --user=root --password

We set up Backup Manager to use this MySQL user:

/bin/sed -i -e "s|[#]*\(.*BM_MYSQL_ADMINLOGIN=\).*$|\1\"backup\"|" \
            -e "s|[#]*\(.*BM_MYSQL_ADMINPASS=\).*$|\1\"$MYSQL_BACKUPPWD\"|" \
         /etc/backup-manager.conf

And we enable the MySQL databases backup:

/bin/sed -i -e 's/[#]*\(.*BM_ARCHIVE_METHOD=.*".*\)"$/\1 mysql"/' \
         /etc/backup-manager.conf

PHPMyAdmin

PHPMyAdmin is a web interface designed to easy the management of MySQL databases. To install it, you need a HTTP server with PHP support. It can be Apache or LigHTTPd. If you want, you can install LigHTTPd with PHP support by following my howto Install Lighttpd and PHP 5 on Debian 4.0 Etch.

Once you have the needed HTTP server, run the following command line :

apt-get install phpmyadmin php5-mcrypt php5-gd

Once installed, you can access it by clicking on this link : http://localhost/phpmyadmin/

We create the database used by PHPMyAdmin to propose some optionnal features:

SCRIPT=/usr/share/doc/phpmyadmin/scripts/create_tables_mysql_4_1_2+.sql.gz
if [ -e "/usr/share/doc/phpmyadmin/examples/create_tables_mysql_4_1_2+.sql.gz" ]; then
  SCRIPT=/usr/share/doc/phpmyadmin/examples/create_tables_mysql_4_1_2+.sql.gz
fi
gunzip --to-stdout ${SCRIPT} \
    | mysql --user=root --password

Once this done, we create the user associated to this database:

MYSQL_PMAPWD=`apg -q -a  0 -n 1 -M NCL`
echo "GRANT USAGE ON mysql.* TO 'pma'@'localhost' IDENTIFIED BY '$MYSQL_PMAPWD';;
GRANT SELECT (
    Host, User, Select_priv, Insert_priv, Update_priv, Delete_priv,
    Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv,
    File_priv, Grant_priv, References_priv, Index_priv, Alter_priv,
    Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv,
    Execute_priv, Repl_slave_priv, Repl_client_priv
    ) ON mysql.user TO 'pma'@'localhost';
GRANT SELECT ON mysql.db TO 'pma'@'localhost';
GRANT SELECT ON mysql.host TO 'pma'@'localhost';
GRANT SELECT (Host, Db, User, Table_name, Table_priv, Column_priv)
    ON mysql.tables_priv TO 'pma'@'localhost';" \
    | mysql --user=root --password

And we setup PHPMyAdmin to use this database:

/bin/sed -i -e "s|[/]*\(.*pmadb.*=\)[^;]*\(;.*\)$|\1 'phpmyadmin'\2|" \
            -e "s|[/]*\(.*controluser.*=\)[^;]*\(;.*\)$|\1 'pma'\2|" \
            -e "s|[/]*\(.*controlpass.*=\)[^;]*\(;.*\)$|\1 '$MYSQL_PMAPWD'\2|" \
            -e "s|[/]*\(.*pma_.*;.*\)$|\1|" \
        /etc/phpmyadmin/config.inc.php

Note: Sometimes, it is necessary to "force" the PHPMyAdmin URL, this can be done by adding the following configuration line at the end of the file /etc/phpmyadmin/config.inc.php. Replace the bold URL by the one that fit your configuration:

$cfg['PmaAbsoluteUri'] = 'http://www.my-domain.com/some-path/phpmyadmin/';

Going deeper

Implementing a search engine

If you want to use a search engine in your application, you can use the free open-source SQL full-text search engine Sphinx.

Source : Thanks to Guillaume Pousséo.

Solve errors #1025 for InnoDB tables

If you encounter a error #1025 when altering a table using the InnoDB engine, you can get more informations about the problem with this SQL command:

SHOW ENGINE INNODB STATUS

Source: Thanks to snoyes on irc.freenode.net#mysql