Reproducing subjects at 1:1 ratio or greater can reveal a world which is not normally visible for us to study closely without difficulty. Macro photography is commonly seen today with subjects such as insects and flowers, for example there is a large community on G+ tagging shots with #macromonday to share such photos. The Closer and Closer Macro group on Flickr has nearly a million photographs, demonstrating the popularity of macro photography.
A typical macro shot has razor sharp focus on the subject matter and a narrow depth of field (sometimes extended with focus stacking). The use of a ring flash or a flash diffuser is common in order to improve light conditions as the photographer needs to work very close to the subject. Tripods are not required but are very helpful in order to stabilise the camera when longer exposures are needed to compensate for poor light conditions and the subject can be relied on to not fly off while you are setting up the tripod.
Personally I use the Canon EF 100mm 2.8 USM lens on a 60D with a 430 EX II flash and a diffuser. Below are some sample photographs taken with the lens.
View on black here. This shot of a hornet has been cropped and rotated to improve the composition. Some photographers feel strongly that using crop to achieve a 'macro effect' is not appropriate. In this case the original shot was already a macro shot, as can be seen here. If you are shooting for your own enjoyment then there is no reason to worry about 'rules' but just be aware that some people may take a purist position.
This is a shot of an ice cube on a printed circuit board, with an air bubble (about 2 to 3 mm wide) trapped in the melting water. Again the shot has been cropped for composition. View on black here.
Euro coin, uncropped image shot af f/5.0, 1/400s with ISO 100 and no post processing (JPEG file straight from camera). View on black. Criticism of this picture would probably include that the coin is not fully in focus and that the subject does not fill the frame. A second light source would have been helpful as well.
In conclusion, macro photography can bring out previously unseen colours and details in the world around you. It can be great fun and range from very easy shots such as the coin to very difficult where the subject is prone to flee at the slightest provocation. There are some inexpensive ways, such as extension tubes, to get into macro photography or you can pick up some good glass - many macro lenses have excellent optical characteristics.