Loading Local Data in MySQL 8 in Windows

After creating your table in your Database, the next task is to populate it using either the LOAD DATA or the INSERT INTO command. If you have your data in a file, let's say 'pet.txt', you can load from the file to your table using the command below:

LOAD DATA LOCAL INFILE 'path/to/your/pet.txt' fields terminated by ',' lines terminated by '\n';

If the statement fails, it is likely that your MySQL installation does not have local file capability enabled by default. In order for the above command to work, both the MySQL Server and Client must have local file capability enabled. This tutorial walks you through enabling local file in both MySQL Server and MySQL client. We assume you have installed MYSQL 8.0 installed with at least these packages: MySQL Server, MySQL shell, and MySQL Workbench. MySQL Workbench is not necessary but it will allow you to see where the MySQL configuration is being read from, and start and stop the MySQL server. Below is a screenshot of MySQL workbench showing the configuration file the MySQL server is reading from.

To enable local file in MySQL server, we need to edit the configuration file shown in your MySQL Workbench and add a line that says "local-infile" under [mysqld] group. When you open the file to edit in your text editor such as Notepad, you'll notice that some lines start with brackets. That's called group. Any configuration parameters that come after the line that says [mysqld] apply to MySQL Server. In my case, [mysqld] was the last group in the file, so I simply added local-infile at the end of the file as shown in the screenshot below:

Now, for the MySQL server to take effect of the change, you need to restart the server which you can do using MySQL workbench. See the screenshot below. Simply stop the server and start again and click on "Server Status" to see if it started properly. If everything goes well, congratulations, you just enabled your MySQL Server to load local file data.

Now, the next step is to enable the same in the MySQL client. If you are new to computer science and not quite sure what a client and server is, here is a quick overview. A server is an application that is serving the client and a client is an application that is requesting service from a server. This can be confusing, but when you installed your MySQL, you decided to install both the server and the client. When you go to command line and type "mysql -u username -p" and a black screen appears with a prompt that says msqi>, you are using the client which is an interface that takes your commands and forwards that command to the server and, when the server responds, gives the response back to you. You can enable your MySQL client to enable local data load by using the option --local-infile=1 when you are starting your clientas shown below.

Now, you will be able to load local data to your table. See the screenshot below from my MySQL client.

Please note that there are several ways to do this, but the one I've described in this tutorial requires the least amount of work. Some mac users may have the same problem, and if you do make sure both your MySQL server and MySQL client has local file enabled. The process of enabling in the MySQl server is the same in principle - you just have to find out where the configuration file is located so you can edit the file and restart the server. To find out where your configuration file is located: type mysqld --verbose --help from your terminal. See the screenshot below. It will tell you where the configuration is being read from. You just have to edit one of these files as described above, restart the server and when you start you client make sure you use --local-infile=1 at the time of start.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.