Using xtrabackup to make fast MySQL backups – backup and restore

Percona provides a really interesting tool for MySQL backups! It works on a live running MySQL server by copying the MySQL binary files from the data directory and because the tool knows how the engines work (InnoDB, MyISAM, and so on) it can make point-in-time consistent MySQL data files. Of course, using this tool on a database with InnoDB tables only is the best case, because no write lock will be used.

There are two main tasks when making a backup and one, which is not mandatory:

  1. Execute xtrabackup with –backup option to copy the MySQL data files and additional information for the tool
  2. Execute xtrabackup with –prepare option to prepare the MySQL (InnoDB) files ready for use of a MySQL server. The files are consistented, i.e. the whole backup is consistent despite the copy of the different files that happened at different times.
  3. Execute xtrabackup with –prepare option again to further prepare the MySQL (InnoDB) files ready for use of a MySQL server. Additional preparation such as InnoDB log files and more. This step is not mandatory and it may be skipped because the MySQL server, which uses the data files, will create the InnoDB log files.

The directory with MySQL copied files contains not only the MySQL files but additional information plus my.cnf (MySQL current configuration) backup. The backup files may be prepared (and restore) in a different server than the original, on which the backup was made.

xtrabackup may enable compressing on-the-fly when copying the binary files with the option –compress but the steps to use (restore) the backup in a new server are different – additional step to decompress before prepare. So the above steps become:

  1. xtrabackup –backup –compress – copy and compress on-the-fly.
  2. xtrabackup –decompress – decompress the backup files
  3. xtrabackup –prepare – make point-in-time consistent MySQL data files, which are ready for a MySQL server.
  4. xtrabackup –prepare – additional preparation to make the start up of the MySQL server with those files faster.

And here is a real-world example:

STEP 1) Install the xtrabackup utility

yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm
yum install -y percona-xtrabackup-24 qpress

The percona-xtrabackup-24 provides the percona xtrabackup tool for backup of MySQL 5.1, 5.5, 5.6 and 5.7 servers, as well as Percona Server for MySQL with XtraDB. Install percona-xtrabackup-80 for use with MySQL 8 and later.
The whole output is included in the Bonus 3 section below.

STEP 2) Make backup

Make the backup. The datadir of the MySQL server is needed and a new direcotry for the backup files. The MySQL server is running and serving requests.

[root@srv ~]# xtrabackup --backup --slave-info --datadir=/var/lib/mysql/ --target-dir=/mnt/backups/sql-xtrabackup/
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql --log_bin --server-id=1 --open_files_limit=5000 --innodb_buffer_pool_size=256M --innodb_log_buffer_size=32M --innodb_log_files_in_group=2 --innodb_flush_log_at_trx_commit=0 --innodb_file_per_table=1 --innodb_flush_method=O_DIRECT --datadir=/var/lib/mysql/ 
xtrabackup: recognized client arguments: --password=* --backup=1 --slave-info=1 --target-dir=/mnt/backups/sql-xtrabackup/ 
200919 14:35:11  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' (using password: YES).
200919 14:35:11  version_check Connected to MySQL server
200919 14:35:11  version_check Executing a version check against the server...
200919 14:35:11  version_check Done.
200919 14:35:11 Connecting to MySQL server host: localhost, user: not set, password: set, port: not set, socket: not set
Using server version 5.7.31-log
xtrabackup version 2.4.20 based on MySQL server 5.7.26 Linux (x86_64) (revision id: c8b4056)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql/
xtrabackup: open files limit requested 5000, set to 5000
xtrabackup: using the following InnoDB configuration:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 50331648
xtrabackup: using O_DIRECT
InnoDB: Number of pools: 1
200919 14:35:11 >> log scanned up to (1135932912)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 34 for mywordpress/wp_users, old maximum was 0
200919 14:35:12 [01] Copying ./ibdata1 to /mnt/backups/sql-xtrabackup/ibdata1
200919 14:35:12 >> log scanned up to (1135932912)
200919 14:35:13 >> log scanned up to (1135932912)
200919 14:35:14 [01]        ...done
200919 14:35:14 >> log scanned up to (1135932912)
200919 14:35:15 >> log scanned up to (1135932912)
200919 14:35:16 [01] Copying ./mywordpress/wp_users.ibd to /mnt/backups/sql-xtrabackup/mywordpress/wp_users.ibd
200919 14:35:16 [01]        ...done
200919 14:35:16 [01] Copying ./mywordpress/wp_gglcptch_whitelist.ibd to /mnt/backups/sql-xtrabackup/mywordpress/wp_gglcptch_whitelist.ibd
200919 14:35:16 [01]        ...done
200919 14:35:16 [01] Copying ./mywordpress/wp_usermeta.ibd to /mnt/backups/sql-xtrabackup/mywordpress/wp_usermeta.ibd
200919 14:35:16 [01]        ...done
200919 14:35:16 >> log scanned up to (1135932912)
200919 14:35:17 [01] Copying ./mywordpress/wp_postmeta.ibd to /mnt/backups/sql-xtrabackup/mywordpress/wp_postmeta.ibd
200919 14:35:17 [01]        ...done
200919 14:35:17 [01] Copying ./mywordpress/wp_posts.ibd to /mnt/backups/sql-xtrabackup/mywordpress/wp_posts.ibd
200919 14:35:17 >> log scanned up to (1135932912)
200919 14:35:18 >> log scanned up to (1135932912)
200919 14:35:19 [01]        ...done
200919 14:35:19 >> log scanned up to (1135932912)
.....
.....
200919 14:36:30 [01] Copying ./mysql/columns_priv.MYD to /mnt/backups/sql-xtrabackup/mysql/columns_priv.MYD
200919 14:36:30 [01]        ...done
200919 14:36:30 >> log scanned up to (1135933567)
200919 14:36:31 [01] Copying ./mysql/proxies_priv.frm to /mnt/backups/sql-xtrabackup/mysql/proxies_priv.frm
200919 14:36:31 [01]        ...done
200919 14:36:31 [01] Copying ./mysql/help_category.frm to /mnt/backups/sql-xtrabackup/mysql/help_category.frm
200919 14:36:31 [01]        ...done
200919 14:36:31 [01] Copying ./mysql/proc.frm to /mnt/backups/sql-xtrabackup/mysql/proc.frm
200919 14:36:31 [01]        ...done
200919 14:36:31 Finished backing up non-InnoDB tables and files
200919 14:36:31 [00] Writing /mnt/backups/sql-xtrabackup/xtrabackup_slave_info
200919 14:36:31 [00]        ...done
200919 14:36:31 [00] Writing /mnt/backups/sql-xtrabackup/xtrabackup_binlog_info
200919 14:36:31 [00]        ...done
200919 14:36:31 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '1135933558'
xtrabackup: Stopping log copying thread.
.200919 14:36:31 >> log scanned up to (1135933567)

