Introduction:
In this article, we are here to know different ways to generate the backup in the MySQL database server. It is essential to make regular backups of all data in case of loss.
Why we need to?
As your data grows, it becomes increasingly important to safeguard it by implementing regular backups and having a reliable restoration process.
Steps for creating backups:
NOTE: In my case I am mentioning the database name as “TEST_DB”.
—>Single table from MySQL database
$mysql -u root -p(PASSWORD) TEST_DB TABLE1(TABLENAME) > TABLE1.sql
—>Database level
$mysql -u root -p(PASSWORD) TEST_DB | gzip > TEST_DB.sql.gz
—>All Databases
$mysql -u root -p(PASSWORD) --all-databases > all_databases.sql
—>Structure only (no data)
$mysql -u root -p(PASSWORD) --no-data TEST_DB > TEST_DB.sql
—>Backup of data (no table structure)
$mysql -u root -p(PASSWORD) --no-create-info TEST_DB > TEST_DB.sql.
Restore MySQL Database using following commands.
If you are moving the database from one server to another server or remotely then use below commands.
—>Restore a database on the same server
$mysql -u root TEST_DB –p < database.sql
Or
$ mysql -e “all_databases.sql”
—>Restore from remote server
$ mysql -u root-h (host_name) TEST_DB –p < database.sql
—>Restore a table from a database
$ mysql -u username TEST_DB < table.sql
—> Restore all databases from the dump as ROOT
$ mysql -u root < all_databases.sql
Conclusion:
In the article, I have provided step-by-step procedures about how to Backup & Restore databases and tables using the command line for MySQL.
