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
LVL 1
rgb192Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

arnoldCommented:
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.
0
rgb192Author Commented:
is devart for table comparison?
0
arnoldCommented:
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?
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

rgb192Author Commented:
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
0
arnoldCommented:
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?
0
rgb192Author Commented:
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
0
arnoldCommented:
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.
0
rgb192Author Commented:
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'
0
arnoldCommented:
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.
0
rgb192Author Commented:
I have so many different names
'again', 'repeat'

I have done this months ago and forget

I do not have the insert data
0
arnoldCommented:
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.
0
rgb192Author Commented:
>>
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
0
arnoldCommented:
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?
0
rgb192Author Commented:
godaddy.com mysql hosting

I use mysql workbench
0
arnoldCommented:
If you can access your mysql instance using mysql workbench, then you can use your mysqldump to remotely connect and get the data down.

I think you can also use workbench to copy the remote database and store it locally.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rgb192Author Commented:
I can not do a mysql dump because connection is remote.  Mysql workbench only allows mysql dump from localhost connection.
0
rgb192Author Commented:
ok, mysql dump using php or phpmyadmin could be a solution

thanks
0
arnoldCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.