200919 14:36:32 Executing UNLOCK TABLES
200919 14:36:32 All tables unlocked
200919 14:36:32 [00] Copying ib_buffer_pool to /mnt/backups/sql-xtrabackup/ib_buffer_pool
200919 14:36:32 [00]        ...done
200919 14:36:32 Backup created in directory '/mnt/backups/sql-xtrabackup/'
MySQL binlog position: filename 'srv-bin.000001', position '1004'
200919 14:36:32 [00] Writing /mnt/backups/sql-xtrabackup/backup-my.cnf
200919 14:36:32 [00]        ...done
200919 14:36:32 [00] Writing /mnt/backups/sql-xtrabackup/xtrabackup_info
200919 14:36:32 [00]        ...done
xtrabackup: Transaction log of lsn (1135932903) to (1135933567) was copied.
200919 14:36:33 completed OK!

In the end, there must be written: “completed OK!”.
The whole output of the command is included in the Bonus 1 section below. An example with compress option enabled is in Bonus 2 section below.
Here is what contains the backup diectory:
Keep on reading!

gitlab in podman cannot create unix sockets in glusterfs because of SELinux

Installing gitlab-ee (and gitlab-ce) under CentOS 7 with enabled SELinux (i.e. enforcing mode) looped endlessly the container in restarting the installation process! There were multiple errors for missing sockets in the podman logs of the gitlab container. Here are some of the errors:
Missing postgresql unix socket in “/var/opt/gitlab/postgresql”:

Recipe: gitlab::database_migrations
  * bash[migrate gitlab-rails database] action run
    [execute] rake aborted!
              PG::ConnectionBad: could not connect to server: No such file or directory
                Is the server running locally and accepting
                connections on Unix domain socket "/var/opt/gitlab/postgresql/.s.PGSQL.5432"?
              /opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/db.rake:53:in `block (3 levels) in <top (required)>'
              /opt/gitlab/embedded/bin/bundle:23:in `load'
              /opt/gitlab/embedded/bin/bundle:23:in `<main>'
              Tasks: TOP => gitlab:db:configure
              (See full trace by running task with --trace)
    
    
    Error executing action `run` on resource 'bash[migrate gitlab-rails database]'
.....
.....
Running handlers:
There was an error running gitlab-ctl reconfigure:

bash[migrate gitlab-rails database] (gitlab::database_migrations line 55) had an error: Mixlib::ShellOut::ShellCommandFailed: Expected process to exit with [0], but received '1'
---- Begin output of "bash"  "/tmp/chef-script20200915-35-lemic5" ----
STDOUT: rake aborted!
PG::ConnectionBad: could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/var/opt/gitlab/postgresql/.s.PGSQL.5432"?
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/db.rake:53:in `block (3 levels) in <top (required)>'
/opt/gitlab/embedded/bin/bundle:23:in `load'
/opt/gitlab/embedded/bin/bundle:23:in `<main>'
Tasks: TOP => gitlab:db:configure
(See full trace by running task with --trace)
STDERR: 
---- End output of "bash"  "/tmp/chef-script20200915-35-lemic5" ----
Ran "bash"  "/tmp/chef-script20200915-35-lemic5" returned 1

