MySQL backup script and mysql script

MySQL backup script and mysql script

Mysqlbackup. php:

<? Php // back up mysql set_time_limit (0); date_default_timezone_set ('prc'); // configure $ configs = array ('host1' => array ('localhost', 'root ', 'root', array (), // empty to back up all databases; otherwise, back up these databases 'd:/xampp/mysql/bin/mysqldump ', // backup tool dirname (_ FILE __). "/localhost", // Add host name 5 to the directory, // Delete the SQL file from the previous 5 days),); foreach ($ configs as $ config) {$ logsfile = $ config [5]. '/'. date ('ymmd '). '. log'; logs ($ logsfile, $ config [0]. "backup \ n"); backup ($ config );} Function backup ($ config) {list ($ host, $ username, $ password, $ databases, $ backuptool, $ backupdir, $ day) = $ config; $ command = "$ backuptool-u $ username-h $ host-p $ password % s> % s"; $ logsfile = $ backupdir. '/'. date ('ymmd '). '. log'; $ backfilename = $ backupdir. '/'. date ('ymmd '). "% s. SQL "; // The backup SQL file named if (! Is_dir ($ backupdir) {mkdir ($ backupdir, 0755, true);} // Delete the backup file get_dir_files ($ backupdir, $ returnval) in the last 10 days; if ($ returnval) {foreach ($ returnval as $ v) {$ time = filemtime ($ v); if ($ time <strtotime ("-$ day ") & (pathinfo ($ v, PATHINFO_EXTENSION) = 'zip') {unlink ($ v) ;}} if (! $ Databases) {$ databases = getdatabases ($ host, $ username, $ password);} // start backup foreach ($ databases as $ database) {$ outputfile = sprintf ($ backfilename, $ database); $ execcommand = sprintf ($ command, $ database, $ outputfile); try {if (system ($ execcommand) === false) {throw new Exception ('execute backup command error! ');} // If the file is too large, the compression will fail (the SQL file tested is 4.62 GB, and the compression fails. The compressed file is not created. Test 2.81G can) if (file_exists ($ outputfile) {$ zip = new ZipArchive (); $ filename = pathinfo ($ outputfile, PATHINFO_FILENAME); $ zipname = $ backupdir. '/'.w.filename.'.zip'; // if ($ zip-> open ($ zipname, ZIPARCHIVE: OVERWRITE) = true) {$ zip-> addFile ($ outputfile, $ filename. '. '. pathinfo ($ outputfile, PATHINFO_EXTENSION); $ zip-> close ();} else {throw new Exception ('ziparchive open error! ') ;}} If (! File_exists ($ zipname) | (filesize ($ zipname) = 0) {throw new Exception ('ziparchive create error! ');} $ Message = date ('Y-m-d H: I: s'). "$ database backup complete! \ R \ n "; logs ($ logsfile, $ message);} catch (Exception $ e) {$ message = date ('Y-m-d H: I: s '). $ e-> getLine (). ''. $ e-> getMessage (). "\ r \ n"; logs ($ logsfile, $ message) ;}} function getdatabases ($ host, $ username, $ password) {$ databases = array (); try {$ mysqli = new Mysqli ($ host, $ username, $ password); $ result = $ mysqli-> query ("show databases"); if ($ result) {while ($ row = $ result-> fetch_row () {(current ($ Row )! = 'Information _ scheme' & current ($ row )! = 'Mysql' & current ($ row )! = 'Performance _ scheme') & $ databases [] = current ($ row);} $ result-> free_result (); $ mysqli-> close ();} else {throw new Exception ('no databases! ') ;}} Catch (Exception $ e) {$ message = date ('Y-m-d H: I: s '). $ e-> getLine (). ''. $ e-> getMessage (). "\ r \ n"; logs ($ logsfile, $ message);} return $ databases;} function logs ($ file, $ contents) {$ dirname = dirname ($ file); try {if (! Is_dir (dirname ($ file) |! Is_writeable (dirname ($ file) {throw new Exception ("file is not direcoprocessor or file can't write");} file_put_contents ($ file, $ contents, FILE_APPEND);} catch (Exception $ e) {$ message = date ('Y-m-d H: I: s '). $ e-> getLine. ''. $ e-> getMessage (). "\ r \ n"; logs ($ logfile, $ message) ;}// get the files in the current directory (excluding subfolders) function get_dir_files ($ currPath, & $ returnVal = array () {if (is_dir ($ currPath) {$ currPath = (substr ($ c UrrPath,-1, 1) = '/')? Substr ($ currPath, 0, strlen ($ currPath)-1): $ currPath; if ($ handler = opendir ($ currPath )) {while ($ fileName = readdir ($ handler ))! = False) {if ($ fileName! = '.' & $ FileName! = '..' & $ FileName [0]! = '. ') {If (is_file ($ currPath. '/'. $ fileName) {$ returnVal [] = $ currPath. '/'. $ fileName ;}} closedir ($ handler) ;}}}?>

 

