Backup and Restore operation in MySQL Server.
Know the version of my MySQL server ?
After you log in to mysql shell check out the first few lines, it will have the server version.
See all databases in mysql ?
From mysql shell, type the command 'show databases'.
Set a database as working or current database ?
From mysql shell, type the command use database_name
See all the tables in my current selected database ?
From mysql shell, type the command 'show tables'
To take back up of a single database
Give the below command from your terminal
If we use '--databases' then the dump.sql will have 'CREATE DATABASE db1' and 'USE db1' statements, so while restoring this script you need not specify the target database name for recreation of tables, MySQL will create the database for you.
To take back up of a multiple database
Use the below command
MySQL will create the database for you as your script already have the 'CREATE DATABASE' statement.
Otherwise specify an existing database as destination database
This post covered the most basic and steps for backup and restore, a shot for the jump in guys..
Happy Hacking !!
Some handy MySQL tips before we move on:
How to log into mysql shell ?
Type the below command in the terminal
mysql --user=username --password=password
Know the version of my MySQL server ?
After you log in to mysql shell check out the first few lines, it will have the server version.
See all databases in mysql ?
From mysql shell, type the command 'show databases'.
mysql show databases
Set a database as working or current database ?
From mysql shell, type the command use database_name
mysql --user=username --password=password
See all the tables in my current selected database ?
From mysql shell, type the command 'show tables'
mysql show tables
How to take BackUp
To take back up of all databases
Type the the below in your terminal
mysqldump --user=username --password=password --all-databases > alldbbck.sql
To take back up of a single database
Give the below command from your terminal
mysqldump --databases db1 > dump.sql mysqldump db1 > dump.sql
If we use '--databases' then the dump.sql will have 'CREATE DATABASE db1' and 'USE db1' statements, so while restoring this script you need not specify the target database name for recreation of tables, MySQL will create the database for you.
To take back up of a multiple database
Use the below command
mysqldump --databases db1 db2 db3 > dump.sql
How to Restore
If the dump script have create database and use statements (i.e created using --all-databases or --databases option) then it's not necessary to specify a database in which the backup is to be restored.
Fire the below command from your terminal:
mysql --user=username --password=password < dump.sql
MySQL will create the database for you as your script already have the 'CREATE DATABASE' statement.
Otherwise specify an existing database as destination database
mysql --user=username --password=password dump < dump.sql
This post covered the most basic and steps for backup and restore, a shot for the jump in guys..
Happy Hacking !!
0 comments:
Post a Comment