Missing redis socket in

Running handlers:
There was an error running gitlab-ctl reconfigure:

redis_service[redis] (redis::enable line 19) had an error: RuntimeError: ruby_block[warn pending redis restart] (/opt/gitlab/embedded/cookbooks/cache/cookbooks/redis/resources/service.rb line 65) had an error: RuntimeError: Execution of the command `/opt/gitlab/embedded/bin/redis-cli -s /var/opt/gitlab/redis/redis.socket INFO` failed with a non-zero exit code (1)
stdout: 
stderr: Could not connect to Redis at /var/opt/gitlab/redis/redis.socket: No such file or directory

It should be noted that the /var/opt/gitlab directory has been mapped in /mnt/storage/podman/gitlab/data. GlusterFS is used for /mnt/storage, so the gitlab files resides on a GlusterFS volume.

ERROR 1) Cannot create unix socket.

Checking the /var/log/audit/audit.log reveiled the problem immediately:
Keep on reading!

syslog – UDP local to syslog-ng and send remote. Forward syslog to remote server.

After writing an article for the rsyslog daemon about forwarding local UDP logging to a remote server using TCP – UDP local to rsyslog and send remote with TCP and compression this time going to use syslog-ng daemon for those who use it as default in their Linux distribution.
As mentioned in the previous article always use a non-blocking way of writing logs using UDP locally and then transfer (forward) the logs to the centralized log server(s). The example here transfers the web server’s access logs to a remote server. The web server is an Nginx web server.
The goal is to use

  • UDP for the client program (Nginx in the case) for non-blocking log writes.
  • TCP between our local machine and the remote syslog server – to be sure not to lose messages on bad connectivity.
  • local caching for our client machine – not to lose messages if the remote syslog is temporary unreachable.

The configuration and the commands are tested on CentOS 7, CentOS 8, Gentoo and Ubuntu 18 LTS. Check out UDP remote logging here – nginx remote logging to UDP rsyslog server (CentOS 7) to see how to build the server-side part – the syslog server accepting the syslog messages and writing them into files.

STEP 1) Listen for local UDP connections

Configuration file /etc/syslog-ng/syslog-ng.conf

source udp_local {
    network(ip(127.0.0.1) port(514) transport("udp") so_rcvbuf(67108864) log_fetch_limit(1000) max-connections(1000) log-iw-size(1000000));
};

Keep on reading!

storcli with multiple disks from different enclosures

Creating a Virtual device with the AVAGO storcli command-line tool under Linux. Two examples are included:

  1. All disks are from one of the enclosure. All disks are included explicitly.
  2. Disks from two enclosures are included. One controller with two enclosures.

Check out how to Install the new storcli to manage (LSI/AVAGO/Broadcom) MegaRAID controller under CentOS 7
There are 31 disks of 36 harddisk bays. 5 are missing on purpose for the examples.

The initial states of the controller and the disks.

livecd ~ # opt/MegaRAID/storcli/storcli /c0 show
Generating detailed summary of the adapter, it may take a while to complete.

CLI Version = 007.0510.0000.0000 May 4, 2018
Operating system = Linux 4.19.72-gentoo
Controller = 0
Status = Success
Description = None

Product Name = LSI 2108 MegaRAID
Serial Number = FW-ABQRCBEAARBWA
SAS Address =  5003048004015f00
PCI Address = 00:06:00:00
System Time = 07/20/2020 22:58:35
Mfg. Date = 00/00/00
Controller Time = 07/20/2020 22:58:36
FW Package Build = 12.15.0-0239
FW Version = 2.130.403-4660
BIOS Version = 3.30.02.2_4.16.08.00_0x06060A05
Driver Name = megaraid_sas
Driver Version = 07.706.03.00-rc1
Vendor Id = 0x1000
Device Id = 0x79
SubVendor Id = 0x15D9
SubDevice Id = 0x700
Host Interface = PCI-E
Device Interface = SAS-6G
Bus Number = 6
Device Number = 0
Function Number = 0
Physical Drives = 31

