Backup de Mysql

[Amigo_zz 0]

Colegas,Preciso de ler uma base de dados mysql, saber que tabelas la tem dentro, saber que campos e suas propriedades, e ler os seus registos para consegui criar um ficheiro .SQL e enviar por email.Tudo isto para agendar uma tarefa no servidor que todos os dias de madrugada, faça backup de todas as bases de dados que tenho dos meus clientes.Alguem ja se deve ter lembrado isso. Alguma dica?Obrigado

[jrcardozo 4]

posso estar completamente errado, mas ai vai o chute....não seria com fso o back up e uma rotina para verificar o horario do servidor para execurar a cópia?

[Amigo_zz 0]

posso estar completamente errado, mas ai vai o chute....não seria com fso o back up e uma rotina para verificar o horario do servidor para execurar a cópia?

Não. Existe na linguagem SQL instruções para ler as tabelas, campos e propriedades. Com isso poderei criar o ficheiro .SQL e enviar email. O agendamento faço no windows mesmo visto que tenho servidor dedicado.Obrigado.

[arr 0]

Amigo ZZUso o seguinte script para backup do MySQL, ele faz o backup de todos os BDs, zipa e envia por email.Na verdade eu não uso o zip nem o email.É é só copiar uns utilitários (FREE, tem o caminho nas instruçoes), setar o usuario e senha, salvar como .bat na pasta bin do MYSQL e agendar no servidor:

