CodeVoyager

MySQL Data & Schema Mangling

Edit Page

MySQL Data & Schema Mangling

Subsetting - Extracting subset of data (in a consistent way, foreign keys etc)

Versioning - Tracking the history of database schema - Schema Synchronization - Schema Versioning

Data Synchronization - Database Migrations - Dump / Restore data

Three Rules for Database Work

Database Subsetting

Woow: Open Jail - The Jailer Project Web Site DbUnit and Jailer « Don’t Panic!

Commercial tool: Powerful Test Data Creation, Data Masking and Test Data Management

TOAD’s Data Subset Wizard is a simple four-step utility for automatically generating relationally correct and efficient SQL scripts to copy a user-defined percentage of data from target to source database. Toad World Blogs - Toad Subsets Data

Mechanics of Flite - 3 Methods to Extract a Subset of Your Data Using mysqldump mysql - Is it possible to mysqldump a subset of a database required to reproduce a query? - Database Administrators

Jailer: Java connectivity with MySQL - Stack Overflow

Relational Migration Data Subset - Informatica

Database Versioning / Schema Sync / Schema Diff

Database Version Control – techPortal Do you use source control for your database items? - Stack Overflow

Liquibase | Database Refactoring | home dbdeploy.com solidbase - A database change management and version control tool - Google Project Hosting

Lots of tools: Flyway: the agile database migration framework for Java davedevelopment/phpmig · GitHub octalforty-wizardby - Database continuous integration for the .NET Framework - Google Project Hosting

DBRIRE

Perl: / SQLFairy John Goulah » SQL::Translator dbsrgits/sql-translator · GitHub Easy visualisation of database schemas using SQLFairy | What You’re Doing Is Rather Desperate Visualize MySQL Database on Ubuntu – PNG Dump : www.solutioning.eu SQL::Translator::Manual - search.cpan.org http://search.cpan.org/dist/SQL-Translator/

Schema Diff Tool mysqldiff – a utility for comparing MySQL database structures aspiers/mysqldiff · GitHub

In PHP, really good and simple one: caviola/mysqldiff · GitHub

Schema Diff in PHP: iamcal/SchemaDiff · GitHub This is really stupid tool - simply a wrapper around textual Diff.

Python: sql-dump-schema-diff - Compares two DB schemas from SQL dump files - Google Project Hosting

  • Note, selecting ’libsql-translator-perl’ instead of ‘sqlfairy’

    Latest: SQL-Translator-0.11016, from 09 Oct 2012 http://vapour.s22.xrea.com/phpdojo/index.php?SQL-Translater

    sqlt-diagram: interface to Diagram producer sqlt-diff : diff two schemas to generate schema mutation file sqlt-graph : interface to GraphViz producer sqlt-dumper : create a data dumper script from a schema sqlt : command-line interface for text-to-text translations sqlt.cgi : CGI interface for all SQLFairy functions

    apt-get install sqlfairy (version is 0.11011-1)

WTF is this from Facebook: Online Schema Change for MySQL | Facebook openarkkit - Common utilities for MySQL - Google Project Hosting

Database Migrations / Data Sync

Ruby on Rails has db.migration http://guides.rubyonrails.org/migrations.html :: Ruby on Rails Guides: Migrations

http://www.devco.net/archives/2009/12/14/splitting_mysql_dumps_by_table_-_take_2.php :: Splitting MySQL dumps by table – take 2 | R.I.Pienaar

In PHP: http://stackoverflow.com/a/3324589/1579985

http://phinx.org/ :: Phinx

https://github.com/davejkiger/mysql-php-migrations :: davejkiger/mysql-php-migrations · GitHub https://github.com/Billiam/MySQL-PHP-AutoMigrations :: Billiam/MySQL-PHP-AutoMigrations · GitHub

https://github.com/apinstein/mp :: apinstein/mp · GitHub https://github.com/nickinuse/php-migrations :: nickinuse/php-migrations · GitHub

http://drarok.com/ladder/ :: Ladder - A PHP database migration tool https://github.com/alwex/php-database-migration :: alwex/php-database-migration · GitHub

