Backups

Streaming backups in parallel using tee

Posted by

So you need to build a new set of databases, perhaps in a new location or geographical zone. Management wants it done yesterday cause the newly updated service hit the front page of reddit and your website and its back-end databases are getting smashed.

The standard method would be to stream a backup from the master or a dedicated backup slave and build each new read only slave from that backup.

You tried streaming the backups in parallel using pssh because some random database blog said you can. https://dbadojo.com/2020/08/26/streaming-backups-in-parallel-using-pssh/

But that failed with a python memory error, you don’t want to use the split workaround and management are still screaming at you….so you search again…

Thinking of the basic requirements, you want one stream of data (from the backup) to go to multiple locations. So you want a linux utility which can redirect streams… enter the venerable tee command.
Tee will redirect a stream from a pipe to as many locations as you want, with the last stream going to a file or that webscale file called /dev/null.

Example of streaming a backup to two locations:

mariabackup --backup --slave-info --tmpdir=/tmp --stream=xbstream \
--parallel=4 --datadir=/var/lib/mysql 2>backup.log | tee \
>(ssh -q 192.168.56.112 -t "mbstream --directory=/var/lib/mysql -x --parallel=4") \
>(ssh -q 192.168.56.113 -t "mbstream --directory=/var/lib/mysql -x --parallel=4") \
> /dev/null

This command will split the streamed backup into two ssh commands which connect to two hosts, and run the mbstream -x command to create an unprepared backup in the datadir.
If you have more hosts, it is just a matter of adding redirections and the associated commands.

One space and performance improvement would be to add pigz to parallel compress before the tee and in each tee’ed ssh command.

Until next time.

 

Full example: Parallel streaming backup using tee.

-- Split standard input into separate parallel streams using tee

mariabackup --backup --slave-info --tmpdir=/tmp --stream=xbstream --parallel=4 --datadir=/var/lib/mysql 2>backup.log | tee >(ssh -q 192.168.56.112 -t "mbstream --directory=/var/lib/mysql -x --parallel=4") >(ssh -q 192.168.56.113 -t "mbstream --directory=/var/lib/mysql -x --parallel=4") > /dev/null

-- Use pssh to run prepare in parallel. This is a better use case for pssh

[root@db1 test_db]# pssh -i --host='192.168.56.112 192.168.56.113' "mariabackup --prepare --target-dir=/var/lib/mysql"
[1] 05:10:23 [SUCCESS] 192.168.56.112
Stderr: mariabackup based on MariaDB server 10.4.14-MariaDB Linux (x86_64)
[00] 2020-08-31 05:10:22 cd to /var/lib/mysql/
[00] 2020-08-31 05:10:22 This target seems to be not prepared yet.
[00] 2020-08-31 05:10:22 mariabackup: using the following InnoDB configuration for recovery:
[00] 2020-08-31 05:10:22 innodb_data_home_dir = .
[00] 2020-08-31 05:10:22 innodb_data_file_path = ibdata1:12M:autoextend
[00] 2020-08-31 05:10:22 innodb_log_group_home_dir = .
[00] 2020-08-31 05:10:22 InnoDB: Using Linux native AIO
[00] 2020-08-31 05:10:22 Starting InnoDB instance for recovery.
[00] 2020-08-31 05:10:22 mariabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
2020-08-31 5:10:22 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-08-31 5:10:22 0 [Note] InnoDB: Uses event mutexes
2020-08-31 5:10:22 0 [Note] InnoDB: Compressed tables use zlib 1.2.7
2020-08-31 5:10:22 0 [Note] InnoDB: Number of pools: 1
2020-08-31 5:10:22 0 [Note] InnoDB: Using SSE2 crc32 instructions
2020-08-31 5:10:22 0 [Note] InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
2020-08-31 5:10:22 0 [Note] InnoDB: Completed initialization of buffer pool
2020-08-31 5:10:22 0 [Note] InnoDB: page_cleaner coordinator priority: -20
2020-08-31 5:10:22 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=2008409552
2020-08-31 5:10:22 0 [Note] InnoDB: Last binlog file './mysql-bin.000003', position 172310127
[00] 2020-08-31 05:10:22 Last binlog file ./mysql-bin.000003, position 172310127
[00] 2020-08-31 05:10:23 completed OK!
[2] 05:10:23 [SUCCESS] 192.168.56.113
Stderr: mariabackup based on MariaDB server 10.4.14-MariaDB Linux (x86_64)
[00] 2020-08-31 05:10:22 cd to /var/lib/mysql/
[00] 2020-08-31 05:10:22 This target seems to be not prepared yet.
[00] 2020-08-31 05:10:22 mariabackup: using the following InnoDB configuration for recovery:
[00] 2020-08-31 05:10:22 innodb_data_home_dir = .
[00] 2020-08-31 05:10:22 innodb_data_file_path = ibdata1:12M:autoextend
[00] 2020-08-31 05:10:22 innodb_log_group_home_dir = .
[00] 2020-08-31 05:10:22 InnoDB: Using Linux native AIO
[00] 2020-08-31 05:10:22 Starting InnoDB instance for recovery.
[00] 2020-08-31 05:10:22 mariabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
2020-08-31 5:10:22 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-08-31 5:10:22 0 [Note] InnoDB: Uses event mutexes
2020-08-31 5:10:22 0 [Note] InnoDB: Compressed tables use zlib 1.2.7
2020-08-31 5:10:22 0 [Note] InnoDB: Number of pools: 1
2020-08-31 5:10:22 0 [Note] InnoDB: Using SSE2 crc32 instructions
2020-08-31 5:10:22 0 [Note] InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
2020-08-31 5:10:22 0 [Note] InnoDB: Completed initialization of buffer pool
2020-08-31 5:10:22 0 [Note] InnoDB: page_cleaner coordinator priority: -20
2020-08-31 5:10:22 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=2008409552
2020-08-31 5:10:22 0 [Note] InnoDB: Last binlog file './mysql-bin.000003', position 172310127
[00] 2020-08-31 05:10:23 Last binlog file ./mysql-bin.000003, position 172310127
[00] 2020-08-31 05:10:23 completed OK!

