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
Filed under: EC2, Ruby on Rails, rails | 8 Comments
-
Recent Comments
Ezhil on Changing your mysql data … Larry Kubin on About Christian on About John Grimes on Capistrano & EC2 Sitting i… gaspard on Changing your mysql data … -
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?
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
Thanks Steve this worked for me. Be sure and set perms properly.
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.
it would be nice to store it under /home/mysql just like that http://www.freelancis.net/ressources/mysql/changing_directory
The above is not affective