tag:blogger.com,1999:blog-54214730805023114602024-02-24T14:01:36.037+05:30My (SQL) WorkLogIt’s all about My(SQL) favorite tools, utilities, tips, tricks, code snippets, discoveries and best practices.Umesh Shastryhttp://www.blogger.com/profile/02551756983528645221noreply@blogger.comBlogger33125tag:blogger.com,1999:blog-5421473080502311460.post-55482945365956003802011-02-21T20:44:00.000+05:302011-02-21T20:44:11.622+05:30MySQL: Bulk import and logging the warnings to a file<span style="color: #666666; font-family: "Trebuchet MS", sans-serif; font-size: x-small;"><strong>Platform: Any UNIX*</strong></span><br />
<span style="font-family: "Trebuchet MS", sans-serif; font-size: x-small;"></span><br />
<span style="color: black; font-family: "Trebuchet MS", sans-serif; font-size: x-small;">If you are working on a small set of data or with fewer tables and if you notice that the DML statements such as INSERT, UPDATE, and LOAD DATA INFILE as well as DDL statements such as CREATE TABLE and ALTER TABLE operation has generated few warnings then you can just execute the “SHOW WARNINGS” SQL command to display the exact warning messages generated during the last DML operation.</span><br />
<span style="font-family: "Trebuchet MS", sans-serif; font-size: x-small;">But “<span style="color: blue;">SHOW WARINGS</span>” shows nothing if the last statement used a table and generated no messages.</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;"><span style="font-size: xx-small;"><strong><em>SHOW WARNINGS</em></strong> shows the error, warning, and note messages that resulted from the last statement that generated messages in the current session.</span></span><br />
<span style="font-family: "Trebuchet MS", sans-serif; font-size: x-small;">“SHOW WARINGS” can certainly help you in grabbing the warnings but what if you are loading a huge dump file or performing a batch operation and at the middle of load operation MySQL reports that the last DML completed with 500000 warnings? Certainly not sounds good if you are serious about your data.</span><br />
<br />
<span style="color: #990000; font-family: "Trebuchet MS", sans-serif; font-size: xx-small;">Query OK, 3000000000 rows affected, 500000 warnings (xxxxx.xxx xxxx)</span><br />
<span style="color: #990000; font-family: "Trebuchet MS", sans-serif; font-size: xx-small;">Records: 3000000000 Duplicates: 0 Warnings: 500000</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;"><br />
<span style="font-size: x-small;"></span></span><br />
<span style="font-family: "Trebuchet MS", sans-serif; font-size: x-small;"><strong>So how do you grab those warnings?</strong></span><br />
<span style="font-family: "Trebuchet MS", sans-serif; font-size: x-small;">You can use the --show-warnings parameter with the “mysql” command line utility which causes warnings to be shown after each statement if there are any. This option applies to interactive and batch mode.</span><br />
<br />
<span style="color: #660000; font-family: "Trebuchet MS", sans-serif; font-size: xx-small;">/path/to/mysql <span style="color: black; font-size: x-small;"><span style="color: blue; font-size: xx-small;">--show-warnings</span> </span>–uUserName –pPassword DBNAME < /path/to/dump_file.sql >> /path/to/load_warnings.log 2>&1</span><br />
<span style="font-family: "Trebuchet MS", sans-serif; font-size: x-small;">The 2>&1 will write standard output and standard errors to a file (/path/to/load_warnings.log)</span><br />
<br />
<span style="font-family: "Trebuchet MS", sans-serif; font-size: x-small;">You may not see warnings</span><br />
<span style="font-family: "Trebuchet MS", sans-serif; font-size: x-small;">(i) If max_error_count is set to 0. In this case, warning_count still indicates how many warnings have occurred, but none of the messages are stored. </span><br />
<span style="font-family: "Trebuchet MS", sans-serif; font-size: x-small;">(ii) if sql_notes session variable is set to 0 to cause Note-level warnings not to be recorded.</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;"><br />
<span style="font-size: x-small;"></span></span><br />
<span style="color: #274e13; font-family: "Trebuchet MS", sans-serif; font-size: x-small;">##On EE</span><br />
<span style="font-family: "Trebuchet MS", sans-serif; font-size: x-small;"><a href="http://www.experts-exchange.com/Database/MySQL/Q_26835461.html">ttp://www.experts-exchange.com/Database/MySQL/Q_26835461.html</a></span><br />
<span style="font-family: "Trebuchet MS", sans-serif;"><br />
<span style="font-size: x-small;"></span></span><br />
<span style="font-family: "Trebuchet MS", sans-serif; font-size: x-small;"></span><div class="blogger-post-footer">7TKU66CE8V5W</div>Umesh Shastryhttp://www.blogger.com/profile/02551756983528645221noreply@blogger.com5tag:blogger.com,1999:blog-5421473080502311460.post-64080095043947512812010-09-01T18:54:00.000+05:302010-09-01T18:54:21.023+05:30MySQL: Kill sleeping connections<span style="color: #666666; font-family: "Trebuchet MS", sans-serif; font-size: xx-small;">Platform: MySQL 5.x</span><br />
<br />
<span style="color: #20124d; font-family: "Trebuchet MS", sans-serif; font-size: x-small;">Most of the time it would be handy to have a native MySQL script which would allow one to kill the sleeping connections which are in sleep state for more than 180 sec.. On the other hand DBA's can use "wait_timeout" etc parameters to control this..</span><br />
<br />
<span style="color: #20124d; font-family: Trebuchet MS; font-size: x-small;">[code]</span><br />
<br />
<span style="font-size: xx-small;"><span style="color: blue;"> # -- Make sure you are logged as MySQL 'root' user or any user who have got super privileges</span></span><span style="color: #20124d; font-family: Trebuchet MS; font-size: x-small;"></span> <br />
<span style="color: blue;"></span><br />
<span style="color: blue;"><br />
</span><br />
<span style="color: blue;">DELIMITER $$ </span><br />
<span style="color: blue;">DROP PROCEDURE IF EXISTS `uKillSleepingSessions`$$</span><br />
<span style="color: blue;"><br />
</span><br />
<span style="color: blue;">CREATE PROCEDURE `uKillSleepingSessions`() </span><br />
<span style="color: blue;">COMMENT 'This routne is used to kill idle sessions'</span><br />
<span style="color: blue;">READS SQL DATA </span><br />
<span style="color: blue;"><br />
</span><br />
<span style="color: blue;">BEGIN </span><br />
<span style="color: blue;"><br />
</span><br />
<span style="color: blue;">DECLARE no_more_rows BOOLEAN;</span><br />
<span style="color: blue;">DECLARE loop_cntr INT DEFAULT 0;</span><br />
<span style="color: blue;">DECLARE num_rows INT DEFAULT 0;</span><br />
<span style="color: blue;">DECLARE uID bigint(4);</span><br />
<br />
<span style="color: blue;">DECLARE my_cur CURSOR FOR</span><br />
<span style="color: blue;">SELECT ID </span><br />
<span style="color: blue;"> FROM information_schema.PROCESSLIST PL</span><br />
<span style="color: blue;">WHERE PL.COMMAND='Sleep' AND PL.TIME > 180;</span><br />
<span style="color: blue;"><br />
</span><br />
<span style="color: blue;">DECLARE CONTINUE HANDLER FOR NOT FOUND</span><br />
<span style="color: blue;">SET no_more_rows = TRUE;</span><br />
<span style="color: blue;"><br />
</span><br />
<span style="color: blue;">OPEN my_cur;</span><br />
<span style="color: blue;">select FOUND_ROWS() into num_rows;</span><br />
<br />
<span style="color: blue;">the_loop: LOOP</span><br />
<span style="color: blue;"><br />
</span><br />
<span style="color: blue;">FETCH my_cur</span><br />
<span style="color: blue;">INTO uID;</span><br />
<span style="color: blue;"><br />
</span><br />
<span style="color: blue;">IF no_more_rows THEN</span><br />
<span style="color: blue;"> CLOSE my_cur;</span><br />
<span style="color: blue;"> LEAVE the_loop;</span><br />
<span style="color: blue;">END IF;</span><br />
<span style="color: blue;"><br />
</span><br />
<span style="color: blue;">SET @tmp_sql= CONCAT("KILL ",uID); </span><br />
<span style="color: blue;">PREPARE s1 FROM @tmp_sql; </span><br />
<span style="color: blue;">EXECUTE s1; </span><br />
<span style="color: blue;"><br />
</span><br />
<span style="color: blue;">DEALLOCATE PREPARE s1; </span><br />
<span style="color: blue;">SET loop_cntr = loop_cntr + 1;</span><br />
<span style="color: blue;">END LOOP the_loop;</span><br />
<span style="color: blue;"><br />
</span><br />
<span style="color: blue;">END$$</span><br />
<span style="color: blue;">DELIMITER ;</span><br />
<br />
<br />
<br />
<span style="color: blue; font-family: Arial, Helvetica, sans-serif;"># Usage from mysql prompt/gui client</span><br />
<span style="color: blue; font-family: Arial, Helvetica, sans-serif;"># Once called , it would cleanup idle connections which are sleeping for more than 180 sec</span><br />
<span style="color: blue; font-family: Arial, Helvetica, sans-serif;">call uKillSleepingSessions();</span><br />
<br />
<span style="color: #20124d; font-family: Trebuchet MS; font-size: x-small;">[/code]</span><div class="blogger-post-footer">7TKU66CE8V5W</div>Umesh Shastryhttp://www.blogger.com/profile/02551756983528645221noreply@blogger.com13tag:blogger.com,1999:blog-5421473080502311460.post-81193117419872890732010-01-22T21:20:00.001+05:302010-01-22T21:22:02.807+05:30Cannot create Windows service for MySQL.Error:0<span style="font-size: 85%;"><span style="font-family: trebuchet ms;"><span style="font-size: 78%;"><span style="color: #006600; font-weight: bold;">Platform: Windows</span> <br />
<br />
<br />
</span><span style="font-family: trebuchet ms;">People often checks MySQL's download section and when they notice that there is new version of MySQL released they just can't wait for a second & starts downloading it so that they can install it on their windows box for testing purpose.</span><span style="font-family: trebuchet ms;"> </span><span style="font-family: trebuchet ms;">Ok fine, download is completed its time to install it. Couple of successful "NEXT" and In the last step they </span></span></span><meta content="text/html; charset=utf-8" equiv="Content-Type"><meta content="Word.Document" name="ProgId"><meta content="Microsoft Word 11" name="Generator"><meta content="Microsoft Word 11" name="Originator"><link href="file:///C:%5CDOCUME%7E1%5CADMINI%7E1%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml" rel="File-List" style="font-family: trebuchet ms;"><style>
<!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} p {mso-margin-top-alt:auto; margin-right:0in; mso-margin-bottom-alt:auto; margin-left:0in; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} pre {margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; tab-stops:45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt; font-size:10.0pt; font-family:"Courier New"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} -->
</style><span style="font-family: trebuchet ms; font-size: 85%;">encounter a error message such as</span> <br />
<pre><span style="font-family: trebuchet ms; font-size: 85%;">"<span style="color: red; font-weight: bold;">Cannot create Windows service for MySQL.Error:0</span>"
We are so intelligent ppl that we don't want to take any help from our colleague..
And what we do is we just delete the directory in which new installation has been done & starts the installation
wizard one-more time in the hope that this time we gonna install it.
Again the result is same </span><span style="font-family: trebuchet ms; font-size: 85%;">"<span style="color: red; font-weight: bold;">Cannot create Windows service for MySQL.Error:0</span>"</span><span style="font-size: 85%;">
</span>
<meta content="text/html; charset=utf-8" equiv="Content-Type"><meta content="Word.Document" name="ProgId"><meta content="Microsoft Word 11" name="Generator"><meta content="Microsoft Word 11" name="Originator"><link href="file:///C:%5CDOCUME%7E1%5CADMINI%7E1%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml" rel="File-List"><style>
<!-- /* Font Definitions */ @font-face {font-family:"Trebuchet MS"; panose-1:2 11 6 3 2 2 2 2 2 4; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:647 0 0 0 159 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} -->
</style><span style="color: #000099; font-family: trebuchet ms;">So why are we getting error that too at the last step? one common thought is "there must be something wrong
or some sort of bug in the new release</span><span style="color: #000099;">"</span>
<span style="font-size: 100%; font-weight: bold;"><span style="font-family: trebuchet ms;">How to resolve this error?</span></span>
<meta content="text/html; charset=utf-8" equiv="Content-Type"><meta content="Word.Document" name="ProgId"><meta content="Microsoft Word 11" name="Generator"><meta content="Microsoft Word 11" name="Originator"><span style="font-family: trebuchet ms;">The primary reason for this error is that you haven't properly uninstalled the </span>
<span style="font-family: trebuchet ms;">previously installed MySQL server & in the installation wizard last step was</span>
<span style="font-family: trebuchet ms;"> to create the service with the name "MySQL" and which has the same name as that </span>
<span style="font-family: trebuchet ms;"> of the existing service (default is "MySQL").</span>
<span style="font-family: trebuchet ms;">Solution for this error is to first stop the service if it is running and then remove </span>
<span style="font-family: trebuchet ms;">the service.</span>
<span style="font-size: 85%;">
<span style="color: #990000; font-size: 78%;"><span style="font-family: trebuchet ms;">C:\>NET STOP MYSQL</span>
<span style="font-family: trebuchet ms;">The MySQL</span>
<span style="font-family: trebuchet ms;">service is stopping.</span>
<span style="font-family: trebuchet ms;">The MySQL service was stopped successfully.</span></span></span>
<span style="color: #660000; font-size: 78%;"><span style="font-family: trebuchet ms;">Once the service is stopped, it can be removed with the SC utility:</span>
<span style="font-family: trebuchet ms;">C:\>SC DELETE MYSQL</span>
<span style="font-family: trebuchet ms;">[SC]</span>
<span style="font-family: trebuchet ms;">DeleteService SUCCESS</span></span>
<span style="font-size: 85%;"><span style="font-family: trebuchet ms;">With the previous service stopped and removed, you can now run the </span><span style="font-family: trebuchet ms;">MySQL Configuration Wizard again to configure and start the new MySQL service</span></span>.</pre><img alt="" border="0" height="1" src="http://www.assoc-amazon.com/e/ir?t=widgetsamazon-20&l=bil&camp=213689&creative=392969&o=1&a=0596101716" style="border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none; margin: 0px; padding-bottom: 0px! important; padding-left: 0px! important; padding-right: 0px! important; padding-top: 0px! important;" width="1" /><iframe align="left" frameborder="0" marginheight="0" marginwidth="0" scrolling="no" src="http://rcm.amazon.com/e/cm?t=widgetsamazon-20&o=1&p=8&l=bpl&asins=B003178588&fc1=000000&IS2=1&lt1=_blank&m=amazon&lc1=0000FF&bc1=000000&bg1=FFFFFF&f=ifr" style="align: left; height: 245px; padding-right: 10px; padding-top: 5px; width: 131px;"></iframe><br />
<br />
<iframe align="left" frameborder="0" marginheight="0" marginwidth="0" scrolling="no" src="http://rcm.amazon.com/e/cm?t=widgetsamazon-20&o=1&p=8&l=bpl&asins=0596101716&fc1=000000&IS2=1&lt1=_blank&m=amazon&lc1=0000FF&bc1=000000&bg1=FFFFFF&f=ifr" style="align: left; height: 245px; padding-right: 10px; padding-top: 5px; width: 131px;"></iframe><iframe align="left" frameborder="0" marginheight="0" marginwidth="0" scrolling="no" src="http://rcm.amazon.com/e/cm?t=widgetsamazon-20&o=1&p=8&l=bpl&asins=0672329387&fc1=000000&IS2=1&lt1=_blank&m=amazon&lc1=0000FF&bc1=000000&bg1=FFFFFF&f=ifr" style="align: left; height: 245px; padding-right: 10px; padding-top: 5px; width: 131px;"></iframe><br />
<br />
<div class="blogger-post-footer">7TKU66CE8V5W</div>Umesh Shastryhttp://www.blogger.com/profile/02551756983528645221noreply@blogger.com38tag:blogger.com,1999:blog-5421473080502311460.post-52172148208705055062010-01-14T16:47:00.002+05:302010-01-14T18:28:54.172+05:30MySQL: Copy tables (Only structure and NO DATA) using stored procedure<span style="font-family: "Trebuchet MS", sans-serif; font-size: x-small;">Last week some one asked how to Copy the tables (Only structure and NO DATA..also no other DB objects) from one schema to another on EE. This can be easily done from command line but user wanted to do this thru stored procedure. </span><br />
<span style="font-family: "Trebuchet MS", sans-serif; font-size: x-small;"><a href="http://www.experts-exchange.com/Database/MySQL/Q_25024073.html">http://www.experts-exchange.com/Database/MySQL/Q_25024073.html</a></span><br />
<span style="font-family: "Trebuchet MS", sans-serif; font-size: x-small;">So I came with a very small MySQL procedure which was doing as needed by the user. I'm not sure whether this is the best way to do this but "<span style="color: magenta;"><strong><em>There is always room for improvement</em></strong></span>."</span><br />
<br />
<span style="font-size: x-small;"><span style="color: #660000;"><span style="font-family: "Trebuchet MS", sans-serif;">DELIMITER $$</span> </span></span><br />
<span style="color: #660000; font-family: "Trebuchet MS", sans-serif; font-size: x-small;"></span><br />
<span style="color: #660000; font-family: "Trebuchet MS", sans-serif; font-size: x-small;">DROP PROCEDURE IF EXISTS `CopySchema`$$</span><br />
<span style="color: #660000; font-family: "Trebuchet MS", sans-serif; font-size: x-small;">CREATE PROCEDURE `CopySchema`(sourceSchema VARCHAR(64),targetSchema VARCHAR(64))</span><br />
<br />
<span style="color: #660000; font-family: "Trebuchet MS", sans-serif; font-size: x-small;"></span><br />
<span style="color: #660000; font-family: "Trebuchet MS", sans-serif; font-size: x-small;">BEGIN </span><br />
<br />
<span style="color: #660000; font-family: "Trebuchet MS", sans-serif; font-size: x-small;">DECLARE no_more_rows BOOLEAN;</span><br />
<span style="color: #660000; font-family: "Trebuchet MS", sans-serif; font-size: x-small;">DECLARE loop_cntr INT DEFAULT 0;</span><br />
<span style="color: #660000; font-family: "Trebuchet MS", sans-serif; font-size: x-small;">DECLARE num_rows INT DEFAULT 0;</span><br />
<span style="color: #660000; font-family: "Trebuchet MS", sans-serif; font-size: x-small;">DECLARE my_table VARCHAR(64);</span><br />
<br />
<span style="color: #660000; font-family: "Trebuchet MS", sans-serif; font-size: x-small;">DECLARE my_cur CURSOR FOR</span><br />
<span style="color: #660000; font-family: "Trebuchet MS", sans-serif; font-size: x-small;">SELECT TABLE_NAME AS myTable</span><br />
<span style="color: #660000; font-family: "Trebuchet MS", sans-serif; font-size: x-small;"> FROM information_schema.TABLES</span><br />
<span style="color: #660000; font-family: "Trebuchet MS", sans-serif; font-size: x-small;">WHERE information_schema.TABLES.TABLE_SCHEMA=sourceSchema AND TABLE_TYPE = 'BASE TABLE';</span><br />
<br />
<span style="color: #660000; font-family: "Trebuchet MS", sans-serif; font-size: x-small;"></span><br />
<span style="color: #660000; font-family: "Trebuchet MS", sans-serif; font-size: x-small;">DECLARE CONTINUE HANDLER FOR NOT FOUND</span><br />
<br />
<span style="color: #660000; font-family: "Trebuchet MS", sans-serif; font-size: x-small;"></span><br />
<span style="color: #660000; font-family: "Trebuchet MS", sans-serif; font-size: x-small;">SET no_more_rows = TRUE;</span><br />
<span style="color: #660000; font-family: "Trebuchet MS", sans-serif; font-size: x-small;">SET @tmp_sql= CONCAT("CREATE DATABASE IF NOT EXISTS ",targetSchema); </span><br />
<span style="color: #660000; font-family: "Trebuchet MS", sans-serif; font-size: x-small;">PREPARE s1 FROM @tmp_sql; </span><br />
<span style="color: #660000; font-family: "Trebuchet MS", sans-serif; font-size: x-small;">EXECUTE s1; </span><br />
<span style="color: #660000; font-family: "Trebuchet MS", sans-serif; font-size: x-small;">DEALLOCATE PREPARE s1; </span><br />
<br />
<span style="color: #660000; font-family: "Trebuchet MS", sans-serif; font-size: x-small;"></span><br />
<span style="color: #660000; font-family: "Trebuchet MS", sans-serif; font-size: x-small;">OPEN my_cur;</span><br />
<span style="color: #660000; font-family: "Trebuchet MS", sans-serif; font-size: x-small;">select FOUND_ROWS() into num_rows;</span><br />
<br />
<span style="color: #660000; font-family: "Trebuchet MS", sans-serif; font-size: x-small;">the_loop: LOOP</span><br />
<span style="color: #660000; font-family: "Trebuchet MS", sans-serif; font-size: x-small;"> FETCH my_cur </span><br />
<span style="color: #660000; font-family: Trebuchet MS; font-size: x-small;"> INTO my_table;</span><br />
<br />
<span style="color: #660000; font-family: "Trebuchet MS", sans-serif; font-size: x-small;"> IF no_more_rows THEN</span><br />
<span style="color: #660000; font-family: "Trebuchet MS", sans-serif; font-size: x-small;"> CLOSE my_cur;</span><br />
<span style="color: #660000; font-family: "Trebuchet MS", sans-serif; font-size: x-small;"> LEAVE the_loop;</span><br />
<span style="color: #660000; font-family: "Trebuchet MS", sans-serif; font-size: x-small;"> END IF;</span><br />
<br />
<span style="color: #660000; font-family: "Trebuchet MS", sans-serif; font-size: x-small;"></span><br />
<span style="color: #660000; font-family: "Trebuchet MS", sans-serif; font-size: x-small;"> SET @tmp_sql= CONCAT("CREATE TABLE IF NOT EXISTS ",targetSchema,'.',my_table,' LIKE ',sourceSchema,'.',my_table); </span><br />
<span style="color: #660000; font-family: "Trebuchet MS", sans-serif; font-size: x-small;"> PREPARE s1 FROM @tmp_sql; </span><br />
<span style="color: #660000; font-family: "Trebuchet MS", sans-serif; font-size: x-small;"> EXECUTE s1; </span><br />
<span style="color: #660000; font-family: "Trebuchet MS", sans-serif; font-size: x-small;"> DEALLOCATE PREPARE s1; </span><br />
<br />
<span style="color: #660000; font-family: "Trebuchet MS", sans-serif; font-size: x-small;"> SET loop_cntr = loop_cntr + 1;</span><br />
<span style="color: #660000; font-family: "Trebuchet MS", sans-serif; font-size: x-small;">END LOOP the_loop;</span><br />
<br />
<span style="color: #660000; font-family: "Trebuchet MS", sans-serif; font-size: x-small;">SELECT CONCAT('Summary: ', loop_cntr, ' tables copied from schema "',sourceSchema,'" to "',targetSchema,'"') AS "Schema copying";</span><br />
<br />
<span style="color: #660000; font-family: "Trebuchet MS", sans-serif; font-size: x-small;"></span><br />
<span style="color: #660000; font-family: "Trebuchet MS", sans-serif; font-size: x-small;">END$$</span><br />
<span style="color: #660000; font-family: "Trebuchet MS", sans-serif; font-size: x-small;">DELIMITER ;</span><br />
<br />
<span style="color: blue; font-family: "Trebuchet MS", sans-serif; font-size: xx-small;">Usage: </span><br />
<br />
<span style="color: blue; font-family: "Trebuchet MS", sans-serif; font-size: xx-small;">call CopySchema('dba','dba_dummy');</span><br />
<br />
<br />
<iframe align="left" frameborder="0" marginheight="0" marginwidth="0" scrolling="no" src="http://rcm.amazon.com/e/cm?t=httpushastrbl-20&o=1&p=8&l=bpl&asins=0596101716&fc1=000000&IS2=1&lt1=_blank&m=amazon&lc1=0000FF&bc1=000000&bg1=FFFFFF&f=ifr" style="align: left; height: 245px; padding-right: 10px; padding-top: 5px; width: 131px;"></iframe><iframe align="left" frameborder="0" marginheight="0" marginwidth="0" scrolling="no" src="http://rcm.amazon.com/e/cm?t=httpushastrbl-20&o=1&p=8&l=bpl&asins=0672329387&fc1=000000&IS2=1&lt1=_blank&m=amazon&lc1=0000FF&bc1=000000&bg1=FFFFFF&f=ifr" style="align: left; height: 245px; padding-right: 10px; padding-top: 5px; width: 131px;"></iframe><iframe align="left" frameborder="0" marginheight="0" marginwidth="0" scrolling="no" src="http://rcm.amazon.com/e/cm?t=httpushastrbl-20&o=1&p=8&l=bpl&asins=0596008643&fc1=000000&IS2=1&lt1=_blank&m=amazon&lc1=0000FF&bc1=000000&bg1=FFFFFF&f=ifr" style="align: left; height: 245px; padding-right: 10px; padding-top: 5px; width: 131px;"></iframe><div class="blogger-post-footer">7TKU66CE8V5W</div>Umesh Shastryhttp://www.blogger.com/profile/02551756983528645221noreply@blogger.com3tag:blogger.com,1999:blog-5421473080502311460.post-57336973314877031312009-11-18T20:00:00.000+05:302009-11-18T20:00:35.583+05:30ERROR 1037 (HY001): Out of memory; restart server and try again (needed 123456789 bytes).<span style="font-family: "Trebuchet MS", sans-serif;">Today morning when I was trying to execute a peice of SQL code and all the time I was welcomed by the below error.</span><br />
<br />
<span style="color: red; font-size: x-small;"><em>ERROR 1037 (HY001): Out of memory; restart server and try again (needed 123456789 bytes).</em></span><br />
<br />
<span style="font-family: "Trebuchet MS", sans-serif;">If we get this kind of error message then the first thing we need to check is the available memory on the server and have to ensure that the server has not run out of disk space for the swap file and also need confirm that there is enough free memory for MySQL to grow.</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;"></span><br />
<span style="font-family: "Trebuchet MS", sans-serif;"> This indicate that we need to increase or decrease the values of some buffers to manage memory usage more efficiently otherwise MySQL might complain about the same thing by means of above error message.</span><div class="blogger-post-footer">7TKU66CE8V5W</div>Umesh Shastryhttp://www.blogger.com/profile/02551756983528645221noreply@blogger.com0tag:blogger.com,1999:blog-5421473080502311460.post-13465896870190667242009-10-30T22:06:00.003+05:302009-12-30T15:51:13.068+05:30MySQL - Extract numbers out of a string<span style="font-family: "Trebuchet MS", sans-serif; font-size: x-small;">Yesterday on EE I saw a very interesting request from a user for "Extracting numbers out of a string".</span><br />
<span style="font-size: x-small;"><span style="font-family: "Trebuchet MS", sans-serif;">This could be done in other languages with just 1 liner code but he needed it inside a SELECT query.</span></span><br />
<span style="font-size: x-small;"><span style="font-family: "Trebuchet MS", sans-serif;"></span> </span><span style="font-family: "Trebuchet MS", sans-serif; font-size: x-small;"><a href="http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_24855357.html">http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_24855357.html</a> </span><span style="font-family: "Trebuchet MS", sans-serif; font-size: x-small;"></span><br />
<br />
<span style="font-family: "Trebuchet MS", sans-serif; font-size: x-small;">So I came with a very small MySQL function which was doing as needed by the user. I'm not sure whther this is the best way to do this but "<strong><em><span style="color: magenta;">There is always room for improvement</span></em></strong>."</span> <br />
<span style="font-family: Trebuchet MS; font-size: x-small;"></span> <br />
<span style="font-family: Trebuchet MS; font-size: x-small;"></span> <br />
<span style="font-family: "Trebuchet MS", sans-serif;"><span style="font-size: x-small;">DELIMITER $$</span> </span><br />
<span style="font-family: "Trebuchet MS", sans-serif; font-size: x-small;"></span><br />
<span style="font-size: x-small;"><br />
<span style="font-family: "Trebuchet MS", sans-serif;"></span></span><br />
<span style="font-family: "Trebuchet MS", sans-serif; font-size: x-small;">DROP FUNCTION IF EXISTS `uExtractNumberFromString`$$</span><br />
<span style="font-family: "Trebuchet MS", sans-serif; font-size: x-small;">CREATE FUNCTION `uExtractNumberFromString`(in_string varchar(50)) RETURNS INT</span><br />
<span style="font-family: "Trebuchet MS", sans-serif; font-size: x-small;">NO SQL</span><br />
<span style="font-size: x-small;"><br />
<span style="font-family: "Trebuchet MS", sans-serif;"></span></span><br />
<span style="font-family: "Trebuchet MS", sans-serif; font-size: x-small;">BEGIN</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;"><br />
</span><br />
<span style="font-family: "Trebuchet MS", sans-serif; font-size: x-small;">DECLARE ctrNumber varchar(50);</span><br />
<span style="font-family: "Trebuchet MS", sans-serif; font-size: x-small;">DECLARE finNumber varchar(50) default ' ';</span><br />
<span style="font-family: "Trebuchet MS", sans-serif; font-size: x-small;">DECLARE sChar varchar(2);</span><br />
<span style="font-family: "Trebuchet MS", sans-serif; font-size: x-small;">DECLARE inti INTEGER default 1;</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;"><br />
</span><br />
<span style="font-family: "Trebuchet MS", sans-serif; font-size: x-small;">IF length(in_string) > 0 THEN</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;"><br />
</span><br />
<span style="font-family: "Trebuchet MS", sans-serif; font-size: x-small;">WHILE(inti <= length(in_string)) DO</span><br />
<span style="font-family: "Trebuchet MS", sans-serif; font-size: x-small;"> SET sChar= SUBSTRING(in_string,inti,1);</span><br />
<span style="font-family: "Trebuchet MS", sans-serif; font-size: x-small;"> SET ctrNumber= FIND_IN_SET(sChar,'0,1,2,3,4,5,6,7,8,9');</span><br />
<span style="font-size: x-small;"><br />
<span style="font-family: "Trebuchet MS", sans-serif;"></span></span><br />
<span style="font-family: "Trebuchet MS", sans-serif; font-size: x-small;"> IF ctrNumber > 0 THEN</span><br />
<span style="font-size: x-small;"><br />
<span style="font-family: "Trebuchet MS", sans-serif;"></span></span><br />
<span style="font-family: "Trebuchet MS", sans-serif; font-size: x-small;"> SET finNumber=CONCAT(finNumber,sChar);</span><br />
<span style="font-family: "Trebuchet MS", sans-serif; font-size: x-small;"> ELSE</span><br />
<span style="font-size: x-small;"><br />
<span style="font-family: "Trebuchet MS", sans-serif;"></span></span><br />
<span style="font-family: "Trebuchet MS", sans-serif; font-size: x-small;"> SET finNumber=CONCAT(finNumber,'');</span><br />
<span style="font-family: "Trebuchet MS", sans-serif; font-size: x-small;"> END IF;</span><br />
<span style="font-size: x-small;"><br />
<span style="font-family: "Trebuchet MS", sans-serif;"></span></span><br />
<span style="font-family: "Trebuchet MS", sans-serif; font-size: x-small;"> SET inti=inti+1;</span><br />
<span style="font-family: "Trebuchet MS", sans-serif; font-size: x-small;">END WHILE;</span><br />
<span style="font-family: "Trebuchet MS", sans-serif; font-size: x-small;">RETURN CAST(finNumber AS SIGNED INTEGER) ;</span><br />
<span style="font-family: "Trebuchet MS", sans-serif; font-size: x-small;">ELSE</span><br />
<span style="font-family: "Trebuchet MS", sans-serif; font-size: x-small;"> RETURN 0;</span><br />
<span style="font-family: "Trebuchet MS", sans-serif; font-size: x-small;">END IF;</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;"><br />
</span><br />
<span style="font-family: "Trebuchet MS", sans-serif; font-size: x-small;">END$$</span><br />
<span style="font-size: x-small;"><br />
</span><br />
<br />
<em><span style="font-size: x-small;">select uExtractNumberFromString('12;e1hhsak123s12');</span></em> <br />
<span style="font-size: x-small;">12112312</span><br />
<br />
<span style="font-family: Trebuchet MS; font-size: x-small;"></span><div class="blogger-post-footer">7TKU66CE8V5W</div>Umesh Shastryhttp://www.blogger.com/profile/02551756983528645221noreply@blogger.com6tag:blogger.com,1999:blog-5421473080502311460.post-87934934986307245432009-10-06T20:24:00.003+05:302009-12-30T15:56:14.946+05:30Client does not support authentication protocol requested by server; consider upgrading MySQL client<span style="font-family: "Trebuchet MS", sans-serif; font-size: x-small;"></span><br />
<span style="color: #cccccc; font-family: "Trebuchet MS", sans-serif; font-size: x-small;">Reference: MySQL Manual</span><br />
<br />
<span style="font-family: "Trebuchet MS", sans-serif;">If you are trying to evaluate some free open source application available on various websites(which are especially developed using PHP) and when the application try to access newer version of MySQL server you may end up seeing a error message like below</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;"><br />
<span style="font-size: x-small;"></span></span><br />
<span style="color: red; font-family: "Trebuchet MS", sans-serif;">Client does not support authentication protocol requested by server; consider upgrading MySQL client</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;"><br />
</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;"> Reason for this unwanted welcome message is that In older versions of PHP, the mysql extension does not support the authentication protocol used in MySQL 4.1.1 and higher. This is true regardless of the PHP version being used. If you wish to use the mysql extension with MySQL 4.1 or newer, you may need to follow one of the options discussed above for configuring MySQL to work with old clients. The mysqli extension (stands for "MySQL, Improved"; added in PHP 5) is compatible with the improved password hashing employed in MySQL 4.1 and higher, and no special configuration of MySQL need be done to use this MySQL client library.</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;"> MySQL >=4.1 uses an authentication protocol based on a password hashing algorithm that is not compatible with that used by older (< 4.1) clients. </span><br />
<span style="font-family: "Trebuchet MS", sans-serif;"><br />
</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;">There are several ways through which you can resolve this type of issues</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;"><br />
</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;">- Tell the server to use the older password hashing algorithm</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;"> (i) Start mysqld with the --old-passwords option(also, you can place old-passwords in my.cnf/my.ini under mysqld group).</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;"> (ii) Assign an old-format password to each account that has had its password updated to the longer 4.1 format. You can identify these accounts with the following query: </span><br />
<span style="font-family: "Trebuchet MS", sans-serif;"> mysql> SELECT Host, User, Password FROM mysql.user</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;"> -> WHERE LENGTH(Password) > 16;</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;">For each account record displayed by the query, use the Host and User values and assign a password using the OLD_PASSWORD() function and either SET PASSWORD or UPDATE</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;"><br />
</span><br />
<span style="font-family: "Trebuchet MS", sans-serif;"></span><br />
<span style="font-family: "Trebuchet MS", sans-serif;">For further options please take a look at the manual page</span><br />
<span style="color: blue; font-family: "Trebuchet MS", sans-serif;">http://dev.mysql.com/doc/refman/5.1/en/old-client.html</span><div class="blogger-post-footer">7TKU66CE8V5W</div>Umesh Shastryhttp://www.blogger.com/profile/02551756983528645221noreply@blogger.com0tag:blogger.com,1999:blog-5421473080502311460.post-11565750190366973602009-09-02T19:37:00.004+05:302009-12-30T15:56:41.208+05:30Scheduled tasks in MySQL<span style="font-family: "Trebuchet MS",sans-serif; font-size: small;">Couple of days ago, during the training session one of the trainee(experienced in other DBMS) was really curious to know on how to setup & use Scheduled tasks in MySQL. I was also equally interested to spend some time on "Event Scheduler" as I was also getting a chance to show that how MySQL is equipped with the rich features but unfortunately the discussion ended before taking off. Most of the ppl felt its just a matter of one line adding to the crontab & job is done. So most of users were not aware of the fact that MySQL has added a feature which could do what the </span><span style="font-size: x-small;"><span style="font-family: "Trebuchet MS",sans-serif;"><span style="font-size: small;">Unix crontab (also known as a “cron job”) or the Windows Task Scheduler can.</span> </span></span><br />
<div style="font-family: 'Trebuchet MS',sans-serif;"><span style="font-size: x-small;"><br />
</span><br />
</div><span style="font-size: small;"><span style="font-family: "Trebuchet MS",sans-serif;">MySQL Events are tasks that run according to a schedule due to this can also be referred as scheduled events. When you create an event, you are creating a named database object containing one or more SQL statements to be executed at one or more regular intervals, beginning and ending at a specific date and time. Conceptually, this is similar to the idea of the Unix crontab (also known as a “cron job”) or the Windows Task Scheduler. </span></span><br />
<div style="font-family: 'Trebuchet MS',sans-serif;"><span style="font-size: x-small;"><br />
</span><br />
</div><div style="font-family: 'Trebuchet MS',sans-serif;"><span style="font-size: small;">(From Manual)</span><br />
</div><div style="font-family: 'Trebuchet MS',sans-serif;"><span style="font-size: small;"><br />
</span><br />
</div><div style="font-family: 'Trebuchet MS',sans-serif;"><span style="font-size: small;">The global <i><b><code class="literal" style="font-family: 'Trebuchet MS',sans-serif;">event_scheduler</code></b></i> system variable determines whether the Event Scheduler is enabled and running on the server. </span><br />
</div><div style="font-family: 'Trebuchet MS',sans-serif;"></div><div style="font-family: 'Trebuchet MS',sans-serif;"><span style="font-size: small;"></span><br />
</div><div style="font-family: 'Trebuchet MS',sans-serif;"><span style="font-size: small;">Version Introduced 5.1.6<br />
Command Line Format --event-scheduler[=value]<br />
Config File Format event-scheduler<br />
Option Sets Variable Yes, event_scheduler<br />
Variable Name event_scheduler<br />
Variable Scope Global<br />
Dynamic Variable Yes<br />
Permitted Values <br />
Type enumeration<br />
Default OFF<br />
Valid Values ON, OFF, DISABLED</span><br />
</div><div style="font-family: 'Trebuchet MS',sans-serif;"></div><div style="font-family: 'Trebuchet MS',sans-serif;"><br />
<span style="font-size: x-small;">From the mysql prompt you can enable/disable using any one of the following statements <br />
</span><br />
</div><div style="font-family: 'Trebuchet MS',sans-serif;"></div><pre class="programlisting" style="color: #4c1130;"><span style="font-family: "Trebuchet MS",sans-serif;"> </span></pre><pre class="programlisting" style="color: #4c1130;"><span style="font-family: "Trebuchet MS",sans-serif; font-size: x-small;">mysql>SET GLOBAL event_scheduler = ON;</span>
<span style="font-family: "Trebuchet MS",sans-serif; font-size: x-small;">mysql></span><span style="font-family: "Trebuchet MS",sans-serif; font-size: x-small;">SET @@global.event_scheduler = ON;</span>
<span style="font-family: "Trebuchet MS",sans-serif; font-size: x-small;">mysql></span><span style="font-family: "Trebuchet MS",sans-serif; font-size: x-small;">SET GLOBAL event_scheduler = 1;</span>
<span style="font-family: "Trebuchet MS",sans-serif; font-size: x-small;">mysql></span><span style="font-family: "Trebuchet MS",sans-serif; font-size: x-small;">SET @@global.event_scheduler = 1;</span>
</pre><br />
<div style="font-family: 'Trebuchet MS',sans-serif;"><span style="font-size: small;">Also, In the server configuration file (<code class="filename" style="font-family: 'Trebuchet MS',sans-serif;">my.cnf</code><span style="font-family: "Trebuchet MS",sans-serif;">, or </span><code class="filename" style="font-family: 'Trebuchet MS',sans-serif;">my.ini</code> on Windows systems), include the line where it will be read by the server (for example, in a <code class="literal" style="font-family: 'Trebuchet MS',sans-serif;">[mysqld]</code> section): </span><br />
</div><div style="font-family: 'Trebuchet MS',sans-serif;"></div><div style="font-family: 'Trebuchet MS',sans-serif;"><span style="font-size: small;">event_scheduler=ON|OFF|DISABLED</span><br />
<span style="font-size: small;"><br />
</span><br />
<span style="font-size: small;">So now we know that what needs to be done at the server level in order to use the feature. Okay the platform is ready for event_scheduler and can demonstrate you with a tiny example. <br />
</span><br />
<span style="font-size: small;"> Just ensuring that event_scheduler is enabled or not</span><br />
<span style="font-size: x-small;"><br />
</span><br />
<div style="font-family: 'Trebuchet MS',sans-serif;"><span style="font-size: x-small;"></span><br />
</div><div style="font-family: 'Trebuchet MS',sans-serif;"><span style="font-size: small;"><i style="color: #4c1130;"><b>show global variables like 'event_scheduler';</b></i><br />
<i style="color: blue;"><b>event_scheduler ON</b></i><br />
</span><br />
</div></div><span style="font-size: small;"><br />
</span><br />
<div style="font-family: 'Trebuchet MS',sans-serif;"><span style="font-size: small;"> The result of the SQL command ensures me that "</span><span style="font-size: small;">event_scheduler" is enabled now and I can use the feature(but this settings should be made permanent by adding it to config file ).<br />
</span><br />
</div><span style="font-size: small;"><br style="font-family: 'Trebuchet MS',sans-serif;" /><b><span style="font-family: "Trebuchet MS",sans-serif;">Syntax for CREATE EVENT</span></b></span><br />
<span style="font-size: small;"><br style="font-family: 'Trebuchet MS',sans-serif;" /></span><br />
<pre style="color: #4c1130; font-family: 'Trebuchet MS',sans-serif;"><span style="font-size: small;">CREATE EVENT [ IF NOT EXISTS ] event_name
ON SCHEDULE schedule
[ ON COMPLETION [ NOT ] PRESERVE ]
[ ENABLED | DISABLED ]
[ COMMENT 'comment' ]
DO sql_statement;</span></pre><span style="font-size: small;"><br />
</span><br />
<span style="font-size: small;"><br />
</span><br />
<br />
<div style="font-family: 'Trebuchet MS',sans-serif;"><span style="font-size: small;">In which - the "event_name" must be a valid identifier of up to 64 characters (e.g me_ushastry, me_trainer etc ushastry1978 etc), with delimiters allowed (`Something To Do`), possibly qualified (database1.event1). Events are database objects, so they are stored within a database and event names must be unique within that database. When checking for uniqueness, MySQL uses case-insensitive comparisons.</span><br />
</div><div style="font-family: 'Trebuchet MS',sans-serif;"><span style="font-size: small;">The "schedule" can be a timestamp in the future, a recurring interval, or a combination of recurring intervals and timestamps.</span><br />
</div><div style="font-family: 'Trebuchet MS',sans-serif;"><span style="font-size: small;">The possibilities are:</span><br />
</div><ul style="font-family: 'Trebuchet MS',sans-serif;"><li><span style="font-size: small;">AT timestamp [+ interval integer_value time_keyword ]</span></li>
<li><span style="font-size: small;">EVERY interval</span></li>
<li><span style="font-size: small;">EVERY interval STARTS timestamp</span></li>
<li><span style="font-size: small;">EVERY interval ENDS timestamp</span></li>
<li><span style="font-size: small;">EVERY interval STARTS timestamp ENDS timestamp</span></li>
</ul><div style="font-family: 'Trebuchet MS',sans-serif;"><span style="font-size: small;">"AT timestamp" means "Do this once at the specified time". The timestamp must contain both date and time (that is, it must be a DATETIME or TIMESTAMP value) and must be in the future -- you cannot make an event which is supposed to have already occurred. To specify an exact time, you can also add an interval to the timestamp (using + INTERVAL, a positive integer and one of YEAR, MONTH, WEEK, DAY, HOUR, MINUTE, or SECOND); this makes sense only when you're using the CURRENT_TIMESTAMP function. Here are two examples:</span><br />
</div><span style="font-size: small;"><br />
</span><br />
<span style="color: #444444; font-family: "Trebuchet MS",sans-serif; font-size: small;"><span style="font-family: "Trebuchet MS",sans-serif;">Scenario is like we have a table named </span></span><span style="font-family: "Trebuchet MS",sans-serif; font-size: small;">`tbl_users` and we have to delete the records from the table every 5 minutes and we want to automate this process so that no one has to sit and run the piece of SQL code manually.</span><span style="color: #444444; font-size: small;"><span style="font-family: "Trebuchet MS",sans-serif;"><br />
</span></span><br />
<span style="font-size: small;"><br />
</span><br />
<span style="color: #444444; font-size: small;"><span style="font-family: "Trebuchet MS",sans-serif;"> </span></span><span style="font-size: small;"><span style="font-family: "Trebuchet MS",sans-serif;"># Checking "event_scheduler" </span><br style="font-family: 'Trebuchet MS',sans-serif;" /><br style="font-family: 'Trebuchet MS',sans-serif;" /><span style="font-family: "Trebuchet MS",sans-serif;"><i style="color: #4c1130;"><b>create database mysqlscheduler;<br />
use mysqlscheduler;</b></i><br />
<br />
<i style="color: #4c1130;"><b>CREATE TABLE IF NOT EXISTS `tbl_users` <br />
( <br />
`id` int(10) unsigned NOT NULL AUTO_INCREMENT, <br />
`name` varchar(100) DEFAULT NULL, <br />
</b></i></span><i style="color: #4c1130;"><b><span style="font-family: "Trebuchet MS",sans-serif;"> `email` varchar(100) DEFAULT NULL, <br />
</span></b></i></span><span style="font-size: small;"><span style="font-family: "Trebuchet MS",sans-serif;"><i style="color: #4c1130;"><b> `promo_emails` enum('YES','NO') DEFAULT 'NO',<br />
PRIMARY KEY (`id`) <br />
) ENGINE=MyISAM; </b></i><br />
</span></span><span style="color: #444444; font-size: small;"><span style="font-family: "Trebuchet MS",sans-serif;"><br />
</span></span><br />
<span style="font-size: small;"><br />
</span><br />
<span style="color: #444444; font-size: small;"><span style="font-family: "Trebuchet MS",sans-serif;"># Add couple of records for testing</span></span><br />
<span style="color: #444444; font-size: small;"><span style="font-family: "Trebuchet MS",sans-serif;"><br />
</span></span><br />
<span style="font-size: small;"><i style="color: #4c1130;"><b><span style="color: #444444;"><span style="font-family: "Trebuchet MS",sans-serif;">INSERT INTO mysqlscheduler.tbl_users VALUES('','Umesh','ushastry@gmail.com','NO'),</span></span><span style="color: #444444;"><span style="font-family: "Trebuchet MS",sans-serif;">('','Umesh','ushastry_duplicate@gmail.com','YES'),</span></span><span style="color: #444444;"><span style="font-family: "Trebuchet MS",sans-serif;">('','Umesh','ushastry_dummy@gmail.com','NO')</span></span></b></i></span><span style="color: #444444; font-size: small;"><span style="font-family: "Trebuchet MS",sans-serif;"><i style="color: #4c1130;"><b>;</b></i><br />
<br />
SELECT * FROM mysqlscheduler.tbl_users;<br />
<br />
1 Umesh ushastry@gmail.com NO<br />
2 Umesh ushastry_duplicate@gmail.com YES<br />
3 Umesh ushastry_dummy@gmail.com NO<br />
\N \N \N NO<br />
</span></span><br />
<span style="font-size: small;"><br />
</span><br />
<span style="color: #444444; font-size: small;"><span style="font-family: "Trebuchet MS",sans-serif;"> Now time to create EVENT which would take care of deleting the records</span></span><br />
<span style="color: #444444; font-size: small;"><span style="font-family: "Trebuchet MS",sans-serif;"><br />
</span></span><br />
<span style="color: #444444; font-size: small;"><span style="font-family: "Trebuchet MS",sans-serif;"><i style="color: #4c1130; font-family: 'Trebuchet MS',sans-serif;"><b>CREATE EVENT drop_dummy_user<br />
ON SCHEDULE EVERY 15 MINUTE<br />
STARTS TIMESTAMP '2009-01-01 00:00:00'<br />
ENDS TIMESTAMP '2009-12-31 23:59:00'<br />
DO DELETE FROM mysqlscheduler.tbl_users WHERE promo_emails='YES';</b></i><br />
</span></span><br />
<span style="font-size: small;"><br />
</span><br />
<span style="color: #444444; font-size: small;"><span style="font-family: "Trebuchet MS",sans-serif;"></span></span><br />
<div style="color: #4c1130; font-family: 'Trebuchet MS',sans-serif;"><span style="color: #444444; font-size: small;">C:\ushastry\MySQL_Com\mysql-5.1.34-win32\bin>mysql -uroot -p</span><br />
</div><div style="color: #4c1130; font-family: 'Trebuchet MS',sans-serif;"><span style="color: #444444; font-size: small;">Welcome to the MySQL monitor. Commands end with ; or \g.<br />
Your MySQL connection id is 26<br />
Server version: 5.1.34-community MySQL Community Server (GPL)<br />
<br />
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.<br />
</span><br />
</div><div style="color: #4c1130; font-family: 'Trebuchet MS',sans-serif;"><span style="font-size: small;">mysql> SELECT * FROM mysql.event\G<br />
*************************** 1. row ***************************<br />
db: mysqlscheduler<br />
name: drop_dummy_user<br />
body: DELETE FROM mysqlscheduler.tbl_users WHERE promo_emails='YES'<br />
definer: root@localhost<br />
execute_at: NULL<br />
interval_value: 15<br />
interval_field: MINUTE<br />
created: 2009-09-02 14:51:45<br />
modified: 2009-09-02 14:51:45<br />
last_executed: NULL<br />
starts: 2009-01-01 00:00:00<br />
ends: 2009-12-31 23:59:00<br />
status: ENABLED<br />
on_completion: DROP<br />
sql_mode:<br />
comment:<br />
originator: 1<br />
time_zone: SYSTEM<br />
character_set_client: utf8<br />
collation_connection: utf8_general_ci<br />
db_collation: latin1_swedish_ci<br />
body_utf8: DELETE FROM mysqlscheduler.tbl_users WHERE promo_emails='YES'<br />
1 row in set (0.00 sec)<br />
<br />
mysql></span><br />
</div><span style="font-size: small;"><br />
</span><br />
<div style="font-family: 'Trebuchet MS',sans-serif;"><span style="font-size: small;">Above command output can be read as<br />
</span><br />
</div><ul style="font-family: 'Trebuchet MS',sans-serif;"><li><span style="font-size: small;">the db column shows the name of the database that contains the event</span></li>
<li><span style="font-size: small;">the name column holds the name of the event; recall that it must be unique within the database</span></li>
<li><span style="font-size: small;">the definer column shows whose privileges will be checked when the event is executed; if the user shown no longer has the proper privileges to execute the event's SQL statement, an error will be returned</span></li>
<li><span style="font-size: small;">the execute_at column shows when the event should be executed; this is always NULL unless the ON SCHEDULE clause uses the AT timestamp option</span></li>
<li><span style="font-size: small;">the interval_value column shows the numeric value of how often the event will be executed; this value will be NULL if the ON SCHEDULE clause uses the AT timestamp option</span></li>
<li><span style="font-size: small;">the interval_field columns shows the datetime value of how often the event will be executed; this value will be NULL if the ON SCHEDULE clause uses the AT timestamp option</span></li>
<li><span style="font-size: small;">the created column shows the timestamp when the event was created</span></li>
<li><span style="font-size: small;">the modified column shows the timestamp when the event was last modified; this will be the same as the created column if ALTER EVENT has never changed the event</span></li>
<li><span style="font-size: small;">the last_executed column shows the timestamp of the last time the event was executed; in this example, it is NULL because the newly-created event hasn't been executed yet</span></li>
<li><span style="font-size: small;">the starts column shows the timestamp when the event will start executing; this value will be NULL if the ON SCHEDULE clause uses the AT timestamp option</span></li>
<li><span style="font-size: small;">the ends column shows the timestamp when the event will stop executing; this value will be NULL if the ON SCHEDULE clause uses the AT timestamp option</span></li>
<li><span style="font-size: small;">the status column shows whether the event is currently enabled or disabled; in this example, it shows ENABLED, the default value</span></li>
<li><span style="font-size: small;">the on_completion column shows whether the event will be preserved or dropped when it is finished; in this example, it shows that the event will be preserved, as specified in the CREATE EVENT statement</span></li>
<li><span style="font-size: small;">the comment column shows the comment included in the CREATE EVENT statement</span></li>
</ul><span style="color: #444444; font-size: small;"><span style="font-family: "Trebuchet MS",sans-serif;">After 15 min or so you can check and confirm that the records has been deleted </span></span><br />
<span style="color: #444444; font-size: small;"><span style="font-family: "Trebuchet MS",sans-serif;"><br />
</span></span><br />
<span style="color: #444444; font-size: small;"><span style="font-family: "Trebuchet MS",sans-serif;"><i style="color: #4c1130;"><b>SELECT * FROM mysqlscheduler.tbl_users;</b></i><br />
<br />
1 Umesh ushastry@gmail.com NO<br />
3 Umesh ushastry_dummy@gmail.com NO<br />
\N \N \N NO</span></span><br />
<span style="font-size: small;"><br />
</span><br />
<span style="color: #444444; font-size: small;"><span style="font-family: "Trebuchet MS",sans-serif;">Also, can be verified from the event meta data that when it was executed last time <br />
</span></span><br />
<span style="font-size: small;"><br />
</span><br />
<span style="color: #444444; font-size: xx-small;"><span style="font-family: "Trebuchet MS",sans-serif;"><span style="font-size: small;">mysql> SELECT * FROM mysql.event\G<br />
*************************** 1. row ***************************<br />
db: mysqlscheduler<br />
name: drop_dummy_user<br />
body: DELETE FROM mysqlscheduler.tbl_users WHERE promo_emails='YES'<br />
definer: root@localhost<br />
execute_at: NULL<br />
interval_value: 15<br />
interval_field: MINUTE<br />
created: 2009-09-02 14:51:45<br />
modified: 2009-09-02 14:51:45<br />
<b style="color: blue;">last_executed: 2009-09-02 14:00:00</b><br />
starts: 2009-01-01 00:00:00<br />
ends: 2009-12-31 23:59:00<br />
status: ENABLED<br />
on_completion: DROP<br />
sql_mode:<br />
comment:<br />
originator: 1<br />
time_zone: SYSTEM<br />
character_set_client: utf8<br />
collation_connection: utf8_general_ci<br />
db_collation: latin1_swedish_ci<br />
body_utf8: DELETE FROM mysqlscheduler.tbl_users WHERE promo_emails='YES'<br />
1 row in set (0.00 sec)</span><br />
</span></span><br />
<br />
<br />
<span style="color: #444444; font-size: xx-small;"><span style="font-family: "Trebuchet MS",sans-serif;">More details can be found here - http://dev.mysql.com/doc/refman/5.1/en/events.html</span></span><br />
<br />
<div style="font-family: 'Trebuchet MS',sans-serif;"><span style="font-size: x-small;"></span><br />
</div><div class="blogger-post-footer">7TKU66CE8V5W</div>Umesh Shastryhttp://www.blogger.com/profile/02551756983528645221noreply@blogger.com3tag:blogger.com,1999:blog-5421473080502311460.post-37781714649697702012009-08-07T19:53:00.010+05:302009-12-30T15:57:00.755+05:30MySQL - Restoring a single table from nightly backup (mysqldump generated file)<span style="font-family: trebuchet ms;">Very often we get the requests from customer to restore a "tiny" table from the nightly backup into the development/staging environment. This request need to be done on urgent basis as its just a matter of a one table and customer don't want to restore entire database of 10GB so the restoration might take less than 5 min - this is how most of the customer/managers thinks and they are abs right as why should it take more time for a single table?. This can be addressed by playing with the privileges and with the help of parameters available to 'mysql' command line utility<br />
<br />
Steps to restore single table from dump file<br />
<br />
# Create a user & GRANT him all rights on the table which need to be restored<br />
# Start importing as show below<br />
<br />
</span><span style="color: #660000; font-family: trebuchet ms; font-style: italic; font-weight: bold;">mysql -uRestUser -pSecret --force --one-database DB_</span><span style="color: #660000; font-family: trebuchet ms; font-style: italic; font-weight: bold;">NAME</span><span style="color: #660000; font-style: italic; font-weight: bold;"><span style="font-family: trebuchet ms;"> < /path/to/dumpfile.sql</span></span><br />
<br />
<span style="font-family: trebuchet ms;">ResetUser - "user" created for restoring single table</span><br />
<br />
<span style="font-family: trebuchet ms;">--one-database - Ignore statements except those for the default database named on the command line<br />
<br />
</span><span style="font-family: trebuchet ms;">--force - Parameter would force the import to continue even if an SQL error occurs( mainly due to the privileges as the new user don't have the privilege on other tables)</span><br />
<br />
<span style="font-size: 85%;"><span style="font-family: trebuchet ms;"><br />
<br />
<br />
<br />
</span></span><div class="blogger-post-footer">7TKU66CE8V5W</div>Umesh Shastryhttp://www.blogger.com/profile/02551756983528645221noreply@blogger.com4tag:blogger.com,1999:blog-5421473080502311460.post-44603186380460575372009-07-30T22:18:00.003+05:302009-12-30T15:57:18.626+05:30Step-by-Step guide for Installing MySQL on RHEL5<span style="font-family: trebuchet ms; font-size: 85%;"><b>Installation of MySQL Server on Linux (RedHat Enterprise Linux 5)<br />
<br />
</b></span><span style="font-size: 85%;"><span style="font-family: trebuchet ms;">The recommended way to install MySQL on RPM-based Linux distributions is by using the RPM packages. The RPMs that MySQL/Sun provide to the community should work on all versions of Linux that support RPM packages and use </span></span><span style="font-family: Courier New; font-size: 85%;">glibc</span><span style="font-size: 85%;"><span style="font-family: trebuchet ms;"> 2.3. You can download it from http://mirrors.sunsite.dk/mysql/downloads/mysql/5.1.html</span><br />
<br />
<span style="font-family: trebuchet ms;">Checking if MySQL is already installed & would be uninstalling it to install latest MySQL version</span><br />
<br />
</span><span style="font-family: Courier New; font-size: 85%;">[root@localhost mysql]# rpm -qa | grep -i '^mysql-'<br />
MySQL-python-1.2.1-1<br />
mysql-5.0.22-2.1.0.1<br />
mysql-server-5.0.22-2.1.0.1<br />
mysql-connector-odbc-3.51.12-2.2<br />
<br />
Un-installing older version of MySQL<br />
<br />
[root@localhost mysql]# rpm --nodeps -ev MySQL-python-1.2.1-1<br />
[root@localhost mysql]# rpm --nodeps -ev mysql-5.0.22-2.1.0.1<br />
[root@localhost mysql]# rpm --nodeps -ev mysql-server-5.0.22-2.1.0.1<br />
warning: /var/log/mysqld.log saved as /var/log/mysqld.log.rpmsave<br />
[root@localhost mysql]# rpm --nodeps -ev mysql-connector-odbc-3.51.12-2.2<br />
[root@localhost mysql]#<br />
<br />
</span><span style="font-size: 85%;"><br />
<span style="font-family: trebuchet ms;">First, using a web browser or <b>wget</b> utility, you should download the server RPM from the location: http://www.mysql.com/Downloads/MySQL-*.rpm<br />
Once you download the packages, install it as shown below. <br />
<br />
</span></span><span style="font-family: Courier New; font-size: 85%;"><br />
[root@localhost mysql]# ls -la<br />
total 39572<br />
drwxr-xr-x 2 root root 4096 Jul 26 17:26 .<br />
drwxr-xr-x 3 root root 4096 Jul 26 16:40 ..<br />
-rw-r--r-- 1 root root 6664128 Jun 11 14:07 MySQL-client-community-5.0.83-0.rhel5.i386.rpm<br />
-rw-r--r-- 1 root root 10616677 Jun 11 14:17 MySQL-devel-community-5.0.83-0.rhel5.i386.rpm<br />
-rw-r--r-- 1 root root 19613508 Jun 11 14:26 MySQL-server-community-5.0.83-0.rhel5.i386.rpm<br />
-rw-r--r-- 1 root root 3532055 Jun 11 14:31 MySQL-shared-compat-5.0.83-0.rhel5.i386.rpm<br />
<br />
Here is brief details of packages that we are going to install<br />
<br />
</span><span style="font-family: Courier New; font-size: 85%;"><b>MySQL-server-community-5.0.83-0.rhel5.i386.rpm:</b> </span><span style="font-size: 85%;"><span style="font-family: trebuchet ms;">The MySQL server. You need this unless you only want to connect to a MySQL server running on another machine. </span><br />
<b style="font-family: trebuchet ms;">MySQL-client-community-5.0.83-0.rhel5.i386.rpm:</b><span style="font-family: trebuchet ms;"> The standard MySQL client programs. You probably always want to install this package. </span><br />
<b style="font-family: trebuchet ms;">MySQL-devel-community-5.0.83-0.rhel5.i386.rpm:</b><span style="font-family: trebuchet ms;"> The libraries and include files that are needed if you want to compile other MySQL clients, such as the Perl modules.</span><br />
<b style="font-family: trebuchet ms;">MySQL-shared-compat-5.0.83-0.rhel5.i386.rpm:</b><span style="font-family: trebuchet ms;"> This package includes the shared libraries for MySQL 3.23, 4.0, and so on, up to the current release. It contains single-threaded and thread-safe libraries. Install this package instead of </span></span><span style="font-family: Courier New; font-size: 85%;">MySQL-shared</span><span style="font-size: 85%;"><span style="font-family: trebuchet ms;"> if you have applications installed that are dynamically linked against older versions of MySQL but you want to upgrade to the current version without breaking the library dependencies. </span><br />
<br />
<br />
<span style="font-family: trebuchet ms;">To perform a standard minimal installation, you can only install the server and client RPMs and for complete installation you have to install all the packages.</span><br />
<br />
<br />
<br />
</span><span style="font-family: Courier New; font-size: 85%;">[root@localhost mysql]#<b style="color: blue;"> rpm -ivh MySQL-*.rpm</b><br />
Preparing... ########################################### [100%]<br />
1:MySQL-shared-compat ########################################### [ 25%]<br />
2:MySQL-client-community ########################################### [ 50%]<br />
3:MySQL-devel-community ########################################### [ 75%]<br />
4:MySQL-server-community ########################################### [100%]<br />
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !<br />
To do so, start the server, then issue the following commands:<br />
/usr/bin/mysqladmin -u root password 'new-password'<br />
/usr/bin/mysqladmin -u root -h localhost.localdomain password 'new-password'<br />
<br />
Alternatively you can run:<br />
/usr/bin/mysql_secure_installation<br />
<br />
which will also give you the option of removing the test<br />
databases and anonymous user created by default. This is<br />
strongly recommended for production servers.<br />
<br />
See the manual for more instructions.<br />
<br />
Please report any problems with the /usr/bin/mysqlbug script!<br />
<br />
The latest information about MySQL is available on the web at<br />
http://www.mysql.com<br />
Support MySQL by buying support/licenses at http://shop.mysql.com<br />
Starting MySQL..[ OK ]<br />
Giving mysqld 2 seconds to start<br />
[root@localhost mysql]#<br />
<br />
<br />
</span><span style="font-size: 85%;"><span style="font-family: trebuchet ms;">The server RPM places data under the </span></span><span style="font-family: Courier New; font-size: 85%;">/var/lib/mysql</span><span style="font-size: 85%;"><span style="font-family: trebuchet ms;"> directory. The RPM also creates a login account for a user named </span></span><span style="font-family: Courier New; font-size: 85%;">mysql</span><span style="font-size: 85%;"><span style="font-family: trebuchet ms;"> (if one does not exist) to use for running the MySQL server, and creates the appropriate entries in </span></span><span style="font-family: Courier New; font-size: 85%;">/etc/init.d/</span><span style="font-size: 85%;"><span style="font-family: trebuchet ms;"> to start the server automatically at boot time.</span><br />
<br />
</span><br />
<div style="font-family: trebuchet ms;"><span style="font-size: 85%;">If you want to install the MySQL RPM on older Linux distributions that do not support initialization scripts in /etc/init.d (directly or via a symlink), you should create a symbolic link that points to the location where your initialization scripts actually are installed. For example, if that location is /etc/rc.d/init.d, use these commands before installing the RPM to create /etc/init.d as a symbolic link that points there: </span><br />
</div><span style="font-family: Courier New; font-size: 85%;">shell> <b>cd /etc</b><br />
shell> <b>ln -s rc.d/init.d .</b><br />
</span><br />
<div style="font-family: trebuchet ms;"><span style="font-size: 85%;">However, all current major Linux distributions should support the new directory layout that uses /etc/init.d, because it is required for LSB (Linux Standard Base) compliance. </span><br />
</div><div style="font-family: trebuchet ms;"><span style="font-size: 85%;">If the RPM files that you install include MySQL-server, the <b>mysqld</b> server should be up and running after installation. You should be able to start using MySQL. </span><br />
</div><span style="font-size: 85%;"><br />
<br />
</span><br />
<h4 style="font-family: trebuchet ms;"><span style="font-size: 85%;">Securing the Server</span></h4><span style="font-size: 85%;"><br />
</span><span style="font-family: Courier New; font-size: 85%;"><span style="color: blue;">[root@localhost mysql]# <b>/usr/bin/mysql_secure_installation</b></span><br />
<br />
<br />
<br />
<br />
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL<br />
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!<br />
<br />
<br />
In order to log into MySQL to secure it, we'll need the current<br />
password for the root user. If you've just installed MySQL, and<br />
you haven't set the root password yet, the password will be blank,<br />
so you should just press enter here.<br />
<br />
Enter current password for root (enter for none):<br />
OK, successfully used password, moving on...<br />
<br />
Setting the root password ensures that nobody can log into the MySQL<br />
root user without the proper authorisation.<br />
<br />
Set root password? [Y/n] y<br />
New password:<br />
Re-enter new password:<br />
Password updated successfully!<br />
Reloading privilege tables..<br />
... Success!<br />
<br />
<br />
By default, a MySQL installation has an anonymous user, allowing anyone<br />
to log into MySQL without having to have a user account created for<br />
them. This is intended only for testing, and to make the installation<br />
go a bit smoother. You should remove them before moving into a<br />
production environment.<br />
<br />
Remove anonymous users? [Y/n] y<br />
... Success!<br />
<br />
Normally, root should only be allowed to connect from 'localhost'. This<br />
ensures that someone cannot guess at the root password from the network.<br />
<br />
Disallow root login remotely? [Y/n] y<br />
... Success!<br />
<br />
By default, MySQL comes with a database named 'test' that anyone can<br />
access. This is also intended only for testing, and should be removed<br />
before moving into a production environment.<br />
<br />
Remove test database and access to it? [Y/n] y<br />
- Dropping test database...<br />
... Success!<br />
- Removing privileges on test database...<br />
... Success!<br />
<br />
Reloading the privilege tables will ensure that all changes made so far<br />
will take effect immediately.<br />
<br />
Reload privilege tables now? [Y/n] y<br />
... Success!<br />
<br />
Cleaning up...<br />
<br />
<br />
<br />
All done! If you've completed all of the above steps, your MySQL<br />
installation should now be secure.<br />
<br />
Thanks for using MySQL!<br />
<br />
<br />
<span style="color: blue;">[root@localhost mysql]#</span><br />
<br />
<br />
</span><span style="font-size: 85%;"><br />
</span><br />
<h4 style="font-family: trebuchet ms;"><span style="font-size: 85%;">Test mysql connectivity</span></h4><span style="font-size: 85%;"><span style="font-family: trebuchet ms;">Type the following command to connect to MySQL server:</span><br />
<br />
</span><span style="font-family: Courier New; font-size: 85%;"><br />
<span style="color: blue;">[root@localhost mysql]# <b>mysql -u root -p</b></span><br />
<span style="color: blue;">Enter password: </span><br />
<span style="color: blue;">Welcome to the MySQL monitor. Commands end with ; or \g.</span><br />
<span style="color: blue;">Your MySQL connection id is 10</span><br />
<span style="color: blue;">Server version: 5.0.83-community MySQL Community Edition (GPL)</span><br />
<br />
<span style="color: blue;">Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.</span><br />
<br />
<span style="color: blue;">mysql> </span><br />
</span><div class="blogger-post-footer">7TKU66CE8V5W</div>Umesh Shastryhttp://www.blogger.com/profile/02551756983528645221noreply@blogger.com17tag:blogger.com,1999:blog-5421473080502311460.post-84051156354295004462009-07-28T09:32:00.007+05:302009-12-30T15:58:08.397+05:30ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)<span style="font-size: 85%;"><span style="font-family: trebuchet ms;"><span style="color: #999999; font-size: 78%;">Platform: Linux/Unix</span> <br />
<br />
If you come across below errors when trying to connect MySQL then there might be couple of reasons behind it.</span> </span><meta content="text/html; charset=utf-8" equiv="Content-Type"><meta content="Word.Document" name="ProgId"><meta content="Microsoft Word 11" name="Generator"><meta content="Microsoft Word 11" name="Originator"><link href="file:///C:%5CDOCUME%7E1%5CADMINI%7E1%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml" rel="File-List" style="font-family: trebuchet ms;"><style>
<!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} -->
</style><span style="font-size: 85%;"><span style="font-size: 78%;"><span style="font-family: trebuchet ms; font-size: 12px;"><span style="font-size: 85%;"><br />
<br />
<br />
</span></span></span></span><br />
<pre style="color: red; font-family: trebuchet ms;"><span style="font-family: trebuchet ms; font-size: 85%;">-bash-3.2$ mysql --user=root --password
</span><span style="font-size: 85%;"><span style="font-family: trebuchet ms;">Enter password:</span>
</span></pre><span style="font-size: 85%;"><span style="font-size: 78%;"><span style="font-family: trebuchet ms; font-size: 12px;"><span style="color: red; font-family: trebuchet ms; font-size: 100%; font-weight: bold;">ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/</span><span style="font-size: 85%;"><span style="color: red; font-family: trebuchet ms; font-size: 100%; font-weight: bold;">mysql.sock' (2)</span> <br />
<br />
</span></span></span><span style="font-family: trebuchet ms;">One of the reason could be that MySQL server isn't running. So how do you check whether or not MySQL is running? try running below shell command and see what it reports <br />
<br />
</span><br />
<span style="color: #660000; font-family: trebuchet ms; font-size: 78%; font-style: italic; font-weight: bold;">-bash-3.2$ ps -e|grep mysqld <br />
30878 ? 00:00:00 mysqld_safe <br />
30939 ? 00:58:06 mysqld <br />
-bash-3.2$ </span><br />
<br />
<br />
<span style="font-family: trebuchet ms;">If you see at least 2 process are running/listed from the above command then it is confirmed that MySQL is running. </span></span><meta content="text/html; charset=utf-8" equiv="Content-Type"><meta content="Word.Document" name="ProgId"><meta content="Microsoft Word 11" name="Generator"><meta content="Microsoft Word 11" name="Originator"><link href="file:///C:%5CDOCUME%7E1%5CADMINI%7E1%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml" rel="File-List"><style>
<!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} p {mso-margin-top-alt:auto; margin-right:0in; mso-margin-bottom-alt:auto; margin-left:0in; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} tt {font-family:"Courier New"; mso-ascii-font-family:"Courier New"; mso-fareast-font-family:"Times New Roman"; mso-hansi-font-family:"Courier New"; mso-bidi-font-family:"Courier New";} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} -->
</style><br />
<span style="font-size: 85%;"><span style="font-family: trebuchet ms;">What if you don't see any process(as shown below)? then the MySQL services is not at all running then try to start it and watch for errors(you can check error log for same) and resolve them before making an attempt to start.</span></span><br />
<span style="font-size: 85%;"><span style="color: #660000; font-family: trebuchet ms; font-size: 78%; font-style: italic; font-weight: bold;">-bash-3.2$ ps -e|grep mysqld <br />
-bash-3.2$ </span></span><br />
<div style="font-family: trebuchet ms;"><span style="font-size: 85%;">Starting MySQL(easiest way :-)) <br />
</span><br />
</div><div style="font-weight: bold;"><span style="color: #660000; font-size: 78%;"><span style="font-family: trebuchet ms;">/etc/init.d/mysql start</span></span><br />
</div><span style="font-size: 85%;"><span style="font-family: trebuchet ms;"><span style="font-family: trebuchet ms;">If there are no errors, re-enter the process status(ps) command once again & if the service is now running, then try connecting to MySQL and </span></span></span><span style="font-family: trebuchet ms;"><span style="font-size: 85%;">If still you are receiving the error then <span style="font-family: trebuchet ms;">another reason(wasn't first one) could be if the location of the Unix socket has been changed or is different from the default location</span></span>.</span> <br />
<meta content="Microsoft Word 11" name="Originator"><link href="file:///C:%5CDOCUME%7E1%5CADMINI%7E1%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml" rel="File-List"><style>
<!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} p {mso-margin-top-alt:auto; margin-right:0in; mso-margin-bottom-alt:auto; margin-left:0in; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} pre {margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; tab-stops:45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt; font-size:10.0pt; font-family:"Courier New"; mso-fareast-font-family:"Times New Roman";} tt {font-family:"Courier New"; mso-ascii-font-family:"Courier New"; mso-fareast-font-family:"Times New Roman"; mso-hansi-font-family:"Courier New"; mso-bidi-font-family:"Courier New";} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} -->
</style><br />
<div style="font-family: trebuchet ms;"><span style="font-size: 85%;">It might be that the location of the socket file has been changed so first one has to check the MyQL config file (<span style="font-family: trebuchet ms; font-size: 85%;">/etc/my.cnf</span>) and locate the the value for the <span style="font-size: 85%;">socket</span> parameter(you may see two socket details - for client and mysqld groups).</span><br />
</div><div style="color: #660000; font-family: trebuchet ms; font-weight: bold;"><span style="font-size: 78%;">-bash-3.2$ more /etc/my.cnf |grep socket <br />
socket=/var/lib/mysql/mysql.sock <br />
-bash-3.2$ <br />
</span><br />
</div><div style="font-family: trebuchet ms;"><span style="font-size: 85%;"><span style="font-size: 78%;"><span style="color: #660000; font-weight: bold;"></span></span>It could be that the value for the <span style="font-size: 85%;">--socket</span> option is set at the command-line when MySQL is started is different than what the client <span style="font-size: 85%;">mysql</span> is expecting in that case you have to either change the socket file path in the config file or you will have to supply the socket parameter with its path for the the client</span><br />
</div><pre style="color: #660000; font-family: trebuchet ms; font-weight: bold;"><span style="color: #000099; font-family: trebuchet ms; font-size: 78%; font-weight: normal;">-bash-3.2$ mysql --user=root --password --socket=/var/lib/mysql/mysql.sock
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 421830
Server version: 5.0.45 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql></span>
</pre><div class="blogger-post-footer">7TKU66CE8V5W</div>Umesh Shastryhttp://www.blogger.com/profile/02551756983528645221noreply@blogger.com12tag:blogger.com,1999:blog-5421473080502311460.post-4683090373166655002009-07-21T18:52:00.003+05:302009-12-30T15:58:27.856+05:30Resetting the Root Password on Unix/Linix Systems<span style="font-size: 85%;"><span style="font-family: trebuchet ms;">Log on to your system as the Unix mysql user that the mysqld server runs as. </span><br />
<br />
<span style="font-family: trebuchet ms;">Locate the .pid file that contains the server's process ID. The exact location and name of this file depend on your distribution, host name, and configuration. Common locations are /var/lib/mysql/, /var/run/mysqld/, and /usr/local/mysql/data/. Generally, the file name has an extension of .pid and begins with either mysqld or your system's host name. </span><br />
<br />
<span style="font-family: trebuchet ms;">You can stop the MySQL server by sending a normal kill (not kill -9) to the mysqld process, using the path name of the .pid file in the following command: </span><br />
<br />
<span style="color: #660000; font-family: trebuchet ms; font-size: 78%;">shell> kill `cat /mysql-data-directory/host_name.pid`</span><br />
<br />
<span style="font-family: trebuchet ms;">Note the use of backticks rather than forward quotes with the cat command; these cause the output of cat to be substituted into the kill command. </span><br />
<br />
<span style="font-family: trebuchet ms;">Create a text file and place the following statements in it. Replace the password with the password that you want to use. </span><br />
<br />
<span style="color: #660000; font-family: trebuchet ms; font-size: 78%;">UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';<br />
FLUSH PRIVILEGES;</span><br />
<br />
<span style="font-family: trebuchet ms;">The UPDATE and FLUSH statements each must be written on a single line. The UPDATE statement resets the password for all existing root accounts, and the FLUSH statement tells the server to reload the grant tables into memory. </span><br />
<br />
<span style="font-family: trebuchet ms;">Save the file. For this example, the file will be named /home/me/mysql-init. The file contains the password, so it should not be saved where it can be read by other users. </span><br />
<br />
<span style="font-family: trebuchet ms;">Start the MySQL server with the special --init-file option: </span><br />
<br />
<span style="color: #660000; font-family: trebuchet ms; font-size: 78%;">shell> mysqld_safe --init-file=/home/me/mysql-init &</span><br />
<br />
<span style="font-family: trebuchet ms;">The server executes the contents of the file named by the --init-file option at startup, changing each root account password. </span><br />
<br />
<span style="font-family: trebuchet ms;"><span style="font-family: trebuchet ms;">After the server has started successfully, delete /home/me/mysql-init.</span><br />
<br />
<span style="color: #999999; font-family: trebuchet ms; font-size: 78%;">Reference: MySQL Manual</span><br />
</span><br />
</span><div class="blogger-post-footer">7TKU66CE8V5W</div>Umesh Shastryhttp://www.blogger.com/profile/02551756983528645221noreply@blogger.com0tag:blogger.com,1999:blog-5421473080502311460.post-22955610912715850832009-07-21T13:38:00.006+05:302009-12-30T15:58:45.894+05:30MySQL - Which engine to use?<span style="font-size: 85%;"><span style="font-family: trebuchet ms;">it is good to have an idea of the advantages and disadvantages of the various storage engines. The following table provides an overview of some storage engines provided with MySQL<br />
<br />
</span></span><br />
<a href="http://2.bp.blogspot.com/_rxMvxNbPwwM/SmV4bpw4UYI/AAAAAAAAEMw/w-nqza6pskc/s1600-h/MySQL_Engine_Overview.JPG" onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5360823347917115778" src="http://2.bp.blogspot.com/_rxMvxNbPwwM/SmV4bpw4UYI/AAAAAAAAEMw/w-nqza6pskc/s400/MySQL_Engine_Overview.JPG" style="cursor: pointer; height: 247px; width: 400px;" /></a><br />
<br />
<span style="font-size: 85%;"><span style="font-family: trebuchet ms;"><br />
</span></span><span style="font-size: 78%;"><span style="font-family: trebuchet ms; font-size: 85%;"><span style="color: #999999; font-size: 78%;">Reference: MySQL Manual</span></span></span><span style="font-size: 85%;"><span style="font-family: trebuchet ms;"><br />
<br />
</span></span><div class="blogger-post-footer">7TKU66CE8V5W</div>Umesh Shastryhttp://www.blogger.com/profile/02551756983528645221noreply@blogger.com2tag:blogger.com,1999:blog-5421473080502311460.post-50018112168244072752009-07-09T17:55:00.004+05:302009-12-30T15:59:35.379+05:30Choosing Which Version of MySQL to Install<div style="font-family: trebuchet ms;"><span style="font-size: 85%;">The first decision to make is whether someone want to use a production (stable) release or a development release. In the MySQL development process, multiple release series co-exist, each at a different stage of maturity:<br />
</span><br />
</div><div style="font-family: trebuchet ms;"><span style="font-size: 85%;">At the time of writing this:<br />
</span><br />
</div><ul style="font-family: trebuchet ms;"><li><br />
<span style="font-size: 85%;">MySQL 5.4 and 6.0 are the current development release series. </span><br />
<br />
</li>
<li><br />
<span style="font-size: 85%;">MySQL 5.1 is the current General Availability (Production) release series. New releases are issued for bugfixes only; no new features are being added that could affect stability. </span><br />
<br />
</li>
<li><br />
<span style="font-size: 85%;">MySQL 5.0 is the previous stable (production-quality) release series. </span><br />
<br />
</li>
<li><br />
<span style="font-size: 85%;">MySQL 4.1, 4.0, and 3.23 are old stable (production-quality) release series. MySQL 4.1 is now at the end of the product lifecycle. Active development and support for these versions has ended. </span><br />
<br />
</li>
</ul><span style="font-family: trebuchet ms; font-size: 85%;">The MySQL naming scheme uses release names that consist of three numbers and a suffix; for example, <span class="bold"><strong>mysql-5.0.12-beta</strong></span>. The numbers within the release name are interpreted as follows: </span><br />
<div class="itemizedlist" style="font-family: trebuchet ms;"><ul><li style="font-family: trebuchet ms;"><br />
<span style="font-size: 85%;">The first number (<span class="bold"><strong>5</strong></span>) is the major version and describes the file format. All MySQL 5 releases have the same file format. </span><br />
<br />
</li>
<li style="font-family: trebuchet ms;"><br />
<span style="font-size: 85%;">The second number (<span class="bold"><strong>0</strong></span>) is the release level. Taken together, the major version and release level constitute the release series number. </span><br />
<br />
</li>
<li><br />
<span style="font-size: 85%;"><span style="font-family: trebuchet ms;">The third number (</span><span class="bold" style="font-family: trebuchet ms;"><strong>12</strong></span>) is the version number within the release series. This is incremented for each new release. Usually you want the latest version for the series you have chosen. </span><br />
<br />
</li>
</ul></div><br />
<div style="font-family: trebuchet ms;"><span style="font-size: 85%;">Release names also include a suffix to indicates the stability level of the release. Releases within a series progress through a set of suffixes to indicate how the stability level improves. The possible suffixes are: </span><br />
</div><div class="itemizedlist" style="font-family: trebuchet ms;"><ul><li><br />
<span style="font-size: 85%;"><span class="bold"><strong>alpha</strong></span> indicates that the release is for preview purposes only. Known bugs should be documented in the News section . Most alpha releases implement new commands and extensions. Active development that may involve major code changes can occur in an alpha release. However, we do conduct testing before issuing a release. </span><br />
<br />
</li>
<li><br />
<span style="font-size: 85%;"><span class="bold"><strong>beta</strong></span> indicates that the release is appropriate for use with new development. Within beta releases, the features and compatibility should remain consistent. However, beta releases may contain numerous and major unaddressed bugs. </span><br />
<span style="font-size: 85%;">All APIs, externally visible structures, and columns for SQL statements will not change during future beta, release candidate, or production releases. </span><br />
<br />
</li>
<li><br />
<span style="font-size: 85%;"><span class="bold"><strong>rc</strong></span> indicates a Release Candidate. Release candidates are believed to be stable, having passed all of MySQL's internal testing, and with all known fatal runtime bugs fixed. However, the release has not been in widespread use long enough to know for sure that all bugs have been identified. Only minor fixes are added. (A release candidate is what formerly was known as a gamma release.) </span><br />
<br />
</li>
<li><br />
<span style="font-size: 85%;">If there is no suffix, it indicates that the release is a General Availability (GA) or Production release. GA releases are stable, having successfully passed through all earlier release stages and are believed to be reliable, free of serious bugs, and suitable for use in production systems. Only critical bugfixes are applied to the release. </span><br />
<br />
</li>
</ul></div><div style="font-family: trebuchet ms;"><span style="font-size: 85%;">MySQL uses a naming scheme that is slightly different from most other products. In general, it is usually safe to use any version that has been out for a couple of weeks without being replaced by a new version within the same release series. </span><br />
</div><div class="blogger-post-footer">7TKU66CE8V5W</div>Umesh Shastryhttp://www.blogger.com/profile/02551756983528645221noreply@blogger.com0tag:blogger.com,1999:blog-5421473080502311460.post-74923077112540413402009-06-25T20:21:00.011+05:302009-12-30T16:00:32.968+05:30MySQL - Commonly used SQL commands<span style="font-family: trebuchet ms; font-size: 85%;">Most commonly used MySQL specific SQL Commands - Now I don't have to google for them every time I forget the syntax.</span><span style="font-size: 85%;"> <br />
<br />
<br />
<span style="font-family: trebuchet ms; font-weight: bold;">GRANT <br />
<br />
</span></span><meta content="text/html; charset=utf-8" equiv="Content-Type"><meta content="Word.Document" name="ProgId"><meta content="Microsoft Word 11" name="Generator"><meta content="Microsoft Word 11" name="Originator"><link href="file:///C:%5CDOCUME%7E1%5CDBA%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml" rel="File-List"><style>
<!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} code {font-family:"Courier New"; mso-ascii-font-family:"Courier New"; mso-fareast-font-family:"Times New Roman"; mso-hansi-font-family:"Courier New"; mso-bidi-font-family:"Courier New";} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} -->
</style><br />
<div class="MsoNormal" style="font-family: trebuchet ms;"><span style="font-size: 85%;">The <code><b><u><span style="color: blue;">GRANT</span></u></b></code> statement enables system administrators to create MySQL user accounts and to grant rights to accounts. To use <code><b><u><span style="color: blue;">GRANT</span></u></b></code>, you must have the <code><b><u><span style="color: blue;">GRANT</span></u></b></code><code><u><span style="color: blue;"> OPTION</span></u></code> privilege, and you must have the privileges that you are granting. The <span style="font-weight: bold;">REVOKE</span><a href="http://dev.mysql.com/doc/refman/5.0/en/revoke.html" title="12.5.1.5. REVOKE Syntax"><code></code></a> statement is related and enables administrators to remove account privileges. To determine what privileges an account has, use <code><b><u><span style="color: blue;">SHOW GRANTS</span></u></b></code><b>.</b></span><br />
</div><br />
<pre class="programlisting"><span style="font-weight: bold;">
</span><span style="font-family: trebuchet ms; font-size: 85%;">GRANT
<em class="replaceable"><code>priv_type</code></em> [(<em class="replaceable"><code>column_list</code></em>)]
[, <em class="replaceable"><code>priv_type</code></em> [(<em class="replaceable"><code>column_list</code></em>)]] ...
ON [<em class="replaceable"><code>object_type</code></em>] <em class="replaceable"><code>priv_level</code></em>
TO <em class="replaceable"><code>user</code></em> [IDENTIFIED BY [PASSWORD] '<em class="replaceable"><code>password</code></em>']
[, <em class="replaceable"><code>user</code></em> [IDENTIFIED BY [PASSWORD] '<em class="replaceable"><code>password</code></em>']] ...
[REQUIRE {NONE | <em class="replaceable"><code>ssl_option</code></em> [[AND] <em class="replaceable"><code>ssl_option</code></em>] ...}]
[WITH <em class="replaceable"><code>with_option</code></em> [<em class="replaceable"><code>with_option</code></em>] ...]
<em class="replaceable"><code>object_type</code></em>:
TABLE
| FUNCTION
| PROCEDURE
<em class="replaceable"><code>priv_level</code></em>:
*
| *.*
| <em class="replaceable"><code>db_name</code></em>.*
| <em class="replaceable"><code>db_name.tbl_name</code></em>
| <em class="replaceable"><code>tbl_name</code></em>
| <em class="replaceable"><code>db_name</code></em>.<em class="replaceable"><code>routine_name</code></em>
<em class="replaceable"><code>with_option</code></em>:
GRANT OPTION
| MAX_QUERIES_PER_HOUR <em class="replaceable"><code>count</code></em>
| MAX_UPDATES_PER_HOUR <em class="replaceable"><code>count</code></em>
| MAX_CONNECTIONS_PER_HOUR <em class="replaceable"><code>count</code></em>
| MAX_USER_CONNECTIONS <em class="replaceable"><code>count</code></em>
<em class="replaceable"><code>ssl_option</code></em>:
SSL
| X509
| CIPHER '<em class="replaceable"><code>cipher</code></em>'
| ISSUER '<em class="replaceable"><code>issuer</code></em>'
| SUBJECT '<em class="replaceable"><code>subject</code></em>'</span>
<span style="font-weight: bold;">
</span></pre><h4 class="title" style="font-family: trebuchet ms;"><code class="literal">REVOKE</code></h4><pre class="programlisting"><span style="font-weight: bold;">SHOW VARIABLES</span></pre><span style="font-size: 85%;"><span style="font-family: trebuchet ms;">SHOW [GLOBAL | SESSION] VARIABLES</span> <br />
</span><br />
<pre class="programlisting"><span style="font-size: 85%;"><span style="font-family: trebuchet ms;"> [LIKE '</span><em class="replaceable" style="font-family: trebuchet ms;"><code>pattern</code></em><span style="font-family: trebuchet ms;">' | WHERE </span><em class="replaceable" style="font-family: trebuchet ms;"><code>expr</code></em><span style="font-family: trebuchet ms;">]</span></span>
</pre><meta content="text/html; charset=utf-8" equiv="Content-Type"><meta content="Word.Document" name="ProgId"><meta content="Microsoft Word 11" name="Generator"><meta content="Microsoft Word 11" name="Originator"><link href="file:///C:%5CDOCUME%7E1%5CDBA%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml" rel="File-List"><style>
<!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} p {mso-margin-top-alt:auto; margin-right:0in; mso-margin-bottom-alt:auto; margin-left:0in; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} code {font-family:"Courier New"; mso-ascii-font-family:"Courier New"; mso-fareast-font-family:"Times New Roman"; mso-hansi-font-family:"Courier New"; mso-bidi-font-family:"Courier New";} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} -->
</style><br />
<div style="font-family: trebuchet ms;"><span style="font-size: 85%;"><code><u><span style="color: blue;">SHOW VARIABLES</span></u></code> shows the values of MySQL system variables. This information also can be obtained using mysqladmin <a href="http://dev.mysql.com/doc/refman/5.0/en/mysqladmin.html" title="4.5.2. mysqladmin — Client for Administering a MySQL Server"><strong></strong></a>command. The <code>LIKE</code> clause, if present, indicates which variable names to match. The <code>WHERE</code> clause can be given to select rows using more general conditions. </span><br />
</div><br />
<span style="font-family: trebuchet ms; font-size: 85%;">With the <code face="trebuchet ms">GLOBAL</code> modifier, <code face="trebuchet ms">SHOW VARIABLES</code> displays the values that are used for new connections to MySQL. With <code face="trebuchet ms">SESSION</code>, it displays the values that are in effect for the current connection. If no modifier is present, the default is <code face="trebuchet ms">SESSION</code>. <code face="trebuchet ms">LOCAL</code> is a synonym for <code face="trebuchet ms">SESSION</code>. </span><br />
<div class="MsoNormal"><br />
</div><span style="font-family: trebuchet ms; font-size: 85%; font-weight: bold;">SHOW STATUS</span><span style="font-size: 85%;"> <br />
</span><span style="font-family: trebuchet ms; font-size: 85%;"><a href="http://dev.mysql.com/doc/refman/5.0/en/show-status.html" title="12.5.5.32. SHOW STATUS Syntax"><code class="literal"></code></a><span style="font-size: 85%;">SHOW STATUS provides server status information. The LIKE clause, if present, indicates which variable names to match</span> </span><br />
<pre class="programlisting" style="font-family: trebuchet ms;"><span style="font-size: 85%;"><span style="font-family: trebuchet ms;">SHOW [GLOBAL | SESSION] STATUS</span>
<span style="font-family: trebuchet ms;"> [LIKE '</span><em class="replaceable" style="font-family: trebuchet ms;"><code>pattern</code></em><span style="font-family: trebuchet ms;">' | WHERE </span><em class="replaceable" style="font-family: trebuchet ms;"><code>expr</code></em><span style="font-family: trebuchet ms;">]</span></span>
</pre><span style="font-size: 85%;">With a <span style="font-weight: bold;">LIKE</span> <a href="http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html#operator_like"><code class="literal"></code></a>clause, the statement displays only rows for those variables with names that match the pattern: </span><br />
<pre class="programlisting">mysql> <strong class="userinput"><code>SHOW STATUS LIKE 'Key%';</code></strong>
+--------------------+----------+
| Variable_name | Value |
+--------------------+----------+
| Key_blocks_used | 14955 |
| Key_read_requests | 96854827 |
| Key_reads | 162040 |
| Key_write_requests | 7589728 |
| Key_writes | 3813196 |
+--------------------+----------+
</pre><br />
<pre class="programlisting"><span style="font-weight: bold;">SHOW SLAVE STATUS</span>
<span style="font-size: 100%;"><span style="font-family: trebuchet ms;">This statement provides status information on essential parameters of the slave </span><span style="font-family: trebuchet ms;"> threads.
If you issue this statement using the `mysql`<span style="text-decoration: underline;"><span style="font-weight: bold;"></span></span></span><a href="http://dev.mysql.com/doc/refman/5.0/en/mysql.html" style="font-family: trebuchet ms;" title="4.5.1. mysql — The MySQL Command-Line Tool"><strong class="command"></strong></a><span style="font-family: trebuchet ms;"> client, you can use a </span><code class="literal" style="font-family: trebuchet ms;">\G</code><span style="font-family: trebuchet ms;"> </span><span style="font-family: trebuchet ms;"> statement terminator
rather than a semicolon to obtain a more readable vertical </span><span style="font-family: trebuchet ms;"> layout:</span></span>
mysql> <strong class="userinput"><code>SHOW SLAVE STATUS\G</code></strong>
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: root
Master_Port: 3306
Connect_Retry: 3
<span style="font-weight: bold;">SHOW BINARY LOGS / SHOW MASTER LOGS</span>
<span style="font-family: trebuchet ms;">Lists the binary log files on the server</span>
mysql> <strong class="userinput"><code>SHOW BINARY LOGS;</code></strong>
+---------------+-----------+
| Log_name | File_size |
+---------------+-----------+
| binlog.000015 | 724935 |
| binlog.000016 | 733481 |
+---------------+-----------+
<span style="font-weight: bold;">SHOW [STORAGE] ENGINES</span>
<span style="font-size: 100%; font-weight: bold;">
<span style="font-family: trebuchet ms;"></span></span><span style="font-family: trebuchet ms; font-size: 100%;">SHOW ENGINES displays status information about the server's storage engines.
This is particularly useful for checking whether a storage engine is
supported, or to see what the default engine is. <code class="literal">
SHOW TABLE TYPES</code> is a deprecated synonym.</span>
<span style="font-family: trebuchet ms; font-size: 85%;">mysql> <strong class="userinput"><code>SHOW ENGINES\G</code></strong>
*************************** 1. row ***************************
Engine: MyISAM
Support: DEFAULT
Comment: Default engine as of MySQL 3.23 with great performance
*************************** 2. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
*************************** 3. row ***************************
Engine: HEAP
Support: YES
Comment: Alias for MEMORY
--
</span><span style="font-family: trebuchet ms; font-size: 85%;">*************************** 4. row ***************************
Engine: InnoDB
Support: YES
</span>
<span style="font-family: trebuchet ms; font-size: 85%;">
</span></pre><code class="literal" style="font-weight: bold;">SHOW ERRORS</code> <br />
<br />
<pre class="programlisting">SHOW ERRORS [LIMIT [<em class="replaceable"><code>offset</code></em>,] <em class="replaceable"><code>row_count</code></em>]
SHOW COUNT(*) ERRORS
</pre><div style="font-family: trebuchet ms;"><span style="font-size: 85%;">This statement is similar to <span style="font-weight: bold;">SHOW WARNINGS</span><a href="http://dev.mysql.com/doc/refman/5.0/en/show-warnings.html" title="12.5.5.37. SHOW WARNINGS Syntax"><code class="literal"></code></a>, except that instead of displaying errors, warnings, and notes, it displays only errors. </span><br />
</div><h4 class="title"><code class="literal">SHOW WARNINGS</code></h4><span style="font-size: 85%;"><span style="font-family: trebuchet ms;">SHOW WARNINGS [LIMIT [</span><em class="replaceable" style="font-family: trebuchet ms;"><code>offset</code></em><span style="font-family: trebuchet ms;">,] </span><em class="replaceable" style="font-family: trebuchet ms;"><code>row_count</code></em><span style="font-family: trebuchet ms;">]</span> </span><br />
<pre class="programlisting" style="font-family: trebuchet ms;"><span style="font-size: 85%;">SHOW COUNT(*) WARNINGS
</span></pre><div style="font-family: trebuchet ms;"><span style="font-family: trebuchet ms; font-weight: bold;">show warnings</span><span style="font-family: trebuchet ms;"> </span><span style="font-family: trebuchet ms; font-size: 85%;">shows the error, warning, and note messages that resulted from the last statement that generated messages in the current session. It shows nothing if the last statement used a table and generated no messages. (That is, a statement that uses a table but generates no messages clears the message list.) Statements that do not use tables and do not generate messages have no effect on the message list. </span><br />
</div><div face="trebuchet ms"><meta content="text/html; charset=utf-8" equiv="Content-Type"><meta content="Word.Document" name="ProgId"><meta content="Microsoft Word 11" name="Generator"><meta content="Microsoft Word 11" name="Originator"><link href="file:///C:%5CDOCUME%7E1%5CDBA%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml" rel="File-List"><style>
<!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} code {font-family:"Courier New"; mso-ascii-font-family:"Courier New"; mso-fareast-font-family:"Times New Roman"; mso-hansi-font-family:"Courier New"; mso-bidi-font-family:"Courier New";} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} -->
</style><br />
</div><div class="MsoNormal" style="font-family: trebuchet ms;"><span style="font-size: 85%;">Warnings are generated for DML statements such as <code><u><span style="color: blue;">INSERT</span></u></code>, <code><u><span style="color: blue;">UPDATE</span></u></code>, and <code><u><span style="color: blue;">LOAD DATA INFILE</span></u></code> as well as DDL statements such as <code><u><span style="color: blue;">CREATE TABLE</span></u></code> and <code><u><span style="color: blue;">ALTER TABLE</span></u></code>.</span><br />
</div><br />
<br />
<div style="font-family: trebuchet ms;"><span style="font-size: 85%;"><a href="http://dev.mysql.com/doc/refman/5.0/en/show.html" title="12.5.5. SHOW Syntax"><code class="literal"></code></a><span style="font-family: trebuchet ms; font-size: 85%; font-weight: bold;">SHOW</span><span style="font-size: 85%;"><span style="font-family: trebuchet ms;"> many forms that provide information about databases, tables, columns, or status information about the server. This section describes those following: </span></span></span><br />
</div><pre class="programlisting" style="font-family: trebuchet ms;"><span style="font-family: trebuchet ms; font-size: 85%;">SHOW CHARACTER SET [<em class="replaceable"><code>like_or_where</code></em>]
SHOW COLLATION [<em class="replaceable"><code>like_or_where</code></em>]
SHOW [FULL] COLUMNS FROM <em class="replaceable"><code>tbl_name</code></em> [FROM <em class="replaceable"><code>db_name</code></em>] [<em class="replaceable"><code>like_or_where</code></em>]
SHOW CREATE DATABASE <em class="replaceable"><code>db_name</code></em>
SHOW CREATE FUNCTION <em class="replaceable"><code>func_name</code></em>
SHOW CREATE PROCEDURE <em class="replaceable"><code>proc_name</code></em>
SHOW CREATE TABLE <em class="replaceable"><code>tbl_name</code></em>
SHOW DATABASES [<em class="replaceable"><code>like_or_where</code></em>]
SHOW ENGINE <em class="replaceable"><code>engine_name</code></em> {LOGS | STATUS }
SHOW [STORAGE] ENGINES
SHOW ERRORS [LIMIT [<em class="replaceable"><code>offset</code></em>,] <em class="replaceable"><code>row_count</code></em>]
SHOW FUNCTION CODE <em class="replaceable"><code>func_name</code></em>
SHOW FUNCTION STATUS [<em class="replaceable"><code>like_or_where</code></em>]
SHOW GRANTS FOR <em class="replaceable"><code>user</code></em>
SHOW INDEX FROM <em class="replaceable"><code>tbl_name</code></em> [FROM <em class="replaceable"><code>db_name</code></em>]
SHOW INNODB STATUS
SHOW PROCEDURE CODE <em class="replaceable"><code>proc_name</code></em>
SHOW PROCEDURE STATUS [<em class="replaceable"><code>like_or_where</code></em>]
SHOW [BDB] LOGS
SHOW MUTEX STATUS
SHOW OPEN TABLES [FROM <em class="replaceable"><code>db_name</code></em>] [<em class="replaceable"><code>like_or_where</code></em>]
SHOW PRIVILEGES
SHOW [FULL] PROCESSLIST
SHOW PROFILE [<em class="replaceable"><code>types</code></em>] [FOR QUERY <em class="replaceable"><code>n</code></em>] [OFFSET <em class="replaceable"><code>n</code></em>] [LIMIT <em class="replaceable"><code>n</code></em>]
SHOW PROFILES
SHOW [GLOBAL | SESSION] STATUS [<em class="replaceable"><code>like_or_where</code></em>]
SHOW TABLE STATUS [FROM <em class="replaceable"><code>db_name</code></em>] [<em class="replaceable"><code>like_or_where</code></em>]
SHOW TABLES [FROM <em class="replaceable"><code>db_name</code></em>] [<em class="replaceable"><code>like_or_where</code></em>]
SHOW TRIGGERS [FROM <em class="replaceable"><code>db_name</code></em>] [<em class="replaceable"><code>like_or_where</code></em>]
SHOW [GLOBAL | SESSION] VARIABLES [<em class="replaceable"><code>like_or_where</code></em>]
SHOW WARNINGS [LIMIT [<em class="replaceable"><code>offset</code></em>,] <em class="replaceable"><code>row_count</code></em>]
<em class="replaceable"><code>like_or_where</code></em>:
LIKE '<em class="replaceable"><code>pattern</code></em>'
| WHERE <em class="replaceable"><code>expr</code></em>
</span>
<span style="font-size: 85%;"><span style="font-weight: bold;">
ALTER TABLE</span></span>
</pre><span style="font-size: 85%;"><span style="font-family: trebuchet ms; font-size: 85%;">ALTER [IGNORE] TABLE <em class="replaceable"><code>tbl_name</code></em></span> <br />
</span><br />
<pre class="programlisting"><span style="font-family: trebuchet ms; font-size: 85%;"> <em class="replaceable"><code>alter_specification</code></em> [, <em class="replaceable"><code>alter_specification</code></em>] ...
<em class="replaceable"><code>alter_specification</code></em>:
<em class="replaceable"><code>table_options</code></em>
| ADD [COLUMN] <em class="replaceable"><code>col_name</code></em> <em class="replaceable"><code>column_definition</code></em>
[FIRST | AFTER <em class="replaceable"><code>col_name</code></em> ]
| ADD [COLUMN] (<em class="replaceable"><code>col_name</code></em> <em class="replaceable"><code>column_definition</code></em>,...)
| ADD {INDEX|KEY} [<em class="replaceable"><code>index_name</code></em>]
[<em class="replaceable"><code>index_type</code></em>] (<em class="replaceable"><code>index_col_name</code></em>,...) [<em class="replaceable"><code>index_type</code></em>]
| ADD [CONSTRAINT [<em class="replaceable"><code>symbol</code></em>]] PRIMARY KEY
[<em class="replaceable"><code>index_type</code></em>] (<em class="replaceable"><code>index_col_name</code></em>,...) [<em class="replaceable"><code>index_type</code></em>]
| ADD [CONSTRAINT [<em class="replaceable"><code>symbol</code></em>]]
UNIQUE [INDEX|KEY] [<em class="replaceable"><code>index_name</code></em>]
[<em class="replaceable"><code>index_type</code></em>] (<em class="replaceable"><code>index_col_name</code></em>,...) [<em class="replaceable"><code>index_type</code></em>]
| ADD [FULLTEXT|SPATIAL] [INDEX|KEY] [<em class="replaceable"><code>index_name</code></em>]
(<em class="replaceable"><code>index_col_name</code></em>,...) [<em class="replaceable"><code>index_type</code></em>]
| ADD [CONSTRAINT [<em class="replaceable"><code>symbol</code></em>]]
FOREIGN KEY [<em class="replaceable"><code>index_name</code></em>] (<em class="replaceable"><code>index_col_name</code></em>,...)
<em class="replaceable"><code>reference_definition</code></em>
| ALTER [COLUMN] <em class="replaceable"><code>col_name</code></em> {SET DEFAULT <em class="replaceable"><code>literal</code></em> | DROP DEFAULT}
| CHANGE [COLUMN] <em class="replaceable"><code>old_col_name</code></em> <em class="replaceable"><code>new_col_name</code></em> <em class="replaceable"><code>column_definition</code></em>
[FIRST|AFTER <em class="replaceable"><code>col_name</code></em>]
| MODIFY [COLUMN] <em class="replaceable"><code>col_name</code></em> <em class="replaceable"><code>column_definition</code></em>
[FIRST | AFTER <em class="replaceable"><code>col_name</code></em>]
| DROP [COLUMN] <em class="replaceable"><code>col_name</code></em>
| DROP PRIMARY KEY
| DROP {INDEX|KEY} <em class="replaceable"><code>index_name</code></em>
| DROP FOREIGN KEY <em class="replaceable"><code>fk_symbol</code></em>
| DISABLE KEYS
| ENABLE KEYS
| RENAME [TO] <em class="replaceable"><code>new_tbl_name</code></em>
| ORDER BY <em class="replaceable"><code>col_name</code></em> [, <em class="replaceable"><code>col_name</code></em>] ...
| CONVERT TO CHARACTER SET <em class="replaceable"><code>charset_name</code></em> [COLLATE <em class="replaceable"><code>collation_name</code></em>]
| [DEFAULT] CHARACTER SET [=] <em class="replaceable"><code>charset_name</code></em> [COLLATE [=] <em class="replaceable"><code>collation_name</code></em>]
| DISCARD TABLESPACE
| IMPORT TABLESPACE
<em class="replaceable"><code>index_col_name</code></em>:
<em class="replaceable"><code>col_name</code></em> [(<em class="replaceable"><code>length</code></em>)] [ASC | DESC]
<em class="replaceable"><code>index_type</code></em>:
USING {BTREE | HASH | RTREE}
<em class="replaceable"><code>table_options</code></em>:
<em class="replaceable"><code>table_option</code></em> [[,] <em class="replaceable"><code>table_option</code></em>] ...</span>
<span style="color: #3333ff; font-weight: bold;">To be continued...</span>
</pre><div class="blogger-post-footer">7TKU66CE8V5W</div>Umesh Shastryhttp://www.blogger.com/profile/02551756983528645221noreply@blogger.com1tag:blogger.com,1999:blog-5421473080502311460.post-76454013203676471452009-06-25T16:30:00.002+05:302009-12-30T16:01:14.798+05:30MySQL - Sample backup script<span style="color: #660000; font-size: 78%;"><span style="font-family: trebuchet ms;">Platform - Linux<br />
<br />
<span style="font-size: 85%;">Sample script to backup MySQL DBs</span><br />
<br />
<span style="color: black; font-size: 85%;">#!/bin/bash<br />
<br />
# Pls change these<br />
</span></span></span><span style="color: #660000; font-size: 78%;"><span style="font-family: trebuchet ms;"><span style="color: black; font-size: 85%;">db_host='localhost'<br />
</span></span></span><span style="color: #660000; font-size: 78%;"><span style="font-family: trebuchet ms;"><span style="color: black; font-size: 85%;">db_user='backupuser'<br />
</span></span></span><span style="color: #660000; font-size: 78%;"><span style="font-family: trebuchet ms;"><span style="color: black; font-size: 85%;">db_user='secretpassword'<br />
</span></span></span><span style="color: #660000; font-size: 78%;"><span style="font-family: trebuchet ms;"><span style="color: black; font-size: 85%;">#<br />
# BACKUP_DIR - Need to change as per HOST/DB environment<br />
#<br />
BACKUP_DIR="/backups/`hostname -s`/mysql"<br />
<br />
if [ ! -d $BACKUP_DIR ] ; then<br />
mkdir -p $BACKUP_DIR<br />
chmod 700 $BACKUP_DIR<br />
fi<br />
<br />
# Pls donot touch below code<br />
<br />
DATE=`date +"%Y%m%d"`<br />
MYSQLDUMP="$(which mysqldump)"<br />
LOG_FILE=${BACKUP_DIR}/runlog${DATE}.log<br />
MYSQL="$(which mysql)"<br />
<br />
DB_LIST="$($MYSQL -u$db_user -h$db_host -p$db_pass -Bse 'show databases')"<br />
<br />
exec 2> ${LOG_FILE}<br />
<br />
MYSQLDUMP_OPT="${MYSQLDUMP} -u${db_user} -p${db_pass} -h${db_host} --opt"<br />
<br />
for DB_NAME in ${DB_LIST} ; do<br />
<br />
if [ ! -d ${BACKUP_DIR}/${DB_NAME} ] ; then<br />
mkdir -p ${BACKUP_DIR}/${DB_NAME}<br />
fi<br />
<br />
${MYSQLDUMP_OPT} ${DB_NAME} | gzip -9 > ${BACKUP_DIR}/${DB_NAME}/${DATE}.dmp.gz<br />
done<br />
<br />
if [ -s ${LOG_FILE} ]; then<br />
<br />
mail -s "Alert- Backup failure `hostname -s` " me@hostname.com < ${LOG_FILE} else #Cleanup process..Delete backup files which are one week old find $BACKUP_DIR -type f -name "*.dmp.gz" -mtime +7 -exec rm {} \; mail -s "Notification - Backup done `hostname -s` " </span></span></span><span style="color: #660000; font-size: 78%;"><span style="font-family: trebuchet ms;"><span style="color: black; font-size: 85%;">me@hostname.com < ${LOG_FILE}</span></span></span><span style="color: #660000; font-size: 78%;"><span style="font-family: trebuchet ms;"><span style="color: black; font-size: 85%;"> <br />
fi</span><br />
</span></span><div class="blogger-post-footer">7TKU66CE8V5W</div>Umesh Shastryhttp://www.blogger.com/profile/02551756983528645221noreply@blogger.com0tag:blogger.com,1999:blog-5421473080502311460.post-9432158221798724802009-06-24T16:28:00.022+05:302009-12-30T16:02:12.507+05:30Step-by-Step guide for Installing MySQL on Windows<span style="font-size: 85%;"><span style="font-family: trebuchet ms;">You can download the MySQL database from the MySQL website http://www.mysql.com by clicking on the <span style="font-weight: bold;">downloads</span> tab. Scroll down to the MySQL database server & standard clients section and select the latest production release of MySQL, 5.1.35 at the time of writing.</span> <span style="font-family: trebuchet ms; font-weight: bold;"><br />
<br />
Installation of MySQL Server</span> <br />
<br />
<span style="font-family: trebuchet ms;">Unzip the setup file and execute the downloaded MSI file. Follow the instructions below exactly when installing MySQL Server:</span></span> <br />
<br />
<div style="text-align: center;"><a href="http://4.bp.blogspot.com/_rxMvxNbPwwM/SkIhLSN6KzI/AAAAAAAAD-k/aa8FHYdJi_A/s1600-h/1.JPG" onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5350875785022024498" src="http://4.bp.blogspot.com/_rxMvxNbPwwM/SkIhLSN6KzI/AAAAAAAAD-k/aa8FHYdJi_A/s400/1.JPG" style="cursor: pointer; height: 306px; width: 400px;" /></a> <br />
</div><div style="text-align: center;"><span style="font-size: 85%;"><span style="font-family: trebuchet ms;">Click on the "setup"</span></span> <br />
</div><br />
<span style="font-size: 85%;"><span style="font-family: trebuchet ms;"><br />
</span></span><br />
<div style="text-align: center;"><a href="http://2.bp.blogspot.com/_rxMvxNbPwwM/SkIhTycKJUI/AAAAAAAAD-s/l__JaSnnN7g/s1600-h/2.JPG" onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5350875931110679874" src="http://2.bp.blogspot.com/_rxMvxNbPwwM/SkIhTycKJUI/AAAAAAAAD-s/l__JaSnnN7g/s400/2.JPG" style="cursor: pointer; height: 306px; width: 400px;" /></a> <br />
<span style="font-size: 85%;"><span style="font-family: trebuchet ms;">Perform a typical installation <br />
<br />
</span></span><a href="http://4.bp.blogspot.com/_rxMvxNbPwwM/SkI1QIgbkHI/AAAAAAAAD-0/apVUtcgzkJc/s1600-h/3.JPG" onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5350897858547257458" src="http://4.bp.blogspot.com/_rxMvxNbPwwM/SkI1QIgbkHI/AAAAAAAAD-0/apVUtcgzkJc/s400/3.JPG" style="cursor: pointer; height: 306px; width: 400px;" /></a> <br />
<br />
<a href="http://2.bp.blogspot.com/_rxMvxNbPwwM/SkI1QavIVsI/AAAAAAAAD-8/V4NmGTGJCX8/s1600-h/4.JPG" onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5350897863440750274" src="http://2.bp.blogspot.com/_rxMvxNbPwwM/SkI1QavIVsI/AAAAAAAAD-8/V4NmGTGJCX8/s400/4.JPG" style="cursor: pointer; height: 306px; width: 400px;" /></a> <br />
<br />
<a href="http://2.bp.blogspot.com/_rxMvxNbPwwM/SkI1Qa8QudI/AAAAAAAAD_E/PtyIESG54iY/s1600-h/5.JPG" onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5350897863495825874" src="http://2.bp.blogspot.com/_rxMvxNbPwwM/SkI1Qa8QudI/AAAAAAAAD_E/PtyIESG54iY/s400/5.JPG" style="cursor: pointer; height: 306px; width: 400px;" /></a> <br />
<br />
<a href="http://4.bp.blogspot.com/_rxMvxNbPwwM/SkI1QslFYtI/AAAAAAAAD_M/-6Dm4NJwi7M/s1600-h/6.JPG" onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5350897868230451922" src="http://4.bp.blogspot.com/_rxMvxNbPwwM/SkI1QslFYtI/AAAAAAAAD_M/-6Dm4NJwi7M/s400/6.JPG" style="cursor: pointer; height: 306px; width: 400px;" /></a> <br />
</div><meta content="text/html; charset=utf-8" equiv="Content-Type"><meta content="Word.Document" name="ProgId"><meta content="Microsoft Word 11" name="Generator"><meta content="Microsoft Word 11" name="Originator"><link href="file:///C:%5CDOCUME%7E1%5CDBA%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml" rel="File-List"><link href="file:///C:%5CDOCUME%7E1%5CDBA%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_editdata.mso" rel="Edit-Time-Data"><style>
v\:* {behavior:url(#default#VML);} o\:* {behavior:url(#default#VML);} w\:* {behavior:url(#default#VML);} .shape {behavior:url(#default#VML);}
</style><style>
<!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} -->
</style><span style="font-family: Arial; font-size: 10px;"><shapetype id="_x0000_t75" preferrelative="t" spt="75"><stroke><formulas><f eqn="if lineDrawn pixelLineWidth 0"><f eqn="sum @0 1 0"><f eqn="sum 0 0 @1"><f eqn="prod @2 1 2"><f eqn="prod @3 21600 pixelWidth"><f eqn="prod @3 21600 pixelHeight"><f eqn="sum @0 0 1"><f eqn="prod @6 1 2"><f eqn="prod @7 21600 pixelWidth"><f eqn="sum @8 21600 0"><f eqn="prod @7 21600 pixelHeight"><f eqn="sum @10 21600 0"></f></f></f></f></f></f></f></f></f></f></f></f></formulas><path connecttype="rect" extrusionok="f" gradientshapeok="t"><lock aspectratio="t" ext="edit"></lock></path></stroke></shapetype><shape id="_x0000_i1025" type="#_x0000_t75"><imagedata src="file:///C:\DOCUME~1\DBA\LOCALS~1\Temp\msohtml1\01\clip_image001.png" title=""></imagedata></shape><!--[if !vml]--><!--[endif]--></span><span style="font-size: 85%;"><span style="font-family: trebuchet ms;"> <br />
</span></span><br />
<div style="text-align: center;"><a href="http://2.bp.blogspot.com/_rxMvxNbPwwM/SkI1Q-IWedI/AAAAAAAAD_U/ycmC5h8HXo8/s1600-h/7.JPG" onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5350897872941775314" src="http://2.bp.blogspot.com/_rxMvxNbPwwM/SkI1Q-IWedI/AAAAAAAAD_U/ycmC5h8HXo8/s400/7.JPG" style="cursor: pointer; height: 306px; width: 400px;" /></a> <br />
<span style="font-size: 85%;">Check box to configure MySQL Server</span> <br />
<div style="text-align: left;"><br />
<span style="font-size: 85%;">If you checked the Configure the MySQL Server now check box on the final dialog of the MySQL <br />
Server installation, then the MySQL Server Instance Configuration Wizard will automatically start. <br />
Follow the instructions below carefully to configure your MySQL Server to run correctly with <br />
EventSentry.</span> <br />
</div><br />
<br />
<a href="http://1.bp.blogspot.com/_rxMvxNbPwwM/SkI29xoh1jI/AAAAAAAAD_k/7qEJ9r7dNZQ/s1600-h/9.JPG" onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5350899742192817714" src="http://1.bp.blogspot.com/_rxMvxNbPwwM/SkI29xoh1jI/AAAAAAAAD_k/7qEJ9r7dNZQ/s400/9.JPG" style="cursor: pointer; height: 306px; width: 400px;" /></a> <br />
<span style="font-size: 85%;">Select Detailed Configuration</span> <br />
<br />
<br />
<a href="http://3.bp.blogspot.com/_rxMvxNbPwwM/SkI3Gj60t8I/AAAAAAAAD_s/wEkH71Fwqh4/s1600-h/10.JPG" onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5350899893130278850" src="http://3.bp.blogspot.com/_rxMvxNbPwwM/SkI3Gj60t8I/AAAAAAAAD_s/wEkH71Fwqh4/s400/10.JPG" style="cursor: pointer; height: 306px; width: 400px;" /></a> <br />
<div style="text-align: left;"><span style="font-size: 85%;">I was installing it on my local machine where other applications & tools are running I decided to opt "developer machine" but it is recommended that you use a Dedicated MySQL Server Machine for your MySQL database, if this is not an option then select "Server Machine".</span> <br />
</div><div style="text-align: left;"><br />
<span style="font-size: 85%;">If you selected Dedicated MySQL Server Machine and your MySQL service does not start after the wizard completes, then try to re-run the wizard (or re-install) MySQL, but this time select the Server Machine option.</span> <br />
</div><br />
<br />
<a href="http://1.bp.blogspot.com/_rxMvxNbPwwM/SkI3RUoXcdI/AAAAAAAAD_0/vR_CIOOre8Y/s1600-h/11.JPG" onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5350900078004892114" src="http://1.bp.blogspot.com/_rxMvxNbPwwM/SkI3RUoXcdI/AAAAAAAAD_0/vR_CIOOre8Y/s400/11.JPG" style="cursor: pointer; height: 306px; width: 400px;" /></a> <br />
<div style="text-align: left;"><span style="font-size: 85%;">I have checked "Multifunctional databases" as I wanted MyISAM as default storage engine but if you want you can select "Transactional Database Only", this will make sure that InnoDB is the main storage engine. If you have checked 3rd option then only myISAM engine would be available</span> <br />
</div><br />
<br />
<a href="http://4.bp.blogspot.com/_rxMvxNbPwwM/SkI3bez6pBI/AAAAAAAAD_8/Cwob8aZjrRE/s1600-h/12.JPG" onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5350900252536382482" src="http://4.bp.blogspot.com/_rxMvxNbPwwM/SkI3bez6pBI/AAAAAAAAD_8/Cwob8aZjrRE/s400/12.JPG" style="cursor: pointer; height: 306px; width: 400px;" /></a> <br />
<span style="font-size: 85%;">Select the drive where the database files will be stored. <br />
Select the drive on the fastest drive(s) on your server</span> <br />
<br />
<br />
<a href="http://3.bp.blogspot.com/_rxMvxNbPwwM/SkI3hJQGXfI/AAAAAAAAEAE/vH1Nj7Gf88c/s1600-h/13.JPG" onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5350900349828226546" src="http://3.bp.blogspot.com/_rxMvxNbPwwM/SkI3hJQGXfI/AAAAAAAAEAE/vH1Nj7Gf88c/s400/13.JPG" style="cursor: pointer; height: 306px; width: 400px;" /></a> <br />
<br />
<br />
<br />
<a href="http://2.bp.blogspot.com/_rxMvxNbPwwM/SkI3rRKI0tI/AAAAAAAAEAM/Oqh4XUWIXn0/s1600-h/14.JPG" onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5350900523749397202" src="http://2.bp.blogspot.com/_rxMvxNbPwwM/SkI3rRKI0tI/AAAAAAAAEAM/Oqh4XUWIXn0/s400/14.JPG" style="cursor: pointer; height: 306px; width: 400px;" /></a> <br />
<span style="font-size: 85%;">It is recommended that you leave the default port 3306 in place, however <br />
EventSentry will also work with non-standard ports if necessary.</span> <br />
<br />
<br />
<br />
<a href="http://2.bp.blogspot.com/_rxMvxNbPwwM/SkI31N35plI/AAAAAAAAEAU/aExZH_cqh_Q/s1600-h/15.JPG" onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5350900694666290770" src="http://2.bp.blogspot.com/_rxMvxNbPwwM/SkI31N35plI/AAAAAAAAEAU/aExZH_cqh_Q/s400/15.JPG" style="cursor: pointer; height: 306px; width: 400px;" /></a> <br />
<br />
<br />
<a href="http://2.bp.blogspot.com/_rxMvxNbPwwM/SkI36IphPGI/AAAAAAAAEAc/ChO4mn4qtJM/s1600-h/16.JPG" onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5350900779163139170" src="http://2.bp.blogspot.com/_rxMvxNbPwwM/SkI36IphPGI/AAAAAAAAEAc/ChO4mn4qtJM/s400/16.JPG" style="cursor: pointer; height: 306px; width: 400px;" /></a> <br />
<span style="font-size: 85%;">It is highly recommended that you run the MySQL Server as a Windows <br />
service(you can disable this if you want to start it manually whenever required) and include the binary directory in the search path.</span> <br />
<br />
<a href="http://4.bp.blogspot.com/_rxMvxNbPwwM/SkI3-xZAn_I/AAAAAAAAEAk/A-BC8aWaAbk/s1600-h/17.JPG" onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5350900858819223538" src="http://4.bp.blogspot.com/_rxMvxNbPwwM/SkI3-xZAn_I/AAAAAAAAEAk/A-BC8aWaAbk/s400/17.JPG" style="cursor: pointer; height: 306px; width: 400px;" /></a> <br />
<span style="font-size: 85%;">Specify a secure root password, you may want to check the box Enable root access <br />
from remote machines if you plan on administering your MySQL server <br />
from your workstation or other servers.</span> <br />
<br />
<div style="text-align: left;"><span style="color: #990000; font-size: 85%; font-weight: bold;">If you are getting an error message after clicking the Next button, then please enable port 3306 <br />
in the Windows XP Firewall Settings </span><br />
<br />
</div><a href="http://4.bp.blogspot.com/_rxMvxNbPwwM/SkI4DT--jVI/AAAAAAAAEAs/nz6pDUT4afI/s1600-h/18.JPG" onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5350900936824753490" src="http://4.bp.blogspot.com/_rxMvxNbPwwM/SkI4DT--jVI/AAAAAAAAEAs/nz6pDUT4afI/s400/18.JPG" style="cursor: pointer; height: 306px; width: 400px;" /></a> <br />
<br />
<a href="http://2.bp.blogspot.com/_rxMvxNbPwwM/SkI4IQXY1yI/AAAAAAAAEA0/Mba9krB_8aA/s1600-h/19.JPG" onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5350901021752743714" src="http://2.bp.blogspot.com/_rxMvxNbPwwM/SkI4IQXY1yI/AAAAAAAAEA0/Mba9krB_8aA/s400/19.JPG" style="cursor: pointer; height: 306px; width: 400px;" /></a> <br />
<br />
<div style="text-align: left;">Done!!! <br />
<br />
<span style="font-size: 85%;">But if you are installing MySQL on a Windows XP workstation, or any other computer that has a firewall enabled, and the wizard fails with an error message similar to the one shown below (Can't connect to MySQL server on 'localhost'), then you will have to exclude the MySQL daemon from your firewall configuration</span> <br />
<br />
<div style="text-align: center;"><a href="http://3.bp.blogspot.com/_rxMvxNbPwwM/SkI8tqmcH9I/AAAAAAAAEA8/n0li4fQ9q8M/s1600-h/20.JPG" onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5350906062496866258" src="http://3.bp.blogspot.com/_rxMvxNbPwwM/SkI8tqmcH9I/AAAAAAAAEA8/n0li4fQ9q8M/s400/20.JPG" style="cursor: pointer; height: 254px; width: 400px;" /></a> <br />
</div><br />
<span style="font-size: 85%;">On Windows XP, you can exclude MySQL from the firewall by following the steps below: <br />
1. Navigate to Start -> Settings -> Control Panel -> Windows Firewall</span> <br />
<br />
<div style="text-align: center;"><a href="http://1.bp.blogspot.com/_rxMvxNbPwwM/SkI853zhi1I/AAAAAAAAEBE/pGHAfFg0ejI/s1600-h/21.JPG" onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5350906272199838546" src="http://1.bp.blogspot.com/_rxMvxNbPwwM/SkI853zhi1I/AAAAAAAAEBE/pGHAfFg0ejI/s400/21.JPG" style="cursor: pointer; height: 400px; width: 337px;" /></a> <br />
<div style="text-align: left;"><br />
<span style="font-size: 85%;"><span style="font-family: trebuchet ms;">2. In the resulting dialog, enter the information as shown in the screenshot</span></span> <br />
</div></div><br />
<br />
<div style="text-align: center;"><a href="http://1.bp.blogspot.com/_rxMvxNbPwwM/SkI9QdTXTRI/AAAAAAAAEBM/gu4RKShBrT8/s1600-h/22.JPG" onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5350906660222618898" src="http://1.bp.blogspot.com/_rxMvxNbPwwM/SkI9QdTXTRI/AAAAAAAAEBM/gu4RKShBrT8/s400/22.JPG" style="cursor: pointer; height: 289px; width: 393px;" /></a> <br />
<br />
<div style="text-align: left;"><span style="font-size: 85%;"><span style="font-family: trebuchet ms;">After clicking OK twice, return to the MySQL error message and select Retry. MySQL should now be able to create the instance correctly.</span></span> <br />
</div></div></div></div><meta content="text/html; charset=utf-8" equiv="Content-Type"><meta content="Word.Document" name="ProgId"><meta content="Microsoft Word 11" name="Generator"><meta content="Microsoft Word 11" name="Originator"><link href="file:///C:%5CDOCUME%7E1%5CDBA%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml" rel="File-List"><link href="file:///C:%5CDOCUME%7E1%5CDBA%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_editdata.mso" rel="Edit-Time-Data"><style>
v\:* {behavior:url(#default#VML);} o\:* {behavior:url(#default#VML);} w\:* {behavior:url(#default#VML);} .shape {behavior:url(#default#VML);}
</style><style>
<!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} -->
</style><span style="font-family: Arial; font-size: 10px;"><shapetype coordsize="21600,21600" filled="f" id="_x0000_t75" path="m@4@5l@4@11@9@11@9@5xe" preferrelative="t" spt="75" stroked="f"><stroke joinstyle="miter"><formulas><f eqn="if lineDrawn pixelLineWidth 0"><f eqn="sum @0 1 0"><f eqn="sum 0 0 @1"><f eqn="prod @2 1 2"><f eqn="prod @3 21600 pixelWidth"><f eqn="prod @3 21600 pixelHeight"><f eqn="sum @0 0 1"><f eqn="prod @6 1 2"><f eqn="prod @7 21600 pixelWidth"><f eqn="sum @8 21600 0"><f eqn="prod @7 21600 pixelHeight"><f eqn="sum @10 21600 0"></f></f></f></f></f></f></f></f></f></f></f></f></formulas><path connecttype="rect" extrusionok="f" gradientshapeok="t"><lock aspectratio="t" ext="edit"></lock></path></stroke></shapetype><shape id="_x0000_i1025" type="#_x0000_t75"><imagedata src="file:///C:\DOCUME~1\DBA\LOCALS~1\Temp\msohtml1\01\clip_image001.png" title=""></imagedata></shape><!--[if !vml]--><!--[endif]--></span> <br />
<div class="blogger-post-footer">7TKU66CE8V5W</div>Umesh Shastryhttp://www.blogger.com/profile/02551756983528645221noreply@blogger.com91tag:blogger.com,1999:blog-5421473080502311460.post-40739769937029990462009-06-21T22:53:00.004+05:302009-12-30T16:02:58.676+05:30MySQL - Syncing slave with the master<span style="font-family: trebuchet ms; font-size: 85%;">Manual procedure of syncing <span style="font-weight: bold;">slave</span> with he </span><span style="font-family: trebuchet ms; font-size: 85%; font-weight: bold;">master <br />
<br />
Below commands to be run on Slave <br />
<br />
</span><span style="font-family: trebuchet ms; font-size: 85%;">1. </span><span style="font-family: trebuchet ms; font-size: 85%;">Check the slave status and Stop the slave its is running <br />
<br />
</span><br />
<div class="MsoNormal" style="font-family: trebuchet ms; margin-left: 0.5in;"><span style="font-size: 85%;"><span style="color: blue; font-size: 9pt;">mysql> show slave status\G</span></span><br />
</div><br />
<span style="font-size: 85%;"><span style="color: blue; font-family: "Courier New"; font-size: 9pt;"><span style="font-family: trebuchet ms;">mysql> stop slave;</span></span> <br />
</span><span style="font-family: trebuchet ms; font-size: 85%;"><br />
2. Get the dump of <span style="font-weight: bold;">master</span> server <br />
</span><meta content="text/html; charset=utf-8" equiv="Content-Type"><meta content="Word.Document" name="ProgId"><meta content="Microsoft Word 11" name="Generator"><meta content="Microsoft Word 11" name="Originator"><link href="file:///C:%5CDOCUME%7E1%5CADMINI%7E1%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml" rel="File-List" style="font-family: trebuchet ms;"><style>
<!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} -->
</style><span style="font-size: 85%;"><br />
</span><br />
<div class="MsoNormal" style="font-family: trebuchet ms; margin-left: 0.5in;"><span style="color: green; font-size: 85%;">shell> time mysqldump --opt -master_server_ip --master-data=1 --triggers --routines --databases DB1 DB2 DB3 DBn</span><span style="color: green; font-size: 85%;"> | gzip > /path/to/dumpfile.gz</span><br />
</div><div class="MsoNormal" style="font-family: trebuchet ms; margin-left: 0.5in;"><span style="font-size: 85%;"><br />
</span><br />
</div><span style="font-family: trebuchet ms; font-size: 85%;">3. Once the above dump is over, </span><span style="font-family: trebuchet ms; font-size: 85%;">you can start importing</span><span style="font-size: 85%;"> <br />
<br />
</span><meta content="text/html; charset=utf-8" equiv="Content-Type"><meta content="Word.Document" name="ProgId"><meta content="Microsoft Word 11" name="Generator"><meta content="Microsoft Word 11" name="Originator"><link href="file:///C:%5CDOCUME%7E1%5CADMINI%7E1%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml" rel="File-List" style="font-family: trebuchet ms;"><style>
<!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} /* List Definitions */ @list l0 {mso-list-id:1963146607; mso-list-type:hybrid; mso-list-template-ids:658813036 67698703 67698713 67698715 67698703 67698713 67698715 67698703 67698713 67698715;} @list l0:level1 {mso-level-tab-stop:.5in; mso-level-number-position:left; text-indent:-.25in;} ol {margin-bottom:0in;} ul {margin-bottom:0in;} -->
</style><br />
<div class="MsoNormal" style="font-family: trebuchet ms; margin-left: 0.5in; text-indent: -0.25in;"><span style="font-size: 85%;"><span style="color: green; font-size: 9pt;">shell> time gunzip –c </span></span><span style="color: green; font-size: 85%;">/path/to/dumpfile.gz</span><span style="font-size: 85%;"><span style="color: green; font-size: 9pt;"> | mysql </span><span style="font-size: 9pt;"></span></span><br />
</div><br />
<br />
<div class="MsoNormal" style="font-family: trebuchet ms; margin-left: 0.5in; text-indent: -0.25in;"><span style="font-size: 85%;"><br />
</span><span style="font-size: 85%;"><span style="font-size: 9pt;"></span></span><br />
</div><br />
<div class="MsoNormal" style="font-family: trebuchet ms; margin-left: 0.5in; text-indent: -0.25in;"><span style="font-size: 85%;"><span style="font-size: 9pt;"></span><span style="font-size: 9pt;">Once the above import is done, start slave on slave server</span></span><!--[endif]--><br />
</div><span style="font-size-adjust: none; font-size: 7pt; font-stretch: normal; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;"></span><br />
<br />
<div class="MsoNormal" style="font-family: trebuchet ms;"><span style="font-size: 85%;"><span style="font-size: 9pt;"></span></span><br />
</div><br />
<div class="MsoNormal" style="font-family: trebuchet ms; margin-left: 0.5in;"><span style="font-size: 85%;"><span style="color: blue; font-size: 9pt;">mysql> show slave status\G</span></span><br />
</div><br />
<div class="MsoNormal" style="margin-left: 0.5in;"><span style="font-size: 85%;"><span style="color: blue; font-family: "Courier New"; font-size: 9pt;"><span style="font-family: trebuchet ms;">mysql> start slave;</span></span></span><br />
</div><br />
<span style="font-size: 85%;"><br />
<span style="color: red; font-size: 78%; font-style: italic;">In production environment you can do this kind of sync activity by taking a maitenance window/down time during the dump process(mysqldump command)</span> <br />
</span><br />
<div class="MsoNormal" style="font-family: lucida grande; margin-left: 0.5in;"><meta content="text/html; charset=utf-8" equiv="Content-Type"><meta content="Word.Document" name="ProgId"><meta content="Microsoft Word 11" name="Generator"><meta content="Microsoft Word 11" name="Originator"><link href="file:///C:%5CDOCUME%7E1%5CADMINI%7E1%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml" rel="File-List"><style>
<!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} -->
</style><br />
</div><div class="MsoNormal" style="font-family: lucida grande; margin-left: 0.5in;"><span style="color: green; font-size: 85%;"></span><br />
</div><div style="font-family: lucida grande;"><br />
</div><div class="blogger-post-footer">7TKU66CE8V5W</div>Umesh Shastryhttp://www.blogger.com/profile/02551756983528645221noreply@blogger.com0tag:blogger.com,1999:blog-5421473080502311460.post-20843018183894279242009-06-20T18:30:00.005+05:302009-12-30T16:03:32.398+05:30MySQL - Restoring a single database from a backup file contaning multiple databases<span style="font-family: trebuchet ms; font-size: 85%;">Sometime (rather I would say most of the time) you may need to restore a single/specific database from the nightly backup but unfortunately your nightly backup contains all the other databases and restoring all the databases is not the solution as we may end up losing some of the data which might be added to the databases after the nightly backup. Fortunately there is one option "</span><span style="font-family: trebuchet ms; font-size: 85%;">--one-database</span><span style="font-family: trebuchet ms; font-size: 85%;">" available in the 'mysql' command line utility which comes into our rescue. <br />
</span><meta content="text/html; charset=utf-8" equiv="Content-Type"><meta content="Word.Document" name="ProgId"><meta content="Microsoft Word 11" name="Generator"><meta content="Microsoft Word 11" name="Originator"><span style="font-family: trebuchet ms; font-size: 85%;"><br />
</span><link href="file:///C:%5CDOCUME%7E1%5CADMINI%7E1%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml" rel="File-List" style="font-family: trebuchet ms;"><style>
<!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} tt {font-family:"Courier New"; mso-ascii-font-family:"Courier New"; mso-fareast-font-family:"Times New Roman"; mso-hansi-font-family:"Courier New"; mso-bidi-font-family:"Courier New";} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} -->
</style><br />
<div style="font-family: trebuchet ms;"><span style="font-size: 85%;">In order to restore a single database from the nightly backup you can use the --one-database or -o option when restoring</span><br />
</div><pre style="color: #3333ff; font-family: trebuchet ms; font-weight: bold;"><span style="font-size: 85%;">shell>mysql --one-database <span style="font-style: italic;">required_db</span> < <i>/path/to/backup.sql</i></span></pre><span style="font-family: trebuchet ms; font-size: 85%;"><br />
</span><br />
<div class="blogger-post-footer">7TKU66CE8V5W</div>Umesh Shastryhttp://www.blogger.com/profile/02551756983528645221noreply@blogger.com2tag:blogger.com,1999:blog-5421473080502311460.post-36308016741334683442009-06-19T14:50:00.005+05:302009-12-30T16:04:14.129+05:30mysqlhotcopy<span style="font-size: 85%;"><span style="font-family: trebuchet ms;"><span style="color: #3333ff; font-size: 100%;"><span style="font-weight: bold;">mysqlhotcopy</span></span> is a Perl script that uses LOCK TABLES, FLUSH TABLES, and cp or scp to make a database backup quickly. It is the fastest way to make a backup of the database or single tables, but it can be run only on the same machine where the database directories are located. mysqlhotcopy works only for backing up MyISAM and ARCHIVE tables. It runs on Unix and NetWare.<br />
<br />
<br />
The options can be viewed by executing the following command:<br />
<span style="color: #660000; font-size: 78%;">shell>mysqlhotcopy --help</span><br />
<br />
<br />
Backup one/many database at once<br />
<br />
<span style="color: #660000; font-size: 78%;">shell>mysqlhotcopy [options] db_name1...db_nameN /path/to/backup_directory</span><br />
<br />
Using mysqlhotcopy to backup only those tables within a given database that match a regular expression:<br />
<br />
<span style="color: #660000; font-size: 78%;">shell>mysqlhotcopy [options] db_name./regex/</span><br />
<br />
The regular expression for the table name can be negated by prefixing it with a tilde (“~”):<br />
<br />
<br />
<span style="color: #660000; font-size: 78%;">shell>mysqlhotcopy [options] db_name./~regex/</span><br />
<br />
For complete info on mysqlhotcopy<br />
http://dev.mysql.com/doc/refman/5.1/en/mysqlhotcopy.html<br />
<br />
<br />
</span><br />
</span><div class="blogger-post-footer">7TKU66CE8V5W</div>Umesh Shastryhttp://www.blogger.com/profile/02551756983528645221noreply@blogger.com0tag:blogger.com,1999:blog-5421473080502311460.post-61561067862421729522009-06-19T12:26:00.003+05:302009-12-30T16:04:32.845+05:30MySQL - Renaming database<span style="font-family: trebuchet ms; font-size: 85%;">You would hear a big "NO" if you ask some some one about the command to RENAME the DATABASE.<br />
<br />
Sometime ago MySQL tried to include the SQL command(This statement was added in MySQL 5.1.7) to rename the database "RENAME {DATABASE | SCHEMA} <em class="replaceable"><code>db_name</code></em> TO <em class="replaceable"><code>new_db_name</code></em>;" <br />
<br />
but was found to be dangerous and was removed in MySQL 5.1.23. It was intended to enable upgrading pre-5.1 databases to use the encoding implemented in 5.1 for mapping database names to database directory names . However, use of this statement could result in loss of database contents, which is why it was removed. Do not use <code class="literal">RENAME DATABASE</code> in earlier versions in which it is present.<br />
<br />
Then is there any workaround for this? yes there couple of ways to do this.. below suggestion are given by some of MySQL experts on online forums which I'm including here<br />
<br />
</span><br />
<pre style="font-family: trebuchet ms; margin: 0em;"><span style="font-size: 85%;">A) use <span style="font-weight: bold;">mysqldump</span> to dump the database, create the new
database, reload the dump file into the new database, then drop the old
database. That is, in effect, a database rename, although (I suspect)
it can cause problems if you have foreign key relationships that refer
to the table names in the original database.</span></pre><span style="font-family: trebuchet ms; font-size: 85%;"><br />
</span><br />
<pre style="font-family: trebuchet ms; margin: 0em;"><span style="font-size: 85%;">B) Another approach is to create the new database, and then, for each
table in the original database, use RENAME TABLE orig_db.t TO new_db.t
to move the table from one database to the other. Then drop the old
database.</span></pre><span style="font-family: trebuchet ms; font-size: 85%;"><br />
C) Some ppl suggest this way ( NOT A SAFE-WAY though if you have mixed engines)<br />
<br />
i) Bring down MySQL if it is running<br />
ii) rename database directory using OS commands<br />
iii) Bring up MySQL<br />
</span><div class="blogger-post-footer">7TKU66CE8V5W</div>Umesh Shastryhttp://www.blogger.com/profile/02551756983528645221noreply@blogger.com1tag:blogger.com,1999:blog-5421473080502311460.post-12459991836632160222009-06-18T13:37:00.005+05:302009-12-30T16:04:57.313+05:30MySQL - lower,upper char count<span style="font-family: trebuchet ms; font-size: 85%;">In one of my task I wanted to get the count of upper/lower char from a string but noticed that the string function provided in MySQL couldn't get me that.. and also at the moment it seems that the regex function in MySQL can match only and the matched stats cannot be captured nor returned.<br />
<br />
I know 2 liner code in any scripting language can perform this tasks very well.<br />
<br />
<br />
<span style="font-size: 78%;">DELIMITER $$<br />
<br />
DROP FUNCTION IF EXISTS `test`.`uGetLowerUpperCharCount`$$<br />
<br />
CREATE FUNCTION `uGetLowerUpperCharCount`(prm_string varchar(250)) RETURNS varchar(250) CHARSET latin1<br />
BEGIN<br />
DECLARE strPos INT default 1;<br />
DECLARE strUpperLen INT default 0;<br />
DECLARE strLowerLen INT default 0;<br />
DECLARE strNonAlphaLen INT default 0;<br />
WHILE strPos <= LENGTH(prm_string) DO IF ASCII(SUBSTRING(prm_string,strPos ,1)) >= 65 AND ASCII(SUBSTRING(prm_string,strPos ,1)) <=90 THEN SET strUpperLen = strUpperLen+1; ELSEIF ASCII(SUBSTRING(prm_string,strPos ,1)) >= 97 AND ASCII(SUBSTRING(prm_string,strPos ,1)) <=122 THEN SET strLowerLen = strLowerLen+1; ELSE SET strNonAlphaLen = strNonAlphaLen+1; END IF; SET strPos = strPos+1; END WHILE; RETURN CONCAT('String ',prm_string,' has ', IF(strUpperLen>0,CONCAT(strUpperLen, ' - Upper Chars '),''),IF(strLowerLen>0,CONCAT(',',strLowerLen,' - Lower Chars '),''),IF(strNonAlphaLen>0,CONCAT(' and ',strNonAlphaLen,' - Non-Alpha '),''));<br />
END$$<br />
<br />
DELIMITER ;</span><br />
<br />
<span style="font-weight: bold;">Usage:</span><br />
<br />
<span style="font-size: 78%;">select uGetLowerUpperCharCount('Umesh Kumar Shastry');<br />
<br />
String Umesh Kumar Shastry has 3 - Upper Chars ,14 - Lower Chars and 2 - Non-Alpha </span><br />
</span><div class="blogger-post-footer">7TKU66CE8V5W</div>Umesh Shastryhttp://www.blogger.com/profile/02551756983528645221noreply@blogger.com0tag:blogger.com,1999:blog-5421473080502311460.post-21526059415062715392009-06-17T21:22:00.002+05:302009-12-30T16:05:15.375+05:30MySQL - initcap function<span style="color: #3333ff; font-size: 85%;"><span style="font-family: trebuchet ms;"><span style="color: black;">Sometime back I was looking for a built-in initcap/ucfirst function in MySQL but unfortunately couldn't find such string functions so decided to write my own.. thanks to the MySQL community member who corrected the bug in my function & posted it back.</span><br />
<br />
<br />
<span style="font-size: 78%;">DELIMITER $$<br />
<br />
DROP FUNCTION IF EXISTS `test`.`initcap`$$<br />
<br />
CREATE FUNCTION `initcap`(x char(30)) RETURNS char(30) CHARSET utf8<br />
BEGIN<br />
SET @str='';<br />
SET @l_str='';<br />
WHILE x REGEXP ' ' DO<br />
SELECT SUBSTRING_INDEX(x, ' ', 1) INTO @l_str;<br />
SELECT SUBSTRING(x, LOCATE(' ', x)+1) INTO x;<br />
SELECT CONCAT(@str, ' ', CONCAT(UPPER(SUBSTRING(@l_str,1,1)),LOWER(SUBSTRING(@l_str,2)))) INTO @str;<br />
END WHILE;<br />
RETURN LTRIM(CONCAT(@str, ' ', CONCAT(UPPER(SUBSTRING(x,1,1)),LOWER(SUBSTRING(x,2)))));<br />
END$$<br />
<br />
DELIMITER ;</span><br />
<br />
<br />
<span style="font-weight: bold;">Usage: </span><br />
<br />
<span style="font-size: 78%;">select initcap('umesh kumar shastry');<br />
<br />
Umesh Kumar Shastry<br />
<br />
select initcap('ashutosh s');<br />
<br />
Ashutosh S<br />
<br />
select initcap('rahul giri');<br />
<br />
Rahul Giri<br />
<br />
select initcap('alam seraj');<br />
<br />
Alam Seraj<br />
<br />
select initcap('atul kaushik');<br />
<br />
Atul Kaushik<br />
<br />
</span><br />
</span></span><div class="blogger-post-footer">7TKU66CE8V5W</div>Umesh Shastryhttp://www.blogger.com/profile/02551756983528645221noreply@blogger.com10tag:blogger.com,1999:blog-5421473080502311460.post-63189627120711885652009-06-17T12:13:00.005+05:302009-12-30T15:51:55.859+05:30MySQL - Copying records from remote server DB table to local server DB table<span style="font-size: 85%;"><span style="font-family: trebuchet ms;">Sometime you may need to populate(sync) local testing table with the production table which is hosted on a remote server. </span><br />
<br />
<span style="font-family: trebuchet ms;"><br />
# This command should be run on local server(SINGLE COMMAND)</span><br />
<br />
<span style="color: #660000; font-size: 78%; font-weight: bold;"><span style="font-family: trebuchet ms;">mysqldump -t -h [remote_host_ip] -u[remote_User] -p[remote_Password] [remote_DbName] [remote_TableName] | mysql -h localhost -u [local_User] -p[local_Password] [local_DbName]</span></span></span><br />
<br />
<span style="font-size: 85%;"><br />
<span style="font-family: trebuchet ms;"><span style="color: #3333ff; font-weight: bold;">What if you want to copy last month data only?</span><br />
</span><br />
</span><span style="font-family: trebuchet ms; font-size: 78%;"><span style="color: #660000; font-weight: bold;">mysqldump -t -h [remote_host_ip] -u[remote_User] -p[remote_Password] [remote_DbName] [remote_TableName] </span></span><span style="font-family: Century Gothic;"><span style="color: maroon; font-size: 85%;"><b>-w "column_date_time > NOW() - INTERVAL 1 MONTH"</b></span></span><span style="font-family: trebuchet ms; font-size: 78%;"><span style="color: #660000; font-weight: bold;"><b>| mysql -h localhost -u [local_User] -p[local_Password] [local_DbName]</b></span></span><b><br />
</b><div class="blogger-post-footer">7TKU66CE8V5W</div>Umesh Shastryhttp://www.blogger.com/profile/02551756983528645221noreply@blogger.com0tag:blogger.com,1999:blog-5421473080502311460.post-74504778981266460022009-06-15T13:44:00.005+05:302009-12-30T16:05:41.436+05:30MySQL - Thread stack overrun<span style="font-family: trebuchet ms; font-size: 85%;">Sometime you may come across a situation wherein a stored procedure(in mycase it was </span><span style="font-family: trebuchet ms; font-size: 85%;">uGetBussinessDays</span><span style="font-family: trebuchet ms; font-size: 85%;"> which was working perfectly until this morning is now throwing a Thread stack overrun error) when called throws Thread stack overrun error.<br />
<br />
<span class="MsgBodyText"></span>Typically the error message would look like:<br />
<br />
</span><span style="color: red; font-family: trebuchet ms; font-size: 85%;">Error Code : 1436<br />
Thread stack overrun: 6444 bytes used of a 131072 byte stack, and 128000 bytes needed. Use 'mysqld -O thread_stack=#' to specify a bigger stack.<br />
</span><span style="font-family: trebuchet ms; font-size: 85%;"><br />
<br />
According to the MySQL manual "The default (192KB) is large enough for normal operation. If the thread stack size is too small, it limits the complexity of the SQL statements that the server can handle, the recursion depth of stored procedures, and other memory-consuming actions" .<br />
<br />
To resolve this issue you need to increase the default value of parameter thread_stack (128 in my my-small.cnf) to something 258K in your MySQL configuration file</span>.<div class="blogger-post-footer">7TKU66CE8V5W</div>Umesh Shastryhttp://www.blogger.com/profile/02551756983528645221noreply@blogger.com6