snipt

Ctrl+h for KB shortcuts

Bash

backup all databases mysql

mysqldump --opt --extended-insert --single-transaction --create-options --default-character-set=utf8 --all-databases --user=<user> --password=<password> > all.sql
https://snipt.net/embed/2b620a12190a51ef2778c379b44721d1/
/raw/2b620a12190a51ef2778c379b44721d1/
2b620a12190a51ef2778c379b44721d1
bash
Bash
1
2019-05-24T16:49:16
True
False
False
/api/public/snipt/4914/
backup-all-databases-mysql
<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></span><span id="L-1"><a name="L-1"></a>mysqldump --opt --extended-insert --single-transaction --create-options --default-character-set<span class="o">=</span>utf8 --all-databases --user<span class="o">=</span>&lt;user&gt; --password<span class="o">=</span>&lt;password&gt; &gt; all.sql </span></pre></div> </td></tr></table>
backup, mysql, sysadmin

Bash

dump a database on one server and load it on another

1
2
3
4
# server
mysqldump -u<user> -p<pass> database | nc -l -p <port>
# client
nc <server> <port> | mysql -u<user> -p<pass> database
https://snipt.net/embed/c4801dc7574c2138cf8c0d8ccbac5042/
/raw/c4801dc7574c2138cf8c0d8ccbac5042/
c4801dc7574c2138cf8c0d8ccbac5042
bash
Bash
4
2019-05-24T16:56:16
True
False
False
/api/public/snipt/3695/
dump-a-database-on-one-server-and-load-it-on-another
<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"># server</span> </span><span id="L-2"><a name="L-2"></a>mysqldump -u&lt;user&gt; -p&lt;pass&gt; database <span class="p">|</span> nc -l -p &lt;port&gt; </span><span id="L-3"><a name="L-3"></a><span class="c1"># client</span> </span><span id="L-4"><a name="L-4"></a>nc &lt;server&gt; &lt;port&gt; <span class="p">|</span> mysql -u&lt;user&gt; -p&lt;pass&gt; database </span></pre></div> </td></tr></table>
mysql, netcat

Bash

mysql: check all tables in all databases

mysqlcheck -u root -p --auto-repair --check --optimize --all-databases
https://snipt.net/embed/6d4279ae5b219885f32e6eee51071999/
/raw/6d4279ae5b219885f32e6eee51071999/
6d4279ae5b219885f32e6eee51071999
bash
Bash
1
2019-05-24T17:10:47
True
False
False
/api/public/snipt/2043/
mysql-check-all-tables-in-all-databases
<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></span><span id="L-1"><a name="L-1"></a>mysqlcheck -u root -p --auto-repair --check --optimize --all-databases </span></pre></div> </td></tr></table>
dba, mysql, sysadmin

MySQL

create rw mysql user

GRANT SELECT,INDEX,ALTER,LOCK TABLES,INSERT,UPDATE,DELETE,CREATE,DROP on database.* to 'user_rw'@'localhost' identified by 'xxxxx';
https://snipt.net/embed/73135d7138ef0f34aa83bd2c1f864df6/
/raw/73135d7138ef0f34aa83bd2c1f864df6/
73135d7138ef0f34aa83bd2c1f864df6
mysql
MySQL
1
2019-05-19T07:12:36
True
False
False
/api/public/snipt/1398/
create-rw-mysql-user
<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></span><span id="L-1"><a name="L-1"></a><span class="k">GRANT</span> <span class="k">SELECT</span><span class="p">,</span><span class="k">INDEX</span><span class="p">,</span><span class="k">ALTER</span><span class="p">,</span><span class="k">LOCK</span> <span class="kp">TABLES</span><span class="p">,</span><span class="k">INSERT</span><span class="p">,</span><span class="k">UPDATE</span><span class="p">,</span><span class="k">DELETE</span><span class="p">,</span><span class="k">CREATE</span><span class="p">,</span><span class="k">DROP</span> <span class="k">on</span> <span class="k">database</span><span class="p">.</span><span class="o">*</span> <span class="k">to</span> <span class="s1">&#39;user_rw&#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;xxxxx&#39;</span><span class="p">;</span> </span></pre></div> </td></tr></table>
mysql

MySQL

create mysql replication user for both master and slave

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repl@'localhost' IDENTIFIED BY 'p4ss';
https://snipt.net/embed/d181cb63f424bb366a902b86674735e1/
/raw/d181cb63f424bb366a902b86674735e1/
d181cb63f424bb366a902b86674735e1
mysql
MySQL
1
2019-05-12T23:39:51
True
False
False
/api/public/snipt/1364/
create-mysql-replication-user-for-both-master-and-slave
<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></span><span id="L-1"><a name="L-1"></a><span class="k">GRANT</span> <span class="n">REPLICATION</span> <span class="n">SLAVE</span><span class="p">,</span> <span class="n">REPLICATION</span> <span class="n">CLIENT</span> <span class="k">ON</span> <span class="o">*</span><span class="p">.</span><span class="o">*</span> <span class="k">TO</span> <span class="n">repl</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;p4ss&#39;</span><span class="p">;</span> </span></pre></div> </td></tr></table>
mysql, replication

Bash

schedule weekly backup

mysql-zrm-scheduler --add --interval weekly --backup-set weeklyrun --start 3:00 --backup-level 0
https://snipt.net/embed/30b1de243dccb1ef943b5450013118e6/
/raw/30b1de243dccb1ef943b5450013118e6/
30b1de243dccb1ef943b5450013118e6
bash
Bash
1
2019-05-12T12:51:20
True
False
False
/api/public/snipt/1346/
schedule-weekly-backup
<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></span><span id="L-1"><a name="L-1"></a>mysql-zrm-scheduler --add --interval weekly --backup-set weeklyrun --start 3:00 --backup-level 0 </span></pre></div> </td></tr></table>
mysql, mysql-zrm

Bash

schedule daily backup

mysql-zrm-scheduler --add --interval daily --backup-set dailyrun --start 1:00 --backup-level 0
https://snipt.net/embed/ab489175b79961ca422227c55d8a51f7/
/raw/ab489175b79961ca422227c55d8a51f7/
ab489175b79961ca422227c55d8a51f7
bash
Bash
1
2019-05-15T02:38:03
True
False
False
/api/public/snipt/1345/
schedule-daily-backup
<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></span><span id="L-1"><a name="L-1"></a>mysql-zrm-scheduler --add --interval daily --backup-set dailyrun --start 1:00 --backup-level 0 </span></pre></div> </td></tr></table>
mysql, mysql-zrm

Bash

run daily mysql-zrm backup now

mysql-zrm-scheduler --now --backup-set dailyrun --backup-level 0
https://snipt.net/embed/0135cd882c98cb78632d7c8117ca740d/
/raw/0135cd882c98cb78632d7c8117ca740d/
0135cd882c98cb78632d7c8117ca740d
bash
Bash
1
2019-05-18T19:12:00
True
False
False
/api/public/snipt/1344/
run-daily-mysql-zrm-backup-now
<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></span><span id="L-1"><a name="L-1"></a>mysql-zrm-scheduler --now --backup-set dailyrun --backup-level 0 </span></pre></div> </td></tr></table>
mysql, mysql-zrm

Bash

query msyql backups

mysql-zrm-reporter --where backup-set=dailyrun --show backup-status-info
https://snipt.net/embed/4cd2e2f0a31cab3dfc994928fdfdefb3/
/raw/4cd2e2f0a31cab3dfc994928fdfdefb3/
4cd2e2f0a31cab3dfc994928fdfdefb3
bash
Bash
1
2019-05-12T14:30:47
True
False
False
/api/public/snipt/1343/
query-msyql-backups
<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></span><span id="L-1"><a name="L-1"></a>mysql-zrm-reporter --where backup-set<span class="o">=</span>dailyrun --show backup-status-info </span></pre></div> </td></tr></table>
mysql, mysql-zrm

Bash

query mysql scheduled backups

mysql-zrm-scheduler --query
https://snipt.net/embed/1bba78ec0735ea7d51468584c0e9528c/
/raw/1bba78ec0735ea7d51468584c0e9528c/
1bba78ec0735ea7d51468584c0e9528c
bash
Bash
1
2019-05-12T06:54:07
True
False
False
/api/public/snipt/1342/
query-mysql-backups
<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></span><span id="L-1"><a name="L-1"></a>mysql-zrm-scheduler --query </span></pre></div> </td></tr></table>
mysql, mysql-zrm