You’ll eventually run into a client who has a crashed table in their database – this table will be pretty large – maybe even larger than the system’s default temporary directory (usually /tmp).
You could resize /tmp, but it is even easier to move the directory MySQL uses!
Here is a little script that will remove any tmpdir lines from your /etc/my.cnf file and will also add /home/mysql_tmp as its directory to use. You can name it whatever you want… but the reason I went with /home/something is because most of the hosting servers we set up will have large /home partitions. You don’t want to choose a directory that will end up filling up your / partition.
You could also increase the size of tmp, but that’s another tutorial.
|
1 2 3 4 5 6 7 8 |
#!/bin/bash sed -i '/tmpdir/d' /etc/my.cnf sed -i '/\[mysqld\]/a tmpdir=\/home\/mysql_tmp' /etc/my.cnf mkdir /home/mysql_tmp chmod 1777 /home/mysql_tmp chown mysql.mysql /home/mysql_tmp service mysql restart |
Let’s see if it worked:
|
1 |
mysqladmin var|grep tmpdir |
You should see something like:
|
1 2 3 |
root@linuxbrigade.com [~]# mysqladmin var|grep tmpdir | slave_load_tmpdir | /home/mysql_tmp | tmpdir | /home/mysql_tmp |
Related posts from Linux Brigade!
Import MySQL database from command line You’ll need to import a database via command line from time to time – I’ll normally opt for this anyway. After seeing how easy it is, you’ll probably end up using the command line instead […]- Warning: Skipping the data of table mysql.event. Your MySQL script was running great, but then you started getting errors – If you get this warning when dumping the mysql database, it’s not a bug – MySQL did this on purpose because they […]
- Check, repair, optimize all databases with one command My server had some tables marked as ‘crashed’ in one of the databases.. the server had recently gone down pretty harshly so I wanted to go through and check, repair and optimize all of the […]
- Back up all of your mysql databases nightly Have you ever wanted to make your own MySQL database backups? You can use this script I wrote to make a backup of all databases, then save each one in a dated backup directory. It will […]
Install nrpe on XenServer If you are using nagios in your hosting environment and need to install nrpe on your XenServer servers, here’s a good way to do it.
I’m using XenServer 6.1 (latest version).First, […]