PD LIST :
=======

-----------------------------------------------------------------------------------
EID:Slt DID State DG     Size Intf Med SED PI SeSz Model                   Sp Type 
-----------------------------------------------------------------------------------
12:0      0 UGood -  1.817 TB SATA HDD N   N  512B Hitachi HUA723020ALA640 D  -    
12:1      1 UGood -  1.817 TB SATA HDD N   N  512B HGST HUS724020ALA640    D  -    
12:2      2 UGood -  1.817 TB SATA HDD N   N  512B Hitachi HDS723020BLA642 D  -    
12:3      3 UGood -  1.817 TB SATA HDD N   N  512B Hitachi HDS723020BLA642 D  -    
12:4      4 UGood -  1.817 TB SATA HDD N   N  512B Hitachi HDS723020BLA642 D  -    
12:5      5 UGood -  1.817 TB SATA HDD N   N  512B HGST HUS722T2TALA604    D  -    
12:6      6 UGood -  1.817 TB SATA HDD N   N  512B TOSHIBA DT01ACA200      D  -    
12:7      7 UGood -  1.817 TB SATA HDD N   N  512B Hitachi HDS723020BLA642 D  -    
12:8      8 UGood -  1.817 TB SATA HDD N   N  512B Hitachi HDS723020BLA642 D  -    
12:9      9 UGood -  1.817 TB SATA HDD N   N  512B Hitachi HDS723020BLA642 D  -    
12:10    10 UGood -  1.817 TB SATA HDD N   N  512B Hitachi HDS723020BLA642 D  -    
12:11    11 UGood -  1.817 TB SATA HDD N   N  512B Hitachi HDS723020BLA642 D  -    
37:0     13 UGood -  1.817 TB SATA HDD N   N  512B Hitachi HDS723020BLA642 U  -    
37:1     14 UGood -  1.817 TB SATA HDD N   N  512B Hitachi HDS723020BLA642 U  -    
37:2     15 UGood -  1.817 TB SATA HDD N   N  512B Hitachi HDS723020BLA642 U  -    
37:3     16 UGood -  1.817 TB SATA HDD N   N  512B Hitachi HUA723020ALA640 U  -    
37:4     17 UGood -  1.817 TB SATA HDD N   N  512B Hitachi HDS723020BLA642 U  -    
37:6     19 UGood -  1.817 TB SATA HDD N   N  512B Hitachi HDS723020BLA642 U  -    
37:7     20 UGood -  1.817 TB SATA HDD N   N  512B HGST HUS722T2TALA604    U  -    
37:8     21 UGood -  1.817 TB SATA HDD N   N  512B HGST HUS724020ALA640    U  -    
37:10    23 UGood -  1.817 TB SATA HDD N   N  512B Hitachi HDS723020BLA642 U  -    
37:11    24 UGood -  1.817 TB SATA HDD N   N  512B Hitachi HDS723020BLA642 U  -    
37:13    26 UGood -  1.817 TB SATA HDD N   N  512B Hitachi HDS723020BLA642 U  -    
37:14    27 UGood -  1.817 TB SATA HDD N   N  512B HGST HUS724020ALA640    U  -    
37:16    29 UGood -  1.817 TB SATA HDD N   N  512B HGST HUS724020ALA640    U  -    
37:17    30 UGood -  1.817 TB SATA HDD N   N  512B Hitachi HUA723020ALA640 U  -    
37:19    32 UGood -  1.817 TB SATA HDD N   N  512B Hitachi HDS723020BLA642 U  -    
37:20    33 UGood -  1.817 TB SATA HDD N   N  512B Hitachi HDS723020BLA642 U  -    
37:21    34 UGood -  1.817 TB SATA HDD N   N  512B HGST HUS722T2TALA604    U  -    
37:22    35 UGood -  1.817 TB SATA HDD N   N  512B Hitachi HDS723020BLA642 U  -    
37:23    36 UGood -  1.817 TB SATA HDD N   N  512B HGST HUS724020ALA640    U  -    
-----------------------------------------------------------------------------------

EID-Enclosure Device ID|Slt-Slot No.|DID-Device ID|DG-DriveGroup
DHS-Dedicated Hot Spare|UGood-Unconfigured Good|GHS-Global Hotspare
UBad-Unconfigured Bad|Onln-Online|Offln-Offline|Intf-Interface
Med-Media Type|SED-Self Encryptive Drive|PI-Protection Info
SeSz-Sector Size|Sp-Spun|U-Up|D-Down/PowerSave|T-Transition|F-Foreign
UGUnsp-Unsupported|UGShld-UnConfigured shielded|HSPShld-Hotspare shielded
CFShld-Configured shielded|Cpybck-CopyBack|CBShld-Copyback Shielded

