[MySQL practical experience] load data infile reports ERROR 1148 (42000) or ERROR 1045 (

[MySQL practice] when loading data infile reports ERROR 1148 (42000) or ERROR 1045 (28000, load data infile is a very efficient command, from db1.table1 of host1 Through select... into outfile: import data to a text file, and then import data to db2.table1 of host2. Some typical problems encountered during use and solutions are finally found. This is recorded as a note. 1. root User (here only the root of mysql, not the root of Linux System) when the mysql server deployment machine imports data through the load data infile command, as long as the file path is specified correctly, generally, no problem occurs. 2. when a non-root user imports data on the mysql server deployment machine by running the load data infile command, the following ERROR occurs: ERROR 1045 (28000): Access denied for user 'xxx' @ 'xxx' (using password: YES) Possible cause: This is generally because non-root users do not have FILE Privilege. You can view the permissions of the current login user through show grants, or you can select mysql. the user can view the permissions of a user. Generally, normal user has no FILE Permission. Solution: 1) Add the local parameter by using the command load data local infile 'f Ilename 'into table xxx. xxx to import data (recommended) 2) Activate FILE Privilege for the normal user. Note: The FILE Permission is different from SELECE/DELETE/UPDATE permission, the latter can specify a table in a database, while the FILE is global, that is, it can only use grant FILE on *. * to 'xxx' @ 'xxx', the FILE Permission takes effect for all tables of all databases. Grant all on db. * to 'xxx' @ 'xxx' the specified user cannot have the FILE permission on the specified database. According to the minimum permission principle (operating system security concept), this method is not safe. 3) modify. my. for details about the configuration in cnf, refer to the official website. http://www.markhneedham.com/blog/2011/01/18/mysql-the-used-command-is-not-allowed-with-this-mysql-version/ 3. when a non-root user loads data local infile from the client machine to the remote mysql server, the following ERROR occurs: ERROR 1148 (42000 ): the used command is not allowed with this MySQL version possible cause (from mysql reference manual): If load data LOCALis disabled, either in the server or the client, a client that attempts to issue such a statement es the fol-lowing error message: ERROR 1148: The used command is not allowed with this MySQL version visible for security reasons, by default, it is not allowed to export data remotely from the client host through the load data command. Solution: For the mysql command-line client, enable load data local by specifying the -- local-infile [= 1] option, or disable it with the -- local-infile = 0 option, that is, when you need to import data from the client host and log on to mysql, you must explicitly specify the parameters using -- local-infile [= 1]. The typical command format is: after mysql -- local-infile-u user-ppasswd is successfully logged on, run load data infile 'filename' into table xxx. xxx