It provides the best performance for InnoDB backups, and can also back up tables from MyISAM and other storage engines and it provides many convenient options to accommodate different backup scenarios. If your tables are primarily InnoDB tables, or if you have a mix of InnoDB and MyISAM tables, a better tool may be the mysqlbackup command of the MySQL Enterprise Backup product (which is not free).With large data sizes, even if the backup step takes a reasonable time, restoring the data can be very slow because replaying the SQL statements involves disk I/O for insertion, index creation, etc.įor large-scale backup and restore, a physical backup is more appropriate, to copy the data files in their original format that can be restored quickly: It is not intended as a fast or scalable solution for backing up substantial amounts of data. You can clone databases for development and DBA work, or produce slight variations of an existing database for testing. Mysqldump advantages include the convenience and flexibility of viewing or even editing the output before restoring. Performance and scalability considerations To reload a dump file containing such statements, the ALTER privilege for the affected database is required. These may be used when dumping stored programs to preserve their character encodings. Mysqldump output can include ALTER DATABASE statements that change the database collation. To reload a dump file, you must have the same privileges needed to create each of the dumped objects by issuing CREATE statements manually. Certain options might require other privileges as noted in the option descriptions. Mysqldump requires at least the SELECT privilege for dumped tables, SHOW VIEW for dumped views, TRIGGER for dumped triggers, and LOCK TABLES if the -single-transaction option is not used. The mysqldump command can also generate output in CSV, other delimited text, or XML format. It dumps one or more MySQL database for backup or transfer to another SQL server. The mysqldump client is a utility that performs logical backups, producing a set of SQL statements that can reproduce the original schema objects, table data, or both. Performance and scalability considerations.