Tumblelog by Soup.io
Newer posts are loading.
You are at the newest post.
Click here to check if anything new just came in.

July 23 2018

04:11

Best practices for diffing two online MySQL databases

We’ve had to move our internal Red Hat Beaker instance to a new MySQL database version. We made the jump with a 5min downtime of Beaker. One of the items we wanted to make sure is to not to loose any data.

Setup and Motivation

A database dump is about 135 GB compressed with gzip. The main database was being served by a MySQL 5.1 master/slave setup.

We discussed two possible strategies for switching to MariaDB. Either a dump and load which meant a downtime of 16h, or the use of an additional MariaDB slave which will be promoted to the new master. We chose the latter: a new MariaDB 10.2 slave promoted to be the new master.

We wanted to make sure that both slaves, the MySQL 5.1 and new MariaDB 10.2, were in sync and with promoting the MariaDB 10.2 slave to master we would not loose any data. To verify data consistency across the slaves, we diffed both databases.

Diffing

I went through a few iterations of dumping and diffing. Here are the items, which worked best.

Ignore mysql-utils if you only have read access

MySQL comes with a bunch of utilities and one of them is a tool to compare two databases, called mysqldbcompare and mysqldiff. I’ve tried mysqldiff first, but, after studying the source code, decided against using it. Reason being is that you will have to grant it additional write privileges to the databases which are arguably small, but still too much I was comfortable with.

Use the “at” utility to schedule mysqldump

The best way I found to kick off performing the database dumps at the same time is to use at. Scheduling a mysqldump manually for the two databases introduces way too much noisy differences. I guess, it goes without mention, that the database hosts clocks are synchronized (e.g. by the use of chronyd).

Dump the entire database at once

The mysqldump tool can dump each table separately, but that is not what you want. Also the default options which are geared towards a dump and load is not what you want.

Instead I dumped MySQL with:

mysqldump --single-transaction --order-by-primary --skip-extended-insert beaker | gzip > mysql.sql.gz;

while for MariaDB I used:

mysqldump --order-by-primary --skip-extended-insert beaker | gzip > mariadb.sql.gz;

The options used are aiding the later diff:

  • –order-by-primary orders every dumped table row consistently by their primary keys
  • –single-transaction keeps a transaction open until the dump has finished so you get a comparable database snapshot across the two databases for the same starting point
  • –skip-extended-inserts is used to have an INSERT statement for each row, otherwise they’re collapsed to multi-row insert statements which are harder to compare

Compression (GZip) and shell pipes are your friend

With big databases, like the Beaker production database, you want to avoid writing anything uncompressed. Linux ships additional gzip wrappers for cat (zcat), less (zless) and so on, which will help with creating shell pipes in order to process the data.

Cut up the dump

Once you have both database dumps, cut them up into their separate tables. Purpose of this is not to sift through the dumps with your own eye, but rather to cater for diff. The diff tool loads the entire file into memory and you will face, with large database dumps, it is running out of memory quickly:

diff mysql-beaker.sql.gz mariadb-replica-beaker.sql.gz
diff: memory exhausted

While I did found a tool to diff both large files, having a unified diff output is easier to compare data with.

Example: Using gzip and a pipe from my point above:

diff -u <(zcat mysql/table1.sql.gz) <(zcat mariadb/table1.sql.gz) > diffed/table1.diff

Now you can use your SHELL foo to loop over all cut up tables and write the diff into a separate folder which then lets you easily compare.

June 30 2017

01:17
Debugging with RPM packages

April 14 2017

01:00
Profiling Haskell: Don’t chase the red herring

January 24 2017

23:40
Changing a website using the developer console

June 03 2016

16:49

August 07 2015

02:12
PyCon Australia 2015

May 23 2015

romanofski
11:47
6056 4047
revolution
Reposted frompotpants420 potpants420 viagetstoned getstoned

May 12 2015

00:31
git: Moving partial changes between commits

April 22 2015

23:51
Haskell: From N00b to Beginner

April 11 2015

romanofski
12:24
Reposted fromKrebs Krebs viaverschwoerer verschwoerer
romanofski
12:20
0316 9525 450
no time to stop, I shit in run
Reposted frombecurious becurious viagetstoned getstoned
12:17

Lorraine Loots’ Microscopic Watercolor Paintings Of The Cosmic Universe The Size Of Your Thumbnail

Lorraine Loots - Watercolor Lorraine Loots - Watercolor Lorraine Loots - Watercolor Lorraine Loots - Watercolor

Can you imagine trying to fit images of the cosmic universe into a circle only an inch, inch and a half wide? Artist Lorraine Loots accomplishes this with nothing more than watercolors and an incredible eye for detail. Watercolor is known for its unpredictable nature and organic qualities. Being able to control this medium in a realistic manner in such a small space speaks volumes to Loots artistic skill.  She renders her miniatures paintings on themed days throughout the year, completion date included.

In the series titled Microcosm Mondays, extremely tiny watercolor paintings depicting celestial images of outer space are created, one of which is a reference to a real photograph taken by the Hubble Space Telescope. This project gives us other equally clever names, each with their own mini-series. These include Tiny Tuesdays, Free Fridays, and with a play on words, Fursdays. Each series having a different theme, guess what this artist draws on Fursdays… cute little furry animals! All so incredibly detailed, down to the last hair and whisker. Each series is drawn on different days of the week, and at the end of the year, a total of 100 microscopic paintings will be completed. What makes Loot’s small masterpieces even more fun is that once one is completed, it is auctioned off on Instagram! So now there not only an element of surprise what day she will post her delicate piece, but also a factor of chance as you bid to have one for yourself. Don’t miss the action and check out Loots Instagram here. (via MyModernMet)

Lorraine Loots - WatercolorLorraine Loots - Watercolor Lorraine Loots - Watercolor Lorraine Loots - Watercolor Lorraine Loots - WatercolorLorraine Loots - Watercolor Lorraine Loots - Watercolor Lorraine Loots - Watercolor Lorraine Loots - Watercolor

 

The post Lorraine Loots’ Microscopic Watercolor Paintings Of The Cosmic Universe The Size Of Your Thumbnail appeared first on Beautiful/Decay Artist & Design.

Reposted fromcuty cuty viagetstoned getstoned
romanofski
12:10
6185 be2c
Reposted fromKane1337 Kane1337 viagetstoned getstoned

April 03 2015

romanofski
04:47
8768 8013 450
beer drinks
Reposted frommajkey majkey viatomster tomster
romanofski
04:47
8768 8013 450
beer drinks
Reposted frommajkey majkey viatomster tomster
04:41
1431 5cba 450

bloodtributes:

kirstenelizabethh:

computerfag:

Handwriting goals.

This is so aesthetically pleasing.

This isn’t possible

Reposted fromKillah883 Killah883 viagetstoned getstoned

March 20 2015

romanofski
11:47

February 12 2015

romanofski
20:12
Reposted fromkrolik krolik viamonkeyvault monkeyvault

February 06 2015

23:40
4848 5999 450

qoax:

sociallubrication:

The Lions Mane Jellyfish is the largest jellyfish in the world. They have been swimming in arctic waters since before dinosaurs (over 650 million years ago) and are among some of the oldest surviving species in the world.

Absolutely wow.

I’m going to cry

Reposted fromviirus viirus viagetstoned getstoned

January 31 2015

romanofski
03:43
Older posts are this way If this message doesn't go away, click anywhere on the page to continue loading posts.
Could not load more posts
Maybe Soup is currently being updated? I'll try again automatically in a few seconds...
Just a second, loading more posts...
You've reached the end.

Don't be the product, buy the product!

Schweinderl