permissionsmysql-pythonmysql-connector

Permission danied when reading file from /var/lib/mysql-files

I am using cursor.execute to generate the file my_file.csv and save it in /var/lib/mysql-files. Here is my code:

import mysql.connector

connection = mysql.connector.connect(host='localhost', port='3306', database='my_db', user='my_user', password='my_pass')
cursor = connection.cursor()

cursor.execute('''select my_db.my_table.my_column '''
           '''from my_db.my_table '''
           '''into outfile '/var/lib/mysql-files/my_file.csv' '''
           '''fields optionally enclosed by '"' terminated by ',' LINES TERMINATED BY '\n' ''')

cursor.close()
connection.close()

Then I try to read this file with pandas directly from the /var/lib/mysql-files directory.

import pandas as pd

file = pd.read_csv("/var/lib/mysql-files/my_file.csv")

And I got this error:

PermissionError: [Errno 13] Permission denied: '/var/lib/mysql-files/my_file.csv'

My Question: how do I get permission to read the file my_file.csv from the /var/lib/mysql-files directory?

Note 1: I tried to change the permission on /var/lib/mysql-files directory with this command:

chown -R mysql:mysql /var/lib/mysql-files

but I still get the same permission error.

Note 2: I tried to include my user in the mysql group by running this command in the terminal on Ubuntu 24.04.1 LTS:

sudo usermod -a -G mysql $(whoami)

but I still get the same permission error.

Solution

  • A ls -l on the concerned directory shows that it is owned by mysql user and group.

    In order to get read access granted, you must be part of the mysql group (or use the mysql user).

    To add your current user to the mysql group you can execute: usermod -aG mysql $(whoami). Then logout and login.

    Or, as a quick test, run newgrp mysql and restart your script from the same console to confirm your issue is solved.

    EDIT:

    You are getting confused with the system shell and the mysql interactive shell. All of the above commands are to be executed from the system shell (sh, bash, zsh, ...):

    $ usermod -aG mysql $(whoami)
$ newgrp mysql
$ python3 ... # your python script