Jan 25 2009

Quick DB Setups with MySQL Sandbox

Category: Databasesjgoulah @ 2:57 PM

Introduction

There are various reasons to setup quick “sandbox” instances of MySQL. You can use them to test different types of replication (such as master-master or various slave topologies), to test your code against different versions of MySQL, or to setup instances of MySQL on a per developer basis where each person has their own database running on a different port so they can breakdown/setup the DB easily or make schema changes without affecting other team members. A perfect tool to do all of these things easily is MySQL Sandbox.

Download the Prerequisites

To use MySQL sandbox effectively you need two things, the MySQL sandbox tool itself, and a MySQL tarball that the sandbox script can use to setup instances.

You can download the latest MySQL sandbox version like so (2.0.12 as of this writing):

wget http://launchpad.net/mysql-sandbox/mysql-sandbox-2.0/2.0/+download/mysql_sandbox_2.0.12.tar.gz

You can download the MySQL tarball from any MySQL mirror. Its important to get the non RPM, Intel C/C++ compiled, glibc-2.3 version (look for a tar.gz file with “icc” in the filename) for example if you want version 5.1:

wget ftp://mirror.anl.gov/pub/mysql/Downloads/MySQL-5.1/mysql-5.1.30-linux-i686-icc-glibc23.tar.gz

Installing an Instance with MySQL Sandbox

First you need to extract the MySQL Sandbox tool and change directory to it:

$ tar xzvf mysql_sandbox_2.0.12.tar.gz
$ cd mysql_sandbox_2.0.12

The easiest and quickest way to create an instance is:

$ ./make_sandbox /path/to/mysql-X.X.XX-osinfo.tar.gz

where mysql-X.X.XX-osinfo.tar.gz is the MySQL tarball we just downloaded. And you’re done.

However, this will put the sandbox in a directory under your home directory ($HOME/sandboxes/msb_X_X_XX), which may or may not suit your purposes. It sets it up with default users, passwords, ports, and directory name. Lets fine tune things a bit.

Setting up a Custom Tuned Instance

I want to put my instance into a partition I created called /mnt/mysql_sandboxes. I’ve created a subdirectory in there called tarballs, which holds the MySQL tarball that we downloaded above which MySQL Sandbox will extract for setup. Since I’m installing version 5.1.30 I want to call the directory that houses the MySQL data files 5.1.30_single, but you can call it anything you like. I’ll create a default user named jgoulah and a password goulah. By default it sets the port to the version number without the dots (5130 in this case) so we’ll give it a custom port so that it listens on 10000 instead.

mysql_sandbox_2.0.12 $  ./make_sandbox \
/mnt/mysql_sandboxes/tarballs/mysql-5.1.30-linux-i686-icc-glibc23.tar.gz \
--upper_directory=/mnt/mysql_sandboxes/ --sandbox_directory=5.1.30_single \
--db_user=jgoulah --db_password=goulah --sandbox_port=10000

Here’s the output:

unpacking /mnt/mysql_sandboxes/tarballs/mysql-5.1.30-linux-i686-icc-glibc23.tar.gz
Executing ./low_level_make_sandbox \
        --basedir=/mnt/mysql_sandboxes/tarballs/5.1.30 \
        --sandbox_directory=msb_5_1_30 \
        --install_version=5.1 \
        --sandbox_port=5130 \
        --no_ver_after_name \
        --upper_directory=/mnt/mysql_sandboxes/ \
        --sandbox_directory=5.1.30_single \
        --db_user=jgoulah \
        --db_password=goulah \
        --basedir=/mnt/mysql_sandboxes/tarballs/5.1.30 \
        --sandbox_port=10000 \
        --my_clause=log-error=msandbox.err
    The MySQL Sandbox,  version 2.0.12 16-Oct-2008
    (C) 2006,2007,2008 Giuseppe Maxia, Sun Microsystems, Database Group
installing with the following parameters:
upper_directory                = /mnt/mysql_sandboxes/
sandbox_directory              = 5.1.30_single
sandbox_port                   = 10000
datadir_from                   = script
install_version                = 5.1
basedir                        = /mnt/mysql_sandboxes/tarballs/5.1.30
my_file                        =
operating_system_user          = jgoulah
db_user                        = jgoulah
db_password                    = goulah
my_clause                      = log-error=msandbox.err
prompt_prefix                  = mysql
prompt_body                    =  [\h] {\u} (\d) > '
force                          = 0
no_ver_after_name              = 1
verbose                        = 0
load_grants                    = 1
no_load_grants                 = 0
do you agree? ([Y],n) y
loading grants
. sandbox server started
installation options saved to current_options.conf.
To repeat this installation with the same options,
use ./low_level_make_sandbox --conf_file=current_options.conf
----------------------------------------
Your sandbox server was installed in /mnt/mysql_sandboxes//5.1.30_single

Its now installed and started up, we can see that the process is running with the correct options:

$  ps -ef | grep mysql | grep jgoulah
jgoulah  11128     1  0 13:48 pts/3    00:00:00 /bin/sh /mnt/mysql_sandboxes/tarballs/5.1.30/bin/mysqld_safe --defaults-file=/mnt/mysql_sandboxes//5.1.30_single/my.sandbox.cnf
jgoulah  11203 11128  0 13:48 pts/3    00:00:00 /mnt/mysql_sandboxes/tarballs/5.1.30/bin/mysqld --defaults-file=/mnt/mysql_sandboxes//5.1.30_single/my.sandbox.cnf --basedir=/mnt/mysql_sandboxes/tarballs/5.1.30 --datadir=/mnt/mysql_sandboxes//5.1.30_single/data --user=jgoulah --log-error=/mnt/mysql_sandboxes//5.1.30_single/data/msandbox.err --pid-file=/mnt/mysql_sandboxes//5.1.30_single/data/mysql_sandbox10000.pid --socket=/tmp/mysql_sandbox10000.sock --port=10000

And we can connect to it on the port 10000:

$  mysql -u jgoulah --protocol=TCP  -P 10000 -pgoulah

You can also go into the directory where we’ve installed this and there are some convenience scripts:

$ cd /mnt/mysql_sandboxes/5.1.30_single/

You can run the use script to connect into mysql (same thing we just did above except we don’t have to remember our port, user, or pass):

$ ./use
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.30 MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql [localhost] {jgoulah} ((none)) >

Stop the instance:

$  ./stop

Or start it back up again:

$  ./start
. sandbox server started

There are a few other scripts which you can experiment with in this directory which are documented here

Setting up a Replicated Instance

The nice thing about this tool is it will also setup replicated instances of MySQL with a single command. This allows you to test your application under a replicated environment, or even test different replication topologies including multiple slaves or multi-master replication.

We’ll use similar options as the single instance above, except we’ll use port 11000 this time (the slaves get port + 1, …, port + n where n is number of slaves). We’ll put the install into /mnt/mysql_sandboxes/5.1.30_replicated. Note this time we use the make_replication_sandbox script:

mysql_sandbox_2.0.12 $  ./make_replication_sandbox \
/mnt/mysql_sandboxes/tarballs/mysql-5.1.30-linux-i686-icc-glibc23.tar.gz \
--upper_directory=/mnt/mysql_sandboxes/ \
--replication_directory=5.1.30_replicated --sandbox_base_port=11000
installing and starting master
installing slave 1
installing slave 2
starting slave 1
. sandbox server started
starting slave 2
. sandbox server started
initializing slave 1
initializing slave 2
replication directory installed on /mnt/mysql_sandboxes//5.1.30_replicated

Now we have a master and two slaves going. Note that the command to setup replicated sandboxes will not let us specify the user as we did with the single instance, but two users are created by default:

User: root@localhost Password: msandbox
User: msandbox@% Password: msandbox

You can run the use script as shown above, or connect directly to the master:

$  mysql -u msandbox --protocol=TCP  -P 11000 -pmsandbox

Create a database:

mysql> create database jg_repl_test;
mysql> exit;

Connect to one of the slaves:

$  mysql -u msandbox --protocol=TCP  -P 11001 -pmsandbox
mysql> show databases like '%jg_%';
+------------------+
| Database (%jg_%) |
+------------------+
| jg_repl_test     |
+------------------+

And we can see that it has replicated across.

There are various options you can give the make_replication_sandbox command, for example you can give it the –master_master option to setup a multi master instance.

