Hi,
I have removed all old data and restore the backup data to the directory /usr/local/mysql/data
I also created root password with mysqladmin and flush its previliges...
Execute the following perl+dbi script seems fine, it retrieves all data from the customer table in DB which I just restored:
#!/usr/bin/perl
use DBI;
$database = "DB";
$username = "me";
$pw = "me0901";
$dbh = DBI->connect("DBI:mysql:$database",$username,$pw);
die "Unable to connect: $DBI::errstr\n" unless (defined $dbh);
$sth = $dbh->prepare(q{SELECT * from customer}) or die "Unable to prep our query:".$dbh->errstr."\n";
$rc = $sth->execute or die "Unable to execute our query:".$dbh->errstr."\n";
while ($aref = $sth->fetchrow_arrayref){
for ($i=0; $i < $sth->{NUM_OF_FIELDS};$i++){
print $sth->{NAME}->[$i].": " . $aref->[$i] . "\n";
}
}
$sth->finish;
$dbh->disconnect;
But when I execute a complex query, it hangs there forever. I don't have a chance to see the result because I need to go home to have dinner. The same query run in a FreeBSD system in Mysql5.01 just takes 2 seconds. The MySQL 5.01 in FreeBSD is the source of the backup. I restore this backup to MySQL 4.1.10 in Redhat9.0.
I have checked the DB's indexes on the most complex table, it shows indexes are being indexed.
mysql> show index from transaction;
+-------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| transaction | 0 | PRIMARY | 1 | transcode | A | 161629 | NULL | NULL | | BTREE | |
| transaction | 1 | custcode | 1 | custcode | A | 715 | NULL | NULL | | BTREE | |
| transaction | 1 | prodcode | 1 | prodcode | A | 3367 | NULL | NULL | | BTREE | |
| transaction | 1 | date | 1 | date | A | 1197 | NULL | NULL | | BTREE | |
| transaction | 1 | netsales | 1 | netsales | A | 23089 | NULL | NULL | | BTREE | |
| transaction | 1 | salesvolume | 1 | salesvolume | A | 206 | NULL | NULL | | BTREE | |
| transaction | 1 | custcode_2 | 1 | custcode | A | 788 | NULL | NULL | | BTREE | |
| transaction | 1 | custcode_2 | 2 | date | A | 53876 | NULL | NULL | | BTREE | |
| transaction | 1 | prodcode_2 | 1 | prodcode | A | 923 | NULL | NULL | | BTREE | |
| transaction | 1 | prodcode_2 | 2 | date | A | 161629 | NULL | NULL | | BTREE | |
+-------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
10 rows in set (0.47 sec)
One strange thing I found is when the query was running, I use top command to check the loading of mysqld, but it is 0%. This is usually 9x% when the same perl script running agains MySQL5.0.1 in FreeBSD. The result of running the top command in Redhat against mysqld is shown as below:
19:26:21 up 14 days, 20:38, 1 user, load average: 0.00, 0.00, 0.00
1 processes: 1 sleeping, 0 running, 0 zombie, 0 stopped
CPU states: 0.0% user 0.1% system 0.0% nice 0.0% iowait 99.8% idle
Mem: 513852k av, 506580k used, 7272k free, 0k shrd, 124196k buff
370448k actv, 0k in_d, 10116k in_c
Swap: 2132888k av, 15556k used, 2117332k free 270220k cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND 2893 mysql 15 0 50560 44M 3008 S 0.0 8.8 0:00 0 mysqld
But I confirmed that all mysqld instances are running in the system as shown below:
# netstat -a | grep mysql
tcp 0 0 *:mysql *:* LISTEN
unix 2 [ ACC ] STREAM LISTENING 127441 /tmp/mysql.sock
[EMAIL PROTECTED] root]# !ps
ps -auxww | grep mysql
root 2873 0.0 0.1 4360 1016 ? S 14:16 0:00 /bin/sh ./S98mysqld
mysql 2893 0.0 8.8 302680 45340 ? S 14:16 0:00 [mysqld]
mysql 2894 0.0 8.8 302680 45340 ? S 14:16 0:00 [mysqld]
mysql 2895 0.0 8.8 302680 45340 ? S 14:16 0:00 [mysqld]
....
The questions I would like to ask is how to make sure mysqld is start up properly? Because it seems that the start script that come with the binary files from mysql4.1.10 can';t be started by typing "mysql.server start", and I don't see a /etc/my.conf is installed in the system, so I copied the support-files/my-huge.cnf to /etc/my.cnf.
The second question is how to make sure all indexes are in-placed after the data restore? I used the following script backup all data from MySQL5.0.1:
DBNAMES="`mysql --user=$USERNAME --password=$PASSWORD --host=$DBHOST --batch --skip-column-names -e "show databases"|
sed 's/ /%/g'`"
Thank you in advance Sam.
mel list_php wrote:
Yeah, I have looked at it, but not sure whether I need to repopulate the mysql.host db file. If I do that, I may be also need to recreate all db passwords as well.
The error is:
InnoDB: log sequence number 0 43634.
InnoDB: Doing recovery: scanned up to log sequence number 0 43634
050302 16:36:43 InnoDB: Flushing modified pages from the buffer pool...
050302 16:36:43 InnoDB: Started; log sequence number 0 43634
050302 16:36:43 [ERROR] Fatal error: Can't open privilege tables: Table 'mysql.host' doesn't exist
050302 16:36:43 mysqld ended
I don't know how you made your backup, apparently you missed the mysql.host table.
Usually when making a new install a mysql database containing all the privileges is created, maybe you erased this when copying your files?
In an other mail you say you used the --all-databases option so I suppose the error is somewhere else.
To identify the problem you may try to recreate the mysql db, see if it works, and if yes check your dump.
About your indexes I usually use mysqldump and the indexes are exported as well, you can check with show index but I'm not sure that is what you are looking for.
http://dev.mysql.com/doc/mysql/en/mysqldump.html
Since all DB data in this server are restored from the FreeBSD system in MySQL 5.0. I don't know how to recreate all indexes for db.
Is there simple way for recreating all indexes? or check whether indexes are in-placed?
Thanks Sam
Good luck!
From: sam wun <[EMAIL PROTECTED]> To: mysql@lists.mysql.com Subject: Silly mistake Date: Wed, 02 Mar 2005 15:58:28 +0800
Hi,
I admit I m silly to shutdown mysqld with the killall command in the Redhat server, I can't restart mysql service now. Most of the reason is because the script mysql.server come with the mysql 4.1.10 does not like mysql.server start or mysql.server stop, so I need to start it up use & and shut it down with killall.
Anyway, after killall mysql, I got the following error when I tried to restart it.
Here is the error:
./mysqld
Starting mysqld daemon with databases from /usr/local/mysql/data
STOPPING server from pid file /usr/local/mysql/data/vivaserver.pid
050302 15:52:05 mysqld ended
How can I start mysqld now?
Thanks Sam
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