OnlineBuddies/Modyllic · GitHub :: OnlineBuddies/Modyllic · GitHub

Mysqldump / Simple Migrations :)

Ruby script that splits a MySQL dump into a file per table. ripienaar/mysql-dump-split · GitHub

Dump / Restore

Not really needed: Fast MySQL Backup Restoration

Smarter, faster backups and restores of MySQL databases using mysqldump - and other useful tips

deviantintegral/mysql-parallel · GitHub Fast, Parallel MySQL Backups and Imports with Mydumper | 2bits.com, Inc. - Drupal Performance Optimization, Development, Managed Hosting, Customization and Consulting Scuzzbopper/Yet-Another-MySQL-Backup · GitHub MySQL Back up & Restore: Faster InnoDB database backup / restore using Mydumper and Myloader multi-threaded tools | vbtechsupport.com

Neće da može (bajata verzija): apt-get install mydumper

mydumper –compress –verbose=3 –no-schemas –threads=4 –outputdir=/srv/etc/tmp/mydumper/m2 –host=utpc-office.dyndns-server.com –user=dgvip –password=45c1ce4de1b30057cd32c476e552622cd57e5de3 –database=datingvip_m2 –tables-list=ads_networks_views mydumper –compress –no-locks –verbose=3 –no-schemas –threads=4 –outputdir=/srv/etc/tmp/mydumper –host=utpc-office.dyndns-server.com –user=dgvip –password=45c1ce4de1b30057cd32c476e552622cd57e5de3 –database=datingvip_m2

mydumper –compress –verbose=3 –threads=8 –outputdir=/srv/etc/tmp/mydumper/myserver –host=savioko.com –user=dgvip –password=45c1ce4de1b30057cd32c476e552622cd57e5de3 –database=datingvip_m2

-T, –tables-list Comma delimited table list to dump (does not exclude regex option) -s, –statement-size Attempted size of INSERT statement in bytes, default 1000000 -x, –regex Regular expression for ‘db.table’ matching -r, –rows Try to split tables into chunks of this many rows -b, –binlogs Get a snapshot of the binary logs as well as dump data

-e, –build-empty-files Build dump files even if no data available from table -i, –ignore-engines Comma delimited list of storage engines to ignore -k, –no-locks Do not execute the temporary shared read lock. WARNING: This will cause inconsistent backups -l, –long-query-guard Set long query timer in seconds, default 60 –kill-long-queries Kill long running queries (instead of aborting) -D, –daemon Enable daemon mode -I, –snapshot-interval Interval between each dump snapshot (in minutes), requires –daemon, default 60 -L, –logfile Log file name to use, by default stdout is used -P, –port TCP/IP port to connect to -S, –socket UNIX domain socket file to use for connection -V, –version Show the program version and exit -v, –verbose Verbosity of output, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2

Chef: vagrant-build-mydumper/modules/build/manifests/init.pp at master · kwbr/vagrant-build-mydumper · GitHub

How to install, really:

apt-get install build-essential cmake libglib2.0-dev libmysqlclient-dev zlib1g-dev libpcre3-dev rubygems
gem install fpm

Fix some problems for new MySQL: MySQL 5.5 has removed the separate libmysqlclient_r library and creates symlinks for compatibility’s sake. But, it’s not doing it right.

Btw, libmysqlclient_r.a is “re-entrant” = is guaranteed to be thread-safe per connection. Now, there is no difference in newer versions (both are re-entrant). Newer versions of the MySQL client distributions do not include the _r version. Some may have a symbolic link from libmyqslclient_r.a to libmyqslclient.a

cd /usr/lib/x86_64-linux-gnu
ln -s libmysqlclient.a libmysqlclient_r.a
ln -s libmysqlclient.so libmysqlclient_r.so

That fixed it.

wget -O /tmp/mydumper.tar.gz https://launchpad.net/mydumper/0.5/0.5.2/+download/mydumper-0.5.2.tar.gz
tar xvf /tmp/mydumper.tar.gz -C /opt

cd /opt/mydumper-*
cmake .
make
make install

It’s installed

