snipt

Ctrl+h for KB shortcuts

MySQL

Update portion of a string in MySQL

1
2
3
4
#Don't forget backup or make a copy before apply!
#http://stackoverflow.com/questions/1876762/mysql-way-to-update-portion-of-a-string

update table set field = REPLACE(field, 'string', 'anothervalue') WHERE field LIKE '%string%';
https://snipt.net/embed/c92fe10d35e18b4c97149a8b2efc2c6d/
/raw/c92fe10d35e18b4c97149a8b2efc2c6d/
c92fe10d35e18b4c97149a8b2efc2c6d
mysql
MySQL
4
2019-08-22T02:27:29
True
False
False
/api/public/snipt/102162/
update-portion-of-a-string-in-mysql
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1">1</a> <a href="#L-2">2</a> <a href="#L-3">3</a> <a href="#L-4">4</a></pre></div></td><td class="code"><div class="highlight"><pre><span></span><span id="L-1"><a name="L-1"></a><span class="c1">#Don&#39;t forget backup or make a copy before apply!</span> </span><span id="L-2"><a name="L-2"></a><span class="c1">#http://stackoverflow.com/questions/1876762/mysql-way-to-update-portion-of-a-string</span> </span><span id="L-3"><a name="L-3"></a> </span><span id="L-4"><a name="L-4"></a><span class="k">update</span> <span class="k">table</span> <span class="kt">set</span> <span class="n">field</span> <span class="o">=</span> <span class="k">REPLACE</span><span class="p">(</span><span class="n">field</span><span class="p">,</span> <span class="s1">&#39;string&#39;</span><span class="p">,</span> <span class="s1">&#39;anothervalue&#39;</span><span class="p">)</span> <span class="k">WHERE</span> <span class="n">field</span> <span class="k">LIKE</span> <span class="s1">&#39;%string%&#39;</span><span class="p">;</span> </span></pre></div> </td></tr></table>
%¨string%, MySQL, REPLACE, UDATE

Bash

Diff of two directories and generate a patch

#Diff of two dirs and generate a patch.
diff -urPp dir.old/ dir.new/ > newpatch.patch
https://snipt.net/embed/a374ca697edc9fa7331f2882c902a270/
/raw/a374ca697edc9fa7331f2882c902a270/
a374ca697edc9fa7331f2882c902a270
bash
Bash
2
2019-08-24T11:41:22
True
False
False
Mar 18, 2013 at 04:06 PM
/api/public/snipt/57879/
diff-of-two-directories-and-generate-a-patch
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1">1</a> <a href="#L-2">2</a></pre></div></td><td class="code"><div class="highlight"><pre><span></span><span id="L-1"><a name="L-1"></a><span class="c1">#Diff of two dirs and generate a patch.</span> </span><span id="L-2"><a name="L-2"></a>diff -urPp dir.old/ dir.new/ &gt; newpatch.patch </span></pre></div> </td></tr></table>
diff, patch

Bash

Compact tar.bz2 and on destination

1
2
3
#This is useful when you don't have much space on server, and need backup or copy
#So, you can backup direct to destination.
tar -cf - /home/dir_backup |bzip2 -9vv | ssh [email protected] "cat > /backups/dir_backup.tar.bz2"
https://snipt.net/embed/ff3556c4d1185c97e7eacbd696ca6a35/
/raw/ff3556c4d1185c97e7eacbd696ca6a35/
ff3556c4d1185c97e7eacbd696ca6a35
bash
Bash
4
2019-08-19T19:31:51
True
False
False
Sep 29, 2012 at 03:39 PM
/api/public/snipt/47820/
compact-tarbz2-and-on-destination
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1">1</a> <a href="#L-2">2</a> <a href="#L-3">3</a></pre></div></td><td class="code"><div class="highlight"><pre><span></span><span id="L-1"><a name="L-1"></a><span class="c1">#This is useful when you don&#39;t have much space on server, and need backup or copy</span> </span><span id="L-2"><a name="L-2"></a><span class="c1">#So, you can backup direct to destination.</span> </span><span id="L-3"><a name="L-3"></a>tar -cf - /home/dir_backup <span class="p">|</span>bzip2 -9vv <span class="p">|</span> ssh [email protected] <span class="s2">&quot;cat &gt; /backups/dir_backup.tar.bz2&quot;</span> </span></pre></div> </td></tr></table>
backup, bz2, bzip2, ssh, tar, tar.bz2

PostgreSQL console (psql)

pg_dump on a remote server

1
2
3
4
5
6
7
#make a backup from a remote postgresql server
#Instructions from here
#http://www.mkyong.com/database/backup-restore-database-in-postgresql-pg_dumppg_restore/
pg_dump -i -h $REMOTESERVER -p 5432 -U $USERDATABASE -F c -b -v -f "backupdatabase.backup" $DATABASE

#Restore, if you need. You can restore local or remotely
pg_restore -i -h localhost -p 5432 -U $USER -d $DATABASE -v "backupdatabase.backup"
https://snipt.net/embed/0b97c217544a5cf8e64f5d7282b06ed8/
/raw/0b97c217544a5cf8e64f5d7282b06ed8/
0b97c217544a5cf8e64f5d7282b06ed8
psql
PostgreSQL console (psql)
7
2019-05-25T08:07:04
True
False
False
Sep 18, 2012 at 03:43 PM
/api/public/snipt/47336/
pg_dump-on-a-remote-server
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1">1</a> <a href="#L-2">2</a> <a href="#L-3">3</a> <a href="#L-4">4</a> <a href="#L-5">5</a> <a href="#L-6">6</a> <a href="#L-7">7</a></pre></div></td><td class="code"><div class="highlight"><pre><span></span><span id="L-1"><a name="L-1"></a><span class="o">#</span><span class="n">make</span> <span class="n">a</span> <span class="n">backup</span> <span class="k">from</span> <span class="n">a</span> <span class="n">remote</span> <span class="n">postgresql</span> <span class="k">server</span> </span><span id="L-2"><a name="L-2"></a><span class="o">#</span><span class="n">Instructions</span> <span class="k">from</span> <span class="n">here</span> </span><span id="L-3"><a name="L-3"></a><span class="o">#</span><span class="n">http</span><span class="p">:</span><span class="o">//</span><span class="n">www</span><span class="mf">.</span><span class="n">mkyong</span><span class="mf">.</span><span class="n">com</span><span class="o">/</span><span class="k">database</span><span class="o">/</span><span class="n">backup</span><span class="o">-</span><span class="n">restore</span><span class="o">-</span><span class="k">database</span><span class="o">-</span><span class="k">in</span><span class="o">-</span><span class="n">postgresql</span><span class="o">-</span><span class="n">pg_dumppg_restore</span><span class="o">/</span> </span><span id="L-4"><a name="L-4"></a><span class="n">pg_dump</span> <span class="o">-</span><span class="n">i</span> <span class="o">-</span><span class="n">h</span> <span class="err">$</span><span class="n">REMOTESERVER</span> <span class="o">-</span><span class="n">p</span> <span class="mf">5432</span> <span class="o">-</span><span class="n">U</span> <span class="err">$</span><span class="n">USERDATABASE</span> <span class="o">-</span><span class="n">F</span> <span class="n">c</span> <span class="o">-</span><span class="n">b</span> <span class="o">-</span><span class="n">v</span> <span class="o">-</span><span class="n">f</span> <span class="s s-Name">&quot;backupdatabase.backup&quot;</span> <span class="err">$</span><span class="k">DATABASE</span> </span><span id="L-5"><a name="L-5"></a> </span><span id="L-6"><a name="L-6"></a><span class="o">#</span><span class="n">Restore</span><span class="p">,</span> <span class="k">if</span> <span class="n">you</span> <span class="n">need</span><span class="mf">.</span> <span class="n">You</span> <span class="n">can</span> <span class="n">restore</span> <span class="k">local</span> <span class="k">or</span> <span class="n">remotely</span> </span><span id="L-7"><a name="L-7"></a><span class="n">pg_restore</span> <span class="o">-</span><span class="n">i</span> <span class="o">-</span><span class="n">h</span> <span class="n">localhost</span> <span class="o">-</span><span class="n">p</span> <span class="mf">5432</span> <span class="o">-</span><span class="n">U</span> <span class="err">$</span><span class="k">USER</span> <span class="o">-</span><span class="n">d</span> <span class="err">$</span><span class="k">DATABASE</span> <span class="o">-</span><span class="n">v</span> <span class="s s-Name">&quot;backupdatabase.backup&quot;</span> </span></pre></div> </td></tr></table>
pg_dump, pg_restore, postgre, postgreSQL

Bash

Rsync by especific extension

1
2
3
#Syncronize all files with extension flv and swf to destination, including paths,
#but exclude all other files
rsync -av --include '*/' --include '*.flv' --include '*.swf' --exclude '*' /source/ /destination/
https://snipt.net/embed/eca73edaf3be0619fb996af927340958/
/raw/eca73edaf3be0619fb996af927340958/
eca73edaf3be0619fb996af927340958
bash
Bash
3
2019-08-24T19:31:49
True
False
False
Sep 04, 2012 at 09:21 AM
/api/public/snipt/46676/
rsync-by-especific-extension
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1">1</a> <a href="#L-2">2</a> <a href="#L-3">3</a></pre></div></td><td class="code"><div class="highlight"><pre><span></span><span id="L-1"><a name="L-1"></a><span class="c1">#Syncronize all files with extension flv and swf to destination, including paths,</span> </span><span id="L-2"><a name="L-2"></a><span class="c1">#but exclude all other files</span> </span><span id="L-3"><a name="L-3"></a>rsync -av --include <span class="s1">&#39;*/&#39;</span> --include <span class="s1">&#39;*.flv&#39;</span> --include <span class="s1">&#39;*.swf&#39;</span> --exclude <span class="s1">&#39;*&#39;</span> /source/ /destination/ </span></pre></div> </td></tr></table>
rsync

MySQL

Show last modified dat of a MySQL table

#From your database, execute this query.
SHOW TABLE STATUS LIKE '%';
https://snipt.net/embed/1b2cbc767447c491c7954df7473cfeec/
/raw/1b2cbc767447c491c7954df7473cfeec/
1b2cbc767447c491c7954df7473cfeec
mysql
MySQL
2
2019-08-22T02:29:05
True
False
False
Aug 30, 2012 at 02:59 PM
/api/public/snipt/46524/
show-last-modified-dat-of-a-mysql-table
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1">1</a> <a href="#L-2">2</a></pre></div></td><td class="code"><div class="highlight"><pre><span></span><span id="L-1"><a name="L-1"></a><span class="c1">#From your database, execute this query.</span> </span><span id="L-2"><a name="L-2"></a><span class="k">SHOW</span> <span class="k">TABLE</span> <span class="n">STATUS</span> <span class="k">LIKE</span> <span class="s1">&#39;%&#39;</span><span class="p">;</span> </span></pre></div> </td></tr></table>

PostgreSQL console (psql)

Create user, database and grant privileges PostgreSQL

1
2
3
4
5
CREATE USER myuser WITH PASSWORD 'mypasswd';

CREATE DATABASE mydb;

GRANT ALL PRIVILEGES ON DATABASE mydb to myuser;
https://snipt.net/embed/7255150555d22d0b3eda4dee841516ae/
/raw/7255150555d22d0b3eda4dee841516ae/
7255150555d22d0b3eda4dee841516ae
psql
PostgreSQL console (psql)
7
2019-05-24T14:53:55
True
False
False
/api/public/snipt/46452/
create-user-database-and-grant-privileges-postgresql
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1">1</a> <a href="#L-2">2</a> <a href="#L-3">3</a> <a href="#L-4">4</a> <a href="#L-5">5</a></pre></div></td><td class="code"><div class="highlight"><pre><span></span><span id="L-1"><a name="L-1"></a><span class="k">CREATE</span> <span class="k">USER</span> <span class="n">myuser</span> <span class="k">WITH</span> <span class="k">PASSWORD</span> <span class="s1">&#39;mypasswd&#39;</span><span class="p">;</span> </span><span id="L-2"><a name="L-2"></a> </span><span id="L-3"><a name="L-3"></a><span class="go">CREATE DATABASE mydb;</span> </span><span id="L-4"><a name="L-4"></a> </span><span id="L-5"><a name="L-5"></a><span class="go">GRANT ALL PRIVILEGES ON DATABASE mydb to myuser;</span> </span></pre></div> </td></tr></table>
PostgreSQL, Postgres