MySQL tips: Back Up and Restore MySQL Database
Sometimes we have to take backup and restore our database. In this post I’m describing how to take backup from MySQL database and restore to Linux/Unix machine. If phpMyAdmin is installed to your system, you can take backup from web portal. If not installed you can take backup from command prompt.
Here is MySQL command to take MySQL database dump.
mysqldump --user=root —-password db_stag > new_db.sql
- root: is my database user.
- password: is password of my database user root.
- db_stag: is existing database name.
- new_db.sql: is new sql file which is database backup.
Also we can use following command:
mysqldump -u=root -p db_stag > new_db.sql
If you use this command it will ask password for your root database user. Once you type password for root user and press enter, it will take backup database ie. database dump to the current directory.
Now its time to restore database from the backup that we already taken. Before use this command login to MySQL and create new database new_db_stag from MySQL console.
To login MySQL use following command:
mysql -u root -p
where root is MySQL root user.
It will ask password for MySQL root user. Type password and press enter.
To create new database use following command:
create database new_db_stag;
If you want to check the database that you create, use following command.
Exit from the MySQL console and use following command.
mysql -u root -p new_db_stag < new_db.sql
This command will restore new_db.sql to new_db_stag in MySQL database which you can check with accessing MySQL database.