Outils personnels
Vous êtes ici : Accueil GNU / Linux Debian Installer et configurer MySQL sur Debian
Actions sur le document
  • Send this page to somebody
  • Print this page
  • Add Bookmarklet

MySQL for Debian 4.0 Etch

Par Pierre-Yves Landuré - Dernière modification 16/12/2010 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 -

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

Attachements

Thanks!

Posté par Anonymous Coward le 10/04/2008 19:05
This is a great tutorial!

Best,
Anonymous Coward

Still having errors..

Posté par C.G.B.S. le 30/07/2008 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

Posté par Pierre-Yves Landuré le 30/07/2008 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

Posté par C.G.B.S. le 31/07/2008 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 ?

Posté par Pierre-Yves Landuré le 31/07/2008 18:37
try

/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

Posté par Pierre-Yves Landuré le 31/07/2008 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

Posté par Pierre-Yves Landuré le 31/07/2008 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.

Wounderful!

Posté par DanielS le 29/08/2008 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

Posté par Sailani Montila le 02/09/2008 18:10
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

error socket

Posté par woelfi le 03/09/2008 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 ?

Posté par lwolf le 04/09/2008 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

BlogBang
Navigation
 

Réalisé avec Plone

Ce site respecte les normes suivantes :

Wikio