Header Ads

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

Flipper
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

MySQLTuner
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

MySQL
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-tablesRefer 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 ;)
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.