-- change ownership in parallel.

[root@db1 test_db]# pssh -i --host='192.168.56.112 192.168.56.113' "chown -R mysql:mysql /var/lib/mysql"
[1] 05:10:41 [SUCCESS] 192.168.56.112
[2] 05:10:41 [SUCCESS] 192.168.56.113

-- start databases in parallel.

[root@db1 test_db]# pssh -i --host='192.168.56.112 192.168.56.113' "systemctl start mariadb"
[1] 05:10:48 [SUCCESS] 192.168.56.112
[2] 05:10:48 [SUCCESS] 192.168.56.113

-- check the status of databases ... you guessed it, in parallel.

[root@db1 test_db]# pssh -i --host='192.168.56.112 192.168.56.113' "systemctl status mariadb"
[1] 05:10:53 [SUCCESS] 192.168.56.112
● mariadb.service - MariaDB 10.4.14 database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; vendor preset: disabled)
Drop-In: /etc/systemd/system/mariadb.service.d
└─migrated-from-my.cnf-settings.conf
Active: active (running) since Mon 2020-08-31 05:10:48 UTC; 4s ago
Docs: man:mysqld(8)
https://mariadb.com/kb/en/library/systemd/
Process: 4219 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Process: 4163 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= || VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $? -eq 0 ] && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS)
Process: 4161 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Main PID: 4186 (mysqld)
Status: "Taking your SQL requests now..."
CGroup: /system.slice/mariadb.service
└─4186 /usr/sbin/mysqld

Aug 31 05:10:48 db2 systemd[1]: Starting MariaDB 10.4.14 database server...
Aug 31 05:10:48 db2 mysqld[4186]: 2020-08-31 5:10:48 0 [Note] /usr/sbin/mysqld (mysqld 10.4.14-MariaDB-log) starting as process 4186 ...
Aug 31 05:10:48 db2 mysqld[4186]: 2020-08-31 5:10:48 0 [Warning] Could not increase number of max_open_files to more than 16384 (request: 32183)
Aug 31 05:10:48 db2 systemd[1]: Started MariaDB 10.4.14 database server.
[2] 05:10:53 [SUCCESS] 192.168.56.113
● mariadb.service - MariaDB 10.4.14 database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; vendor preset: disabled)
Drop-In: /etc/systemd/system/mariadb.service.d
└─migrated-from-my.cnf-settings.conf
Active: active (running) since Mon 2020-08-31 05:10:48 UTC; 4s ago
Docs: man:mysqld(8)
https://mariadb.com/kb/en/library/systemd/
Process: 4225 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Process: 4169 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= || VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $? -eq 0 ] && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS)
Process: 4167 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Main PID: 4192 (mysqld)
Status: "Taking your SQL requests now..."
CGroup: /system.slice/mariadb.service
└─4192 /usr/sbin/mysqld

Aug 31 05:10:48 db3 systemd[1]: Starting MariaDB 10.4.14 database server...
Aug 31 05:10:48 db3 mysqld[4192]: 2020-08-31 5:10:48 0 [Note] /usr/sbin/mysqld (mysqld 10.4.14-MariaDB-log) starting as process 4192 ...
Aug 31 05:10:48 db3 mysqld[4192]: 2020-08-31 5:10:48 0 [Warning] Could not increase number of max_open_files to more than 16384 (request: 32183)
Aug 31 05:10:48 db3 systemd[1]: Started MariaDB 10.4.14 database server.

Streaming backups in parallel using pssh

Posted by

5

So you need to build a new set of databases, perhaps in a new location or geographical zone and the business wants it done yesterday cause the newly launched product hit the front page of hacker news and your website and back-end databases are getting the hug-of-death.

The standard method would be to stream a backup from the master or a dedicated backup slave and build each new read only slave from that backup.

