how to backup mysql database

[anfast]

how to backup mysql database? Thanks.

[NickCatal]

You are going to need to use the mysqldump program.

Check out the official documentation on the program

After you dump the database, you are going to want to compress it somehow (the dumped file will be a lot of INSERT INTO commands, over and over and over again) and then save it.

Do this and you should be able to rollback to previous versions of software, which is nice if you are upgrading something like a forum where an upgrade could break any plugins/themes you may be using.

Also useful for testing purposes. You can insert it so that it has a different database name, then you can mess with a real copy of your data without screwing up what your actual visitors are seeing

[Ray]

If you are a shared hosting customer, you will need to make sure to specify the host (mysql.steadfast.net) that you are connecting to. The command should look similar to:

mysqldump -h mysql.steadfast.net -u mysql_username -ppassword mysql_database > backup_file.sql

Note the lack of space between the -p and the password. That is intentional. If you do not specify the password like that, it will prompt for a password, which should not be an issue if running it manually. If you are creating a script for backing up the databases though, you would need to have that entered in there.

[gunwitch]

Is it possible to backup MySQL databases automatically?
(cron job or a tool able to create a copy of DB every time when DB content is changed)

[Ray]

Sure.

You could create a cron job through the control panel to automatically back up your databases to an FTP accessible location or come up with a lot of ways to store the backed up data.

Doing so every time any data is changed could be extremely resource intensive, but a cron job every so often would probably not result in any problems.

[Scoundrel]

I'd suggest to use LVM snapshots for backups. This is really great method to create consistent backups quickly.

[anurdh65]

A Simple Database Backup:

You can use mysqldump to create a simple backup of your database using the following syntax.

mysqldump -u [username] -p [password] [databasename] > [backupfile.sql]

o [username] - this is your database username
o [password] - this is the password for your database
o [databasename] - the name of your database
o [backupfile.sql] - the file to which the backup should be written.


The resultant dump file will contain all the SQL statements needed to create the table and populate the table in a new database server. To backup your database 'Customers' with the username 'sadmin' and password 'pass21' to a file custback.sql, you would issue the command:

mysqldump -u sadmin -p pass21 Customers > custback.sql

You can also ask mysqldump to add a drop table command before every create command by using the option --add-drop-table. This option is useful if you would like to create a backup file which can rewrite an existing database without having to delete the older database manually first.

mysqldump --add-drop-table -u sadmin -p pass21 Customers > custback.sql

Backing up only specified tables

If you'd like restrict the backup to only certain tables of your database, you can also specify the tables you want to backup. Let's say that you want to backup only customer_master & customer_details from the Customers database, you do that by issuing

mysqldump --add-drop-table -u sadmin -p pass21 Customers customer_master customer_details> custback.sql

So the syntax for the command to issue is:

mysqldump -u [username] -p [password] [databasename] [table1 table2 ....]

o [tables] - This is a list of tables to backup. Each table is separated by a space.

[Dougy]

If you are creating a script for backing up the databases though, you would need to have that entered in there. If you do not specify the password like that, it will prompt for a password, which should not be an issue if running it manually.

Good tip!

[ThomasAnderson]

There are some ways to back up the database of MYSQL:

Backup your MsSQL database automaticallly with Auto MySQLBackUP

Back up eith MySQLDump:
mysqldump ---user [user name] ---password=[password]
[database name] > [dump file]

[legatobd]

very helpful question