Archives pour la catégorie ‘Tutoriel’

8 Practical Examples of Linux Xargs Command for Beginners

27/03/2017 Comments off

The Linux xargs command may not be a hugely popular command line tool, but this doesn’t take away the fact that it’s extremely useful, especially when combined with other commands like findand grep. If you are new to xargs, and want to understand its usage, you’ll be glad to know that’s exactly what we’ll be doing here.

Before we proceed, please keep in mind that all the examples presented in this tutorial have been tested on Ubuntu 14.04 LTS. Shell used is Bash, and version is 4.3.11.

1. How Xargs command works?

Well, before jumping onto its usage, it’s important to understand what exactly Xargs does. In layman’s terms, the tool – in its most basic form – reads data from standard input (stdin) and executes the command (supplied to it as argument) one or more times based on the input read. Any blanks and spaces in input are treated as delimiters, while blank lines are ignored. 


If no command is supplied as argument to xargs, the default command that the tool executes is echo. For example, in the following example, I just executed ‘xargs’ and entered ‘Hello World’ on stdin. As I pressed Ctrl+D (to tell xargs that we’re done with the input), the echocommand was automatically executed, and ‘Hello World’ was printed again.

How xargs command works

2. How to use xargs with another command?

While echo is the default command xargs executes, you can explicitly specify any other command. For example, you can pass the find command along with its ‘-name’ option as argument to xargs, and then pass the name of the file (or type of files) you want find to search as input through stdin.

Here’s the complete command in question:

xargs find -name

For example, we provided « *.txt » in input through stdin, which means we want the find command to search all .txt files in the current directory (as well as its subdirectories).

Here’s the command in action:

Combine xargs with other commands

Lire la suite…

Categories: Système, Tutoriel Tags: , ,

How to save rules of the iptables?

18/03/2017 Comments off

Saving iptables rules for reboot

On a server, iptables rules don’t reload automatically at reboot. You need to reload the rules using ax executable shell scripture a dedicated utility that will load them at the same time as the program itself, i.e. with the kernel.

Depending of the version of Linux you use, you can select different methods:

sudo su
iptables-save > /etc/iptables.rules

In /etc/network/if-pre-up.d/iptables, put:

iptables-restore < /etc/iptables.rules
exit 0

After, in /etc/network/if-post-down.d/iptables, put:

iptables-save -c > /etc/iptables.rules
if [ -f /etc/iptables.rules ];
       then iptables-restore < /etc/iptables.rules
exit 0

After, give permission to the scripts:

sudo chmod +x /etc/network/if-post-down.d/iptables sudo chmod +x /etc/network/if-pre-up.d/iptables

Another scenario is to is to install iptables-persistent:

sudo apt-get install iptables-persistent

After it’s installed, you can save/reload iptables rules anytime:

    sudo /etc/init.d/iptables-persistent save 
    sudo /etc/init.d/iptables-persistent reload

Or if you use Ubuntu server 16.04, things are simpler:

The installation as described above works without a problem, but the two commands for saving and reloading above do not seem to work with a 16.04 server. The following commands work with that version:

    sudo netfilter-persistent save
    sudo netfilter-persistent reload

How to Disable Webcam / FaceTime Camera on Mac Completely

02/03/2017 Comments off


Some of the more privacy conscious Mac users out there might put tape over their webcam or use apps like Oversight to detect camera activity. While either of those approaches can be satisfactory for many users (or considered totally paranoid and overboard to others), many advanced Mac users in the security community go a step further and just outright disable their Macs front-facing web camera. This article will show you how to completely deactivate the front FaceTime camera on a Mac.


To be clear, this aims to completely disable the software components behind the built-in camera on Macs which prevents it from being used by any application, this webcam is sometimes called the FaceTime camera or the iSight camera, or simply the front-facing camera. All modern Macs have this camera, it is located at the top of the display and embedded into the screen bezel. By disabling the Macs camera, any application that requires it’s usage will no longer function as intended because camera access will become impossible. 

This is an advanced tutorial aimed at advanced users, it is not intended for novice or casual Mac users. This approach disables the Mac built-in camera by changing system level permissions for system level files directly relating to the camera components. If you are not comfortable modifying system files using the command line with super user privileges, do not proceed.

