Install and setup MySQL on Debian
MySQL is a lightweight database management system. It is particularly suited to manage simple databases storing a large number of records. This is the preferred database management system for Web applications.
This howto is tested on :
- Debian 6.0 Squeeze
- Debian 7.0 Wheezy
- Ubuntu 10.04 Lucid Lynx
- Ubuntu 10.10 Natty Narwal
Installation
Install the MySQL server software and the password generation tool apg :
DEBIAN_FRONTEND='noninteractive' command apt-get install mysql-server apg
Generate a random password for the MySQL "root" account:
MYSQL_PASSWORD="$(command apg -q -a 0 -n 1 -M NCL)"
Display the generated password, and keep it preciously:
command echo "The password for MySQL root account is: '${MYSQL_PASSWORD}'."
Assign the password to the MySQL root account:
command echo "SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('${MYSQL_PASSWORD}');
SET PASSWORD FOR 'root'@'::1' = PASSWORD('${MYSQL_PASSWORD}');
SET PASSWORD FOR 'root'@'${HOSTNAME}' = PASSWORD('${MYSQL_PASSWORD}')" \
| command mysql --user=root command mysqladmin -u root password "${MYSQL_PASSWORD}"
Store the root access parameters in the ".my.cnf" file of your account (ease administration):
if [ ! -e "${HOME}/.my.cnf" ]; then
echo "[client]
user=root
password=${MYSQL_PASSWORD}" \
> "${HOME}/.my.cnf"
command chmod 400 "${HOME}/.my.cnf"
fi
Enable slow query log and point-in-time restoration (and other enhancements) :
command wget 'https://raw.github.com/biapy/howto.biapy.com/master/mysql/base-optimisations.cnf' \
--quiet --no-check-certificate --output-document='/etc/mysql/conf.d/base-optimisations.cnf'
Restart the server :
/etc/init.d/mysql restart
The MySQL server is ready.
Finalization
This howto recommands:
- Ease MySQL server administration with mysql-tools.
- Setup a regular analyze and optimization / defragmentation of MySQL tables.
- Optimize MySQL on Debian.
- Install PHPMyAdmin on Debian.
Allow network access to MySQL databases
Warning: The following setting can be a security breach. Use it only if your server is on a local network, or if you use a strong firewall.
For your MySQL server to be available on the network, change its listening IP address:
# command sed -i -e 's/^bind-address.*/#\0/' "/etc/mysql/my.cnf"
When MySQL clients are remote, enable the connection compression by default to enhance data fetch time and reduce network load:
echo "# Enable connection compression for clients.
# Reduce network load and enhance response time for remote clients.
[client]
compress" \
> '/etc/mysql/conf.d/client-compression.cnf'
Reload the configuration:
/etc/init.d/mysql restart
Backups
Backup the installation with Backup Manager (see Install and setup Backup Manager on Debian).
Create the "backup" MySQL user with rights to use the "mysqldump" tool:
MYSQL_BACKUPPWD=$(command apg -q -a 0 -n 1 -M NCL) echo "GRANT SELECT, LOCK TABLES, SHOW VIEW ON *.* TO backup@localhost IDENTIFIED BY '$MYSQL_BACKUPPWD';" \ | command mysql --user=root --password
Set up Backup Manager to access to MySQL databases with the user "backup":
command sed -i -e "s|[#]*\(.*BM_MYSQL_ADMINLOGIN=\).*$|\1\"backup\"|" \ -e "s|[#]*\(.*BM_MYSQL_ADMINPASS=\).*$|\1\"${MYSQL_BACKUPPWD}\"|" \ "/etc/backup-manager.conf"
Create a MySQL client configuration file for Backup Manager:
echo "[client]
user=backup
password=${MYSQL_BACKUPPWD}" > '/root/.backup-manager_my.cnf'
command chmod go-rw '/root/.backup-manager_my.cnf'
Enable the MySQL backup with Backup Manager:
command sed -i -e 's/[#]*\(.*BM_ARCHIVE_METHOD=.*".*\)"$/\1 mysql"/' \ "/etc/backup-manager.conf"
Backup Manager create empty backups if the file /root/.my.cnf contains a password. This is how to fix the bug:
command dpkg-divert --divert '/usr/share/backup-manager/backup-methods.sh.orig' \
--rename '/usr/share/backup-manager/backup-methods.sh'
command cp -a '/usr/share/backup-manager/backup-methods.sh.orig' '/usr/share/backup-manager/backup-methods.sh'
command sed -i -e 's/--defaults-extra-file/--defaults-file/g' '/usr/share/backup-manager/backup-methods.sh'
Note: If Backup Manager archives are empty, delete the MySQL configuration file created by the tool:
# command rm '/root/.backup-manager_my.cnf'
References
These books can help you:
- Learning MySQL
- MySQL (4th Edition)
- MySQL Cookbook
- Understanding MySQL Internals
- MySQL High Availability: Tools for Building Robust Data Centers
- High Performance MySQL: Optimization, Backups, Replication, and More
Thanks
- Thanks to MySQL (en) developers.
- Thanks to everybody that posted in Drop all tables in a MySQL database (en).