FastSysAdmin

Category: MySQL

  • Mount MySQL tmpdir as tmpfs for better performance

    1. Create a directory
      #mkdir -p /var/mysqltmp
    2. Change directory ownership to mysql user
      chown mysql:mysql /var/mysqltmp 
    3. Get uid and gid of mysql user
      #id mysql 
    4. Edit /etc/fstab to automatically mount tmpfs, add a new line. replace gid and uid
      tmpfs /var/mysqltmp tmpfs rw,gid=27,uid=27,size=2G,nr_inodes=50k,mode=0700 0 0 
    5. Mount the filesystems as defined in /etc/fstab
      mount -a 
    6. Edit /etc/my.cnf to change  tmpdir
      [mysqld]
      tmpdir = /var/mysqltmp 
    7. Restart mysqld service
      #service mysqld restart 
    8. Check if variable is updated in mysql console
      SHOW VARIABLES LIKE ‘tmpdir’;