packing database

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View

Hi all!

I am new to MySQL, and I have to short questions:

I have MySQL on W2K, with a DB where  I store date for app. one month,
then I delete them.
How can I pack the database, so I get a smaller file?
(currently 1 GB, was 740 MB a few days ago, even there is less data

I still login as root - I could not find a way to login to my DB using
my own username - probably simple - can anyone help?


Re: packing database


run the command

mysqlcheck -u root -p[your password] --all-databases --analyze --optimize

from the command line. To create a new user, use these commands:

grant usage on *.* to [username]@[hostname] identified by '[password]';
grant all on [database].* to [username]@[hostname] [with grant option];
(repeat this for every database that the new user should have access on).

The 'with grant option' is optional - if you add it, it gives this user
permission to grant other users the same permission.


Re: packing database

Thank you for your reply.

For the first option I have tried "optimize table", so it tells me now,
that I cannot run mtsqlcheck while running "optimize table".
How do I get rid of that?

I asume that Hostname is the computer's name?
The first commend tells me that 0 rows were affected. The other then
fails "Cannot find matching rows".


Re: packing database

You should not run optimize table and 'mysqlcheck ...' at the same time
(they do basically the same thing - mysqlcheck verified a little more') -
but there shouldn't be any other problems.

Quoted text here. Click to load it

This is the computer's name or the IP address(es) from where you should be
allowed to access the MySQL server. If it should only be allowed from your
computer, use 'localhost'. You can also use wildcards - if you e.g. have a
network where all hosts are the IP address range 192.168.0.x, you could
specify '192.168.0.%' to allow access from all computers in your network,
but not from outside (the Internet).

Which version do you use? If you use MySQL 5, try it this way:

create user [user]@[host] identified by '[password]';
grant all on [database].* to [user]@[host] [with grant tables]; (and again,
repeat this for all databases).

To find out, which users already exist, try

select Host, User, Password from mysql.user;

Is there a user, you want to delete, try

delete from mysql.user where Host='[host]' and User='[user]';
delete from mysq.db where Host='[host]' and User='[user]';
flush privileges;

or (with MySQL 5)

drop user [user]@[host];


Re: packing database

Markus Popp wrote:
Quoted text here. Click to load it

I figured that, but how do I stop it?

Seems like once started it stays.

The password is ok

I used MySql 5.0, with an older ODBC driver, as there are problems with
the new one with floating point numbers


Site Timeline