MySQL Configuration File - Updating and changing the default location of my.cnf

MySQL Option file or Configuration file

Most MySQL programs can read startup options from option files (also called as configuration files).

Option files provide a convenient way to specify commonly used options so that they need not be entered on the command line each time you run a program.

File path: /etc/my.cnf

The  mysqld --verbose --help  is used to display detailed information about the mysql server's configurations, options, and supported features.

To check the contents of the my.cnf file :

    sudo nano /etc/my.cnf


Let's update our configuration file now.

We will remove all the comments and one option of 'server-id', for which we will assign the value as 1.

To check for the serer-id in MySQL you execute this command:

    show variables like '%server_id%';


As you can see the server_id is 1. Now let's update this in our option file.

In our configuration file we will add the following line:

    server-id=1

Notice, that our system variable uses underscores, but in configuration file we use '-' instead of '_'.

Your configuration should look something like this.



Now let's change the default file location of our configuration file (Optional)

In this example we will use another default location of our config file. i.e.  /etc/mysql/my.cnf
    
1. Create a new directory inside /etc/
 
    sudo mkdir /etc/mysql

2. Copy /etc/my.cnf to /etc/mysql/my.cnf

    sudo cp /etc/my.cnf /etc/mysql/my.cnf

3. Rename /etc/my.cnf to /etc/my.cnf.old

    sudo mv /etc/my.cnf /etc/my.cnf.old

4. Restart the mysql server

    sudo systemctl restart mysqld


Great! You have successfully changed the default location of the configuration file


Now let's say instead of /etc/mysql/my.cnf, I want my configuration file to be in the  /etc/percona directory. How can i achieve this ?


We can do this with the help of !include or !includedir

Here are the steps to execute this.

1. Create one directory lets say percona under /etc i.e. /etc/percona

     sudo mkdir /etc/percona

2. Now copy the content of /etc/mysql/my.cnf file to /etc/percona/my.cnf file.

    sudo cp /etc/mysql/my.cnf /etc/percona/my.cnf

3. Now delete everything from /etc/mysql/my.cnf file and add the following line.

    !includedir /etc/percona

4. Restart the mysqld server 

    sudo systemctl restart mysqld

Mysql will now load its configuration from the /etc/percona/my.cnf instead of /etc/mysql/my.cnf as specified by the !include directive. 


Great! Now we now know how configuration file 'my.cnf' defines server settings. We can update and change its default location to customise MySQL behaviour as needed ðŸŽ‰






Comments

Post a Comment

Popular posts from this blog

MySQL User Administration - Creating new users, logging in with them , and dropping users.

Image
Learn how to create a new user and explore multiple methods to connect to mysql with that user. Additionally, discover the steps to delete a user when no loner needed. Let's look at the steps to create a MySQL user: Step 1: First let's check what options are available when creating a user.     help create user; Step 2: To create user execute this command:     create user if not exists 'user_name' identified by 'password' password expire never account unlock; Step 3 : Now let's grant the permissions to the user. In this case we are granting all the privileges to the user.     grant all privileges on *.* to 'junior' with grant option; Step 4: Flush the privileges.     flush privileges;     (To reload the privileges, ensuring any changes made to the account take effect immediately) Step 5: To confirm whether all privileges are granted:     show grants for user_name; Now the question arises: how do we connect to mysql with the ...
Read more

MySQL config editor , Executing MySQL commands from the terminal, mysqlimport and mysqlcheck

Image
We will log in to MySQL without entering credentials by using MySQL config editor, then execute mysql commands from the terminal, import the data into a mysql table, and check data integrity using mysqlcheck. mysql_config_editor is a command line utility provided by MySQL to securely manage login credentials. It can store credentials like username, password, host and port in an encrypted login file path. To learn more about this command and how to use it you can execute this query:          mysql_config_editor --help Now we will set the credentials by using mysql_config_editor:     mysql_config_editor set --user=root --password -login-path=client To verify whether the credentials are stored and encrypted :     mysql_config_editor print Now let's check whether we can log in to MySQL without providing user and password credentials.     mysql Great! We have successfully stored our credentials with the help of mysql_config_editor and...
Read more