To simply import a sql file (and get any results printed to the prompt)
mysql -uUSERNAME -p DBNAME < FILENAME
The parameters are
-u username (note no space between the u and USERNAME)
-p password (can be given as the username (without a space between p and PASSWORD; or if omitted mysql will ask for it)
DBNAME is the name of the database to import to (if the sql file don’t create a database on it’s own)
FILENAME is the name of the file to import
Other parameters can be
-h server host (dns name or ip) if -h is omitted then localhost is assumed.
-P port number
-f force (will not break on errors)
To import a sql file and then have to output to a file (for instance when running from a crontab)
mysql -uUSERNAME -p DBNAME < FILENAME > OUTFILE
Works just as the previous import except that any output will be written to OUTFILE.
If you get an error messages like “mysqldump: Couldn’t execute ‘SHOW FIELDS FROM `store_information`’: View ‘database.viewname’ references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them (1356)” then a number of things might be wrong:
- The view is “corrupted” (this could happen if the table the view is of has been updated in a way so that the view is no longer correct – for instance if the table structure is updated)
- The user you are using are lacking permissions (to the view/table (that the view is referencing) )
What I usually do when this happens to solve it is to:
- Verify if the user running the mysqldump has the permissions (or test as root if you can – as root has more permissions).
If this is the case: update the permissions on the user running the dump – or change to another user
- Try to find out if the table the view is referencing has been updated (think back what have I done/check with the rest of the team)
Or if you do not wish to/can not do this, check a previous backup up the base table (from when mysqldump worked with the view) and compare the structure with the current structure in the database.
If this is the case: I just drop the view and recreate the view so that it gets corrected in the underlying db structure and re-run the dump.
More info for people using this notebook as a article; here is some manual pages that will help you along if you don’t know how to alter permissions/create views.
More information about user permissions
More information about views