You are here: Home / Debian GNU/Linux / Servers / MySQL / Install and setup MySQL on Debian

Install and setup MySQL on Debian

by Pierre-Yves Landuré — last modified Nov 11, 2017 09:37

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:

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:

Thanks

Comments (0)