122 lines
2.7 KiB
Plaintext
122 lines
2.7 KiB
Plaintext
|
|
||
|
|
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(),
|
||
|
|
),
|
||
|
|
),
|
||
|
|
),
|
||
|
|
|