This tutorial applies to modern versions of MacOS including Sierra and El Capitan, you will need turn off rootless temporarily so that you can make modifications to the system folder, if you’re not sure how to do that, you can learn how to disable SIP on Mac OS here. You should aways backup a Mac before making any modifications to system software. Older versions of Mac OS X that wish to disable the iSight camera can follow these instructions instead to accomplish the same effect.

How to Disable the Web Camera on Mac

This is a string of commands that will completely disable the built-in Mac camera, meaning no applications will be able to use the front-facing camera at all. This is intended for advanced users only who thoroughly understand proper syntax and command line usage. 

  1. Back up the Mac if you have not done so already, then you will need to disable SIPfirst (and yes you should re-enable it when finished)
  2. Open the Terminal app as found in /Applications/Utilities/
  3. One by one on their own line and executed separately, issue the following five command strings into the command line and authenticate:

sudo chmod a-r /System/Library/Frameworks/CoreMediaIO.framework/Versions/A/Resources/VDC.plugin/Contents/MacOS/VDC

sudo chmod a-r /System/Library/PrivateFrameworks/CoreMediaIOServicesPrivate.framework/Versions/A/Resources/AVC.plugin/Contents/MacOS/AVC

sudo chmod a-r /System/Library/QuickTime/QuickTimeUSBVDCDigitizer.component/Contents/MacOS/QuickTimeUSBVDCDigitizer

sudo chmod a-r /Library/CoreMediaIO/Plug-Ins/DAL/AppleCamera.plugin/Contents/MacOS/AppleCamera

sudo chmod a-r /Library/CoreMediaIO/Plug-Ins/FCP-DAL/AppleCamera.plugin/Contents/MacOS/AppleCamera

  1. Exit Terminal when complete, don’t forget to re-enable SIP on the Mac as well

(Note you can also use chmod 200 instead of a-r if you prefer using numbers, the effect will be the same and permissions will be –w——-)

After the Mac camera has been disabled this way, if you attempt to open FaceTime, Skype, Photo Booth, QuickTime, iMovie, or any other app which uses the built-in camera, you will get a message stating “there is no connected camera” on the Mac – which is exactly what you would want to see if you disabled the camera intentionally.

Mac camera disabled as shown by no camera connected error message

You should not need to reboot for the changes to take effect, though you may need to relaunch some active applications with camera access.

How to Re-Enable the Camera on Mac

Just as before when disabling the camera, to re-enable the Mac camera this way you will likely need to temporarily disable SIP in Mac OS before beginning. Then the commands to issue one by one are as follows:

sudo chmod a+r /System/Library/Frameworks/CoreMediaIO.framework/Versions/A/Resources/VDC.plugin/Contents/MacOS/VDC

sudo chmod a+r /System/Library/PrivateFrameworks/CoreMediaIOServicesPrivate.framework/Versions/A/Resources/AVC.plugin/Contents/MacOS/AVC

sudo chmod a+r /System/Library/QuickTime/QuickTimeUSBVDCDigitizer.component/Contents/MacOS/QuickTimeUSBVDCDigitizer

sudo chmod a+r /Library/CoreMediaIO/Plug-Ins/DAL/AppleCamera.plugin/Contents/MacOS/AppleCamera

sudo chmod a+r /Library/CoreMediaIO/Plug-Ins/FCP-DAL/AppleCamera.plugin/Contents/MacOS/AppleCamera

(Note you can also use chmod 755 instead of a+r if you prefer using numbers to return to -rwxr-xr-x, the effect will be the same)

You’ll notice the difference between the enabling and disabling commands are simply the permissions change chmod command flag – has turned into a +, indicating the file(s) have read access now whereas before they did not, which is what prevented the camera from working. 

If this approach is insufficient for your privacy or security needs for whatever reason, you’d likely need to go a step further and actually disassemble your Mac hardware to physically disconnect any camera cables, a task which is quite advanced but undeniably the most effective approach if you want to completely disable the Mac camera and don’t ever want the Macs camera to be used.

Why would I want to disable the Mac camera?

