snipt

Ctrl+h for KB shortcuts

Text only

Percona 5.6

1
2
3
4
// stop cluster
service mysql bootstrap-stop
// start cluster
service mysql bootstrap-pxc
https://snipt.net/embed/9386cdb21632e037d663cb04f37826f6/
https://snipt.net/raw/9386cdb21632e037d663cb04f37826f6/
9386cdb21632e037d663cb04f37826f6
text
Text only
6
2016-09-30T21:42:39
True
False
False
/api/public/snipt/147418/
percona-56
<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 id="L-1"><a name="L-1"></a>// stop cluster </span><span id="L-2"><a name="L-2"></a>service mysql bootstrap-stop </span><span id="L-3"><a name="L-3"></a>// start cluster </span><span id="L-4"><a name="L-4"></a>service mysql bootstrap-pxc </span></pre></div> </td></tr></table>
mysql, percona

MySQL

Get size of database

SELECT table_schema "DB Name", Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" FROM information_schema.tables GROUP BY table_schema;
https://snipt.net/embed/8bc9ac636c1c1b93033c9480c02808e9/
https://snipt.net/raw/8bc9ac636c1c1b93033c9480c02808e9/
8bc9ac636c1c1b93033c9480c02808e9
mysql
MySQL
1
2016-09-30T23:58:13
True
False
False
May 23, 2016 at 12:24 AM
/api/public/snipt/147031/
get-size-of-database
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1">1</a></pre></div></td><td class="code"><div class="highlight"><pre><span id="L-1"><a name="L-1"></a><span class="k">SELECT</span> <span class="n">table_schema</span> <span class="s2">&quot;DB Name&quot;</span><span class="p">,</span> <span class="nf">Round</span><span class="p">(</span><span class="nf">Sum</span><span class="p">(</span><span class="n">data_length</span> <span class="o">+</span> <span class="n">index_length</span><span class="p">)</span> <span class="o">/</span> <span class="mi">1024</span> <span class="o">/</span> <span class="mi">1024</span><span class="p">,</span> <span class="mi">1</span><span class="p">)</span> <span class="s2">&quot;DB Size in MB&quot;</span> <span class="k">FROM</span> <span class="n">information_schema</span><span class="p">.</span><span class="kp">tables</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">table_schema</span><span class="p">;</span> </span></pre></div> </td></tr></table>
mysql

SQL

MySQL create user, grant privilege

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON my_db.* TO 'newuser'@'localhost';
https://snipt.net/embed/7d3ade302b050a58b4fecda7fcfa83b7/
https://snipt.net/raw/7d3ade302b050a58b4fecda7fcfa83b7/
7d3ade302b050a58b4fecda7fcfa83b7
sql
SQL
3
2016-09-30T21:52:46
True
False
False
Oct 25, 2015 at 02:38 PM
/api/public/snipt/141931/
mysql-create-user-grant-privilege
<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 id="L-1"><a name="L-1"></a><span class="k">CREATE</span> <span class="k">USER</span> <span class="s1">&#39;newuser&#39;</span><span class="o">@</span><span class="s1">&#39;localhost&#39;</span> <span class="n">IDENTIFIED</span> <span class="k">BY</span> <span class="s1">&#39;password&#39;</span><span class="p">;</span> </span><span id="L-2"><a name="L-2"></a><span class="k">GRANT</span> <span class="k">ALL</span> <span class="k">PRIVILEGES</span> <span class="k">ON</span> <span class="n">my_db</span><span class="p">.</span><span class="o">*</span> <span class="k">TO</span> <span class="s1">&#39;newuser&#39;</span><span class="o">@</span><span class="s1">&#39;localhost&#39;</span><span class="p">;</span> </span></pre></div> </td></tr></table>
mysql, sql

Bash

mysql2 gem with mariadb

$ sudo apt-get install libmariadbd-dev
$ gem install mysql2
https://snipt.net/embed/7714f80e2d8ee14c7c0fd0df6ff3e5a4/
https://snipt.net/raw/7714f80e2d8ee14c7c0fd0df6ff3e5a4/
7714f80e2d8ee14c7c0fd0df6ff3e5a4
bash
Bash
3
2016-09-30T22:57:03
True
False
False
/api/public/snipt/140788/
mysql2-gem-with-mariadb
<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 id="L-1"><a name="L-1"></a><span class="nv">$ </span>sudo apt-get install libmariadbd-dev </span><span id="L-2"><a name="L-2"></a><span class="nv">$ </span>gem install mysql2 </span></pre></div> </td></tr></table>
gem, mariadb, mysql, ruby

MySQL

Mysql importing errors

SET foreign_key_checks = 0;
https://snipt.net/embed/de729115ea11ac53277b45d415e8d9f9/
https://snipt.net/raw/de729115ea11ac53277b45d415e8d9f9/
de729115ea11ac53277b45d415e8d9f9
mysql
MySQL
1
2016-09-30T21:53:44
True
False
False
Mar 30, 2014 at 09:22 PM
/api/public/snipt/126066/
mysql-importing-errors
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1">1</a></pre></div></td><td class="code"><div class="highlight"><pre><span id="L-1"><a name="L-1"></a><span class="kt">SET</span> <span class="n">foreign_key_checks</span> <span class="o">=</span> <span class="mi">0</span><span class="p">;</span> </span></pre></div> </td></tr></table>
importing, mysql, sql

Text only

MySQL Find & Replace

update table_name set field_name = replace(field_name,'string_to_find','string_to_replace');
https://snipt.net/embed/91ceec1f408f00ebb1d2f4dbdce72f8d/
https://snipt.net/raw/91ceec1f408f00ebb1d2f4dbdce72f8d/
91ceec1f408f00ebb1d2f4dbdce72f8d
text
Text only
1
2016-09-30T21:43:07
True
False
False
Mar 18, 2014 at 12:04 PM
/api/public/snipt/122803/
mysql-find-replace
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1">1</a></pre></div></td><td class="code"><div class="highlight"><pre><span id="L-1"><a name="L-1"></a>update table_name set field_name = replace(field_name,&#39;string_to_find&#39;,&#39;string_to_replace&#39;); </span></pre></div> </td></tr></table>
mysql
Copyrighted, illegal, or inappropriate content? Email support@snipt.net.