![]() This was tested using Windows 10 and Windows Server 2016, both running MySQL 8.0.x. These could be inserted into the variable if needed. Since a user with only DB access cannot have SUPER, the only thing one can do is change the DEFINER manually in the dump. I first attempted to change it to with set securefilepriv in which I get Error Code: 1238. I omitted field terminations, enclosures, and line terminations because they were the default values. The MySQL server is running with the -secure-file-priv option so it cannot execute this statement Currently, securefilepriv is set to NULL I checked using the show variables like statement. This produces a file: X:/mysql_secure/my_script_data/my_file_prefix_190827_suffix.csv. Data Directory Initialization Procedure Use -initialize for secure by default installation (that is, including generation of a random initial root password). ![]() SET := CONCAT("SELECT * FROM my_database.table WHERE conditions INTO OUTFILE "_suffix.csv'") SELECT 'my_script_data/') INTO in Windows returns a path with '\' To create a timestamped CSV file with your output use the following: - # VARIABLES ![]() That means the following path must already exist: X:/mysql_secure/my_script_data/ In your SQL script, store your output directory in a variable, using CONCAT() to append other file paths as needed (they will have to be created manually before executing, unfortunately). If you do not, the MySQLxx service will not start. So, reissue the LOAD DATA statement with this right path for fixing the error. NOTE: Make sure to save your file in the same encoding that it was originally written in (usually UTF-8). Open the file and under mysqld section we can see secure-file-priv along with value as shown: secure-file-privC:/ProgramData/MySQL/MySQL Server 5.7/Uploads This is the path we need to use. Do this by modifying the my.ini file ( create a backup first), searching for secure-file-priv and providing an absolute path: Let's assume you want to store the output in a subdirectory to make it easier to manage e.g./my_script_data/ inside the secure folder, but you might run this script on multiple servers, possibly with different secure directories.įirstly, for each server, set the secure_file_priv to the most appropriate directory, perhaps on another drive e.g. ![]()
0 Comments
Leave a Reply. |