Changing your mysql data directory

23Mar07

The default specs for an EC2 instance include 165GB of local storage. That storage is split between 2 partitions. The default partition (/) is 9.85GB, with the rest in /mnt, which Amazon calls their “ephemeral storage”.

One problem that I ran into is the default location for installing applications, including mysql, is the default partition. So my mysql database was initially stored in what was left of the 9.85GB on the root drive. As the database grew, it quickly started to consume all of the storage on this partition.

I recommend you change your mysql data directory to the /mnt drive.

Here is how I did it.

Step 1. Backup your database!

Step 2. Shutdown mysql

service mysqld stop

Step 3. Create a new data directory on /mnt and change

mkdir /mnt/mysql_db

Step 4. Change ownership of that directory to the same owner of your rails app

chown steveodom.www /mnt/mysql_db

Step 5. Modify the mysql config file

whereis my.cn

=> /etc/my.cn

vi /etc/my.cn

Which will look like this:

——————-

[mysqld]
datadir=/mnt/mysql_db/ #was /var/lib/mysql/
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Noticed I changed the very first line to point to /mnt/mysql_db.

Step 6. Move your databases to /mnt/mysql_db

mv mysql/your_database_name /mnt/mysql_db/

Step 7. Restart mysql

service mysqld start

About these ads


12 Responses to “Changing your mysql data directory”

  1. How do you ensure that your database stays around between reboots, or on new instances? Do you just store a database dump to S3 and use that when firing up new instances?

    What about ways to store the actual database on S3?

  2. That’s right, Andrew. I dump the data to S3 once an hour right now. Check out this package too:

    http://www.openfount.com/blog/s3infidisk-for-ec2

  3. 3 Troy

    Thanks Steve this worked for me. Be sure and set perms properly.

  4. 4 Doug

    In order to preserve the file permissions when copying the database over you’re probably better off using rsync.

    Assuming the permissions on your original directories and files are correct you could eliminate steps 3+4 and change step 6 to:
    rsync -av mysql/your_database_name /mnt/mysql_db

    The -a flag will preserve the file ownerships and permissions for you. Then you just have to delete the old files once you’re happy that everything went smoothly.

  5. it would be nice to store it under /home/mysql just like that http://www.freelancis.net/ressources/mysql/changing_directory

  6. 6 Ezhil

    The above is not affective

  7. very usefull
    thanks

  8. Could you address what to do when there is NO my.cnf file? I did a search using FIND and it didn’t come up at all. Do we make one?

  9. With the higher income from his salary as compared to a student
    who only received allowances, he began to increase his bets;
    sometimes going as high as a few hundred dollars per stake (his salary was only about 2.
    In the long run sports like soccer, cricket, volleyball and other
    exclusive sports events World Cup and Olympics are more enclosed because
    of their ideal viewership in the world which furthermore captured a large number of the dignified viewers.

    in China but there is an ongoing debate over whether the first casinos began in Ancient
    China or the Nile Delta.

  10. Agen Taruhan Judi Bola Casino Online – Lucky303.com adalah Agen Taruhan Judi Bola Casino Online serta Agen Casino Online Terbesar dan Situs Agen Judi Bola yang sangat Terpercaya dengan permainan Judi Online Terlengkap yang memberikan BONUS dan pelayanan 24 jam nonstop. Jasa pembuatan akun permainan Judi Casino Online dan Taruhan Bola serta Poker, Tangkas dan Togel Online. (Customer Service Lucky303 pelayanan cepat dan aman).


  1. 1 Some EC2, Fedora, Rails, Mongrel, Memcached Links - Laughing Meme
  2. 2 Border Crossing Stats » Changing your mysql data directory Niblets….Simple. Less.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Follow

Get every new post delivered to your Inbox.

%d bloggers like this: