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.

13321 views
d

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:

sam

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.

  1. How to configure phpMyAdmin with automatic login by setting auth_type to config.
  2. How to create new users in MySQL and control their permissions for better security.
  3. How to generate sitemaps dynamically using PHP.
  4. How to perform simple SELECT statements in SQL to communicate with SQL databases.

More in: MySQL