Mysql

Mysql is a famous Open Source SQL database. This page describes how to install it with Debian.

Mysql Install

Install the following packages :

  • mysql-server
  • php5-mysql (if you want to connect mysql with php)

Configure the admin password :

If you also have a web server on the computer, I advice you to install PhpMyAdmin which provides an user-friendly interface for managing mysql.

Check

If you try to connect without password, it should fail :

> mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

With a password :

> mysql -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 36
Server version: 5.1.49-3 (Debian)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.1.49, for debian-linux-gnu (x86_64) using readline 6.1

Connection id:          36
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.1.49-3 (Debian)
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:            /var/run/mysqld/mysqld.sock
Uptime:                 1 min 30 sec

Threads: 1  Questions: 102  Slow queries: 0  Opens: 99  Flush tables: 1  Open tables: 23  Queries per second avg: 1.133
--------------

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
+--------------------+
2 rows in set (0.00 sec)

mysql>

For information, the account debian-sys-maint is generated during the install, you can find the generated password inside /etc/mysql/debian.cnf.

Change admin password

You can change the admin passord with the following command :

>/usr/bin/mysqladmin -u root password 'enter-your-good-new-password-here'

Another way is the reconfigure the package :

>dpkg-reconfigure mysql-server-5.1

Make mysql available remotely

By default, Mysql is only listening on 127.0.0.1. To make it available to other computers, comment the following line inside /etc/mysql/my.cnf :

 bind-address          = 127.0.0.1

Script mysql dump

Here is a small script /home/scripts/export_db.sh to export all databases to a file (which can be archived) :

#/bin/sh
/usr/bin/mysqldump --defaults-file=/etc/mysql/debian.cnf --all-databases --add-drop-database --result-file=/home/scripts/dump_mysql/all_databases.sql

This script use the maintenance account from Debian (debian-sys-maint) and exports all datas into /home/scripts/dump_mysql/all_databases.sql.

To run automatically the script, you just have to add it to cron, for example by creating /etc/cron.d/exportdb :

55 23 * * * root /home/scripts/export_db.sh

:!: It's recommended to protect the access to the exported datas :

chmod og-rx -R /home/scripts/dump_mysql/

Backup

  • /etc/mysql/
  • /var/lib/mysql/
  • /var/log/mysql/

dump mysql :

  • /etc/cron.d/exportdb
  • /home/scripts/export_db.sh
  • /home/scripts/dump_mysql/all_databases.sql

Links

en/linux/mysql.txt · Last modified: 2011/10/04 21:42 by matthieu
Recent changes RSS feed Debian Powered by PHP Valid XHTML 1.0 Valid CSS Driven by DokuWiki