Most Mac users would not want to disable their FaceTime / iSight camera. Typically only very advanced Mac users who have a specific reason to completely disable the built-in camera on their Mac would want to do this, whether they are systems administrators, security professionals, for privacy reasons, or otherwise. This is not intended for the average Mac user. If you’re an average, casual, or novice Mac user who is concerned about privacy and any possible camera shenanigans, try putting tape on your web cam, like the FBI director does, which is much lower tech and less involved, easy to reverse, and quite effective since obviously if something is obstructing the camera lens than it is not usable.


Categories: Sécurité, Système, Tutoriel Tags: ,

HowTo: The Ultimate Logrotate Command Tutorial with 10 Examples

12/11/2016 Comments off

Managing log files effectively is an essential task for Linux sysadmin.

In this article, let us discuss how to perform following log file operations using UNIX logrotateutility.

  • Rotate the log file when file size reaches a specific size
  • Continue to write the log information to the newly created file after rotating the old log file
  • Compress the rotated log files
  • Specify compression option for the rotated log files
  • Rotate the old log files with the date in the filename
  • Execute custom shell scripts immediately after log rotation
  • Remove older rotated log files

1. Logrotate Configuration files

Following are the key files that you should be aware of for logrotate to work properly.

/usr/sbin/logrotate – The logrotate command itself.

/etc/cron.daily/logrotate – This shell script executes the logrotate command everyday.

$ cat /etc/cron.daily/logrotate

/usr/sbin/logrotate /etc/logrotate.conf
if [ $EXITVALUE != 0 ]; then
    /usr/bin/logger -t logrotate "ALERT exited abnormally with [$EXITVALUE]"
exit 0

/etc/logrotate.conf – Log rotation configuration for all the log files are specified in this file.