//--- Begin Batch File ---//:::: Creates a backup of all databases in MySQL.:: Zip, encrypts and emails the backup file.:::: Each database is saved to a seperate file in a new folder.:: The folder is zipped and then deleted. :: the zipped backup is encrypted and then emailed, unless the file exceeds the maximum filesize:: In all cases the logfile is emailed.:: The encrypted backup is deleted, leaving the unencrypted zipfile on your local machine.:::: Version 1.1 :::: Changes in version 1.1 (released June 29th, 2006):: - backups are now sent to the address specified by the mailto variable:::: The initial version 1.0 was released on May 27th, 2006:: :::: This version of the script was written by Mathieu van Loon (mathieu-public@jijenik.com):: It is based heavily on the script by Wade Hedgren (see comments at http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html):::: This script requires several freeware libraries::: - zipgenius (a compression tool), www.zipgenius.it:: - blat (an emailer tool), www.blat.net:: - doff (extracts datetime, ignores regional formatting), www.jfitz.com/dos/index.html:::: Some areas where this script could be improved::: - include error trapping and handling:: - make steps such as encryption and email optional :: - allow the user to specify a single database on the command line::@echo off:::: Configuration options:::: The threshold for emailing the backup file. If the backup is larger :: it will not be emailed (the logfile is always sent).set maxmailsize=10000000:: The passphrase used to encrypt the zipfile. Longer is more secure.set passphrase=secret:: Name of the database userset dbuser=root:: Password for the database userset dbpass=password:: Recipients of database backup, comma seperated, enclosed in quotesset mailto="backups@example.com,backups2@example.com":: From address for emailset mailfrom="MySQL Backup Service <noreply@example.com>":: Email serverset mailsmtp=localhost:: Email subjectset mailsubject="MySQL Backup":: directory where logfiles are storedset logdir=C:\DatabaseBackups\logs:: directory where backup files are storedset bkupdir=C:\DatabaseBackups:: Install folder of MySQLset mysqldir=C:\Program Files (x86)\MySQL\MySQL Server 4.1:: Data directory of MySQL (only used to enumerate databases, we use mysqldump for backup)set datadir=C:\Program Files (x86)\MySQL\MySQL Server 4.1\data:: Path of zipgenius compression toolset zip=C:\Program Files (x86)\ZipGenius 6\zg.exe:: Path of blat mail toolset mail=C:\DatabaseBackups\Backupscript\libraries\Blat250\full\blat.exe:: Path of doff date tool (specify only the folder not the exe)set doff=C:\DatabaseBackups\Backupscript\libraries\doff10:::::: NO NEED TO CHANGE ANYTHING BELOW:::::: get the date and then parse it into variablespushd %doff%for /f %%i in ('doff.exe yyyymmdd_hhmiss') do set fn=%%ifor /f %%i in ('doff.exe dd-mm-yyyy hh:mi:ss') do set nicedate=%%ipopdset logfile="%logdir%\%fn%_Backuplog.txt":: Switch to the data directory to enumerate the folderspushd "%datadir%":: Write to the log fileecho Beginning MySQLDump Process > %logfile%echo Start Time = %nicedate% >> %logfile%echo --------------------------- >> %logfile%echo. >> %logfile%:: Create the backup folderif not exist "%bkupdir%\%fn%\" (echo Making Directory %fn%echo Making Directory %fn% >> %logfile%mkdir "%bkupdir%\%fn%"):: Loop through the data structure in the data dir to get the database namesfor /d %%f in (*) do (:: Run mysqldump on each database and compress the data by piping through gZipecho Backing up database %fn%_%%f.sqlecho Backing up database %fn%_%%f.sql >> %logfile%"%mysqldir%\bin\mysqldump" --user=%dbuser% --password=%dbpass% --databases %%f --opt --quote-names --allow-keywords --complete-insert > "%bkupdir%\%fn%\%fn%_%%f.sql"echo Done... >> %logfile%):: return from data dirpopdpushd %bkupdir%echo Zipping databasesecho Zipping databases >> %logfile%REM C9 : maximum compressionREM AM : Delete source filesREM F1 : Store relative pathREM R1 : include subfoldersREM K0 : Do not display progress"%zip%" -add "%fn%_MySQLBackup.zip" C9 AM F1 R1 K0 +"%bkupdir%\%fn%"echo Crypting zipfileecho Crypting zipfile >> %logfile%REM C : Create non-executable zipREM S : Do not delete after x triesREM 3 : Use AES encryption"%zip%" -encrypt "%fn%_MySQLBackup.zip" C S 3 "%passphrase%" %mailfrom%echo Deleting directory %fn%echo Deleting directory %fn% >> %logfile%rmdir /s /q "%bkupdir%\%fn%":: Go back and get the end time for the scriptset endtime=1:: return from backup dirpopd:: update the nicedate for the logpushd %doff%for /f %%i in ('doff.exe dd-mm-yyyy hh:mi:ss') do set nicedate=%%ipopd:: Write to the log fileecho. >> %logfile%echo --------------------------- >> %logfile%echo MySQLDump Process Finished >> %logfile%echo End Time = %nicedate% >> %logfile%echo. >> %logfile%:: Send the log file in an e-mail, include the backup file if it is not too large:: We use the CALL Trick to enable determination of the filesize (type CALL /? at prompt for info):: note that you _must_ specify the full filename as the argumentpushd %bkupdir%Call :MAILFILE "%bkupdir%\%fn%_MySQLBackup.czip"echo Backup completedgoto :EOF:MAILFILEif /i %~z1 LSS %maxmailsize% (echo Emailing backup file"%mail%" %logfile% -q -attach %1 -serverSMTP %mailsmtp% -f %mailfrom% -to %mailto% -subject %mailsubject%) ELSE (echo Size of backup file %~z1 B exceeds configured email size %maxmailsize% B.echo Emailing logfile onlyecho Size of backup file %~z1 B exceeds configured email size %maxmailsize% B. only emailing logfile. >> %logfile%"%mail%" %logfile% -q -serverSMTP %mailsmtp% -f %mailfrom% -to %mailto% -subject %mailsubject%)echo Deleting encrypted backup filedel %1popd//--- End Batch File ---//

[jrcardozo 4]

aos moderas...esse topico não deveria ficar no forum de banco de dados??