Conclusion

We’ve seen how to create a single instance of MySQL and also a replicated master with two slaves. Each of these takes only a few seconds to setup once you have the directory layout decided. There really isn’t a much easier way to setup scratch instances of MySQL for all kinds of different purposes.

Tags:

6 Responses to “Quick DB Setups with MySQL Sandbox”

  1. Ila says:

    Nice Tutorial. Thanks! It really helped me setting up MySQL sandbox and connect to it locally using mysql client and also using JDBC. But I have no luck connecting to it from another machine in the same network. It gives the following error
    ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.73.22' (113)
    Or when I try using jdbc to connect remotely, it gives the following exception:

    An error occurred while establishing the connection:
    Type: com.mysql.jdbc.CommunicationsException Error Code: 0 SQL State: 08S01

  2. jgoulah says:

    Show the command you’re using to connect?

  3. Ila says:

    Sorry i couldn’t reply earlier. When I installed the sandboxed MySQL (version 5.1.32), the installation process created a directory named ’5.1.32′ . I navigate to this directory and execute the following code:

    ./mysql -u root --protocol=TCP -P 5132 -p

    or

    ./mysql -u root --protocol=TCP -P 5132 -p -h localhost

    The above code(s) works perfectly by asking the password and allowing me to login and I get to the mysql prompt

    However if i execute the same command from another machine in the same network but with a host attribute, then it is not working. Here is the command i am issuing

    mysql -u root --protocol=TCP -P 5132 -p -h 192.168.73.22

    and this is the error i am getting:
    ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.73.22' (113)

    Any help/pointers to solve this problem would be much appreciated. Thanks!

  4. Ila says:

    Ok. I found the problem.. In the end, it was the firewall rules in the machine that hosts MySQL sandbox. I have to open up the port 5132 to allow remote machine to connect to. Good Tutorial! It really helped me setting up my machine. Thanks again!

  5. Tools4Mac: MySQL Sandbox | Frankys Blog says:

    [...] einer produktiv, sondern in einer Sandbox Umgebung. Ein schönes Tutorial hat John Goulah in seinem Blog dazu verfasst. Gerade für den Master -> Slave Betrieb bzw. die Replikation der Datenbanken auf [...]

Leave a Reply

You must be logged in to post a comment.