# Create a package from that
/var/lib/gems/*/bin/fpm -s dir -n mydumper -v 0.5.2 -C "/opt/mydumper" -t deb --prefix /usr --url http://www.mydumper.org/ --description "A high-performance MySQL backup tool." .

# Package is here
ll /opt/mydumper-*/mydumper_*.deb

Later, to install (there are multiple ways, but this is the best):

apt-get install gdebi-core
cd /opt/mydumper-0.5.2
gdebi mydumper*.deb

rm -rf /opt/mydumper*

Execute like:

/usr/local/bin/mydumper
/usr/local/bin/myloader

Na 2Mb upload speed, radio je 25 minuta baze veličine oko 2.7GB. Znači otprilike. Istom brzinom je išao i na 1 i na više threadova. Znači, ograničenje je bio network.

Progress bar: mysqldump - How far along is the importation of my MySQL dump? - Stack Overflow pv - Pipe Viewer - is a terminal-based tool for monitoring the progress of data through a pipeline.

Bandwidth Limit / Traffic Shaper

trickle How I Manage Bandwidth Using Trickle On Ubuntu - Ubuntu Portal

Execute command on multiple files

Except on .gz?

find . -type f -not -path '*.gz' -exec ls {} \;

Speed of a MySQL import

MySQL - Speed of INSERT Statements MySQL :: MySQL 5.5 Reference Manual :: 8.5.4 Bulk Data Loading for InnoDB Tables

Tested database: datingvip_m2: Packed file 200M, unpacked really 1.4G, takes up MySQL disk space 3.5G

Prepare for timing: mysql -uroot -pAnyPassword -e “DROP DATABASE IF EXISTS datingvip_m2; CREATE DATABASE datingvip_m2;”

  1. .sh script: time ./mysqlrestore.sh datingvip_m2 21 minutes from .sh, size with partitions 3.5G

  2. SOURCE command: time mysql -uroot -pAnyPassword -e "USE datingvip_m2; SOURCE datingvip_m2.sql;" 20 minutes (the same)

  3. Mydumper: time /usr/local/bin/myloader --compress-protocol --verbose=3 --threads=8 --overwrite-tables --directory=m2 --host=localhost --user=dgvip --password=45c1ce4de1b30057cd32c476e552622cd57e5de3 --database=datingvip_m2 Ok. This one IS the fastest. 10 minutes! A lot faster!

Clear winner is: Mydumper!

-

5 Things You Overlooked with MySQL Dumps

Percona Xtrabackup

The fastest restore / almost instant restore is with Percona Xtrabackup.

Percona Xtrabackup

Script examples: Fridus/run-xtrabackup · GitHub olc/auto_repl · GitHub

On Hot Backups and Restore using XtraBackup | ovais.tariq Painless, ultra fast hot backups and restores of MySQL databases with Percona’s XtraBackup iask/myxtrabackupex · GitHub

xtrabackup works only on innobackupex is wrappers script, that works both for InnoDB and MyISAM.

How to use

Backup

xtrabackup --backup --datadir=/var/lib/mysql/ --target-dir=/data/backups/mysql/

xtrabackup will not overwrite existing files

Tricks

Exclude Tables

Schema Visualization

SchemaSpy webERP Forum - Create a WebERP database Schema

Just interesting - writing UML diagrams in browser: wwwsqldesigner - Visual web-based SQL modelling tool - Google Project Hosting

Using PHP mysqldiff

wget https://raw.github.com/caviola/mysqldiff/master/mysqldiff.php

my :root@local -e “CREATE DATABASE datingvip_d1;” my :root@local -e “DROP DATABASE IF EXISTS $2; CREATE DATABASE $2;”

php mysqldiff.php
–database1 datingvip_m1 –host1 utpc-office.dyndns-server.com
–user1 dgvip –pwd1 45c1ce4de1b30057cd32c476e552622cd57e5de3
–database2 datingvip_m1 –host2 localhost
–user2 root –pwd2 devbox
–drop-tables –drop-columns –overwrite
–output-file datingvip_m1-diff.sql

