Como faço backup de um banco de dados para uma instrução .sql?

Question

Como faço backup de um banco de dados para um único arquivo SQL? Após o backup, como posso carregá-lo / restaurar o banco de dados com o InnoDB ? Deve ser InnoDB .

    

Answer 1

Backing up MySQL

MySQL backups are performed using the common mysqldump tool. This is a command line utility that ships with MySQL and you use at as follows:

% mysqldump --user=user --password=pass --opt DBNAME > dumpfile.sql

You may also need to specify the --host= parameter to force the hostname you are connecting to. This depends largely on how you've setup your user security. This will produce a text file with a series of INSERT/DROP/CREATE SQL statements that will recreate the database.

The --opt flag is very important. This is shorthand to pass in many flags at once; --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset. This ensures that your database is in a good state while the backup is performed, including restricting all write access while the backup is in operation. Any locks placed will be automatically removed when this utility finishes.

Fonte