dksnap
dksnap copied to clipboard
kelda
Mariadb/Mysql restore not working?
Hi,
I was looking for a tool that could do exactly this, and I was really happy when I found it. This is functionality my expensive Datagrip totally lacks, but is super handy. However; there seems to be a bug when it comes to restoring a snapshot. The tool reports a success, but no container is actually started. I've tried replacing and starting from a snapshot; both don't work. When replacing, the old container is shut down, but no new container is spun up. In the docker logs (sudo journalctl -fu docker.service) I only see the following line pop up every time I try to start from a snapshot:
dockerd[927]: time="2020-05-01T10:23:45.486207189+02:00" level=info msg="ignoring event" module=libcontainerd namespace=moby topic=/tasks/delete type="*events.TaskDelete"
I am using the following docker-compose:
version: '3'
services:
mariadb:
image: mariadb:latest
ports:
- "3306:3306"
volumes:
- ./dump_20200210.sql:/docker-entrypoint-initdb.d/sql_dump_file.sql
- ./packet_size.cnf:/etc/mysql/mariadb.conf.d/packet_size.cnf
environment:
- MYSQL_ROOT_PASSWORD=
- MYSQL_DATABASE=testdb
- MYSQL_ALLOW_EMPTY_PASSWORD=true
EDIT: In my vscode-docker I see a bunch of new containers have been created, three of which had the names of the snapshots I took. The others were just hashes. I guess dksnap did something; but not what I would expect :)
Also, on another note; it is not possible to define a root password in this tool. This should totally be the case; since it would provide a little extra security. It should at least prompt the user when passwordless auth fails to let me enter the password. I have never worked with go before; so I'm afraid I won't be too much of an help in fixing this issue.
Thank you for your time.
Hi @MagicLegend
I tried out your setup on my machine, and it seemed to work. Only difference was that I didn't set the packet_size.cnf config, and I obviously don't have the same data in my dump_20200210.sql.
I'm just wildly guessing, but my theory is that your dump file has some statements that conflict with the dump file generated by dksnap. So when dksnap boots the container, the container loads both database files, and crashes.
It'd be helpful if you:
- Check if
dksnapis actually booting the container, but then crashing. I.e. rundocker ps -aafter using dksnap to boot your snapshot. - If you don't see a container being booted at all, you can boot it directly with
docker run <snapshot image>. To get the snapshot image, rundocker images: you should see an image that matches your snapshot name. - Finally, you could try snapshotting and booting without any
volumes.
To your note on defining a password, we support that for Postgres, so it's just a matter of doing the same for Mariadb. I'll make an issue on it.
Thanks!
Hi @kklin
Sorry getting back at you this late. The packet_size.cnf didn't do anything anyway (I got this docker-compose.yml from a colleague; and just dropped it in and started :-)). I have cleaned up the compose, and created a test sql file to init the db with.
version: '3'
services:
mariadb:
image: mariadb:latest
ports:
- "3306:3306"
volumes:
- ./init.sql:/docker-entrypoint-initdb.d/sql_dump_file.sql
environment:
- MYSQL_ROOT_PASSWORD=
- MYSQL_DATABASE=testdb
- MYSQL_ALLOW_EMPTY_PASSWORD=true
create table if not exists Genre
(
name varchar(255) not null
primary key,
createdAt datetime null,
updatedAt datetime null
);
I just wanted it to contain something as a poc.
After initial docker-compose up. docker ps -a:
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
39007184cede mariadb:latest "docker-entrypoint.s…" 20 seconds ago Up 19 seconds 0.0.0.0:3306->3306/tcp mariadb_mariadb_1
After first snapshot taken. docker ps -a:
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
52b5f2b8a07d ec8623bec446 "/dksnap/entrypoint.…" 4 seconds ago Created stupefied_khayyam
48d3910d1b83 22312ee512a8 "/dksnap/entrypoint.…" 4 seconds ago Created ecstatic_mestorf
b5886fd1640e 64c6496bfa7e "/dksnap/entrypoint.…" 4 seconds ago Created eager_hypatia
37188460428a e63baea27eb6 "/dksnap/entrypoint.…" 4 seconds ago Created condescending_ganguly
bb0c535d281d 651c9b429177 "/dksnap/entrypoint.…" 4 seconds ago Created ecstatic_feynman
14a005fe5af3 427842cf9f51 "/dksnap/entrypoint.…" 4 seconds ago Created optimistic_thompson
39007184cede mariadb:latest "docker-entrypoint.s…" About a minute ago Up About a minute 0.0.0.0:3306->3306/tcp mariadb_mariadb_1
docker images:
REPOSITORY TAG IMAGE ID CREATED SIZE
pre-edit latest 094a056263b6 49 seconds ago 361MB
<none> <none> 245696944ad6 10 minutes ago 366MB
<none> <none> 2ef60475f4ea 11 minutes ago 366MB
mariadb latest b6184b68d1fd 11 days ago 357MB
docker volumes ls:
DRIVER VOLUME NAME
local 0bf48bdbee8f8afdbfbd35e7e9f1e7ce3a258491c7ea4f9bde8fff85bf60aa8e
local 00c777b9c248d51406af87d5afec3994bbe88e844aea2d5427e714f854b1ef76
local 2b20c46a8e81e2d3f1eb56505c0266befa639ed67b6882148b280242439cee2f
local 48caf4639985c78bf7a57f9a058a818281af868585fba95861245a4c5859cff9
local 798a5288304df8890d87054b65e0ca22592da1f1f9f5d643b59d3acfdfbdb8ea
local 4680a7efe4cce7b822f68f92f6c43493c06937fa0ac46dd93183e72889c26152
local d11b7d861e97e0044016f871b06b1abd25bdbd5f281e054f4b9efb1588e020c1
Executing SQL:
INSERT INTO testdb.Genre (name, createdAt, updatedAt) VALUES ('Genre1', '2020-05-06 17:55:20', '2020-05-06 17:55:21');
INSERT INTO testdb.Genre (name, createdAt, updatedAt) VALUES ('Genre2', null, null);
Taking second snapshot (post-edit). docker ps -a:
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
b2bbced5a25f 2a8b1092a82c "/dksnap/entrypoint.…" 10 seconds ago Created agitated_matsumoto
33d2bebc2573 4b2bf4e0b4a9 "/dksnap/entrypoint.…" 10 seconds ago Created intelligent_fermat
51c454a07ac4 93d8918eede0 "/dksnap/entrypoint.…" 10 seconds ago Created relaxed_sammet
ef562848ae2c 026b0b6c771d "/dksnap/entrypoint.…" 11 seconds ago Created hardcore_ellis
a8d3b83a903d 745d051b4b24 "/dksnap/entrypoint.…" 11 seconds ago Created busy_wescoff
41b6b27e647b f24f9df9009e "/dksnap/entrypoint.…" 11 seconds ago Created blissful_lovelace
52b5f2b8a07d ec8623bec446 "/dksnap/entrypoint.…" 4 minutes ago Created stupefied_khayyam
48d3910d1b83 22312ee512a8 "/dksnap/entrypoint.…" 4 minutes ago Created ecstatic_mestorf
b5886fd1640e 64c6496bfa7e "/dksnap/entrypoint.…" 4 minutes ago Created eager_hypatia
37188460428a e63baea27eb6 "/dksnap/entrypoint.…" 4 minutes ago Created condescending_ganguly
bb0c535d281d 651c9b429177 "/dksnap/entrypoint.…" 4 minutes ago Created ecstatic_feynman
14a005fe5af3 427842cf9f51 "/dksnap/entrypoint.…" 4 minutes ago Created optimistic_thompson
39007184cede mariadb:latest "docker-entrypoint.s…" 6 minutes ago Up 5 minutes 0.0.0.0:3306->3306/tcp mariadb_mariadb_1
docker images:
REPOSITORY TAG IMAGE ID CREATED SIZE
post-edit latest a5dd3c62314d 43 seconds ago 361MB
pre-edit latest 094a056263b6 5 minutes ago 361MB
<none> <none> 245696944ad6 14 minutes ago 366MB
<none> <none> 2ef60475f4ea 15 minutes ago 366MB
mariadb latest b6184b68d1fd 11 days ago 357MB
docker volumes ls:
DRIVER VOLUME NAME
local 0bf48bdbee8f8afdbfbd35e7e9f1e7ce3a258491c7ea4f9bde8fff85bf60aa8e
local 00c777b9c248d51406af87d5afec3994bbe88e844aea2d5427e714f854b1ef76
local 2b20c46a8e81e2d3f1eb56505c0266befa639ed67b6882148b280242439cee2f
local 4ce9a7ca0a36a1ad79f1c444fd3fd924bbf318f379e88fa4f7420f6453ccee21
local 41f5fc888da89cd1fb9401e9d318326100ae177363a683ac1554150f93bed3cc
local 48caf4639985c78bf7a57f9a058a818281af868585fba95861245a4c5859cff9
local 372cfd7f670756a0bc1425942a953816b0ce4bd95ce5cddc8a83eee69600b889
local 798a5288304df8890d87054b65e0ca22592da1f1f9f5d643b59d3acfdfbdb8ea
local 4680a7efe4cce7b822f68f92f6c43493c06937fa0ac46dd93183e72889c26152
local d11b7d861e97e0044016f871b06b1abd25bdbd5f281e054f4b9efb1588e020c1
local f6d0e19dfcae9d92f854a5810f930ca88707a25465d197adff6292a5454ff347
local f99c50765d34993e7cd5277ac4ed470c43854a47edb470f9bb4fbaab887ead88
local fdd71f6c06d3524181460c5a5c055112dcb7eecdbdec15c6cdd76606bbaf520b
Diff view in dksnap:
Rolling back to initial state
After rolling back. docker ps -a:
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
967244ad7844 pre-edit:latest "/dksnap/entrypoint.…" 16 seconds ago Exited (1) 5 seconds ago mariadb_mariadb_1
e1b2d8bd064c 094a056263b6 "/dksnap/entrypoint.…" 58 seconds ago Created cool_ritchie
8ae6ec66fea9 a5dd3c62314d "/dksnap/entrypoint.…" 58 seconds ago Created busy_heisenberg
b2bbced5a25f 2a8b1092a82c "/dksnap/entrypoint.…" 3 minutes ago Created agitated_matsumoto
33d2bebc2573 4b2bf4e0b4a9 "/dksnap/entrypoint.…" 3 minutes ago Created intelligent_fermat
51c454a07ac4 93d8918eede0 "/dksnap/entrypoint.…" 3 minutes ago Created relaxed_sammet
ef562848ae2c 026b0b6c771d "/dksnap/entrypoint.…" 3 minutes ago Created hardcore_ellis
a8d3b83a903d 745d051b4b24 "/dksnap/entrypoint.…" 3 minutes ago Created busy_wescoff
41b6b27e647b f24f9df9009e "/dksnap/entrypoint.…" 3 minutes ago Created blissful_lovelace
52b5f2b8a07d ec8623bec446 "/dksnap/entrypoint.…" 7 minutes ago Created stupefied_khayyam
48d3910d1b83 22312ee512a8 "/dksnap/entrypoint.…" 7 minutes ago Created ecstatic_mestorf
b5886fd1640e 64c6496bfa7e "/dksnap/entrypoint.…" 7 minutes ago Created eager_hypatia
37188460428a e63baea27eb6 "/dksnap/entrypoint.…" 7 minutes ago Created condescending_ganguly
bb0c535d281d 651c9b429177 "/dksnap/entrypoint.…" 7 minutes ago Created ecstatic_feynman
14a005fe5af3 427842cf9f51 "/dksnap/entrypoint.…" 7 minutes ago Created optimistic_thompson
docker images:
REPOSITORY TAG IMAGE ID CREATED SIZE
post-edit latest a5dd3c62314d 3 minutes ago 361MB
pre-edit latest 094a056263b6 8 minutes ago 361MB
<none> <none> 245696944ad6 17 minutes ago 366MB
<none> <none> 2ef60475f4ea 18 minutes ago 366MB
mariadb latest b6184b68d1fd 11 days ago 357MB
docker volumes ls:
DRIVER VOLUME NAME
local 0bf48bdbee8f8afdbfbd35e7e9f1e7ce3a258491c7ea4f9bde8fff85bf60aa8e
local 00c777b9c248d51406af87d5afec3994bbe88e844aea2d5427e714f854b1ef76
local 2b20c46a8e81e2d3f1eb56505c0266befa639ed67b6882148b280242439cee2f
local 4ce9a7ca0a36a1ad79f1c444fd3fd924bbf318f379e88fa4f7420f6453ccee21
local 25c76e062b4cd6ada91ddcfd3f3dc156ed6dad7c2ddcd7fd99cd4a5de8d9fb80
local 41f5fc888da89cd1fb9401e9d318326100ae177363a683ac1554150f93bed3cc
local 48caf4639985c78bf7a57f9a058a818281af868585fba95861245a4c5859cff9
local 372cfd7f670756a0bc1425942a953816b0ce4bd95ce5cddc8a83eee69600b889
local 798a5288304df8890d87054b65e0ca22592da1f1f9f5d643b59d3acfdfbdb8ea
local 4680a7efe4cce7b822f68f92f6c43493c06937fa0ac46dd93183e72889c26152
local d11b7d861e97e0044016f871b06b1abd25bdbd5f281e054f4b9efb1588e020c1
local dabd8767959ed48ea2911e7af893c417df39f65d3c0265a1555fe07897e03950
local ecf937401bc4ddee113064f40591059b981e1d6d393ae93b40f34358c07e37f9
local f6d0e19dfcae9d92f854a5810f930ca88707a25465d197adff6292a5454ff347
local f99c50765d34993e7cd5277ac4ed470c43854a47edb470f9bb4fbaab887ead88
local fdd71f6c06d3524181460c5a5c055112dcb7eecdbdec15c6cdd76606bbaf520b
docker logs mariadb_mariadb_1
2020-05-06 16:00:19+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 1:10.4.12+maria~bionic started.
2020-05-06 16:00:19+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
2020-05-06 16:00:19+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 1:10.4.12+maria~bionic started.
2020-05-06 16:00:19+00:00 [Note] [Entrypoint]: Initializing database files
PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER !
To do so, start the server, then issue the following commands:
'/usr/bin/mysqladmin' -u root password 'new-password'
'/usr/bin/mysqladmin' -u root -h password 'new-password'
Alternatively you can run:
'/usr/bin/mysql_secure_installation'
which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.
See the MariaDB Knowledgebase at http://mariadb.com/kb or the
MySQL manual for more instructions.
Please report any problems at http://mariadb.org/jira
The latest information about MariaDB is available at http://mariadb.org/.
You can find additional information about the MySQL part at:
http://dev.mysql.com
Consider joining MariaDB's strong and vibrant community:
https://mariadb.org/get-involved/
2020-05-06 16:00:22+00:00 [Note] [Entrypoint]: Database files initialized
2020-05-06 16:00:22+00:00 [Note] [Entrypoint]: Starting temporary server
2020-05-06 16:00:22+00:00 [Note] [Entrypoint]: Waiting for server startup
2020-05-06 16:00:22 0 [Note] mysqld (mysqld 10.4.12-MariaDB-1:10.4.12+maria~bionic) starting as process 125 ...
2020-05-06 16:00:22 0 [Note] InnoDB: Using Linux native AIO
2020-05-06 16:00:22 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-05-06 16:00:22 0 [Note] InnoDB: Uses event mutexes
2020-05-06 16:00:22 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2020-05-06 16:00:22 0 [Note] InnoDB: Number of pools: 1
2020-05-06 16:00:22 0 [Note] InnoDB: Using SSE2 crc32 instructions
2020-05-06 16:00:22 0 [Note] mysqld: O_TMPFILE is not supported on /tmp (disabling future attempts)
2020-05-06 16:00:22 0 [Note] InnoDB: Initializing buffer pool, total size = 256M, instances = 1, chunk size = 128M
2020-05-06 16:00:22 0 [Note] InnoDB: Completed initialization of buffer pool
2020-05-06 16:00:22 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2020-05-06 16:00:22 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
2020-05-06 16:00:22 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2020-05-06 16:00:22 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2020-05-06 16:00:22 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2020-05-06 16:00:22 0 [Note] InnoDB: Waiting for purge to start
2020-05-06 16:00:22 0 [Note] InnoDB: 10.4.12 started; log sequence number 60972; transaction id 21
2020-05-06 16:00:22 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2020-05-06 16:00:22 0 [Note] Plugin 'FEEDBACK' is disabled.
2020-05-06 16:00:22 0 [Warning] 'user' entry 'root@39007184cede' ignored in --skip-name-resolve mode.
2020-05-06 16:00:22 0 [Warning] 'user' entry '@39007184cede' ignored in --skip-name-resolve mode.
2020-05-06 16:00:22 0 [Warning] 'proxies_priv' entry '@% root@39007184cede' ignored in --skip-name-resolve mode.
2020-05-06 16:00:22 0 [Note] InnoDB: Buffer pool(s) load completed at 200506 16:00:22
2020-05-06 16:00:22 0 [Note] Reading of all Master_info entries succeeded
2020-05-06 16:00:22 0 [Note] Added new Master_info '' to hash table
2020-05-06 16:00:22 0 [Note] mysqld: ready for connections.
Version: '10.4.12-MariaDB-1:10.4.12+maria~bionic' socket: '/var/run/mysqld/mysqld.sock' port: 0 mariadb.org binary distribution
2020-05-06 16:00:23+00:00 [Note] [Entrypoint]: Temporary server started.
Warning: Unable to load '/usr/share/zoneinfo/leap-seconds.list' as time zone. Skipping it.
2020-05-06 16:00:27 10 [Warning] 'proxies_priv' entry '@% root@39007184cede' ignored in --skip-name-resolve mode.
2020-05-06 16:00:27+00:00 [Note] [Entrypoint]: Creating database testdb
2020-05-06 16:00:27+00:00 [Note] [Entrypoint]: /usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/dump.sql
2020-05-06 16:00:28+00:00 [Note] [Entrypoint]: /usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/sql_dump_file.sql
ERROR 1050 (42S01) at line 30: Table 'Genre' already exists
Given the logging, I presume dksnap adds a dump.sql file to the container; but since the original sql_dump_file.sql is also mounted; mariadb tries to run them both. Which of course wouldn't work; and could also lead to data-loss (if CREATE OR REPLACE is used; or a DROP TABLE syntax). Your guess seemed to be accurate :-)
I assumed the massive amounts of sha-hash-containers had something to do with it breaking; but I didn't read the log of the failed container. My bad; since I could have figured out what was going wrong on my own then :-). However; I am still curious where all those extra containers come from, and why they are not cleaned up properly. This is adding a lot of unreadable clutter to the docker commands (and confusing novices like me :D)...
I can't seem to re-create my own error now. That's always fun. However, I am quite certain it would have had something to do with the failing of the dump.sql scripts.
Thanks for the info. That's right, dksnap adds a dump.sql.
That dump.sql makes your original sql_dump_file.sql file redundant since it's been applied by the time dksnap takes the snapshot. So as a workaround, I'd just remove your volume that mounts that file, and reference the image created by dksnap directly.
I agree, there seems to be more dksnap containers than I'd expect. docker-compose may be restarting them automatically after they crash