[Met_help] [rt.rap.ucar.edu #80473] History for removing part of a MySQL database
John Halley Gotway via RT
met_help at ucar.edu
Wed Jul 10 16:59:44 MDT 2019
----------------------------------------------------------------
Initial Request
----------------------------------------------------------------
Hi, there,
I would like to see if I can remove part of a MySQL database, in order to
free up some space, but not delete the database entirely so I can save that
stuff.
Is there a way to:
a) Save a database and store it offline
b) Delete a portion of the database (say 1 or 2 year's worth of data)
c) Restore the entire database later if need be
Thanks!
Perry
----------------------------------------------------------------
Complete Ticket History
----------------------------------------------------------------
Subject: Re: [rt.rap.ucar.edu #80473] removing part of a MySQL database
From: Howard Soh
Time: Tue May 16 13:44:57 2017
Yes, it can be done by using MySQL client applications.
a) Save a database and store it offline.
"mysqldump" does this. It saves 1) all tables, 2) some tables, or
some part of table.
b) Delete a portion of the database (say 1 or 2 year's worth of data)
Yes, it's possible: 1) deleting some tables or 2) deleting the
part of table. Dropping (deleting) some tables is better than deleting
the part of tables. Deleting part of tables does not save the disk
space. MySQL keeps the allocated disk space even though the part of
tables were deleted. And it does not have a tool to shrink the disk
space which was taken by the deleted records. But there is a work
around to release disk space.
c) Restore the entire database later if need be
Yes this can be done by using "mysql" command.
Cheers,
Howard
On 5/16/2017 11:14 AM, perry.shafran at noaa.gov via RT wrote:
> Tue May 16 11:14:40 2017: Request 80473 was acted upon.
> Transaction: Ticket created by perry.shafran at noaa.gov
> Queue: met_help
> Subject: removing part of a MySQL database
> Owner: Nobody
> Requestors: perry.shafran at noaa.gov
> Status: new
> Ticket <URL:
https://rt.rap.ucar.edu/rt/Ticket/Display.html?id=80473 >
>
>
> Hi, there,
>
> I would like to see if I can remove part of a MySQL database, in
order to
> free up some space, but not delete the database entirely so I can
save that
> stuff.
>
> Is there a way to:
>
> a) Save a database and store it offline
> b) Delete a portion of the database (say 1 or 2 year's worth of
data)
> c) Restore the entire database later if need be
>
> Thanks!
>
> Perry
>
------------------------------------------------
Subject: removing part of a MySQL database
From: perry.shafran at noaa.gov
Time: Tue May 16 13:49:04 2017
OK, let's say my database has data from 2015 thru 2017, and I wanted
to a)
save the database and then b) remove 2015 and Jan-June 2016 data from
the
database. How would I go about doing that? What are the commands?
Thanks!
Perry
On Tue, May 16, 2017 at 3:44 PM, Howard Soh via RT <met_help at ucar.edu>
wrote:
> Yes, it can be done by using MySQL client applications.
>
> a) Save a database and store it offline.
>
> "mysqldump" does this. It saves 1) all tables, 2) some tables,
or
> some part of table.
>
> b) Delete a portion of the database (say 1 or 2 year's worth of
data)
>
> Yes, it's possible: 1) deleting some tables or 2) deleting the
part
> of table. Dropping (deleting) some tables is better than deleting
the part
> of tables. Deleting part of tables does not save the disk space.
MySQL
> keeps the allocated disk space even though the part of tables were
deleted.
> And it does not have a tool to shrink the disk space which was taken
by the
> deleted records. But there is a work around to release disk space.
>
>
> c) Restore the entire database later if need be
>
> Yes this can be done by using "mysql" command.
>
> Cheers,
> Howard
>
>
> On 5/16/2017 11:14 AM, perry.shafran at noaa.gov via RT wrote:
> > Tue May 16 11:14:40 2017: Request 80473 was acted upon.
> > Transaction: Ticket created by perry.shafran at noaa.gov
> > Queue: met_help
> > Subject: removing part of a MySQL database
> > Owner: Nobody
> > Requestors: perry.shafran at noaa.gov
> > Status: new
> > Ticket <URL:
https://rt.rap.ucar.edu/rt/Ticket/Display.html?id=80473 >
> >
> >
> > Hi, there,
> >
> > I would like to see if I can remove part of a MySQL database, in
order to
> > free up some space, but not delete the database entirely so I can
save
> that
> > stuff.
> >
> > Is there a way to:
> >
> > a) Save a database and store it offline
> > b) Delete a portion of the database (say 1 or 2 year's worth of
data)
> > c) Restore the entire database later if need be
> >
> > Thanks!
> >
> > Perry
> >
>
>
>
------------------------------------------------
Subject: removing part of a MySQL database
From: Tatiana Burek
Time: Tue May 16 13:52:45 2017
Perry,
You can use METViewer scrubbing module to remove parts of the data.
Please look at this page:
http://www.dtcenter.org/met/metviewer/doc/database_scrubbing.html
It describes how to create a configuration XML file and run the
script.
Tatiana
On Tue May 16 13:49:04 2017, perry.shafran at noaa.gov wrote:
> OK, let's say my database has data from 2015 thru 2017, and I wanted
> to a)
> save the database and then b) remove 2015 and Jan-June 2016 data
from
> the
> database. How would I go about doing that? What are the commands?
>
> Thanks!
>
> Perry
>
> On Tue, May 16, 2017 at 3:44 PM, Howard Soh via RT
<met_help at ucar.edu>
> wrote:
>
> > Yes, it can be done by using MySQL client applications.
> >
> > a) Save a database and store it offline.
> >
> > "mysqldump" does this. It saves 1) all tables, 2) some tables, or
> > some part of table.
> >
> > b) Delete a portion of the database (say 1 or 2 year's worth of
data)
> >
> > Yes, it's possible: 1) deleting some tables or 2) deleting the
part
> > of table. Dropping (deleting) some tables is better than deleting
the
> > part
> > of tables. Deleting part of tables does not save the disk space.
> > MySQL
> > keeps the allocated disk space even though the part of tables were
> > deleted.
> > And it does not have a tool to shrink the disk space which was
taken
> > by the
> > deleted records. But there is a work around to release disk space.
> >
> >
> > c) Restore the entire database later if need be
> >
> > Yes this can be done by using "mysql" command.
> >
> > Cheers,
> > Howard
> >
> >
> > On 5/16/2017 11:14 AM, perry.shafran at noaa.gov via RT wrote:
> > > Tue May 16 11:14:40 2017: Request 80473 was acted upon.
> > > Transaction: Ticket created by perry.shafran at noaa.gov
> > > Queue: met_help
> > > Subject: removing part of a MySQL database
> > > Owner: Nobody
> > > Requestors: perry.shafran at noaa.gov
> > > Status: new
> > > Ticket <URL:
> > > https://rt.rap.ucar.edu/rt/Ticket/Display.html?id=80473 >
> > >
> > >
> > > Hi, there,
> > >
> > > I would like to see if I can remove part of a MySQL database, in
> > > order to
> > > free up some space, but not delete the database entirely so I
can
> > > save
> > that
> > > stuff.
> > >
> > > Is there a way to:
> > >
> > > a) Save a database and store it offline
> > > b) Delete a portion of the database (say 1 or 2 year's worth of
> > > data)
> > > c) Restore the entire database later if need be
> > >
> > > Thanks!
> > >
> > > Perry
> > >
> >
> >
> >
------------------------------------------------
Subject: Re: [rt.rap.ucar.edu #80473] removing part of a MySQL database
From: Howard Soh
Time: Tue May 16 14:32:38 2017
InnoDB engine is a different story. The disk space is not released
with
dropping (deleting) tables if the database engine is InnoDB.
Tatiana, were InnoDB tables created with extra option (for saving into
individual file per table)?
If not, one option is available for InnoDB engine tables which is
dropping (deleting) a whole database in order to save the disk space.
Cheers,
Howard
On 5/16/2017 1:52 PM, Tatiana Burek via RT wrote:
> Perry,
>
> You can use METViewer scrubbing module to remove parts of the data.
> Please look at this page:
> http://www.dtcenter.org/met/metviewer/doc/database_scrubbing.html
>
> It describes how to create a configuration XML file and run the
script.
> Tatiana
>
>
> On Tue May 16 13:49:04 2017, perry.shafran at noaa.gov wrote:
>> OK, let's say my database has data from 2015 thru 2017, and I
wanted
>> to a)
>> save the database and then b) remove 2015 and Jan-June 2016 data
from
>> the
>> database. How would I go about doing that? What are the commands?
>>
>> Thanks!
>>
>> Perry
>>
>> On Tue, May 16, 2017 at 3:44 PM, Howard Soh via RT
<met_help at ucar.edu>
>> wrote:
>>
>>> Yes, it can be done by using MySQL client applications.
>>>
>>> a) Save a database and store it offline.
>>>
>>> "mysqldump" does this. It saves 1) all tables, 2) some tables, or
>>> some part of table.
>>>
>>> b) Delete a portion of the database (say 1 or 2 year's worth of
data)
>>>
>>> Yes, it's possible: 1) deleting some tables or 2) deleting the
part
>>> of table. Dropping (deleting) some tables is better than deleting
the
>>> part
>>> of tables. Deleting part of tables does not save the disk space.
>>> MySQL
>>> keeps the allocated disk space even though the part of tables were
>>> deleted.
>>> And it does not have a tool to shrink the disk space which was
taken
>>> by the
>>> deleted records. But there is a work around to release disk space.
>>>
>>>
>>> c) Restore the entire database later if need be
>>>
>>> Yes this can be done by using "mysql" command.
>>>
>>> Cheers,
>>> Howard
>>>
>>>
>>> On 5/16/2017 11:14 AM, perry.shafran at noaa.gov via RT wrote:
>>>> Tue May 16 11:14:40 2017: Request 80473 was acted upon.
>>>> Transaction: Ticket created by perry.shafran at noaa.gov
>>>> Queue: met_help
>>>> Subject: removing part of a MySQL database
>>>> Owner: Nobody
>>>> Requestors: perry.shafran at noaa.gov
>>>> Status: new
>>>> Ticket <URL:
>>>> https://rt.rap.ucar.edu/rt/Ticket/Display.html?id=80473 >
>>>>
>>>>
>>>> Hi, there,
>>>>
>>>> I would like to see if I can remove part of a MySQL database, in
>>>> order to
>>>> free up some space, but not delete the database entirely so I can
>>>> save
>>> that
>>>> stuff.
>>>>
>>>> Is there a way to:
>>>>
>>>> a) Save a database and store it offline
>>>> b) Delete a portion of the database (say 1 or 2 year's worth of
>>>> data)
>>>> c) Restore the entire database later if need be
>>>>
>>>> Thanks!
>>>>
>>>> Perry
>>>>
>>>
>>>
>
>
------------------------------------------------
Subject: removing part of a MySQL database
From: Tatiana Burek
Time: Tue May 16 14:49:49 2017
Howard,
Thanks for sharing the info about InnoDB.
All tables in METViewer databases are created with ENGINE = MyISAM.
Would the space be released in this case?
Tatiana
On Tue May 16 14:32:38 2017, hsoh wrote:
> InnoDB engine is a different story. The disk space is not released
with
> dropping (deleting) tables if the database engine is InnoDB.
>
> Tatiana, were InnoDB tables created with extra option (for saving
into
> individual file per table)?
> If not, one option is available for InnoDB engine tables which is
> dropping (deleting) a whole database in order to save the disk
space.
>
> Cheers,
> Howard
>
> On 5/16/2017 1:52 PM, Tatiana Burek via RT wrote:
> > Perry,
> >
> > You can use METViewer scrubbing module to remove parts of the
data.
> > Please look at this page:
> > http://www.dtcenter.org/met/metviewer/doc/database_scrubbing.html
> >
> > It describes how to create a configuration XML file and run the
script.
> > Tatiana
> >
> >
> > On Tue May 16 13:49:04 2017, perry.shafran at noaa.gov wrote:
> >> OK, let's say my database has data from 2015 thru 2017, and I
wanted
> >> to a)
> >> save the database and then b) remove 2015 and Jan-June 2016 data
from
> >> the
> >> database. How would I go about doing that? What are the
commands?
> >>
> >> Thanks!
> >>
> >> Perry
> >>
> >> On Tue, May 16, 2017 at 3:44 PM, Howard Soh via RT
<met_help at ucar.edu>
> >> wrote:
> >>
> >>> Yes, it can be done by using MySQL client applications.
> >>>
> >>> a) Save a database and store it offline.
> >>>
> >>> "mysqldump" does this. It saves 1) all tables, 2) some tables,
or
> >>> some part of table.
> >>>
> >>> b) Delete a portion of the database (say 1 or 2 year's worth of
data)
> >>>
> >>> Yes, it's possible: 1) deleting some tables or 2) deleting the
part
> >>> of table. Dropping (deleting) some tables is better than
deleting the
> >>> part
> >>> of tables. Deleting part of tables does not save the disk space.
> >>> MySQL
> >>> keeps the allocated disk space even though the part of tables
were
> >>> deleted.
> >>> And it does not have a tool to shrink the disk space which was
taken
> >>> by the
> >>> deleted records. But there is a work around to release disk
space.
> >>>
> >>>
> >>> c) Restore the entire database later if need be
> >>>
> >>> Yes this can be done by using "mysql" command.
> >>>
> >>> Cheers,
> >>> Howard
> >>>
> >>>
> >>> On 5/16/2017 11:14 AM, perry.shafran at noaa.gov via RT wrote:
> >>>> Tue May 16 11:14:40 2017: Request 80473 was acted upon.
> >>>> Transaction: Ticket created by perry.shafran at noaa.gov
> >>>> Queue: met_help
> >>>> Subject: removing part of a MySQL database
> >>>> Owner: Nobody
> >>>> Requestors: perry.shafran at noaa.gov
> >>>> Status: new
> >>>> Ticket <URL:
> >>>> https://rt.rap.ucar.edu/rt/Ticket/Display.html?id=80473 >
> >>>>
> >>>>
> >>>> Hi, there,
> >>>>
> >>>> I would like to see if I can remove part of a MySQL database,
in
> >>>> order to
> >>>> free up some space, but not delete the database entirely so I
can
> >>>> save
> >>> that
> >>>> stuff.
> >>>>
> >>>> Is there a way to:
> >>>>
> >>>> a) Save a database and store it offline
> >>>> b) Delete a portion of the database (say 1 or 2 year's worth of
> >>>> data)
> >>>> c) Restore the entire database later if need be
> >>>>
> >>>> Thanks!
> >>>>
> >>>> Perry
> >>>>
> >>>
> >>>
> >
> >
>
------------------------------------------------
Subject: Re: [rt.rap.ucar.edu #80473] [Comment] removing part of a MySQL database
From: Howard Soh
Time: Tue May 16 14:56:54 2017
Yes, the disk space will be released with dropping (deleting) tables.
Deleting records requires extra manual steps to release disk space.
At dakota, there are about 30 databases with InnoDB engine and about
160
databases with MyISAM.
Cheers,
Howard
On 5/16/2017 2:49 PM, Tatiana Burek via RT wrote:
> https://rt.rap.ucar.edu/rt/Ticket/Display.html?id=80473
> This is a comment. It is not sent to the Requestor(s):
>
> Howard,
> Thanks for sharing the info about InnoDB.
>
> All tables in METViewer databases are created with ENGINE = MyISAM.
> Would the space be released in this case?
>
> Tatiana
>
> On Tue May 16 14:32:38 2017, hsoh wrote:
>> InnoDB engine is a different story. The disk space is not released
with
>> dropping (deleting) tables if the database engine is InnoDB.
>>
>> Tatiana, were InnoDB tables created with extra option (for saving
into
>> individual file per table)?
>> If not, one option is available for InnoDB engine tables which is
>> dropping (deleting) a whole database in order to save the disk
space.
>>
>> Cheers,
>> Howard
>>
>> On 5/16/2017 1:52 PM, Tatiana Burek via RT wrote:
>>> Perry,
>>>
>>> You can use METViewer scrubbing module to remove parts of the
data.
>>> Please look at this page:
>>> http://www.dtcenter.org/met/metviewer/doc/database_scrubbing.html
>>>
>>> It describes how to create a configuration XML file and run the
script.
>>> Tatiana
>>>
>>>
>>> On Tue May 16 13:49:04 2017, perry.shafran at noaa.gov wrote:
>>>> OK, let's say my database has data from 2015 thru 2017, and I
wanted
>>>> to a)
>>>> save the database and then b) remove 2015 and Jan-June 2016 data
from
>>>> the
>>>> database. How would I go about doing that? What are the
commands?
>>>>
>>>> Thanks!
>>>>
>>>> Perry
>>>>
>>>> On Tue, May 16, 2017 at 3:44 PM, Howard Soh via RT
<met_help at ucar.edu>
>>>> wrote:
>>>>
>>>>> Yes, it can be done by using MySQL client applications.
>>>>>
>>>>> a) Save a database and store it offline.
>>>>>
>>>>> "mysqldump" does this. It saves 1) all tables, 2) some tables,
or
>>>>> some part of table.
>>>>>
>>>>> b) Delete a portion of the database (say 1 or 2 year's worth of
data)
>>>>>
>>>>> Yes, it's possible: 1) deleting some tables or 2) deleting the
part
>>>>> of table. Dropping (deleting) some tables is better than
deleting the
>>>>> part
>>>>> of tables. Deleting part of tables does not save the disk space.
>>>>> MySQL
>>>>> keeps the allocated disk space even though the part of tables
were
>>>>> deleted.
>>>>> And it does not have a tool to shrink the disk space which was
taken
>>>>> by the
>>>>> deleted records. But there is a work around to release disk
space.
>>>>>
>>>>>
>>>>> c) Restore the entire database later if need be
>>>>>
>>>>> Yes this can be done by using "mysql" command.
>>>>>
>>>>> Cheers,
>>>>> Howard
>>>>>
>>>>>
>>>>> On 5/16/2017 11:14 AM, perry.shafran at noaa.gov via RT wrote:
>>>>>> Tue May 16 11:14:40 2017: Request 80473 was acted upon.
>>>>>> Transaction: Ticket created by perry.shafran at noaa.gov
>>>>>> Queue: met_help
>>>>>> Subject: removing part of a MySQL database
>>>>>> Owner: Nobody
>>>>>> Requestors: perry.shafran at noaa.gov
>>>>>> Status: new
>>>>>> Ticket <URL:
>>>>>> https://rt.rap.ucar.edu/rt/Ticket/Display.html?id=80473 >
>>>>>>
>>>>>>
>>>>>> Hi, there,
>>>>>>
>>>>>> I would like to see if I can remove part of a MySQL database,
in
>>>>>> order to
>>>>>> free up some space, but not delete the database entirely so I
can
>>>>>> save
>>>>> that
>>>>>> stuff.
>>>>>>
>>>>>> Is there a way to:
>>>>>>
>>>>>> a) Save a database and store it offline
>>>>>> b) Delete a portion of the database (say 1 or 2 year's worth of
>>>>>> data)
>>>>>> c) Restore the entire database later if need be
>>>>>>
>>>>>> Thanks!
>>>>>>
>>>>>> Perry
>>>>>>
>>>>>
>>>
>
>
------------------------------------------------
Subject: removing part of a MySQL database
From: Tatiana Burek
Time: Tue May 16 15:04:46 2017
All databases that were created after July 2015 are mandatory assigned
to be MyISAM. I added it to mv_mysql.sql script that creates tables.
Could you provide the steps needed to release the disk space?
Tatiana
On Tue May 16 14:56:54 2017, hsoh wrote:
> Yes, the disk space will be released with dropping (deleting)
tables.
> Deleting records requires extra manual steps to release disk space.
>
> At dakota, there are about 30 databases with InnoDB engine and about
160
> databases with MyISAM.
>
> Cheers,
> Howard
>
>
> On 5/16/2017 2:49 PM, Tatiana Burek via RT wrote:
> > https://rt.rap.ucar.edu/rt/Ticket/Display.html?id=80473
> > This is a comment. It is not sent to the Requestor(s):
> >
> > Howard,
> > Thanks for sharing the info about InnoDB.
> >
> > All tables in METViewer databases are created with ENGINE =
MyISAM.
> > Would the space be released in this case?
> >
> > Tatiana
> >
> > On Tue May 16 14:32:38 2017, hsoh wrote:
> >> InnoDB engine is a different story. The disk space is not
released with
> >> dropping (deleting) tables if the database engine is InnoDB.
> >>
> >> Tatiana, were InnoDB tables created with extra option (for saving
into
> >> individual file per table)?
> >> If not, one option is available for InnoDB engine tables which is
> >> dropping (deleting) a whole database in order to save the disk
space.
> >>
> >> Cheers,
> >> Howard
> >>
> >> On 5/16/2017 1:52 PM, Tatiana Burek via RT wrote:
> >>> Perry,
> >>>
> >>> You can use METViewer scrubbing module to remove parts of the
data.
> >>> Please look at this page:
> >>>
http://www.dtcenter.org/met/metviewer/doc/database_scrubbing.html
> >>>
> >>> It describes how to create a configuration XML file and run the
script.
> >>> Tatiana
> >>>
> >>>
> >>> On Tue May 16 13:49:04 2017, perry.shafran at noaa.gov wrote:
> >>>> OK, let's say my database has data from 2015 thru 2017, and I
wanted
> >>>> to a)
> >>>> save the database and then b) remove 2015 and Jan-June 2016
data from
> >>>> the
> >>>> database. How would I go about doing that? What are the
commands?
> >>>>
> >>>> Thanks!
> >>>>
> >>>> Perry
> >>>>
> >>>> On Tue, May 16, 2017 at 3:44 PM, Howard Soh via RT
<met_help at ucar.edu>
> >>>> wrote:
> >>>>
> >>>>> Yes, it can be done by using MySQL client applications.
> >>>>>
> >>>>> a) Save a database and store it offline.
> >>>>>
> >>>>> "mysqldump" does this. It saves 1) all tables, 2) some tables,
or
> >>>>> some part of table.
> >>>>>
> >>>>> b) Delete a portion of the database (say 1 or 2 year's worth
of data)
> >>>>>
> >>>>> Yes, it's possible: 1) deleting some tables or 2) deleting the
part
> >>>>> of table. Dropping (deleting) some tables is better than
deleting the
> >>>>> part
> >>>>> of tables. Deleting part of tables does not save the disk
space.
> >>>>> MySQL
> >>>>> keeps the allocated disk space even though the part of tables
were
> >>>>> deleted.
> >>>>> And it does not have a tool to shrink the disk space which was
taken
> >>>>> by the
> >>>>> deleted records. But there is a work around to release disk
space.
> >>>>>
> >>>>>
> >>>>> c) Restore the entire database later if need be
> >>>>>
> >>>>> Yes this can be done by using "mysql" command.
> >>>>>
> >>>>> Cheers,
> >>>>> Howard
> >>>>>
> >>>>>
> >>>>> On 5/16/2017 11:14 AM, perry.shafran at noaa.gov via RT wrote:
> >>>>>> Tue May 16 11:14:40 2017: Request 80473 was acted upon.
> >>>>>> Transaction: Ticket created by perry.shafran at noaa.gov
> >>>>>> Queue: met_help
> >>>>>> Subject: removing part of a MySQL database
> >>>>>> Owner: Nobody
> >>>>>> Requestors: perry.shafran at noaa.gov
> >>>>>> Status: new
> >>>>>> Ticket <URL:
> >>>>>> https://rt.rap.ucar.edu/rt/Ticket/Display.html?id=80473 >
> >>>>>>
> >>>>>>
> >>>>>> Hi, there,
> >>>>>>
> >>>>>> I would like to see if I can remove part of a MySQL database,
in
> >>>>>> order to
> >>>>>> free up some space, but not delete the database entirely so I
can
> >>>>>> save
> >>>>> that
> >>>>>> stuff.
> >>>>>>
> >>>>>> Is there a way to:
> >>>>>>
> >>>>>> a) Save a database and store it offline
> >>>>>> b) Delete a portion of the database (say 1 or 2 year's worth
of
> >>>>>> data)
> >>>>>> c) Restore the entire database later if need be
> >>>>>>
> >>>>>> Thanks!
> >>>>>>
> >>>>>> Perry
> >>>>>>
> >>>>>
> >>>
> >
> >
>
------------------------------------------------
Subject: Re: [rt.rap.ucar.edu #80473] removing part of a MySQL database
From: Howard Soh
Time: Tue May 16 15:45:57 2017
The commands are after "mysql -u <db_user> -p -A <database_name> "
To delete a table:
drop table <table_name>;
To delete records:
delete from <table_name> where <where_condition>;
rename table <table_name> to <table_name>_tmp;
create table <table_name> like <table_name>_tmp;
insert into <table_name> select * from <table_name>_tmp;
drop table <table_name>_tmp;
Cheers,
Howard
On 5/16/2017 3:04 PM, Tatiana Burek via RT wrote:
> All databases that were created after July 2015 are mandatory
assigned to be MyISAM. I added it to mv_mysql.sql script that creates
tables.
> Could you provide the steps needed to release the disk space?
>
> Tatiana
>
> On Tue May 16 14:56:54 2017, hsoh wrote:
>> Yes, the disk space will be released with dropping (deleting)
tables.
>> Deleting records requires extra manual steps to release disk space.
>>
>> At dakota, there are about 30 databases with InnoDB engine and
about 160
>> databases with MyISAM.
>>
>> Cheers,
>> Howard
>>
>>
>> On 5/16/2017 2:49 PM, Tatiana Burek via RT wrote:
>>> https://rt.rap.ucar.edu/rt/Ticket/Display.html?id=80473
>>> This is a comment. It is not sent to the Requestor(s):
>>>
>>> Howard,
>>> Thanks for sharing the info about InnoDB.
>>>
>>> All tables in METViewer databases are created with ENGINE =
MyISAM.
>>> Would the space be released in this case?
>>>
>>> Tatiana
>>>
>>> On Tue May 16 14:32:38 2017, hsoh wrote:
>>>> InnoDB engine is a different story. The disk space is not
released with
>>>> dropping (deleting) tables if the database engine is InnoDB.
>>>>
>>>> Tatiana, were InnoDB tables created with extra option (for saving
into
>>>> individual file per table)?
>>>> If not, one option is available for InnoDB engine tables which is
>>>> dropping (deleting) a whole database in order to save the disk
space.
>>>>
>>>> Cheers,
>>>> Howard
>>>>
>>>> On 5/16/2017 1:52 PM, Tatiana Burek via RT wrote:
>>>>> Perry,
>>>>>
>>>>> You can use METViewer scrubbing module to remove parts of the
data.
>>>>> Please look at this page:
>>>>>
http://www.dtcenter.org/met/metviewer/doc/database_scrubbing.html
>>>>>
>>>>> It describes how to create a configuration XML file and run the
script.
>>>>> Tatiana
>>>>>
>>>>>
>>>>> On Tue May 16 13:49:04 2017, perry.shafran at noaa.gov wrote:
>>>>>> OK, let's say my database has data from 2015 thru 2017, and I
wanted
>>>>>> to a)
>>>>>> save the database and then b) remove 2015 and Jan-June 2016
data from
>>>>>> the
>>>>>> database. How would I go about doing that? What are the
commands?
>>>>>>
>>>>>> Thanks!
>>>>>>
>>>>>> Perry
>>>>>>
>>>>>> On Tue, May 16, 2017 at 3:44 PM, Howard Soh via RT
<met_help at ucar.edu>
>>>>>> wrote:
>>>>>>
>>>>>>> Yes, it can be done by using MySQL client applications.
>>>>>>>
>>>>>>> a) Save a database and store it offline.
>>>>>>>
>>>>>>> "mysqldump" does this. It saves 1) all tables, 2) some tables,
or
>>>>>>> some part of table.
>>>>>>>
>>>>>>> b) Delete a portion of the database (say 1 or 2 year's worth
of data)
>>>>>>>
>>>>>>> Yes, it's possible: 1) deleting some tables or 2) deleting the
part
>>>>>>> of table. Dropping (deleting) some tables is better than
deleting the
>>>>>>> part
>>>>>>> of tables. Deleting part of tables does not save the disk
space.
>>>>>>> MySQL
>>>>>>> keeps the allocated disk space even though the part of tables
were
>>>>>>> deleted.
>>>>>>> And it does not have a tool to shrink the disk space which was
taken
>>>>>>> by the
>>>>>>> deleted records. But there is a work around to release disk
space.
>>>>>>>
>>>>>>>
>>>>>>> c) Restore the entire database later if need be
>>>>>>>
>>>>>>> Yes this can be done by using "mysql" command.
>>>>>>>
>>>>>>> Cheers,
>>>>>>> Howard
>>>>>>>
>>>>>>>
>>>>>>> On 5/16/2017 11:14 AM, perry.shafran at noaa.gov via RT wrote:
>>>>>>>> Tue May 16 11:14:40 2017: Request 80473 was acted upon.
>>>>>>>> Transaction: Ticket created by perry.shafran at noaa.gov
>>>>>>>> Queue: met_help
>>>>>>>> Subject: removing part of a MySQL database
>>>>>>>> Owner: Nobody
>>>>>>>> Requestors: perry.shafran at noaa.gov
>>>>>>>> Status: new
>>>>>>>> Ticket <URL:
>>>>>>>> https://rt.rap.ucar.edu/rt/Ticket/Display.html?id=80473 >
>>>>>>>>
>>>>>>>>
>>>>>>>> Hi, there,
>>>>>>>>
>>>>>>>> I would like to see if I can remove part of a MySQL database,
in
>>>>>>>> order to
>>>>>>>> free up some space, but not delete the database entirely so I
can
>>>>>>>> save
>>>>>>> that
>>>>>>>> stuff.
>>>>>>>>
>>>>>>>> Is there a way to:
>>>>>>>>
>>>>>>>> a) Save a database and store it offline
>>>>>>>> b) Delete a portion of the database (say 1 or 2 year's worth
of
>>>>>>>> data)
>>>>>>>> c) Restore the entire database later if need be
>>>>>>>>
>>>>>>>> Thanks!
>>>>>>>>
>>>>>>>> Perry
>>>>>>>>
>>>
>
>
------------------------------------------------
Subject: removing part of a MySQL database
From: Tatiana Burek
Time: Wed May 17 08:30:30 2017
The scrubbing module performs 'OPTIMIZE TABLE' operation after
deleting. It 'reclaims the unused space and defragments the data
file'. This should take care of the freeing the space.
Tatiana
On Tue May 16 15:45:57 2017, hsoh wrote:
> The commands are after "mysql -u <db_user> -p -A <database_name> "
>
>
> To delete a table:
>
> drop table <table_name>;
>
>
> To delete records:
>
> delete from <table_name> where <where_condition>;
>
> rename table <table_name> to <table_name>_tmp;
> create table <table_name> like <table_name>_tmp;
> insert into <table_name> select * from <table_name>_tmp;
> drop table <table_name>_tmp;
>
> Cheers,
> Howard
>
> On 5/16/2017 3:04 PM, Tatiana Burek via RT wrote:
> > All databases that were created after July 2015 are mandatory
> > assigned to be MyISAM. I added it to mv_mysql.sql script that
> > creates tables.
> > Could you provide the steps needed to release the disk space?
> >
> > Tatiana
> >
> > On Tue May 16 14:56:54 2017, hsoh wrote:
> >> Yes, the disk space will be released with dropping (deleting)
> >> tables.
> >> Deleting records requires extra manual steps to release disk
space.
> >>
> >> At dakota, there are about 30 databases with InnoDB engine and
about
> >> 160
> >> databases with MyISAM.
> >>
> >> Cheers,
> >> Howard
> >>
> >>
> >> On 5/16/2017 2:49 PM, Tatiana Burek via RT wrote:
> >>> https://rt.rap.ucar.edu/rt/Ticket/Display.html?id=80473
> >>> This is a comment. It is not sent to the Requestor(s):
> >>>
> >>> Howard,
> >>> Thanks for sharing the info about InnoDB.
> >>>
> >>> All tables in METViewer databases are created with ENGINE =
MyISAM.
> >>> Would the space be released in this case?
> >>>
> >>> Tatiana
> >>>
> >>> On Tue May 16 14:32:38 2017, hsoh wrote:
> >>>> InnoDB engine is a different story. The disk space is not
released
> >>>> with
> >>>> dropping (deleting) tables if the database engine is InnoDB.
> >>>>
> >>>> Tatiana, were InnoDB tables created with extra option (for
saving
> >>>> into
> >>>> individual file per table)?
> >>>> If not, one option is available for InnoDB engine tables which
is
> >>>> dropping (deleting) a whole database in order to save the disk
> >>>> space.
> >>>>
> >>>> Cheers,
> >>>> Howard
> >>>>
> >>>> On 5/16/2017 1:52 PM, Tatiana Burek via RT wrote:
> >>>>> Perry,
> >>>>>
> >>>>> You can use METViewer scrubbing module to remove parts of the
> >>>>> data.
> >>>>> Please look at this page:
> >>>>>
http://www.dtcenter.org/met/metviewer/doc/database_scrubbing.html
> >>>>>
> >>>>> It describes how to create a configuration XML file and run
the
> >>>>> script.
> >>>>> Tatiana
> >>>>>
> >>>>>
> >>>>> On Tue May 16 13:49:04 2017, perry.shafran at noaa.gov wrote:
> >>>>>> OK, let's say my database has data from 2015 thru 2017, and I
> >>>>>> wanted
> >>>>>> to a)
> >>>>>> save the database and then b) remove 2015 and Jan-June 2016
data
> >>>>>> from
> >>>>>> the
> >>>>>> database. How would I go about doing that? What are the
> >>>>>> commands?
> >>>>>>
> >>>>>> Thanks!
> >>>>>>
> >>>>>> Perry
> >>>>>>
> >>>>>> On Tue, May 16, 2017 at 3:44 PM, Howard Soh via RT
> >>>>>> <met_help at ucar.edu>
> >>>>>> wrote:
> >>>>>>
> >>>>>>> Yes, it can be done by using MySQL client applications.
> >>>>>>>
> >>>>>>> a) Save a database and store it offline.
> >>>>>>>
> >>>>>>> "mysqldump" does this. It saves 1) all tables, 2) some
tables,
> >>>>>>> or
> >>>>>>> some part of table.
> >>>>>>>
> >>>>>>> b) Delete a portion of the database (say 1 or 2 year's worth
of
> >>>>>>> data)
> >>>>>>>
> >>>>>>> Yes, it's possible: 1) deleting some tables or 2) deleting
the
> >>>>>>> part
> >>>>>>> of table. Dropping (deleting) some tables is better than
> >>>>>>> deleting the
> >>>>>>> part
> >>>>>>> of tables. Deleting part of tables does not save the disk
> >>>>>>> space.
> >>>>>>> MySQL
> >>>>>>> keeps the allocated disk space even though the part of
tables
> >>>>>>> were
> >>>>>>> deleted.
> >>>>>>> And it does not have a tool to shrink the disk space which
was
> >>>>>>> taken
> >>>>>>> by the
> >>>>>>> deleted records. But there is a work around to release disk
> >>>>>>> space.
> >>>>>>>
> >>>>>>>
> >>>>>>> c) Restore the entire database later if need be
> >>>>>>>
> >>>>>>> Yes this can be done by using "mysql" command.
> >>>>>>>
> >>>>>>> Cheers,
> >>>>>>> Howard
> >>>>>>>
> >>>>>>>
> >>>>>>> On 5/16/2017 11:14 AM, perry.shafran at noaa.gov via RT wrote:
> >>>>>>>> Tue May 16 11:14:40 2017: Request 80473 was acted upon.
> >>>>>>>> Transaction: Ticket created by perry.shafran at noaa.gov
> >>>>>>>> Queue: met_help
> >>>>>>>> Subject: removing part of a MySQL database
> >>>>>>>> Owner: Nobody
> >>>>>>>> Requestors: perry.shafran at noaa.gov
> >>>>>>>> Status: new
> >>>>>>>> Ticket <URL:
> >>>>>>>> https://rt.rap.ucar.edu/rt/Ticket/Display.html?id=80473 >
> >>>>>>>>
> >>>>>>>>
> >>>>>>>> Hi, there,
> >>>>>>>>
> >>>>>>>> I would like to see if I can remove part of a MySQL
database,
> >>>>>>>> in
> >>>>>>>> order to
> >>>>>>>> free up some space, but not delete the database entirely so
I
> >>>>>>>> can
> >>>>>>>> save
> >>>>>>> that
> >>>>>>>> stuff.
> >>>>>>>>
> >>>>>>>> Is there a way to:
> >>>>>>>>
> >>>>>>>> a) Save a database and store it offline
> >>>>>>>> b) Delete a portion of the database (say 1 or 2 year's
worth
> >>>>>>>> of
> >>>>>>>> data)
> >>>>>>>> c) Restore the entire database later if need be
> >>>>>>>>
> >>>>>>>> Thanks!
> >>>>>>>>
> >>>>>>>> Perry
> >>>>>>>>
> >>>
> >
> >
------------------------------------------------
More information about the Met_help
mailing list