How to Back Up and Restore MySQL Databases with Mysqldump
This instructional exercise discloses how to reinforcement and reestablish MySQL or MariaDB information bases from the order line utilizing the mysqldump utility.
The reinforcement documents made by the mysqldump utility are fundamentally a bunch of SQL articulations that can be utilized to reproduce the first information base. The mysqldump order can likewise create records in CSV and XML design.
You can likewise utilize the mysqldump utility to move your MySQL information base to another MySQL worker.
In the event that you don't reinforcement your information bases, a product bug or a hard-drive disappointment could be tragic. To help save you loads of time and dissatisfaction, it is unequivocally suggested that you avoid potential risk of consistently backing up your MySQL information bases.
Mysqldump Command Syntax
Prior to going into how to utilize the mysqldump order, how about we start by checking on the essential grammar.
The mysqldump utility articulations take the accompanying structure:
mysqldump [options] > file.sql
alternatives - The mysqldump choices
file.sql - The landfill (reinforcement) document
To utilize the mysqldump order the MySQL worker should be open and running.
Backup a Single MySQL Database
The most widely recognized use instance of the mysqldump device is to reinforcement a solitary information base.
For instance, to make a reinforcement of the information base named database_name utilizing the client root and save it to a record named database_name.sql you would run the accompanying order:
mysqldump -u root -p database_name > database_name.sql
You will be incited to enter the root secret key. After effective verification, the landfill cycle will begin. Contingent upon the information base size, the cycle can take some time.
On the off chance that you are signed in as the very client that you are utilizing to play out the fare and that the client doesn't need a secret phrase, you can preclude the - u and - p alternatives:
mysqldump database_name > database_name.sql
Backup Multiple MySQL Databases
To reinforcement various MySQL information bases with one order you need to utilize the - information base alternative followed by the rundown of data sets you need to reinforcement. Every information base name should be isolated by space.
mysqldump -u root -p --databases database_name_a database_name_b > databases_a_b.sql
The order above will make a dump document containing the two information bases.
Backup All MySQL Databases
Utilize the - all-information bases alternative to back up all the MySQL information bases:
mysqldump -u root -p --all-databases > all_databases.sql
Same similarly as with the past model the order above will make a solitary dump document containing all the information bases.
Backup all MySQL databases to separate files
The mysqldump utility doesn't give a choice to reinforcement all information bases to isolate records yet we effectively accomplish that with a basic slam FOR circle :
for DB in $(mysql -e 'show databases' -s --skip-column-names); do mysqldump $DB > "$DB.sql"; done
The order above will make a different dump record for every information base utilizing the data set name as the filename.
Create a Compressed MySQL Database Backup
On the off chance that the information base size is enormous it is a smart thought to pack the yield. To do that essentially pipe the yield to the gzip utility, and divert it to a record as demonstrated as follows:
mysqldump database_name | gzip > database_name.sql.gz
Create a Backup with Timestamp
In the event that you need to keep more than one reinforcement in a similar area, at that point you can add the current date to the reinforcement filename:
mysqldump database_name > database_name-$(date +%Y%m%d).sql
The order above will make a document with the accompanying organization database_name-20180617.sql
Restoring a MySQL dump
You can reestablish a MySQL dump utilizing the mysql apparatus. The order general grammar is as per the following:
mysql database_name < file.sql
Much of the time you'll have to make an information base to bring into. In the event that the information base as of now exists, first you need to erase it.
In the accompanying model the primary order will make an information base named database_name and afterward it will import the landfill database_name.sql into it:
mysql -u root -p -e "create database database_name"; mysql -u root -p database_name < database_name.sql
Restore a Single MySQL Database from a Full MySQL Dump
In the event that you upheld up the entirety of your information bases utilizing the - all-data sets choice and you need to reestablish a solitary data set from a reinforcement document which contains various data sets utilize the - one-information base choice as demonstrated as follows:
mysql --one-database database_name < all_databases.sql
Export and Import a MySQL Database in One Command
Rather than making a dump record from one information base and afterward import the reinforcement into another MySQL data set you can utilize the accompanying joke:
mysqldump -u root -p database_name | mysql -h remote_host -u root -p remote_database_name
The order above will pipe the yield to a mysql customer on the far off host and it will bring it into an information base named remote_database_name. Prior to running the order, ensure the information base as of now exists on the distant worker.
Automate Backups with Cron
Computerizing the way toward support up the information bases is as straightforward as making a cron work what will run the mysqldump order at indicated time.
To set up computerized reinforcements of a MySQL information base utilizing cronjob, follow the means underneath:
Make a document named .my.cnf in your client home index:
sudo nano ~/.my.cnf
Reorder the accompanying content into the .my.cnf document.
[client] user = dbuser password = dbpasswd
Remember to supplant dbuser and dbpasswdwith the information base client and client's secret word.
Confine consents of the accreditations record with the goal that solitary your client approaches it:
chmod 600 ~/.my.cnf
Make a registry to store the reinforcements:
Open your client crontab record:
Add the accompanying cron work that will make a reinforcement of an information base name mydb consistently at 3am:
0 3 * * * /usr/bin/mysqldump -u dbuser mydb > /home/username/db_backups/mydb-$(date +\%Y\%m\%d).sql
Remember to supplant username with your genuine client name. We're likewise getting away from the percent-signs (%), on the grounds that they have uncommon significance in crontab.
You can likewise make another cronjob to erase any reinforcements more seasoned than 30 days:
find /path/to/backups -type f -name "*.sql" -mtime +30 -delete
Obviously, you need to change the order as per your reinforcement area and document names. To study the discover order check our How to Find Files in Linux Using the Command Line manage.
This instructional exercise covers just the essentials, however it should be a decent beginning for any individual who needs to figure out how to make and reestablish MySQL information bases from the order line utilizing the mysqldump utility.
On the off chance that you need to study working with MySQL from the order line, investigate our How to oversee MySQL client records and information bases control.
You can likewise check the instructional exercise about how to reset a MySQL root secret key in the event that you have failed to remember it.
In the event that you have any inquiries or input, don't hesitate to leave a remark.
Particle is an open-source cross-stage code manager created by GitHub. It has an inherent bundle chief, inserted Git control, keen autocompletion, ...
An HTML editor is a software for enhancing HTML, the markup of an internet web page. It stands for Tiny Moxiecode Content Editor. TinyMCE is a effe...
Redis is an open-source in-memory key-esteem information store. It tends to be utilized as an information base, store and, message representative, ...
phpMyAdmin is an open-source PHP based instrument for overseeing MySQL and MariaDB workers over an online interface. phpMyAdmin permits you ...
While diverting the yield of an order to a document or funneling it to another order, you may see that the blunder messages are imprinted on the sc...
Deb is the establishment bundle design utilized by all Debian based dispersions. The Ubuntu vaults contain a huge number of deb bundles that...
CodeIgniter Subquery: This tutorial will explain you how to write subquery in codeigniter using active records. SubQuery is a query ...
Hi! In this tutorial we'll see how to find codeigniter version you are using. For pros it may sound basic but would be a challenge fo...
R is an open-source programming language and free climate that has practical experience in factual figuring and graphical portrayal. It is principa...
FTP (File Transfer Protocol) is a standard customer worker network convention that permits clients to move documents to and from a distant organiza...
Cup is a free and open-source miniature web system for Python intended to assist engineers with building secure, adaptable and viable web applicati...
Docker Compose is an instrument that permits you to characterize and arrange multi-holder Docker applications. It utilizes a YAML document to arran...
Spotify is a computerized music real time feature that gives you moment admittance to a great many melodies, from old works of art to the most rece...
sudo is an order line program that permits confided in clients to execute orders as root or another client. In this article we'll show b...
PHP is one of the most utilized worker side programming dialects. Numerous well known CMS and structures, for example, WordPress, Magento, and Lara...
Apache Maven is an open-source venture the executives and perception device utilized principally for Java ventures. Expert uses a Project Object Mo...
twist is an order line utility for moving information from or to a far off worker. With twist, you can download or transfer information utilizing o...
Vim/Vi is the word processor of decision for some product designers and Linux framework chairmen. As a matter of course, Vim doesn't sho...
Trade is a space on a plate that is utilized when the measure of actual RAM memory is full. At the point when a Linux framework runs out of RAM, in...