Mysqldump is a command line tool that can be used for a variety of tasks. These can include creating new MySQL instances and restoring specific tables. It is important to know that using
mysqldump securely is essential. However, if you are unfamiliar with the utility you may end up with real problems.
To dump your database, you can use the
mysqldump query command.
However, there are many more options available. Here we list out some common options:
-single-transaction: this option allows you to create a dump that reads the database in its current state
-compact: This option helps to produce a smaller, more compact output
-create-options: This option allows you to create a dump that omits the
AUTO_INCREMENT setting on primary keys.
--quick: If you are looking to perform a dump of only a few rows at a time, you should use the
-flush-logs: This option will flush the logs for each database dumped.
-ansi-mode: This option will allow you to quote the identifiers within the "characters" of the SQL statement.
For example, to dump a single table:
mysqldump db_name table_name > table_name.sql
To dump all the tables of a database:
mysqldump --host=localhost --user=root --port=3306 --p --all-databases > database.sql
Now we have backup files from
mysqldump, we can use
mysql to restore the backup:
mysql -u username -p db_name < /path/to/table_name.sql