Keep on reading!

aptly delete a mirror and remove all files

Executing drop command on a mirror will only remove the meta information for the mirror and it will not remove the package files occupying space on the file system.

Dropping mirror in aptly supposes to execute a clean command with aplty

aptly db cleanup

The newly created Bionic mirrors in the prevoius article on the aptly subject – Mirror the official Ubuntu repositories using aptly will be deleted here and removing all files with:

aptly@srv:~$ aptly mirror drop bionic-main
Mirror `bionic-main` has been removed.
aptly@srv:~$ aptly mirror drop bionic-security-main
Mirror `binonic-security-main` has been removed.
aptly@srv:~$ aptly mirror drop bionic-universe     
Mirror `bionic-universe` has been removed.
aptly@srv:~$ aptly mirror drop bionic-updates-main
Mirror `binonic-updates-main` has been removed.
aptly@srv:~$ aptly mirror drop bionic-updates-universe
Mirror `bionic-updates-universe` has been removed.
aptly@srv:~$ aptly mirror list
No mirrors found, create one with `aptly mirror create ...`.

The occupied space on the disk mounted in /srv is 270G:

aptly@srv:~$ df -h
Filesystem      Size  Used Avail Use% Mounted on
udev            1.9G     0  1.9G   0% /dev
tmpfs           395M  3.5M  391M   1% /run
/dev/sda3        19G  4.6G   13G  27% /
tmpfs           2.0G  204K  2.0G   1% /dev/shm
tmpfs           5.0M     0  5.0M   0% /run/lock
tmpfs           2.0G     0  2.0G   0% /sys/fs/cgroup
/dev/sda4       470G  270G  176G  61% /srv
tmpfs           395M     0  395M   0% /run/user/0
tmpfs           395M     0  395M   0% /run/user/1001

Actually freeing the space on the disk with the clean aptly command:

aptly@srv:~$ aptly db cleanup
Loading mirrors, local repos, snapshots and published repos...
Loading list of all packages...
Deleting unreferenced packages (143121)...
Building list of files referenced by packages...
Building list of files in package pool...
Deleting unreferenced files (194097)...
Disk space freed: 268.80 GiB...
Compacting database...

The occupied space on the disk mounted in /srv is below 2G after the cleaning command:

aptly@srv:~$ df -h
Filesystem      Size  Used Avail Use% Mounted on
udev            1.9G     0  1.9G   0% /dev
tmpfs           395M  3.5M  391M   1% /run
/dev/sda3        19G  4.6G   13G  27% /
tmpfs           2.0G  204K  2.0G   1% /dev/shm
tmpfs           5.0M     0  5.0M   0% /run/lock
tmpfs           2.0G     0  2.0G   0% /sys/fs/cgroup
/dev/sda4       470G    1G  176G   1% /srv
tmpfs           395M     0  395M   0% /run/user/0
tmpfs           395M     0  395M   0% /run/user/1001

edit mysql options in docker (or docker-compose) mysql

Modifying the default options for the docker (podman) MySQL server is essential. The default MySQL options are too conservative and even for simple (automation?) tests the options could be .
For example, modifying only one or two of the default InnoDB configuration options may lead to boosting multiple times faster execution of SQL queries and the related automation tests.

Here are three simple ways to modify the (default or current) MySQL my.cnf configuration options:

  • Command-line arguments. All MySQL configuration options could be overriden by passing them in the command line of mysqld binary. The format is:
    --variable-name=value
    

    and the variable names could be obtained by

    mysqld --verbose --help
    

    and for the live configuration options:

    mysqladmin variables
    
  • Options in a additional configuration file, which will be included in the main configuration. The options in /etc/mysql/conf.d/config-file.cnftake precedence.
  • Replacing the default my.cnf configuration file/etc/mysql/my.cnf.

Check out also the official page – https://hub.docker.com/_/mysql.
Under CentOS 8 docker is replaced by podman and just replace the docker with podman in all of the commands below.

OPTION 1) Command-line arguments.

This is the simplest way of modifying the default my.cnf (the one, which comes with the docker image or this in the current docker image file). It is fast and easy to use and change, just a little bit of much writing in the command-line. As mentioned above all MySQL options could be changed by a command-line argument to the mysqld binary. For example:

mysqld --innodb_buffer_pool_size=1024M

