Skip to content

Instantly share code, notes, and snippets.

@ParkinT
Last active August 29, 2015 14:01
Show Gist options
  • Save ParkinT/22e59e6b450d4694431a to your computer and use it in GitHub Desktop.
Save ParkinT/22e59e6b450d4694431a to your computer and use it in GitHub Desktop.
Recently, I needed to make changes to a MySQL database on a deployed application. My development setup is on a Nitrous.IO box but I found it necessary to make a quick surgical edit of the database {the client had messed up an input and it was easiest to simply edit the record's association}
/*
This document has been created with Marked.app <http://markedapp.com>, Copyright 2013 Brett Terpstra
Content is property of the document author
Please leave this notice in place, along with any additional credits below.
---------------------------------------------------------------
Title: GitHub
Author: Brett Terpstra
Description: Github README style. Includes theme for Pygmentized code blocks.
*/
html,body{color:black}*:not('#mkdbuttons'){margin:0;padding:0}#wrapper{font:13.34px helvetica,arial,freesans,clean,sans-serif;-webkit-font-smoothing:subpixel-antialiased;line-height:1.4;padding:3px;background:#fff;border-radius:3px;-moz-border-radius:3px;-webkit-border-radius:3px}p{margin:1em 0}a{color:#4183c4;text-decoration:none}#wrapper{background-color:#fff;padding:30px;margin:15px;font-size:14px;line-height:1.6}#wrapper>*:first-child{margin-top:0!important}#wrapper>*:last-child{margin-bottom:0!important}@media screen{#wrapper{box-shadow:0 0 0 1px #cacaca,0 0 0 4px #eee}}h1,h2,h3,h4,h5,h6{margin:20px 0 10px;padding:0;font-weight:bold;-webkit-font-smoothing:subpixel-antialiased;cursor:text}h1{font-size:28px;color:#000}h2{font-size:24px;border-bottom:1px solid #ccc;color:#000}h3{font-size:18px;color:#333}h4{font-size:16px;color:#333}h5{font-size:14px;color:#333}h6{color:#777;font-size:14px}p,blockquote,table,pre{margin:15px 0}ul{padding-left:30px}ol{padding-left:30px}ol li ul:first-of-type{margin-top:0}hr{background:transparent url(data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAAYAAAAECAYAAACtBE5DAAAAGXRFWHRTb2Z0d2FyZQBBZG9iZSBJbWFnZVJlYWR5ccllPAAAAyJpVFh0WE1MOmNvbS5hZG9iZS54bXAAAAAAADw/eHBhY2tldCBiZWdpbj0i77u/IiBpZD0iVzVNME1wQ2VoaUh6cmVTek5UY3prYzlkIj8+IDx4OnhtcG1ldGEgeG1sbnM6eD0iYWRvYmU6bnM6bWV0YS8iIHg6eG1wdGs9IkFkb2JlIFhNUCBDb3JlIDUuMC1jMDYwIDYxLjEzNDc3NywgMjAxMC8wMi8xMi0xNzozMjowMCAgICAgICAgIj4gPHJkZjpSREYgeG1sbnM6cmRmPSJodHRwOi8vd3d3LnczLm9yZy8xOTk5LzAyLzIyLXJkZi1zeW50YXgtbnMjIj4gPHJkZjpEZXNjcmlwdGlvbiByZGY6YWJvdXQ9IiIgeG1sbnM6eG1wPSJodHRwOi8vbnMuYWRvYmUuY29tL3hhcC8xLjAvIiB4bWxuczp4bXBNTT0iaHR0cDovL25zLmFkb2JlLmNvbS94YXAvMS4wL21tLyIgeG1sbnM6c3RSZWY9Imh0dHA6Ly9ucy5hZG9iZS5jb20veGFwLzEuMC9zVHlwZS9SZXNvdXJjZVJlZiMiIHhtcDpDcmVhdG9yVG9vbD0iQWRvYmUgUGhvdG9zaG9wIENTNSBNYWNpbnRvc2giIHhtcE1NOkluc3RhbmNlSUQ9InhtcC5paWQ6OENDRjNBN0E2NTZBMTFFMEI3QjRBODM4NzJDMjlGNDgiIHhtcE1NOkRvY3VtZW50SUQ9InhtcC5kaWQ6OENDRjNBN0I2NTZBMTFFMEI3QjRBODM4NzJDMjlGNDgiPiA8eG1wTU06RGVyaXZlZEZyb20gc3RSZWY6aW5zdGFuY2VJRD0ieG1wLmlpZDo4Q0NGM0E3ODY1NkExMUUwQjdCNEE4Mzg3MkMyOUY0OCIgc3RSZWY6ZG9jdW1lbnRJRD0ieG1wLmRpZDo4Q0NGM0E3OTY1NkExMUUwQjdCNEE4Mzg3MkMyOUY0OCIvPiA8L3JkZjpEZXNjcmlwdGlvbj4gPC9yZGY6UkRGPiA8L3g6eG1wbWV0YT4gPD94cGFja2V0IGVuZD0iciI/PqqezsUAAAAfSURBVHjaYmRABcYwBiM2QSA4y4hNEKYDQxAEAAIMAHNGAzhkPOlYAAAAAElFTkSuQmCC) repeat-x 0 0;border:0 none;color:#ccc;height:4px;padding:0}#wrapper>h2:first-child{margin-top:0;padding-top:0}#wrapper>h1:first-child{margin-top:0;padding-top:0}#wrapper>h1:first-child+h2{margin-top:0;padding-top:0}#wrapper>h3:first-child,#wrapper>h4:first-child,#wrapper>h5:first-child,#wrapper>h6:first-child{margin-top:0;padding-top:0}a:first-child h1,a:first-child h2,a:first-child h3,a:first-child h4,a:first-child h5,a:first-child h6{margin-top:0;padding-top:0}h1+p,h2+p,h3+p,h4+p,h5+p,h6+p,ul li>:first-child,ol li>:first-child{margin-top:0}dl{padding:0}dl dt{font-size:14px;font-weight:bold;font-style:italic;padding:0;margin:15px 0 5px}dl dt:first-child{padding:0}dl dt>:first-child{margin-top:0}dl dt>:last-child{margin-bottom:0}dl dd{margin:0 0 15px;padding:0 15px}dl dd>:first-child{margin-top:0}dl dd>:last-child{margin-bottom:0}blockquote{border-left:4px solid #DDD;padding:0 15px;color:#777}blockquote>:first-child{margin-top:0}blockquote>:last-child{margin-bottom:0}table{border-collapse:collapse;border-spacing:0;font-size:100%;font:inherit}table th{font-weight:bold;border:1px solid #ccc;padding:6px 13px}table td{border:1px solid #ccc;padding:6px 13px}table tr{border-top:1px solid #ccc;background-color:#fff}table tr:nth-child(2n){background-color:#f8f8f8}img{max-width:100%}code,tt{margin:0 2px;padding:0 5px;white-space:nowrap;border:1px solid #eaeaea;background-color:#f8f8f8;border-radius:3px;font-family:Consolas,'Liberation Mono',Courier,monospace;font-size:12px;color:#333}pre>code{margin:0;padding:0;white-space:pre;border:0;background:transparent}.highlight pre{background-color:#f8f8f8;border:1px solid #ccc;font-size:13px;line-height:19px;overflow:auto;padding:6px 10px;border-radius:3px}pre{background-color:#f8f8f8;border:1px solid #ccc;font-size:13px;line-height:19px;overflow:auto;padding:6px 10px;border-radius:3px}pre code,pre tt{background-color:transparent;border:0}.poetry pre{font-family:Georgia,Garamond,serif!important;font-style:italic;font-size:110%!important;line-height:1.6em;display:block;margin-left:1em}.poetry pre code{font-family:Georgia,Garamond,serif!important;word-break:break-all;word-break:break-word;-webkit-hyphens:auto;-moz-hyphens:auto;hyphens:auto;white-space:pre-wrap}sup,sub,a.footnote{font-size:1.4ex;height:0;line-height:1;vertical-align:super;position:relative}sub{vertical-align:sub;top:-1px}@media print{body{background:#fff}img,pre,blockquote,table,figure{page-break-inside:avoid}#wrapper{background:#fff;border:0}code{background-color:#fff;color:#333!important;padding:0 .2em;border:1px solid #dedede}pre{background:#fff}pre code{background-color:white!important;overflow:visible}}@media screen{body.inverted{color:#eee!important;border-color:#555;box-shadow:none}.inverted #wrapper,.inverted hr .inverted p,.inverted td,.inverted li,.inverted h1,.inverted h2,.inverted h3,.inverted h4,.inverted h5,.inverted h6,.inverted th,.inverted .math,.inverted caption,.inverted dd,.inverted dt,.inverted blockquote{color:#eee!important;border-color:#555;box-shadow:none}.inverted td,.inverted th{background:#333}.inverted h2{border-color:#555}.inverted hr{border-color:#777;border-width:1px!important}::selection{background:rgba(157,193,200,0.5)}h1::selection{background-color:rgba(45,156,208,0.3)}h2::selection{background-color:rgba(90,182,224,0.3)}h3::selection,h4::selection,h5::selection,h6::selection,li::selection,ol::selection{background-color:rgba(133,201,232,0.3)}code::selection{background-color:rgba(0,0,0,0.7);color:#eee}code span::selection{background-color:rgba(0,0,0,0.7)!important;color:#eee!important}a::selection{background-color:rgba(255,230,102,0.2)}.inverted a::selection{background-color:rgba(255,230,102,0.6)}td::selection,th::selection,caption::selection{background-color:rgba(180,237,95,0.5)}.inverted{background:#0b2531;background:#252a2a}.inverted #wrapper{background:#252a2a}.inverted a{color:#acd1d5}}.highlight .c{color:#998;font-style:italic}.highlight .err{color:#a61717;background-color:#e3d2d2}.highlight .k,.highlight .o{font-weight:bold}.highlight .cm{color:#998;font-style:italic}.highlight .cp{color:#999;font-weight:bold}.highlight .c1{color:#998;font-style:italic}.highlight .cs{color:#999;font-weight:bold;font-style:italic}.highlight .gd{color:#000;background-color:#fdd}.highlight .gd .x{color:#000;background-color:#faa}.highlight .ge{font-style:italic}.highlight .gr{color:#a00}.highlight .gh{color:#999}.highlight .gi{color:#000;background-color:#dfd}.highlight .gi .x{color:#000;background-color:#afa}.highlight .go{color:#888}.highlight .gp{color:#555}.highlight .gs{font-weight:bold}.highlight .gu{color:#800080;font-weight:bold}.highlight .gt{color:#a00}.highlight .kc,.highlight .kd,.highlight .kn,.highlight .kp,.highlight .kr{font-weight:bold}.highlight .kt{color:#458;font-weight:bold}.highlight .m{color:#099}.highlight .s{color:#d14}.highlight .na{color:#008080}.highlight .nb{color:#0086b3}.highlight .nc{color:#458;font-weight:bold}.highlight .no{color:#008080}.highlight .ni{color:#800080}.highlight .ne,.highlight .nf{color:#900;font-weight:bold}.highlight .nn{color:#555}.highlight .nt{color:#000080}.highlight .nv{color:#008080}.highlight .ow{font-weight:bold}.highlight .w{color:#bbb}.highlight .mf,.highlight .mh,.highlight .mi,.highlight .mo{color:#099}.highlight .sb,.highlight .sc,.highlight .sd,.highlight .s2,.highlight .se,.highlight .sh,.highlight .si,.highlight .sx{color:#d14}.highlight .sr{color:#009926}.highlight .s1{color:#d14}.highlight .ss{color:#990073}.highlight .bp{color:#999}.highlight .vc,.highlight .vg,.highlight .vi{color:#008080}.highlight .il{color:#099}.highlight .gc{color:#999;background-color:#eaf2f5}.type-csharp .highlight .k,.type-csharp .highlight .kt{color:#00F}.type-csharp .highlight .nf{color:#000;font-weight:normal}.type-csharp .highlight .nc{color:#2b91af}.type-csharp .highlight .nn{color:#000}.type-csharp .highlight .s,.type-csharp .highlight .sc{color:#a31515}
<!DOCTYPE html>
<html>
<head>
<title>PHPMyAdmin Remote with Nitrous.IO</title>
<link rel="stylesheet" href="github.css" />
</head>
<body>
<h2>PHPMyAdmin Remote with Nitrous <img src="https://gist.githubusercontent.com/ParkinT/22e59e6b450d4694431a/raw/d2bde10f78da6fd5b438f0cb726b09f527d48bbf/NitrousCharged.png" alt="Nitrous-Charged" /></h2>
<p>I am a big fan of <a href="https://www.nitrous.io/join/E49oOOrie2w">Nitrous.IO</a> and use it exclusively now to develop web applications. If you are unfamiliar with <a href="https://www.nitrous.io/join/E49oOOrie2w">Nitrous.IO</a>, take a look at these resources:</p>
<ul>
<li><a href="https://learnable.com/hub/play/47">&#8220;Nitrous.io and Why You Really, Really Should Check it Out&#8221;</a> on <a href="https://learnable.com/topics/search?q=thom+parkin">Learnable</a></li>
<li><a href="https://www.youtube.com/watch?v=rWEKT1Kz1a4">Introduction to Nitrous</a> (The Director&#8217;s Cut)</li>
<li><a href="http://www.sitepoint.com/nitrous-io-rails-development-cloud/">Nitrous.IO - Rails Development in the Cloud</a></li>
<li><a href="http://www.sitepoint.com/nitrous-io-heroku-perfect-pair/">Nitrous.IO and Heroku: A Perfect Pair</a></li>
</ul>
<hr />
<p>Recently, I needed to make changes to a MySQL database on a deployed application. My development setup is on a <a href="https://www.nitrous.io/join/E49oOOrie2w">Nitrous.IO</a> box but I found it necessary to make a quick surgical edit of the database {the client had messed up an input and it was easiest to simply edit the record&#8217;s association}.</p>
<p>Installing PHPMyAdmin on the production server was impossible and would pose a very <strong>serious</strong> security risk. So, I wondered how difficult it would be to use my favorite development tool - <a href="https://www.nitrous.io/join/E49oOOrie2w">Nitrous.IO</a> - to accomplish my <em>Remote MySQL access</em>.</p>
<p>As it turns out, it is not too tough at all.
In case you find yourself in a similar situation, I have worked it out and provide all the details here.</p>
<p>The <strong>Autoparts</strong> is probably my favorite feature of <a href="https://www.nitrous.io/join/E49oOOrie2w">Nitrous.IO</a>. It allows you to install and provision dozens of useful utilties (beyond the hefty toolchain that is already provided with every <a href="https://www.nitrous.io/join/E49oOOrie2w">Nitrous.IO</a> box).
Begin this process by installing PHPMyAdmin by way of Autoparts.</p>
<blockquote>
<p>From the Autoparts menu select <em>Manage Packages&#8230;</em><br />
Then enter &#8216;phpmy&#8217; into the <em>Filter Packages</em> field<br />
Click <em>Install</em> on the &#8220;phpMyAdmin 4.1.7&#8221; (It should be the <strong>only</strong> choice)</p>
</blockquote>
<p>As instructed, you must immediately provide a username/password for PHPMyAdmin <code>mysqladmin -u user password</code></p>
<p>In the workspace directory you will see <code>www/phpadmin</code></p>
<p>The <code>config.inc.php</code> is the configuration file. That is where you must add the particluars of any remote MySQL instances you wish to access.
The <em>user</em> and <em>password</em> you enter here must also be added to the Database PRIVILEDGES (see #2 below)</p>
<p>For my <a href="https://www.digitalocean.com/?refcode=ad30861cee8b">Digital Ocean Droplet</a> (that has a Ruby-on-Rails application running with MySQL) I had to also perform the following steps.</p>
<p>Over SSH, on my host (Droplet) I edited the <code>/etc/mysql/my.cnf</code> file
<ol>
<li>Remark the line <code>bind-address = 127.0.0.1</code></li>
<li>Grant permissions to myself as a user on the database in MySQL <code>GRANT ALL ON database_name.* TO 'user'@'ec2-54-235-221-217.compute-1.amazonaws.com' IDENTIFIED BY 'your_password';</code> and then <code>FLUSH PRIVILEGES;</code> I think you need to restart the MySQL server ``</li>
<li>Verify MySQL is listening on port 3306 with <code>netstat -plutn</code></li>
</ol>
</p>
<p>When you select <em>Preview</em> on your <a href="https://www.nitrous.io/join/E49oOOrie2w">Nitrous.IO</a> box you will see the <code>PHPMyAdmin</code> directory. Clicking on it activates the utility.
In the selection of &#8220;Servers&#8221; you should see the new remote server. Select it and begin MySQL Administration!</p>
<p>From now on the MySQL and Apache daemons will be running on this <a href="https://www.nitrous.io/join/E49oOOrie2w">Nitrous.IO</a> box. If you want to use the <a href="https://www.nitrous.io/join/E49oOOrie2w">Nitrous.IO</a> box for other development you need to <em>selectively</em> activate and deactivate the PHPMyAdmin services.</p>
<blockquote>
<p>Use <code>apachectl stop</code> and <code>apachectl start</code></p>
</blockquote>
<p>If you find improvements or updates please submit a <a href="https://www.nitrous.io/hack_button?source=embed&runtime=php&repo=ParkinT%2F22e59e6b450d4694431a&file_to_open=NitrousPHPAdminRemote.md" style="border:0;text-decoration:none"><img src="https://d3o0mnbgv6k92a.cloudfront.net/assets/hack-s-v1-7475db0cf93fe5d1e29420c928ebc614.png" alt="Update this Gist on Nitrous.IO" /></a> Pull Request.</p>
</body>
</html>

PHPMyAdmin Remote with Nitrous Nitrous-Charged

I am a big fan of Nitrous.IO and use it exclusively now to develop web applications. If you are unfamiliar with Nitrous.IO, take a look at these resources:


Recently, I needed to make changes to a MySQL database on a deployed application. My development setup is on a Nitrous.IO box but I found it necessary to make a quick surgical edit of the database {the client had messed up an input and it was easiest to simply edit the record's association}.

Installing PHPMyAdmin on the production server was impossible and would pose a very serious security risk. So, I wondered how difficult it would be to use my favorite development tool - Nitrous.IO - to accomplish my Remote MySQL access.

As it turns out, it is not too tough at all. In case you find yourself in a similar situation, I have worked it out and provide all the details here.

The Autoparts is probably my favorite feature of Nitrous.IO. It allows you to install and provision dozens of useful utilties (beyond the hefty toolchain that is already provided with every Nitrous.IO box). Begin this process by installing PHPMyAdmin by way of Autoparts.

From the Autoparts menu select Manage Packages... Then enter 'phpmy' into the Filter Packages field Click Install on the "phpMyAdmin 4.1.7" (It should be the only choice)

As instructed, you must immediately provide a username/password for PHPMyAdmin mysqladmin -u user password

In the workspace directory you will see www/phpadmin

The config.inc.php is the configuration file. That is where you must add the particluars of any remote MySQL instances you wish to access. The user and password you enter here must also be added to the Database PRIVILEDGES (see #2 below)

For my Digital Ocean Droplet (that has a Ruby-on-Rails application running with MySQL) I had to also perform the following steps.

Over SSH, on my host (Droplet) I edited the /etc/mysql/my.cnf file

  1. Remark the line bind-address = 127.0.0.1
  2. Grant permissions to myself as a user on the database in MySQL GRANT ALL ON database_name.* TO 'user'@'ec2-54-235-221-217.compute-1.amazonaws.com' IDENTIFIED BY 'your_password'; and then FLUSH PRIVILEGES; I think you need to restart the MySQL server ``
  3. Verify MySQL is listening on port 3306 with netstat -plutn

When you select Preview on your Nitrous.IO box you will see the PHPMyAdmin directory. Clicking on it activates the utility. In the selection of "Servers" you should see the new remote server. Select it and begin MySQL Administration!

From now on the MySQL and Apache daemons will be running on this Nitrous.IO box. If you want to use the Nitrous.IO box for other development you need to selectively activate and deactivate the PHPMyAdmin services.

Use apachectl stop and apachectl start

If you find improvements or updates please submit a Update This Gist on Nitrous.IO Pull Request.

0 info it worked if it ends with ok
1 verbose cli [ '/home/action/.parts/bin/node',
1 verbose cli '/home/action/.parts/bin/npm',
1 verbose cli '-g',
1 verbose cli 'install',
1 verbose cli 'gitsup' ]
2 info using [email protected]
3 info using [email protected]
4 verbose node symlink /home/action/.parts/bin/node
5 verbose cache add [ 'gitsup', null ]
6 verbose cache add name=undefined spec="gitsup" args=["gitsup",null]
7 verbose parsed url { protocol: null,
7 verbose parsed url slashes: null,
7 verbose parsed url auth: null,
7 verbose parsed url host: null,
7 verbose parsed url port: null,
7 verbose parsed url hostname: null,
7 verbose parsed url hash: null,
7 verbose parsed url search: null,
7 verbose parsed url query: null,
7 verbose parsed url pathname: 'gitsup',
7 verbose parsed url path: 'gitsup',
7 verbose parsed url href: 'gitsup' }
8 silly lockFile c4fb981d-gitsup gitsup
9 verbose lock gitsup /home/action/.npm/c4fb981d-gitsup.lock
10 silly lockFile c4fb981d-gitsup gitsup
11 silly lockFile c4fb981d-gitsup gitsup
12 verbose addNamed [ 'gitsup', '' ]
13 verbose addNamed [ null, '*' ]
14 silly lockFile 4dc62b53-gitsup gitsup@
15 verbose lock gitsup@ /home/action/.npm/4dc62b53-gitsup.lock
16 silly addNameRange { name: 'gitsup', range: '*', hasData: false }
17 verbose url raw gitsup
18 verbose url resolving [ 'https://registry.npmjs.org/', './gitsup' ]
19 verbose url resolved https://registry.npmjs.org/gitsup
20 info trying registry request attempt 1 at 23:05:41
21 http GET https://registry.npmjs.org/gitsup
22 http 404 https://registry.npmjs.org/gitsup
23 silly registry.get cb [ 404,
23 silly registry.get { date: 'Thu, 08 May 2014 23:05:41 GMT',
23 silly registry.get server: 'CouchDB/1.5.0 (Erlang OTP/R14B04)',
23 silly registry.get 'content-type': 'application/json',
23 silly registry.get 'cache-control': 'max-age=0',
23 silly registry.get 'content-length': '52',
23 silly registry.get 'accept-ranges': 'bytes',
23 silly registry.get via: '1.1 varnish',
23 silly registry.get age: '0',
23 silly registry.get 'x-served-by': 'cache-v37-ASH',
23 silly registry.get 'x-cache': 'MISS',
23 silly registry.get 'x-cache-hits': '0',
23 silly registry.get 'x-timer': 'S1399590341.439096451,VS0,VE96',
23 silly registry.get 'keep-alive': 'timeout=10, max=50',
23 silly registry.get connection: 'Keep-Alive' } ]
24 silly lockFile 4dc62b53-gitsup gitsup@
25 silly lockFile 4dc62b53-gitsup gitsup@
26 error 404 'gitsup' is not in the npm registry.
26 error 404 You should bug the author to publish it
26 error 404
26 error 404 Note that you can also install from a
26 error 404 tarball, folder, or http url, or git url.
27 error System Linux 3.13.4
28 error command "/home/action/.parts/bin/node" "/home/action/.parts/bin/npm" "-g" "install" "gitsup"
29 error cwd /home/action/workspace/www/remotephpadmin
30 error node -v v0.10.26
31 error npm -v 1.4.3
32 error code E404
33 verbose exit [ 1, true ]
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment