How do I backup MySQL in Linux?

How do I backup MySQL in Linux?

1. Copying from the mysql directory
By default, MySQL databases on servers that use Linux are stored in the following directory:
/var/lib/mysql/
If you shut down the mysqld service first, you can copy your databases to an example /backup directory using the following command:
cp –Rp /var/lib/mysql/*.* /backup

The –R switch for the cp command means recursive, which you want to use because each database is in a separate directory. The –p switch is for permissions, which will maintain the permissions of what is copied.
You generally want to shutdown the mysqld service before using the above method because if a database is copied while it is actively being used, the resulting backup will be corrupt and therefore worthless. If you are certain none of the databases are not being used at the time, you can use the above command.


2. The mysqldump command
The mysqldump command lets you back up both individual databases and all databases on a server without having to shutdown the mysqld service. Because of this ability to make backups while still keeping databases online, this method is preferred.

 

Individual databases
An example command that would let you back up a database named example to the directory /backup while logged in as root is as follows:
mysqldump example > /backup/example_backup.sql

Unless it is a small database, it is recommended that you then compress the resulting database backup in order to reduce the amount of time necessary to transfer the backup. The following command would compress the backup of the example database:
tar czvf /backup/example_backup.tar.gz /backup./example_backup.sql

 

All database
If you have numerous databases and backing all of them up individually would be too time consuming, the following command will backup all MySQL databases on your server to the /backup directory:
mysqldump -A > /backup/databases.sql(or --all-databases)
The –A switch (“-all-databases” performs the same function) will dump any and all databases on the server.

    • Related Articles

    • Mysql Basic Commands

      [mysql dir]/bin/mysql -h hostname -u root -p Create a database on the sql server. create database [databasename]; List all databases on the sql server. show databases; Switch to a database. use [db name]; To see all the tables in the db. show tables; ...

    • How can I monitor what my MySQL server is doing?

      A handy little Linux application called mytop fits this just perfectly. This is a near-time monitor just like the UNIX utility 'top' that specifically looks at what the MySQL server is doing. It updates every few seconds so you can get a reasonable ...

    • Securing MySQL

      Due to differing needs and requirements this is difficult to answer except on a case by case basis. The MySQL website has a section regarding general security of a MySQL database available here: http://dev.mysql.com/doc/refman/5.0/en/security.html ...

    • Common SSH Commands - Linux Shell Commands

      Navigating in UNIX pwd Shows the full path of the current directory ls Lists all the files in the current directory ls -al Lists all files and information ls –alR Lists all files and information in all subdirectories ls -alR | more Same as ls –alR, ...

    • MySQL Optimization / Repair Information

      How MySQL Uses Memory This page lists some of the ways that the mysqld server uses memory, and associated mysqld variable names Memory Use MySQL 5.0 Memory Use MySQL 4.1 MySQL Optimization which covers: - Optimization Overview - Optimizing SELECT and ...