Mysqlbackup. bat:

D: \ xampp \ php \ php.exe-q D: \ wamp \ www \ php_lib \ basic \ mysqlbackup. php
Pause;

Linux shell backup MySQL:

#! /Bin/sh # sed-I's/^ M // G'/home/taskschd/backup. sh # Note: ^ The input method of M is Ctrl + v, and then Ctrl + Mdbs = (test) ROOT_DIR =/home/backup/for dbname in $ {dbs [@]} do # backup data BACKUP_DIR = $ ROOT_DIR $ dbname '_' $ (date + % Y % m % d ). SQL/usr/local/mysql/bin/mysqldump -- opt-uroot-pabc $ dbname> $ BACKUP_DIR # delete data three days ago delete_file = $ dbname '_' $ (date-d "-5 day" "+ % Y % m % d ") '. SQL 'rm $ ROOT_DIR $ delete_file done

A shell backup mysql Script: http://www.cnblogs.com/luoyunshu/p/3435378.html

How does one back up mySQL Databases in linux?

Method 1: Suitable for mysql databases in all formats. It is regularly executed by exporting and importing a script to the database:
1. Export the entire database mysqldump-u user name-p Database Name> exported file name mysqldump-u wcnc-p smgp_1__wcnc>/storage path/wcnc. SQL
2. Export a table mysqldump-u user name-p database name Table Name> exported file name mysqldump-u wcnc-p smgp_rj_wcnc users>/storage path/wcnc_users. SQL
3. Export a database structure mysqldump-u wcnc-p-d -- add-drop-table smgp_rj_wcnc>/storage path/wcnc_db. SQL
Definition:
-D no data
-- Add-drop-table: add a drop table before each create statement.
4. Run the source command to import the database to the mysql Database Console:
For example, mysql-u root-p mysql> use Database

Method 2: For mysql Data Tables in the format of MyISAM
Assume that the data file is in/var/lib/mysql
Write a script directly.
Cp-r/var/lib/mysql/directory to which the backup is saved

Remote backup uses rsync increments or regular full backup.

Who can give me a script for MYSQL automatic backup?

Maybe you won't use it.

You can put this script into crontab and execute it once every morning to automatically back up data.

This script can be executed only once a day, and only the backups of the last five days are retained on the server.

Code:

#! /Bin/bash
# This is a ShellScript For Auto DB Backup
# Powered by aspbiz
#2004-09

# Setting
# Set the database name, database login name, password, backup path, Log Path, data file location, and backup mode
# By default, the backup method is tar, mysqldump, or mysqldotcopy.
# By default, log on to the mysql database with root (empty) and back up to/root/dbxxxxx. tgz
DBName = mysql
DBUser = root
DBPasswd =
BackupPath =/root/
LogFile =/root/db. log
DBPath =/var/lib/mysql/
# BackupMethod = mysqldump
# BackupMethod = mysqlhotcopy
# BackupMethod = tar
# Setting End

NewFile = "$ BackupPath" db $ (date + % y % m % d). tgz
DumpFile = "$ BackupPath" db $ (date + % y % m % d)
OldFile = "$ BackupPath" db $ (date + % y % m % d -- date = '5 days ago '). tgz

Echo "-----------------------------------------"> $ LogFile
Echo $ (date + "% y-% m-% d % H: % M: % S") >>$ LogFile
Echo "--------------------------"> $ LogFile
# Delete Old File
If [-f $ OldFile]
Then
Rm-f $ OldFile >>$ LogFile 2> & 1
Echo "[$ OldFile] Delete Old File Success! ">>> $ LogFile
Else
Echo "[$ OldFile] No Old Backup File! ">>> $ LogFile
Fi

If [-f $ NewFile]
Then
Echo "[$ NewFile] The Backup File is exists, Can't Backup! ">>> $ LogFile
Else
Case $ BackupMethod in
Mysqldump)
If [-z $ DBPasswd]
Then
Mysqldump-u $ DBUser -- opt $ DBName> $ DumpFile
Else
Mysqldump-u $ DBUser-p $ DBPasswd -- opt $ DBName> $ DumpFile
F... the remaining full text>