What if you have network bandwidth to burn? perhaps there is a way to run the streaming backups in parallel. Ivan G came up with one way to do it. See his article “Streaming Percona XtraBackup for MySQL to Multiple Destinations”

His article actually gave me the spark of an idea to determine if we could do something similar in parallel.

Enter pssh (parallel-ssh) which was hosted on google code but now is available in most linux distros.

pssh runs ssh commands on a list of hosts in parallel.

Update:

pssh uses python and os.fork to create the parallel processes.

This means that the data copied/streamed is streamed in one chunk. So if the data is greater than available RAM (x3 if compression is used) then the command will fail with “OSError: [Errno 12] Cannot allocate memory”.

Memory << Data workaround:

  1. Use gzip or pigz to compress the stream of data. This will allow the stream to be up to 2-3 times the size of available RAM.
  2. Use split to split stream into chunks and then combine those chunks on the destination. See example below.
  3. See my next post for a different method.

Here is an simple pssh example:

[root@db1 ~]# pssh -i --host='192.168.56.111 192.168.56.112 192.168.56.113' "hostname"
[1] 01:04:40 [SUCCESS] 192.168.56.112
db2
[2] 01:04:40 [SUCCESS] 192.168.56.111
db1
[3] 01:04:40 [SUCCESS] 192.168.56.113
db3

So to stream a backup from one db to another you run something like this:

mariabackup --backup --slave-info --tmpdir=/tmp --stream=xbstream --parallel=4 --datadir=/var/lib/mysql 2>backup.log | ssh -q 192.168.56.112 -t "mbstream --directory=/var/lib/mysql -x --parallel=4"

Example of a streaming backup to multiple hosts using pssh:

Note: The critical option is -I which allows pssh to take input from standard input.

mariabackup --backup --slave-info --tmpdir=/tmp --stream=xbstream --parallel=4 --datadir=/var/lib/mysql 2>backup.log | pssh -I --host='192.168.56.112 192.168.56.113' "mbstream --directory=/var/lib/mysql -x --parallel=4"

Preparing the backup on multiple hosts:

pssh -i --host='192.168.56.112 192.168.56.113' "mariabackup --prepare --target-dir=/var/lib/mysql"

Change ownership of multiple hosts:

pssh -i --host='192.168.56.112 192.168.56.113' "chown -R mysql:mysql /var/lib/mysql"

Start the db on multiple hosts:

pssh -i --host='192.168.56.112 192.168.56.113' "systemctl start mariadb.service"

Thanks to Ivan for the idea. Most people would use ansible or something similar to configure servers now over plain ssh command line. But thanks to pssh that option exists as well.

Until next time.

Full example (assumes small dataset):

[root@db1 ~]# mariabackup --backup --slave-info --tmpdir=/tmp --stream=xbstream --parallel=4 --datadir=/var/lib/mysql 2>backup.log | pssh -I --host='192.168.56.112 192.168.56.113' "mbstream --directory=/var/lib/mysql -x --parallel=4"
[1] 04:43:16 [SUCCESS] 192.168.56.112
[2] 04:43:16 [SUCCESS] 192.168.56.113


[root@db1 ~]# pssh -i --host='192.168.56.112 192.168.56.113' "ls -l /var/lib/mysql"
[1] 04:43:23 [SUCCESS] 192.168.56.112
total 12352
-rw-r-----. 1 root root 32768 Aug 26 04:43 aria_log.00000001
-rw-r-----. 1 root root 52 Aug 26 04:43 aria_log_control
-rw-r-----. 1 root root 324 Aug 26 04:43 backup-my.cnf
-rw-r-----. 1 root root 972 Aug 26 04:43 ib_buffer_pool
-rw-r-----. 1 root root 12582912 Aug 26 04:43 ibdata1
-rw-r-----. 1 root root 2560 Aug 26 04:43 ib_logfile0
drwx------. 2 root root 4096 Aug 26 04:43 mysql
drwx------. 2 root root 20 Aug 26 04:43 performance_schema
drwx------. 2 root root 20 Aug 26 04:43 test
-rw-r-----. 1 root root 29 Aug 26 04:43 xtrabackup_binlog_info
-rw-r-----. 1 root root 73 Aug 26 04:43 xtrabackup_checkpoints
-rw-r-----. 1 root root 554 Aug 26 04:43 xtrabackup_info
[2] 04:43:23 [SUCCESS] 192.168.56.113
total 12352
-rw-r-----. 1 root root 32768 Aug 26 04:43 aria_log.00000001
-rw-r-----. 1 root root 52 Aug 26 04:43 aria_log_control
-rw-r-----. 1 root root 324 Aug 26 04:43 backup-my.cnf
-rw-r-----. 1 root root 972 Aug 26 04:43 ib_buffer_pool
-rw-r-----. 1 root root 12582912 Aug 26 04:43 ibdata1
-rw-r-----. 1 root root 2560 Aug 26 04:43 ib_logfile0
drwx------. 2 root root 4096 Aug 26 04:43 mysql
drwx------. 2 root root 20 Aug 26 04:43 performance_schema
drwx------. 2 root root 20 Aug 26 04:43 test
-rw-r-----. 1 root root 29 Aug 26 04:43 xtrabackup_binlog_info
-rw-r-----. 1 root root 73 Aug 26 04:43 xtrabackup_checkpoints
-rw-r-----. 1 root root 554 Aug 26 04:43 xtrabackup_info