$ cat /etc/logrotate.conf
rotate 4
include /etc/logrotate.d
/var/log/wtmp {
    minsize 1M
    create 0664 root utmp
    rotate 1

/etc/logrotate.d – When individual packages are installed on the system, they drop the log rotation configuration information in this directory. For example, yum log rotate configuration information is shown below.

$ cat /etc/logrotate.d/yum
/var/log/yum.log {
    size 30k
    create 0600 root root

2. Logrotate size option: Rotate the log file when file size reaches a specific limit

If you want to rotate a log file (for example, /tmp/output.log) for every 1KB, create the logrotate.conf as shown below.

$ cat logrotate.conf
/tmp/output.log {
        size 1k
        create 700 bala bala
        rotate 4

This logrotate configuration has following three options:

  • size 1k – logrotate runs only if the filesize is equal to (or greater than) this size.
  • create – rotate the original file and create the new file with specified permission, user and group.
  • rotate – limits the number of log file rotation. So, this would keep only the recent 4 rotated log files.

Before the logrotation, following is the size of the output.log:

$ ls -l /tmp/output.log
-rw-r--r-- 1 bala bala 25868 2010-06-09 21:19 /tmp/output.log

Now, run the logrotate command as shown below. Option -s specifies the filename to write the logrotate status.

$ logrotate -s /var/log/logstatus logrotate.conf

Note : whenever you need of log rotation for some files, prepare the logrotate configuration and run the logroate command manually.
After the logrotation, following is the size of the output.log:

$ ls -l /tmp/output*
-rw-r--r--  1 bala bala 25868 2010-06-09 21:20 output.log.1
-rwx------ 1 bala bala        0 2010-06-09 21:20 output.log

Eventually this will keep following setup of rotated log files.

  • output.log.4.
  • output.log.3
  • output.log.2
  • output.log.1
  • output.log

Please remember that after the log rotation, the log file corresponds to the service would still point to rotated file (output.log.1) and keeps on writing in it. You can use the above method, if you want to rotate the apache access_log or error_log every 5 MB.

Ideally, you should modify the /etc/logrotate.conf to specify the logrotate information for a specific log file.

Also, if you are having huge log files, you can use: 10 Awesome Examples for Viewing Huge Log Files in Unix

3. Logrotate copytruncate option: Continue to write the log information in the newly created file after rotating the old log file.

$ cat logrotate.conf
/tmp/output.log {
         size 1k
         rotate 4

copytruncate instruct logrotate to creates the copy of the original file (i.e rotate the original log file) and truncates the original file to zero byte size. This helps the respective service that belongs to that log file can write to the proper file.

While manipulating log files, you might find the sed substitutesed delete tips helpful.

4. Logrotate compress option: Compress the rotated log files

If you use the compress option as shown below, the rotated files will be compressed with gzip utility.

$ cat logrotate.conf
/tmp/output.log {
        size 1k
        create 700 bala bala
        rotate 4

Output of compressed log file:

$ ls /tmp/output*
output.log.1.gz output.log

5. Logrotate dateext option: Rotate the old log file with date in the log filename

$ cat logrotate.conf
/tmp/output.log {
        size 1k
        create 700 bala bala
        rotate 4

After the above configuration, you’ll notice the date in the rotated log file as shown below.

$ ls -lrt /tmp/output*
-rw-r--r--  1 bala bala 8980 2010-06-09 22:10 output.log-20100609.gz
-rwxrwxrwx 1 bala bala     0 2010-06-09 22:11 output.log

This would work only once in a day. Because when it tries to rotate next time on the same day, earlier rotated file will be having the same filename. So, the logrotate wont be successful after the first run on the same day.

Typically you might use tail -f to view the output of the log file in realtime. You can even combine multiple tail -f output and display it on single terminal.

6. Logrotate monthly, daily, weekly option: Rotate the log file weekly/daily/monthly

For doing the rotation monthly once,

$ cat logrotate.conf
/tmp/output.log {
        rotate 4

Add the weekly keyword as shown below for weekly log rotation.

$ cat logrotate.conf
/tmp/output.log {
        rotate 4

Add the daily keyword as shown below for every day log rotation. You can also rotate logs hourly.

$ cat logrotate.conf
/tmp/output.log {
        rotate 4

7. Logrotate postrotate endscript option: Run custom shell scripts immediately after log rotation

Logrotate allows you to run your own custom shell scripts after it completes the log file rotation. The following configuration indicates that it will execute after the logrotation.

$ cat logrotate.conf
/tmp/output.log {
        size 1k
        rotate 4

8. Logrotate maxage option: Remove older rotated log files

Logrotate automatically removes the rotated files after a specific number of days.  The following example indicates that the rotated log files would be removed after 100 days.

$ cat logrotate.conf
/tmp/output.log {
        size 1k
        rotate 4
        maxage 100

9. Logrotate missingok option: Dont return error if the log file is missing

You can ignore the error message when the actual file is not available by using this option as shown below.

$ cat logrotate.conf
/tmp/output.log {
        size 1k
        rotate 4

10. Logrotate compresscmd and compressext option: Sspecify compression command for the log file rotation

$ cat logrotate.conf
/tmp/output.log {
        size 1k
        compresscmd /bin/bzip2
        compressext .bz2
        rotate 4

Following compression options are specified above:

  • compress – Indicates that compression should be done.
  • compresscmd – Specify what type of compression command should be used. For example: /bin/bzip2
  • compressext – Specify the extension on the rotated log file. Without this option, the rotated file would have the default extension as .gz. So, if you use bzip2 compressioncmd, specify the extension as .bz2 as shown in the above example.
Categories: Système, Tutoriel Tags:

Apache Web Server Hardening & Security Guide

13/05/2016 Comments off

apache security best practicesSecure Apache Web Server – Practical Guide

1       Introduction

The Web Server is a crucial part of web-based applications. Apache Web Server is often placed at the edge of the network hence it becomes one of the most vulnerable services to attack. Having default configuration supply many sensitive information which may help hacker to prepare for an attack the web server.

The majority of web application attacks are through XSS, Info Leakage, Session Management and PHP Injection attacks which is due to weak programming code and failure to sanitize web application infrastructure. According to the security vendor Cenzic, 96% of tested applications have vulnerabilities. Below chart from Cenzic shows the vulnerability trend report of 2013.

This practical guide provides you the necessary skill set to secure Apache Web Server.  In this course, we will talk about how to Harden & Secure Apache Web Server on Unix platform. Following are tested on Apache 2.4.x and I don’t see any reason it won’t work with Apache 2.2.x.

  1. This assumes you have installed Apache on UNIX platform. If not, you can go through Installation guide. You can also refer very free video about how to Install Apache, MySQL & PHP.
  2. We will call Apache installation directory /opt/apache as $Web_Server throughout this course.
  3. You are advised to take a backup of existing configuration file before any modification.

1.1  Audience

This is designed for Middleware Administrator, Application Support, System Analyst, or anyone working or eager to learn Hardening & Security guidelines. Fair knowledge of Apache Web Server & UNIX command is mandatory. This is seven page guide, click on Next to proceed. You may navigate through table of contents at right hand side.


BONUS (Download in PDF Format): Apache HTTP Security & Hardening Guide

Lire la suite…

Tutorial: Using VMWare ESXi and PFsense as a network firewall/router

12/05/2016 Comments off

vmware esxi

Using VMWare ESXi and PFsense as a network firewall/router

In most networks, you will have dedicated hardware to function as your “edge” (firewall/router). This is typically for the best, but there are always cases where you can’t put out that dedicated hardware. Sometimes it’s for cost reasons and sometimes it’s for complexity. In my particular case, I was installing an ESXi server in a datacenter and only had 2 amps of power to work with, of which my server took up ~1.8amps at peak load. So cost came into play and we simply couldn’t afford to put in dedicated hardware that could push enough bits. In such cases, it is possible the setup ESXi on the network edge, in a reasonably secure fashion, with PFSense acting as a firewall.

vmware_vsphereThe most important requirement to this project is that your VMWare ESXi server has at least two network ports on it. One will be the WAN port, one will be the LAN port. Also throughout this tutorial I will use PFSense as my firewall/router OS of choice, however it is just an example that can be easily swapped out with any other virtualized firewall product. Some options include Palo Alto Networks, Fortinet, and even generic *NIX operating systems with the right forwarding/firewall setup.

Section 1 – VMWare Setup

Step 1 – Install & Connect to ESXi

  • You should already have ESXi setup and connected via the VSphere client on Windows.
  • It’s recommended that you static the IP address of the VMWare Management interface, if you’ve not done so already.
  • Go to Configuration > Networking
  • Rename the vSwitch interface you’re using to “LAN”
Step 2 – Add new interface
You want “Virtual Machine” type
Step 3 – Select NIC
You want to select your unused NIC (assuming you only have two)
Step 4 – Name it
This is your “WAN” interface
Step 5 – Confirm you’ve got two networks
You’ll notice that we’ve got two vSwitches now. The “LAN” switch has the Management network and is connected currently. The “WAN” switch has nothing, and the adapter is disconnected.

Section 2 – Virtual Machine Setup

Step 1 – New VM 2015-08-25-18_29_17-New-VM
Step 2 – Typical Setup 2015-08-25-18_29_31-Create-New-Virtual-Machine
Step 3 – Name your VM 2015-08-25-18_29_39-Create-New-Virtual-Machine
Step 4 – Select Datastore 2015-08-25-18_29_46-Create-New-Virtual-Machine
Step 5 – OS Type
If you’re using PFSense, select “Other” and “FreeBSD 64bit”
Step 6 – Two NICs
Unlike most VMs with 1 NIC, add 2 NICs to this VM.
Make sure one adapter is on “WAN” network and one adapter is on “LAN” network.
Step 7 – Allocated HD
PFSense doesn’t need much space, but it should be allocated a 2:1 for swap (e.g. 4096 MB swap file for 2048 MB of RAM), plus some extra space for packages and logs may be useful.
Step 8 – Edit before completion 2015-08-25-18_30_46-Create-New-Virtual-Machine
Step 9 – Final settings
As this is my firewall, I want to make sure it is plenty fast. So I opted for 4 cores and 2 GB RAM. Also attach the CD drive to PFSense installer (be it datastore ISO or real USB/Optical drive).
Step 8 – Verify Network
Hop back to Configuration > Networking and you should see something like this. Note: various VMs are all attached to the LAN vSwitch, however only PFsense VM is attached to both WAN & LAN (just like a real firewall).
Step 9 – VM Startup
Go to Configuration > VM Startup/Shutdown
Click Properties
Step 10 – Set PFSense to first boot order
You may have other VMs that you want to auto-start, but as this is your firewall, it should be the first to start.

Section 3 – PFSense

Step 1 – Install PFSense
Once you’ve installed PFSense, it will automatically configure its local interface to
Step 2 (Optional) – Change local network
You can reconfigure the local network either via web interface (at the aforementioned IP: or command line
Step 3 – Configure WAN
Again, this can be configured either via the web, or command line.
Step 4 – Plug in WAN cable 2015-08-19-13.59.53
Step 5 – Test
If you’ve got the ports configured properly (i.e. WAN hardware is WAN in VMWare and WAN in PFSense), you should be able to connect to the internet.

There are two big questions after building a setup like this, the first is security. Since PFSense is the host to provide an interface on the WAN, it should be the only method of ingress into your network. With no VMware management interface on the WAN, there should be no way for an outside party to access ESXi directly. I’ve used this setup successfully (and safely) before, as have others. However, you always need to balance your particular security concerns with the cost of dedicated devices.

The second question is remote management/maintenance/failure. Managing ESXi remotely is easy, if you setup a VPN on your PFSense VM. Without that (or similar) you will not be able to remotely manage the box (by design). But what happens if there is a failure either in the VMWare hardware or the PFSense virtual machine? That’s the big failing point of this setup – you’re down. If, for whatever reason, PFsense dies – your network is offline and you cannot remotely manage it. If this hardware is installed in a dateacenter, you’d need to either get in there yourself or remote hands reboot. Something to keep in mind when balancing the cost issue. OF course, if it’s local (say you use this at home), then it’s not such a big deal.
IMG_07121I will note that this is the setup I use in my home network, which doubles as my homelab. Having a VM for a firewall gives me a lot of flexibility, like adding an entirely separate vSwitched network for experimental VMs. I can also swap out the firewall VM for another one with next to no downtime. It also allows me to skip one more piece of hardware at home which would add to my otherwise hefty powerbill.


Categories: Réseau, Sécurité, Tutoriel Tags:

MySQL database replication with Linux

04/03/2016 Comments off

MySQL database replication with Linux

Database replication is a technique where a given database is copied to one or more locations, so that the reliability, fault-tolerance or accessibility of the database can be improved. Replication can be snapshot-based (where entire data is simply copied over to another location), merge-based (where two or more databases are merged into one), or transaction-based (where data updates are periodically applied from master to slaves).

MySQL replication is considered as transactional replication. To implement MySQL replication, the master keeps a log of all database updates that have been performed. The slave(s) then connect to the master, read individual log entries, and perform recorded updates. Besides maintaining a transaction log, the master performs various housekeeping tasks, such as log rotation and access control. When new transactions occur and get logged on the master server, the slaves commit the same transactions on their copy of the master database, and update their position in the master server’s transaction log. This master-to-slave replication process is done asynchronously, which means that the master server doesn’t have to wait for the slaves to catch up. If the slaves are unable to connect to the master for a period of time, they will download and execute all pending transactions when connectivity is re-established.

Database replication allows one to have an exact copy of a live database of a master server at another remote server (slave server) without taking the master server offline. In case the master server is down or having any trouble, one can temporarily point database clients or DNS resolver to the slave server’s IP address, achieving transparent failover. It is must be noted that MySQL replication is not a backup solution. For example, if an unintended DELETE command gets executed in the master server by accident, the same transaction will mess up all slave servers.

In this article, we will demonstrate master-slave based MySQL replication on two Linux computers. Let’s assume that the IP addresses of master/slave servers are and, respectively.

Setting up a Master MySQL Server

This part will explain the steps needed on the master server.

First, log in to MySQL, and create test_repl database.

$ mysql -u root -p
mysql> CREATE DATABASE test_repl;

Next, create a table inside test_repl database, and insert three sample records.

mysql> USE test_repl;
mysql> CREATE TABLE employee (EmployeeID int, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255));
mysql> INSERT INTO employee VALUES(1,"LastName1","FirstName1","Address1","City1"),(2,"Lastname2","FirstName2","Address2","City2"),(3,"LastName3","FirstName3","Address3","City4");

After exiting the MySQL server, edit my.cnf file using your favorite text editor. my.cnf is found under /etc, or /etc/mysql directory.

# nano /etc/my.cnf

Add the following lines under [mysqld] section.


The server-id option assigns an integer ID (ranging from 1 to 2^23) to the master server. For simplicity, ID 1 and 2 are assigned to the master server and the slave server, respectively. The master server must enable binary logging (with log-bin option), which will activate the replication. Set the binlog-do-db option to the name of a database which will be replicated to the slave server. The innodb_flush_log_at_trx_commit=1 and sync_binlog=1options must be enabled for the best possible durability and consistency in replication.

After saving the changes in my.cnf, restart mysqld daemon.

# systemctl restart mysqld


# /etc/init.d/mysql restart

Log in to the master MySQL server, and create a new user for a slave server. Then grant replication privileges to the new user.

mysql> CREATE USER repl_user@;
mysql> GRANT REPLICATION SLAVE ON *.* TO repl_user@ IDENTIFY BY 'repl_user_password';

A new user for the slave server is repl_user, and its password is repl_user_password. Note that the master MySQL server must not bind to the loopback interface since a remote slave server needs to log in to the master server as repl_user. Check this tutorial to change MySQL server’s binding interface.

Finally, check the master server status by executing the following command on the server.



Please note that the first and second columns (e.g., master-bin.000002 and 107) will be used by the slave server to perform master-to-slave replication.

Lire la suite…

Sauvegarde MySQL

03/02/2016 Comments off

sauvegarde mysqlSauvegarde MySQL

Pour sauvegarder une base de données (sans et avec compression) :

# mysqldump NOM_BASE > NOM_FICHIER
# mysqldump NOM_BASE | gzip > NOM_FICHIER

Pour restaurer une base de données (sans et avec compression) :

# mysqladmin create NOM_BASE
# gunzip < NOM_FICHIER | mysql NOM_BASE

Sauvegarder toutes les bases :

# mysqldump --opt --all-databases > NOM_FICHIER

Pour sauvegarder uniquement certaines tables :


Pour presque faire un « –exclude » (qui manque cruellement à mysqldump):

mysql -B -N -e 'show databases' | 
  perl -ne 'print unless /b(?:phpmyadmin|mysql|information_schema)b/' | 
  xargs echo mysqldump -B

Et pour sauvegarder des tables correspondant à un motif (préfixe le plus souvent) :

# mysqldump NOM_BASE $(mysql NOM_BASE -B --column-names=False -e "show tables like 'exemple_%'") > NOM_FICHIER

Pour dumper avec une condition particulière :

mysqldump -t <base> <table> --where="my_id='66666666'"

Ce qui permet de réinjecter des données résultantes d’un SELECT * FROM base.table WHERE my_id='66666666'.

Il est évidement possible de faire toutes ces opération sur une instance en précisant son port avec l’option –port (valable pour mysqldump et mysql).

Pour obtenir une liste des utilisateurs mysql, on peut utiliser cette fonction (glanée sur serverfault) :

    'SHOW GRANTS FOR ''', user, '''@''', host, ''';'
    ) AS query FROM mysql.user" | 
  mysql | 
  sed 's/(GRANT .*)/1;/;s/^(Grants for .*)/## 1 ##/;/##/{x;p;x;}'

Lire la suite…

MySQL – Gestion des binlogs

03/02/2016 Comments off

mysql binlogsPar défaut, MySQL stocke chaque requête en écriture dans des fichiers appelés binlogs.

Configuration des binlogs

Par défaut les binlogs sont conservés sur 10 jours, avec des fichiers n’excédant pas 100 Mo :

#log_bin                        = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
#binlog_do_db           = include_database_name
#binlog_ignore_db       = include_database_name
binlog_format           = mixed


On peut choisir 3 types de format pour les binlogs :

  • statement : les requêtes INSERT / UPDATE sont conservées
  • row : les modifications de chaque ligne sont conservées (via une sorte de code « binaire » propre à MySQL)
  • mixed : en mode statement… sauf dans certains cas où cela passe en mode row

Avantages et inconvénients :

Le mode statement est utile pour conserver en clair toutes les requêtes. Il permet aussi de meilleures performances quand des UPDATE contiennent des clauses WHERE qui modifient de nombreuses lignes. Pour de la réplication, il peut être non fiable car le résultat d’un UPDATE peut donner des résultats différents sur un serveur SLAVE. Cela peut aussi poser des soucis avec les transactions InnoDB.

Le mode row a l’inconvénient de rendre illisibles toutes les requêtes. Dans certains cas particuliers (UPDATE contiennent des clauses WHERE qui modifient de nombreuses lignes), il peut être moins performant. Il a l’avantage d’être plus fiable pour de la réplication.

Le mode mixed est un bon compromis pour de la réplication : il permet de voir la plupart des requêtes en clair, mais évite le problème de fiabilité en passant en mode row quand c’est nécessaire.


Pour supprimer les binlogs antérieurs à mysql-bin.00NNNN :

mysql> PURGE BINARY LOGS TO 'mysql-bin.00NNNN';

ou par rapport à une date :

mysql> PURGE BINARY LOGS BEFORE "2011-12-07 00:00:00";


Pour désactiver les binlogs, on ajoutera l’option suivante dans la configuration :



On pourra lire en ligne de commande le contenu d’un binlog via la commande :

# mysqlbinlog /var/log/mysql/mysql-bin.001789 | less

Note : si vous obtenez une erreur mysqlbinlog: unknown variable 'default-character-set=utf8' c’est que la directive default-character-set a été placée dans la configuration MySQL (/etc/mysql ou .my.cnf) dans la mauvaise section : [client] au lieu de [mysql] (ou [mysqldump]).



On pourra ainsi injecter le contenu d’un binlog dans une base… tout simplement avec une commande du type :

# mysqlbinlog /var/log/mysql/mysql-bin.001789 | mysql -P3307

À noter que si une partie des données étaient déjà présentes (cas d’un binlog corrompu lors d’incident lors d’une réplication), on pourra procéder ainsi :

# mysqlbinlog /var/log/mysql/mysql-bin.001789 > mysql-bin.001789.txt
# sed -i 's/INSERT INTO/INSERT IGNORE INTO/gi' mysql-bin.001789.txt
# cat mysql-bin.001789.txt | mysql -P3307

Log des requêtes lentes

Pour débugger les applications lentes, c’est une fonctionnalité intéressante de trouver quel requête est longue. Pour cela on peut spécifier quand une requêtes est considéré comme longue, le chemin où stocker les requêtes, et l’activation des logs.

long_query_time = 2 #Default 10 !
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log

Source: Evolix

MySQL Master / Slave Replication

29/01/2016 Comments off

Source: Uptime Made Easy

Master Slave MySQL Replication Summary

Master / Slave replication in MySQL is a great way to store an exact replica of your database on another machine in another location as part of a disaster recovery plan.  Before setting up Master / Slave replication there are a few things to remember.

  • Writes – Writes to the master database should make it to the slave.  But writes to the slave will not make it to the master.  If you do write records to the slave database directly, be prepared to have to either recreate the records or back them up separately and recover them if the replication breaks.  Many times the only way to get the databases to replicate again is to backup the master and recover it over the top of the slave deleting anything that was in the slave database before.
  • Broken Replication – Writes made directly to the slave can cause the replication to break due to duplicate key rows, etc..  Always write to the master.
  • Reads – Reads should be possible from either server.  Many organizations will use replication so as to create another database to read from thereby taking the load of all of their select statements and reports off the master server.


MySQL Replication schemeMaster Slave MySQL Replication

Steps to Setup MySQL Master / Slave Replication


We will be assuming that the following prerequisites are done prior to beginning the steps listed below:

  • MySQL has been installed on both the master and the slave servers
  • The slave server is able to communicate directly to the mysqld port (typically 3306) on the master server, meaning that there is no firewall, routing, NAT or other problems preventing communication.
  • You have an administrator MySQL user that can create users on both the master and the slave machines.
  • You have permissions to edit the /etc/my.cnf files on both machines and enough privileges to restart mysql.

That should be it!  Let’s begin setting it up.

Lire la suite…