Generated dump is much smaller compared to mysqldump
cmonty14 opened this issue · 9 comments
Hi,
comparing the size of dumped databases with mariabackup vs. mysqldump there's a huge difference:
ct113-devwiki:~# ls -lh /backups/mariadb/
total 164K
-rw-r--r-- 1 root root 795 Oct 6 10:45 20181006_1045_bookstack.sql
-rw-r--r-- 1 root root 795 Oct 6 10:51 20181006_1051_bookstack.sql
-rw-r--r-- 1 root root 795 Oct 6 11:38 20181006_1138_bookstack.sql
-rw-r--r-- 1 root root 795 Oct 6 10:41 20181006_bookstack.sql
-rw-r--r-- 1 root root 65K Oct 6 12:07 bookstack-backup.sql
The dump "bookstack-backup.sql" was creating with this command:
mysqldump -u root -p bookstack > /backups/mariadb/bookstack-backup.sql
Can you please clarify why your script only dumps the database structure but no tables, etc.?
ct113-devwiki:~# more /backups/mariadb/20181006_bookstack.sql
-- MySQL dump 10.16 Distrib 10.3.9-MariaDB, for Linux (x86_64)
--
-- Host: localhost Database: bookstack
-- ------------------------------------------------------
-- Server version 10.3.9-MariaDB
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
This script is a wrapper around mysqldump:
local MY_MYSQLDUMPOPTS='--opt --single-transaction --quick --flush-logs --master-data=2' ${BIN_[MYSQLDUMP]} --defaults-extra-file=${MY_MYSQLEXTRAFILE} ${MY_MYSQLDUMPOPTS} ${DATABASE} > "${MY_TARGET_DUMP}"
Maybe due to the other issue you opened about binlog.
I never use mariadb without binlog.
I have to try to reproduce your issue on a non binlog mariadb instance.
Again, you should activate MariaDB binlog:
https://mariadb.com/kb/en/library/binary-log/
Maybe you can reproduce the issue with Alpine Linux and install the DB packages as instructed here.
This script is a wrapper around mysqldump:
local MY_MYSQLDUMPOPTS='--opt --single-transaction --quick --flush-logs --master-data=2' ${BIN_[MYSQLDUMP]} --defaults-extra-file=${MY_MYSQLEXTRAFILE} ${MY_MYSQLDUMPOPTS} ${DATABASE} > "${MY_TARGET_DUMP}"Maybe due to the other issue you opened about binlog.
I never use mariadb without binlog.
I have to try to reproduce your issue on a non binlog mariadb instance.Again, you should activate MariaDB binlog:
https://mariadb.com/kb/en/library/binary-log/
I think your assumption that this issue is related to binlog is correct.
I tried to execute the dump the database manually:
ct113-devwiki:~# MY_MYSQLDUMPOPTS='--opt --single-transaction --flush-logs --master-data=2'
MY_MYSQLDUMPOPTS='--opt --single-transaction --flush-logs --master-data=2'
ct113-devwiki:~# MY_MYSQLEXTRAFILE='/etc/my.cnf.d/backup.cnf'
MY_MYSQLEXTRAFILE='/etc/my.cnf.d/backup.cnf'
ct113-devwiki:~# mysqldump --defaults-extra-file=${MY_MYSQLEXTRAFILE} ${MY_MYSQLDUMPOPTS} bookstack > /backups/mariadb/20181006_manual_bookstack.sql
mysqldump --defaults-extra-file=${MY_MYSQLEXTRAFILE} ${MY_MYSQLDUMPOPTS} bookstack > /backups/mariadb/20181006_manual_bookstack.sql
mysqldump: Couldn't execute 'SELECT BINLOG_GTID_POS('', '0')': You are not using binary logging (1381)
ct113-devwiki:~# ls -lh /backups/mariadb/
ls -lh /backups/mariadb/
total 168K
-rw-r--r-- 1 root root 39K Oct 3 14:26 20181003_bookstack.sql
-rw-r--r-- 1 root root 795 Oct 6 10:45 20181006_1045_bookstack.sql
-rw-r--r-- 1 root root 795 Oct 6 10:51 20181006_1051_bookstack.sql
-rw-r--r-- 1 root root 795 Oct 6 11:38 20181006_1138_bookstack.sql
-rw-r--r-- 1 root root 795 Oct 6 10:41 20181006_bookstack.sql
-rw-r--r-- 1 root root 799 Oct 6 13:48 20181006_manual_bookstack.sql
-rw-r--r-- 1 root root 65K Oct 6 12:07 bookstack-backup.sql
-rw-r--r-- 1 root root 39K Oct 3 13:35 bookstack.backup.sql
Update:
Not using mysqldump-option --master-data=2 will dump the database as expected.
Yes, make total sense.
I know pretty well alpine, mote that this distribution is a little bit special as it does not use glibc but musl libc. So everything are not always running as expected on "standars" GNU/Linux distros.
Do you use Alpine in a container? Or baremetal?
Do you run mariabackup in alpine or outside?
Please could you test the branch feature/manage_binlog.
Note the change in configuration.
Yes, make total sense.
I know pretty well alpine, mote that this distribution is a little bit special as it does not use glibc but musl libc. So everything are not always running as expected on "standars" GNU/Linux distros.
Do you use Alpine in a container? Or baremetal?
Do you run mariabackup in alpine or outside?
I prefer Alpine Linux in containers; in my case LXC.
(As far as I know many Docker containers run on Alpine Linux, too.)
Typically I install web services in LXC following the principle of micro service.
E.g. Alpine + Nginx + PHP have a footprint of ~50MB.
If the web service requires a DB, I install MariaDB in the same LXC.
Please could you test the branch feature/manage_binlog.
Note the change in configuration.
The modifications are working well in my setup.
Merged into master. Fixed.