-- prepare

[root@db1 ~]# pssh -i --host='192.168.56.112 192.168.56.113' "mariabackup --prepare --target-dir=/var/lib/mysql"
[1] 04:43:56 [SUCCESS] 192.168.56.112
Stderr: mariabackup based on MariaDB server 10.4.14-MariaDB Linux (x86_64)
[00] 2020-08-26 04:43:55 cd to /var/lib/mysql/
[00] 2020-08-26 04:43:55 This target seems to be not prepared yet.
[00] 2020-08-26 04:43:55 mariabackup: using the following InnoDB configuration for recovery:
[00] 2020-08-26 04:43:55 innodb_data_home_dir = .
[00] 2020-08-26 04:43:55 innodb_data_file_path = ibdata1:12M:autoextend
[00] 2020-08-26 04:43:55 innodb_log_group_home_dir = .
[00] 2020-08-26 04:43:55 InnoDB: Using Linux native AIO
[00] 2020-08-26 04:43:55 Starting InnoDB instance for recovery.
[00] 2020-08-26 04:43:55 mariabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
2020-08-26 4:43:55 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-08-26 4:43:55 0 [Note] InnoDB: Uses event mutexes
2020-08-26 4:43:55 0 [Note] InnoDB: Compressed tables use zlib 1.2.7
2020-08-26 4:43:55 0 [Note] InnoDB: Number of pools: 1
2020-08-26 4:43:55 0 [Note] InnoDB: Using SSE2 crc32 instructions
2020-08-26 4:43:55 0 [Note] InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
2020-08-26 4:43:55 0 [Note] InnoDB: Completed initialization of buffer pool
2020-08-26 4:43:55 0 [Note] InnoDB: page_cleaner coordinator priority: -20
2020-08-26 4:43:55 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=60967
[00] 2020-08-26 04:43:55 Last binlog file , position 0
[00] 2020-08-26 04:43:56 completed OK!
[2] 04:43:56 [SUCCESS] 192.168.56.113
Stderr: mariabackup based on MariaDB server 10.4.14-MariaDB Linux (x86_64)
[00] 2020-08-26 04:43:55 cd to /var/lib/mysql/
[00] 2020-08-26 04:43:55 This target seems to be not prepared yet.
[00] 2020-08-26 04:43:55 mariabackup: using the following InnoDB configuration for recovery:
[00] 2020-08-26 04:43:55 innodb_data_home_dir = .
[00] 2020-08-26 04:43:55 innodb_data_file_path = ibdata1:12M:autoextend
[00] 2020-08-26 04:43:55 innodb_log_group_home_dir = .
[00] 2020-08-26 04:43:55 InnoDB: Using Linux native AIO
[00] 2020-08-26 04:43:55 Starting InnoDB instance for recovery.
[00] 2020-08-26 04:43:55 mariabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
2020-08-26 4:43:55 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-08-26 4:43:55 0 [Note] InnoDB: Uses event mutexes
2020-08-26 4:43:55 0 [Note] InnoDB: Compressed tables use zlib 1.2.7
2020-08-26 4:43:55 0 [Note] InnoDB: Number of pools: 1
2020-08-26 4:43:55 0 [Note] InnoDB: Using SSE2 crc32 instructions
2020-08-26 4:43:55 0 [Note] InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
2020-08-26 4:43:55 0 [Note] InnoDB: Completed initialization of buffer pool
2020-08-26 4:43:55 0 [Note] InnoDB: page_cleaner coordinator priority: -20
2020-08-26 4:43:55 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=60967
[00] 2020-08-26 04:43:55 Last binlog file , position 0
[00] 2020-08-26 04:43:56 completed OK!

-- change ownership and start

[root@db1 ~]# pssh -i --host='192.168.56.112 192.168.56.113' "chown -R mysql:mysql /var/lib/mysql"
[1] 04:44:25 [SUCCESS] 192.168.56.112
[2] 04:44:25 [SUCCESS] 192.168.56.113

[root@db1 ~]# pssh -i --host='192.168.56.112 192.168.56.113' "systemctl start mariadb.service"
[1] 04:44:34 [SUCCESS] 192.168.56.112
[2] 04:44:34 [SUCCESS] 192.168.56.113

-- Bonus: Generate change master from xtrabackup_binlog_info file
-- Change the host, user and password.
-- \x27 is the quote character

