Кратко и по существу

Как измерить средний размер записей в MySQL

Следующий запрос вернёт количество обработанных строк, средний размер данных в столбце MESSAGE и максимальный размер данных в столбце MESSAGE для данных за июнь 2020 года из таблицы COMMENTS:

SELECT COUNT(*) count, AVG(CHAR_LENGTH(`MESSAGE`)) avg_length, MAX(CHAR_LENGTH(`MESSAGE`)) max_length
FROM `COMMENTS`
WHERE (`DATE_POSTED` >= '2020-06-01') AND (`DATE_POSTED` < '2020-07-01');
 12   18 дн  

Полезные команды и настройки postfix

1. Как очистить очередь email сообщений:

sudo postsuper -d ALL deferred

2. Как посмотреть значение параметра postfix по умолчанию:

sudo postconf -d параметр

3. Как посмотреть установленное значение параметра postfix:

sudo postconf | grep 'параметр'

4. Как сохранять заголовок письма в /var/log/mail.log:

  1. В основном конфигурационном файле postfix /etc/postfix/main.cf задайте следующий параметр:
header_checks = regexp:/etc/postfix/header_checks
  1. В файле /etc/postfix/header_checks (необходимо создать, если отсутствует) добавьте на новой строке следующее:
/^Subject:/     WARN
  1. Теперь этот файл нужно превратить в индексированную БД и перезагрузить postfix с помощью следующих команд:
sudo postmap /etc/postfix/header_checks
sudo postfix reload

5. Как ограничить количество сообщений, отправляемых через postfix, в час:

В конфиге /etc/postfix/main.cf задайте следующие параметры:

anvil_rate_time_unit = 1h
smtpd_client_message_rate_limit = 200
smtpd_client_recipient_rate_limit = 30
smtpd_client_connection_rate_limit = 20

Пояснение: можно отправлять не более 200 сообщений в час, при этом количество получателей должно быть не более 30, количество соединений на одного пользователя не более 20.

Перезагрузите postfix:

sudo postfix reload

6. Как изменить время, через которое неотправленные письма повторно отправляются в postfix, и как долго будут храниться в очереди неотправленные сообщения:

В конфиге /etc/postfix/main.cf задайте следующие параметры:

#время, через которое проверяется очередь postfix'ом, по умолчанию 300s
queue_run_delay = 5m

#время, на которое откладывается письмо в случае ошибки, но не позже следующего параметра, по умолчанию 300s
minimal_backoff_time = 10m

#по умолчанию 4000s
maximal_backoff_time = 15m

#максимальное время хранения неотправленного письма, по умолчанию 5d
maximal_queue_lifetime = 1d
bounce_queue_lifetime = 1d

Перезагрузите postfix:

sudo postfix reload

7. Как ограничить время отправки сообщения в postfix:

В конфиге /etc/postfix/main.cf задайте следующие параметры:

#ограничение времени, затрачиваемого для отправки сообщения, по умолчанию 600s
smtp_data_done_timeout = 600s

#ограничение времени, затрачиваемого на отправку команды SMTP DATA с получением ответа от удалённого сервера, по умолчанию 120s
smtp_data_init_timeout = 120s

#ограничение времени на отправку тела письма, по умолчанию 180s
smtp_data_xfer_timeout = 180s

Перезагрузите postfix:

sudo postfix reload
 38   1 мес  

How to fix several compatibility issues with new MySQL versions

Let’s see how to solve some problems after updating MySQL server. All ones fixes in one place by one method, so I show detailed only first solve and next will be more short.

... which is not functionally dependent on columns in GROUP BY clause;
this is incompatible with sql_mode=only_full_group_by

Find path of mysqld program:

which mysqld

Find file which you can use for change mysqld options. Change mysqld path in command if you need.

sudo /usr/sbin/mysqld --verbose --help | grep -A 1 "Default options"

Remember printed filenames. Actually remember anyone filename e. g. “/etc/mysql/my.cnf”.

Next you need to know active sql modes:

sudo mysql -u root -p -e "select @@sql_mode"

Copy printed constants to somewhere. Drop “ONLY_FULL_GROUP_BY” constant from list. Next go to open filename of mysqld options file:

sudo mcedit /etc/mysql/my.cnf

And after “!includedir” put “[mysqld]” section (if it missing) and write into [mysqld] section:

sql_mode = "YOUR_NEW_CONSTANTS_LIST"

Save file and restart mysql server:

sudo service mysql restart

Incorrect integer value

Open options file like above and from constants list drop “STRICT_TRANS_TABLES” constant. Save file and restart mysql server.

Incorrect datetime value: ‘0000-00-00 00:00:00’

Again, open options file and now drop constant “NO_ZERO_DATE” from list. Save file and restart mysql server.

 198   9 мес  

How to protect from flood on nginx server

Add next lines into “http” section of nginx.conf file:

limit_conn_zone $binary_remote_addr zone=conn_limit_per_ip:10m;
limit_req_zone $binary_remote_addr zone=req_limit_per_ip:10m rate=5r/s;

And add next lines into “server” section (wherever it placed):

limit_conn conn_limit_per_ip 10;
limit_req zone=req_limit_per_ip burst=10 nodelay;

Change values as you need, according to documenation:
http://nginx.org/en/docs/http/ngx_http_limit_req_module.html

 175   10 мес  

Setting up Linux firewall for make more safe web server

Server works in network with opened every ports — sounds not very safety. Let’s protect it by using netfilter firewall which delivered together with Linux. Netfilter, in turn, managed by iptables utility. So, iptables is what we need.

Warning: if you don’t have physical access to real server (working with it remotely e. g. by SSH) first you need find anybody (e. g. supportguy from hosting provider) who could restore SSH access to you or do something else if you accidentally, suddenly or not specially will block yourself.

So, iptables. I should warn you iptables doesn’t save self settings and after server restart, iptables settings will restored to default. Therefore it is possible to use iptables-persistent program which can save iptables settings when you ask about it. Let’s install it:

sudo apt install iptables-persistent

Setup program will ask you would you want to save present rules. Why not? Of course you want to save present rules because you will change it in next steps. Later on you can save your rules anytime by using next command:

sudo dpkg-reconfigure iptables-persistent

The next what you should know that netfilter will process rules in reverse order. If you block packet in some rule it won’t go to rule above last.

Every iptables command neccessary enter into the console. I added comments to every command for you know what it do.

sudo -i # Working with root privileges
iptables -L -n # Will show list of current rules in system
iptables -F # Will delete all current rules 
iptables -A INPUT -p tcp --tcp-flags ALL NONE -j DROP # Will block null-packets
iptables -A INPUT -p tcp --tcp-flags ALL ALL -j DROP # Will drop XMAS packets
iptables -A INPUT -p tcp ! --syn -m state --state NEW -j DROP # Will protect from Syn-flood
iptables -A INPUT -p tcp -m tcp --dport 22 -j ACCEPT # Don't forget allow access to SSH oneself (change 22 port if your SSH working on another port)
iptables -A INPUT -i lo -j ACCEPT # Will allow access to local interface. It is neccessary for database, mail, etc.
iptables -A INPUT -p tcp -m tcp --dport 80 -j ACCEPT # Will allow 80 port for access to sites by HTTP
iptables -A INPUT -p tcp -m tcp --dport 443 -j ACCEPT # It will allow 443 port for access to sites by HTTPS (if you have)
iptables -A INPUT -p icmp --icmp-type echo-request -j ACCEPT # Allow ping our server (if you block it external services will think that our server doesn't work)
iptables -I INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT # It is neccessary for server updates
iptables -P OUTPUT ACCEPT # Allow output traffic from our server
iptables -P INPUT DROP # It will close left ports
iptables -L -n # Check set rules

Now you need to exit from ssh connection and relogin it again. Check it. It’s ok? Now check how work sites on 80 or 443 port. Also check disallowed ports e. g. 2525:

telnet your_server_IP 2525

Warning: if you got “Connected” it is mean something wrong.

If all is good save your rules:

sudo dpkg-reconfigure iptables-persistent
 234   11 мес  

How to set up Let’s Encrypt on Ubuntu

At first, it is necessary to add the certbot repository and to install the letsencrypt packet from one:

sudo apt install software-properties-common
sudo add-apt-repository ppa:certbot/certbot
sudo apt update
sudo apt upgrade
sudo apt install letsencrypt

It is possible to edit command which will be excecuted every time after certificates had updated. Here:

sudo mcedit /etc/letsencrypt/cli.ini

Change post-hook to that you need:

...
post-hook = service nginx reload
...

To the next, you need to register Let’s Encrypt account:

certbot register --email your@email.com

Create .well-known directory in public root directory of your website. Let’s Encrypt will save temporary necessary data in directory stated above:

mkdir -p /var/www/your_web_site/public/.well-known

Check whether this catalog works:

echo '1234' > /var/www/your_web_site.localhost/public/.well-known/test.txt

Open http ://your_web_site.localhost/.well-known/test.txt in your browser, then:

rm /var/www/your_web_site.localhost/public/.well-known/test.txt

Directory .well-known has to be clean before you goint to the next step!

So, let’s try to create temporary SSL cerificate for testing.

letsencrypt certonly --dry-run -d your_web_site.localhost -d www.your_web_site.localhost

When it asks “webroot”, enter:

/var/www/your_web_site.localhost/

If check is complete well then create the really SSL certificate:

letsencrypt certonly -d your_web_site.localhost -d www.your_web_site.localhost

Check whether new SSL cerificate really works:

openssl x509 -text -in /etc/letsencrypt/live/your_web_site.localhost/cert.pem

Now, it necessary to configuring web server.

Configuring webserver

nginx

Add next lines to your host configuration section:

ssl_certificate /etc/letsencrypt/live/your_web_site.localhost/fullchain.pem;
ssl_certificate_key /etc/letsencrypt/live/your_web_site.localhost/privkey.pem;
ssl_trusted_certificate /etc/letsencrypt/live/your_web_site.localhost/chain.pem;
ssl_ciphers EECDH:+AES256:-3DES:RSA+AES:RSA+3DES:!NULL:!RC4;

For fix OCSP stapling use next command:

tee /etc/nginx/conf.d/ssl_stapling.conf <<EOF
resolver 127.0.0.1;
ssl_stapling on;
ssl_stapling_verify on;
EOF

BUT If you don’t have a local DNS cache server then use this:

nameserver=$(grep nameserver /etc/resolv.conf | head -1 | cut -f2 -d" ")
sed -i s/127.0.0.1/$nameserver/ /etc/nginx/conf.d/ssl_stapling.conf
grep resolver /etc/nginx/conf.d/ssl_stapling.conf

Apache2

Example of host:

# Notice port of SSL host
<VirtualHost *:443>

    # <!-- SSL certificate
    SSLEngine on
    SSLCertificateFile /etc/letsencrypt/live/your_web_site.localhost/fullchain.pem
    SSLCertificateKeyFile /etc/letsencrypt/live/your_web_site.localhost/privkey.pem
    SSLCertificateChainFile /etc/letsencrypt/live/your_web_site.localhost/chain.pem
    # -->

    ServerAdmin admin@localhost
    ServerName your_web_site.localhost
    DocumentRoot /var/www/your_web_site.localhost/
    <Directory /var/www/your_web_site.localhost/>
        Options Indexes FollowSymLinks
        php_admin_value open_basedir /var/www/your_web_site.localhost/
        AllowOverride All
        Require all granted
    </Directory>
    ErrorLog /var/www/logs/your_web_site.localhost-error.log
    LogLevel warn
    CustomLog /var/www/logs/your_web_site.localhost-access.log combined
</VirtualHost>

Finally, check how Let’s Encrypt updates cerificates:

certbot -q renew
 133   1 год  

How to set up server monitoring with the Munin project

If you want to have monitoring by the Munin project, you have to install munin server on one server and munin node per every server which you want to have monitoring. For example:

First server have munin server and munin node together;
Second server have only munin-node;
Third server have only munin-node too, etc.

Munin server will collect states from nodes every some minutes and show them in useful web interface as graphics.
Munin nodes only collects states of servers which they installed.

Installing

If you want to install munin server:

sudo apt install munin

Or if you want to install munin-node:

sudo apt install munin-node

Common commands which you have to enter anyway:

sudo apt install munin-plugins-extra

And:

sudo apt isntall libwww-perl

Or if you got fail above:

sudo apt install libio-all-lwp-perl

Configuring munin nodes

For the next step you need to allow access for munin server to munin nodes. Open munin node configure file on every monitoring server:

sudo mcedit /etc/munin/munin-node.conf

And find this line:

allow ^127\.0\.0\.1$

Append munin server IP address in new line. It need only for nodes, installed on other servers where munin server didn’t install.

Check the node port:

...
# And which port
port 4949

You can change port and/or open in the server firewall if blocked one.

For apply changes restart node:

sudo service munin-node restart

Configuring munin server

Now, you need to open munin server configuration file:

mcedit /etc/munin/munin.conf

And append all nodes including local node in this file. For example:

[ratchet]
    address 127.0.0.1
    use_node_name yes
    port 4949

[serverN]
    address 255.255.255.255
    use_node_name yes
    port 4949

[serverN+1]
    address 255.255.255.255
    use_node_name yes
    port 4949
...

Perhaps you already have local node in this file, so check what you didn’t add several local nodes.

Configuring web interface

Apache2

If you use Apache as a front web server, then:

sudo apt install apache2-utils libapache2-mod-fcgid libcgi-fast-perl
sudo a2enmod rewrite
sudo a2enmod fcgid
sudo service apache2 reload

And create admin account for web interface:

htpasswd -c /etc/munin/munin-htpasswd admin

You can change username “admin” to what you want.

After this steps open for edit munin host configuration file:

mcedit /etc/apache2/conf-enabled/munin.conf

You can use my typical configuration:

# Enable this for template generation
Alias /munin /var/cache/munin/www

# Enable this for cgi-based templates
#Alias /munin-cgi/static /var/cache/munin/www/static
#ScriptAlias /munin-cgi /usr/lib/munin/cgi/munin-cgi-html
#<Location /munin-cgi>
#       Order allow,deny
#       Allow from localhost 127.0.0.0/8 ::1
#       AuthUserFile /etc/munin/munin-htpasswd
#       AuthName "Munin"
#       AuthType Basic
#       require valid-user
#</Location>

<Directory /var/cache/munin/www>
        Order allow,deny
        Allow from all #localhost 127.0.0.0/8 ::1
        Options None

        # This file can be used as a .htaccess file, or a part of your apache
        # config file.
        #
        # For the .htaccess file option to work the munin www directory
        # (/var/cache/munin/www) must have "AllowOverride all" or something 
        # close to that set.
        #

        AuthUserFile /etc/munin/munin-htpasswd
        AuthName "Munin"
        AuthType Basic
        require valid-user

        # This next part requires mod_expires to be enabled.
        #

        # Set the default expiration time for files to 5 minutes 10 seconds from
        # their creation (modification) time.  There are probably new files by
        # that time. 
        #

    <IfModule mod_expires.c>
        ExpiresActive On
        ExpiresDefault M310
    </IfModule>

</Directory> 

# Enables fastcgi for munin-cgi-html if present
#<Location /munin-cgi>
#    <IfModule mod_fastcgi.c>
#        SetHandler fastcgi-script
#    </IfModule>
#</Location>

#<Location /munin-cgi/static>
#       SetHandler None
#</Location>

# Enables fastcgi for munin-cgi-graph if present
ScriptAlias /munin-cgi/munin-cgi-graph /usr/lib/munin/cgi/munin-cgi-graph
<Location /munin-cgi/munin-cgi-graph>
        Order allow,deny
        Allow from all #localhost 127.0.0.0/8 ::1
        AuthUserFile /etc/munin/munin-htpasswd
        AuthName "Munin"
        AuthType Basic
        require valid-user
        <IfModule mod_fcgid.c>
            SetHandler fcgid-script
        </IfModule>
        <IfModule !mod_fcgid.c>
            SetHandler cgi-script
        </IfModule>
</Location>

