mysql and mariadb database backup with mysqldump
To backup data in MYSQL or in MARIADB we use the mysqldump utility to do the work for us.
Backing up all databases
The simples way to make a backup is to dump all of the databases with all of the tables and their data.
The sql statement is like this: mysqldump --user admin --password -lock-all-tables -all-databases > /data/backups/all-db.sql
Mysqldump, this is the utility that used to backup data, --user this is the option for mysqldump that specifiers that the user who do backup, --password this is also an option for mysqldump that tells it the password for the user, --lock-all-tables this option is used to tell mysqldump to lock all the tables while backup is undergoing, --all-database this option tells mysqldump to backup all database in the server and > greater than symbol this redirect the output of the command into the specified location, which is found under data, backups-all, and then write data in all-db.sql.
We can generate two types of insert statement in our backup sql, the one with one insert statement which will run faster when restore or the one with multiple insert statements, to make backup with single insert statement we use this option of mysqldump --extend-insert or to turn it off we use this option --skip-extended-insert.
When we backup innoDB tables or other transactional tables with mysqldump its best to include the --single-transaction option, this will keep the data more consinstent.
When we backup the entire database and we don't want to back up a certain table we use the --ignore-table = the name of the table.
We can reduce the size of the backup file by using one of these options.
- --skip-add-drop-table, leave out drop table statements that clean up old tables.
- --skip-add-locks, dump without first locking each table.
- --skip-comments, suppress comments in the file.
- --skip-disable-keys, suppress commands that manipulate the indexes in the tables.
- --skip-set-charset, suppress set names statements that control character set in use.
- --compact, use all of the previous options in this list
Backing up specific databases.
We can back up specific database by specify, which database we can backup instead of using the generic --all-databases option.
We use the option --database followed by the name of the database we backing up.
The syntax to back up a single database will be like this one: mysqldump --user root --password --lock-all-tables --verbose --databases test_db > test_db_backup.sql
This command will back up only test_db.sql database and the option --verbose will tell mysqldump to show how the process is going.
we can also backup database schema without the data, by using the --no-data option this will backup only the structure of the database and its tables.
To export multiple databases, just we enter them after the --databases option separated by whitespace.
Backing up specific tables.
We can also back up a specific table from the database, and the syntax for backing this up its going to be like this: mysqldump --user root --password --verbose --lock-all-tables --database db_name --tables table_name[,table_name] > c:\backup.sql
How to restore backups into mysql or mariadb RDBMS.
We can restore the entire database or only a single table in the database. To restore data into the system we use the mysql utility to do this.
To restore the entire database, we use this command : mysql --user root --password < test_db.sql
The above command will restore data from the test_db.sql backup file into the system. This will work with both mysql and mariadb.
Restoring only a single table
We can restore the single table in the database backup file by editing the mysqldump file and leave only the information about the tables we want to restore and delete information about tables we don't want to restore.
Another way is by using temporary database by changing the name of the database from the dump file so when the restore is run its will create a new database and ehn we can copy the contents of the table found in the temporary database.
These method describe above will work with all mysql database and mariadb database, because both database system they use the same commands to create and manipulate data.