MySQL Tutorials - Herong's Tutorial Examples

∟Introduction of MySQL Programs

∟--secure-file-priv="" - MySQL Server Option

This section provides a tutorial example on how to change the '--secure-file-priv' MySQL Server option, to control directory permissions for dumping all tables in a database as tab delimited files.

If you are getting the 1290 error as shown in the previous tutorial that prevents you dumping database or tables using the "mysqldump --tab" command, you need to reviewed and modify the "--secure-file-priv" option when starting the MySQL Server. 

mysqldump: Got error: 1290: The MySQL server is running with the
--secure-file-priv option so it cannot execute this statement when
executing 'SELECT INTO OUTFILE'

When running the "mysqldump --tab" command, it will run the "SELECT ... INTO OUTFILE..." SQL statement to dump all records of table into a text file. And the "OUTFULE ..." clause is limited by mysqld option "--secure-file-priv=name".

According to the "mysqld" program manual, the "--secure-file-priv=name" option limits LOAD DATA, SELECT ... OUTFILE, and LOAD_FILE() to files within specified directory.

With the default installation, MySQL Server 8.0 will start with '--secure-file-priv="C:/ProgramData/MySQL/MySQL Server 8.0/Uploads"'. In order to avoid the 1290 error, I can use the above directory with the "mysqldump --tab" command to store output files. 

herong> %mysql%\bin\mysqldump --user=root --password=TopSecret \
   --tab="C:\ProgramData\MySQL\MySQL Server 8.0\Uploads" Library

herong> dir "c:\ProgramData\MySQL\MySQL Server 8.0\Uploads"
             1,385 author.sql
                16 author.txt
             1,417 book.sql
                29 book.txt

If want to dump the database to a different directory, I turn off the "secure-file-priv" setting and restart the MySQL Server: I need to:

1. Modify default options for the MySQL Server in the start up file at C:\ProgramData\MySQL\MySQL Server 8.0\my.ini: 

# secure-file-priv="C:/ProgramData/MySQL/MySQL Server 8.0/Uploads"
secure-file-priv=""

2. Restart "MySQL8" Windows service.

3. Run the mysqldump command again 

herong> mkdir dump

herong> %mysql%\bin\mysqldump --user=root --password=TopSecret \
   --tab=dump Library

herong> dir dump
             1,385 author.sql
             1,417 book.sql

herong> dir "\ProgramData\MySQL\MySQL Server 8.0\Data\dump"
                16 author.txt
                29 book.txt

Note that the "mysqldump --tab" command actually sends and stores output files to 2 different directories:

Table of Contents

 About This Book

 Introduction of SQL

 MySQL Introduction and Installation

►Introduction of MySQL Programs

 List of MySQL Programs

 mysqld - The MySQL Server Program

 mysqladmin - The Client Tool for Administrators

 mysql - The Client Tool for End Users

 Using "mysql" Command to Run SQL Statements

 mysqldump - Dumping Data to Files

►--secure-file-priv="" - MySQL Server Option

 mysqlimport - Loading Data from Files

 PHP Programs and MySQL Server

 Perl Programs and MySQL Servers

 Java Programs and MySQL Servers

 Datatypes and Data Literals

 Operations and Expressions

 Character Strings and Bit Strings

 Commonly Used Functions

 Table Column Types for Different Types of Values

 Using DDL to Create Tables and Indexes

 Using DML to Insert, Update and Delete Records

 Using SELECT to Query Database

 Window Functions for Statistical Analysis

 Use Index for Better Performance

 Transaction Management and Isolation Levels

 Locks Used in MySQL

 Defining and Calling Stored Procedures

 Variables, Loops and Cursors Used in Stored Procedures

 System, User-Defined and Stored Procedure Variables

 MySQL Server Administration

 Storage Engines in MySQL Server

 InnoDB Storage Engine - Primary and Secondary Indexes

 Performance Tuning and Optimization

 Bulk Changes on Large Tables

 MySQL Server on macOS

 Installing MySQL Server on Linux

 Connection, Performance and Second Instance on Linux

 Archived Tutorials

 References

 Full Version in PDF/EPUB