download xiuxiu editor foto shambho shankara mp3 free download pikeno e menor perdicao download download lagu surat at taubat smarthru 4 download pl abbey road 60s drums download mac cell phone repair download download kara winter magic album download intel gma booster terbaru download mp3 tantowi yahya free download farhan ali qadri video naats free download internal medicine harrison download music ragheb alama sinin dhada songs download in ziddu download form 4852 from the irs website free invitation templates download and print download boundless by cynthia hand free la chatimi cantare mp3 download free download of deception point ebook download munni badnam hui free mp3 download jtx party like a rockstar download hide ip ng 1.40 download ooh la la goldfrapp download time after time quietdrive crime and punishment mp3 download sweety gippy mp3 download caminhos da liberdade download minions banana video download reflex flugsimulator download gratis naruto shippuden 208 pt download sniper elite wii download ntsc pirata do espaço download dublado download ptanks full version o justiceiro download pc rip microsoft hda driver download download admit card iti jharkhand 2012 bada 2.0 download wave 2 download gangster life gta apple safari 4 x download business intelligence download oracle download os x dvd installer download gta 8 vice city myegy szybcy i wściekli 5 2011 download download highschool of the dead color download shakira ft pitbull rabiosa zippy download lagu t ara zombie jelly car music download download driver sony vaio 32bit klezmer music to download free download of shreenathji bhajan aga bai arechya download serie v 3 temporada download download the jeremy kyle show download jogos fazenda gratis pc minimizer download para mu susana nothing at all download download um novo vencedor damares playback download command line mac os download paypal jar for android download zeljko vasic zanjisi kukovima marian keyes watermelon ebook download internet download manager flurry icon kruti dev free download windows download chief keef choppa go bang download msi 3.1 windows installer chessmaster free download with crack bravo hits 98 download download disrespect kirko bangz x plane 6 demo download la baby jonas brothers mp3 download netgear ga311 windows 7 download free download habib painter mp3 download tweetdeck desktop windows 7 ekhon ami mp3 download promethean the created download pdf how to download youtube videos to ipad amazon download games steam cypress hill download 2011 download treu nha hang xom 2 download kick out the epic mother wooh da kid stepped download x2 x men united download imaginasamba perfeição download download navigation for mobile zor ka jhatka song download mp3 download audi a4 owners manual download pretty little liars s01e05 avi download nitro circus season 1 download eega promotional song download the simulator 2012 demo x264 codec download mac download lições para toda vida legendado download mkisofs for ubuntu download star trek voyager scorpion lil b 855 download download pokemon black 2 jap rom spells aprilynne pike pdf download ita download ways of reading cursed crusader trainer download download darmowe gry dla dzieci download spyglass for android alaa wardi 7aram free download massive attack teardrop song download download settings for nokia x6 download account opening form obc download fl studio on a mac worth dying for download s bot download free silkroad 1 click downloader download 5 ishq ka sheen download download melodia que eu conheço stephen king novels download free 100 download psp games for free a escolha download dublado ptgui 9 mac download kz hack download gratis download driver yamaha psr 3000 download macroeconomics policy and practice download hp dv1000 sound driver download famous five movies download phim benh nhan nguoi anh download gta 1 for free download gangs of wasseypur movie free download panasonic sd jukebox software download falling up drake download trackmania sunrise extreme full version free download de simuladores de combate aereo download i bruise easily download pioneer dj software free download noah and the whale life goes on mp3 download recover deleted files software download sơ đồ kế toán download free desk phone ringtone fußball manager 12 download vollversion kostenlos even greater mp3 download planetshakers download snmp for windows 2000 free download amuse park game bejeweled 3 jar download sleeping at last quicksand download download ứng dụng cho nokia n8 coral player download luna download jill scott whenever you're around download fairy tail games for pc download photoshop 8 cs me myegy download yahoo latest version for free mp7 player free download mac download internet explorer 8 vista java hry download 128x160 download jason upton key of david download march of the wooden soldiers download barad toro be dast avordam mp3 download songs of aitraaz from songs pk com 6.72 f ai nightmare download download tiny toon adventures nes download i'm yours mp3 download 9 hours rom pipi player download vista deskjet d2360 software download download booster pack hack abhas ha mp3 song download mp3 songs download 3gp download cod 4 3rd person mod trey songz blind download free download hawaii 5 0 season 2 kenji free download fort minor download clubbed to death 2 quebrando regras 2 download portugues chamas da vingança 1984 download download sketchup 8 deutsch virtual router manager download xp download instalador chrome offline download soundgarden live to rise download canon mx410 printer driver download the harold song kesha mp3 garmin 255w download maps free download mise a jour mcafee download visio windows 7 64 bit download gen psp 3000 quasi amici film download gratis ita download plano de fuga rmvb legendado download alfonso loher name in the sky download mp3 zahra damariva alasan download j rock songs download bihar secretariat assistant admit card download project 64 64 bits download naruto chapter 589 cartoon download for ipad download tributo a bezerra da silva download roda a roda jogo cx one full download armin van buuren rapture download mp3 download nein mann video download tambor de funk download rota de fuga rmvb free download of shawty got moves mp3 download cm7 for droid 2 global download executive resume format amnesia game download mac crash bandicoot mutant island download pc ipod touch download pictures to computer download tu pirata soy yo chayanne download lg pc suite p990 harmor vst plugin download download abaqus 6.10 student edition i like cereal song download filmes alta resolucao download italian lessons download mp3 mass effect 3 download pirate download manager idm key download wh cs 2011 bau simulator download ita download efek suara unik download girl talk ultraviolet sound download bicara hati episod 4 annie khalid songs download download driver epson cx5500 free quake 3 download bots infinity blade 2 ipa download crack pk songs download list dream the game download free cydia download step by step motorola xoom rom download download let's go ricky luna remix zune manual download pdf download hit and run 2012 dvdrip download do sapo videos download do jogo cities xl 2011 download song socha na tha by alamgir ktechlab for ubuntu download download vara rece kamelia zippy download schenk mir dein herz gipsy kings volare download free welsh flag to download download afinador do cifra club 3 gatsu 9 ka download free mp3 angry birds android download download manager error the server returned an error download sdo x season 2 song download paint shop pro download turn to u justin bieber download original ruu for evo download maps to print boys over flowers download songs download free regular show episodes download yamaha psr 1500 styles satyamev jayate download song download mouse fix for windows xp download elliot in the morning kenapa tidak bisa download film h.p f4200 printer software download p square game over download mp3 carpet 3d max download download mw 3 1.07 patch the legend of zelda download snes rom download 9 temporada friends download on my freebox ne fonctionne pas download tito lopez the blues sharebeast download sweeney todd final scene papago x8.5 wince 5 download download monkey for rhino download na paz de jah download gratuito adobe reader 8 ngo accounting software download download onto mp3 from youtube somebody's me mp3 download enrique download ocarina of time 1.0 download 64 bit windows tax form 8379 download frisky tinie tempa download zippy download pro update psp go 6.60 download lagu chrisye gejolak cinta download themes for gw300 clr via c# richter download download 8195 the damned rar parayathe ariyathe malayalam song download autobiography of abraham lincoln download download phim hiep dao hoa hetaoni english download part 2 cenário de novela download mp3 sandra brown envy download pdf download sims 1 love bed sende ahasa wage mp3 download download voice changing application download feed us 2 free download open source library management system download alana grace black roses red download audi a3 manual download free irctc mobile application stand o food 3 apk download download dss dj effects visual basic software download download film khuda kay liye download intezar remix by falak sri rama rajyam mp3 download download dan seals one friend download virtual families mac free download gossip girl cecily von ziegesar download autoramas fale mal de mim gabin doo uap mp3 download free download love in this club mp3 download dead space 3 demo pc charmed download season 1 nhac chuong theo ten download angry birds season download free pc reign of hunters download download de pokemon flash download full screen theme wordpress download manager 6.05 crack download planta x zumbi download razor ramon entrance music download skype xperia 8 download lagu tercipta untukku ungu download shining inheritance ep 16 trial download microsoft project 2010 halo ce download key thermodynamics 6th cengel download vmware ova converter download download e.r legendado 1 temporada wilfred season one download download hivi mata hati download apun bola mp3 zmierzch księżyc w nowiu download peb cod tool download 1.5 web client get download speed ryback meat mp3 download music download on itunes download beenie man i'm okay vigilante 8 2nd offence download pc download movie 2012 in hindi latha tamil font free download word melhor impossivel download legendado download lloyds tsb bank statement download oki b6300 driver how to download spoutcraft free popup blocker download google chrome download tenth avenue north losing download sound intervention mw2 download jogo harry potter pc download pokemon blanc nds alda célia playback download download shwayze get you home download tower bloxx mobile game dani california official video download download jump out the gym download all killer no filler sum 41 ra one full movie download 2011 download rebelde só pro meu prazer download tees maar khan movie in avi format activex control download install download video setia band stasiun cinta download de temas nokia x2 00 download power geez 2005 computer games download com download benny and babloo songs soc pc camera driver download for xp manually download sophos virus definitions jackie chan adventures download links download dragostea se face in doi download terjemahan kitab al umm hp photosmart c3180 download scanner download ipcop for windows 7 nero 8 download windows 7 64 bit kick buttowski kick in genes download dewana 2013 mp3 download download 2 chainz birthday song free how to download correct video driver download film g 30s pki firing games download full version download free 3d motorbike racing download call of duty 4 zombies download smart mobile themes download crbl romanu n are noroc hotfiles download diggy simmons make you mine download bangla natok bhalobashi tai download kml from my maps download song if this charlie sheen shinda new album download download outcast 1 temporada keterlaluan the potters mp3 download download office 2007 upload download falling skies 2 temporada rmvb download sajan all songs oblivion mod manager download mac download toma o meu coração download pierce the veil caraphernelia mp3 download lagu jkt48 original download leaf by elle varner vandalism coming alive mp3 download download god of war betrayal 240x320 download amanda by zigi mp3 download apostila do trf hp 635 driver download windows xp download pro e student version download office 2007 turkish proofing tools download filme a era do nariz vermelho are you in download download lagu true worshippers jadi sepertimu warlords battlecry 3 download free full version apostilas calculo 1 download calof duti 2 download gratis