MySQL Server Forum - How To Dump The Database and Read It Back In?

Author

How To Dump The Database and Read It Back In?

Eric Robinson

2005-06-23, 8:23 pm

Our database just slowed down radically. Certain queries drive CPU
utilization through the roof. This happened once before, and the technican
simply dumped the data to disk and then played it back in. This seemed to
correct database corruption. After than, the database went back to normal
performance. Does anyone know how to do this?

--Eric

Chris Hope

2005-06-23, 8:23 pm

Eric Robinson wrote:

> Our database just slowed down radically. Certain queries drive CPU
> utilization through the roof. This happened once before, and the
> technican simply dumped the data to disk and then played it back in.
> This seemed to correct database corruption. After than, the database
> went back to normal performance. Does anyone know how to do this?

If you're having issues like this and dumping and re-reading the data is
fixing it, then there's bound to be issues which can be approached in
another way.

There are some table maintenance functions availble. The manuals page is
here: http://dev.mysql.com/doc/mysql/en/t...enance-sql.html

Of particular use are 'repair table', 'optimize table' and 'check
table'. From reading the manual page, the optimize table function
appears to fix indexes which may be what your problem relates to.

If you do decide to dump the database you want the mysqldump command.
The manual page is here:
http://dev.mysql.com/doc/mysql/en/mysqldump.html

You can then read it into the database on the command line like so
"mysql -u [username] -p [databasename] < dumpfile.txt"

However, you should really do this as a last resort. It would be better
to try to work out what the problem is and have a regular maintenance
program to prevent the slowdowns in the first place.

--
Chris Hope | www.electrictoolbox.com | www.linuxcdmall.com

Eric Robinson

2005-06-24, 3:23 am

Thanks for the tips. We have 9 MySQL servers, and this occurs on only 1 of
them. Both times, it was after a vendor-supplied software upgrade. My guess
is that their upgrade procedure is mucking up the tables or indexes.

"Chris Hope" < blackhole@electricto
olbox.com> wrote in message
news:d9fi7i$q94$1@lu
st.ihug.co.nz...
> Eric Robinson wrote:
>
>
> If you're having issues like this and dumping and re-reading the data is
> fixing it, then there's bound to be issues which can be approached in
> another way.
>
> There are some table maintenance functions availble. The manuals page is
> here: http://dev.mysql.com/doc/mysql/en/t...enance-sql.html
>
> Of particular use are 'repair table', 'optimize table' and 'check
> table'. From reading the manual page, the optimize table function
> appears to fix indexes which may be what your problem relates to.
>
> If you do decide to dump the database you want the mysqldump command.
> The manual page is here:
> http://dev.mysql.com/doc/mysql/en/mysqldump.html
>
> You can then read it into the database on the command line like so
> "mysql -u [username] -p [databasename] < dumpfile.txt"
>
> However, you should really do this as a last resort. It would be better
> to try to work out what the problem is and have a regular maintenance
> program to prevent the slowdowns in the first place.
>
> --
> Chris Hope | www.electrictoolbox.com | www.linuxcdmall.com

Eric Robinson

2005-06-24, 3:23 am

Also, we have some 100 tables. Is there an easy way to optimize all of them
squentially, or do I have to manually use the optimize command on each of
them individually?

"Chris Hope" < blackhole@electricto
olbox.com> wrote in message
news:d9fi7i$q94$1@lu
st.ihug.co.nz...
> Eric Robinson wrote:
>
>
> If you're having issues like this and dumping and re-reading the data is
> fixing it, then there's bound to be issues which can be approached in
> another way.
>
> There are some table maintenance functions availble. The manuals page is
> here: http://dev.mysql.com/doc/mysql/en/t...enance-sql.html
>
> Of particular use are 'repair table', 'optimize table' and 'check
> table'. From reading the manual page, the optimize table function
> appears to fix indexes which may be what your problem relates to.
>
> If you do decide to dump the database you want the mysqldump command.
> The manual page is here:
> http://dev.mysql.com/doc/mysql/en/mysqldump.html
>
> You can then read it into the database on the command line like so
> "mysql -u [username] -p [databasename] < dumpfile.txt"
>
> However, you should really do this as a last resort. It would be better
> to try to work out what the problem is and have a regular maintenance
> program to prevent the slowdowns in the first place.
>
> --
> Chris Hope | www.electrictoolbox.com | www.linuxcdmall.com

Gordon Burditt

2005-06-24, 3:23 am

>Also, we have some 100 tables. Is there an easy way to optimize all of them
>squentially, or do I have to manually use the optimize command on each of
>them individually?

I'm not sure when it was introduced, but consider:

mysqlcheck --repair --all-databases
or mysqlcheck --optimize --all-databases

Gordon L. Burditt

Chris Hope

2005-06-24, 3:23 am

Gordon Burditt wrote:

>
> I'm not sure when it was introduced, but consider:
>
> mysqlcheck --repair --all-databases
> or mysqlcheck --optimize --all-databases

Very useful. I always forget about those command line tools.

--
Chris Hope | www.electrictoolbox.com | www.linuxcdmall.com

Eric Robinson

2005-06-24, 11:23 am

You're my hero for today.

"Gordon Burditt" <gordonb.ro44x@burditt.org> wrote in message
news:11bn0he3s9o78dd
@corp.supernews.com...
>
> I'm not sure when it was introduced, but consider:
>
> mysqlcheck --repair --all-databases
> or mysqlcheck --optimize --all-databases
>
> Gordon L. Burditt

Sponsored Links