Installation and configuration of MySQL under Linux

Setting up a MySQL server, optimization and recommendations

Because the databases it serves...

MySQL and its installation

MySQL is the most widespread databases server for web servers. It combines a great flexibility of use and many features, while its implementation is simple.

Installation

Thanks to APT we install the matching packages:

apt-get install mysql-server mysql-client libmysqlclient15-dev mysql-common

Configuration

The basic configuration is stored in /etc/mysql/my.cnf

vi /etc/mysql/my.cnf

Here are a few interesting options to change default

language = / usr/share/mysql/English

Choose the default language for messages from the server (french)

key_buffer = 32 M

Size of the index cache

query_cache_limit = 2 M

Size limit of the cache by query

query_cache_size = 32 M

Total size of the cache of queries

#log_bin = /var/log/mysql/mysql-bin.log
#expire_logs_days = 10

Disable the binary log in commenting on these two lines thanks to # (the binary log is useful in the case of replications with master-slave servers)

log_slow_queries = /var/log/mysql/mysql-slow.log

Set log slow queries (useful to identify and optimize them)

long_query_time = 2

Time (in seconds) from which a request is considered slow

[mysqld]

default-character-set = utf8

Default character set for the server

default-collation = utf8_general_ci

Collation of the character set

[customer]

default-character-set = utf8

Default character set for the customer

Don't forget to reload the server as a result of these changes:

/etc/init.d/mysql reload

Optimization

If you want to optimize this configuration (after him have operated, the statistics are not representative initially), call MySQLTuner. It is a script written in Perl that will change not your configuration but you will propose improvements.

wget http://mysqltuner.com/mysqltuner.pl
chmod +x mysqltuner.pl
./mysqltuner.pl

Securing

It is easy to set up a few simple rules to secure the server by the script:

mysql_secure_installation

And answering a few questions:

Set root password? THERE

Allows you to change the password for root (in mysql)

Remove anonymous users? THERE

Remove anonymous access

Disallow root login remotely? THERE

Remove the (recommended) remote root access

Remove test database and access to it? THERE

Remove the test database and access

Reload privilege tables now? THERE

Reloads the privileges as a result of changes

Connection

You can connect through the client command line:

mysql -ulogin -pmotdepasse

Either by using a graphical client, or even also phpMyAdmin (default installation: apt-get install phpmyadmin and then edit config.inc.php or copy it from config.sample.inc.php)

Useful shell commands

ulogin password nom_base Pei mysqldump - opt >fichier.sql

Exports the nom_base base to fichier.sql

mysqldump ulogin password Pei - all-databases - opt >fichier.sql

Exports all the basics to fichier.sql

MySQLlogin password Pei u <fichier.sql

Important the instructions in the SQL file

mysqladmin

A collection of tools to administer the server.

Option -opt handset -add-drop-table - add-locks - all - extended-insert - quick - lock-tables. Refer to the documentation (man mysqldump) for more information.

Basic MySQL (customer) command-line commands

Complete all of your orders with the semicolon character.

SHOW DATABASES

List databases

SHOW STATUS

Displays the status of the server

USE nom_base

Selects the default base

SHOW TABLES

Displays the tables in the current database

DESCRIBLE table

Displays the structure of the table

SELECT * FROM table

Displays the contents of the table

CREATE DATABASE based

Creates a new database

To have orders in hand, don't forget the Memento MySQL by editions Eyrolles ;)

In summary:

• Creating database tables
• Connecting to a database
• Rename/delete databases, tables and keys
• Type of fields
• Index type (key)
• Users management
• Change the structure of a table
• Queries
• Conditional queries with WHERE
• Triggers (TRIGGER)
• Grouping and sorting
• Mathematical functions
• Unions and joins
• Various functions
• Manipulation of character strings
• Functions of dates
• Stored procedures and functions
• Transactions
• Views
• Administration and performance
• Server variables