It will start MySQL server with variable innodb_buffer_pool_size set to 1G. Translating it to (for multiple options just add them at the end of the command):

  • docker run

    root@srv ~ # docker run --name my-mysql -v /var/lib/mysql:/var/lib/mysql \
    -e MYSQL_ROOT_PASSWORD=111111 \
    -d mysql:8 \
    --innodb_buffer_pool_size=1024M \
    --innodb_read_io_threads=4 \
    --innodb_flush_log_at_trx_commit=2 \
    --innodb_flush_method=O_DIRECT
    1bb7f415ab03b8bfd76d1cf268454e3c519c52dc383b1eb85024e506f1d04dea
    root@srv ~ # docker exec -it my-mysql mysqladmin -p111111 variables|grep innodb_buffer_pool_size
    | innodb_buffer_pool_size                                  | 1073741824
    
  • docker-compose:

    # Docker MySQL arguments example
    version: '3.1'
    
    services:
    
      db:
        image: mysql:8
        command: --default-authentication-plugin=mysql_native_password --innodb_buffer_pool_size=1024M --innodb_read_io_threads=4 --innodb_flush_log_at_trx_commit=2 --innodb_flush_method=O_DIRECT
        restart: always
        environment:
          MYSQL_ROOT_PASSWORD: 111111
        volumes:
         - /var/lib/mysql_data:/var/lib/mysql
        ports:
          - "3306:3306"
    

    Here is how to run it (the above text file should be named docker-compose.yml and the file should be in the current directory when executing the command below):

    root@srv ~ # docker-compose up
    Creating network "docker-compose-mysql_default" with the default driver
    Creating my-mysql ... done
    Attaching to my-mysql
    my-mysql | 2020-06-16 09:45:35+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.20-1debian10 started.
    my-mysql | 2020-06-16 09:45:35+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
    my-mysql | 2020-06-16 09:45:35+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.20-1debian10 started.
    my-mysql | 2020-06-16T09:45:36.293747Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.
    my-mysql | 2020-06-16T09:45:36.293906Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.20) starting as process 1
    my-mysql | 2020-06-16T09:45:36.307654Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
    my-mysql | 2020-06-16T09:45:36.942424Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
    my-mysql | 2020-06-16T09:45:37.136537Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock' bind-address: '::' port: 33060
    my-mysql | 2020-06-16T09:45:37.279733Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
    my-mysql | 2020-06-16T09:45:37.306693Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/var/run/mysqld' in the path is accessible to all OS users. Consider choosing a different directory.
    my-mysql | 2020-06-16T09:45:37.353358Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.20'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  MySQL Community Server - GPL.
    

    And check the option:

    root@srv ~ # docker exec -it my-mysql mysqladmin -p111111 variables|grep innodb_buffer_pool_size
    | innodb_buffer_pool_size                                  | 1073741824
    

OPTION 2) Options in a additional configuration file.

Create a MySQL option file with name config-file.cnf:

[mysqld]
innodb_buffer_pool_size=1024M
innodb_read_io_threads=4
innodb_flush_log_at_trx_commit=2
innodb_flush_method=O_DIRECT
  1. docker run
  2. The source path must be absolute path!

    docker run --name my-mysql \
    -v /var/lib/mysql_data:/var/lib/mysql \
    -v /etc/mysql/docker-instances/config-file.cnf:/etc/mysql/conf.d/config-file.cnf \
    -e MYSQL_ROOT_PASSWORD=111111 \
    -d mysql:8
    
  3. docker-compose
    The source path may not be absolute path.

    # Docker MySQL arguments example
    version: '3.1'
    
    services:
    
      db:
        container_name: my-mysql
        image: mysql:8
        command: --default-authentication-plugin=mysql_native_password
        restart: always
        environment:
          MYSQL_ROOT_PASSWORD: 111111
        volumes:
         - /var/lib/mysql_data:/var/lib/mysql
         - ./config-file.cnf:/etc/mysql/conf.d/config-file.cnf
        ports:
          - "3306:3306"
    

OPTION 3) Replacing the default my.cnf configuration file.

