Posts Tagged ‘linux machine’
Whenever there is a database involved, an important task that should never be neglected is backing it up. In the case of MySQL on a Linux machine, backing up consists of executing a single command.
Backing up a MySQL database can be done with one simple command:
mysqldump -h <host> -u <user> -p<password> <db_name> > backup.sql
For example if you want to backup a local database called db1 using the user user1 with password pass1 you should write:
mysqldump -h localhost -u user1 -ppass1 db1 > backup.sql
The backup.sql file contains an SQL query for creating the exact same database. Restoring such a dump-file is just a matter of one command too:
mysql -h <host> -u <user> -p<password> <db_name> < backup.sql
Following the naming from our previous example will result in the following command to restore the database:
mysql -h localhost -u user1 -ppass1 db1 < backup.sql
In the perfect environment the backup should be created by a cronjob or anacronjob.
Another useful thing that one may want to backup is the table structure. For tables whose structure is relatively often changed this can prove to be very helpful. It is done by using one simple modification of the command for backing up an entire database:
mysqldump -d -h <host> -u <user> -p<password> <db_name> > backup.sql
Did you notice the difference? It is the -d option, which specifies that data should not be dumped. Using our previous examples this will be transform to:
mysqldump -d -h localhost -u user1 -ppass1 db1 > backup.sql