Mysql [Bouthors.fr]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
{{tag>en en:linux en:server en:database}}
====== 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 :
  * either when installing the package
  * or after the install, follow [[#Change 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 :
<code>
> 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>
</code>

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

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

<file>
#/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
</file>

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/
dump mysql :
  * /etc/cron.d/exportdb
  * /home/scripts/export_db.sh
  * /home/scripts/dump_mysql/all_databases.sql
===== Links =====
  * [[phpmyadmin]]
  * http://www.mysql.com/