Link to home
Start Free TrialLog in
Avatar of rgb192
rgb192Flag for United States of America

asked on

mysql comparison command

using Mysql workbench
http://dev.mysql.com/doc/workbench/en/mysqldbcompare.html

want to compare two tables which have the same columns


I want to compare the rows

table1 is email_doc3
and
table2 is email_doc3b

what is the comparison command
Avatar of arnold
arnold
Flag of United States of America image

The tool is to compare two database.

You might fare better using a quick perl script to run through compring the info.
Load data from and select from the other with different parameters of interest in the where clause.

The commercial tools, include the options. Devart includes a 30 day trial.
Avatar of rgb192

ASKER

is devart for table comparison?
I do not know.
What fields within each table are of interest to you?

Are the both tables use the same id column?
I.e. can you run a join with the exclusion of non matching columns?
Avatar of rgb192

ASKER

CREATE TABLE `email_doc_again3` (
  `id` int(11) NOT NULL auto_increment,
  `unix_timestamp` bigint(20) default NULL,
  `from_email` varchar(200) default NULL,
  `from_name` varchar(200) default NULL,
  `to_email` varchar(200) default NULL,
  `to_name` varchar(200) default NULL,
  `subject` varchar(400) default NULL,
  `body` varchar(4000) default NULL,
  `real_id` varchar(30) default NULL,
  `checked` int(11) default NULL,
  `me_description` varchar(9000) default NULL,
  `client_description` varchar(4000) default NULL,
  `sms_type` tinyint(4) default NULL,
  `client_start` datetime default NULL,
  `client_end` datetime default NULL,
  `client_total` int(11) default NULL,
  `me_start` datetime default NULL,
  `me_end` datetime default NULL,
  `me_total` int(11) default NULL,
  `real_timestamp` int(11) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `unix_timestamp` (`unix_timestamp`)
) ENGINE=MyISAM AUTO_INCREMENT=2000 DEFAULT CHARSET=utf8;

Open in new window



same exact table I did create table like
as a backup
just so I would not lose data on a bad update
Having a backup setup with binary logging.
How or when does the second table get updated to reflect a good update?

Are you using a required approval to get an update from table1 into table2?
Avatar of rgb192

ASKER

CREATE TABLE email_doc_bak LIKE email_doc;
INSERT INTO email_doc_bak SELECT * FROM email_doc;

I only update the current table

I am not using any fancy tools such as logging this is a personal project.

I have done this 15 or more times

and I do not know which is the newest table
Do a select on unix_timestamp to dentifies the table with
Select max(unix_timestamp) from table

The table that has the more height number is the newest.
Avatar of rgb192

ASKER

that column is unix_timestamp of the email


there are tables which use less/more columns


I think a tablesize may be the newest
all the tables begin with 'email_doc'
If you maintain the same naming convention, I.e. increment every iteration/attempt then
Doc_email_againx where x is the largest would point to the newest.
Alternatively, if you look at the interface you use to insert data, that might point to the last table used.
Avatar of rgb192

ASKER

I have so many different names
'again', 'repeat'

I have done this months ago and forget

I do not have the insert data
Where you doing a raw insert? Or where you using a form?
I.e. real_timestamp is used to unix_timestamp().

You could use mysqldump to export the data from each table and then determine which has the information you want.  Not sure how a table comparison will tell you which table is which.

The backups will have the insert data that you could then re-use.
Avatar of rgb192

ASKER

>>
You could use mysqldump to export the data from each table and then determine which has the information you want.  Not sure how a table comparison will tell you which table is which.

it is a remote website not on localhost
Do you have access to phpmyadmin on the remote side?
Do you have remote access to the mysql server?

What type of access to the remote do you have?
Avatar of rgb192

ASKER

godaddy.com mysql hosting

I use mysql workbench
ASKER CERTIFIED SOLUTION
Avatar of arnold
arnold
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of rgb192

ASKER

I can not do a mysql dump because connection is remote.  Mysql workbench only allows mysql dump from localhost connection.
Avatar of rgb192

ASKER

ok, mysql dump using php or phpmyadmin could be a solution

thanks
if you have mysql server installed locally, one of the tools is mysqldump

C:\Program Files\MySQL\MySQL Server 5.5\bin
mysqldump.exe --help
mysqldump.exe --host=remotehostofyourmysqlserver  -u username --password=yourpassword database [tables]

The data will be output unless you redirect it >> file.sql