You can't just use the metric of uniform convergence because uniform convergence defines (unless $X$ is compact) a strictly finer topology than compact convergence (uniform convergence on every compact subset of $X$) - for an example, consider $f_n \colon x \mapsto \frac{x}{n}$ in $\mathbb{R}^\mathbb{R}$; $f_n \to 0$ compactly, but not uniformly.
Next, unless Munkres uses different terminology (and that's what I suspect), you won't be able to find a metric that induces the topology of compact convergence on $Y^X$ with just the premises that $X$ be $\sigma$-compact and $Y$ a (complete) metric space. In general, for a $\sigma$-compact but not hemicompact space $X$, the topology of compact convergence on $Y^X$ is not metrisable. (Proof sketch below.)
So let us assume that $X$ is hemicompact, that is, there exists a sequence
$$K_0 \subset K_1 \subset K_2 \subset \ldots,\quad K_n \text{ compact }, \bigcup_{n=0}^\infty K_n = X$$
such that $K \subset X \text{ compact} \Rightarrow \bigl(\exists n \in \mathbb{N}\bigr) (K \subset K_n)$.
(For $X$ locally compact and $\sigma$-compact, you can - and should - choose the exhaustion $(K_n)$ so that for all $n$ you have $K_n \subset \overset{\circ}{K}_{n+1}$, that is very convenient in a lot of proofs.)
Then define
$$\varrho_n(f,g) := \sup_{x\in K_n} d\bigl(f(x),\,g(x)\bigr).$$
It is easy to see that $\varrho_n$ is a pseudometric on $Y^X$, and it is finite if $d$ is bounded.
Cap the $\varrho_n$ to be uniformly bounded, the most common choices to do that are (in my experience)
$$ \delta_n(f,\,g) := \min \{ \varrho_n(f,\,g),\, 1\} \text{ or } \delta_n(f,\,g) = \frac{\varrho_n(f,\,g)}{1 + \varrho_n(f,\,g)}.$$
Verification that both of these constructs transform pseudometrics into (bounded) pseudometrics is left as an exercise. If the original pseudometric was separating ($\varrho(f,\,g) = 0 \Rightarrow f = g$), the result is a (bounded) metric.
Now choose a sequence $1 \geqslant \varepsilon_n > 0$ with $\lim\limits_{n\to\infty} \varepsilon_n = 0$ monotonically. For example $\varepsilon_n = 2^{-n}$.
Then define
$$\delta(f,\,g) = \sup_{n\in \mathbb{N}} \{\varepsilon_n\cdot \delta_n(f,\,g)\},$$
or, if $\sum \varepsilon_n < \infty$, you can also define
$$\delta(f,\,g) = \sum_{n = 0}^\infty \varepsilon_n\cdot \delta_n(f,\,g).$$
Either way, the resulting map $Y^X \times Y^X \to \mathbb{R}$ is a metric.
Non-negativity and symmetry are clear because all $\varrho_n$ have these properties. $\delta(f,\,g) = 0 \iff f = g$ follows because a) $\varrho_n(f,\,f) = 0$ for all $n$, and b) $f \neq g \Rightarrow \varrho_n(f,\,g) > 0$ for all large enough $n$. For the sum construction the triangle inequality follows immediately from the triangle inequality for each summand, for the supremum construction, the proof is slightly more involved, for any $\varepsilon > 0$, you find an $n(\varepsilon)$ such that $\varepsilon_{n(\varepsilon)}\cdot \delta_{n(\varepsilon)}(f,\,h) \geqslant \delta(f,\,h) - \varepsilon$, then
$$\begin{aligned}
\delta(f,\,h) &\leqslant \varepsilon_{n(\varepsilon)}\cdot\delta_{n(\varepsilon)}(f,\,h) + \varepsilon\\
&\leqslant \varepsilon_{n(\varepsilon)}\cdot\delta_{n(\varepsilon)}(f,\,g) + \varepsilon_{n(\varepsilon)}\cdot\delta_{n(\varepsilon)}(g,\,h) + \varepsilon\\
&\leqslant \delta(f,\,g) + \delta(g,\,h) + \varepsilon
\end{aligned}$$
for all $g$ follows from the triangle inequality for $\delta_{n(\varepsilon)}$. Taking the limit $\varepsilon \to 0$ yields the triangle inequality for $\delta$.
With both constructions, $\delta$ induces the topology of compact convergence on $Y^X$, I show it only for the supremum construction:
Let $f \in Y^X$, and $K \subset X$ compact. Choose an $n \in \mathbb{N}$ such that $K \subset K_n$. Let $1 > \varepsilon > 0$. Then
$$\begin{aligned}
\{g \in Y^X \colon \sup_{x \in K}d\bigl(f(x),\,g(x)\bigr) < \varepsilon \} &\supset \{g \in Y^X \colon \varrho_n(f,\,g) < \varepsilon \} \\
&\supset \{g \in Y^X \colon \delta_n(f,\,g) < \frac{\varepsilon}{1+\varepsilon}\}\\
&\supset \{g \in Y^X \colon \delta(f,\,g) < \varepsilon_n\cdot\frac{\varepsilon}{1+\varepsilon} \}.
\end{aligned}$$
Thus every neighbourhood of $f$ in the topology $\mathcal{T}_\kappa$ of compact convergence contains a neighbourhood of $f$ in the topology $\mathcal{T}_\delta$ induced by $\delta$, hence $\mathcal{T}_\kappa \subset \mathcal{T}_\delta$.
On the other hand, for any given $\varepsilon > 0$, choose $n$ so that $\varepsilon_n < \varepsilon$. Then for each $h \in \{ g \in Y^X \colon \sup\limits_{x \in K_n} d\bigl(f(x),\,g(x)\bigr) < \varepsilon_n \}$ we have $\delta(f,h) \leqslant \varepsilon_n < \varepsilon$ - for $m \geqslant n$, we have $\varepsilon_m \cdot \delta_m(f,\,h) \leqslant \varepsilon_m \leqslant \varepsilon_n$, and for $m < n$, we have $\delta_m(f,\,h) < \delta_n(f,\,h) < \varepsilon_n$ and hence $\varepsilon_m \cdot \delta_m(f,\,h) \leqslant \delta_m(f,\,h) \leqslant \delta_n(f,\,h) < \varepsilon_n$. Thus each $\mathcal{T}_\delta$-neighbourhood of $f$ contains a $\mathcal{T}_\kappa$-neighbourhood, and that means $\mathcal{T}_\delta \subset \mathcal{T}_\kappa$.
For completeness, note that for any $\delta$-Cauchy sequence $f_n$, all the $f_n(x)$ are Cauchy sequences in $Y$, and therefore the pointwise limit $f$ exists. It is an easy exercise that $f_n$ converges compactly to $f$ then.
Now, the promised sketch of a proof that in general, the topology of compact convergence on $Y^X$ is not metrisable when $X$ is $\sigma$-compact but not hemicompact.
The field $\mathbb{Q}$ of rational numbers is countable, hence $\sigma$-compact in the order topology (which is also the topology inherited from $\mathbb{R}$).
Let $c$ be a choice function for $\mathbb{Q}$, that is
$$c \colon \mathfrak{P}(\mathbb{Q}) \setminus \{\varnothing\} \to \mathbb{Q}; \quad c(S) \in S.$$
With an enumeration of $\mathbb{Q}$ (any surjection from $\mathbb{N}$ onto $\mathbb{Q}$), you can construct such a choice function explicitly.
Let $f \in \mathbb{R}^\mathbb{Q}$. Let $(U_n)$ be a countable system of neighbourhoods of $f$. Without loss of generality, $U_n \supset U_{n+1}$ and each $U_n$ has the form $\{g \colon sup_{K_n} \lvert f(x)-g(x)\rvert \leqslant \varepsilon_n$ where the $\varepsilon_n$ are strictly decreasing and $K_n \subset K_{n+1}$.
Let $K = \{0\} \cup \{ c\bigl((2^{-(n+1)},\,2^{-n})\setminus K_n\bigr) \colon n \in \mathbb{N}\}$.
$K$ is compact (a convergent sequence together with its limit), and not contained in any of the $K_n$. More, $V(K,\,1) = \{ g \colon \lvert f-g\rvert_K \leqslant 1\}$ does not contain any of the $U_n$. Let $x_n = c\bigl((2^{-(n+1)},\,2^{-n})\setminus K_n\bigr)$, and $h(x_n) = n$, $h(x) = 0$ if $x$ is none of the $x_n$. Each $K_n$ can only contain finitely many $x_n$, and thus $U_n$ contains $f + \tau\cdot h$ for all small enough $\tau > 0$. But $\tau\cdot h$ is unbounded on $K$, so $f + \tau\cdot h \notin V(K,\,1)$.
Hence the topology of compact convergence on $\mathbb{R}^\mathbb{Q}$ doesn't satisfy the first axiom of countability, and thus is not metrisable.
Best Answer
A few things that come to mind:
The topology of uniform convergence is too fine on e.g. $C(X)$ where $X$ is an open subset of some $\mathbb{R}^n$. In many situations, you don't have uniform convergence, but locally uniform convergence, and the locally uniform convergence is sufficient for many theorems (the limit of a locally uniformly convergent sequence of continuous functions is continuous; interchange of limit and integral for integrals over compact subsets [path or surface integrals]), and when it isn't, globally uniform convergence is often also not sufficient (integrals over sets of infinite measure).
With the topology of uniform convergence, $C(X)$ is not a topological vector space if there are unbounded continuous functions on $X$ (scalar multiplication is not continuous), nor is it a topological ring then.
The topology of uniform convergence is a natural topology for the space $C_b(X)$ of bounded continuous functions, however. Note that when $X$ is compact, we have $C(X) = C_b(X)$.
Still, for some purposes, the topology of uniform convergence can be an/the adequate topology on $C(X)$ to consider for non-compact $X$, but there are purposes where it is inadequate, and hence other topologies have to be considered.