Add the modified options to a my.cnf template file and map it to the container on /etc/mysql/my.cnf. When overwriting the main MySQL option file – my.cnf you may map the whole /etc/mysql directory (just replace /etc/mysql/my.cnf with /etc/mysql below), too. The source file (or directory) may be any file (or directory) not the /etc/mysql/my.cnf (or /etc/mysql)

  • docker run:
    The source path must be absolute path.

    docker run --name my-mysql \
    -v /var/lib/mysql_data:/var/lib/mysql \
    -v /etc/mysql/my.cnf:/etc/mysql/my.cnf \
    -e MYSQL_ROOT_PASSWORD=111111 \
    --publish 3306:3306 \
    -d mysql:8
    

    Note: here a new option “–publish 3306:3306” is included to show how to map the ports out of the container like all the examples with the docker-compose here.

  • docker-compose:
    The source path may not be absolute path, but the current directory.

    # Use root/example as user/password credentials
    version: '3.1'
    
    services:
    
      db:
        container_name: my-mysql
        image: mysql:8
        command: --default-authentication-plugin=mysql_native_password
        restart: always
        environment:
          MYSQL_ROOT_PASSWORD: 111111
        volumes:
         - /var/lib/mysql_data:/var/lib/mysql
         - ./mysql/my.cnf:/etc/mysql/my.cnf
        ports:
          - "3306:3306"
    

Change the location of container storage in podman (with SELinux enabled)

There two main options to change the location of all the containers’ storages:

  • “mount bind” the new location to the default storage directory (look Note 1)
  • Change the path of the location in the configuration file /etc/containers/storage.conf

You should stop all your containers though it is not mandatory.

You should stop the containers (if any) and copy the directory, because when reconfigured the storage path podman won’t access the ones in the old path – containers and images!

STEP 1) Change the storage path in the podman configuration file.

If the SELinux has been disabled, which should not be done, it is just a matter of changing a path option in the configuration file /etc/containers/storage.conf

# Primary Read/Write location of container storage
graphroot = "/var/lib/containers/storage"

Change it to whatever path you like. Mostly, it should point to the big storage device. In our case, the big storage is mounted under “/mnt/mystorage/virtual/storage”. Change the options to:

# Primary Read/Write location of container storage
graphroot = "/mnt/mystorage/virtual/storage"

Check the running configuration with:

[root@lsrv1 mystorage]# podman info
host:
  BuildahVersion: 1.12.0-dev
  CgroupVersion: v1
  Conmon:
    package: conmon-2.0.8-1.el7.x86_64
    path: /usr/bin/conmon
    version: 'conmon version 2.0.8, commit: f85c8b1ce77b73bcd48b2d802396321217008762'
  Distribution:
    distribution: '"centos"'
    version: "7"
  MemFree: 191963136
  MemTotal: 16563531776
  OCIRuntime:
    name: runc
    package: runc-1.0.0-67.rc10.el7_8.x86_64
    path: /usr/bin/runc
    version: 'runc version spec: 1.0.1-dev'
  SwapFree: 7857680384
  SwapTotal: 8581541888
  arch: amd64
  cpus: 8
  eventlogger: journald
  hostname: lsrv1
  kernel: 3.10.0-1062.9.1.el7.x86_64
  os: linux
  rootless: false
  uptime: 607h 10m 53.36s (Approximately 25.29 days)
registries:
  blocked: null
  insecure: null
  search:
  - registry.access.redhat.com
  - registry.fedoraproject.org
  - registry.centos.org
  - docker.io
store:
  ConfigFile: /etc/containers/storage.conf
  ContainerStore:
    number: 0
  GraphDriverName: overlay
  GraphOptions: {}
  GraphRoot: /mnt/mystorage/virtual/storage
  GraphStatus:
    Backing Filesystem: extfs
    Native Overlay Diff: "true"
    Supports d_type: "true"
    Using metacopy: "false"
  ImageStore:
    number: 0
  RunRoot: /var/run/containers/storage
  VolumePath: /mnt/mystorage/virtual/storage/volumes

Keep on reading!

Data too large, data for [] would be [] which is larger than the limit of

Rsyslog writing to Elasticsearch could lead to an error for some of the records and missing to save them in the backend:

{ ... { "error": { "root_cause": [ { "type": "circuit_breaking_exception", 
"reason": "[parent] Data too large, data for [<http_request>] would be [1008813778\/962mb], which is larger than the limit of [986061209\/940.3mb], 
real usage: [1008812248\/962mb], new bytes reserved: [1530\/1.4kb], usages [request=0\/0b, fielddata=317\/317b, in_flight_requests=1530\/1.4kb, accounting=178301893\/170mb]",
"bytes_wanted": 1008813778, "bytes_limit": 986061209, "durability": "PERMANENT" }], 
"type": "circuit_breaking_exception", "reason": "[parent] Data too large, data for [<http_request>] would be [1008813778\/962mb], which is larger than the limit of [986061209\/940.3mb], 
real usage: [1008812248\/962mb], new bytes reserved: [1530\/1.4kb], usages [request=0\/0b, fielddata=317\/317b, in_flight_requests=1530\/1.4kb, accounting=178301893\/170mb]",
"bytes_wanted": 1008813778, "bytes_limit": 986061209, "durability": "PERMANENT" }, "status": 429 } }