awk '{ print "change master to master_host=\x27192.168.56.111\x27,master_user=\x27repl\x27,master_password=\x27lper\x27,master_log_file=\x27"$1"\x27,m
aster_log_pos="$2 }' xtrabackup_binlog_info
change master to master_host=.168.56.111',master_user='repl',master_password='lper',master_log_file='mysql-bin.000001',master_log_pos=2194

Full Example using split to get around memory issue

[root@db1 test_db]# du -sh /var/lib/mysql
1.7G /var/lib/mysql

[root@db1 test_db]# mariabackup --backup --slave-info --tmpdir=/tmp --stream=xbstream --parallel=4 --datadir=/var/lib/mysql 2>backup.log | pssh -I --host='192.168.56.112 192.168.56.113' "mbstream --directory=/var/lib/mysql -x --parallel=4"
Traceback (most recent call last):
File "/bin/pssh", line 118, in <module>
do_pssh(hosts, cmdline, opts)
File "/bin/pssh", line 89, in do_pssh
statuses = manager.run()
File "/usr/lib/python2.7/site-packages/psshlib/manager.py", line 68, in run
self.update_tasks(writer)
File "/usr/lib/python2.7/site-packages/psshlib/manager.py", line 133, in update_tasks
self._start_tasks_once(writer)
File "/usr/lib/python2.7/site-packages/psshlib/manager.py", line 146, in _start_tasks_once
task.start(self.taskcount, self.iomap, writer, self.askpass_socket)
File "/usr/lib/python2.7/site-packages/psshlib/task.py", line 99, in start
close_fds=False, preexec_fn=os.setsid, env=environ)
File "/usr/lib64/python2.7/subprocess.py", line 711, in __init__
errread, errwrite)
File "/usr/lib64/python2.7/subprocess.py", line 1224, in _execute_child
self.pid = os.fork()
OSError: [Errno 12] Cannot allocate memory

-- Split in 100Mbyte chunks

[root@db1 test_db]# mariabackup --backup --slave-info --tmpdir=/tmp --stream=xbstream --parallel=4 --datadir=/var/lib/mysql 2>backup.log | split -b 100M --filter="exec cat $FILE |pssh -I --host='192.168.56.112 192.168.56.113' 'cat $FILE >> /var/lib/mysql/streamfile ' "
[1] 04:19:28 [SUCCESS] 192.168.56.112
[2] 04:19:28 [SUCCESS] 192.168.56.113
[1] 04:19:30 [SUCCESS] 192.168.56.113
[2] 04:19:30 [SUCCESS] 192.168.56.112
[1] 04:19:33 [SUCCESS] 192.168.56.112
[2] 04:19:33 [SUCCESS] 192.168.56.113
[1] 04:19:35 [SUCCESS] 192.168.56.112
[2] 04:19:35 [SUCCESS] 192.168.56.113
[1] 04:19:36 [SUCCESS] 192.168.56.113
[2] 04:19:37 [SUCCESS] 192.168.56.112
[1] 04:19:39 [SUCCESS] 192.168.56.113
[2] 04:19:39 [SUCCESS] 192.168.56.112
[1] 04:19:41 [SUCCESS] 192.168.56.112
[2] 04:19:41 [SUCCESS] 192.168.56.113
[1] 04:19:43 [SUCCESS] 192.168.56.112
[2] 04:19:43 [SUCCESS] 192.168.56.113
[1] 04:19:45 [SUCCESS] 192.168.56.113
[2] 04:19:45 [SUCCESS] 192.168.56.112
[1] 04:19:47 [SUCCESS] 192.168.56.112
[2] 04:19:47 [SUCCESS] 192.168.56.113
[1] 04:19:50 [SUCCESS] 192.168.56.112
[2] 04:19:50 [SUCCESS] 192.168.56.113
[1] 04:19:53 [SUCCESS] 192.168.56.112
[2] 04:19:53 [SUCCESS] 192.168.56.113

[root@db1 test_db]# pssh -i --host='192.168.56.112 192.168.56.113' "ls -l /var/lib/mysql/"
[1] 04:20:02 [SUCCESS] 192.168.56.112
total 1221516
-rw-r--r--. 1 root root 1250829999 Aug 31 04:19 streamfile
[2] 04:20:02 [SUCCESS] 192.168.56.113
total 1221516
-rw-r--r--. 1 root root 1250829999 Aug 31 04:19 streamfile

-- Run the streamfile thru mbstream to produce the un-prepared datafiles

