snipt

Ctrl+h for KB shortcuts

PHP

PHP Database Backup Script

<?php

backup_tables('localhost','root','','lores_new');


/* backup the db OR just a table */
function backup_tables($host,$user,$pass,$name,$tables = '*')
{
  
  $link = mysql_connect($host,$user,$pass);
  mysql_select_db($name,$link);
  
  //get all of the tables
  if($tables == '*')
  {
    $tables = array();
    $result = mysql_query('SHOW TABLES');
    while($row = mysql_fetch_row($result))
    {
      $tables[] = $row[0];
    }
  }
  else
  {
    $tables = is_array($tables) ? $tables : explode(',',$tables);
  }
  
  //cycle through
  foreach($tables as $table)
  {
    $result = mysql_query('SELECT * FROM '.$table);
    $num_fields = mysql_num_fields($result);
    
    $return.= 'DROP TABLE '.$table.';';
    $row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table));
    $return.= "\n\n".$row2[1].";\n\n";
    
    for ($i = 0; $i < $num_fields; $i++) 
    {
      while($row = mysql_fetch_row($result))
      {
        $return.= 'INSERT INTO '.$table.' VALUES(';
        for($j=0; $j<$num_fields; $j++) 
        {
          $row[$j] = addslashes($row[$j]);
          $row[$j] = ereg_replace("\n","\\n",$row[$j]);
          if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; }
          if ($j<($num_fields-1)) { $return.= ','; }
        }
        $return.= ");\n";
      }
    }
    $return.="\n\n\n";
  }
  
  //save file
  $handle = fopen('db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+');
  fwrite($handle,$return);
  fclose($handle);
}
https://snipt.net/embed/04cbd1016866c21a748118968f7aca2c/
/raw/04cbd1016866c21a748118968f7aca2c/
04cbd1016866c21a748118968f7aca2c
php
PHP
60
2019-07-21T17:59:01
True
False
False
/api/public/snipt/51561/
php-database-backup-script
<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> <a href="#L-8"> 8</a> <a href="#L-9"> 9</a> <a href="#L-10">10</a> <a href="#L-11">11</a> <a href="#L-12">12</a> <a href="#L-13">13</a> <a href="#L-14">14</a> <a href="#L-15">15</a> <a href="#L-16">16</a> <a href="#L-17">17</a> <a href="#L-18">18</a> <a href="#L-19">19</a> <a href="#L-20">20</a> <a href="#L-21">21</a> <a href="#L-22">22</a> <a href="#L-23">23</a> <a href="#L-24">24</a> <a href="#L-25">25</a> <a href="#L-26">26</a> <a href="#L-27">27</a> <a href="#L-28">28</a> <a href="#L-29">29</a> <a href="#L-30">30</a> <a href="#L-31">31</a> <a href="#L-32">32</a> <a href="#L-33">33</a> <a href="#L-34">34</a> <a href="#L-35">35</a> <a href="#L-36">36</a> <a href="#L-37">37</a> <a href="#L-38">38</a> <a href="#L-39">39</a> <a href="#L-40">40</a> <a href="#L-41">41</a> <a href="#L-42">42</a> <a href="#L-43">43</a> <a href="#L-44">44</a> <a href="#L-45">45</a> <a href="#L-46">46</a> <a href="#L-47">47</a> <a href="#L-48">48</a> <a href="#L-49">49</a> <a href="#L-50">50</a> <a href="#L-51">51</a> <a href="#L-52">52</a> <a href="#L-53">53</a> <a href="#L-54">54</a> <a href="#L-55">55</a> <a href="#L-56">56</a> <a href="#L-57">57</a> <a href="#L-58">58</a> <a href="#L-59">59</a> <a href="#L-60">60</a></pre></div></td><td class="code"><div class="highlight"><pre><span></span><span id="L-1"><a name="L-1"></a><span class="cp">&lt;?php</span> </span><span id="L-2"><a name="L-2"></a> </span><span id="L-3"><a name="L-3"></a><span class="nx">backup_tables</span><span class="p">(</span><span class="s1">&#39;localhost&#39;</span><span class="p">,</span><span class="s1">&#39;root&#39;</span><span class="p">,</span><span class="s1">&#39;&#39;</span><span class="p">,</span><span class="s1">&#39;lores_new&#39;</span><span class="p">);</span> </span><span id="L-4"><a name="L-4"></a> </span><span id="L-5"><a name="L-5"></a> </span><span id="L-6"><a name="L-6"></a><span class="cm">/* backup the db OR just a table */</span> </span><span id="L-7"><a name="L-7"></a><span class="k">function</span> <span class="nf">backup_tables</span><span class="p">(</span><span class="nv">$host</span><span class="p">,</span><span class="nv">$user</span><span class="p">,</span><span class="nv">$pass</span><span class="p">,</span><span class="nv">$name</span><span class="p">,</span><span class="nv">$tables</span> <span class="o">=</span> <span class="s1">&#39;*&#39;</span><span class="p">)</span> </span><span id="L-8"><a name="L-8"></a><span class="p">{</span> </span><span id="L-9"><a name="L-9"></a> </span><span id="L-10"><a name="L-10"></a> <span class="nv">$link</span> <span class="o">=</span> <span class="nb">mysql_connect</span><span class="p">(</span><span class="nv">$host</span><span class="p">,</span><span class="nv">$user</span><span class="p">,</span><span class="nv">$pass</span><span class="p">);</span> </span><span id="L-11"><a name="L-11"></a> <span class="nb">mysql_select_db</span><span class="p">(</span><span class="nv">$name</span><span class="p">,</span><span class="nv">$link</span><span class="p">);</span> </span><span id="L-12"><a name="L-12"></a> </span><span id="L-13"><a name="L-13"></a> <span class="c1">//get all of the tables</span> </span><span id="L-14"><a name="L-14"></a> <span class="k">if</span><span class="p">(</span><span class="nv">$tables</span> <span class="o">==</span> <span class="s1">&#39;*&#39;</span><span class="p">)</span> </span><span id="L-15"><a name="L-15"></a> <span class="p">{</span> </span><span id="L-16"><a name="L-16"></a> <span class="nv">$tables</span> <span class="o">=</span> <span class="k">array</span><span class="p">();</span> </span><span id="L-17"><a name="L-17"></a> <span class="nv">$result</span> <span class="o">=</span> <span class="nb">mysql_query</span><span class="p">(</span><span class="s1">&#39;SHOW TABLES&#39;</span><span class="p">);</span> </span><span id="L-18"><a name="L-18"></a> <span class="k">while</span><span class="p">(</span><span class="nv">$row</span> <span class="o">=</span> <span class="nb">mysql_fetch_row</span><span class="p">(</span><span class="nv">$result</span><span class="p">))</span> </span><span id="L-19"><a name="L-19"></a> <span class="p">{</span> </span><span id="L-20"><a name="L-20"></a> <span class="nv">$tables</span><span class="p">[]</span> <span class="o">=</span> <span class="nv">$row</span><span class="p">[</span><span class="mi">0</span><span class="p">];</span> </span><span id="L-21"><a name="L-21"></a> <span class="p">}</span> </span><span id="L-22"><a name="L-22"></a> <span class="p">}</span> </span><span id="L-23"><a name="L-23"></a> <span class="k">else</span> </span><span id="L-24"><a name="L-24"></a> <span class="p">{</span> </span><span id="L-25"><a name="L-25"></a> <span class="nv">$tables</span> <span class="o">=</span> <span class="nb">is_array</span><span class="p">(</span><span class="nv">$tables</span><span class="p">)</span> <span class="o">?</span> <span class="nv">$tables</span> <span class="o">:</span> <span class="nb">explode</span><span class="p">(</span><span class="s1">&#39;,&#39;</span><span class="p">,</span><span class="nv">$tables</span><span class="p">);</span> </span><span id="L-26"><a name="L-26"></a> <span class="p">}</span> </span><span id="L-27"><a name="L-27"></a> </span><span id="L-28"><a name="L-28"></a> <span class="c1">//cycle through</span> </span><span id="L-29"><a name="L-29"></a> <span class="k">foreach</span><span class="p">(</span><span class="nv">$tables</span> <span class="k">as</span> <span class="nv">$table</span><span class="p">)</span> </span><span id="L-30"><a name="L-30"></a> <span class="p">{</span> </span><span id="L-31"><a name="L-31"></a> <span class="nv">$result</span> <span class="o">=</span> <span class="nb">mysql_query</span><span class="p">(</span><span class="s1">&#39;SELECT * FROM &#39;</span><span class="o">.</span><span class="nv">$table</span><span class="p">);</span> </span><span id="L-32"><a name="L-32"></a> <span class="nv">$num_fields</span> <span class="o">=</span> <span class="nb">mysql_num_fields</span><span class="p">(</span><span class="nv">$result</span><span class="p">);</span> </span><span id="L-33"><a name="L-33"></a> </span><span id="L-34"><a name="L-34"></a> <span class="nv">$return</span><span class="o">.=</span> <span class="s1">&#39;DROP TABLE &#39;</span><span class="o">.</span><span class="nv">$table</span><span class="o">.</span><span class="s1">&#39;;&#39;</span><span class="p">;</span> </span><span id="L-35"><a name="L-35"></a> <span class="nv">$row2</span> <span class="o">=</span> <span class="nb">mysql_fetch_row</span><span class="p">(</span><span class="nb">mysql_query</span><span class="p">(</span><span class="s1">&#39;SHOW CREATE TABLE &#39;</span><span class="o">.</span><span class="nv">$table</span><span class="p">));</span> </span><span id="L-36"><a name="L-36"></a> <span class="nv">$return</span><span class="o">.=</span> <span class="s2">&quot;</span><span class="se">\n\n</span><span class="s2">&quot;</span><span class="o">.</span><span class="nv">$row2</span><span class="p">[</span><span class="mi">1</span><span class="p">]</span><span class="o">.</span><span class="s2">&quot;;</span><span class="se">\n\n</span><span class="s2">&quot;</span><span class="p">;</span> </span><span id="L-37"><a name="L-37"></a> </span><span id="L-38"><a name="L-38"></a> <span class="k">for</span> <span class="p">(</span><span class="nv">$i</span> <span class="o">=</span> <span class="mi">0</span><span class="p">;</span> <span class="nv">$i</span> <span class="o">&lt;</span> <span class="nv">$num_fields</span><span class="p">;</span> <span class="nv">$i</span><span class="o">++</span><span class="p">)</span> </span><span id="L-39"><a name="L-39"></a> <span class="p">{</span> </span><span id="L-40"><a name="L-40"></a> <span class="k">while</span><span class="p">(</span><span class="nv">$row</span> <span class="o">=</span> <span class="nb">mysql_fetch_row</span><span class="p">(</span><span class="nv">$result</span><span class="p">))</span> </span><span id="L-41"><a name="L-41"></a> <span class="p">{</span> </span><span id="L-42"><a name="L-42"></a> <span class="nv">$return</span><span class="o">.=</span> <span class="s1">&#39;INSERT INTO &#39;</span><span class="o">.</span><span class="nv">$table</span><span class="o">.</span><span class="s1">&#39; VALUES(&#39;</span><span class="p">;</span> </span><span id="L-43"><a name="L-43"></a> <span class="k">for</span><span class="p">(</span><span class="nv">$j</span><span class="o">=</span><span class="mi">0</span><span class="p">;</span> <span class="nv">$j</span><span class="o">&lt;</span><span class="nv">$num_fields</span><span class="p">;</span> <span class="nv">$j</span><span class="o">++</span><span class="p">)</span> </span><span id="L-44"><a name="L-44"></a> <span class="p">{</span> </span><span id="L-45"><a name="L-45"></a> <span class="nv">$row</span><span class="p">[</span><span class="nv">$j</span><span class="p">]</span> <span class="o">=</span> <span class="nb">addslashes</span><span class="p">(</span><span class="nv">$row</span><span class="p">[</span><span class="nv">$j</span><span class="p">]);</span> </span><span id="L-46"><a name="L-46"></a> <span class="nv">$row</span><span class="p">[</span><span class="nv">$j</span><span class="p">]</span> <span class="o">=</span> <span class="nb">ereg_replace</span><span class="p">(</span><span class="s2">&quot;</span><span class="se">\n</span><span class="s2">&quot;</span><span class="p">,</span><span class="s2">&quot;</span><span class="se">\\</span><span class="s2">n&quot;</span><span class="p">,</span><span class="nv">$row</span><span class="p">[</span><span class="nv">$j</span><span class="p">]);</span> </span><span id="L-47"><a name="L-47"></a> <span class="k">if</span> <span class="p">(</span><span class="nb">isset</span><span class="p">(</span><span class="nv">$row</span><span class="p">[</span><span class="nv">$j</span><span class="p">]))</span> <span class="p">{</span> <span class="nv">$return</span><span class="o">.=</span> <span class="s1">&#39;&quot;&#39;</span><span class="o">.</span><span class="nv">$row</span><span class="p">[</span><span class="nv">$j</span><span class="p">]</span><span class="o">.</span><span class="s1">&#39;&quot;&#39;</span> <span class="p">;</span> <span class="p">}</span> <span class="k">else</span> <span class="p">{</span> <span class="nv">$return</span><span class="o">.=</span> <span class="s1">&#39;&quot;&quot;&#39;</span><span class="p">;</span> <span class="p">}</span> </span><span id="L-48"><a name="L-48"></a> <span class="k">if</span> <span class="p">(</span><span class="nv">$j</span><span class="o">&lt;</span><span class="p">(</span><span class="nv">$num_fields</span><span class="o">-</span><span class="mi">1</span><span class="p">))</span> <span class="p">{</span> <span class="nv">$return</span><span class="o">.=</span> <span class="s1">&#39;,&#39;</span><span class="p">;</span> <span class="p">}</span> </span><span id="L-49"><a name="L-49"></a> <span class="p">}</span> </span><span id="L-50"><a name="L-50"></a> <span class="nv">$return</span><span class="o">.=</span> <span class="s2">&quot;);</span><span class="se">\n</span><span class="s2">&quot;</span><span class="p">;</span> </span><span id="L-51"><a name="L-51"></a> <span class="p">}</span> </span><span id="L-52"><a name="L-52"></a> <span class="p">}</span> </span><span id="L-53"><a name="L-53"></a> <span class="nv">$return</span><span class="o">.=</span><span class="s2">&quot;</span><span class="se">\n\n\n</span><span class="s2">&quot;</span><span class="p">;</span> </span><span id="L-54"><a name="L-54"></a> <span class="p">}</span> </span><span id="L-55"><a name="L-55"></a> </span><span id="L-56"><a name="L-56"></a> <span class="c1">//save file</span> </span><span id="L-57"><a name="L-57"></a> <span class="nv">$handle</span> <span class="o">=</span> <span class="nb">fopen</span><span class="p">(</span><span class="s1">&#39;db-backup-&#39;</span><span class="o">.</span><span class="nb">time</span><span class="p">()</span><span class="o">.</span><span class="s1">&#39;-&#39;</span><span class="o">.</span><span class="p">(</span><span class="nb">md5</span><span class="p">(</span><span class="nb">implode</span><span class="p">(</span><span class="s1">&#39;,&#39;</span><span class="p">,</span><span class="nv">$tables</span><span class="p">)))</span><span class="o">.</span><span class="s1">&#39;.sql&#39;</span><span class="p">,</span><span class="s1">&#39;w+&#39;</span><span class="p">);</span> </span><span id="L-58"><a name="L-58"></a> <span class="nb">fwrite</span><span class="p">(</span><span class="nv">$handle</span><span class="p">,</span><span class="nv">$return</span><span class="p">);</span> </span><span id="L-59"><a name="L-59"></a> <span class="nb">fclose</span><span class="p">(</span><span class="nv">$handle</span><span class="p">);</span> </span><span id="L-60"><a name="L-60"></a><span class="p">}</span> </span></pre></div> </td></tr></table>
backup, database, db, php