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:


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.

8 mon  

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:

9 mon  

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
9 mon  

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

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:


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


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
ssl_stapling on;
ssl_stapling_verify on;

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/$nameserver/ /etc/nginx/conf.d/ssl_stapling.conf
grep resolver /etc/nginx/conf.d/ssl_stapling.conf


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
    ErrorLog /var/www/logs/your_web_site.localhost-error.log
    LogLevel warn
    CustomLog /var/www/logs/your_web_site.localhost-access.log combined

Finally, check how Let’s Encrypt updates cerificates:

certbot -q renew
11 mon  

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.


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


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:

    use_node_name yes
    port 4949

    use_node_name yes
    port 4949

    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


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 ::1
#       AuthUserFile /etc/munin/munin-htpasswd
#       AuthName "Munin"
#       AuthType Basic
#       require valid-user

<Directory /var/cache/munin/www>
        Order allow,deny
        Allow from all #localhost ::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


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

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

# 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 ::1
        AuthUserFile /etc/munin/munin-htpasswd
        AuthName "Munin"
        AuthType Basic
        require valid-user
        <IfModule mod_fcgid.c>
            SetHandler fcgid-script
        <IfModule !mod_fcgid.c>
            SetHandler cgi-script

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 ::1
        AuthUserFile /etc/munin/munin-htpasswd
        AuthName "Munin"
        AuthType Basic
        require valid-user
        <IfModule mod_fcgid.c>
            SetHandler fcgid-script
        <IfModule !mod_fcgid.c>
            SetHandler cgi-script

Reload web server:

sudo service apache2 reload


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


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:

env.ports 80

Reload node:

sudo service munin-node restart

Check what it works:

sudo apt install lynx
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:

env.url http://localhost/nginx-status

Reload node:

sudo service munin-node restart

And check what it works:

sudo apt install lynx
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
11 mon  
11 mon  
11 mon  

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

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:

GRANT ALL PRIVILEGES ON *.* TO 'root2'@'localhost';

Where root2 and PASSWORD change to what you want.

Unfortunately, the user created by such method will not be able to give privileges to other users through the same phpMyAdmin. Therefore the following command can be useful to you for giving privileges to users through mysql-client:


Where DB_NAME and DB_USER change to what you want again.

11 mon  

How to import big MySQL dump quickly

Warning: This trick is only for InnoDB storage engine.

Open MySQL config file “/etc/mysql/my.cnf”:

mcedit /etc/mysql/my.cnf

Write into [mysqld] section:

innodb_flush_log_at_trx_commit = 2

What does that mean? Now, data will be recorded not more often than once per second! It will very unload file system and increase speed of import.

But you need check that you haven’t this option:

general_log = 1

Because everything will be worthless with that option! Every SQL query will be written on the disk and import will be worked slowly.

You need to disable “slow_query_log” if it enabled too.

Well, if you don’t have that options or you have deactivated it, for the next step you need to restart the database server:

service mysql restart

I have to notice you, if your dump archived in gzip, you need to unpack it and import only raw dump! Because unpacking on the fly and importing at the same time works slowly.

So, let’s import:

mysql -u username -p db_name < dump.sql

After all don’t forget to delete or restore “innodb_flush_log_at_trx_commit” option from config “/etc/mysql/my.cnf” and reload mysql server!

Sources: ruhighload and

11 mon  

Fix issue with “Insert the picture” button in the Summernote

Summernote deactivates “Insert the picture” button when you use the context menu by mouse to insert the link.

You can use next code to fix this issue:

$('body').on('paste', '.note-image-url', function(e) {

    return false;
11 mon  
Earlier Ctrl + ↓