Asked By
Hamaky
130 points
N/A
Posted on - 05/06/2011
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?
Answered By
WizKid
0 points
N/A
#90227
Changing the default location of mySQL database files (Linux)
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>
Changing the default location of mySQL database files (Linux)
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!
Answered By
Hamaky
130 points
N/A
#90230
Changing the default location of mySQL database files (Linux)
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?
Changing the default location of mySQL database files (Linux)
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.
Answered By
WizKid
0 points
N/A
#90232
Changing the default location of mySQL database files (Linux)
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.
Answered By
Hamaky
130 points
N/A
#90233
Changing the default location of mySQL database files (Linux)
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?
Answered By
WizKid
0 points
N/A
#90234
Changing the default location of mySQL database files (Linux)
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.
Answered By
Hamaky
130 points
N/A
#90235
Changing the default location of mySQL database files (Linux)
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!
Answered By
WizKid
0 points
N/A
#90236
Changing the default location of mySQL database files (Linux)
Thank you for the compliment!
Â
