Need Sqldump Options Same As Sqlypog Uses

[duff]

Hi

I have a mysql database that holds all the companies design artifacts in enterprise architect , so very important that back and restore works 100%

At present

– if i used mysqldump on the server and do a restore , it truncates data with no errors

– If i use mysqlyog with the default options it works 100%

I used to truncate 8000 records but adding –complete-insert reduced that to 4 errors .

I have been using SELECT COUNT(Object_ID) FROM t_object; to validate is the live database and the restore database contain the same data

as we found one of the models was missing links

The files sizes are quite different

I guess sqlyog is calling a mysqldump on the server to do this work and guessed these settings are the equivalent

Options on Source ( not worried about these for now )

OFF – Lock tables for Read

OFF – Flush logs for dump

OFF – Single transaction

Options to file

ON – Include “USE database” statement –no-create-info ( Not passed for ON condition )

ON – Include “CREATE database” statement –no-create-db ( not passed for ON condition )

ON – Set FOREIGN_KEY_CHECKS=0 Env setting needs to be swiched on before use ( SET FOREIGN_KEY_CHECKS=0 )

OFF – Add lock around INSERT statments –add-locks ( Not passed for Off condition )

OFF – Create bulk INSERT statements –extended-insert ( Not passed for Off condition )

ON – Include “DROP” statments –add-drop-table ( passed for on Condition )

At present the closest i can get this is using these options

$MYSQLDUMP –databases $DB –add-drop-table –extended-insert=FALSE

–quick –single-transaction –lock-tables –add-locks

–complete-insert

–user=***** –password=********* –result-file=$RESULT_FILE_TMP

Any ideas , The mysql server install options are default

[peterlaursen]

I will move your post. This is not MONyog-related.

[peterlaursen]

Quote:

I guess sqlyog is calling a mysqldump on the server

No it doesn't!  Even if we wanted to we cannot do this.  A 'pure' MySQL client  cannot access file system and execute commands on remote servers. That would require a SSH-shell or similar inside the program and there is no such. SQLyog is compiled with the MySQL client C-API (you may know it as 'libmysql') and that one is all what is used for connection. We can only execute SQL (and similar API-calls).  The backup file is aggregated on the client machine from the returns of SELECT statements and the settings specified by user.

[duff]

Hi

Many thanks for your reply , that explains why were are getting differences in the backups

I will go back to investigating all the options for mysqldump

If i cannot get it to work i shall write my own backup script using select statements

may be useful as i can build in stats to validate against restorations tests

Really like Sqlyog its a joy to use

Cheers

[Author]

Posts