MySQL restore command

Question

The standard MySQL restore command is - sudo mysql -u username -p password database_name < backup_file.sql

But I have to use below command to restore mysql backup file - Use Databasename source backup_file_path.sql

I don't understand why i have to use 2nd SQL Command and this is not the standard process.

Answer 1

Hi @Sarvesh Pandey , welcome to Microsoft Q&A forum.

The first command provided by you is to take the back up of database where in you are providing the name of database and path where backup file will be saved as dump file:

- sudo mysql -u username -p password database_name < backup_file.sql  

You can check if this dump file has the create database and use command or not.

Normally if you use mysqldump it automatically creates the create database and use commands by default.

For restoring, if you used mysqldump, you can directly use the source command and it will restore the database.

However, with mysql command you need to specify the create db(If db does not exist) and use statement followed by source command as below:

mysql> CREATE DATABASE IF NOT EXISTS db1;  
mysql> USE db1;  
mysql> source dump.sql  

Please refer to below articles for more details on the same:
https://dev.mysql.com/doc/mysql-backup-excerpt/5.7/en/reloading-sql-format-dumps.html
https://dev.mysql.com/doc/mysql-backup-excerpt/5.7/en/mysqldump-sql-format.html
https://stackoverflow.com/questions/5152921/import-sql-file-into-mysql
https://serverfault.com/questions/233535/difference-between-mysql-and-mysqldump

Please let me know if this helps or else we can discuss further.

----------

If answer helps, please select 'Accept Answer' as this could help other community members having similar queries.