Re: mysql Ver 14.12 Distrib 5.0.27 user privileges question
you need CREATE Privileges. http://dev.mysql.com/doc/refman/5.0/en/grant.html#grant-privileges On Wed, Feb 27, 2013 at 10:42 PM, Rajeev Prasad rp.ne...@yahoo.com wrote: Hello, currently on this version of MySQL a database has been built for me to use. and following privileges are given: I am not able to create a table on my own. what privileges I need to create and modify tables in this database? mysql Ver 14.12 Distrib 5.0.27, for sun-solaris2.9 (sparc) using EditLine wrapper mysql show grants; +---+ | Grants for myuserid@% | +---+ | GRANT USAGE ON *.* TO 'myuserid'@'%' IDENTIFIED BY PASSWORD '*4EF5..6' | | GRANT SELECT, INSERT, UPDATE, DELETE ON `mydb`.* TO 'myuserid'@'%' | +---+ 2 rows in set (0.00 sec) mysql at % means I can do the operations from other hosts too? using ssh. thank you. -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: MySQL Connection Information
No, If you are using non-persistence connection once the query get complete you are closing the connection properly. On Wed, Nov 14, 2012 at 11:44 AM, Trimurthy trimur...@tulassi.com wrote: hi every one, i am working with some application which is developed in php and back end is mysql. in this application each and every page i am including config.php which consists queries to connect to the server. the user name and password is same all the time to connect to the database server. is it causes to an extra overload on the server to process the connection request every time with the same user name and password. Normal 0 false false false EN-US X-NONE AR-SA Thanks Kind Regards, TRIMURTHY -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: facing problem in MYSQL
Hi Ajay, there is no attachment.. On Sat, Jul 28, 2012 at 1:17 PM, Ajay Bansal, System Officer so...@indianjudiciary.gov.in wrote: Dear Sir/Mam, we are facing the problem in our server, kindly help us to solve our problem. mysqlbug script is attached with this mail. hope for your positive response ASAP. -- Regards* *Er. Ajay Bansal System Officer District Sessions Court, Amritsar +919988460582 | so...@indianjudiciary.gov.in -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: Is there any performance difference, maintaining separate ibdata files for each and every table insted of having one singl tabale for all databases.
there is performance issues with a larger number of datafiles than a single, that the reason innodb_file_per_table in not a default option. other, with innodb_file_per_table, you'll use more resources, there can be a problem if you have _many_ tables, there is obviously the problem to keep number of open files (open tables / table_cache / table_definition_cache) to manage. (Ofcourse, myisam tables always had that issue). Netx, with single file, table space can be shared between rows of different tables and schemas. This means less wasted tablespace. where, with innodb_file_per_table, each table may have unused tablspace, which can only be utilized by rows of the same table. This means (sometimes much) more wasted tablespace but still innodb_file_per_table is preferable, this lead to a disk space issue, since the ibdata1 file grew to 90G+ and it was not possible to defragment tablespace using the Alter method (Infact is is hardly possible to do in a production environment, since it leads to a huge downtime.) On Thu, Jun 14, 2012 at 12:19 PM, Rick James rja...@yahoo-inc.com wrote: There should be little or no difference. If you are using thousands of tables, you might encounter overhead in opening the .ibd files. If you are tight on disk space, a single ibdata1 might be more efficient at reusing free blocks. OTOH, if you shrink or drop a big table, the freed space is not returned to the OS if you have a singe ibdata1. In most cases, I recommend innodb_file_per_table=1. -Original Message- From: Pothanaboyina Trimurthy [mailto:skd.trimur...@gmail.com] Sent: Tuesday, May 15, 2012 4:58 AM To: mysql@lists.mysql.com Subject: Is there any performance difference, maintaining separate ibdata files for each and every table insted of having one singl tabale for all databases. hi every one Is there any performance difference, maintaining separate ibdata files for each and every table insted of having one singl tabale for all databases, for InnoDB Storage Engine. please let me know the difference. -- 3murthy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: i need advice on redundancy of mysql server.
Does really Master-Master replication provide load balancing feature? since, each node need to replicate to other node, and MySQL replication still a is single threaded replication , it mean there is only single replication thread sql_thread for DML queries. eg. There is two node with master master replication - Master -1 Master 2. *app1 --read/write* --- *Master 1 --* single thread*-- Master 2* --- *read/write -- app1* Its just a *high availability* not a load balancing*.* Thanks, On Wed, Jun 13, 2012 at 2:33 AM, Johan De Meersman vegiv...@tuxera.bewrote: - Original Message - From: Joey L mjh2...@gmail.com It sounds like you are all consultants. Hehe. I'm not :-p A lot are, though, because the combined technical knowledge on this list draws in consultants looking for stuff, and having experienced consultants on the list in turn heightens the combined technical knowledge again. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: Need help for performance tuning with Mysql
; ++-++--+---+--+ -+--+--+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-++--+---+--+ -+--+--+-+ | 1 | SIMPLE | thold_data | ALL | NULL | NULL | NULL | NULL | 6161 | Using where | ++-++--+---+--+ -+--+--+-+ 1 row in set (0.06 sec) If cache size tuning is not an option , do you think that following action would be an choice to faten the queries little bit more? 1. depriving the database and setup as an another process. (multiple mysql processes) 2. Move the MYD, MYI, frm to ram disk (/dev/shm) Thanks, Yu Rick James さんは書きました: 100% CPU -- A slow query. Tuning will not help. Period. 1. There are only a few things worth tuning -- see http://mysql.rjweb.org/doc.php/memory (they don't include the ones you tried) 2. Instead INDEXes and schema design must be studied. Please provide: SHOW CREATE TABLE SHOW TABLE SIZE EXPLAIN SELECT ... -Original Message- From: Yu Watanabe [mailto:yu.watan...@jp.fujitsu.com] Sent: Tuesday, May 22, 2012 7:07 PM To: mysql@lists.mysql.com Subject: Need help for performance tuning with Mysql Hello all. I would like to ask for advice with performance tuning with MySQL. Following are some data for my server. CPU: Xeon(TM) 2.8GHz (2CPUs - 8core total) Memory : 8GB OS : RHEL 4.4 x86_64 MySQL : MySQL 5.0.50sp1-enterprise Attached file # my.cnf.txt : my.cnf information # mysqlext_20120522131034.log : variable and status information from mysqladmin I have 2 database working with high load. I wanted to speed up my select and update queries not by optimizing the query itself but tuning the my.cnf. I have referred to following site, http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html and read Hiperformance Mysql vol.2 , and increased the following values, table_cache thread_cache_size tmp_table_size max_heap_table_size but made not much difference. According to the ps and sar result *1 PS result Date Time CPU% RSS VSZ 2012/5/22 21:00:39 109 294752 540028 *2 SAR Average CPU user 25% sys 5% io 3% I assume that MySQL can work more but currently not. I am considersing to off load 1 high load database to seperate process and make MySQL work in multiple process. It would be a great help if people in this forum can give us an adivice for the tuning. Best Regards, Yu Watanabe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: Architectural Help
Hi Anupam, You can achive this by circular replication, Circular replication between 4 nodes works fine. but not sure with 50 node server how practical it was. Each database node will be a data center, *Node 1 - data center 1* *-* *Node 2 - Data center 2* *-* *Node 3 - Data Center 3* *-* *Node 4 - Data Center 4* *- .. Node 50 - Data Center - 50*. so basicaly it will Multimaster Replication System; Fail over :- eg. data center 20 have network issue , in that case Node 19 replicate from Node 21, bypass node 20 -- need to do some scripting. http://onlamp.com/onlamp/2006/04/20/advanced-mysql-replication.html Thank You. On Thu, May 24, 2012 at 4:41 PM, Rick James rja...@yahoo-inc.com wrote: I deal with dozens of systems, all doing cross-country or cross-ocean replication. The only viable approach (that we have deployed in production) is * Dual-master, single writer -- That is, all 49 clients write to one machine * The two masters are geographically separate. * Failover involves switching to the 'other' master. It is _not_ fully automated. * Slaves, as needed, scattered around the world -- This provides read scaling. 1M writes per day -- that is an average of 12/sec. Yawn. Replication delay -- you probably will almost never see any. Network outages -- one of many things that can cause trouble. The sooner you write the data _and_ copy it to a _remote_ site, the sooner you are immune to floods, tornados, cyclones, power outages, motherboard death, etc. -Original Message- From: Anupam Karmarkar [mailto:sb_akarmar...@yahoo.com] Sent: Thursday, May 24, 2012 3:39 AM To: Nigel Wood Cc: mysql@lists.mysql.com Subject: Re: Architectural Help Dear Nigel, Thank for reply.. See my comments below --Anupam From: Nigel Wood nw...@plus.net To: Anupam Karmarkar sb_akarmar...@yahoo.com; mysql@lists.mysql.com mysql@lists.mysql.com Sent: Thursday, 24 May 2012 3:35 PM Subject: RE: Architectural Help A few questions: which is more or a problem: network outages, network capacity or query latency? Network Outages is problem, between data centres When you say near real-time do you need transactional consistent view on all 49 servers or can some lag be tolerated? Some lag can be tolerated considering network outage Can any one of the 49 local servers potentially update/delete the same rows or data? Yes central server can update same row or local server but not at same time, Few data will be updated in central location and others majorly in local.One Application Writes in Centeral and Others in local, Local app and central app shares some data. Is there any natural segmentation point within the data? No Do the data centers have diverse networks so that connections to some data centers may remain when others? Yes, we have diverse nework , so connectivity will be avaiable in other data centers. In the event that a local data centre is totally isolated from the others what data should it be allowed to update? Loca application should always write in local database, Central Application Updated will not be available to local. Do your applications produce/examine large data set querying by secondary keys or using tull text search? We dont have text search or we dont query large data Are you in a position to modify the applications? No, Micro changes ok __ From: Anupam Karmarkar [sb_akarmar...@yahoo.com] Sent: Thursday, May 24, 2012 10:17 AM To: mysql@lists.mysql.com Subject: Architectural Help Hi All, I need architectural help for our requirement, We have nearly 50 data centre through out different cities from these data center application connect to central database server currently, there are conectivity and nework flcutions issues for different data center, so we comeup with solution each data center we should have local database server which will keep syncing with other server so that application doesnt fail , User data can be updated in any of server and should reflect in every server. Application consists of write/read/delete operations, Current writes each day central server 1million. Only 1/1000 need to be distrubuted acrross servce rest need to be in central server. How can we achive this ? solution needs very much real time data accepting nework lags. Solution Collect all changes in other 49 server into 1 central server(How can we collect data) 49 keeps updating data into local database from central server(Using Repliation Can be done) --Anupam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Best Regards, Prabhat
Re: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
did you check permission of file /var/run/mysqld/mysqld.sock On Wed, Apr 11, 2012 at 9:48 AM, Larry Martell larry.mart...@gmail.comwrote: On Wed, Apr 11, 2012 at 2:51 AM, Ganesh Kumar bugcy...@gmail.com wrote: Hi Guys, I am using debian squeeze it's working good, I am trying to install mysql-server. mysql-server installation successfully but didn't start service root@devel:/var/run# more /etc/mysql/my.cnf |grep socket # Remember to edit /etc/mysql/debian.cnf when changing the socket location. socket = /var/run/mysqld/mysqld.sock socket = /var/run/mysqld/mysqld.sock socket = /var/run/mysqld/mysqld.sock root@devel:~# /etc/init.d/mysql restart Stopping MySQL database server: mysqld. Starting MySQL database server: mysqld . . . . . . . . . . . . . . failed! root@devel:~# mysql -u root -p Enter password: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) root@devel:~# cd /var/run/mysqld/ root@devel:/var/run/mysqld# ls root@devel:/var/run/mysqld# Is selinux enabled? If so, check the logs for that. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: Error in starting MySQL service on LINUX
may be following will solve: a. create file '/var/lib/mysql/mysql.sock' if not exist, i mean verify path. b. check permission of mysql dir, logfile and other files. /var/lib/mysql c. check the error log it will show you more details about error.. 2012/4/5 Peter Boros pe...@petya.org.hu Hi, What are the parameters you are trying to change? What does the error log say? Peter On Thu, 2012-04-05 at 14:40 +, Manivannan S. wrote: I am not facing the MySQL server problem during installation. I have installed those two rpm packages in my machine successfully and started working in mysql. I am trying to the change parameter value in my.cnf file to tune up the server for performance improvements. For that I stopped MySQL server and try to restart the MySQL service from that moment onwards I am getting this kind of errors like Starting MySQL.Manager of pid-file quit without updating file [FAILED] MySQL manager or server PID file could not be found! [FAILED] ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111) I can't go with any other installable packages by keeping the same package I have to solve this issue. Please give some ideas to resolve this problem. -Original Message- From: lists-mysql [mailto:replies-lists-b3z1-my...@listmail.innovate.net ] Sent: Thursday, April 05, 2012 5:28 PM To: Manivannan S. Subject: RE: Error in starting MySQL service on LINUX Original Message Date: Thursday, April 05, 2012 11:04:48 AM + From: Manivannan S. manivanna...@spanservices.com To: mysql@lists.mysql.com Subject: RE: Error in starting MySQL service on LINUX I am not using random binaries. I am using rpm packages only which are already tested and preconfigured. By installing rpm package also I am facing the same issue. I'm not certain where you got the packages you installed: MySQL-client-5.1.52-1.glibc23.x86_64.rpm MySQL-server-5.1.52-1.glibc23.x86_64.rpm but they are not from the rhel/centos repos. If you install packages from the appropriate repos it will take care of setting up the startup scripts, etc. for your environment. If you install packages/rpm files from some other location you'll need to do the configuration and setup yourself, as is your current situation. I would suggest removing the above and doing a yum install. The current packages in the rhel-6/centos-6 context are: mysql-5.1.61-1.el6_2.1.x86_64 mysql-server-5.1.61-1.el6_2.1.x86_64 If, for some reason, you want to stick with what you've installed then you'll need to work on doing the setup/configuration manually. - Richard DISCLAIMER: This email message and all attachments are confidential and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient, you are notified that any dissemination, distribution or copying of this email is strictly prohibited. If you have received this email in error, please notify us immediately by return email or to mailad...@spanservices.com and destroy the original message. Opinions, conclusions and other information in this message that do not relate to the official business of SPAN, shall be understood to be neither given nor endorsed by SPAN. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: parsing a .sql file
You can extract table names and all the fields use linux command *SED*. http://www.cyberciti.biz/faq/sed-display-text/ sometime I used sed command to extract single database from multiple databases dump file. using sed to extract single database from the dumpfile: = sed -n '/^-- Current Database: `dbname`/,/^-- Current Database: `/p' dumpfile dbname.sql 2error Thanks, On Sun, Nov 13, 2011 at 1:08 PM, Fahim Mohammad fahim...@gmail.com wrote: I have the following info in a text file called file.sql Is there a way to extract table name and all the fields in this table. Thanks. Fahim -- MySQL dump 10.11 -- -- Host: localhostDatabase: ailMel1 -- -- -- Server version 5.0.67 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `all_est` -- DROP TABLE IF EXISTS `all_est`; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; CREATE TABLE `all_est` ( `bin` smallint(5) unsigned NOT NULL, `matches` int(10) unsigned NOT NULL, `misMatches` int(10) unsigned NOT NULL, `repMatches` int(10) unsigned NOT NULL, `nCount` int(10) unsigned NOT NULL, `qNumInsert` int(10) unsigned NOT NULL, `qBaseInsert` int(10) unsigned NOT NULL, `tNumInsert` int(10) unsigned NOT NULL, `tBaseInsert` int(10) unsigned NOT NULL, `strand` char(2) NOT NULL, `qName` varchar(255) NOT NULL, `qSize` int(10) unsigned NOT NULL, `qStart` int(10) unsigned NOT NULL, `qEnd` int(10) unsigned NOT NULL, `tName` varchar(255) NOT NULL, `tSize` int(10) unsigned NOT NULL, `tStart` int(10) unsigned NOT NULL, `tEnd` int(10) unsigned NOT NULL, `blockCount` int(10) unsigned NOT NULL, `blockSizes` longblob NOT NULL, `qStarts` longblob NOT NULL, `tStarts` longblob NOT NULL, KEY `tName` (`tName`(8),`bin`), KEY `qName` (`qName`(12)) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; SET character_set_client = @saved_cs_client; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2011-04-18 4:14:26 -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: Relication Issues
Hi, It could be a hardware issue, as if you do not use RAID and you have bad sectors or the likes. But the most common cause could be a server crash. The INSERT or transaction was partially completed when server crash. When the slave is then restarted, it will try to execute the transaction/statement again *from the beginning*, and will fail since some of the rows were already added to the table. The situation occurs also if you are using MyISAM tables and a statement outside a transaction is interrupted. Thanks, On Thu, Nov 10, 2011 at 6:08 AM, Steven Staples sstap...@mnsi.net wrote: My Apologies, The tables are all MyISAM, most of the inserts/updates/deletes are done through stored procedures on the master. There are about 2 stored procedure calls per second, consisting of a whole bunch of queries, updates, and inserts within them. The latest issue(s) we're having, are: Could not execute Write_rows event on table xxx.x; Duplicate entry '20-1016792' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's mas Could not execute Write_rows event on table xxx.x; Duplicate entry '44870420' for key 'PRIMARY', Er Update_rows event on table xxx.x; Can't find record in 'x', Error_code: 1032; handler error HA_ERR_KEY (Where xxx is the databse, and x is the table) The replication broke yesterday morning for some reason, and the primary key index was at around 900,000. When we found out it was down, and ended up getting to work on it, we tried to restart it and the next ID it was inserting for was 1,016,792. So we're missing about 100,000 enteries, and they are not in the bin log (as far as I can see). Steve. -Original Message- From: Reindl Harald [mailto:h.rei...@thelounge.net] Sent: November 10, 2011 8:42 AM To: mysql@lists.mysql.com Subject: Re: Relication Issues replication is buggy sometimes but without any outputs desribing your problem nobody can really help you - memory tables as example are making much more troubles as myisam Am 10.11.2011 14:26, schrieb Steven Staples: Good morning list, I am having issues with my replication setup. it seems that every few weeks/months something happens, and I have to restart it, skip a row, or delete, rsync and restart replication from scratch. The databases that are being replicated, are rather large... there are about 12 new tables every year, each consisting of about 2-4g in size each (today's total database size is 83g). Again, things work fine for a while, and then there are issues. The backup/slave is on the same network/datacenter, so network speed isn't an issue. I just don't understand why inconsistencies keep arising. Is there a better way to do live backups, or have a hot space in the event of a catastrophe? Is there 3rd party software that would better achieve data integrity or something? Any help here would be appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=aim.prab...@gmail.com -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: Reusing ibdata1 space
I wonder, if there could be any method to regain InnoDB space other than dump the whole database and reimport. Thanks, On Thu, Nov 10, 2011 at 12:44 AM, Johan De Meersman vegiv...@tuxera.bewrote: - Original Message - From: Nick Khamis sym...@gmail.com I should mention that we have deleted the ib_* files in the past. I hope that was an accident, because if you thought that was a good idea I'm sending someone over with the spiked cluebat. Luckily for you, the solution to that particular problem (in the cases where it's indeed caused by the delete of the ib_data files) is more deletes, and you're good at those :-p The tables show up in the listing because in the mysqldata/database directory there are .frm (table descriptor) files created for tables of all engines, even though those files are really artifacts from the MyISAM legacy. Thus, the server scans it, lists it, notices it's an InnoDB table and then fails to find it in the InnoDB data dictionary because you deleted the one it was in. Simply delete the .frm file for such tables, and they'll no longer show up. As for file_per_table, it's generally a good idea to set that to 1, yes. Be aware that you may need to tune other MySQL and/or OS level settings, too, for example max_open_files. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=aim.prab...@gmail.com -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: InnoDB free - What does it really mean?
Hi, The comment is just telling you how much free space is in your InnoDB datafile(s). When that approaches 0, InnoDB will add the data file. Image that there's a box, say it Innodb tablespace, this box is consist of your data, and innodb free is the same as the (capacity of your box - usage) To increase the size simply add innodb data file. MySQL documentation: The data files (tables) that you define in an InnoDB form the tablespace of InnoDB. The tablespace consists of database pages with a default size of 16KB. The pages are grouped into extents of 64 consecutive pages (i.e. 1024Kb). InnoDB allocates space starting from the first data file (table). InnoDB can add a large segment up to 4 extents at a time to ensure good sequentiality of data (4096Kb). Thanks, On Thu, Nov 10, 2011 at 10:03 AM, Rozeboom, Kay [DAS] kay.rozeb...@iowa.gov wrote: In the show table status output, there is comment field labeled InnoDB free. Can someone explain what kind of free space is counted in this figure? Is it space that is not currently part of any segment? Does it include empty pages within segments? Does it include unused space within pages? Kay Rozeboom Information Technology Enterprise Iowa Department of Administrative Services Telephone: 515.281.6139 Fax: 515.281.6137 Email: kay.rozeb...@iowa.gov -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: mysqldump: Got error: 1017: Can't find file: './ssconsole/ss_requestmaster.frm' (errno: 24) when using LOCK TABLES
correct. mysqldump by default has --lock-tables enabled, which means it tries to lock all tables to be dumped before starting the dump. And doing LOCK TABLES t1, t2, ... for really big number of tables will inevitably exhaust all available file descriptors, as LOCK needs all tables to be opened. Workarounds: --skip-lock-tables will disable such a locking completely. Alternatively, --lock-all-tables will make mysqldump to use FLUSH TABLES WITH READ LOCK which locks all tables in all databases (without opening them). In this case mysqldump will automatically disable --lock-tables because it makes no sense when --lock-all-tables is used. or try with add --single_transaction to your mysqldump command On Fri, Sep 23, 2011 at 9:49 AM, Dan Nelson dnel...@allantgroup.com wrote: In the last episode (Sep 23), Shafi AHMED said: I have a mysql database of 200G size and the backup fails due to the foll. Issue. mysqldump: Got error: 1017: Can't find file: './ssconsole/ss_requestmaster.frm' (errno: 24) when using LOCK TABLES Can someone assist pls.? $ perror 24 OS error code 24: Too many open files You need to bump up the max files limit in your OS. It may be defaulting to a small number like 1024. If you can't change that limit, edit your my.cnf and lower the table_open_cache number. You'll lose performance though, since mysql will have to stop accessing some tables to open others. http://dev.mysql.com/doc/refman/5.5/en/not-enough-file-handles.html -- Dan Nelson dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=aim.prab...@gmail.com -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: [question]any performance tools about UPDATE
I don't think any other than show full processlist. In which state query is locked or not. I/O related things you check at OS level. On Thu, Sep 22, 2011 at 11:07 PM, jiangwen jiang jiangwen...@gmail.comwrote: Hi, Is there any performance toolds about UPDATE/INSERT querys? I want to monitor the UPDATE/INSERT performance, check out if there's any performance bottleneck, for example: slow INSERT/UPDATE more I/O where execute INSERT Regards Thanks J.W -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: Can I Develop using Community Edition and Deploy onto Enterprise Edition??
you don't need enterprise for the development environment, you can develop in community version and deploy in enterprise version. but make sure release version should be same for both. On Wed, Sep 21, 2011 at 7:06 AM, Alastair Armstrong alasta...@voxorion.co.za wrote: Hi Please I need some advice. We are in the process of upgrading from the Free Community Edition of MySQL on our Live environment to the Enterprise Edition. Do we need to do the same for my Development environment or can I continue developing on the Community Ed and then simply deploy any code, SQL script, etc to the Live Enterprise edition on our live server? All advice and pointers welcome. Thank you Regards Alastair Armstrong Development Manager Vox Orion (Pty) Ltd 19 Tambach Road Sunninghill Johannesburg Tel:+27 11 808 1000 Direct:+27 11 808 1208 Mobile: +27 83 323 1333 Fax: +27 86 556 8411 Email: alasta...@voxorion.co.za mailto:alasta...@voxorion.co.za Web: www.voxorion.co.za http://www.voxorion.co.za/ This e-mail is intended only for the person to whom it is addressed and may contain confidential information which may be legally privileged. If it has been sent to you in error ,this serves to advise you that you may not forward ,copy, print or use this e-mail or any attachments in any manner or form. You are also requested to please advise the sender immediately by e-mail or by telephone and then to delete this e-mail. Vox Orion accepts no liability for any loss, expense or damage arising from this e-mail and/or any attachments. -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Moving database from one machine to another machine..
Hi, I have 2 machine. Master and a slave replication. few days back I have switched slave machine (innodb_file_per_table) from single innodb file to one per file table. Now I want to do for Master. Now question, Is it recommendable this method, stop MYSQL services on both and copy mysql file's at system level (using scp or rync) form slave machine to master (after deleting ibdata1 and ib_log). update the variable innodb_file_per_table to switch master to one per file table. and start master.. or I can go with usual process.. export and import. And suggestion? Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: Caution : Hard Disk Full
Hi, I think I am too late.. if not for time being you can, move * '57G myisam_data'* dir to drive '*/dev/sde1 266G 184G 70G 73% /hdd5-1 *' and make the soft link with proper permission. Note activity must be done once database is shutdown. and secondly, normally innodb do not release the space once data get deleted. so its advise , to take backup and re-import the databses. steps : 1) Stop writes to the server 2) Dump the data 3) drop all databases (except mysql) 4) stop mysqld 5) remove the innodb data and log files 6) start mysqld (the innodb tablespace should be automatically created) 7) Check the error logs for anything strange 8) import the data Thanks, Prabhat On Thu, Aug 25, 2011 at 9:48 PM, Adarsh Sharma adarsh.sha...@orkash.comwrote: Dear all, Today by chance I am checking the space in mysql data directories. /dev/sdd5 274G 258G 2.6G 100% /hdd4-1 In the next 2 days i know my space becomes empty due to increase in data of myisam tables. [root@sd-1 hdd4-1]# du -sh * 33G innodb_data1 33G innodb_data2 33G innodb_data3 33G innodb_data4 33G innodb_data5 33G innodb_data6 8.1Ginnodb_data7 16K lost+found 57G myisam_data 4.0Ktemp [root@sd-1 hdd4-1]# When it becomes full, I am sure my server down. Can anyone Please let me know the steps I need to follow in this condition. Following are my directories : FilesystemSize Used Avail Use% Mounted on /dev/sda2 29G 12G 15G 45% / /dev/sda1 99M 11M 84M 11% /boot /dev/sda5 69G 35G 32G 52% /hdd1-1 /dev/sdb1 274G 225G 36G 87% /hdd2-1 /dev/sdc5 274G 225G 36G 87% /hdd3-1 /dev/sdd5 274G 258G 2.6G 100% /hdd4-1 /dev/sde1 266G 184G 70G 73% /hdd5-1 Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?** unsub=aim.prab...@gmail.comhttp://lists.mysql.com/mysql?unsub=aim.prab...@gmail.com -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: shall i jump from 5.1 to 5.5
correct. you have to understand the problem first. but still its recommendable to always use latest stable version. On Tue, Aug 16, 2011 at 10:02 AM, Reindl Harald h.rei...@thelounge.netwrote: Am 16.08.2011 17:59, schrieb Luis Daniel Lucio Quiroz: as far as my readings, they claim that 5.5 is the best my question is, shall i jump from 5.1 to 5.5. right now i have a performance problem, would 5.5 help me in that? Regards, LD why do you believe without any information you will get a useful answer? i have a performance problem is simply NO information if you even do not tell which storage engine and wich sort of problem in which context if you should update can nobody answer for you because we do not know if you have any crappy apps / scripts which would have troubles? we have upgraded some hundret webspaces and two dbmail-servers in februray becaus we know our self written applications and having test-environments, if you can do this can nobody say -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: error log rotation problem
this will help you http://adminlinux.blogspot.com/2009/09/mysql-log-file-rotation.html On Fri, Aug 12, 2011 at 9:43 AM, Paul DuBois paul.dub...@oracle.com wrote: On Aug 11, 2011, at 2:30 PM, Keith Murphy wrote: Hey everyone, I have run across something that has me stumped. I have some systems that have very large error logs because we haven't moved from statement-based to mixed-based replication yet so they get a lot of warnings logged. I need to rotate the error logs and have started looking at it doing so. The problem is that on one system a normal course of action works perfectly, but on anther it does not. And these systems were installed from the same RPM packages (5.1.50 -- downloaded from the MySQL website). Here is what I do: log in with mysql client and 'flush logs' OR mysqladmin --flush-log It should rename the old log file to mysqld.log-old and start a new mysqld.log file. On one system it works perfectly On the other...nothing. I tried moving the error log (mv /var/log/mysqld/mysqld.log /var/log/mysqld.log.old) and then issuing the flush logs command...it stays writing to the old file and never makes a new one. If I were to restart mysqld it would solve the problem but this is a production system and that isn't very practical. These systems are very similar. my.cnfs have been checked for differences. I searched the interwebs and specifically bugs.mysql.com for something similar. Not finding anything. I would appreciate any ideas! There was a change to log flushing that affects the error log in 5.1.51/ 5.5.7. It might be the cause of what you're seeing. http://dev.mysql.com/doc/refman/5.1/en/news-5-1-51.html http://dev.mysql.com/doc/refman/5.5/en/news-5-5-7.html Incompatible Change: Previously, if you flushed the logs using FLUSH LOGS or mysqladmin flush-logs andmysqld was writing the error log to a file (for example, if it was started with the --log-error option), it renamed the current log file with the suffix -old, then created a new empty log file. This had the problem that a second log-flushing operation thus caused the original error log file to be lost unless you saved it under a different name. For example, you could use the following commands to save the file: shell mysqladmin flush-logs shell mv host_name.err-old backup-directory To avoid the preceding file-loss problem, renaming no longer occurs. The server merely closes and reopens the log file. To rename the file, you can do so manually before flushing. Then flushing the logs reopens a new file with the original file name. For example, you can rename the file and create a new one using the following commands: shell mv host_name.err host_name.err-old shell mysqladmin flush-logs shell mv host_name.err-old backup-directory (Bug #29751) See also Bug #56821. -- Paul DuBois Oracle Corporation / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=aim.prab...@gmail.com -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: about the config file
correct, dash (“-”) and underscore (“_”) may be used interchangeably. http://dev.mysql.com/doc/refman/5.1/en/command-line-options.html On Tue, Aug 2, 2011 at 4:34 AM, Andrew Moore eroomy...@gmail.com wrote: Hey! I asked this one a long time ago. They are interchangeable in most cases. I can recall there was one or two options (pid-file and log-error...I think?!) that were funky with the '_' but I can't be sure that's still an issue. Try them out and don't worry too much about the aesthetics of the file. Andy On Tue, Aug 2, 2011 at 11:18 AM, Feng He short...@gmail.com wrote: Hello, In mysql's config file my.cnf, are the variable names with _ and - the same? for example, log_error = ... log-error = ... Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=eroomy...@gmail.com -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: Which is better
is optimizer depend on size of table? not sure On Mon, Aug 1, 2011 at 11:48 PM, Shafi AHMED shafi.ah...@sifycorp.comwrote: I hope the former better. Test with query plan output, though Best Rgs, Shafi AHMED -Original Message- From: Adarsh Sharma [mailto:adarsh.sha...@orkash.com] Sent: Tuesday, August 02, 2011 12:12 PM To: mysql@lists.mysql.com Subject: Which is better Dear all, Just want to know which join is better for querying data faster. I have 2 tables A ( 70 GB ) B ( 7 MB ) A has 10 columns B has 3 columns.Indexes exist on both tables's ids. select p.* from table A p, B q where p.id=q.id or select p.* from table B q , A p where q.id=p.id Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=shafi.ah...@sifycorp.com Get your world in your inbox! Mail, widgets, documents, spreadsheets, organizer and much more with your Sifymail WIYI id! Log on to http://www.sify.com ** DISCLAIMER ** Information contained and transmitted by this E-MAIL is proprietary to Sify Technologies Limited and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If this is a forwarded message, the content of this E-MAIL may not have been sent with the authority of the Company. If you are not the intended recipient, an agent of the intended recipient or a person responsible for delivering the information to the named recipient, you are notified that any use, distribution, transmission, printing, copying or dissemination of this information in any way or in any manner is strictly prohibited. If you have received this communication in error, please delete this mail notify us immediately at ad...@sifycorp.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=aim.prab...@gmail.com -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: How to view Query Execution time
you can also use EXPLAIN, which will give you much more details. http://dev.mysql.com/doc/refman/5.5/en/execution-plan-information.html http://www.techiequest.com/mysql-visual-explain-hierarchical-view-of-query-execution-plan/ On Sun, Jul 31, 2011 at 11:45 PM, Suresh Kuna sureshkumar...@gmail.comwrote: Usually, at the end of the query running it displays the time how much it took. Or else enable the profiling and run the query to check the exact time it took for execution at all levels. On Mon, Aug 1, 2011 at 12:11 PM, Adarsh Sharma adarsh.sha...@orkash.com wrote: Dear all, I want to know how much time did it take to run a sample query. In postgresql, we enable timing by \timing command. Is there is any way to enable in Mysql Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?** unsub=sureshkumar...@gmail.com http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com -- Thanks Suresh Kuna MySQL DBA -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: Next Unique Number - Generation
Assign each server a number and prefix/append that number to the unique ID. I will suggest you above, append -A for first machine and -B for second machine. -Prabhat On Thu, Jul 21, 2011 at 12:13 PM, Daevid Vincent dae...@daevid.com wrote: I can think of several ways to accomplish this (or close to it). * Assign each server a number and prefix/append that number to the unique ID. * initialize each table on each server at a different huge number so they don't ever collide: ALTER TABLE `students` AUTO_INCREMENT=1000; Other server: ALTER TABLE `students` AUTO_INCREMENT=2000; * don't re-invent the wheel and just use UUID() * make the primary key a combination key of a regular auto-increment and NOW() or RAND() or some other unique column of the table itself or something like IP number of the server, etc. * setup a trigger to alter the primary key accordingly prior to insert. Maybe one server is even numbers and the other is odd numbers, or use MOD() or other math to have a pool of servers. http://stackoverflow.com/questions/5416548/mysql-two-column-primary-key-with -auto-increment -Original Message- From: Vikram A [mailto:vikkiatb...@yahoo.in] Sent: Wednesday, July 20, 2011 11:18 PM To: MY SQL Mailing list Subject: Next Unique Number - Generation Hi there, I need a technical help fro you, I have developed a software for college school. Here we have concept called register number/admission number. These are two unique umber for each student. My application resides Client/server model. These numbers will be generated (some defined format) to each student when they are admitted at first time. This admission process is taken place at different nodes at a time. In this scenario, I am facing difficulty that, the same number is generated at time in two machines. (Logic is newest number will be displayed in the node before admission). How can I stop this logical issue? Can i have solution for this? Thank you in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=aim.prab...@gmail.com -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: Schema for Website Comments
http://www.ferdychristant.com/blog//archive/DOMM-7QJPM7 On Sun, Jul 10, 2011 at 10:14 PM, Adarsh Sharma adarsh.sha...@orkash.comwrote: Dear all, Today I need to create a schema for my application website that allows user comments too. I think we have to maintain hierarchical data and it is very common as all sites are supporting this feature. Can somebody suggest me some guidelines to follow and some links too. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?** unsub=aim.prab...@gmail.comhttp://lists.mysql.com/mysql?unsub=aim.prab...@gmail.com -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: Innodb as its default storage engine for Mysql 5.0 / 5.1
Yes, InnoDB is the default storage engine for MySQL as of MySQL 5.5.MyISAM and InnoDB has its own features. InnoDB probably the best RDBMS out there. InnoDB is default engine might be due nowadays most of the application required fully ACID-compliant modes, self recovery from a crash, and many more features... 2011/5/25 Halász Sándor h...@tbbs.net 2011/05/25 10:53 +0200, Reindl Harald if there is no good reason i will never enable innodb because MyISAM is enough for most web-apps And also MyISAM supports auto-increment in a lesser part of a primary key and InnoDB not--but although it is of interest, I have not tryed it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=aim.prab...@gmail.com -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: MySQL server has gone away
I had experience with such type of error, It was due lack of resources available to MySql, max connections exceeds on the server. you can write a simple script which will grab and store output of 'show processlist' every min. and later you cna investigate the issue. On Wed, May 25, 2011 at 3:34 AM, Aveek Misra ave...@yahoo-inc.com wrote: Nothing in the error log or the slow query log that suggests that the query size is too large or us taking too much time to execute. Thanks Aveek On May 25, 2011, at 3:53 PM, Rik Wasmus wrote: failed to execute SELECT * FROM cluster_info WHERE cluster = ?: MySQL server has gone away The error MySQL server has gone away is the error from the db handle. Can anyone give me any pointers on why that happens? I looked up the documentation in MySQL docs and the most common reason seems to be that it happens if the query size is very large or if there is a timeout. None of them seems to be a probable cause. The max_allowed_packet on the server is 16 MB and as can be seen in the query above, the query is very small and nowhere near the size limit. We also have a timeout setting (wait_timeout) of 10 minutes and the above query for us cannot possibly take that amount of time. In any case, given the same query, it executes correctly 99% of time (so to speak). It fails intermittently with the above error. What possibly could be the reason? I also looked at the max connections on the server at that time (around ~80) and it is much less than the limit we have (limit is 1000). How can I extract more information when this happens? This error message sucks since it does not tell me what exactly happened. The server version is 5.1.45. Can you access the error log of the server? That can probably shed more light on the issue... -- Rik Wasmus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=ave...@yahoo-inc.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=aim.prab...@gmail.com -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: Changing the default database location
The reason to move database onto the network is that multiple user can access it. Do you think there is a better alternative? shared storage device with MySQL does not work. Perheps it will corrupt datafile. Not sure what do mean by 'multiple user can access' ; Once mysql install you can create N no of users and let them access.. On Fri, May 20, 2011 at 1:06 PM, Johan De Meersman vegiv...@tuxera.bewrote: - Original Message - From: Reindl Harald h.rei...@thelounge.net so put the wgole mysqld and its data on a server in the network for this mysql was built and not for borking the dadadir somewhere else Hmm. The way I interpret what he's saying, is that he wants multiple instances accessing the same datafiles. If that's the case, Firdosh, don't - MySQL is not made for concurrent access to the same datafiles. If multiple people/applications need access to the same data, let them connect to the same server. If that's not an option, you're going to have to look at replication. I repeat, there is no setup possible where it is safe to have multiple instances of mysqld access the same datafiles. If you do, the first thing you'll notice is that one instance doesn't see the other instance's update. The second thing you'll notice, is that your data files will be FUBAR. Fucked Up Beyond Any Recognition. Not kidding, here. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=aim.prab...@gmail.com -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: Restore only one database or one table
might be my blog will help you :) http://adminlinux.blogspot.com/2009/11/extract-single-dbtable-from-dump-file.html have a good time.. On Thu, May 19, 2011 at 7:42 AM, Eric Bergen eric.ber...@gmail.com wrote: The mysql command line has the -o option to only execute queries for the default database. This can be used to restore one database from a dump file that contains many. For specific tables you can setup a restore user that only has permissions on the tables you want to restore then use the -f flag to continue on error. Only use this in emergencies though. On Thursday, May 19, 2011, Adarsh Sharma adarsh.sha...@orkash.com wrote: Johan De Meersman wrote: - Original Message - From: Suresh Kuna sureshkumar...@gmail.com Try to take a tab separated dump, so you can restore what ever you want in terms of tables or databases. Uhh. I'm a bit fuzzy today, but I really don't see how a tab-separated dump will help split off tables or databases :-) To answer the original question, though; the technically accurate answer is yes, you can. It's made easy because mysqldump conveniently dumps database-per database and table-per table. It's a bugger to do, however, because if you take a monolithic dump you need to open the whole dumpfile in a text editor and copy the data you want to another file or straight to the MySQL commandline. Good luck with your 250G backup :-) You can use sed or awk to look for markers and split the file up that way. You'd be much better off in the future to dump database-per-database, and if you think you need it table-per-table. 's Not all that hard, just script to loop over the output of show databases and show tables. Probably plenty of scripts on the internet that do exactly that. Compressing your dumps is a good idea, too - the output is a text file, so bzip2 will probably compress that a factor 10 or better. Simply use bzcat to pipe the file back into the MySQL client to restore. That's pretty nice What I am expected to hear. I will let u know after some practical implementation. Thanks best Regards, Adarsh Sharma -- Eric Bergen eric.ber...@gmail.com http://www.ebergen.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=aim.prab...@gmail.com -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: Mysql server full with idle connections
Hi, to avoid this change ur wait_timeout value to 300 Secs or less, along with this you can also write a script to kill those process (mysql process) which are in sleep mode for more than certain time.. hope this will helpful.. On Wed, Nov 24, 2010 at 6:41 PM, Johan De Meersman vegiv...@tuxera.bewrote: On Wed, Nov 24, 2010 at 1:13 PM, Nigel Wood nw...@plus.net wrote: Quick thought: what is your idle timeout set to on the MySQL server? Could you have configured it to reap these idle connections? I could, probably, but the applications are generally well-behaved, and it's not a recurring problem. I hope. I'm more interested in the cause, atm. On Wed, Nov 24, 2010 at 1:58 PM, John Daisley daisleyj...@googlemail.com wrote: Seen this a lot with poorly written web apps which open connections but dont close them when finished. Try setting wait_timeout and/or interactive_timeout to close unused connections. Well, yes, but as far as we're aware nothing new has been deployed - this setup is several years old. I suppose it's possible that one of those kind of bugs is hiding somewhere in a forgotten corner of code, but given that we're running Drupal and Wordpress, I'd be surprised at something like that remaining unnoticed for so long. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: mysql's system variables
Here you get all details: http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html All the Dynamic variable can be change on fly eg. wait_timeouthttp://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_wait_timeout . On Sat, Oct 23, 2010 at 6:07 PM, wroxdb wro...@gmail.com wrote: Hello, what system variables of mysqld can be changed on fly? thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=aim.prab...@gmail.com -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: MySQL Server has gone away
there is high chance of corruption of any data files, but it will clear only after looking of your error file (generated by mysql) can you post the 50 last lines of your mysql error file. On Thu, Aug 19, 2010 at 4:35 PM, Krishna Chandra Prajapati prajapat...@gmail.com wrote: Hi Jitendra, Check your error log file. Some thing might have gone wrong. Krishna On Wed, Aug 18, 2010 at 9:41 PM, jitendra ranjan jitendra_ran...@yahoo.comwrote: Hi, Whenever i run any commnd on mysql it gives message as below then gives the result successfully. What is the reason of the below error message : ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id:264550 Current database: *** NONE *** Thanks in advance -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: info on mysql
check this, http://adminlinux.blogspot.com/2009/12/mysql-tips-calculate-database-and-table.html Thx On Wed, Aug 11, 2010 at 5:23 PM, PRATIKSHA JAISWAL pratikshadjayswa...@gmail.com wrote: Hi, Thanks all for your help. ---Database Table wise Size in MB--- SELECT TABLE_SCHEMA AS 'Database', TABLE_NAME AS 'Table', CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024),2), Mb) AS Size FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = dbname; ---Database Table wise Size in GB--- SELECT TABLE_SCHEMA AS 'Database', TABLE_NAME AS 'Table', CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024 / 1024),3), Gb) AS Size FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = dbname; Result shows the EMPTY SET. On Tue, Aug 10, 2010 at 10:20 AM, Anirudh Sundar sundar.anir...@gmail.comwrote: Hello Pratiksha, To get the uptime of the MYSQL instance :- mysql\s as given above just give the above command \s For Total number of users connected to server :- mysqlshow global status like '%user%'; ---Database Table wise Size in MB--- SELECT TABLE_SCHEMA AS 'Database', TABLE_NAME AS 'Table', CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024),2), Mb) AS Size FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = dbname; ---Database Table wise Size in GB--- SELECT TABLE_SCHEMA AS 'Database', TABLE_NAME AS 'Table', CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024 / 1024),3), Gb) AS Size FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = dbname; Data size, index size no. of tables, engine type* SELECT s.schema_name,t.engine, CONCAT(IFNULL(ROUND(SUM(t.data_length)/1024/1024,2),0.00),Mb) as Data_size, CONCAT(IFNULL(ROUND(SUM(t.index_length)/1024/1024,2),0.00),Mb) as Index_size,COUNT(table_name) total_tables FROM INFORMATION_SCHEMA.SCHEMATA s LEFT JOIN INFORMATION_SCHEMA.TABLES t ON s.schema_name = t.table_schema WHERE s.schema_name not in(mysql,information_schema,test) GROUP BY s.schema_name,t.engine order by Data_size DESC; For top slow queries :- mysqldumpslow -s c -a -t 5 mysqlslow.log top10_slow-count_envr.log For Engine Info of a table and other details :- Show table status like 'tablename'; Cheers, Anirudh Sundar On Mon, Aug 9, 2010 at 9:31 PM, PRATIKSHA JAISWAL pratikshadjayswa...@gmail.com wrote: Hi All, How can i get following information from database or is there any query for the same. (1) mysql server uptime (2) Total number of users connected to server (3) Data file information / where it is located through mysql prompt / size of data file (4) each Database size (5) Database I/O information (6) Invalid object in database (7) Database performance statistics queries (8) Top 5 queries taking more time for executions. (9) Engine information. -- Thanks Pratiksha -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: info on mysql
use Google :) On Wed, Aug 11, 2010 at 10:02 PM, PRATIKSHA JAISWAL pratikshadjayswa...@gmail.com wrote: Hi Prabhat, Thanks a ton for sharing your blogs with us. I am learning mysql database. I am very good at PostgreSQL. Do you have any other material where in i can go for Mysql Certification and mainly mysql replication set up material etc.. -- Thanks Pratiksha On Wed, Aug 11, 2010 at 5:39 PM, Prabhat Kumar aim.prab...@gmail.comwrote: check this, http://adminlinux.blogspot.com/2009/12/mysql-tips-calculate-database-and-table.html Thx On Wed, Aug 11, 2010 at 5:23 PM, PRATIKSHA JAISWAL pratikshadjayswa...@gmail.com wrote: Hi, Thanks all for your help. ---Database Table wise Size in MB--- SELECT TABLE_SCHEMA AS 'Database', TABLE_NAME AS 'Table', CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024),2), Mb) AS Size FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = dbname; ---Database Table wise Size in GB--- SELECT TABLE_SCHEMA AS 'Database', TABLE_NAME AS 'Table', CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024 / 1024),3), Gb) AS Size FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = dbname; Result shows the EMPTY SET. On Tue, Aug 10, 2010 at 10:20 AM, Anirudh Sundar sundar.anir...@gmail.comwrote: Hello Pratiksha, To get the uptime of the MYSQL instance :- mysql\s as given above just give the above command \s For Total number of users connected to server :- mysqlshow global status like '%user%'; ---Database Table wise Size in MB--- SELECT TABLE_SCHEMA AS 'Database', TABLE_NAME AS 'Table', CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024),2), Mb) AS Size FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = dbname; ---Database Table wise Size in GB--- SELECT TABLE_SCHEMA AS 'Database', TABLE_NAME AS 'Table', CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024 / 1024),3), Gb) AS Size FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = dbname; Data size, index size no. of tables, engine type* SELECT s.schema_name,t.engine, CONCAT(IFNULL(ROUND(SUM(t.data_length)/1024/1024,2),0.00),Mb) as Data_size, CONCAT(IFNULL(ROUND(SUM(t.index_length)/1024/1024,2),0.00),Mb) as Index_size,COUNT(table_name) total_tables FROM INFORMATION_SCHEMA.SCHEMATA s LEFT JOIN INFORMATION_SCHEMA.TABLES t ON s.schema_name = t.table_schema WHERE s.schema_name not in(mysql,information_schema,test) GROUP BY s.schema_name,t.engine order by Data_size DESC; For top slow queries :- mysqldumpslow -s c -a -t 5 mysqlslow.log top10_slow-count_envr.log For Engine Info of a table and other details :- Show table status like 'tablename'; Cheers, Anirudh Sundar On Mon, Aug 9, 2010 at 9:31 PM, PRATIKSHA JAISWAL pratikshadjayswa...@gmail.com wrote: Hi All, How can i get following information from database or is there any query for the same. (1) mysql server uptime (2) Total number of users connected to server (3) Data file information / where it is located through mysql prompt / size of data file (4) each Database size (5) Database I/O information (6) Invalid object in database (7) Database performance statistics queries (8) Top 5 queries taking more time for executions. (9) Engine information. -- Thanks Pratiksha -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: Moving from one MySQL server to three MySQL servers?
1. Setup a single master and 2 slaves. The question is how to tell the web servers to get all the read data from the slaves and to only write to the master? Replication is not an answer to all performance problems. Although updates on the slave are more optimized than if you ran the updates normally, if you use MyISAM tables, table-locking will still occur, and databases under high-load could still struggle. Replication is not a guarantee that the slave will be in sync with the master at any one point in time. Even assuming the connection is always up, a busy slave may not yet have caught up with the master, so you can't simply interchange SELECT queries across master and slave servers. On Thu, Aug 5, 2010 at 2:11 AM, Wm Mussatto mussa...@csz.com wrote: On Wed, August 4, 2010 11:40, Nunzio Daveri wrote: Hello Gurus :-)� I was running a simple load generator against our 16GB Dual Quad core server and it pretty much came down to it's knees within two hours of running tests.� The customer DOES NOT WANT to change any code, they just want to throw hardware at it since it took them a year to create all of the code.� It is a 140GB database with 21GB of indexs all using InnoDB - currently doing 70% reads and 30% writes. My question is what is the best way of distributing the load without changing any of the php / perl code that their web server uses?� This is what I am thinking but need someone to tell me it is a good idea or bad please? 1. Setup a single master and 2 slaves.� The question is how to tell the web servers to get all the read data from the slaves and to only write to the master? 2. Install a MySQL proxy box and let mysql proxy handle the load, problem is now it is the SPOF! 3. Use DNS round robin, BUT how to tell round robin to ONLY go to master for writes and ONLY use one of the 2 slaves for reads? As was mentioned, what the test was would help. Are you using single file or separate file per table. If this is a web application, separate the database onto another server, move to separate files per table and put the files on separate spindles. Of course, the real next step is to find where the actual bottle neck is. Do you have slow query log enables etc.? What are the results. How critical is the consistency between read and writes. There will be a lag between the master and slave which may or may not be critical. -- William R. Mussatto Systems Engineer http://www.csz.com 909-920-9154 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=aim.prab...@gmail.com -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: migration via replication for large DB?
Another suggestion would be :- Take a FULL tar of the MYSQL Data Directory and push it to the NEW server and untar and start mysql (take the master status of the probable Master Server, for replication and bringing the new server to sync with its Master). I think this should be one of the quickest way to do it. You can optimize this by : copy MYSQL Data Directory from old server to new server (*without stopping MySQL Server on source, but make sure on target MySQL service is not running * ) Then during the maintenance window , Rsync the MYSQL Data Directory (*Make sure MySQL services is stopped in source and target server*). This will check and copy only changed data from source to target. Thanks, On Mon, Jul 26, 2010 at 12:43 PM, Anirudh Sundar sundar.anir...@gmail.comwrote: Hello Goeff, Data Import might take some time (considering 50 GB) if the majority of the tables are of type INNODB. If yes, 4 hours should not be enough. If its MYISAM, you can go ahead (Provided you choose data import to replication). Another suggestion would be :- Take a FULL tar of the MYSQL Data Directory and push it to the NEW server and untar and start mysql (take the master status of the probable Master Server, for replication and bringing the new server to sync with its Master). I think this should be one of the quickest way to do it. Please let me know how it goes if you decide to do it this way. Cheers, Anirudh Sundar On Mon, Jul 26, 2010 at 9:56 AM, Rob Wultsch wult...@gmail.com wrote: On Sun, Jul 25, 2010 at 12:55 PM, Geoff Galitz ge...@galitz.org wrote: Hello. I need to migrate a master and slave to new hardware. The DB is approx 50G on disk and my time window for downtime is approximately 4 hours. My question is, is it advisable to do a mysqldump from the old master and then load on the new master and slave, or is it faster to just set the new master up as a slave, and when it catches up to the old master I flip the switch? The catch is that the new hardware is in a different datacenter. In other words, which is faster: dump and load or replication over the Internet? Thanks for your time. There may be significantly better options available to you. What version are you coming from and what version are you to? What engines do you use (Innodb, MyISAM,etc)? Is the data directory currently mounted on a lvm volume? -- Rob Wultsch wult...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=sundar.anir...@gmail.com -- Best Regards, Prabhat Kumar MySQL DBA Mobile : 91-9987681929 My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: Myisam advantages
for a true VARCHAR type; a VARCHAR column starts with a length stored in one or two bytes. · Tables with VARCHAR columns may have fixed or dynamic row length. · The sum of the lengths of the VARCHAR and CHAR columns in a table may be up to 64KB. · A hashed computed index can be used for UNIQUE. This allows you to have UNIQUE on any combination of columns in a table. (However, you cannot search on a UNIQUE computed index.) On Fri, Jul 16, 2010 at 11:16 AM, P.R.Karthik prk...@gmail.com wrote: Hi, I am newbie to mysql can i know the advantages of myisam storage engine and some of its special features. -- Regards Karthik.P.R kart...@mafiree.com -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: How to define the initial space for a table
I am no sure you are taking about cluster table space or innodb table space. Just check : http://dev.mysql.com/doc/refman/5.1/en/create-tablespace.html http://dev.mysql.com/doc/refman/5.0/en/innodb-configuration.html On Wed, Jul 14, 2010 at 2:52 PM, alba.albetti alba.albe...@libero.itwrote: Is it possible in MySQL to define the initial space that the table is going to use and the space for its growth? I usually work on Oracle where the syntax is: create table MYTABLE (...fields...) tablespace MYTABLESPACE (initial 2M next 1M); that means Oracle reserves a data block of 2Mb for the table and where and whether the table will grow, it will allocate more block of 1Mb in the tablespace. How does MySQL behave about that? By the way is there any Manual where it's explained how MySQL manage tablespaces, datafiles, ...? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=aim.prab...@gmail.com -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: How to become a DBA on MySQL
there is another suggestion. if you have good/unlimited net connection. Just explore youtube for MySQL videos.. I remember in my beginning days it helped me a lot. On Tue, Jul 13, 2010 at 11:56 PM, Shawn Green (MySQL) shawn.l.gr...@oracle.com wrote: On 7/13/2010 9:24 AM, alba.albetti wrote: Hi, I'd like to know which are the main tasks for a DBA, so in addition of the on-line tutorial of MySQL, is there anyone telling me whether there exists some tutorial (better if in PDF) where it's possible to undestand and learn all you need for managing the MySQL DB as DBA? I mean I'd like to read something explaining what are datafiles, tablespaces, ... and what usually a DBA is asked to do (creating databases, starting up and shutting down the database, managing the database's storage structures, making database backups and performing recovery, monitoring and tuning performance, ...) In other words I'd like to read how to manage a DB as DBA, because I've always worked only as developer and not as DBA. Thanks! You probably already have the basic skills you need. You just need to shift your approach to the problem. While most developers are more worried about putting data into a database and finding it when they need it, the DBA has a different philosophy. The DBA is the one entrusted to keep that data safe, to protect the data from the bad habits of inexperienced programmers, and to ensure that the whole system (not just the parts the users need to touch) remain in good working order. It's the same difference between borrowing a car to drive it and caring for a car you own while allowing others to drive it from time to time. Here is a very short and incomplete list of the big things you should be able to do as a DBA: 1) Understand the backup and restore processes. Be able to perform them reliably. 2) Understand how the data is stored on the system resources. Be prepared to relocate data to a different resource. 3) Understand how MySQL permissions work. Create and change accounts as necessary. 4) Understand how to read the MySQL performance and diagnostic reports. When something is not working well, these are usually the first place to look for clues. At the minimum learn how to use and understand the EXPLAIN results; the SHOW reports; and how to activate, deactivate, and interpret the InnoDB monitors. 5) Be able to read and understand the Error log. 6) Learn the advantages and disadvantages of each storage engine. 7) Learn at least the basic skills about how to navigate within and maintain the machine that is hosting your MySQL instance. 8) Always keep learning - I cannot stress this on point enough. You must continue learning to stay ahead of your developers. They can and will find new and exciting ways to make your MySQL servers run like they are frozen in mud. Use your development experience to find better ways to write their queries and to design their tables. Also, some problems may be due to bugs. By staying current with the state of the MySQL ecosystem, you will make yourself aware of problems that you may not have needed to encounter on your own. The links to the books and other resources that you have been given (see other responses in this thread) are excellent places to learn these, and all of the other, skills that you will need to use to become a good DBA. How good you become is all in the attitude you take. If you are hands-off and neglectful of your database server, then all of the databases hosted there may become poorly organized resource hogs. If you, as a DBA, are diligent, protectful, proactive, and concerned then you will have a much better chance of achieving amazing performance out of your servers. Regards, -- Shawn Green MySQL Principle Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=aim.prab...@gmail.com -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: Allowing Connections From Remote Clients
GRANT ALL PRIVILEGES ON **.** TO username@'tuna.iamghost.com' IDENTIFIED BY PASSWORD 'password'; **.** ie for all databases , if want on particular DB GRANT ALL PRIVILEGES ON *MyDATABASE.** TO username@'tuna.iamghost.com' IDENTIFIED BY PASSWORD 'password';; On Wed, Jul 14, 2010 at 12:44 AM, Carlos Mennens carlosw...@gmail.comwrote: I am trying to connect to MySQL server [192.168.0.100] from my PC [10.1.10.222] using MySQL Workbench and when I test the connection, I get the following error: Host 'tuna.iamghost.com' is not allowed to connect to this MySQL server. Can someone please tell me what I need to do so I can allow remote connections to the database from the MySQL workbench application? I have access to MySQL via CLI. Thank you! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=aim.prab...@gmail.com -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: finding exact query being run
In my.cnf, check wait_timeout value , default is 4800. Make it to wait_timeout = 60 Thus, the connections will automatically be closed after waiting for 60 seconds. On Mon, Jul 12, 2010 at 11:51 PM, John Daisley daisleyj...@googlemail.comwrote: If you have lots of sleeping processes the chances are you have a poorly written app that is not closing its database connections. Those sleeping processes are just connections waiting for another command from the application or whatever else initiated them. The sleeping processes will not show up in the slow query log because they are not slow queries and you cannot see the sql they are running because they aren't running any. If you need to prevent these building up (and you do because they will eat away at system resources) you can add a wait_timeout option to your my.cnf / my.ini file but the best way to control these is ensure your app closes its connections when it is finished with them and do not use persistent connections unless absolutely necessary Regards John John Daisley SQL Server 2005/2008 Database Administrator Certified MySQL 5 Database Administrator Certified MySQL 5 Developer Cognos BI Developer Telephone: +44 (0)7918 621621 Email: john.dais...@butterflysystems.co.uk - On 12 July 2010 16:39, Machiel Richards machi...@rdc.co.za wrote: Hi All I am trying to find out how to see the exact query being run. When running show processlit, I get a lot of processes that have been running for a VERY long time. I a trying to find out exactly what query it is that is being run , any ideas? I tried the slow query log but it is not showing up in there. All the queries are showing “sleep” Not sure if this means anything Machiel Richards MySQL DBA Relational Database Consulting [image: RDC_Logo] -- John Daisley Certified MySQL 5 Database Administrator Certified MySQL 5 Developer Cognos BI Developer Telephone: +44 (0)7918 621621 Email: john.dais...@butterflysystems.co.uk -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: Innodb Choosing Random Index
You you send us explain of that query. On Sun, Jul 11, 2010 at 6:31 PM, arijit bhattacharyya new2mys...@gmail.comwrote: optimize / analyze table in each of the hosts is not a good option for me , this is an in-production set-up with minimal number of boxes in rotation . On Sun, Jul 11, 2010 at 5:57 PM, arijit bhattacharyya new2mys...@gmail.comwrote: Hi , i am running into trouble due to wrong index chosen by mysql in some particular type of queries . This is happening in a critical production environment where we have deployment in two different colocations . I am seeing that a paticular query is using one index in one set of hosts another index in another set of hosts . We are not using 'use index' clause to explicitly mention the index due to some limitations . But wondering based on what mysql is using different indexes in different hosts . This is really surprising , since dataset table structures are exactly same in all the hosts . Other than changing the code to force using 'use index' , is there any other way to resolve it ? And what's the exact reason behind this ? Just to have mysql choose the correct index always will simply solve my problem . Thanks . -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: Got error 139 from storage engine (InnoDB)
I think this is related to foreign key constraint data types don't match up... so verify your CREATE TABLE structure or post the CREATE TABLE statement for your table. Or there is chance of data type size and the value you are inserting.. such type of problem occurs if the data import ie source and restore ie target are of different versions . Thanks, On Mon, Jul 5, 2010 at 9:36 PM, Rob Wultsch wult...@gmail.com wrote: On Sun, Jul 4, 2010 at 8:35 PM, James Corteciano ja...@linux-source.org wrote: Hi All, I have received error message ERROR 1030 (HY000) at line 167: Got error 139 from storage engine when importing dump database to MySQL server. The MySQL server is using InnoDB. I have google it and it's something problem on exceeding a row-length limit in the InnoDB table. Any have idea how to fix this? Thanks. Regards, James I can not recall having seen that error before. I did a slight amount of googling and it sounds like it might be a innodb tuning issue. Please post: 1. Any relevant entries in your error log file. 2. Your my.cnf. 3. You servers specs and whether the server also runs other daemons. -- Rob Wultsch wult...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=aim.prab...@gmail.com -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: phpMyAdmin and other management tools
You can also explore : http://kontrollsoft.com/software-kontrollbase On Thu, Jul 1, 2010 at 5:47 PM, Nilnandan Joshi nilnand...@synechron.comwrote: http://mysqlonlinehelp.wordpress.com/2010/06/30/tools-for-mysql-database/ regards, Nilnandan Noel Butler wrote: On Thu, 2010-07-01 at 07:38 -0400, David Stoltz wrote: Hi Folks, I'm currently using phpMyAdmin to manage the mySQL databases. I'm wondering what most people like to use? I know there is mySQL Workbench, which I haven't really fooled with yet command line (mysql prompt etc) and phpmyadmin. command line perfect for quick instructions phpmyadmin for its ease with larger DB structures, also, most users wont have command access and its simple for them, it also doesn't require you to install anything on your machine, I've tried a few in the past, but none of them match up to phpmyadmin, and Marc (its author) is always very approachable. Cheers -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: Table creation fail
Correct, a primary key is used to uniquely identify each row in a table. It can either be part of the actual record itself , or it can be an artificial field (one that has nothing to do with the actual record). A primary key can consist of one or more fields on a table. When multiple fields are used as a primary key, they are called a composite key. A Primary key constraint can be defined at various levels: * Primary key constraint defined at column level Syntax: Column Name datatype(size) Primary Key * Primary key constraint defined at table level Syntax: Primary key (Column Name, Column Name) --Prabhat On Thu, Jun 24, 2010 at 3:03 PM, Joerg Bruehe joerg.bru...@sun.com wrote: Hi David, all! David Stoltz wrote: Actually, That table isn't supposed to have a PK, so I removed that, and it works...same effect you suggested. Even if you currently don't need a primary key in that table, IMO you should still define one. Use some 'id_testresult' column with an autoincrement clause, so you need not provide a value. Sooner or later you may (I really think: will) feel the need to uniquely identify a row, especially to delete it, and a primary key will be very helpful then. Your original problem was most likely not due to mentioning primary key but rather to not providing a column name for it. Regards, Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com Sun Microsystems GmbH, Komturstrasse 18a, D-12099 Berlin Geschaeftsfuehrer: Juergen Kunz Amtsgericht Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=aim.prab...@gmail.com -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: Table creation fail
Yes, but few exceptions; Column level constraints are applicable to that column only, whereas table level constraints are used to define composite keys like primary key for the combination of two or more columns in a table. column level constraints contain all types of constraints (like, not null,primary key,foreign key,unique).but table level except not null constraint its supports all constraints. --Prabhat On Fri, Jun 25, 2010 at 2:45 PM, Johan De Meersman vegiv...@tuxera.bewrote: A Primary key constraint can be defined at various levels: * Primary key constraint defined at column level Syntax: Column Name datatype(size) Primary Key * Primary key constraint defined at table level Syntax: Primary key (Column Name, Column Name) Aren't those two alternate syntaxes for the exact same thing ? -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: Altering database size to add more space
There is 2 way to check databases size : A. OS level, you can do *#du -hs *of data dir , it will show current usages of you database size at File system level. B. You can also check on Database level check details herehttp://adminlinux.blogspot.com/2009/12/mysql-tips-calculate-database-and-table.html How can I increase the size of a database? Its depend upon the how have you configured my.cnf and the type of engine you are using. In case MyISAM it will grow up to space on your data drive or the Max size of file limited by OS.. and In case of Innodb it will also grow up to space on you drive same as for MyISAM, since its default configuration is *autoextend* innodb_data_file_path=ibdata1:50M;ibdata2:50M:*autoextend* Check details herehttp://dev.mysql.com/doc/refman/5.0/en/innodb-configuration.html --Prabhat On Thu, Jun 24, 2010 at 9:00 PM, Jim Lyons jlyons4...@gmail.com wrote: What do you mean time to increase? What tells you that? A database's size is determined by the amount of available diskspace. If you need more than the filesystem that it is currently on has, then you can either move the entire schema (which is synonymous to database) to another filesystem and symlink it. You can also store individual tables and table files on other file systems and symlink those. Either way, you have the total collection of disk space available to you. This assumes a Linux OS. If your innodb tables are being restricted, you need to see if you have a max size defined for the table space and if that is what you're bumping into. On Thu, Jun 24, 2010 at 9:13 AM, Sarkis Karayan skara...@gmail.com wrote: I feel like I am missing something, because I am not able to find the answer to this simple question. How can I increase the size of a database? I am using the following query to check the available space and notice that it is time to increase. SELECT table_schema AS 'Db Name', Round( Sum( data_length + index_length ) / 1024 / 1024, 3) AS 'Db Size (MB)', Round( Sum( data_free ) / 1024 / 1024, 3 ) AS 'Free Space (MB)' FROM information_schema.tables GROUP BY table_schema ; Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=jlyons4...@gmail.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: Table Consistency/analize/check/repair
might be this will help you. http://adminlinux.blogspot.com/2010/05/mysql-checking-and-reparing-tables.html On Fri, Jun 18, 2010 at 6:59 PM, a.sm...@ukgrid.net wrote: this built in command is probably a good start: http://dev.mysql.com/doc/refman/5.0/en/mysqlcheck.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=aim.prab...@gmail.com -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: do i have to restart the mysql server when i change some global variables?
Yes and No. Its depend on the type of variable you have changed. If its Dynamic , MySQL restart not required, else its required. System variables can be set at server startup using options on the command line or in an option file. Most of them can be changed dynamically while the server is running by means of the SEThttp://dev.mysql.com/doc/refman/5.1/en/set-option.htmlstatement, which enables you to modify operation of the server without having to stop and restart it. You can refer to system variable values in expressions. Check the complete list of variable. : http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html Thanks, On Mon, May 31, 2010 at 3:17 PM, Lin Chun franks1...@gmail.com wrote: hi as the title thanks -- - Lin Chun -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: Master - master replication
I think setting up few more configuration variable in replication will secure the data in plain text transmission . #--master-ssl #--master-ssl-ca #--master-ssl-capath #--master-ssl-cert #--master-ssl-cipher #--master-ssl-key http://dev.mysql.com/doc/refman/5.0/en/replication-options-slave.html http://dev.mysql.com/doc/refman/5.0/en/secure-create-certs.html Thanks, On Mon, May 24, 2010 at 6:45 PM, Carl c...@etrak-plus.com wrote: Interesting. How is the best way to protect the information while using master - master replication on remote sites? (The data contains the information of children, credit cards and bank accounts.) Thanks, Carl - Original Message - From: John Daisley To: Carl Cc: Walter Heck ; mysql@lists.mysql.com Sent: Monday, May 24, 2010 6:47 AM Subject: Re: Master - master replication also consider that it is much more likely that remote slaves will start falling behind particularly if you throw encryption into the equation. Regards John On 24 May 2010 13:24, Carl c...@etrak-plus.com wrote: Walter, Don't know how I missed that but it exactly what I needed. Thanks, Carl - Original Message - From: Walter Heck wal...@openquery.com To: Carl c...@etrak-plus.com Cc: mysql@lists.mysql.com Sent: Monday, May 24, 2010 5:49 AM Subject: Re: Master - master replication Hi Carl, On Mon, May 24, 2010 at 13:42, Carl c...@etrak-plus.com wrote: 1. Is the data visible during transmission? Not sure what you mean there? 2. Is there a way to encrypt the data during transmission? MySQL supports SSL encryption of replication. Here's a good starting point: http://dev.mysql.com/doc/refman/5.1/en/replication-solutions-ssl.html cheers, Walter Heck Engineer @ Open Query (http://openquery.com) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=john.dais...@butterflysystems.co.uk -- John Daisley Certified MySQL 5 Database Administrator Certified MySQL 5 Developer Cognos BI Developer Telephone: +44 (0)7918 621621 Email: john.dais...@butterflysystems.co.uk -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: Array data type
Hi, How to store multiple values in a single field? Is there any array data type concept in mysql? As Jörg said Multiple values in a single field would be an explicit violation of the relational model... then also, if you want to use. this might be this will help you. I used like this in past: In database, I have taken a column as TEXT. In which I have separated a value by *- *(hifen) eg. furniture table there is 2 col , name (person name) - used (furniture's used by that person). value will be : | Name| Used | -- | Prabhat | chair-table-bed | === And in PHP you can easily separate these value. eg, $names = Markus;Nigel;David; To use these names in a meaningful way, we should first separate them into an array ($namearray), using explode()http://au3.php.net/manual/en/function.explode.php : $namearray = explode(;, $names); The end result: $namearray = Array ( [0] = Markus [1] = Nigel [2] = David ) But remember this is VERY bad database design. I had used since, that was required for few days only. Thanks, In database : On Sun, May 16, 2010 at 9:34 PM, Mark Goodge m...@good-stuff.co.uk wrote: On 14/05/2010 09:54, Joerg Bruehe wrote: Hi Samrat, all! Samrat Kar wrote: Hello, How to store multiple values in a single field? Is there any array data type concept in mysql? Multiple values in a single field would be an explicit violation of the relational model (on which the SQL language is based) and cause all kinds of trouble in your queries. Ever and again, developers use some kind of encoding to store a combination of values (like flags in a bit field) in one database field, but in many cases this makes queries very hard to write, and may prevent optimization of the SQL statement. It depends on your application, especially on whether this field will be used in search conditions (... WHERE combined_field has flag_X ...), to decide about a sensible approach. In general, I would prefer separate fields for different flags, and a separate table for a truly multi-valued field (like multiple postal or mail addresses for a person). If you're merely *storing* the data in the table, and will only ever retrieve it based on other factors - that is, you'll never use that field for any operands including joins and 'where' clauses - then it's often useful to store a flattened array (eg, one created by PHP's serialize() function, javascript JSON or even XML) as a string and then expand it to an array again after retrieving it. That can often be a useful way of storing meta-data about a data object (eg, EXIF data from a photograph), especially where you can't know in advance what the array structure will be when you create the database. However, that's not really an array datatype in MySQL, it's simply a method of storing an array as a string. So it's of fairly limited application, there are cases where it's very useful but it's not a substitute for storing the array values separately using the appropriate table design where you do need to run queries against it. Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=aim.prab...@gmail.com -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: Can't create foreign key
I have faced the same issue in past. * key name must be a unique*. On Fri, May 14, 2010 at 11:33 PM, j...@msdlg.com wrote: Nope, that wasn't the problem. I wasn't aware that the foreign key name must be unique. So, if you have a foreign key named fk_lesson, that same name cannot exist already, even if on a different table. I named the foreign key something completely different and that solved the problem. Jesse -Original Message- From: Carsten Pedersen [mailto:cars...@bitbybit.dk] Sent: Friday, May 14, 2010 1:49 PM To: j...@msdlg.com Cc: mysql@lists.mysql.com Subject: Re: Can't create foreign key Haven't done this in a while, but I'm guessing that you can't create both a constraint and an index with the same name? Type mismatch will in my experience most often generate an errno 150. / Carsten j...@msdlg.com skrev: I'm trying to create a foreign key by executing the following statement: ALTER TABLE `cc`.`takenlessons` ADD CONSTRAINT `fk_lessons` FOREIGN KEY (`LessonID` ) REFERENCES `cc`.`lessons` (`id` ) ON DELETE CASCADE ON UPDATE NO ACTION , ADD INDEX `fk_lessons` (`LessonID` ASC) ; I'm using the RC of MySQL workbench to do this. When I execute this statement, I get the following error: Error Code: 1005 Can't create table 'cc.#sql-115c_61' (errno: 121)) In the past when I got a similar error, it was because the fields didn't match exactly. For instance, one may be Int(10) and one Int(11), or one may be Unsigned, and the other not. But, in this case, both match exactly. What other reasons are there for a foreign key creation to fail like that? Thanks, Jesse !DSPAM:451,4bed85fe818443309765824! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub...@msdlg.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=aim.prab...@gmail.com -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: starting multiple instances with mysqld_multi
Hi,
You can run multiple instances on the same host and specify configuration
options either by adding the options as arguments on the command line, or by
storing them in configuration files.
for example, from the command line:
shell mysqld --port 3306 --log=/path/to/server1/log
shell mysqld --port 3307 --log=/path/to/server2/log
So, when MySQL starts up it reads various config files in sequence. This
means that you can set global options for all instances in one file and then
specific options for individual instances in separate files.
The files it reads by default are (on linux)
/etc/my.cnf
the_mysql_data_dir/my.cnf
~/.my.cnf
You can direct the server to read specific config files after it reads the
default /etc/my.cnf file with the following directive.
--defaults-extra-file=/path/to/extra/config/file
So, if you wanted 2 instances running with some shared options and some
specific options, you could create additional configuration files in /etc
say:
/etc/my.server1.cnf
/etc/my.server2.cnf
where my.server1.cnf has the following lines:
port=3306
log=/path/to/log/file/for/server1
(other options here)
and my.server2.cnf is:
port=3307
log=/path/to/log/file/for/server2
(other options here)
then you could start the separate instances of mysqld as follows:
shell mysqld --defaults-extra-file=/etc/my.server1.cnf
shell mysqld --defaults-extra-file=/etc/my.server2.cnf
about *mysqld_multi*
*mysqld_multi*
mysqld_multi [ options] {start| stop| report} [ server_id]
Use this to run multiple MySQL servers on different socket files and ports.
To set up
multiple servers, a different section of server options must be entered
into a configuration file (e.g., /etc/my.cnf). The naming scheme for each
section must be [mysqldn]
where n is a different number for each server. Options must be entered
separately for
each server in its own section, even when servers use the same options. At
a minimum,
each server should use a different socket file and a different TCP/IP port.
To see an
example of how a configuration file might be set up for multiple servers,
enter the
following from the command line:
mysqld_multi --example
Once multiple servers have been configured, to start a server, you can
enter something
like the following from the command line:
mysqld_multi start 3
This line would start server number 3 listed in the configuration file as
[mysqld3]. By
entering report for the first argument, you can obtain the status on the
server. For
starting and stopping the server, this script uses the mysqladmin utility.
Here is an
alphabetical list of options specific to mysqld_multi that you can enter
from the
command line, along with a brief explanation of each:
--config-file=filename
Specifies the path and name of the server’s configuration file if it is
different from
the default.
--example
Displays a sample configuration file.
--help
Displays basic help information.
--log=filename
Sets the path and name of the logfile. The default is
/tmp/mysqld_multi.log.
--mysqladmin=path
Sets the path to the mysqladmin utility.
--mysqld=path
Specifies the path to mysqld.
--no-log
Instructs the utility not to save messages to a log, but to send them to
stdout
instead.
--password=password
Provides the password for using mysqladmin.
--tcp-ip
Enables TCP/IP communication with the server instead of using a Unix-domain
socket.
--user=user
Provides the username for using mysqladmin. The same user must be used for
all
servers.
--version
Displays the version of the utility.
Thanks,
On Tue, May 11, 2010 at 9:54 AM, Nitin Mehta ntn...@yahoo.com wrote:
Hi,
I am exploring multiple instances with MySQL which will be running using
the same set of binaries (single MySQL base). Two things which I'm not able
to work out are -
1. Why mysqld_multi stop grp_id doesn't work? Well, I know why it
doesn't because there is nothing in the script to handle a 'stop' call; the
actual question would be - why it doesn't work when it is mentioned in the
MySQL reference manual?
2. how to (auto) restart the instances after a server restart? do I
need to write my own script for this or is there anything available which
I'm not aware of?
If you have resolved these issues, please let me know.
Best Regards,
Nitin
--
Best Regards,
Prabhat Kumar
MySQL DBA
Datavail-India Mumbai
Mobile : 91-9987681929
www.datavail.com
My Blog: http://adminlinux.blogspot.com
My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: Out of range value for column 'datestamp' at row 1
Thanks, It worked :) On Mon, May 10, 2010 at 6:25 PM, Jay Ess li...@netrogenic.com wrote: On 2010-05-09 13:29, Prabhat Kumar wrote: INSERT INTO myTable_info (id,range, total_qt, qt_correct, finish_time, username, datestamp) VALUES (NULL,'Kumar', '20', '17', '111', 'Prabhat','* NOW()');* Last_SQL_Error: Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax I think problem with* `datestamp` datetime NOT NULL default '-00-00 00:00:00',* Can any one please suggest me, how to deal with this error. Remove the ' around NOW(). ' Makes NOW() a literal string and not a function call. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=aim.prab...@gmail.com -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Out of range value for column 'datestamp' at row 1
Hi, I have setup replication between 2 servers, on both there is different versions of mysql. Master :5.0.67-log Slave : 5.1.43sp1-enterprise-gpl-advanced-log There is table: CREATE TABLE `myTable_info` ( `id` int(11) NOT NULL auto_increment, `range` varchar(255) NOT NULL, `total_qt` smallint NOT NULL default '0', `qt_correct` smallint NOT NULL default '0', `finish_time` smallint NOT NULL default '0', `username` varchar(100) NOT NULL, * `datestamp` datetime NOT NULL default '-00-00 00:00:00',* PRIMARY KEY (`id`) ) ENGINE=InnoDB ; During replication insert on slave error caused . INSERT INTO myTable_info (id,range, total_qt, qt_correct, finish_time, username, datestamp) VALUES (NULL,'Kumar', '20', '17', '111', 'Prabhat','* NOW()');* Last_SQL_Error: Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax I think problem with* `datestamp` datetime NOT NULL default '-00-00 00:00:00',* Can any one please suggest me, how to deal with this error. -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: how to find no.of transaction in mysql
Hi,
I think you can get only no. of transactions happned on the database since
it was started.
By using command show status ; variable Questions will give value of
no. of transactions happened on the database since database was started.
But there is way u can use, as I used to calculate query executed per
second.
time=10
orig=`mysql -e show status | awk '{if ($1 == Questions) print $2}'`
sleep $time
last=`mysql -e show status | awk '{if ($1 == Questions) print $2}'`
diff=`expr $last - $orig`
avg=`expr $diff / $time`
echo $avg
Thanks,
On Sat, May 8, 2010 at 6:40 PM, Anand anand@gmail.com wrote:
Hi ,
Is it possible to find the no.of transactions happened on the database for
the specified duration of time ? it could be for past 2/3 days.
Thanks
Anand
--
Best Regards,
Prabhat Kumar
MySQL DBA
Datavail-India Mumbai
Mobile : 91-9987681929
www.datavail.com
My Blog: http://adminlinux.blogspot.com
My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: how to find no.of transaction in mysql
or you can use some tools, there is tons of third tools you can use.
On Sat, May 8, 2010 at 7:15 PM, Prabhat Kumar aim.prab...@gmail.com wrote:
Hi,
I think you can get only no. of transactions happned on the database since
it was started.
By using command show status ; variable Questions will give value of
no. of transactions happened on the database since database was started.
But there is way u can use, as I used to calculate query executed per
second.
time=10
orig=`mysql -e show status | awk '{if ($1 == Questions) print $2}'`
sleep $time
last=`mysql -e show status | awk '{if ($1 == Questions) print $2}'`
diff=`expr $last - $orig`
avg=`expr $diff / $time`
echo $avg
Thanks,
On Sat, May 8, 2010 at 6:40 PM, Anand anand@gmail.com wrote:
Hi ,
Is it possible to find the no.of transactions happened on the database for
the specified duration of time ? it could be for past 2/3 days.
Thanks
Anand
--
Best Regards,
Prabhat Kumar
MySQL DBA
Datavail-India Mumbai
Mobile : 91-9987681929
www.datavail.com
My Blog: http://adminlinux.blogspot.com
My LinkedIn: http://www.linkedin.com/in/profileprabhat
--
Best Regards,
Prabhat Kumar
MySQL DBA
Datavail-India Mumbai
Mobile : 91-9987681929
www.datavail.com
My Blog: http://adminlinux.blogspot.com
My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: limiting acess by Triggers
*Column Privileges* Column privileges apply to single columns in a given table. Each privilege to be granted at the column level must be followed by the column or columns, enclosed within parentheses. GRANT SELECT (col1), INSERT (col1,col2) ON mydb.mytbl TO 'someuser'@'somehost'; The allowable *priv_type* values for a column (that is, when you use a * column_list* clause) are INSERThttp://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html#priv_insert, SELECThttp://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html#priv_select, and UPDATEhttp://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html#priv_update. Column privileges are stored in the mysql.columns_priv table. http://dev.mysql.com/doc/refman/5.1/en/grant.html Thanks, On Thu, May 6, 2010 at 7:54 PM, paulo matadr saddon...@yahoo.com.br wrote: Hi all , Im new on mysql,how can block update column of specif table for a user x by trigger in mysql? Is possible deny acess for columns on mysql? version: 5.0.77 Regards -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: tcpdump mysql ?
http://www.mysqlperformanceblog.com/2008/11/07/poor-mans-query-logging/ On Tue, Apr 20, 2010 at 7:19 PM, Keith Murphy bmur...@paragon-cs.comwrote: You should look at www.hackmysql.com. He has a sniffed program strictly for Mysql. Should do what you want. HTH Keith On Apr 20, 2010 5:48 AM, Brent Clark brentgclarkl...@gmail.com wrote: Hiya I tried getting the following command running from the following youtube clip. http://www.youtube.com/watch?v=Zofzid6xIZ4 Look at 19:25 I know I can use tcpdump, with maatkit (Im not always able to install maatkit on clients machines). But based on whats above in the clip, Did Mr Callaghan make a typo or leave something out. This is the command as I understand it. tcpdump -c 100 -s 1000 -A -n -p port 3306 | grep SELECT | sed 's/\/\*.*\*\///g' | sed 's/.*\(SELECT.*\)/\1/gI' | sort | uniq -c | sort -r -n -k 1,1 | head -5 Other question is. What commnds do you use to help debuging and testing. Kind Regards Brent Clark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=bmur...@paragon-cs.com -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: Mysql - Tables Export to Excel!
use can use mysqldump with option *-no-data* eg. *mysqldump -u user -ppassword wordpress user --no-data Dumpdata.txt * where wordpress is my database and user is my table. Thanks On Sun, Apr 18, 2010 at 9:48 PM, Shawn Green shawn.l.gr...@oracle.comwrote: Vikram A wrote: Hi, I would like to export my table structure from MYSQL from a particular db. Is there any tool for doing this? There are several ways to get structure information from within MySQL: the SHOW COLUMNS... command the SHOW INDEXES... command the SHOW CREATE TABLE... command you can also write queries against the tables in the INFORMATION_SCHEMA You can also use an external utility such as mysqldump. Use the --no-data option to get just a dump of your table definitions. If you also want to see triggers and events and stored procedures, you also need to use the --triggers, --events, and --procedure options, too. Details are in the fine manual: http://dev.mysql.com/doc/refman/5.1/en/show.html http://dev.mysql.com/doc/refman/5.1/en/information-schema.html http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html I guess the final answer depends on which information are you looking for and in what format you want to see it. Got any details you want to share? -- Shawn Green MySQL Principle Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=aim.prab...@gmail.com -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: Mysql - Tables Export to Excel!
Note : if you table name it will export all tables from given database; eg:* mysqldump -u user -ppassword wordpress --no-data Dumpdata.txt * It will export all tables from db wordpress. On Mon, Apr 19, 2010 at 8:57 AM, Prabhat Kumar aim.prab...@gmail.comwrote: use can use mysqldump with option *-no-data* eg. *mysqldump -u user -ppassword wordpress user --no-data Dumpdata.txt * where wordpress is my database and user is my table. Thanks On Sun, Apr 18, 2010 at 9:48 PM, Shawn Green shawn.l.gr...@oracle.comwrote: Vikram A wrote: Hi, I would like to export my table structure from MYSQL from a particular db. Is there any tool for doing this? There are several ways to get structure information from within MySQL: the SHOW COLUMNS... command the SHOW INDEXES... command the SHOW CREATE TABLE... command you can also write queries against the tables in the INFORMATION_SCHEMA You can also use an external utility such as mysqldump. Use the --no-data option to get just a dump of your table definitions. If you also want to see triggers and events and stored procedures, you also need to use the --triggers, --events, and --procedure options, too. Details are in the fine manual: http://dev.mysql.com/doc/refman/5.1/en/show.html http://dev.mysql.com/doc/refman/5.1/en/information-schema.html http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html I guess the final answer depends on which information are you looking for and in what format you want to see it. Got any details you want to share? -- Shawn Green MySQL Principle Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=aim.prab...@gmail.com -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: mysql-bin log file
You can add a *expire_logs_days* Variable in my.cnf during the configuration of replication server. #* expire_logs_days = 7* It will purged binary logs older than 7 days.The old logs will be purged during the next bin-log switch. Or, You can also delete bin-log manually using command : PURGE BINARY LOGS TO 'mysql-bin.010'; PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26'; but before you purge please make sure that slave is on sync with master or confirm the current status from slaves. Thanks, On Mon, Apr 19, 2010 at 9:47 AM, Rob Wultsch wult...@gmail.com wrote: On Sun, Apr 18, 2010 at 8:58 PM, Angelina Paul arshup...@gmail.com wrote: How can I remove old mysql-bin log file in log directory? A mysql full backup will clear the old mysql bin log file or not? Thanks, Arshu Paul You probably want http://mysql2.mirrors-r-us.net/doc/refman/5.1/en/purge-master-logs.html -- Rob Wultsch wult...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=aim.prab...@gmail.com -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: InnoDB Default Storage Engine
You need to locate the mySQL config file (helpfully named) my.cnf file. On linux it is located at /etc/my.cnf Then under the [mysqld] add the following line as shown below! *[mysqld] default-storage_engine = InnoDB* And don't forget to restart mysql. After this whenever you create a table its default storage engine is InnoDB. but it will not change any existing table storage engine to Innodb. To do this there is two way. a. You can change storage engine by alter command of tables, but one by one. eg. *Alter table [tablename] engine=myisam; * b. export the database , an then replace MyISAM with InnoDB in dump file (sed -i 's/MyISAM/InnoDB/g' dbdump.sql) and import again. Thanks, On Mon, Apr 19, 2010 at 9:53 AM, Rob Wultsch wult...@gmail.com wrote: On Sun, Apr 18, 2010 at 8:31 PM, Angelina Paul arshup...@gmail.com wrote: I want to change the mysql default storage engine from MyISAM to InnoDB. What are the steps involved .Is it edit my.cnf file and add a line default-storage-engine=innodb and restart the mysql server? If you do not want to change any existing tables all you need to do is add the line to your cnf. How I can bring my databases with mixed storage engine down without any data loss. mysqladmin shutdown What steps I have to take if I encounter a page corruption in innodb tables. The right answer is restore from backup or failover to a slave. The answer you probably want is http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html why I am getting a message *Error*: *No query specified* when I run a show engines\g commands -version (5.0.45) Exactly what are you running? -- Rob Wultsch wult...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=aim.prab...@gmail.com -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: How to watch stored procedures
In phpmyqdmin in SQL window type : show create procedure procedureName; eg; SHOW CREATE PROCEDURE circle_area; in SQL goog_1193647372 Check more on : http://adminlinux.blogspot.com/2009/12/mysql-tips-verify-database-objects.html Thanks On Fri, Apr 9, 2010 at 1:00 PM, alba.albetti alba.albe...@libero.it wrote: Since I work on MySQL by using phpMyAdmin, is there anyone saying to me if and how it's possibile to see the code of a stored procedure that I've created? For example on Oracle I use sql-developer but with phpMyAdmin is it possible to see the whole code of a procedure or of a trigger after creating it? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=aim.prab...@gmail.com -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: Relication
Hi, I would like to recommend you read following: *What is replication *: http://dev.mysql.com/doc/refman/4.1/en/replication.html *How to set : * http://dev.mysql.com/doc/refman/4.1/en/replication-howto.html , http://www.howtoforge.com/mysql_database_replication after reading above articles if you have any doubt please let us know. Thanks, On Mon, Mar 8, 2010 at 7:37 AM, Mohamed Mubeeth mubeeth@gmail.comwrote: Hi friends, im Mubeeth now Im working in software in singapore. i want some help for what is replication and how to set this is my id mubeeth@gmail.com this is handphone +65 84300135 -- Best Regards, Mubeeth S'pore +65-84300135 India +91-99940 44101 -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: Displaying date/time
Might be this will help you: there is a table called message with 3 colums - id, pubdate and message; You can get all messages from the last 5 minutes with the following example; SELECT TIMESTAMPDIFF(MINUTE, pubdate, now()), id, message from message where (TIMESTAMPDIFF(MINUTE, pubdate, now()) 5); Thanks, On Fri, Mar 5, 2010 at 8:19 PM, Tompkins Neil neil.tompk...@googlemail.comwrote: Hi I have a number of rows which have Date and Time data in. I want to display in the following formats based on the systems current time e.g under 1 hour 24min ago e.g under 1 day 16h 29min ago e.g over 1 day 1d 2h 29min ago e.g over 1 week 1w 4d 2h 29min ago How would this best be achieve using MySQL. Thanks, Neil -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: MY SQL Slave Server
*MySQL Install multiple instances.* Create a folder called Conf with Instance.1.ini, Instance.2.ini, and Instance.3.ini. The Port each are listening on should all differ, as well as having a different data directory. I named these C:\Program Files\MySQL\MySQL Server 5.0\MySQLData\Instance1 mysqld --install Mysql-Instance-1 --defaults-file=C:\Program Files\MySQL\MySQL Server 5.0\Conf\Instance.1.ini mysqld --install Mysql-Instance-2 --defaults-file=C:\Program Files\MySQL\MySQL Server 5.0\Conf\Instance.2.ini mysqld --install Mysql-Instance-3 --defaults-file=C:\Program Files\MySQL\MySQL Server 5.0\Conf\Instance.3.ini in the ini file the commands to set these are: * port=3306 * datadir=C:\Program Files\MySQL\MySQL Server 5.0\MySQLData\Instance1 * port=3307 * datadir=C:\Program Files\MySQL\MySQL Server 5.0\MySQLData\Instance2 * port=3308 * datadir=C:\Program Files\MySQL\MySQL Server 5.0\MySQLData\Instance3 After creating the folders, settings and executing the command lines to install mysql as a service. I found that all the services successfully started up and then terminated itself and in the folder .\MySQLData\Instance1 etc, you should notice the following files, ib_logfile0, ib_logfile1, ibdata1 and %SystemName%.err, which displays the following error on each instance. 051220 22:16:28 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist Which simply means it cannot find the system tables. A quick resolve is to copy the entire data directory into each instance folder (Ensure that the standard instance is disabled if you wish to leave this service alone). Then the service should hopefully startup for all 3 services. xcopy data MySQLData/Instance1 xcopy data MySQLData/Instance2 xcopy data MySQLData/Instance3 net start Mysql-Instance-1 net start Mysql-Instance-2 net start Mysql-Instance-3 PS. To remove the instances enter the following commands. mysqld --remove Mysql-Instance-1 mysqld --remove Mysql-Instance-2 mysqld --remove Mysql-Instance-3 source: http://ajohnstone.com/archives/mysql-install-multiple-instances On Sat, Feb 6, 2010 at 10:08 PM, Vikram A vikkiatb...@yahoo.in wrote: I tried to install once again mysql at vista? but deducts the previous installation. and it is ask for the modify , repire and remove options You said that we can install any number of setups in a same system. Is there any other way to install? Please help me thank you From: Thiyaghu CK theyaho...@gmail.com To: Vikram A vikkiatb...@yahoo.in Sent: Sat, 6 February, 2010 3:05:25 PM Subject: Re: MY SQL Slave Server Hi Vikram, Ya sure, slave can be in vista. Yes, you can install 2 or more setups in same system but port has to be different, and if its linux platform change the sock name too. Regards, Thiyaghu CK www.mafiree.com On Sat, Feb 6, 2010 at 2:09 PM, Vikram A vikkiatb...@yahoo.in wrote: Dear Thiyagu! Thank you for the information. I will do the experiment. and let you know the further details. Is it possible making slave at windows vista? As per your information, th emysql has to be installed once again another port; Is it possible to install 2 setups in a same system? Thank you VIKRAM A From: Thiyaghu CK theyaho...@gmail.com To: Vikram A vikkiatb...@yahoo.in Cc: mysql@lists.mysql.com Sent: Sat, 6 February, 2010 1:03:34 PM Subject: Re: MY SQL Slave Server Hi Vikram, So as my understanding you need to have a master slave setup in a single machine(FEDORA 11). 1. Make the already running mysql instance as Master 2. Install a new mysql in the same machine in different port which will be your slave 3. Replicate 4.You can also add more slave in same machine or in different machine and make more copy. Note: Slave should be of same version or higher. For replication steps you can refer http://www.mafiree.com/docs.html or http://www.howtoforge.com/mysql_database_replication Let me know for more details. Regards, Thiyaghu CK www.mafiree.com On Sat, Feb 6, 2010 at 12:33 PM, Vikram A vikkiatb...@yahoo.in wrote: Dear Experts, I would like to configure the slave for my main server. My server is running in the FEDORA 11. I would like to make another mirror of the DB in the same server/ The copy of the DB can be kept at win server / another Fedora server. Can you suggest how to do the above? Thank you VIKKI A The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/ The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/ -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My
Re: how to get the name of primary key ?
u can also get information of table using use use urdbname mysqlshow create table game_log \G 2010/2/1 曹凯 tx...@hotmail.com hi Jesper, thanks a lot! That's what I want. thank u againCC: mysql@lists.mysql.com From: jes...@noggin.com.au Subject: Re: how to get the name of primary key ? Date: Mon, 1 Feb 2010 20:26:36 +1100 To: tx...@hotmail.com On 01/02/2010, at 7:33 PM, 曹凯 wrote: Hi all, if we just know the table name but don't know the name of primary key, is there any variables or constants could instead of the PK? for example: there is a table game_log, and now I have the last inserted_id but don't know what its primary_id is, how can I SELECT * FROM game_log WHERE this_table's_PK = last_inserted_id? You can get the column name from the information schema, however that can't be used directly in another query in the way you've done in your example. E.g. game SELECT COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = 'game' AND TABLE_NAME = 'game_log' AND CONSTRAINT_NAME = 'PRIMARY';+-+| COLUMN_NAME |+-+| GameLogID | +-+1 row in set (0.00 sec) where it is assumed the database name is game. Hope that helps. Jesper _ Windows Live社区两周年，拿奖过新年！ http://events.livetome.cn/2010/2birthday -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: How to change mysql default database directory
Hi, For Linux:*How to change the mysql database location* 1. Create the directory that will be the new datadir. ( example: /home/db ) 2. Change ownership of new folder to the mysql user and group: sudo chown -R mysql:mysql /home/db 3. Stop the MySQL database server: sudo /etc/init.d/mysql stop 4. Now you need to edit the /etc/mysql/my.cnf file 1. sudo nano /etc/mysql/my.cnf 2. look for “datadir = /var/lib/mysql” and change it to datadir = /home/db 5. copy the files from the old datadir to the new location. However, make sure that the files named ib_arch_log_00, ib_logfile0 etc. are not copied to the newer location. 6. Make sure that the files and directories are owned by mysql user. If not: sudo chown -R mysql:mysql /home/db/* 7. Restart the MySQL database server: sudo /etc/init.d/mysql start Hope you find this helpful. On Fri, Jan 29, 2010 at 8:42 AM, Lucky Wijaya luckyx_cool_...@yahoo.comwrote: Hi all, I've installed MySQL on 320GB Harddisk (partitioned into 3 partitions). I want to know if there's a way to change MySQL default database directory from C: to D:. Thanks. -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: Using symlinks for database creation in mysql
I don't agree, Don't have any specific documents but seems to make a bit of sense to me. Opening a file that is a symbolic link takes more time than one that isn't as you need to do more operations. Search the directory for the symbolic link, open it (probably, though an inode look up might be enough), find the file it points to and then open that. Furthermore, I believe that an application that opened a symbolic link is only ever going to reference that file via that link (as that's the file handle it got when it opened the thing). This means that all io to and from that file probably needs to go through a longer path. Where you have hundreds of these going on, those additional system calls are going to add up to a degradation in performance. On Fri, Jan 29, 2010 at 12:14 PM, Suresh Kuna sureshkumar...@gmail.comwrote: Not a problem as you are doing it from a whole data directory. Thanks Suresh Kuna MySQL DBA On Fri, Jan 29, 2010 at 11:56 AM, Manasi Save manasi.s...@artificialmachines.com wrote: Hi All, I am creating symlinks as database. I have mysql data directory created on /var/lib/mysql/databasename. on the same path I am creating /var/lib/mydatabase and creating symlink from /var/lib/mydatabase to /var/lib/mysql/databasename will there any performance issues as there will be quite a few folders and symlinks on the same path? Thanks in advance. -- Regards, Manasi Save -- Thanks Suresh Kuna MySQL DBA -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: Using symlinks for database creation in mysql
And, IF the application uses the file a lot, but opens it infrequently, for example an MySQL data file, then the incremental cost is truly neglectable. IF the symlink is looked up and followed frequently then caches will make it neglectable. IF the application touches a wide range (hundreds of thousands) of symlinks frequently and performs very little actual access work (small files), then there will be a potential significant overhead. On Fri, Jan 29, 2010 at 12:20 PM, prabhat kumar aim.prab...@gmail.comwrote: I don't agree, Don't have any specific documents but seems to make a bit of sense to me. Opening a file that is a symbolic link takes more time than one that isn't as you need to do more operations. Search the directory for the symbolic link, open it (probably, though an inode look up might be enough), find the file it points to and then open that. Furthermore, I believe that an application that opened a symbolic link is only ever going to reference that file via that link (as that's the file handle it got when it opened the thing). This means that all io to and from that file probably needs to go through a longer path. Where you have hundreds of these going on, those additional system calls are going to add up to a degradation in performance. On Fri, Jan 29, 2010 at 12:14 PM, Suresh Kuna sureshkumar...@gmail.comwrote: Not a problem as you are doing it from a whole data directory. Thanks Suresh Kuna MySQL DBA On Fri, Jan 29, 2010 at 11:56 AM, Manasi Save manasi.s...@artificialmachines.com wrote: Hi All, I am creating symlinks as database. I have mysql data directory created on /var/lib/mysql/databasename. on the same path I am creating /var/lib/mydatabase and creating symlink from /var/lib/mydatabase to /var/lib/mysql/databasename will there any performance issues as there will be quite a few folders and symlinks on the same path? Thanks in advance. -- Regards, Manasi Save -- Thanks Suresh Kuna MySQL DBA -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: Rename mysql database
You can try this one. http://preetul.wordpress.com/2009/07/27/rename-database-in-mysql/ Note: I have not tested. On Fri, Jan 8, 2010 at 5:04 PM, Manasi Save manasi.s...@artificialmachines.com wrote: Hi All, I want to rename my database. but as per the mysql documentation it says, RENAME DATABASE command has been removed from mysql 5.1. Can anyone help me with this, that is there any other way I can rename it. Thanks in advance. -- Regards, Manasi Save -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: FW: MySQL export and import into Oracle
http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_if On Fri, Jan 8, 2010 at 5:52 PM, machiel.richards machiel.richa...@gmail.com wrote: Hi guys, Can you please assist me in rewriting this query in order to run this against a mysql database? It seems that the decode function does not exist in mysql. select decode(nvl(receive_email, 'No'), 'Yes', 'Yes', 'No') email_corr, count(*) tot from profiles where email is not null group by (decode(nvl(receive_email, 'No'), 'Yes', 'Yes', 'No')) -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: How Set Up This Table
primary key is a candidate key to uniquely identify each row in a table. A unique key or primary key comprises a single column or set of columns. No two distinct rows in a table can have the same value (or combination of values) in those columns. Depending on its design, a table may have arbitrarily many unique keys but at most one primary key. a compound key is a key that consists of 2 or more attributes that uniquely identify an entity occurrence. On Sat, Jan 2, 2010 at 10:05 PM, Victor Subervi victorsube...@gmail.comwrote: On Sat, Jan 2, 2010 at 11:20 AM, Arthur Fuller fuller.art...@gmail.com wrote: Hi Victor. I think that the first thing you need to consider is whether a product can be in more than one package, and second is whether a package can be in another package. Also, I don't know why you need to auto-generate in either case. It's pretty simple DDL. Case 1: product can only be in one package: 1. Add a Packages table with columns PackageID and PackageName and probably PackagePrice. 2. Add a PackageID column to the Products table and make it a foreign key referencing Packages. Case 2: product can be in multiple packages: 1. Same as above. 2. Create a ProductPackages table that contains PackageID and ProductID, both as foreign keys into Products and Packages. 3. Decide whether you want a compund PK on this new table, or you want instead an auto-increment column that would be the PK. (There are differences of opinion on this one, so I'm leaving it alone; I don't want to start a religious war :) Thanks! That's reversing my thinking! I hadn't considered working the other direction. What is a PK and a compound PK? TIA, V -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: HELP! RESET MASTER hosed replication
*Next time look for 'purge binary logs'!* Purge may also cause problem , before doing purge make sure slave is in sync with master or at least cross check slave currently which bin file getting sync. On Thu, Dec 24, 2009 at 2:05 AM, Carlos Proal carlos.pr...@gmail.comwrote: The issue is that replication relies on this logs !!!, so when you deleted them . Generally speaking you have to: stop the slave sync the master with the slave (there are several ways to do this and depending how busy is your master) grab the master status (position) change the slave to point to the master new position start the slave Hope this helps. Carlos On 12/23/2009 2:20 PM, Daevid Vincent wrote: I got an alert that one of the drives was filling up (3% free). So I figured out that a large chunk was from /var/log/mysql r...@pse10:~# find / -type d -print0 | xargs -0 -n1 du -sk | sort -rn | head -n20 ~/dir-sizes.txt r...@pse10:~# cat ~/dir-sizes.txt 159121012 / 70442396 /var 70127764 /var/log 69991160 /var/log/mysql big offender 56307436 /data 31479936 /home 29386076 /data/mysql 26899784 /data/archive It looked like the /var/log/mysql was pretty full of these Mysql-bin.00 log files, ... -rw-rw 1 mysql adm 105019928 2009-12-23 05:07 mysql-bin.001196 -rw-rw 1 mysql adm 105004751 2009-12-23 05:08 mysql-bin.001197 -rw-rw 1 mysql adm 104978518 2009-12-23 05:10 mysql-bin.001198 -rw-rw 1 mysql adm 104949073 2009-12-23 05:11 mysql-bin.001199 -rw-rw 1 mysql adm 104925795 2009-12-23 05:13 mysql-bin.001200 -rw-rw 1 mysql adm 104974354 2009-12-23 05:14 mysql-bin.001201 -rw-rw 1 mysql adm 105089249 2009-12-23 05:16 mysql-bin.001202 -rw-rw 1 mysql adm 105165487 2009-12-23 05:17 mysql-bin.001203 -rw-rw 1 mysql adm 104926853 2009-12-23 05:19 mysql-bin.001204 -rw-rw 1 mysql adm 105139076 2009-12-23 05:20 mysql-bin.001205 -rw-rw 1 mysql adm 104891552 2009-12-23 05:22 mysql-bin.001206 -rw-rw 1 mysql adm 104959626 2009-12-23 05:25 mysql-bin.001207 -rw-rw 1 mysql adm 104883048 2009-12-23 05:27 mysql-bin.001208 -rw-rw 1 mysql adm 104993511 2009-12-23 05:28 mysql-bin.001209 -rw-rw 1 mysql adm 104945974 2009-12-23 05:30 mysql-bin.001210 -rw-rw 1 mysql adm 35468892 2009-12-23 05:30 mysql-bin.001211 -rw-rw 1 mysql adm 21728 2009-12-23 05:30 mysql-bin.index -rw-r- 1 mysql adm 12836 2009-12-23 00:12 mysql-slow.log ... so I took the liberty of resetting them... http://dev.mysql.com/doc/refman/5.0/en/reset.html vince...@pse10 /var/log/mysql $ dbroot (r...@localhost) [(none)] RESET MASTER; vince...@pse10 /var/log/mysql $ ll total 2792 -rw-rw 1 mysql adm 2801618 2009-12-23 05:35 mysql-bin.01 -rw-rw 1 mysql adm 32 2009-12-23 05:35 mysql-bin.index -rw-r- 1 mysql adm 14987 2009-12-23 05:35 mysql-slow.log -rw-r- 1 mysql adm1102 2009-12-22 00:13 mysql-slow.log.1.gz -rw-r- 1 mysql adm 891 2009-12-21 00:02 mysql-slow.log.2.gz -rw-r- 1 mysql adm1318 2009-12-20 00:02 mysql-slow.log.3.gz -rw-r- 1 mysql adm 687 2009-12-19 00:02 mysql-slow.log.4.gz -rw-r- 1 mysql adm5246 2009-12-17 20:38 mysql-slow.log.5.gz -rw-r- 1 mysql adm 156 2009-12-16 06:25 mysql-slow.log.6.gz -rw-r- 1 mysql adm1114 2009-12-15 16:26 mysql-slow.log.7.gz Which freed up a tremendous amount of space again... However, a co-worker informed me that now our slaves are broken and replication is hosed! What did I do wrong or forget to do? I see no mention of something I was supposed to do for replication scenarios... Was I supposed to RESET SLAVE too? As of right now, /var/log/mysql has grown to mysql-bin.28 since last night when I reset it. How do I recover from this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=aim.prab...@gmail.com -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: login issue..pls help
a. INFORMATION_SCHEMA is the information database ie metadata, the place that stores information about all the other databases that the MySQL server maintains. Inside INFORMATION_SCHEMA there are several read-only tables. They are actually views, not base tables, so there are no files associated with them. Each MySQL user has the right to access these tables, but can see only the rows in the tables that correspond to objects for which the user has the proper access privileges. http://dev.mysql.com/doc/refman/5.0/en/information-schema.html b. Its depend on your requirement like your user will perform only DML query or also want DDL operations. http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html On Thu, Oct 1, 2009 at 12:08 PM, F.A.I.Z.A.L sac.fai...@gmail.com wrote: thanks kumar.. now its working i have another doubt i created an user with limited privilege on particular schema. schema name: test *mysql create user 'test'@'%' identified by 'test'; mysql grant select,insert,update,delete,create,drop on test.* to 'test'@ '%';* after that i login with 'test' user using mysql query browser. but i can able to see other schemas like 'information_Schema' and 'test_schema' created by other. how can i avoid this. i want to see only my default schema.. 2. for created new mysql database or schema what are the privileges are required.. please help.. Best Regards Faizal S GSM : 9840118673 Blog: http://oradbapro.blogspot.com On Thu, Oct 1, 2009 at 11:09 AM, prabhat kumar aim.prab...@gmail.comwrote: Use : mysql -u root -p (it will prompt for password) and if its not a local host also add -h ipaddress On Thu, Oct 1, 2009 at 11:01 AM, F.A.I.Z.A.L sac.fai...@gmail.comwrote: Hi iam getting the following error while login into mysql server. andd141# mysql -u root ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) Can someone please help me understand why I am not able to login from root? Best Regards Faizal S GSM : 9840118673 Blog: http://oradbapro.blogspot.com -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: Adding Fields To Table
You can use : *ALTER TABLE it ADD COLUMN title varchar(25), phone number integer (6);* it will add new column after last. but you can also specify the particular column after you want to add. like. *ALTER TABLE it ADD COLUMN title varchar(25), phone number integer (6) AFTER pusername; * check more options. http://dev.mysql.com/doc/refman/5.1/en/alter-table.html On Thu, Oct 1, 2009 at 12:32 PM, Martijn Tonies m.ton...@upscene.comwrote: Hello Carlos, I am no doubt very new to MySQL and have been studying the on line manual however I find it a bit complex and don't understand what it's suggesting I do in this case. I created a database called 'staff' and then in that database I created one simple table: mysql show tables; +-+ | Tables_in_staff | +-+ | it | +-+ 1 row in set (0.00 sec) mysql describe it; +--+-+--+-+-+---+ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-+---+ | name | varchar(40) | YES | | NULL| | | username | varchar(20) | YES | | NULL| | | email| varchar(20) | YES | | NULL| | | office | char(3) | YES | | NULL| | +--+-+--+-+-+---+ 4 rows in set (0.00 sec) My question is if I would like to add addition fields like 'title' 'phone number', how can I modify the table fields entry to add more fields? Thanks for any help! The manual has a section on how to create and modify your metadata, I suggest you take a look, it explains all. With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! Database questions? Check the forum: http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=aim.prab...@gmail.com -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: login issue..pls help
A. For transaction *innoDB* is best. B. *ALTER TABLE table_name ENGINE = InnoDB; * but* *if you tons of tables so better export all tables in file and replace all occurs of *myisam* with *InnoDB *the import it. and enable InnoDB engine. http://www.linux.com/archive/articles/46370 C. 1. Stop MySQL. 2. Copy current mysql dir to new drive. 3. Modify the current location with new location in /etc/my.cnf file , datadir variable. 4. Start MySQL. note: *datadir=/path/to/datadir/mysq*l http://docdb.fnal.gov/doc/my.cnf.html On Thu, Oct 1, 2009 at 12:53 PM, F.A.I.Z.A.L sac.fai...@gmail.com wrote: hi Kumar thanks for your info.. another doubt.. which storage engine is best for transaction.. in my server. the default engine is myisam. now i changed one of my schema to innoDB. is that correct. now i want to create another schema in different location. how to create that? because current mountpoint space is very less, in this situation how can move existing schemas to new location? thanks in advance.. Best Regards Faizal S GSM : 9840118673 Blog: http://oradbapro.blogspot.com On Thu, Oct 1, 2009 at 12:28 PM, prabhat kumar aim.prab...@gmail.comwrote: a. INFORMATION_SCHEMA is the information database ie metadata, the place that stores information about all the other databases that the MySQL server maintains. Inside INFORMATION_SCHEMA there are several read-only tables. They are actually views, not base tables, so there are no files associated with them. Each MySQL user has the right to access these tables, but can see only the rows in the tables that correspond to objects for which the user has the proper access privileges. http://dev.mysql.com/doc/refman/5.0/en/information-schema.html b. Its depend on your requirement like your user will perform only DML query or also want DDL operations. http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html On Thu, Oct 1, 2009 at 12:08 PM, F.A.I.Z.A.L sac.fai...@gmail.comwrote: thanks kumar.. now its working i have another doubt i created an user with limited privilege on particular schema. schema name: test *mysql create user 'test'@'%' identified by 'test'; mysql grant select,insert,update,delete,create,drop on test.* to 'test'@ '%';* after that i login with 'test' user using mysql query browser. but i can able to see other schemas like 'information_Schema' and 'test_schema' created by other. how can i avoid this. i want to see only my default schema.. 2. for created new mysql database or schema what are the privileges are required.. please help.. Best Regards Faizal S GSM : 9840118673 Blog: http://oradbapro.blogspot.com On Thu, Oct 1, 2009 at 11:09 AM, prabhat kumar aim.prab...@gmail.comwrote: Use : mysql -u root -p (it will prompt for password) and if its not a local host also add -h ipaddress On Thu, Oct 1, 2009 at 11:01 AM, F.A.I.Z.A.L sac.fai...@gmail.comwrote: Hi iam getting the following error while login into mysql server. andd141# mysql -u root ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) Can someone please help me understand why I am not able to login from root? Best Regards Faizal S GSM : 9840118673 Blog: http://oradbapro.blogspot.com -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: login issue..pls help
a. at Linux level you can make soft link to database dir to particular database ie schema. I don't is it possible at mysql level? b. There is no relation between storage engine and table space. Yes, Innodb uses tablespace you can read more on both. c. http://mysql-tips.blogspot.com/2005/04/setup-new-users-in-mysql.html On Thu, Oct 1, 2009 at 2:10 PM, F.A.I.Z.A.L sac.fai...@gmail.com wrote: thanks kumar. Storage in datadir contain only one location.is it possible to have multiple location to store datafiles.if yes, how? what is the different between storage engine and tablespace in mysql? account creation *create user 'test'@'%' identified by 'test'; grant usage on test.* to 'test'@'%' identified by 'test'; grant select,insert,update,delete,create,drop on test.* to 'test'@'%'; * I used 'USAGE' clause this time but even after that i can able to see other schema and i can able to 'select' tables from other schema. how i can restrict this. only default schema should be accessible to the user thanks in advance.. Best Regards Faizal S GSM : 9840118673 Blog: http://oradbapro.blogspot.com On Thu, Oct 1, 2009 at 1:13 PM, prabhat kumar aim.prab...@gmail.comwrote: A. For transaction *innoDB* is best. B. *ALTER TABLE table_name ENGINE = InnoDB; * but* *if you tons of tables so better export all tables in file and replace all occurs of *myisam* with *InnoDB *the import it. and enable InnoDB engine. http://www.linux.com/archive/articles/46370 C. 1. Stop MySQL. 2. Copy current mysql dir to new drive. 3. Modify the current location with new location in /etc/my.cnf file , datadir variable. 4. Start MySQL. note: *datadir=/path/to/datadir/mysq*l http://docdb.fnal.gov/doc/my.cnf.html On Thu, Oct 1, 2009 at 12:53 PM, F.A.I.Z.A.L sac.fai...@gmail.comwrote: hi Kumar thanks for your info.. another doubt.. which storage engine is best for transaction.. in my server. the default engine is myisam. now i changed one of my schema to innoDB. is that correct. now i want to create another schema in different location. how to create that? because current mountpoint space is very less, in this situation how can move existing schemas to new location? thanks in advance.. Best Regards Faizal S GSM : 9840118673 Blog: http://oradbapro.blogspot.com On Thu, Oct 1, 2009 at 12:28 PM, prabhat kumar aim.prab...@gmail.comwrote: a. INFORMATION_SCHEMA is the information database ie metadata, the place that stores information about all the other databases that the MySQL server maintains. Inside INFORMATION_SCHEMA there are several read-only tables. They are actually views, not base tables, so there are no files associated with them. Each MySQL user has the right to access these tables, but can see only the rows in the tables that correspond to objects for which the user has the proper access privileges. http://dev.mysql.com/doc/refman/5.0/en/information-schema.html b. Its depend on your requirement like your user will perform only DML query or also want DDL operations. http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html On Thu, Oct 1, 2009 at 12:08 PM, F.A.I.Z.A.L sac.fai...@gmail.comwrote: thanks kumar.. now its working i have another doubt i created an user with limited privilege on particular schema. schema name: test *mysql create user 'test'@'%' identified by 'test'; mysql grant select,insert,update,delete,create,drop on test.* to 'test'@'%';* after that i login with 'test' user using mysql query browser. but i can able to see other schemas like 'information_Schema' and 'test_schema' created by other. how can i avoid this. i want to see only my default schema.. 2. for created new mysql database or schema what are the privileges are required.. please help.. Best Regards Faizal S GSM : 9840118673 Blog: http://oradbapro.blogspot.com On Thu, Oct 1, 2009 at 11:09 AM, prabhat kumar aim.prab...@gmail.comwrote: Use : mysql -u root -p (it will prompt for password) and if its not a local host also add -h ipaddress On Thu, Oct 1, 2009 at 11:01 AM, F.A.I.Z.A.L sac.fai...@gmail.comwrote: Hi iam getting the following error while login into mysql server. andd141# mysql -u root ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) Can someone please help me understand why I am not able to login from root? Best Regards Faizal S GSM : 9840118673 Blog: http://oradbapro.blogspot.com -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat -- Best Regards, Prabhat
Re: login issue..pls help
innodb. Since, If there are many modifications of the data, it's said that InnoDB works faster because it uses row locking instead of table locking, like MyISAM. However, if there are mainly SELECT statements, a MyISAM table might be faster. On Thu, Oct 1, 2009 at 2:33 PM, F.A.I.Z.A.L sac.fai...@gmail.com wrote: 1 doubt the front end application is lab ticketing system. so user will create many ticket. iam expecting per day 200k ticket user will create. so which storage engine is best to use. innodb or myisam..? Best Regards Faizal S GSM : 9840118673 Blog: http://oradbapro.blogspot.com On Thu, Oct 1, 2009 at 2:10 PM, F.A.I.Z.A.L sac.fai...@gmail.com wrote: thanks kumar. Storage in datadir contain only one location.is it possible to have multiple location to store datafiles.if yes, how? what is the different between storage engine and tablespace in mysql? account creation *create user 'test'@'%' identified by 'test'; grant usage on test.* to 'test'@'%' identified by 'test'; grant select,insert,update,delete,create,drop on test.* to 'test'@'%'; * I used 'USAGE' clause this time but even after that i can able to see other schema and i can able to 'select' tables from other schema. how i can restrict this. only default schema should be accessible to the user thanks in advance.. Best Regards Faizal S GSM : 9840118673 Blog: http://oradbapro.blogspot.com On Thu, Oct 1, 2009 at 1:13 PM, prabhat kumar aim.prab...@gmail.comwrote: A. For transaction *innoDB* is best. B. *ALTER TABLE table_name ENGINE = InnoDB; * but* *if you tons of tables so better export all tables in file and replace all occurs of *myisam* with *InnoDB *the import it. and enable InnoDB engine. http://www.linux.com/archive/articles/46370 C. 1. Stop MySQL. 2. Copy current mysql dir to new drive. 3. Modify the current location with new location in /etc/my.cnf file , datadir variable. 4. Start MySQL. note: *datadir=/path/to/datadir/mysq*l http://docdb.fnal.gov/doc/my.cnf.html On Thu, Oct 1, 2009 at 12:53 PM, F.A.I.Z.A.L sac.fai...@gmail.comwrote: hi Kumar thanks for your info.. another doubt.. which storage engine is best for transaction.. in my server. the default engine is myisam. now i changed one of my schema to innoDB. is that correct. now i want to create another schema in different location. how to create that? because current mountpoint space is very less, in this situation how can move existing schemas to new location? thanks in advance.. Best Regards Faizal S GSM : 9840118673 Blog: http://oradbapro.blogspot.com On Thu, Oct 1, 2009 at 12:28 PM, prabhat kumar aim.prab...@gmail.comwrote: a. INFORMATION_SCHEMA is the information database ie metadata, the place that stores information about all the other databases that the MySQL server maintains. Inside INFORMATION_SCHEMA there are several read-only tables. They are actually views, not base tables, so there are no files associated with them. Each MySQL user has the right to access these tables, but can see only the rows in the tables that correspond to objects for which the user has the proper access privileges. http://dev.mysql.com/doc/refman/5.0/en/information-schema.html b. Its depend on your requirement like your user will perform only DML query or also want DDL operations. http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html On Thu, Oct 1, 2009 at 12:08 PM, F.A.I.Z.A.L sac.fai...@gmail.comwrote: thanks kumar.. now its working i have another doubt i created an user with limited privilege on particular schema. schema name: test *mysql create user 'test'@'%' identified by 'test'; mysql grant select,insert,update,delete,create,drop on test.* to 'test'@'%';* after that i login with 'test' user using mysql query browser. but i can able to see other schemas like 'information_Schema' and 'test_schema' created by other. how can i avoid this. i want to see only my default schema.. 2. for created new mysql database or schema what are the privileges are required.. please help.. Best Regards Faizal S GSM : 9840118673 Blog: http://oradbapro.blogspot.com On Thu, Oct 1, 2009 at 11:09 AM, prabhat kumar aim.prab...@gmail.com wrote: Use : mysql -u root -p (it will prompt for password) and if its not a local host also add -h ipaddress On Thu, Oct 1, 2009 at 11:01 AM, F.A.I.Z.A.L sac.fai...@gmail.comwrote: Hi iam getting the following error while login into mysql server. andd141# mysql -u root ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) Can someone please help me understand why I am not able to login from root? Best Regards Faizal S GSM : 9840118673 Blog: http://oradbapro.blogspot.com -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http
Re: Adding Fields To Table
Thanks Joerg. You are very correct. On Thu, Oct 1, 2009 at 2:40 PM, Joerg Bruehe joerg.bru...@sun.com wrote: Hi! prabhat kumar wrote: You can use : *ALTER TABLE it ADD COLUMN title varchar(25), phone number integer (6);* it will add new column after last. but you can also specify the particular column after you want to add. like. *ALTER TABLE it ADD COLUMN title varchar(25), phone number integer (6) AFTER pusername; * The syntax is correct, but one data type is wrong: A phone number is no numeric type, it is a string of (mostly) digits. Those who think there is no difference, they are wrong: 1) In a phone number, you may need a leading zero. No numeric type supports that, unless you try some formatting which would then affect all values and not just selected ones. 2) Just ask yourself whether it makes sense to compute the average phone number (yes, the term is misleading). Similar reasoning applies to other digit strings, like article number, ISBN (books), SSN (social security number), passport number, etc. Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com Sun Microsystems GmbH, Komturstraße 18a, D-12099 Berlin Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: login issue..pls help
Use : mysql -u root -p (it will prompt for password) and if its not a local host also add -h ipaddress On Thu, Oct 1, 2009 at 11:01 AM, F.A.I.Z.A.L sac.fai...@gmail.com wrote: Hi iam getting the following error while login into mysql server. andd141# mysql -u root ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) Can someone please help me understand why I am not able to login from root? Best Regards Faizal S GSM : 9840118673 Blog: http://oradbapro.blogspot.com -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: optimize my.cnf
Well said Darren. Its not magic :) Christos, you can download and run mysqlreport from http://hackmysql.com/mysqlreportguide it will give you more idea about DB serve performance. On Thu, Sep 3, 2009 at 3:07 PM, Darren Cassar i...@mysqlpreacher.comwrote: Hi Christos, Performance optimization is very subjective, and if you are experiencing low performance it can be a million different things. The description you provided didn't mention any of your current my.cnf settings, nor did it mention what kind of tables your database contains, size of data, types of indexes, engines used by your tables etc. You mention you have a quad core machine running 8G ram how much of that ram is being used by MySQL and how much by other applications? how much of it is free? What kind of CPU usage are you experiencing, what is the size of your db, are you logging slow queries and checking for missing indexes? My.cnf is not a magic box which improves performance by setting a couple of variables and normally the performance improvements are minimal as compared to other things like: 1: faster disks 2: normalized and correctly designed db (not in your power I guess) 3: good code in your application (not in your power either) I'd suggest you read High Performance MySQL 2nd ed which will definitely be of help. Sorry for bombarding you with questions but performance tuning is not something you do by setting a couple of params on my.cnf! Gluck Darren www.mysqlpreacher.com - yet another blog from a mysql dba www.securich.com - a mysql security plugin On Thu, Sep 3, 2009 at 10:06 AM, Christos Pelekis chris...@blueice.org wrote: Hi, can you please send me some optimization examples for my.cnf ? I use mysql 5.1.37 The server run just 2 very busy forums. It is quad core cpu and 8 giga ram so we have lot of run (run debian) Can you please give me some examples? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=i...@mysqlpreacher.com -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: recovery help needed
Step # 1 : Stop mysql service # /etc/init.d/mysql stop Step # 2: Start to MySQL server w/o password: # mysqld_safe --skip-grant-tables Step # 3: Connect to mysql server using mysql client: # mysql -u root Step # 4: Setup new MySQL root user password mysql use mysql; mysql update user set password=PASSWORD(NEW-ROOT-PASSWORD) where User='root'; mysql flush privileges; mysql quit Step # 5: Stop MySQL Server: # /etc/init.d/mysql stop Step # 6: Start MySQL server and test it # /etc/init.d/mysql start # mysql -u root -p Note: You can check structure of table 'user' mysql SHOW CREATE TABLE 'user'; And update other required fields also. Enjoy ;-) On Wed, Aug 26, 2009 at 2:36 PM, Martijn Engler mart...@crystal-labs.nlwrote: I actually get the feeling you are not connecting as root. Try mysql -uroot -p test instead of just mysql test Have a nice day, - Martijn On Wed, Aug 26, 2009 at 03:02, Joemysql@bluepolka.net wrote: OK, thanks, that got me in. But upon inspection, the user.host values do not look fouled up as I thought they were (it appears the bogus update may have aborted). But my access problem remains If I start with --skip-grant-tables, 'show databases' shows all DBs. But without that flag, I only see the 'information_schema' DB. Any suggestions as to where I look from here? On Tuesday 25 August 2009 @ 18:17, Walter Heck - OlinData.com wrote: Hey Joe, stop the server, start it with --skip-grant-tables, change the root entry in mysql.user to your liking, and then restart the server without --skip-grant-tables. viola! Walter On Wed, Aug 26, 2009 at 02:12, Joemysql@bluepolka.net wrote: We have an inaccessible MySQL v5.0.45 DB (w/Innodb) we really need some help regaining access to. While attempting to adjust/add remote user access, we accidentally did the following: use mysql; update user set host = 'SomeBogusIP' where user = 'root'; Now, we can't get into the DB to fix it: # mysql test ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'test' # mysql mysql ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'mysql' We are not MySQL experts by any stretch, so any help is appreciated. Here are the files we evidently touched: # ls -ltr /var/lib/mysql/mysql/ -rw-r- 1 mysql mysql 5256 Aug 25 17:33 db.MYD -rw-r- 1 mysql mysql844 Aug 25 17:35 user.MYD -rw-r- 1 mysql mysql 2048 Aug 25 17:50 user.MYI -rw-r- 1 mysql mysql 4096 Aug 25 17:50 db.MYI We do have a months-old copy of the 'mysql' db directory. Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=li...@olindata.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mart...@crystal-labs.nl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=aim.prab...@gmail.com -- Best Regards, Prabhat
