What is the meaning of speed of light $c$ in $E=mc^2$?

The reason $c$ is important is not because it is the speed of light. It's important because it is a universal conversion factor between time and distance. If you have a certain amount of time $t$, you can calculate the corresponding amount of distance by multiplying it by $c$.

Note: I'm not talking about the distance any particular object travels in the time $t$. If you have a car traveling at speed $v$, you can find out how far the car moves in time $t$ by multiplying by $v$, but that's not converting time into distance. The conversion is something more fundamental.

The fact that time and distance can be converted into each other like this is one of the ways relativity changed our view of the world. One of its consequences is that speeds can now be measured as pure, unitless numbers. How so? Well, normally, we might measure distance in meters and time in seconds. So when you calculate a velocity as distance divided by time, you get an answer in meters per second. But because time can be converted into distance, now you can measure time in meters as well. So if you divide distance (in meters) by time (in meters), the meters cancel out and you get a pure number.

As a pure number, $c = 1$. There are a few things that travel at speed 1, including light and gravity. Light was simply the first one that we discovered, which is the only reason $c$ is called the "speed of light."

Once you see that $c$ is important for reasons having nothing to do with the fact that light travels at that speed, hopefully it seems less strange that it enters into the formula $E = mc^2$. Just as you can convert time into distance, you can also convert mass into energy. You just have to multiply by $c$ twice, not just once, to make the units work out.

c is a priori not the speed of light. It is the speed of massless particles. The way it comes about is as follows: You construct the Lorentz-transformations as the symmetry transformations of Minkowski space. The group has one parameter, that's c. You have to fix it by physical means. You can look at the dynamics of massive particles and massless particles and find that massive particles will approach c asymptotically only at infinite energy, and massless particles always move with c.

Since to our best knowledge photons are massless, c is also the speed of light. Also, that was historically Einstein's motivation, which is why it's usually motivated in textbooks this way. However, should it turn out one day that photons do have very tiny masses, then c will still be there, it will just no longer be called the speed of light.

The constant $c$ is the speed of light, namely the constant $$ c= 299,792,458\,\,{\rm m/s} $$ exactly (using the current definition of a meter and a second). As David mentions, it is called the speed of light only because light was the first entity that was known to move by that speed. But it is also the speed of gravitons or anything else that moves by the maximum speed. It is the most important value of a speed in the Universe.

It appears in the relationship between mass and energy – which is valid for nuclear bombs, thermonuclear bombs, particle accelerators, stars, as well as everything else in this Universe – because $c$ is a fundamental universal constant that is important everywhere in the basic laws of this Universe. That's a conclusion (and partly also assumption) of the special theory of relativity by Albert Einstein. For example, the speed of light in the vacuum is always measured to be the same, regardless of the speed of the source and the observer, and it is the maximum speed that can be achieved by information (or "almost" achieved by moving matter).

Mature physicists understand that the conserved mass and conserved energy are really the same thing and only one independent quantity of this kind is conserved; mass may be converted via $E=mc^2$ and vice versa. After all, adult physicists (in particle physics and other fields that depend on relativity) often use units in which $c=1$. One light second and one second is fundamentally the same thing. The fact that $c$ has an awkward numerical value in SI units is just because the SI units (and other units) carry an awkward cultural baggage. Fundamentally, $c$ is very clean and crisp and it should be $c=1$. In these units, $E=m$ simply holds. Mass and energy is the same thing, when converted by the natural conversion factor.

For beginners or historically, there are various ways to derive $E=mc^2$. Einstein was considering an accelerating mass object. He did work ${\rm d}E$ on this object of rest mass $M_0$ which increased its velocity $v$. One may prove that the inertial mass of the object $M$ also had to increase by ${\rm d}M={\rm d}E/c^2$ for certain things to work – i.e. to prevent the mass object from surpassing the speed of light by extra acceleration.

The keyword you should look for if you want to understand $E=mc^2$ is the "special theory of relativity", the broader theory implying $E=mc^2$ and other things and discovered by Einstein's 1905 paper.

Let me add a caricature of the explanation which contains all the relevant things. Special relativity shows that various things – time, distance – get inflated or contracted by the Lorentz factor of $$ \gamma = \frac{1}{\sqrt{1-v^2/c^2}} >1 $$ That's also true for the mass. If you accept that the total mass is $$ M = M_0\gamma $$ where $M_0$ is the rest mass, then you may Taylor expand the total $Mc^2$ for small $v$ $$ Mc^2 = M_0 c^2 + \frac{1}{2} M_0 v^2 + \dots $$ So the first subleading term is exactly the usual kinetic energy from Newton's world, $M_0v^2/2$, but there's also an even greater term, the latent energy stored in the rest mass, $M_0 c^2$. This latent energy is constant for "non-nuclear" processes that don't change the internal character of the matter. Because it's constant, this term in the energy is physically inconsequential. Then the most important term is the kinetic energy $mv^2/2$, and it has the right coefficient assuming we have $E=mc^2$ to start with. However, $M_0c^2$ is there and it may get changed to other forms of energy if we do change the fundamental character of matter, e.g. if we split uranium nuclei to other nuclei. Relativity guarantees that we release $E=\delta m\cdot c^2$ of energy where $\delta m$ is the change of the rest mass of the radioactive matter.