[root@db1 test_db]# pssh -i --host='192.168.56.112 192.168.56.113' "cat /var/lib/mysql/streamfile | mbstream --directory=/var/lib/mysql -x --parallel=4"
[1] 04:23:13 [SUCCESS] 192.168.56.112
[2] 04:23:13 [SUCCESS] 192.168.56.113
[root@db1 test_db]# pssh -i --host='192.168.56.112 192.168.56.113' "ls -l /var/lib/mysql/"
[1] 04:23:35 [SUCCESS] 192.168.56.112
total 1496016
-rw-r-----. 1 root root 32768 Aug 31 04:23 aria_log.00000001
-rw-r-----. 1 root root 52 Aug 31 04:23 aria_log_control
-rw-r-----. 1 root root 324 Aug 31 04:23 backup-my.cnf
drwx------. 2 root root 4096 Aug 31 04:23 employees
-rw-r-----. 1 root root 976 Aug 31 04:23 ib_buffer_pool
-rw-r-----. 1 root root 281018368 Aug 31 04:23 ibdata1
-rw-r-----. 1 root root 2560 Aug 31 04:23 ib_logfile0
drwx------. 2 root root 4096 Aug 31 04:23 mysql
drwx------. 2 root root 20 Aug 31 04:23 performance_schema
-rw-r--r--. 1 root root 1250829999 Aug 31 04:19 streamfile
drwx------. 2 root root 20 Aug 31 04:23 test
-rw-r-----. 1 root root 35 Aug 31 04:23 xtrabackup_binlog_info
-rw-r-----. 1 root root 83 Aug 31 04:23 xtrabackup_checkpoints
-rw-r-----. 1 root root 565 Aug 31 04:23 xtrabackup_info
[2] 04:23:35 [SUCCESS] 192.168.56.113
total 1496016
-rw-r-----. 1 root root 32768 Aug 31 04:23 aria_log.00000001
-rw-r-----. 1 root root 52 Aug 31 04:23 aria_log_control
-rw-r-----. 1 root root 324 Aug 31 04:23 backup-my.cnf
drwx------. 2 root root 4096 Aug 31 04:23 employees
-rw-r-----. 1 root root 976 Aug 31 04:23 ib_buffer_pool
-rw-r-----. 1 root root 281018368 Aug 31 04:23 ibdata1
-rw-r-----. 1 root root 2560 Aug 31 04:23 ib_logfile0
drwx------. 2 root root 4096 Aug 31 04:23 mysql
drwx------. 2 root root 20 Aug 31 04:23 performance_schema
-rw-r--r--. 1 root root 1250829999 Aug 31 04:19 streamfile
drwx------. 2 root root 20 Aug 31 04:23 test
-rw-r-----. 1 root root 35 Aug 31 04:23 xtrabackup_binlog_info
-rw-r-----. 1 root root 83 Aug 31 04:23 xtrabackup_checkpoints
-rw-r-----. 1 root root 565 Aug 31 04:23 xtrabackup_info

-- Prepare the backup

[root@db1 test_db]# pssh -i --host='192.168.56.112 192.168.56.113' "mariabackup --prepare --target-dir=/var/lib/mysql"
[1] 04:24:14 [SUCCESS] 192.168.56.112
Stderr: mariabackup based on MariaDB server 10.4.14-MariaDB Linux (x86_64)
[00] 2020-08-31 04:24:14 cd to /var/lib/mysql/
[00] 2020-08-31 04:24:14 This target seems to be not prepared yet.
[00] 2020-08-31 04:24:14 mariabackup: using the following InnoDB configuration for recovery:
[00] 2020-08-31 04:24:14 innodb_data_home_dir = .
[00] 2020-08-31 04:24:14 innodb_data_file_path = ibdata1:12M:autoextend
[00] 2020-08-31 04:24:14 innodb_log_group_home_dir = .
[00] 2020-08-31 04:24:14 InnoDB: Using Linux native AIO
[00] 2020-08-31 04:24:14 Starting InnoDB instance for recovery.
[00] 2020-08-31 04:24:14 mariabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
2020-08-31 4:24:14 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-08-31 4:24:14 0 [Note] InnoDB: Uses event mutexes
2020-08-31 4:24:14 0 [Note] InnoDB: Compressed tables use zlib 1.2.7
2020-08-31 4:24:14 0 [Note] InnoDB: Number of pools: 1
2020-08-31 4:24:14 0 [Note] InnoDB: Using SSE2 crc32 instructions
2020-08-31 4:24:14 0 [Note] InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
2020-08-31 4:24:14 0 [Note] InnoDB: Completed initialization of buffer pool
2020-08-31 4:24:14 0 [Note] InnoDB: page_cleaner coordinator priority: -20
2020-08-31 4:24:14 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=2008409552
2020-08-31 4:24:14 0 [Note] InnoDB: Last binlog file './mysql-bin.000003', position 172310127
[00] 2020-08-31 04:24:14 Last binlog file ./mysql-bin.000003, position 172310127
[00] 2020-08-31 04:24:14 completed OK!
[2] 04:24:14 [SUCCESS] 192.168.56.113
Stderr: mariabackup based on MariaDB server 10.4.14-MariaDB Linux (x86_64)
[00] 2020-08-31 04:24:14 cd to /var/lib/mysql/
[00] 2020-08-31 04:24:14 This target seems to be not prepared yet.
[00] 2020-08-31 04:24:14 mariabackup: using the following InnoDB configuration for recovery:
[00] 2020-08-31 04:24:14 innodb_data_home_dir = .
[00] 2020-08-31 04:24:14 innodb_data_file_path = ibdata1:12M:autoextend
[00] 2020-08-31 04:24:14 innodb_log_group_home_dir = .
[00] 2020-08-31 04:24:14 InnoDB: Using Linux native AIO
[00] 2020-08-31 04:24:14 Starting InnoDB instance for recovery.
[00] 2020-08-31 04:24:14 mariabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
2020-08-31 4:24:14 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-08-31 4:24:14 0 [Note] InnoDB: Uses event mutexes
2020-08-31 4:24:14 0 [Note] InnoDB: Compressed tables use zlib 1.2.7
2020-08-31 4:24:14 0 [Note] InnoDB: Number of pools: 1
2020-08-31 4:24:14 0 [Note] InnoDB: Using SSE2 crc32 instructions
2020-08-31 4:24:14 0 [Note] InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
2020-08-31 4:24:14 0 [Note] InnoDB: Completed initialization of buffer pool
2020-08-31 4:24:14 0 [Note] InnoDB: page_cleaner coordinator priority: -20
2020-08-31 4:24:14 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=2008409552
2020-08-31 4:24:14 0 [Note] InnoDB: Last binlog file './mysql-bin.000003', position 172310127
[00] 2020-08-31 04:24:14 Last binlog file ./mysql-bin.000003, position 172310127
[00] 2020-08-31 04:24:14 completed OK!

