MySQL is a management system relational database (RDBMS) Free and open. It is powerful and very popular. It is multi-threaded and multi-users.

Installation
Install Package mysql-server. Installation by hand

Use

Whether MySQL is launched
To determine if server MySQL is launched, type the following command in a terminal:

sudo / etc / init.d /mysql statusIf MySQL is running, you should see a line like:

Status: started
Start
To start the server MySQLType the following command in a terminal:

sudo / etc / init.d /mysql home
Restart
To restart the server MySQLType the following command in a terminal:

sudo / etc / init.d /mysql restart
Stop
To stop the server MySQLType the following command in a terminal:

sudo / etc / init.d /mysql stop
Reloading configuration
For MySQL takes into account changes in its configuration, type the following command in a terminal:

sudo / etc / init.d /mysql reload
Forcing the inclusion of the new configuration
To force MySQL to reload its configuration files, type the following command in a terminal:

sudo / etc / init.d /mysql force-reload
Configuration

Launch console MySQL
•If you have not set a password (not recommended):
mysql -u root•If you set a password:
mysql -u root-pand type your password.

You will then arrive at a prompt type:

mysql>You can then type in your query MySQL. Do not forget the semicolon at the end of the request. If you forget the password prompt to the line.

mysql> Select * from
    ->
This passage tells you that the request is completed. You can enter your query in this way on several lines and finally close with the " ; .


Select the database to use
Either directly when you start the client mysql command line:

mysql -u root-p votre_baseOr when connected to mysql by typing:

mysql> Use votre_base;
Add or change the password of root
To change the password for the root user (not the same as the root system), type the following command in a terminal:

sudo mysqladmin-u root-p password new_password
Redefining power on password for root
If you have lost your root password for MySQLFollow these steps:

It stops the server MySQL :

sudo / etc / init.d /mysql stopIt connects to the server regardless of user privileges:

sudo / usr / bin /mysqld_safe - skip-grant-tables &
mysql -h localhostWe'll use the table containing the parameters MySQL :

use mysqlIt sets up the new password:

update user set password = password ( '<votre_mot_de_passe>') where user = 'root' and host = 'localhost';(replace <votre_mot_de_passe> the password of your choice)

We leave the prompt MySQL :

quitReboot the server MySQL in normal use:

sudo / etc / init.d /mysql restartAnother much simpler method:

sudo / etc / init.d /mysql reset-passwordAnother simple method that works on the latest Ubuntu (7.10, 8.x):

sudo dpkg-reconfigure mysql-server-5.0
Use a different port number
To activate the connection MySQL via port 3306, comment the following line in the file / etc /mysql/ my.cnf (adding # to the top of the line):

skip-networking


Incoming Connections
By default, MySQL listen only connections from localhost (127.0.0.1), is to tell the system where it is installed.

To access MySQL from another computer add the following line to / etc /mysql/ my.cnf in " [mysqld] "

bind-address = x.x.x.xwhere xxxx is the IP address of the interface on which MySQL must listen. This will only listen on a private IP if you have multiple network interfaces.

You can save if 0.0.0.0 MySQL should listen for connections from all network interfaces, and be fully accessible. This can be dangerous if your server is connected to the Internet directly.


Setting UTF-8
Just add the following two lines at the end of the section [mysqld] "File / etc /mysql/ my.cnf :

character-set-server = utf8
skip-character-set-client-handshake
then restart the server mysql :

sudo / etc / init.d /mysql restartYou can check the results once logged in mysql with the following command:

SHOW VARIABLES LIKE 'char%';Result:

mysqld> SHOW VARIABLES LIKE 'char%';
+--------------------------+---------------------- ------+
| Variable_name | Value |
+--------------------------+---------------------- ------+
| Character_set_client | utf8 |
| Character_set_connection | utf8 |
| Character_set_database | utf8 |
| Character_set_filesystem | binary |
| Character_set_results | utf8 |
| Character_set_server | utf8 |
| Character_set_system | utf8 |
| Character_sets_dir | / usr / share /mysqld / charsets / |
+--------------------------+---------------------- ------+
8 rows in set (0.00 sec)
 


Some Queries
All these commands are entered in a prompt mysql Once connected to the server


Database
Create a database
CREATE DATABASE nom_de_la_base;List of databases
SHOW DATABASES;
Tables
List of tables in the current database
SHOW TABLES;Table Structure
DESCRIBE table_name;Rename a table
ALTER TABLE table_name RENAME AS new_name;For the syntax of queries SQLRefer to the Documentation MySQL.


Users
Create a user
CREATE user a password
SET password FOR " = password("password");Delete a user
DROP user username @ localhost;
Privileges
Give all the rights in a database to a specific user
GRANT ALL ON dbname.* TO username @ localhost;Delete rights on a database to a specific user
REVOKE ALL privileges ON dbname.* FROM username @ localhost;
Other operations

Backup and import
For Export the database "mydb", use the command:

mysqldump-u root-p mydb> maBase_backup.sqlThis will export a file "maBase_backup.sql.

For matter a database or a backup "Mabasa, use the command:

mysql -u root-p mydb <maBase_backup.sqlThen type your password.

 

If you want to implement a backup solution for automatic database at regular intervals, visit the tutorial dealing with Automatic backup of databases via cron.

If we did not have access to mysqlcustomer (eg if it has recovered a hard disk system which is dead), we can save the directory "/ var / lib /mysql/ "Where each database is stored in a folder bearing his name.

Other commands such as deleting tables from the database, adding data in the database, etc.. can be found on the Site Documentation MySQL.


Compact the space used by ibdata1
Mysql Stoque its files in / var / lib /mysql. It comes with time and use that file ibdata1 occupies a very important disk space that will not be recovered even by removing all the bases.

Runs to understand and correct this problem:

•Removing InnoDB to recover space
•"Bug" associated
•Export all databases, stop mysql, Delete the file, start sql and import all the bases.