Backup and Restore Database
---------------------------
dbSchema.php
============
Get schema
dbDump.php
==========
Simple dump
dbBackup.php
============
SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = 'database'
SHOW TABLE STATUS LIKE 'table';
Backup big table.
A backup is a dir name as TYPE-YYYY.MM.DD.HHMMSS, with sql file fo each table (dbname.tablename.sql).
The backup are first place in a process dir which you can define outside the dir where you keep all you backup.
e.g to make an rsync only on the complete backup.
--type TYPE
[--config FILENAME]
Filename must be in th same dir of this script
Create an array to define backup options in a file
return array(
'mysql' => array(
'host' => "127.0.0.1",
'port' => 3306,
'user' => "user",
'pass' => "password",
// --- If the server is a master else a slave
'master' => 1,
'options' => "--quick --add-drop-table", ou "--opt"
// --- Method to compress : none, gzip, bzip2, pigz, 7zx, xz
'compress' => "gzip",
// --- backup dir
'dir' => "/backup/keep",
// --- Number of backup to keep
'max' => 3,
// --- In process dir
'processdir' => "/backup/inprocess"
),
'backup type/name' => array(
/* List database to backup - always include a db to list tables */
'db' => array(...),
/**
Rules to include or exclude database and table by name (preg_match)
db\.table
*\.*_tmp
*\.*_old
*\.*_mvt
*\.*_backup
*\.*_histo
*\.*_table
*_.*_user
*\.*_[0-9]{8}
*\.*_v[0-9]{1,}
*/
'rules' => array(
'in' => array(),
'ex' => array(),
),
),
),
dbImport.php
============
Import or Restore a backup.
To import in parallel, cut in several type and/or database
--name DIRNAME
which backup TYPE-YYYY.MM.DD.HHMMSS
[--type TYPE]
Specify a type to follow rules define
[--db name]
Restore only this database
[--config filename]
Filename must be in th same dir of this script
Create an array to define restore options in a file
return array(
'mysql' => array(
'host' => "127.0.0.1",
'port' => 3306,
'user' => "user",
'pass' => "password",
'options' => "",
// --- Method to compress : gzip, bzip2, pigz, 7zx
'compress' => "gzip",
// --- backup dir
'dir' => "/backup",
),
'backup type/name' => array(
/* List database to restore */
'db' => array(...),
/**
Rules to include or exclude table by his name
*.*_tmp
*.*_old
*.*_mvt
*.*_backup
*.*_histo
*.*_table
*.*_user
*.*_[0-9]{8}
*.*_v[0-9]{1,}
*/
'rules' => array(
'in' => array(),
'ex' => array(),
),
),
),
