Installation and configuration of MySQL under Linux

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.


Thanks to APT we install the matching packages:
apt-get install mysql-server mysql-client libmysqlclient15-dev mysql-common


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
default-character-set = utf8
Default character set for the server
default-collation = utf8_general_ci
Collation of the character set
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


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.
chmod +x


It is easy to set up a few simple rules to secure the server by the script:
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


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 or copy it from

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
A collection of tools to administer the server.
Option -opt handset -add-drop-table - add-locks - all - extended-insert - quick - lock-tablesRefer to the documentation (man mysqldump) for more information.

Basic MySQL (customer) command-line commands

Complete all of your orders with the semicolon character.
List databases
Displays the status of the server
USE nom_base
Selects the default base
Displays the tables in the current database
Displays the structure of the table
Displays the contents of the table
Creates a new database
To have orders in hand, don't forget the Memento MySQL by editions Eyrolles ;)
Memento MySQL
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

No comments

Powered by Blogger.