<?php
/** @description: README.md

# Summary
Dumps individual MySQL table structure and data files, organized into directories by database name.
This makes it easier to keep track of updates (via GIT) and/or restore individual tables.

Example file structure:
|_ mysql_dump.php
|_ database1
  |_ table1-structure.sql
  |_ table1-data.sql
|_ database2
  |_ table1-structure.sql
  |_ table1-data.sql

# Setup
1. Create a MySQL user with SELECT priveleges to your desired backup databases
2. Place this file in a main dump directory (e.g. /home/root/database-backups/)
3. Provide MySQL credentials below
4. Optionally specify a different directory to save the files in `$db_dump_dir`
5. Schedule a cron job or manually run this file on the command line

# Additional
* Setup a GIT repository to track changes to the database(s)

eof: README.md */

/**
 * @description
 * Configure database credentials
 */
define('DB_SERVER', 'localhost');
define('DB_SERVER_USERNAME', 'user');
define('DB_SERVER_PASSWORD', 'pass');
/**
 * or use a config file:
require('/path/to/mysql.conf.php');
 */

/**
 * @description
 * Optionally set a different directory to save the backups.
 * By default, it will save in this directory.
 */
$db_dump_dir = dirname(__FILE__) . '/';

/**
 * @description
 * Connect to the database as priveleged user with limited access (READ-only)
 */
$db = new mysqli(DB_SERVER, DB_SERVER_USERNAME, DB_SERVER_PASSWORD);

  $sql = "
    SELECT
      `TABLE_SCHEMA`,
      `TABLE_NAME`
  FROM `information_schema`.`TABLES`
  WHERE `TABLE_SCHEMA` NOT IN ('information_schema')
  ORDER BY
    `TABLE_SCHEMA` ASC,
    `TABLE_NAME` ASC ";
  $result = $db->query($sql);

while ($row = $result->fetch_assoc($result)) {
  $table_schema = $row['TABLE_SCHEMA'];
  $table_name = $row['TABLE_NAME'];

  /**
   * @description
   * Create directories by database name
   */
  $db_dir = $db_dump_dir . $table_schema . '/';
  if ( !is_dir($db_dir) ) {
      mkdir($db_dir);
      chmod($db_dir, 0777);
  }

  /**
   * @description
   * Dump filenames with postfixes for table structure vs. data
   */
  $table_structure_filename = $db_dir . $row['TABLE_NAME'] . '-structure.sql';
  $table_data_filename = $db_dir . $row['TABLE_NAME'] . '-data.sql';

  /**
   * @description
   * Dump the table schema
   */
  $table_structure = "mysqldump " .
      "--compact --skip-opt --add-drop-table --no-data " .
      "-u " . DB_SERVER_USERNAME . " --password=" . DB_SERVER_PASSWORD . " " .
      " " . $table_schema . " " . $table_name .
      " > " . $table_structure_filename;
  shell_exec($table_structure);

  /**
   * @description
   * Dump the table data
   */
  $table_data = "mysqldump ".
      "--compact --complete-insert --no-create-info --order-by-primary " .
      "-u " . DB_SERVER_USERNAME . " --password=" . DB_SERVER_PASSWORD . " " .
      " " . $table_schema . " " . $table_name .
      " > " . $table_data_filename;
  shell_exec($table_data);
}

?>