If you are a MySQL user, you know how important it is to have regular backups of your database. No matter how careful you are, accidents happen and data can be lost. That’s why it’s critical to have a backup and restore plan in place so that you can recover your data if something goes wrong.
In this blog post, we will show you how to create a backup and restore the MySQL database from the command line. We will also give you some tips on how to
Log in to the server SSH
In this quick tutorial, we’ll learn how to create a backup and restore a MySQL database from the command line. We’ll be using the MySQL binlog and MySQL dump utilities which are bundled with MySQL by default.
First, we need to log into the server via SSH. If you don’t have an SSH client, you can use Bitvise or Putty for the Windows OS.
Once you’re logged in, we can start by taking a backup of our database.
The interface of bitwise looks like the picture given below.
To access your server you need the following things.
- IP
- username (Mostly root)
- password
- port number
NOTE: If you are using the ley then you don’t need a password just generate your bitvise public key and add it to your server.
Once you logged in to your server ssh then you can do the export and database backup and all.
Export Database using MySQL dump
If you want to create a backup of your MySQL database, you can use the MySQL dump utility. This tool creates a SQL script that contains all the necessary SQL statements to recreate the database.
To export a database using MySQL dump, you need to specify the following parameters:
-The username and password for a MySQL account that has access to the database
-The name of the database to export
-The name of the file to which the exported database will be saved
For example, the following command exports the “mydb” database to a file called “mydb.sql”:
mysqldump -u username -p password mydb > mydb.sql
Once the export is complete, you can use this SQL script to recreate the database on another server. To do this, you can use the MySQL utility with the following syntax:
mysqldump -u username -p password mydb < mydb.sql
Export database using Mysql access
There are two ways you can export a MySQL database from the command line the first method that you can dump or the other method by accessing the Mysql through the command line.
So for this, you need MySQL root user access go to the terminal and run
$mysql -u root -p
it will ask for the password provide that, and then select the database using
mysqldump -u root -p tutorial >tutorial.sql
Import Database using the command line
There are two ways you can import your MySQL database using the command line. The first way is to use the MySQL dump command, and the second way is to use the MySQL command.
The MySQL dump command is used to create a backup of your database. To use this command, you will need to log into your server using SSH. Once you are logged in, you can run the following command:
mysqldump -u [username] -p [database_name] > [filename].sql
Replace [username] with your MySQL username, [database_name] with the name of the database you want to backup, and [filename] with the name of the file you want to create. After running this command, you will be prompted for your password. Enter your password and hit Enter. This will create a backup of your database in the SQL format.
The MySQL command is used to import your database. To use this command, you will need to log into your server using SSH. Once you are logged in, you can run the following command:
mysqldump -u [username] -p [database_name] < [filename].sql
Replace [username] with your MySQL username, [database_name] with the name of the database you want to backup, and [filename] with the name of the file you want to import. After running this command, you will be prompted for your password. Enter your password and hit Enter. This will import your database from the SQL file.
Import Database using PHPMyAdmin
Assuming that you have a database called “mydatabase” and a user called “myuser” with password “mypassword”, you can do the following:
- Log in to your CyberPanel
Click on the DataBase->PHPMyadmin in the left menu
- In the left pane, select the database you wish to export/import
- In the top menu, click on export/import
Under the export/import method, select Quick – default settings
Click Go
Your browser will download the SQL file or in case of import, the files will be uploaded.
