It can be found at the Lake of Rage at level 30 on the following versions:
Gen 2
Gen 4
Also, the Red Gyarados is just a normal Shiny Gyarados, just like on that you would see in the wild in you were lucky. It is just more hyped and seems a bit more special.
Of course, it is possible to find the Red Gyarados in Gen 4 and transfer it to a Gen 5 game. Similarly, you could also trade the Red Gyarados to Diamond/Pearl/Platinum from HeartGold/SoulSilver.