Hi! Friends after a long time I’m writing this. Many of programmers need to store the records of tables from database for future use or migration from one place to another. In general term this process is called backup and use of that storage of records into another database called restore.
Generally backup is taken with .sql files, because restore from these files is faster and easier as compare to other type of files.
I’m using here MySql database for Backup and Restore commands.
MySql provides a utility file called as mysqldump.exe used for backup and mysql.exe used for restoring backup into MySql database.
MySql provides a utility file called as mysqldump.exe used for backup and mysql.exe used for restoring backup into MySql database.
I’m considering you have installed MySql in “Program Files” into C: drive.
BACKUP COMMAND FOR SPECIFIC DATABASE
Open command prompt (cmd) and change directory bin where mysql is installed and run command like written below.
“C:\Program Files\MySQL\MySQL Server 5.7\bin>mysqldump.exe
–u[username_mysql] –p[password] [database_name]
> [path_to_store_backup]\[filename.sql]”
mysqldump.exe – File used to run backup process in mysql (MySQL utility).
username_mysql – Username used for logging in mysql.
password – Password used to authenticate mysql.
database_name – Name of database you want to take backup of.
path_to_store_backup – Path where to store backup file.
filename.sql – Name of sql file to store backup.
EXAMPLE:
First enter the command in cmd after changing directory to bin of mysql where installed in C: drive.
After this Press enter and you can check your backup file at path you entered in command.
BACKUP OF SPECIFIC TABLE FROM DATABASE
Earlier I made a backup of a single database can have multiple tables. But we can take backup of specific table from database.
You have to insert table name in mysqldump command like this:-
“C:\Program Files\MySQL\MySQL Server 5.7\bin>mysqldump.exe
–u[username_mysql] –p[password] [database_name] [table_name]
> [path_to_store_backup]\[filename.sql]”
EXAMPLE:
“C:\Program Files\MySQL\MySQL Server 5.7\bin>mysqldump.exe
–uroot –p1234 finallygot employee
> F:\DOCUMENTS\FINALLYGOT.COM\emp.sql”BACKUP OF SPECIFIC RECORDS FROM TABLE
If you want to take backup of specific records from a table then, mysqldump is providing this feature also by inserting where clause in command.
“C:\Program Files\MySQL\MySQL Server 5.7\bin>mysqldump.exe
–u[username_mysql] –p[password] [database_name] [table_name]
–where=”[condition]”> [path_to_store_backup]\[filename.sql]”EXAMPLE:
“C:\Program Files\MySQL\MySQL Server 5.7\bin>mysqldump.exe
–uroot –p1234 finallygot employee
- -where=”EmpId<’3’ ” > F:\DOCUMENTS\FINALLYGOT.COM\emp.sql”By this command we will get a backup of table employee but having records having EmpId less than 3
RESTORE COMMAND OF BACKUP
For restoring backup we have a command, just run that command on cmd.
First, change the directory to bin folder of mysql installation directory.
Then right this command in cmd.
“C:\Program Files\MySQL\MySQL Server 5.7\bin>mysql.exe
–u[username_mysql] –p[password] [database_name]
< [path_to_store_backup]\[filename.sql]”Here in restoring backup we change mysqldump to mysql and,
Change sign ‘>’ in command to ‘<’
And run this command with values you will find backup in sql file will restore to written database.
EXAMPLE:
After writing this command with values, press enter to start restore process and after complete restore has been completed in your mentioned database.
Now you can go to database in mysql has the values from sql backup file.
At last I want to say there are lots of features and options provided by mysqldump and these features can help you a lot. You can get knowledge about these features and options from main website of mysql and directly from below link.
0 comments
If you have any questions or suggestions, you are free to ask, i will appreciate that and, i will try my best...