Best practise Check ID/GID for MySQL
sh
# id mysql
uid=27(mysql) gid=27(mysql) groups=27(mysql)
Create and mount
sh
# mkdir -p /var/lib/mysqltempinram
# chown 27:27 /var/lib/mysqltempinram
# echo "tmpfs /var/lib/mysqltempinram tmpfs rw,gid=27,uid=27,size=512M,nr_inodes=10k,mode=0700 0 0" >> /etc/fstab
# mount /var/lib/mysqltempinram
# df -Th /var/lib/mysqltempinram
Add this line under [mysqld] into my.cnf configuration file:
sh
tmpdir = /var/lib/mysqltempinram
restart mysql Check if all is applied:
sh
# mysql
mysql> SHOW VARIABLES LIKE 'tmpdir';
Test
sh
mysql> CREATE TEMPORARY TABLE IF NOT EXISTS mysql.table2 AS (SELECT * FROM mysql.user);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
Quick method - careful! This might make the server run out of memory!!!
Check what's the RAM filesystem on the server
sh
# mount | grep tmpfs
tmpfs on /dev/shm type tmpfs (rw,noexec,nosuid,nodev)
=> /dev/shm
Add this to my.cnf file:
sh
[mysqld]
tmpdir = /dev/shm/
restart mysql Check if all is applied:
sh
# mysql
mysql> SHOW VARIABLES LIKE 'tmpdir';
+---------------+-------------+
| Variable_name | Value |
+---------------+-------------+
| tmpdir | /dev/shm/ |
+---------------+-------------+
Test:
sh
mysql> CREATE TEMPORARY TABLE IF NOT EXISTS mysql.table2 AS (SELECT * FROM mysql.user);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0