[root@db1 test_db]# pssh -i --host='192.168.56.112 192.168.56.113' "chown -R mysql:mysql /var/lib/mysql"
[1] 04:25:08 [SUCCESS] 192.168.56.112
[2] 04:25:08 [SUCCESS] 192.168.56.113
[root@db1 test_db]# pssh -i --host='192.168.56.112 192.168.56.113' "ls -l /var/lib/mysql/"
[1] 04:25:21 [SUCCESS] 192.168.56.112
total 1496012
-rw-r-----. 1 mysql mysql 32768 Aug 31 04:23 aria_log.00000001
-rw-r-----. 1 mysql mysql 52 Aug 31 04:23 aria_log_control
-rw-r-----. 1 mysql mysql 324 Aug 31 04:23 backup-my.cnf
drwx------. 2 mysql mysql 4096 Aug 31 04:23 employees
-rw-r-----. 1 mysql mysql 976 Aug 31 04:23 ib_buffer_pool
-rw-r-----. 1 mysql mysql 281018368 Aug 31 04:24 ibdata1
-rw-r-----. 1 mysql mysql 0 Aug 31 04:24 ib_logfile0
drwx------. 2 mysql mysql 4096 Aug 31 04:23 mysql
drwx------. 2 mysql mysql 20 Aug 31 04:23 performance_schema
-rw-r--r--. 1 mysql mysql 1250829999 Aug 31 04:19 streamfile
drwx------. 2 mysql mysql 20 Aug 31 04:23 test
-rw-r-----. 1 mysql mysql 35 Aug 31 04:23 xtrabackup_binlog_info
-rw-r-----. 1 mysql mysql 81 Aug 31 04:24 xtrabackup_checkpoints
-rw-r-----. 1 mysql mysql 565 Aug 31 04:23 xtrabackup_info
[2] 04:25:21 [SUCCESS] 192.168.56.113
total 1496012
-rw-r-----. 1 mysql mysql 32768 Aug 31 04:23 aria_log.00000001
-rw-r-----. 1 mysql mysql 52 Aug 31 04:23 aria_log_control
-rw-r-----. 1 mysql mysql 324 Aug 31 04:23 backup-my.cnf
drwx------. 2 mysql mysql 4096 Aug 31 04:23 employees
-rw-r-----. 1 mysql mysql 976 Aug 31 04:23 ib_buffer_pool
-rw-r-----. 1 mysql mysql 281018368 Aug 31 04:24 ibdata1
-rw-r-----. 1 mysql mysql 0 Aug 31 04:24 ib_logfile0
drwx------. 2 mysql mysql 4096 Aug 31 04:23 mysql
drwx------. 2 mysql mysql 20 Aug 31 04:23 performance_schema
-rw-r--r--. 1 mysql mysql 1250829999 Aug 31 04:19 streamfile
drwx------. 2 mysql mysql 20 Aug 31 04:23 test
-rw-r-----. 1 mysql mysql 35 Aug 31 04:23 xtrabackup_binlog_info
-rw-r-----. 1 mysql mysql 81 Aug 31 04:24 xtrabackup_checkpoints
-rw-r-----. 1 mysql mysql 565 Aug 31 04:23 xtrabackup_info

