- Instant help with your Sql coding problems

Backup MySQL database without locking tables

by Mike
Question:
How to backup MySQL database without locking tables?
Answer:
mysqldump -u testuser -p testdb --single-transaction > /tmp/backup_without_lock.sql
Description:

If you want to backup a MySQL database in a way that tables are not locked during the dump process - for example, if you want to backup a live database -  use the --single-transaction option of the mysqldump utility.

The --single-transaction option sets the transaction isolation mode to REPEATABLE READ and sends a START TRANSACTION SQL statement to the server before dumping data. It is useful only with transactional tables such as InnoDB, because then it dumps the consistent state of the database at the time when START TRANSACTION was issued without blocking any applications.

When using this option, you should keep in mind that only InnoDB tables are dumped in a consistent state. For example, any MyISAM or MEMORY tables dumped while using this option may still change state.

Reference:
The --single-transaction option reference
Share "How to backup MySQL database without locking tables?"
Related snippets:
Tags:
backup without lock, dump without lock, no lock during backup, mysql, backup, without locking
Technical term:
Backup MySQL database without locking tables
Trending snippets
Backup only certain rows from MySQL databaseGet last 30 days records in MySQLCreate INSERT statement for each record with mysqldumpBackup MySQL data onlyBackup MySQL database without locking tablesSkip tables in MySQL backupDelete all records from a table in MySQLCreate table in MySQLBackup MySQL schema onlyDelete records from MySQL tableBackup only selected tables in MySQLGet current year in MySQLBackup MySQL databaseSort MySQL resultsConvert INT to DATETIME in MySQLGroup by year of Unix timestamp in MySQLReturn 0 if field is NULL in MySQLImport an SQL file using the command line in MySQLList all tables in a MySQL databaseFind MySQL data directory location
Network articles
Create hover underline animation in TailwindCSSAppend an array to another in JavaScriptRemove whitespace from the beginning and end of a string in PHPUse Sass Nesting with multiple classesCreate horizontal rule with text using TailwindCSSRemove duplicate lines in Notepad++Export VS Code extensionsExtract data from JSON in PHPGet the last character of a string in JavaScriptConvert a string to sentence case in JavaScriptGet the maximum value in an array using JavaScriptGet the value of text input field in JavaScriptUse colors in JavaScript consoleAdd vertical rulers in VS CodeAdd cookie in ChromePHP string concatenationRedirect all requests to index.php using htaccessGet string length in JavaScriptHow to run useEffect only once in ReactHow to install PHP 8 on Windows 10