Cooking with UNIX/Linux

	Cooking with UNIX/Linux
	Comm. with a T68i using IrDA
	Compiling Perl and modules
	Compiling MySQL
	MySQL backup
	Network File System (NFS)
	PostgreSQL backup
	Enabling your digicam
	nvidia drivers
	Wireless (OLD - linux-wlan-ng)
	Wireless cards / access points
	Gnu Privacy Guard (GPG)
	Getting started with CVS
	Compiling PHP with modules
	Courier Mail Server Suite
	Courier-ClamAV-Amavis combo
	SpamAssassin with Courier
	Building Debian kernel pkgs
	Trying out Bochs
	Rebuilding Debian packages
	Apache, mod_ssl, Tomcat

Cooking with UNIX/Linux > MySQL backup

MySQL backup

MYSQLDUMP RECIPE by Frederik Dannemare

To dump structures from one database, all tables and all data included,
and with "drop table" so you can restore overwriting existing tables:

You may have to enter username and password, in this case:

    mysqldump --add-drop-table -u username -p my_db > my_db.sql

and restore with:

    mysql -u username -p < my_db.sql my_db

*** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** ***

EXAMPLE OF MYSQL DATA MIGRATION BETWEEN HOSTS

1) Ensure database consistency on old host before migrating data:
mysql> REVOKE insert,update,delete,create,drop,references,index,alter
        ON some_db.* FROM some_user;
mysql> REVOKE insert,update,delete,create,drop,references,index,alter
        ON some_db.* FROM some_user@localhost;

2) Dump all databases on old host:
mysqldump -u root -p --opt --all-databases > dump_of_all_databases.sql
gzip dump_of_all_databases.sql

3) Restore all databases on new host:
zcat dump_of_all_databases.sql.gz | mysql -u root -p

4) Restore privileges to some_user:
mysql> GRANT insert,update,delete,create,drop,references,index,alter
        ON some_db.* TO some_user IDENTIFIED BY some_passwd;
mysql> GRANT insert,update,delete,create,drop,references,index,alter
        ON some_db.* TO some_user@localhost IDENTIFIED BY some_passwd;

*** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** ***

TOP