Number of Rows in DB.
Does anyone know of a quick way to get the number of rows in a database from the command prompt. I run a cron job that does a mysql dump nightly from a prod server piped to a standy server. It's a 2 line shell script including outputting the date at time. After the dump, I would like to output the number of rows in the prod database and the newly updated standby database to make sure these match. The output is e-mailed to me, so in the morning I could simply see if they match and be assured that the dump went well. Thanks, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Number of Rows in DB.
hi.. you can use... select count(1) from table_name; (it will traverse only column 1) as count(*) will go through all the columns, it will take much time compared to this which generally will take less time. by the way, can you send the shell script which you are using, it might be of some use to me. Thanks sujay -Original Message- From: Michael J. Pawlowsky [mailto:[EMAIL PROTECTED] Sent: Thursday, October 14, 2004 4:24 PM To: [EMAIL PROTECTED] Subject: Number of Rows in DB. Does anyone know of a quick way to get the number of rows in a database from the command prompt. I run a cron job that does a mysql dump nightly from a prod server piped to a standy server. It's a 2 line shell script including outputting the date at time. After the dump, I would like to output the number of rows in the prod database and the newly updated standby database to make sure these match. The output is e-mailed to me, so in the morning I could simply see if they match and be assured that the dump went well. Thanks, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Number of Rows in DB.
I'm want ALL the rows from the WHOLE DATABASE not just one table. I could do a show tables and then loop through each one and perform the select count and add it up. But I'm hoping to find a better way. As for the script, simply read the mysqldump man page. It is in the examples near the bottom. In this case I needed to tweak it a bit by adding some quotes (-Q) and extenderd inserts etc. Otherwise I was gettting errors. But I ran the stock example on the man page for a couple of years now on 2 other databases. Mike Sujay Koduri wrote: hi.. you can use... select */count(1) /*from table_name; (it will traverse only column 1) as count(*) will go through all the columns, it will take much time compared to this which generally will take less time. by the way, can you send the shell script which you are using, it might be of some use to me. Thanks sujay -Original Message- From: Michael J. Pawlowsky [mailto:[EMAIL PROTECTED] Sent: Thursday, October 14, 2004 4:24 PM To: [EMAIL PROTECTED] Subject: Number of Rows in DB. Does anyone know of a quick way to get the number of rows in a database from the command prompt. I run a cron job that does a mysql dump nightly from a prod server piped to a standy server. It's a 2 line shell script including outputting the date at time. After the dump, I would like to output the number of rows in the prod database and the newly updated standby database to make sure these match. The output is e-mailed to me, so in the morning I could simply see if they match and be assured that the dump went well. Thanks, Mike -- 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]
RE: Number of Rows in DB.
hi all.. I think there is no way to count all the rows in a DB, using a single query. we can say.. desc table_name; (gives all the tables in the DB) and then get no of rows for each table.. Thanks sujay -Original Message- From: Michael J. Pawlowsky [mailto:[EMAIL PROTECTED] Sent: Thursday, October 14, 2004 5:00 PM To: [EMAIL PROTECTED] Subject: Re: Number of Rows in DB. I'm want ALL the rows from the WHOLE DATABASE not just one table. I could do a show tables and then loop through each one and perform the select count and add it up. But I'm hoping to find a better way. As for the script, simply read the mysqldump man page. It is in the examples near the bottom. In this case I needed to tweak it a bit by adding some quotes (-Q) and extenderd inserts etc. Otherwise I was gettting errors. But I ran the stock example on the man page for a couple of years now on 2 other databases. Mike Sujay Koduri wrote: hi.. you can use... select */count(1) /*from table_name; (it will traverse only column 1) as count(*) will go through all the columns, it will take much time compared to this which generally will take less time. by the way, can you send the shell script which you are using, it might be of some use to me. Thanks sujay -Original Message- From: Michael J. Pawlowsky [mailto:[EMAIL PROTECTED] Sent: Thursday, October 14, 2004 4:24 PM To: [EMAIL PROTECTED] Subject: Number of Rows in DB. Does anyone know of a quick way to get the number of rows in a database from the command prompt. I run a cron job that does a mysql dump nightly from a prod server piped to a standy server. It's a 2 line shell script including outputting the date at time. After the dump, I would like to output the number of rows in the prod database and the newly updated standby database to make sure these match. The output is e-mailed to me, so in the morning I could simply see if they match and be assured that the dump went well. Thanks, Mike -- 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]
Re: Number of Rows in DB.
try: SHOW TABLE STATUS Shawn Green Database Administrator Unimin Corporation - Spruce Pine Michael J. Pawlowsky [EMAIL PROTECTED] wrote on 10/14/2004 06:54:16 AM: Does anyone know of a quick way to get the number of rows in a database from the command prompt. I run a cron job that does a mysql dump nightly from a prod server piped to a standy server. It's a 2 line shell script including outputting the date at time. After the dump, I would like to output the number of rows in the prod database and the newly updated standby database to make sure these match. The output is e-mailed to me, so in the morning I could simply see if they match and be assured that the dump went well. Thanks, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Number of Rows in DB.
Sujay, The output of DESCRIBE table_name; does not include number of rows. Perhaps you meant SHOW TABLE STATUS LIKE 'table_name'; or simply SHOW TABLE STATUS; as Michael suggested, to get output for each and every table. If so, it is important to note that the row count for InnoDB tables is an approximation, rather than an accurate count, so it won't help here. http://dev.mysql.com/doc/mysql/en/SHOW_TABLE_STATUS.html Michael, I'm not sure how helpful the total will be. Apparently, you are worried about the possibility of the standby db having the wrong number of rows, but in that scenario, isn't it possible that table A has lost a row, while table B has gained a row, yielding identical totals? If you do a SHOW TABLE STATUS in the prod db and loop through the results, you can get the row counts for the MyISAM tables directly from the results and then do a SELECT COUNT(*) from the InnoDB tables to get their row counts. Now that you have numbers for each table, why not do the same thing in standby and compare the results table by table, rather than adding and comparing the total? WARNING: Note that any inserts or deletes on prod between the mysqldump and the counting will throw off the results. If you just want to verify that standby matches the dump, I suppose you could turn off extended inserts and count the number of inserts in the dumpfile with something like grep 'INSERT' dumpfile | wc -l then compare that total to the total obtained by looping through the tables in standby, as described above. But I'm still skeptical of the usefulness of that test. Michael Sujay Koduri wrote: hi all.. I think there is no way to count all the rows in a DB, using a single query. we can say.. desc table_name; (gives all the tables in the DB) and then get no of rows for each table.. Thanks sujay -Original Message- From: Michael J. Pawlowsky [mailto:[EMAIL PROTECTED] Sent: Thursday, October 14, 2004 5:00 PM To: [EMAIL PROTECTED] Subject: Re: Number of Rows in DB. I want ALL the rows from the WHOLE DATABASE not just one table. I could do a show tables and then loop through each one and perform the select count and add it up. But I'm hoping to find a better way. As for the script, simply read the mysqldump man page. It is in the examples near the bottom. In this case I needed to tweak it a bit by adding some quotes (-Q) and extended inserts etc. Otherwise I was gettting errors. But I ran the stock example on the man page for a couple of years now on 2 other databases. Mike Sujay Koduri wrote: hi.. you can use... select */count(1) /*from table_name; (it will traverse only column 1) as count(*) will go through all the columns, it will take much time compared to this which generally will take less time. by the way, can you send the shell script which you are using, it might be of some use to me. Thanks sujay -Original Message- From: Michael J. Pawlowsky [mailto:[EMAIL PROTECTED] Sent: Thursday, October 14, 2004 4:24 PM To: [EMAIL PROTECTED] Subject: Number of Rows in DB. Does anyone know of a quick way to get the number of rows in a database from the command prompt. I run a cron job that does a mysql dump nightly from a prod server piped to a standy server. It's a 2 line shell script including outputting the date at time. After the dump, I would like to output the number of rows in the prod database and the newly updated standby database to make sure these match. The output is e-mailed to me, so in the morning I could simply see if they match and be assured that the dump went well. Thanks, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Number of Rows in DB.
Note that SHOW TABLE STATUS counts for innodb are educated guesses -- innodb has to do a table scan to get the actual count. On Thu, 14 Oct 2004 10:19:19 -0400, Michael Stassen [EMAIL PROTECTED] wrote: Sujay, The output of DESCRIBE table_name; does not include number of rows. Perhaps you meant SHOW TABLE STATUS LIKE 'table_name'; or simply SHOW TABLE STATUS; as Michael suggested, to get output for each and every table. If so, it is important to note that the row count for InnoDB tables is an approximation, rather than an accurate count, so it won't help here. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Number of Rows in DB.
Getting closer Thanks... This db had LOTS of tables... That's why I'm simply trying to get the total. Is there a way to only get one column of this. then I can do something like select SUM(show table status (rows)); [EMAIL PROTECTED] wrote: try: SHOW TABLE STATUS Shawn Green Database Administrator Unimin Corporation - Spruce Pine Michael J. Pawlowsky [EMAIL PROTECTED] wrote on 10/14/2004 06:54:16 AM: Does anyone know of a quick way to get the number of rows in a database from the command prompt. I run a cron job that does a mysql dump nightly from a prod server piped to a standy server. It's a 2 line shell script including outputting the date at time. After the dump, I would like to output the number of rows in the prod database and the newly updated standby database to make sure these match. The output is e-mailed to me, so in the morning I could simply see if they match and be assured that the dump went well. Thanks, Mike -- 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]
Re: Number of Rows in DB.
Well basically what I did was simply move a script that I've been using on other databases and tried it on this new LARGE db. And luckily the number or rows were off by something like 30,000 so it was easy to figure out something went wrong (got total from phpMyAdmin) I then tweak my dumps so that it works. I'm just worried that some new data might be introduced into the db that might wreck my dumps again. So I wanted a way to compare the databases. To be honest if I'm off a couple of rows, that won't bother me too much. But if it's more than that I will need to investigate. Mike Michael, I'm not sure how helpful the total will be. Apparently, you are worried about the possibility of the standby db having the wrong number of rows, but in that scenario, isn't it possible that table A has lost a row, while table B has gained a row, yielding identical totals? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Number of Rows in DB.
Michael J. Pawlowsky [EMAIL PROTECTED] wrote on 14/10/2004 17:01:34: Getting closer Thanks... This db had LOTS of tables... That's why I'm simply trying to get the total. Is there a way to only get one column of this. then I can do something like select SUM(show table status (rows)); IF all the tables have the same table structure, create a MERGE table of them all, then SELECT count(*) FROM merged ; Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Number of Rows in DB.
They do not... But I think I will just re-write my script in php command line and loop through it. It was just that I loved the simplicity of the bash script. [EMAIL PROTECTED] wrote: Michael J. Pawlowsky [EMAIL PROTECTED] wrote on 14/10/2004 17:01:34: Getting closer Thanks... This db had LOTS of tables... That's why I'm simply trying to get the total. Is there a way to only get one column of this. then I can do something like select SUM(show table status (rows)); IF all the tables have the same table structure, create a MERGE table of them all, then SELECT count(*) FROM merged ; Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Number of Rows in DB.
Well, here's a quick and dirty way to do it: #!/bin/sh mkdir /tmp/count chmod a+rwx /tmp/count cd /tmp/count mysqldump -T . production wc -l *.txt production rm *.sql *.txt mysqldump -T . standby wc -l *.txt standby diff production standby rm -rf /tmp/count Creates output like so: 20,21c20,21 3 users.txt 952 total --- 6 users.txt 955 total Eamon Daly - Original Message - From: Michael J. Pawlowsky [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, October 14, 2004 5:54 AM Subject: Number of Rows in DB. Does anyone know of a quick way to get the number of rows in a database from the command prompt. I run a cron job that does a mysql dump nightly from a prod server piped to a standy server. It's a 2 line shell script including outputting the date at time. After the dump, I would like to output the number of rows in the prod database and the newly updated standby database to make sure these match. The output is e-mailed to me, so in the morning I could simply see if they match and be assured that the dump went well. Thanks, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Number of Rows in DB.
Thanks Eamon... I will think about it... But I'm looking at almost 1,000,000 rows. Not sure this is the most effecient way. But I do appreciate the effort! I think the idea of a php script that loops through show tables to get the table names and then does a Select COUNT(*) on each one and sums it up would probably be more effecient. Cheers, Mike Eamon Daly wrote: Well, here's a quick and dirty way to do it: #!/bin/sh mkdir /tmp/count chmod a+rwx /tmp/count cd /tmp/count mysqldump -T . production wc -l *.txt production rm *.sql *.txt mysqldump -T . standby wc -l *.txt standby diff production standby rm -rf /tmp/count Creates output like so: 20,21c20,21 3 users.txt 952 total --- 6 users.txt 955 total Eamon Daly - Original Message - From: Michael J. Pawlowsky [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, October 14, 2004 5:54 AM Subject: Number of Rows in DB. Does anyone know of a quick way to get the number of rows in a database from the command prompt. I run a cron job that does a mysql dump nightly from a prod server piped to a standy server. It's a 2 line shell script including outputting the date at time. After the dump, I would like to output the number of rows in the prod database and the newly updated standby database to make sure these match. The output is e-mailed to me, so in the morning I could simply see if they match and be assured that the dump went well. Thanks, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Number of Rows in DB.
Would UNION help? Something like this: select count(ID) as Total from categories where ID 5 UNION select count(ID) as Total from products where ID 5 UNION select count(ID) as Total from systems where ID 5 Then you could just sum Total? -Original Message- They do not... But I think I will just re-write my script in php command line and loop through it. It was just that I loved the simplicity of the bash script. Getting closer Thanks... This db had LOTS of tables... That's why I'm simply trying to get the total. Is there a way to only get one column of this. then I can do something like select SUM(show table status (rows)); IF all the tables have the same table structure, create a MERGE table of them all, then SELECT count(*) FROM merged ; Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Number of Rows in DB.
Pfft. Efficiency, schmefficiency. Here's a version in perl:
#!/usr/bin/perl
use strict;
use warnings;
use DBI;
use MySQL::Config qw(parse_defaults);
die $0: specify exactly 2 arguments ($0 table_1 table_2)\n if @ARGV != 2;
my %mysql_cfg = parse_defaults my, ['client'];
my $user = $mysql_cfg{'user'} || $ENV{'USER'};
my $pass = $mysql_cfg{'password'};
my %tables;
foreach my $db (@ARGV) {
my $dbh = DBI-connect(DBI:mysql:database=$db, $user, $pass) || die;
for (@{ $dbh-selectall_arrayref('SHOW TABLE STATUS') }) {
$tables{$db}{$_-[0]} =
$dbh-selectrow_array(SELECT COUNT(1) FROM $_-[0]);
}
}
foreach my $table (sort keys %{ $tables{$ARGV[0]} }) {
printf(Table `%s` differs (%d rows in %s, %d rows in %s)\n,
$table,
$tables{$ARGV[0]}{$table}, $ARGV[0],
$tables{$ARGV[1]}{$table}, $ARGV[1])
if $tables{$ARGV[0]}{$table} != $tables{$ARGV[1]}{$table};
}
Run like so:
$ ./whee.pl production standby
Table `users` differs (6 rows in production, 8 rows in standby)
Eamon Daly
- Original Message -
From: Michael J. Pawlowsky [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Thursday, October 14, 2004 12:19 PM
Subject: Re: Number of Rows in DB.
Thanks Eamon... I will think about it... But I'm looking at almost
1,000,000 rows.
Not sure this is the most effecient way. But I do appreciate the effort!
I think the idea of a php script that loops through show tables to get
the table names and then does a Select COUNT(*) on each one and sums
it up would probably be more effecient.
Cheers,
Mike
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Number of Rows in DB.
SQL: create TEMPORARY table A select count(1) as C from TABLE1; insert into A select count(1) as C from TABLE2; . select sum(C) from a; drop table A; Or create TEMPORARY table A (C integer); insert into A select count(1) from TABLE1 union all select count(1) from TABLE2 ... union all select count(1) from TABLEn; select sum(C) as count from A; drop table A; Santino At 6:54 -0400 14-10-2004, Michael J. Pawlowsky wrote: Does anyone know of a quick way to get the number of rows in a database from the command prompt. I run a cron job that does a mysql dump nightly from a prod server piped to a standy server. It's a 2 line shell script including outputting the date at time. After the dump, I would like to output the number of rows in the prod database and the newly updated standby database to make sure these match. The output is e-mailed to me, so in the morning I could simply see if they match and be assured that the dump went well. Thanks, Mike -- 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]
