Changing the default location of mySQL database files (Linux)

Asked By 130 points N/A Posted on -
qa-featured

I am loading up a Linux server from scratch. I have downloaded the latest stable release of MySQL.

When installing MySQL Server, by default, I noticed that the MySQL installation on Linux places the location at /var/lib/ folder.

I do not want to use the /var/lib folder. I have a separate partition that I wish to use for MySQL data files.

Is there a way to change the default data file location?

SHARE
Best Answer by Electrica
Go To Solution
Answered By 0 points N/A #90227

Changing the default location of mySQL database files (Linux)

qa-featured

By default the path where MySQL installs the database files is actually located at: /var/lib/mysql.

This folder contains the default MySQL database as well as another databases you create. Each database gets its own folder name within its main folder.

You can change the default base path by setting the "datadir" parameter.

datadir= <your required path>

About WizKid
Questions
0
Answers
25
Best Answers
4
Answered By 0 points N/A #90229

Changing the default location of mySQL database files (Linux)

qa-featured

If you are using the InnoDB engine, you will need to redirect the default innDB data file as well. This is done by setting the following parameters in the MySQL configuration file.

innodb_data_home_dir = /usr/local/mysql/data/
 
Please note that you only need to set this if you are using the InnoDB engine. This should be done in event you are changing the default data directory path.
 
WARNING: Please ensure that the path exists and that the "MySQL" user has full permissions to it. If not your MySQL service will not start!
About Electrica
Questions
0
Answers
23
Best Answers
6
Answered By 130 points N/A #90230

Changing the default location of mySQL database files (Linux)

qa-featured

You all talk about a MySQL configuration file. Can you tell me where can I find the file? Is it there by default or do i have to create one?

About Hamaky
Questions
1
Answers
3
Best Answers
0
Best Answer
Best Answer
Answered By 0 points N/A #90231

Changing the default location of mySQL database files (Linux)

qa-featured

The MySQL configuration file for Linux is usually named "my.cnf". This file resides in /var/etc/my.cnf.

It is not a requirement that the file should exist. If the file is there, the MySQL server instance will override its default values with the values specified inside the file.

As a security measure this file should only be accessible to the super user or the MySQL system group accounts.

If by any chance the file owner is not in any group, MySQL server instance will not start.

If you cannot find the file, you need to create it and put the entries.

About Electrica
Questions
0
Answers
23
Best Answers
6
Answered By 0 points N/A #90232

Changing the default location of mySQL database files (Linux)

qa-featured

In the event that you are creating a new configuration file for the MySQL Server installation, you will need to put the settings in a [mysqld] section inside the configuration file.

This is because this configuration file is a common one that is read by all the MySQL tools. Example: mysqladmin, mysqldump.

Therefore each tool will be looking for its own section within the configuration file.

I have posted the required lines below for the MySQL server instance.

[mysqld]

datadir = <your data directory>/
innodb_data_home_dir =<your data directory>/
 
Please note that the trailing forward slash after the path is mandatory! 
 
You will need to stop and start the MySQL service instance for the changes to take effect.
About WizKid
Questions
0
Answers
25
Best Answers
4
Answered By 130 points N/A #90233

Changing the default location of mySQL database files (Linux)

qa-featured

Thank you WizKid and Electrica! Your advise was spot on! I could not find a my.cnf file. Therefore I created a blank one and put the settings as specified. Just to check, I logged on to the MySQL instance and created a database. Then I checked if a database folder was created in the new path.

And yes, I saw the folder. Out of curiosity, the Innodb file does not get created inside the database folder, instead it stands alone as a separate file. Does this mean that all tables that are specified as InnoDB are stored in this single file?
About Hamaky
Questions
1
Answers
3
Best Answers
0
Answered By 0 points N/A #90234

Changing the default location of mySQL database files (Linux)

qa-featured

In databases where tables are MyISAM (the default engine), the data is stored in the same location as the table definition file. The data is stored inside the folder of the database.

Table that uses the InnoDB Engine supports transactions. This means that tables created with InnoDB respond to COMMIT and ROLLBACK commands. The data related to these tables are stored in the InnoDB data file. They are not stored in the same location as the table definition. Therefore you will not find the data file of the InnoDB tables inside database folder.
 
If you are thinking of backing up a database by a simple file copy of the database folder, you need to think again. The correct method of backing up a database with InnoDB tables is, to use the mysqldump command line utility.
About WizKid
Questions
0
Answers
25
Best Answers
4
Answered By 130 points N/A #90235

Changing the default location of mySQL database files (Linux)

qa-featured

Thank you WizKid for the in-depth insight on the InnoDB engine. I will keep your advice in mind when backing up the databases. Thank you again!

PS: WizKid, that is a cool signature!
About Hamaky
Questions
1
Answers
3
Best Answers
0
Answered By 0 points N/A #90236

Changing the default location of mySQL database files (Linux)

qa-featured

Thank you for the compliment!

 

About WizKid
Questions
0
Answers
25
Best Answers
4

I can't assign IP address in my CISCO switch?

Previous

I can't assign IP address in Cisco switch?

Next

Related Questions