fguillen fguillen - 2 months ago 8x
MySQL Question

How to create temporary tables in a RDS replica instance

My developers are asking me to be able to create Temporary Tables.

But the connection they have configured is connected to a RDS Read Replica Instance.

How can I set up privileges so their connection can create Temporary Tables in that instance?


Open a writing privileges connection to your master RDS instance and create a tmp database as a store point for the Temporary Tables:

create database if not exists tmp;

Assign SELECT and CREATE TEMPORARY TABLES to your replica user's connection:

grant SELECT, CREATE TEMPORARY TABLES ON tmp.* TO youruser@'%';

Now though your replica connection you can manipulate Temporary Tables:

create temporary table tmp.my_temporary_table
  from my_databaes.my_table as mt
  limit 10;

select * from tmp.my_temporary_table;

drop temporary table tmp.my_temporary_table;