Importing that with disabling checks… my :root@local -e “SET autocommit=0; SET unique_checks=0; SET foreign_key_checks=0; SOURCE datingvip_m1-diff.sql; COMMIT; SET autocommit=1; SET unique_checks=1; SET foreign_key_checks=1;”

put this in a script:

mydiff_write2file () { local db="${1}"

php mysqldiff.php
–database1 $db –host1 utpc-office.dyndns-server.com
–user1 dgvip –pwd1 45c1ce4de1b30057cd32c476e552622cd57e5de3
–database2 $db –host2 localhost
–user2 root –pwd2 devbox
–drop-tables –drop-columns –overwrite
–output-file “${db}-diff.sql” }

mydiff_import () { local db="${1}" my :root@local -e “SET autocommit=0; SET unique_checks=0; SET foreign_key_checks=0; USE ${db}; SOURCE ${db}-structure.sql; COMMIT; SET autocommit=1; SET unique_checks=1; SET foreign_key_checks=1;” }

mydiff_write2file “datingvip_w1” mydiff_write2file “datingvip_m1” mydiff_write2file “datingvip_a1”

mydiff_import “datingvip_w1” mydiff_import “datingvip_m1” mydiff_import “datingvip_a1” my :root@local -e “DROP DATABASE IF EXISTS datingvip_d1; CREATE DATABASE datingvip_d1;”

my :root@local -e “CREATE USER ‘dgvip_tester’@’localhost’ IDENTIFIED BY ‘dgvip_unit_test’;”

Must have privileges both on source and on test dbs

my :root@local -e “GRANT ALL PRIVILEGES ON . TO ‘dgvip_tester’@’localhost’ WITH GRANT OPTION”

E: PHP Fatal error: Allowed memory size of 268435456 bytes exhausted (tried to allocate 565 bytes) i A: echo “memory_limit = 512M” »/etc/php5/conf.d/90-newsettings.ini PROBLEM: ./inc/_bootstrap_defaults.php:ini_set (‘memory_limit’, ‘256M’);

E: increase post_max_size and upload_max_filesize to 30M A: echo “post_max_size = 30M” »/etc/php5/conf.d/90-newsettings.ini echo “upload_max_filesize = 30M” »/etc/php5/conf.d/90-newsettings.ini

Ok. something in the database is wrong

:: Copying DB structure

db=datingvip_w1 mysqldump –defaults-group-suffix=:dgvip@utp –set-gtid-purged=OFF –no-data –single-transaction –result-file="${db}-structure.sql" “${db}”

my :root@local -e “DROP DATABASE IF EXISTS ${db}; CREATE DATABASE ${db};” my :root@local -e “USE ${db}; SOURCE ${db}-structure.sql;”

WE CANT HAVE PARTITIONS… I THINK. THEIR’RE TOO SLOW…

Delete everything: for suffix in w1 m1 a1; do db=“datingvip_$suffix” && my :root@local -e “DROP DATABASE IF EXISTS ${db}; CREATE DATABASE ${db};”; done

db=datingvip_m1 cat “${db}-structure.sql”
| sed -e ’s/!50017 DEFINER=[^\]/!50017 DEFINER=CURRENT_USER/’
| sed -e ‘N;s|/*!50100[^\]*/|/* PARTITION WAS HERE! */|;$!P;$!D’
> “${db}-structure-cleaned.sql”

Import all for suffix in w1 m1 a1; do db=“datingvip_$suffix” && my :root@local -e “USE ${db}; SOURCE ${db}-structure-cleaned.sql;”; done

cat datingvip_a1-structure | sed -e ‘N;s|/*!50100[^\]*/|/* XXX WAS HERE! */|;$!P;$!D’ | grep -i PARTITION

echo " user=root pass=devbox host=localhost db=test delay=5 port=3306 socket= batchmode=0 header=1 color=1 idle=1 " > ~/.mytop

mytop

alter-local.sh je komanda za azuriranje lokalnog radi samo m1 i a1 ne treba joj nista, osim sto u njoj treba zameniti pass

ona cita iz fajla alter-all.sql

date 16. May 2013 | modified 29. Dec 2023
filename: MySQL » Data & Schema Mangling
Article Content