Personal tools
You are here: Home GNU / Linux Debian 4.0 Etch MySQL for Debian 4.0 Etch
Document Actions
  • Send this page to somebody
  • Print this page
  • Add Bookmarklet

MySQL for Debian 4.0 Etch

by Pierre-Yves Landuré last modified 2010-12-16 14:06

Even if installing MySQL is a very common thing, this howto will help you to install rapidly a quite secure MySQL server on Debian 4.0 Etch.

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

su -


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 :


We create the database :

| 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 :

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 my.other.hostname"

And use this command line to set up MySQL properly:

echo $SQL_QUERY | mysql --user=root --password


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`
TO backup@localhost
| 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\"|" \

And we enable the MySQL databases backup:

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


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:

if [ -e "/usr/share/doc/phpmyadmin/examples/create_tables_mysql_4_1_2+.sql.gz" ]; then
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';;
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 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|" \

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/ Replace the bold URL by the one that fit your configuration:

$cfg['PmaAbsoluteUri'] = '';

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:


Source: Thanks to snoyes on



Posted by Anonymous Coward at 2008-04-10 19:05
This is a great tutorial!

Anonymous Coward

Still having errors..

Posted by C.G.B.S. at 2008-07-30 11:00
I guess that I completely messed up the whole MySQL thing, is there any way to remove completely everything which has to do whit it and start again from the beginning ?

localhost:~# su -
localhost:~# apt-get --purge mysql-server
E: Invalid operation mysql-server
localhost:~# rm -r /var/lib/mysql*
rm: cannot remove `/var/lib/mysql*': No such file or directory
localhost:~# MYSQL_PWD=`apg -q -a 0 -n 1 -M NCL`
localhost:~# echo "Your password for the root account of MySQL will be : '$MYSQL_PWD'."
Your password for the root account of MySQL will be : 'mewhisIj2'.
localhost:~# echo "SET PASSWORD FOR 'root'@'$HOSTNAME' = PASSWORD('$MYSQL_PWD');" \
> | mysql -u root
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
localhost:~# mysqladmin -u root password "$MYSQL_PWD"
mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)'
Check that mysqld is running and that the socket: '/var/run/mysqld/mysqld.sock' exists!
localhost:~# /var/run/mysqld/mysqld.sock
-su: /var/run/mysqld/mysqld.sock: No such file or directory

I really appreciate your help and sorry for beeing such a n00b.

ok, my wrong for purging your MySQL install

Posted by Pierre-Yves Landuré at 2008-07-30 11:11
Do this to completely remove MySQL from your server:

apt-get --purge remove mysql-server
rm -r /etc/mysql*
rm -r /var/lib/mysql*

Then, restart this howto from the very beginning (aka, mysql install).

Good Luck

Thank you for all your help

Posted by C.G.B.S. at 2008-07-31 18:07
I purged mysql and reinstalled.
I know this have nothing to do now with your guide but I still have the error ERROR 2002, I searched in a few forums the last days but had no luck.
A lot people have this error but I don't saw any solution.

localhost:~# echo "SET PASSWORD FOR 'root'@'$HOSTNAME' = PASSWORD('$MYSQL_PWD');" \
> | mysql -u root
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

localhost:~# mysqld
080731 10:36:24 [Warning] Can't create test file /var/lib/mysql/localhost.lower-test
080731 10:36:24 [Warning] Can't create test file /var/lib/mysql/localhost.lower-test
mysqld: Can't change dir to '/var/lib/mysql/' (Errcode: 2)
080731 10:36:24 [ERROR] Aborting
080731 10:36:24 [Note] mysqld: Shutdown complete

localhost:~# tail -f /var/log/syslog
Jul 31 10:16:53 localhost mysqld[2948]: 080731 10:16:53 [Note] /usr/sbin/mysqld: Shutdown complete
Jul 31 10:16:53 localhost mysqld[2948]:
Jul 31 10:16:53 localhost mysqld_safe[2950]: ended
Jul 31 10:17:01 localhost /USR/SBIN/CRON[3015]: (root) CMD ( cd / && run-parts --report /etc/cron.hourly)
Jul 31 10:17:07 localhost /etc/init.d/mysql[3088]: 0 processes alive and '/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf ping' resulted in
Jul 31 10:17:07 localhost /etc/init.d/mysql[3088]: Could not open required defaults file: /etc/mysql/debian.cnf
Jul 31 10:17:07 localhost /etc/init.d/mysql[3088]: Fatal error in defaults handling. Program aborted
Jul 31 10:17:07 localhost /etc/init.d/mysql[3088]:
Jul 31 10:35:43 localhost -- MARK --
Jul 31 10:39:01 localhost /USR/SBIN/CRON[3291]: (root) CMD ( [ -d /var/lib/php5 ] && find /var/lib/php5/ -type f -cmin +$(/usr/lib/php5/maxlifetime) -print0 | xargs -r -0 rm)

restarting mysql ?

Posted by Pierre-Yves Landuré at 2008-07-31 18:37

/etc/init.d/mysql restart

i'm afraid i can't help you, unless you use the "contact" button, and give me a ssh access to your server.

my bad my bad

Posted by Pierre-Yves Landuré at 2008-07-31 21:12
i've been wrong.

To purge your Mysql install correctly do:

apt-get --purge remove mysql-server*
rm /var/lib/mysql*

Good luck

and a problem in my guide

Posted by Pierre-Yves Landuré at 2008-07-31 21:14
i'm sorry.. i've done a recent change to this guide, and it turn the change cause problems.

I've corrected it , it should be fine now.


Posted by DanielS at 2008-08-29 05:16
This worked flawlessly, and by far the easiest I've seen to date. (Also Up to date! Major Points there!) I Thank you for the time you took to post this and all of your other great posts. My Lighttpd is awesome thanks to you!

i get this error message and i can't even execute mysql in terminal because i always got this message

Posted by Sailani Montila at 2008-09-02 18:10
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

error socket

Posted by woelfi at 2008-09-03 12:23
Set an environment variable MYSQL_UNIX_PORT=/var/run/mysqld/mysqld.sock

Not a nice solution but it would help right away. (Remember this is not restart save unless you take care for it.)

is mysql started ?

Posted by lwolf at 2008-09-04 02:55
This error happen when mysql does not start correctly.

You should check mysql log file in /var/log/mysql , and syslog : /var/log/syslog for errors after a mysql start attempt.

Good luck


Powered by Plone CMS, the Open Source Content Management System

This site conforms to the following standards: