title: Automated or regular backup of mysql data
body: <p>I want to take regular backups of some tables in my mysql database using &lt;insert favorite PHP framework here&gt; / plain php / my second favorite language. I want it to be automated so that the backup can be restored later on in case something goes wrong.</p>
  
  <p>I tried executing a query and saving the results to a file. Ended up with code that looks somewhat like this. </p>
  
  <pre><code>$sql = 'SELECT * FROM my_table ORDER id DESC';
  $result = mysqli_query( $connect, $sql );  
  if( mysqli_num_rows( $result ) &gt; 0){
  
      $output=fopen('/tmp/dumpfile.csv','w+');
  
      /* loop through recordset and add that to the file */
      while( $row = mysqli_fetch_array( $result ) ) {
          fputcsv( $output, $row, ',', '"');
      }
  
      fclose( $output );
  }
  </code></pre>
  
  <p>I set up a cron job on my local machine to hit the web page with this code. I also tried writing a cronjob on the server run the script as a CLI. But it's causing all sorts of problems. These include</p>
  
  <ol>
  <li>Sometimes the data is not consistent    </li>
  <li>The file appears to be truncated    </li>
  <li>The output cannot be imported into another database  </li>
  <li>Sometimes the script times out</li>
  </ol>
  
  <p>I have also heard about <code>mysqldump</code>. I tried to run it with <code>exec</code> but it produces an error.</p>
  
  <p>How can I solve this?</p>