Solution to SOURCE Error 2 with MySQL
The error happens when importing database backups using the SOURCE command, either because you got the path wrong, or because you used the command incorrectly.
By. Jacob
Edited: 2021-01-26 15:34
This is just a quick reminder to those having problems backing up- and importing MySQL databases. Often when I try to import a database using the SOURCE command I will get a SOURCE error 2 message.
The error simply means that we have gotten the file path wrong. However, in my case, it was because I used a less than sign between the SOURCE and the file name.
The reason for my mistake is that I use a greater than sign when backing up a database using mysqldump, and sometimes I mess up because the same logic does not apply when importing. Hopefully I will remember it now.
mysql> SOURCE < /home/username/database_backup.sql
RROR:
Failed to open file '< /home/username/database_backup.sql', error: 2
If you have made the same mistake, simply remove the less than sign from your statement. The SQL statement should look like this:
SOURCE /home/UserName/some_sql_file.sql
The problem can also be that you miss typed the path for the .sql file, though I suppose this is unlikely if you know what you are doing, but be sure to double check this as well.
Alternatively, you can also import databases using mysql in a terminal, in which case you will be using the less than sign:
mysql database_name < database_name.sql
To create a .sql backup of a database, use mysqldump:
mysqldump database_name > database_name.sql
Writing file paths
To understand how to write the file system paths, read this article: Absolute and Relative Paths
Basically, when making backups, you can either write an absolute path for the destination file, beginning with a forward slash "/", or you can use a relative path. Simply writing the file name of the destination file will write the file in the current directory.
You can use the CD command to navigate to the desired location, if you feel more comfortable doing that.
You need to make sure the location is writable when creating a backup. Personally I prefer just outputting to /home/MyUser/databases/name-of-output-file.sql, since I know this will be writable.
Tell us what you think:
for those of us who don't know what we are doing, we don't even know how to get the proper directory pathway so please tell me how to do that.