• Mysqldump - How to Create New MySQL Instances and Restore Specific Tables
  • Create a backup
  • Restore a backup

#Mysqldump - How to Create New MySQL Instances and Restore Specific Tables

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.

#Create a backup

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 --quick option.
• -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

#Restore a backup

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