The following takes a little effort, but it should get you close to dump
file size.
On 5.0.x you can use Information_schema.columns to get average row
length for MyISAM tables. Take that plus the punctutation {~35 + 3* #
cols for insert per row if you enclose your columns in "'s} in the
insert statements generated by mysqldump times the number of rows and
that will give you the size of the MyISAM tables.
For INNODB use
mysql> select avg(length(concat( col1, col2,...))) AS Avg_Len, count(*)
-> from table ;
+----------+----------+
| Avg_Len | count(*) |
+----------+----------+
| 107.5588 | 3514429 |
+----------+----------+
1 row in set (1 min 1.31 sec)
I would also use Information_schema.columns to get the column names so I
would not have to type them.
-----Original Message-----
From: Gleb Paharenko [mailto:[EMAIL PROTECTED]
Sent: Tuesday, December 20, 2005 12:24 PM
To: mysql@lists.mysql.com
Subject: [SPAM] - Re: locating ibdata1 and *.ibd files in different
directories. - Bayesian Filter detected spam
Hello.
> symbolic links! Thats a neat solution. Question: when you say
> "symbolic links for databases" do you mean links to ibd files, ibdata1
> file, either, or something else?
I've meant symbolic links for databases. See:
http://dev.mysql.com/doc/refman/5.0/en/symbolic-links.html
> Is mysql smart enough not to use my indices when importing until after
> the import, or should I de-activate my indices until after the import?
mysqldump from 5.0.17 distribution sets FOREIGN_KEY_CHECKS to 0; for
your version you can check this by yourself.
> Also, is there a formula of what I can expect the size of the dumped
> files to be?
For a pity, I don't know any formula, even approximate.
Nathan Gross wrote:
> On 12/20/05, Gleb Paharenko <[EMAIL PROTECTED]> wrote:
>
>>Hello.
>>
>>
>>
>>Please, next time answer to the list as well.
>
> Sorry. I didn't realize Gmail's 'reply' didn't go to the list. First
> time I noticed the 'reply to all' option in Gmail. Thanks.
>
>
>>As far as I know, you can't specify the location of ibd files,
they're s=
>
> tored in the
>
>>database directory, however, you can use symbolic links for databases
to =
>
> have
>
>>them in another place.
>
> symbolic links! Thats a neat solution. Question: when you say
> "symbolic links for databases" do you mean links to ibd files, ibdata1
> file, either, or something else?
>
>
>
>>>all databases? So the question is if I can locate the ibdata1 file
somew=
>
> here else.
>
>>
>>Have you tried just to change the value of innodb_home_dir to the new
>>location, and move there ibdata1 file?
>
> And leave the original subdirs(databases) in the original place? I can
> try. This means though, that the absolute db paths are coded into the
> ibdata file.
>
>>>ibd files. BUT, the ibdata1 file is still 7 gig and being
>>
>>
>>
>>If you want do decrease the size of ibdata1 file, you should dump all
>>your InnoDB tables, stop the server, remove all existing tablespace
>>files, configure a new tablespace, restart the server, import the dump
>>files. In such a way you'll move all your tables which are in ibdata1
>>tablespace to may ibd files in databases. See:
>>
>> http://dev.mysql.com/doc/refman/5.0/en/adding-and-removing.html
>
> Is mysql smart enough not to use my indices when importing until after
> the import, or should I de-activate my indices until after the import?
> Also, is there a formula of what I can expect the size of the dumped
> files to be?
>
> Thank you much!
> -nat
>
--
For technical support contracts, goto
https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
