mysqldump vs phpmyadmin dump

--0016e64b96deb884670478c70d4c
Content-Type: text/plain; charset=UTF-8

Hi everyone,

Here is a rookie question.

The problem what I found is that mysqldump and phpmyadmin generate different
size of backup file against same database.

I try to dump same database with the 2 different methods, the original
database is 2.8mb, phpmyadmin export 1.5mb file, and mysqldump export only
941kb file.

I know there must be some difference between the two export method, but
after I import the 941kb file which mysqldump created into a new database,
it just works fine.

So I'm not sure if I can use mysqldump as the best option to do mysql
backup, can someone can help me to figure out why phpmyadmin would generate
twice big file? And I see some post that address it is not recommend to
import mysqldump file by using phpmyadmin, because it will cause problem.

Thanks and best regards

Wang

--0016e64b96deb884670478c70d4c--

Re: mysqldump vs phpmyadmin dump

Wang Zi Feng wrote:
> Hi everyone,
>
> Here is a rookie question.
>
> The problem what I found is that mysqldump and phpmyadmin generate different
> size of backup file against same database.
>
> I try to dump same database with the 2 different methods, the original
> database is 2.8mb, phpmyadmin export 1.5mb file, and mysqldump export only
> 941kb file.

mysqldump has a number of different settings, and the file size will
vary according to which you use. For example, using extended insert
syntax will significantly increase the size of the output, and that
could easily account for the difference between your two files.

What's probably happening is that the settings you're using when running
mysqldump from the command line are different to those used by
phpMyAdmin, so you end up with differently formatted files.

> I know there must be some difference between the two export method, but
> after I import the 941kb file which mysqldump created into a new database,
> it just works fine.
>
> So I'm not sure if I can use mysqldump as the best option to do mysql
> backup, can someone can help me to figure out why phpmyadmin would generate
> twice big file? And I see some post that address it is not recommend to
> import mysqldump file by using phpmyadmin, because it will cause problem.

Importing any large file via phpMyAdmin is likely to have problems, as
you'll find yourself limited by the maximum upload file size of the web
server where phpMyAdmin is running (that's typically 2Mb for PHP on
Apache, although the administrators can change that). But the source of
the file is irrelevant; so long as it's within the file upload limit
then it doesn't matter whether it was exported by phpMyAdmin itself or
created using mysqldump from the command line.

Mark

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: mysqldump vs phpmyadmin dump

--0016e64ccb9cbf2f780478ca3b43
Content-Type: text/plain; charset=UTF-8

hi there, thanks for your reply.

the mysql server is running on windows xp sp3, every time I use root user to
log in.

the command line i use for mysqldump is

mysqldump --user=root --password=pass test>test.sql

phpmyadmin is with following checked

Add DROP TABLE / VIEW / PROCEDURE / FUNCTION / EVENT

Add IF NOT EXISTS

Add AUTO_INCREMENT value

Enclose table and field names with backquotes

Complete inserts

Extended inserts

I read about the manual saying that mysqldump is default enabled with -opt,
which is --add-drop-table --add-locks --create-options --disable-keys
--extended-insert --lock-tables --quick --set-charset.

So I wonder what is the most secure way to backup mysql database to keep
data consistency?

Thanks and best regards

Wang

2009/11/20 Mark Goodge

> Wang Zi Feng wrote:
>
>> Hi everyone,
>>
>> Here is a rookie question.
>>
>> The problem what I found is that mysqldump and phpmyadmin generate
>> different
>> size of backup file against same database.
>>
>> I try to dump same database with the 2 different methods, the original
>> database is 2.8mb, phpmyadmin export 1.5mb file, and mysqldump export only
>> 941kb file.
>>
>
> mysqldump has a number of different settings, and the file size will vary
> according to which you use. For example, using extended insert syntax will
> significantly increase the size of the output, and that could easily account
> for the difference between your two files.
>
> What's probably happening is that the settings you're using when running
> mysqldump from the command line are different to those used by phpMyAdmin,
> so you end up with differently formatted files.
>
>
> I know there must be some difference between the two export method, but
>> after I import the 941kb file which mysqldump created into a new database,
>> it just works fine.
>>
>> So I'm not sure if I can use mysqldump as the best option to do mysql
>> backup, can someone can help me to figure out why phpmyadmin would
>> generate
>> twice big file? And I see some post that address it is not recommend to
>> import mysqldump file by using phpmyadmin, because it will cause problem.
>>
>
> Importing any large file via phpMyAdmin is likely to have problems, as
> you'll find yourself limited by the maximum upload file size of the web
> server where phpMyAdmin is running (that's typically 2Mb for PHP on Apache,
> although the administrators can change that). But the source of the file is
> irrelevant; so long as it's within the file upload limit then it doesn't
> matter whether it was exported by phpMyAdmin itself or created using
> mysqldump from the command line.
>
> Mark
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=frank.zifeng@gmail.com
>
>

--0016e64ccb9cbf2f780478ca3b43--