[root@db1 test_db]# pssh -i --host='192.168.56.112 192.168.56.113' "systemctl start mariadb"
[1] 04:26:59 [SUCCESS] 192.168.56.112
[2] 04:26:59 [SUCCESS] 192.168.56.113
[root@db1 test_db]# pssh -i --host='192.168.56.112 192.168.56.113' "systemctl status mariadb"
[1] 04:27:05 [SUCCESS] 192.168.56.113
● mariadb.service - MariaDB 10.4.14 database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; vendor preset: disabled)
Drop-In: /etc/systemd/system/mariadb.service.d
└─migrated-from-my.cnf-settings.conf
Active: active (running) since Mon 2020-08-31 04:26:59 UTC; 6s ago
Docs: man:mysqld(8)
https://mariadb.com/kb/en/library/systemd/
Process: 4072 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Process: 4016 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= || VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $? -eq 0 ] && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS)
Process: 4014 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Main PID: 4039 (mysqld)
Status: "Taking your SQL requests now..."
CGroup: /system.slice/mariadb.service
└─4039 /usr/sbin/mysqld

Aug 31 04:26:59 db3 systemd[1]: Starting MariaDB 10.4.14 database server...
Aug 31 04:26:59 db3 mysqld[4039]: 2020-08-31 4:26:59 0 [Note] /usr/sbin/mysqld (mysqld 10.4.14-MariaDB-log) starting as process 4039 ...
Aug 31 04:26:59 db3 mysqld[4039]: 2020-08-31 4:26:59 0 [Warning] Could not increase number of max_open_files to more than 16384 (request: 32183)
Aug 31 04:26:59 db3 systemd[1]: Started MariaDB 10.4.14 database server.
[2] 04:27:05 [SUCCESS] 192.168.56.112
● mariadb.service - MariaDB 10.4.14 database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; vendor preset: disabled)
Drop-In: /etc/systemd/system/mariadb.service.d
└─migrated-from-my.cnf-settings.conf
Active: active (running) since Mon 2020-08-31 04:26:59 UTC; 6s ago
Docs: man:mysqld(8)
https://mariadb.com/kb/en/library/systemd/
Process: 4026 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Process: 3970 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= || VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $? -eq 0 ] && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS)
Process: 3968 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Main PID: 3993 (mysqld)
Status: "Taking your SQL requests now..."
CGroup: /system.slice/mariadb.service
└─3993 /usr/sbin/mysqld

Aug 31 04:26:59 db2 systemd[1]: Starting MariaDB 10.4.14 database server...
Aug 31 04:26:59 db2 mysqld[3993]: 2020-08-31 4:26:59 0 [Note] /usr/sbin/mysqld (mysqld 10.4.14-MariaDB-log) starting as process 3993 ...
Aug 31 04:26:59 db2 mysqld[3993]: 2020-08-31 4:26:59 0 [Warning] Could not increase number of max_open_files to more than 16384 (request: 32183)
Aug 31 04:26:59 db2 systemd[1]: Started MariaDB 10.4.14 database server.

Faster streaming backups using mariabackup and pigz

Posted by

1

Are you looking for a faster way to stream a backup from one db server to another?

Maybe you are rebuilding a replica from the master after someone wrote to the slave and now the data is out of sync.

I had a look at what was out there in terms of articles about streaming backups.

Most of them got part of the way but clearly haven’t had people asking in chat “are we there yet?” over and over, wondering when their replicas will be back so they can direct the read traffic at them.
The key here is to use the parallel options that come with mariabackup and use pigz to compress and decompress in parallel.

Do you want speed?
Do you want to saturate the 10Gigabit bonded link or InfiniBand (I wish…) ?
Do you have spare CPUs to use?

WARNING:

  • This command assumes the destination directory is empty. 
  • The command will 8 CPUs (4 for mariabackup and 4 for pigz) on the source
  • The command can use up to 8 CPUs (4 for pigz to decompress, 4 for mbstream)
  • Don’t run pigz without -p option. As by default it will use all CPUs available.

The command:

mariabackup --backup --slave-info --tmpdir=/tmp --stream=xbstream --parallel=4 --datadir=/var/lib/mysql 2>backup.log | pigz -p 4 | ssh -q replica -t "pigz -dc -p 4 | mbstream --directory=/var/lib/mysql -x --parallel=4"

Explanation of the command:

  1. This command will take a streaming backup (using xbstream) in parallel from the database in /var/lib/mysql.
  2. It will write output the backup.log (just in case you need to debug…)
  3. Then it will pipe that toq pigz (parellel gzip) which will compress in parallel.
  4. This stream of compressed xbstream will be sent encrypted via ssh to host called replica.
  5. On the replica, the compressed xbstream will be uncompressed by pigz, then piped thru mbstream into the /var/lib/mysql directory.

Next steps:

  1. Prepare the backup using the appropriate amount of RAM. 
    mariabackup --prepare --use-memory=16G --target-dir=/var/lib/mysql/
  2. Change the ownership of the destination datadir 
    chown -R mysql:mysql /var/lib/mysql
  3. Start the db 
    systemctl start mariadb
  4. Look at xtrabackup_slave_info to get the slave info 
    cat /var/lib/mysql/xtrabackup_slave_info
  5. Change the master using CHANGE MASTER using that info.

Until next time.