Skip to content

About Mysql

MySQL is the world's most popular open source database. With its proven performance, reliability and ease-of-use, MySQL has become the leading database choice for web-based applications, used by high profile web properties including Facebook, Twitter, YouTube, Yahoo! and many more...

Installation

We install mysql/mariadb package

sudo apt install mariadb-server

We launch the securization of our SQL server by typing.

During the process you can set up your root password, reject the remote access on your DB, delete anonymous accounts, etc...

sudo mysql_secure_installation

Set up SQL server

Client configuration

Edit /etc/mysql/mariadb.conf.d/50-client.cnf to change the charset.

We must do that to avoid to broke our database.

default-character-set = utf8mb4

Configure on which port we want listen with our mariadb server.

port    = 3306

Server configuration

Edit /etc/mysql/mariadb.conf.d/50-server.cnf to adapt as beneath.

user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
innodb_file_per_table = 1
local-infile=1

We configure on which ip our SQL server is running.

# If you want listen on localhost only
bind-address        = 127.0.0.1

# If you want listen on internet
bind-address        = 0.0.0.0

And we add these value.

key_buffer_size     = 128M
max_allowed_packet  = 16M
thread_stack        = 256K
thread_cache_size       = 20
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam_recover_options  = BACKUP
max_connections        = 250
table_cache            = 64
thread_concurrency     = 10

We set up the cache.

query_cache_limit   = 64M
query_cache_size    = 128M

We set up innodb (to be adapted to the case by case)

innodb_buffer_pool_size = 4G
innodb_log_buffer_size = 1G
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit=2
innodb_buffer_pool_instances = 30

Note

For better performance, innodb_log_buffer_size should be equal to 25% of the value of innodb_buffer_pool_size

Server charset modification

# charachter-set-server = utf8 is possible
character-set-server  = utf8mb4
collation-server      = utf8mb4_general_ci

Access management

  • Connect to your sql server mysql -u root -p
  • Create our data base create database $dbname;
  • Create our database user create user "$user"@"$host" identified by "$pass";
  • Give right permissions to the user grant all privileges on $dbname.* to "$user"@"$host";
  • Refresh the permissions flush privileges;
  • And exit sql server by quit;

Backup

We will write a simple backup script with database compression called backup_db.sh.

#!/bin/bash
#
## on se place dans le repertoire ou l'on veut sauvegarder les bases
#
cd /$user/scripts/backups/

for i in $BDD; do

## Sauvegarde des bases de donnees en fichiers .sql
mysqldump -uroot -p$PASS $i > ${i}_`date +"%Y-%m-%d"`.sql

## Compression des exports en tar.bz2 (le meilleur taux de compression)
tar jcf ${i}_`date +"%Y-%m-%d"`.sql.tar.bz2 ${i}_`date +"%Y-%m-%d"`.sql

## Suppression des exports non compresses
rm ${i}_`date +"%Y-%m-%d"`.sql

done

Automatization of the backup cleaning.

#!/bin/bash
#
# All save older than one weak are deleted
#
find /$user/scripts/backups/ -type f -mtime +6 -delete

We create symbolics links for our daily usage

ln -s /$user/scripts/backup_bdd.sh 01-backup_db
ln -s /$user/scripts/clean_backups.sh 02-clean_backups

Structure of our backup system.

├── scripts
│   ├── backup_bdd.sh
│   ├── backups
│   └── clean_backups.sh