Unfortunately, such writes are not saved in the Elasticsearch and the data has been lost.

The problem here is that the Java VM has reached the maximum allowed memory and more memory should be allowed to be used by the Java Virtual Machine.

Find the Java VM option for the Elasticsearchjvm.options. In CentOS 7 the file is located in /etc/elasticsearch/jvm.options and set more memory with the variables “-Xms[SIZE]g -Xmx[SIZE]g”, such as:

.....
-Xms4g
-Xmx4g
.....

|grep -v grep
This will allow 4G “maximum size of total heap space” to be used by the Java Virtual Machine. By default, it is 1G (-Xms1g -Xmx1g). It is a good idea to set it half of the server’s memory. Save and restart the Elasticsearch service as usual:

systemctl restart elasticsearch

You should see the variable in the command line with ps command:

[root@loganalyzer ~]# ps axuf|grep elasticsearch
elastic+   592 10.8 34.4 168638848 5493156 ?   Ssl  00:56   4:23 /usr/share/elasticsearch/jdk/bin/java -Des.networkaddress.cache.ttl=60
-Des.networkaddress.cache.negative.ttl=10 -XX:+AlwaysPreTouch -Xss1m -Djava.awt.headless=true -Dfile.encoding=UTF-8 
-Djna.nosys=true -XX:-OmitStackTraceInFastThrow -Dio.netty.noUnsafe=true -Dio.netty.noKeySetOptimization=true
-Dio.netty.recycler.maxCapacityPerThread=0 -Dio.netty.allocator.numDirectArenas=0 -Dlog4j.shutdownHookEnabled=false 
-Dlog4j2.disable.jmx=true -Djava.locale.providers=COMPAT 
-Xms4g -Xmx4g 
-XX:+UseConcMarkSweepGC -XX:CMSInitiatingOccupancyFraction=75 -XX:+UseCMSInitiatingOccupancyOnly 
-Djava.io.tmpdir=/tmp/elasticsearch-16851535740012150929 -XX:+HeapDumpOnOutOfMemoryError -XX:HeapDumpPath=/var/lib/elasticsearch 
-XX:ErrorFile=/var/log/elasticsearch/hs_err_pid%p.log elasticsearch
-Xlog:gc*,gc+age=trace,safepoint:file=/var/log/elasticsearch/gc.log:utctime,pid,tags:filecount=32,filesize=64m 
-XX:MaxDirectMemorySize=2147483648 -Des.path.home=/usr/share/elasticsearch -Des.path.conf=/etc/elasticsearch 
-Des.distribution.flavor=default -Des.distribution.type=rpm -Des.bundled_jdk=true 
-cp /usr/share/elasticsearch/lib/* org.elasticsearch.bootstrap.Elasticsearch -p /var/run/elasticsearch/elasticsearch.pid --quiet
elastic+   690  0.0  0.0  70448  4516 ?        Sl   00:56   0:00  \_ /usr/share/elasticsearch/modules/x-pack-ml/platform/linux-x86_64/bin/controller

The environment variable ES_JAVA_OPTS could be used, too.

ES_JAVA_OPTS="-Xms4g -Xmx4g" ./bin/elasticsearch 

collectd nginx plugin: curl_easy_perform failed because of selinux

Enabling the Nginx plugin for collectd under CentOS (or any other system using SELinux) might be confusing for a newbie. Most sources on the Internet would just install collectd-nginx:

yum install -y collectd-nginx

and configure it in the nginx.conf and collectd.conf. Still, the statistics might not work as expected, the collectd may not be able to gather statistics from the Nginx.

SELinux may prevent collectd (plugin) daemon to connect to Nginx and gather statistics from the Nginx stats page.

Checking the collectd log and it reports a problem:
Keep on reading!

aptly remove a package from a repository using the cli

Here is a fast tip – how to remove a package from our local aptly repository:

  1. Remove the package from the local repository.
  2. Create a new snapshot form the local repository.
  3. Publish the snapshot by switching to the newly created snapshot from the above step.

The commands executing over repository with name xenial-apps to remove package with name example-app and version 10.5.1.22-ubuntu20. The snapshot name xenial-apps1588149526 is just a temporary name used for the snapshot (the ID is unix timestamp of the current time).

aptly repo remove  xenial-apps 'example-app (= 10.5.1.22-ubuntu20)'
aptly snapshot create xenial-apps1588149526 from repo xenial-apps
aptly publish switch xenial-apps ubuntu xenial-apps1588149526

Real world example.

This is the log from our system with just changed names:
Keep on reading!