Great Fairies are powerful entities who will grant you special powers like magic spells, enhanced skills and even halved damage received. Additionally, they'll fully heal you every time you visit them.
You can find a total of six Great Fairies throughout the game, but you'll have to obtain certain equipment to reach them, and pay an increasing amount of rupees for their services.
Great Fairy #1
The first Great Fairy Fountain can be found at the top of Death Mountain Trail. Before heading to her location, you'll need to unlock the Bomb Bag from Dondongo Cavern (part of the main story).
Once at the top, look for a wall on the left that can be broken with your bombs to reveal the entrance to the Great Fairy Fountain. Play Zelda's Lullaby in front of the fountain and pay 25 rupees to learn the Din's Fire magic power! This will also unlock the magic power bar, which is the resource you'll use to cast magic powers like Din's Fire and others.
Great Fairy #2
The second Great Fairy Fountain can be found at Hyrule Castle, and you can only reach it with child Link. Like the first one, you'll need to unlock the Bomb Bag to open up the entrance. Shortly after getting to the other side of the guarded gate, look for an opening to the right where you can find a breakable wall again. Use your bombs to break it and crawl through the small tunnel to reach the fountain.
Once again, play Zelda's Lullaby in front of the fountain, then pay 50 rupees to receive the Gift of Improved Spin, which will enhance your charged spin attacks.
Great Fairy #3
You can find the third Great Fairy Fountain in the Zora Fountain area. Once again, the Bomb Bag will be required to reach it. Look for a breakable wall on the small patch of ground to the southeast of the area and use a bomb to break it.
As always, play Zelda's Lullaby in front of the fountain, then pay 100 rupees to receive your new power. This time, you'll learn the Farore's Wind magic spell, which lets you set a warp point inside dungeons and warp to it whenever you cast it again.
Great Fairy #4
The fourth Great Fairy Fountain is located in Death Mountain Crater and requires the Megaton Hammer, unlocked during the Fire Temple. Make sure you go with adult Link and the Goron Tunic equipped to avoid taking damage from the extreme heat.
Once inside Death Mountain Crater, cross the bridge on the right side (the only bridge that isn't broken) and use the Megaton Hammer to break the rocks that can't be destroyed by bombs. Play Zelda's Lullaby and pay 200 rupees to receive the Gift of Doubled Magic, which basically doubles your maximum magic power bar.
Great Fairy #5
To find the fifth Great Fairy Fountain, you'll need to reach the Evil Desert Colossus area. Make sure you get at least the first Rupee Bag upgrade, as you'll have to pay 300 rupees for this one. You'll find a breakable wall close to the entrance of this area, on your right. It's very easy to spot.
Once again, play Zelda's Lullaby, then pay 300 rupees to learn the Nayru's Love magic spell, which will make you temporarily invulnerable.
Great Fairy #6
The last Great Fairy Fountain can be found outside the Ganondorf's Castle area (adult era), but you'll need to have the Golden Gauntlets first, which are obtained inside the Shadow Room of Ganondorf's Castle, the final dungeon of the game.
The entrance to the fountain can be found behind the huge black and golden rock on the right side, just before the bridge leading to the castle. As always, you'll need to play Zelda's Lullaby and pay a whopping 500 rupees. That said, the reward is completely worth it: the Gift of Halved Damage, which permanently halves damage taken.
