imported>Hundfred No edit summary |
imported>Hundfred No edit summary |
||
(3 intermediate revisions by the same user not shown) | |||
Line 34: | Line 34: | ||
[[http://s23.org/wiki/Image:Backup_with_mysqladmin.PNG]] |
[[http://s23.org/wiki/Image:Backup_with_mysqladmin.PNG]] |
||
[[http://s23.org/wiki/Image:Backup_with_mysqladmin_advanced_properties.PNG]] |
|||
if you plan to have a scheduled backup with mysql-administrator, then you have to enter a connection-profile and to make sure, that passwords are saved in that profile. |
|||
[[http://s23.org/wiki/Image:Backup_with_mysqladmin_connection_profile_save_passwords.PNG]] |
|||
also we need to enable [[mysql write ahead logs (bin-logs)]] in the mysql-configuration file |
|||
together with the database-dump and those bin-logs it is possilbe to do a recovery to a certain timestamp, |
|||
see [[writeback_mysql_bin-logs_to_the_myslq-database]] |
Latest revision as of 12:48, 23 June 2009
mysql-backup[edit]
using mysqldump with the command lock-tables doesn't lead to a consistent backup with innodb databases, and just works fine with myissam databases.
this command you can use to get a consistent backup
C:\DOKUME~1\Admin> mysqldump -u root -p --skip-opt --single-transaction --add-drop-table --create-option --quick --extended-insert --set-charset --disable-keys --database <DATABASE-NAME> > backup.sql Enter password: ****** C:\DOKUME~1\Admin>
with this stored-procedures are not included in that backup this can happen with this command
C:\DOKUME~1\Admin>mysqldump -u root -p mysql proc > backup_all_stored_procedures.sql Enter password: ****** C:\DOKUME~1\Admin>
this will backup ALL stored procedures if you want just to backup stored procedures for a single database, then you can try this one here:
mysqldump -u root -p --where='db=testdbsm' --no-create-info mysql proc > backup_stored_procedure_of_this_db.sql
in the version 6 of mysql there will be an extra-tool for backup.
there is also a perl tool to do a mysql-backup [[1]]
here some screenshots, how to do a backup with mysql-administrator
[[2]] [[3]] if you plan to have a scheduled backup with mysql-administrator, then you have to enter a connection-profile and to make sure, that passwords are saved in that profile. [[4]]
also we need to enable mysql write ahead logs (bin-logs) in the mysql-configuration file
together with the database-dump and those bin-logs it is possilbe to do a recovery to a certain timestamp, see writeback_mysql_bin-logs_to_the_myslq-database