mysql repl
Dear mysql@lists.mysql.com: Our db is the innodb eng. We have no hot backup tools to dump the data. If lock the table by the following cmd before we dump the data FLUSH TABLES WITH READ LOCK; It seems ok,it can lock the innodb too. But the replication would be crushed ,even it start up smoothly,due to the duplicated entry. Do any one try to lock innodb tables to make the snap shot? Since shutdown the master db would take great effect,we don't hope so. Best regards. MaFai [EMAIL PROTECTED] 2005-07-06 34955929 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
performance with 10 million entries
[EMAIL PROTECTED] : When use query with 'like' condition,it would cost too much time. Mysql is using innodb structure ,but not myisam. We haven't test how diff between innodb myisam. But now the performance is too low. We plan to separate the table group by date or specified flag. It seems need the table storing the splited table name. When query,the program needs loop the splited table. We know the Oracle support view. So the program needs not loop the table by application layer. But mysql doesn't provide view, any suggestion? BTW,we have tune the my.cnf,such query buffer,sorting buffer,innodb buffer,etc. Table structure: | WAVE_RECORD | CREATE TABLE `WAVE_RECORD` ( `wr_no` bigint(20) NOT NULL auto_increment, `ip_addr` varchar(15) NOT NULL default '', `file_name` varchar(100) default NULL, `rec_date` datetime NOT NULL default '-00-00 00:00:00', `in_phone` varchar(20) NOT NULL default '', `out_phone` varchar(20) NOT NULL default '', `staff_name` varchar(20) NOT NULL default '', `folder_name` varchar(8) NOT NULL default '', `status` char(1) NOT NULL default '', `log_date` datetime NOT NULL default '-00-00 00:00:00', `server_name` varchar(20) NOT NULL default '', `file_size` bigint(20) NOT NULL default '0', PRIMARY KEY (`wr_no`), UNIQUE KEY `file_name` (`file_name`), KEY `idx_ip_addr` (`ip_addr`), KEY `idx_rec_date` (`rec_date`), KEY `idx_in_phone` (`in_phone`), KEY `idx_out_phone` (`out_phone`), KEY `idx_staff_name` (`staff_name`), KEY `idx_status` (`status`) ) TYPE=InnoDB | Here's some example about the performance. mysql select count(*) from WAVE_RECORD; +--+ | count(*) | +--+ | 18733998 | +--+ 1 row in set (2 min 49.60 sec) mysql select count(*) from WAVE_RECORD where server_name='gz_inc01'; +--+ | count(*) | +--+ | 550421 | +--+ 1 row in set (14 min 47.11 sec) Best regards. MaFai [EMAIL PROTECTED] 2005-01-26 34955929 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: performance with 10 million entries
Dear Scott Haneda: Thx. I also try add the key for 'server_name',it cost 3 hours,but it still not yet completed. For some reason, wehave broken adding key process. _ We now engage on spliting table, hope this help. At2005-01-26,15:32:29you wrote: on1/25/0511:10PM,MaFaiat[EMAIL PROTECTED]wrote: mysql selectcount(*)fromWAVE_RECORDwhereserver_name='gz_inc01'; Idon'tseeakeyonserver_name,thatmayhelp -- - ScottHanedaTel:415.898.2602 http://www.newgeo.com Fax:313.557.5052 [EMAIL PROTECTED] Novato,CAU.S.A. = = = = = = = = = = = = = = = = = = = = = = Best regards. MaFai [EMAIL PROTECTED] 2005-01-26 34955929
Huge Innodb file
Dear [EMAIL PROTECTED]: Here's my setting: innodb_data_file_path = ibdata1:10M:autoextend Now the ibdata1 has been grow up to 1.3G We try to add more ibdata file to store the data by the following setting. innodb_data_file_path = ibdata1:1500M;ibdata2:1500M:autoextend innodb_data_file_path = ibdata1:10M;ibdata2:10M:autoextend But both of them failed,it seems the new setting would crush the db by the diff pages in the datafile. Here's the log, do any way to solve this? 040924 15:55:38 mysqld started InnoDB: Error: data file /home/mysql/data/ibdata1 is of a different size InnoDB: 82560 pages (rounded down to MB) InnoDB: than specified in the .cnf file 96000 pages! InnoDB: Could not open or create data files. InnoDB: If you tried to add new data files, and it failed here, InnoDB: you should now edit innodb_data_file_path in my.cnf back InnoDB: to what it was, and remove the new ibdata files InnoDB created InnoDB: in this failed attempt. InnoDB only wrote those files full of InnoDB: zeros, but did not yet use them in any way. But be careful: do not InnoDB: remove old data files which contain your precious data! 040924 15:55:38 Can't init databases 040924 15:55:38 Aborting Best regards. MaFai [EMAIL PROTECTED] 2004-09-24 34955929 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
flush table with read lock
Dear, [EMAIL PROTECTED], Does flush table with read lock timeout? E.g: After we excute flush table with read lock , All of the table in all of the database can't be updated or delete. But if we doesn't execute 'unlock tables',does it release the lock due to time out? Any idea appreciated. Best regards. MaFai [EMAIL PROTECTED] 2004-07-27
Re: Re: flush table with read lock
Dear Paul DuBois: Good,it can use make a hot backup by this lock. or make a snapshot for replication. Thx At 2004-07-27, 10:13:43 you wrote: At 9:52 +0800 7/27/04, MaFai wrote: Dear, [EMAIL PROTECTED], Does flush table with read lock timeout? No, but the lock will be released if the client connection drops. There is an inactivity timeout on client connections, so after that timeout expires, the server will close the connection. As a side effect the lock will be released. E.g: After we excute flush table with read lock , All of the table in all of the database can't be updated or delete. But if we doesn't execute 'unlock tables',does it release the lock due to time out? Any idea appreciated. Best regards. MaFai [EMAIL PROTECTED] 2004-07-27 -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] = = = = = = = = = = = = = = = = = = = = = = Best regards. MaFai [EMAIL PROTECTED] 2004-07-27
Re: RE: Upgrade 3.23 to 4.1.x
Dear Victor Pendleton: It contains both isam innodb tables. At 2004-07-14, 12:29:39 you wrote: What table types are you currently using? -Original Message- From: MaFai To: [EMAIL PROTECTED] Sent: 7/13/04 10:09 PM Subject: Upgrade 3.23 to 4.1.x Dear all: Do any one try to upgrade the mysql from 3.23 to 4.1? In doc,mysql doesn't recommend update the mysql from diff series.Do any one try it successfully? Do I just need to copy the datafile to the new mysql ? Best regards. MaFai [EMAIL PROTECTED] 2004-07-14 = = = = = = = = = = = = = = = = = = = = = = Best regards. MaFai [EMAIL PROTECTED] 2004-07-20
Fw: Confirm 2 question in replication.
Dear [EMAIL PROTECTED]: Dear, [EMAIL PROTECTED], I'd like to confirm 2 queries on the replication as follows: 1. Could we not using root on mysql for setting up the replication? ( I think no) 2. Could the password for replication be changed? ( I think Yes) Best regards. MaFai [EMAIL PROTECTED] 2004-07-20 = = = = = = = = = = = = = = = = = = = = Best regards. MaFai [EMAIL PROTECTED] 2004-07-20
Upgrade 3.23 to 4.1.x
Dear all: Do any one try to upgrade the mysql from 3.23 to 4.1? In doc,mysql doesn't recommend update the mysql from diff series.Do any one try it successfully? Do I just need to copy the datafile to the new mysql ? Best regards. MaFai [EMAIL PROTECTED] 2004-07-14
Re: RE: Replication Performance
Dear ALL: Some time the slave would crushed by I/O error.It happen at the server with the lower CPU.( per 45 days ) All of the slave database has block the binary log,it reduce the CPU loading. The database would replicate text,int,varchar but no blob. Network connection hasn't drop frequently,it seems hard to avoid that. Do any way make the slave query more time but not just idle and wait for the data? Since we can't bear the slave need 10 minutes to synchronize the data with master. Here the master status Uptime: 2592393 Threads: 24 Questions: 214644229 Slow queries: 197 Opens: 580 Flush tables: 1 Open tables: 254 Queries per second avg: 82.798 At 2004-07-01, 22:06:24 you wrote: What does the network setup look like? What type of data is being replicated varchar, text, blob? Is the network connection being dropped or are there any errors being logged anywhere? -Original Message- From: MaFai To: [EMAIL PROTECTED] Sent: 6/30/04 10:34 PM Subject: Replication Performance Dear, [EMAIL PROTECTED], We have set up 1 master and 4 slave as replication. Sometime,the slave need 4~10 minutes to synchronize the data with master database. Do any way to tune the performance? Or any other way to reduce the time to replicate? Best regards. MaFai [EMAIL PROTECTED] 2004-07-01 = = = = = = = = = = = = = = = = = = = = = = Best regards. MaFai [EMAIL PROTECTED] 2004-07-02
Replication Performance
Dear, [EMAIL PROTECTED], We have set up 1 master and 4 slave as replication. Sometime,the slave need 4~10 minutes to synchronize the data with master database. Do any way to tune the performance? Or any other way to reduce the time to replicate? Best regards. MaFai [EMAIL PROTECTED] 2004-07-01
Replication A-B-C
Hello all: I have set the repliaction between A and B server successfully. While the B server replicate the data from A server,we fould the relay log refreshing.But the master log has no change. Since we want to add a Server C to replicate the Server B, the master log must existing and refreshing. Should I set the Server C 's master log to the relay log(Server B's)? Or an other way to set the Server B updating the master log while it replicate the Server A. We have make sure that the 'log-bin' parameter in our my.cnf file and the master log file existing. Any idea apprecated. Best regards. MaFai [EMAIL PROTECTED] 2004-05-19
Want to monitor the replication of slave
Hello, mysql, I want to monitor the replication of slave. chk.sql content: show slave status $mysql -uroot -p chk.sql result But nothing happend. I want to know,how can I monitor the slave mysql. Any idea appreciated. Best regards. MaFai [EMAIL PROTECTED] 2003-11-10 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Slow query log setting
Hello, mysql, I have set the slow query parameter in the my.cnf as the following. [safe_mysqld] err-log=/var/log/mysqld.log log-slow-queries=/var/log/mysqlslow.log pid-file=/var/run/mysqld/mysqld.pid After I check the status of mysql today,we found 6 slow query occur. But there is no log in /var/log/mysqlslow.log,why? any idea appreciated. Mysql Version 4.0.12. Best regards. MaFai [EMAIL PROTECTED] 2003-11-06 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: Heap table in replication,bug?
Hello, Victoria Reznichenko, The p_showing table,the following is the create table sql statement. | p_showing | CREATE TABLE `p_showing` ( `showing_timestamp` timestamp(14) NOT NULL, `showing_channel_name` varchar(50) NOT NULL default '', `showing_asset_name` varchar(50) NOT NULL default '', `showing_start_time` datetime default NULL, `showing_keywords` varchar(100) default NULL, `showing_ip` varchar(15) NOT NULL default '', `showing_port` varchar(10) NOT NULL default '', `showing_end_time` datetime default NULL, `showing_resource` varchar(20) NOT NULL default '', `showing_status` varchar(20) NOT NULL default '', `showing_AutoDelte` varchar(5) NOT NULL default '', `showing_Feed` varchar(20) NOT NULL default '', `showing_Interactive_Control` varchar(5) NOT NULL default '', `showing_start_time_ctime` datetime default NULL, `showing_end_time_ctime` datetime default NULL, `showing_URL` varchar(100) NOT NULL default '', `source_ip` varchar(15) default NULL ) TYPE=HEAP | Mysql Version mysqladmin Ver 8.40 Distrib 4.0.12, for pc-linux on i686 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 4.0.12-standard-log Protocol version10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 1 day 5 hours 9 min 2 sec Threads: 4 Questions: 1005973 Slow queries: 22 Opens: 102 Flush tables: 1 Open tables: 78 Queries per second avg: 9.586 Thank u in advance === At 2003-11-03, 14:51:00 you wrote: === MaFai [EMAIL PROTECTED] wrote: Hello, mysql, The replication running smoothly between the master and slave,except that the heap table can not be synchronized. While the master insert the record into the heap table,the slave would do the same job. While the master delete the record in the heap table,the slave wouldn't do so. After serval days passed,the heap table would overloading in the slave,because it never delete the record in heap table. The mysql seems doesn't support heap table in replication,right? If yes,i should change the table type to innodb,although it would lose the performance. The manaul doesn't mention this. Any idea apprecated. Env:Mysql 4.0,Linux Red Hat7,256mb ram. What exactly version of MySQL do you use? I tested replication with HEAP tables and all worked like a charm. Could you provide a test case? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] = = = = = = = = = = = = = = = = = = = = Best regards. MaFai [EMAIL PROTECTED] 2003-11-04 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Slow Query
Hello, mysql, Research on the mysql status,we found the mysql take slow query.But we don't know when it happened. Does any command to trace the slow query(such as what sql statement,when it occur)? or any tools to monitor when it happened? In my loading test,when I run 120 multiconnection,it would take an slow query. But the online machine has the max connection limitation by default value 100,why it still take a slow query.The average query just 9.856,I can not believe that it would make the mysql slow query. Any idea appreciated. Best regards. MaFai [EMAIL PROTECTED] 2003-11-04 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Heap table in replication,bug?
Hello, mysql, The replication running smoothly between the master and slave,except that the heap table can not be synchronized. While the master insert the record into the heap table,the slave would do the same job. While the master delete the record in the heap table,the slave wouldn't do so. After serval days passed,the heap table would overloading in the slave,because it never delete the record in heap table. The mysql seems doesn't support heap table in replication,right? If yes,i should change the table type to innodb,although it would lose the performance. The manaul doesn't mention this. Any idea apprecated. Env:Mysql 4.0,Linux Red Hat7,256mb ram. Best regards. MaFai [EMAIL PROTECTED] 2003-11-03 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
If the mysql crash,how can I trace the log
Hello, mysql, Yesterday,mysql process disappear sliently. After I check the err-log,no clues found. But the PID file still exising.I think the mysql terminate by some reason.But I still not found why. The error-log mysqld.log wouldn't provide any valueable msg.It just contains the shutdown start up log. Any idea? Best regards. MaFai [EMAIL PROTECTED] 2003-08-27 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Checking the mysql status
Hello, mysql, I want to write a shell script to check the slave synchronize with master failed or sucessfully. Any idea to implement this? Best regards. MaFai [EMAIL PROTECTED] 2003-08-11 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
When mysql ruinned by Disaster
Hello, mysql, We have 1 master and salve in my lan. If the master failed,coze the unrecoverable disaster. At that time,I want to make the slave to be the master instead of the orginial one. Do you agree my following step? 0.Stop slave. 1.All java program operating database should redirect to the new master mysql which it acts as slave before the master failed.. 2.Copy the new master data to the third computer,and make the third mysql replicate the new master. 3.Start replicate But the ip address would change too.It seems hard to slove this,if I doesn't reset the dns. Any idea would be applicated. Best regards. MaFai [EMAIL PROTECTED] 2003-07-29 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication Problem
Hello, mysql, 1 master and 1 slave replicated successfully. After I change the master IP address. So the slave can not replicate the master data. Then I type change master to 'new ip'. Show slave status show that an error log found. How can I slove this? Log: mysql show slave status\G *** 1. row *** Master_Host: 192.168.80.18 Master_User: mafai Master_Port: 3306 Connect_retry: 60 Master_Log_File: paytv01-bin.012 Read_Master_Log_Pos: 37468899 Relay_Log_File: paytv02-relay-bin.001 Relay_Log_Pos: 6436 Relay_Master_Log_File: paytv01-bin.003 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_do_db: paytv Replicate_ignore_db: Last_errno: 1050 Last_error: error 'Table 'p_address_book' already exists' on query 'c reate table p_address_book( refno int not null, namevarchar(30), group_name varchar(20), phone int(8), email varchar(50) ) TYPE=INNODB' Skip_counter: 0 Exec_master_log_pos: 6258 Relay_log_space: 47656748 1 row in set (0.06 sec) Best regards. MaFai [EMAIL PROTECTED] 2003-07-28 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
about replication concept
Hello, mysql, I would also like to know the impact when:- - the master db fail - a lot of insert/update transactions in the master, would it replicate immediately to the 2nd database - when the secondary db fail, the master cannot send the data to the 2nd, would the data queue up ? - Any chance for the data in 2 db not sync, if so how to re-synchronize them ? Best regards. MaFai [EMAIL PROTECTED] 2003-07-04 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to drop unique key
Hello, mysql, p_asset CREATE TABLE `p_asset` (\ `ano` int(11) NOT NULL auto_increment,\ `asset_name` varchar(50) NOT NULL default '',\ `asset_type` int(11) default NULL,\ `asset_format` varchar(50) default NULL,\ `asset_keywords` varchar(200) default NULL,\ `asset_duration` bigint(20) default NULL,\ `asset_status` varchar(10) default NULL,\ `asset_channel` varchar(20) default NULL,\ PRIMARY KEY (`ano`),\ UNIQUE KEY `asset_name_2` (`asset_name`),\ KEY `p_asset_name_idx01` (`asset_name`)\ ) TYPE=InnoDB According to the manual,there is no 'drop unique key' in alter command I attempt to type 'alter table p_asset drop index asset_name',but it doesn't functional. alter table p_asset drop unique key( asset_name),obviously it wouldn't take effect. How can I drop the unique in p_asset table? Best regards. MaFai [EMAIL PROTECTED] 2003-06-30 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
about desc command
Hello, mysql, After create a table,it can use desc tablename to describe the table scheme. But it can not demonstrate that the column is unique or not.It just simplely show the column is MUL type. How can I get more information from the table by using mysql commend?(Except use mysqldump to read the sql file) or How can I type command to show the table structure? mysqltype some command mysqlcreate tabe //Show the specifing created table sql information .. ... Best regards. MaFai [EMAIL PROTECTED] 2003-06-25 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
datetime column dummy question
Hello, mysql, A table contains a column named mydate. //Wrong sql statement alter table p_asset add mydate datetime default now(); alter table p_asset add mydate datetime default time(); alter table p_asset add mydate datetime default now; alter table p_asset add mydate datetime default time; alter table p_asset add mydate datetime default date(); alter table p_asset add mydate datetime default datetime(); How can I add the default now value into the specified column? I try to find in the mysql manual,but in the default value charter,no relative information can be found. I also know this question is stupid,but hope you help. Best regards. MaFai [EMAIL PROTECTED] 2003-06-25 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
specified function mysql
Hello, mysql, I'm just wondering is there a function instead of update to overwrite data that has expired?? Or can mysql automatically deletes records or rows that has expires just like cookies delete themselves after expiring. Best regards. MaFai [EMAIL PROTECTED] 2003-06-23 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
about the MySql tools
Hello, mysql, Do any sql tools support MySql like Oracle (PL/SQL) Development Tools ? Oracle (PL/SQL) Development Tools is really imporve the efficient of development,but it doesn't support MySql. Do any other tools instead of it? Best regards. MaFai [EMAIL PROTECTED] 2003-06-18 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
about replication
Hello, mysql, 2 Mysql 4.0.12 windows version with INNODB replciate sucessfully,both in myisam and innodb table. According to mysql manual(PDF),there is only one master database in the same time,but with many slave server. Master for query,and slave for update or insert operation. Therefore,while programming with JAVA tech,I must write 2 connection pool? one for query,other for update? That's not very proper.Coze the programe switch the connection frequently.It really slow down the web application performance. How do you slove this problem?Any idea appreciate. Best regards. MaFai [EMAIL PROTECTED] 2003-06-12 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