ScriptAlias /munin-cgi/munin-cgi-html /usr/lib/munin/cgi/munin-cgi-html
<Location /munin-cgi/munin-cgi-html>
        Order allow,deny
        Allow from all #localhost 127.0.0.0/8 ::1
        AuthUserFile /etc/munin/munin-htpasswd
        AuthName "Munin"
        AuthType Basic
        require valid-user
        <IfModule mod_fcgid.c>
            SetHandler fcgid-script
        </IfModule>
        <IfModule !mod_fcgid.c>
            SetHandler cgi-script
        </IfModule>
</Location>

Reload web server:

sudo service apache2 reload

nginx

If you using nginx web server, then open config file:

sudo mcedit /etc/nginx/sites-available/localhost.conf

And add next lines:

location ^~ /munin {
    alias /var/cache/munin/www;
    auth_basic "Admin Zone";
    auth_basic_user_file /etc/munin/munin-htpasswd;
}

Reload web server:

sudo service nginx reload

Plugins

Apache monitoring

For activate Apache monitoring enter this on node servers:

ln -s /usr/share/munin/plugins/apache_accesses /etc/munin/plugins/apache_accesses
ln -s /usr/share/munin/plugins/apache_processes /etc/munin/plugins/apache_processes
ln -s /usr/share/munin/plugins/apache_volume /etc/munin/plugins/apache_volume
mcedit /etc/munin/plugin-conf.d/munin-node

Find/add apache configuration:

[apache_*]
env.url http://127.0.0.1:%d/server-status?auto
env.ports 80

Reload node:

sudo service munin-node restart

Check what it works:

sudo apt install lynx
lynx http://127.0.0.1:8070/server-status?auto
munin-node-configure --suggest | grep apache

It have to be only “yes | yes”.

nginx monitoring

ln -s /usr/share/munin/plugins/nginx_request /etc/munin/plugins/nginx_request
ln -s /usr/share/munin/plugins/nginx_status /etc/munin/plugins/nginx_status
mcedit /etc/munin/plugin-conf.d/munin-node

Find/add next lines:

[nginx*] 
env.url http://localhost/nginx-status

Reload node:

sudo service munin-node restart

And check what it works:

sudo apt install lynx
lynx http://127.0.0.1/nginx-status
munin-node-configure --suggest | grep nginx

MySQL monitoring

ln -s /usr/share/munin/plugins/mysql_innodb /etc/munin/plugins/mysql_innodb
ln -s /usr/share/munin/plugins/mysql_queries /etc/munin/plugins/mysql_queries
ln -s /usr/share/munin/plugins/mysql_slowqueries /etc/munin/plugins/mysql_slowqueries
ln -s /usr/share/munin/plugins/mysql_threads /etc/munin/plugins/mysql_threads
ln -s /usr/share/munin/plugins/mysql_ /etc/munin/plugins/mysql_
ln -s /usr/share/munin/plugins/mysql_bytes /etc/munin/plugins/mysql_bytes
sudo apt install libdbi-perl libdbd-mysql-perl libcache-cache-perl
sudo service munin-node restart
munin-node-configure --suggest | grep mysql
 150   1 год  
 114   1 год  
 208   1 год  

How to set the MySQL root password in the Debian 9 (or MySQL 5.7+, MariaDB 10.1+)

You can think that there is no problem to set the root password in installation time. It’s true until 5.7 version of MySQL (or MariaDB 10.1). Now you can be authorized under root (and without password), but only through the auth_socket. For example, authorization under root in mysql-client:

mysql -u root

It means that the phpMyAdmin or other scripts will not be able to use root access more.

Now the next methods do not work:

mysqladmin -u root password
SET PASSWORD FOR root@localhost=PASSWORD, UPDATE user SET Password=PASSWORD

What can we make to use root privileges as earlier? We can create new user with root privileges and use it. Let’s do that.

First step:

mysql -u root

Create new a user with root privileges:

CREATE USER 'root2'@'localhost' IDENTIFIED BY 'PASSWORD';
GRANT ALL PRIVILEGES ON *.* TO 'root2'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Where root2 and PASSWORD change to what you want.

Therefore the following command can be useful to you for giving privileges to users through mysql-client:

GRANT ALL PRIVILEGES ON `DB_NAME`.* TO 'DB_USER'@'localhost';

Where DB_NAME and DB_USER change to what you want again.

 102   1 год  
Ранее Ctrl + ↓