•  Print this page
    Print this page

MySQL
 
 
MySQL is a fast, multi-threaded, multi-user open source SQL database server.

The MySQL database is released under the GNU General Public License (GPL). MySQL can be used for free when you are not distributing any part of the MySQL system. For example: If you run a commercial web site using the MySQL Database Software as long as MySQL is not distributed.

Please read the MySQL manual for situations where a commercial license is needed.

More information about MySQL can be found at: https://www.mysql.com

MySQL online manual can be found at: https://dev.mysql.com/doc/index.html

The latest MySQL version can be downloaded from: https://dev.mysql.com/downloads/index.html

Quick guides







Backup and restore MySQL database.



Information
None

Operating system used
Windows XP Home Edition Version 5.1 SP 2

Software prerequisites
MySQL 3.23 or higher

Procedure
  1. Goto the MySQL bin directory, e.g.: C:\Tools\mysql\bin

  2. There are several methods to backup MySQL database(s).
    • To BACKUP ONE MySQL database you can use the following command:
      mysqldump --allow-keywords --opt -u[username] -p[password] [database] > [backup_file]

      For example:
      mysqldump --allow-keywords --opt -uroot -pmysecret mambo > mambo_backup_24092004.sql

    • If you want to compress the backup file, you first need to install Cygwin which contains the gzip.exe tool:
      mysqldump --allow-keywords --opt -u[username] -p[password] [database] | gzip > [backup_file.gz]

      For example:
      mysqldump --allow-keywords --opt -uroot -pmysecret mambo | gzip > mambo_backup_24092004.gz

    • If you want to backup ALL databases, you can use the following command:
      mysqldump --allow-keywords --opt -u[username] -p[password] --all-databases > [backup_file]

      For example:
      mysqldump --allow-keywords --opt -uroot -pmysecret > backup_all_databases_24092004.sql



    • If you only want to backup the table structures (no content), you can use the following command:
      mysqldump --allow-keywords --opt -u[username] -p[password] --no-data [database] > [backup_file]

      For example:
      mysqldump --allow-keywords --opt -uroot -pmysecret --no-data mambo > mambo_structure_backup_24092004.sql

    • If you only want to backup the content of the tables (no table structure), you can use the following command:
      mysqldump --allow-keywords --opt -u[username] -p[password] --no-create-info [database] > [backup_file]

      For example:
      mysqldump --allow-keywords --opt -uroot -pmysecret --no-create-info mambo > mambo_content_backup_24092004.sql

  3. There are several methods to restore MySQL database(s).
    • To RESTORE ONE MySQL database you can use the following command:
      mysql -u[username] -p[password] [database] < [backup_file]

      For example:
      mysql -uroot -pmysecret mambo < mambo_backup_24092004.sql

    • If you want to restore the database from a compressed backup file:
      gunzip < [backup_file.gz] | mysql -u[username] -p[password] [database]

      For example:
      gunzip < mambo_backup_24092004.gz | mysql -uroot -pmysecret mambo

    • If you want to restore ALL databases (you have used option --all-databases) you can use the following command:
      mysql -u[username] -p[password] < [backup_file]

      For example:
      mysql -uroot -pmysecret< backup_all_databases_24092004.sql