To take a copy of a MariaDB 10.x database without locking it, you can use the “mysqldump” command with the “–single-transaction” option. This option tells mysqldump to start a transaction before dumping data and keep it open for the entire dump process, which allows other operations to continue on the database while the backup is being created.
Here is a step-by-step guide on how to take a copy of a MariaDB 10.x database without locking it:
Step 1: Use the “mysqldump” command with the “–single-transaction” option
mysqldump --single-transaction -u ecenica -p[password] [database_name] > [dump_file.sql]
This command creates a backup of the database in the form of a SQL file, which can then be imported into a new database. The “–single-transaction” option tells mysqldump to start a transaction before dumping data and keep it open for the entire dump process. This allows other operations to continue on the database while the backup is being created.
Step 2: Import the dump file into a new database
mysql -u ecenica -p[password] [new_database_name] < [dump_file.sql]
Step 3: Test the backup in a development environment
It’s important to test the backup in a development environment before using it in production to ensure that the backup was successful and the data is consistent.
Additionally, you can use the “xtrabackup” command to take a hot backup of InnoDB tables without locking the tables.
xtrabackup --user=ecenica --password=[password] --datadir=[path_to_datadir] --backup --target-dir=[backup_folder]
Please note that the dump file created by mysqldump may not be completely consistent if your tables are being written to while the backup is being made.
It’s recommended to schedule the backup during low traffic hours to minimize the chances of data inconsistency.