What returns from distance function?
Distance between two points. but in which type? and how can i convert this value to meter?
Re: When to use Stored Procedures
Hi Johan, Johan De Meersman wrote: as a totally off-topc question, wouldn't something along the lines of LIMIT COUNT(*)/2, 1 do that trick? On 4/15/10, Rhino rhi...@sympatico.ca wrote: ...snip... For example, suppose you had to determine the median grade for a test. ... That would require running the query twice as LIMIT only accepts numeric literals. For a large dataset, that would destroy the efficiency of the stored procedure. It's more efficient with MySQL to capture the value in a temporary table, count those temporary results, then create a LIMIT query using the prepared statement syntax (dynamic SQL) against the data in the temp table. http://dev.mysql.com/doc/refman/5.1/en/select.html The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants (except when using prepared statements). The above process could very easily be encapsulated by a stored PROCEDURE (but not by a stored FUNCTION) so that you would not need to implement it in your client code. Unfortunately the stored functions are not allowed to use prepared statements, yet. http://dev.mysql.com/doc/refman/5.1/en/stored-program-restrictions.html SQL prepared statements (PREPARE, EXECUTE, DEALLOCATE PREPARE) can be used in stored procedures, but not stored functions or triggers. Hope that helps! -- Shawn Green MySQL Principle Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: What returns from distance function?
I used below query from http://lists.mysql.com/mysql/219805
SET @center = GeomFromText( 'POINT(39.78824896727801 30.50930339115439)' )
;# MySQL returned an empty result set (i.e. zero rows).
SET @radius = 0.005;# MySQL returned an empty result set (i.e. zero rows).
SET @bbox = GeomFromText( CONCAT( 'POLYGON((', X( @center ) - @radius , ' ',
Y( @center ) - @radius , ',', X( @center ) + @radius , ' ', Y( @center ) -
@radius , ',', X( @center ) + @radius , ' ', Y( @center ) + @radius , ',',
X( @center ) - @radius , ' ', Y( @center ) + @radius , ',', X( @center ) -
@radius , ' ', Y( @center ) - @radius , '))' ) ) ;# MySQL returned an empty
result set (i.e. zero rows).
SELECT astext( point ) , Distance( @center , point ) AS dist
FROM psn.psn_place
WHERE MBRContains( @bbox , point )
ORDER BY dist
LIMIT 10;
I don't know type of 'dist' in this result, I need 'dist' column in meter
format.
+--+-+
| astext( point ) | dist|
+--+-+
| POINT(39.7872360228843 30.5097413063049) | 0.00110355155014048 |
| POINT(39.7872648779901 30.5084055662155) | 0.00133211161219657 |
| POINT(39.7871288466708 30.5080997943878) | 0.00164417619226759 |
+--+-+
On Sun, Apr 18, 2010 at 4:25 PM, Onur UZUN onuruzu...@gmail.com wrote:
Distance between two points. but in which type? and how can i convert this
value to meter?
Re: Mysql - Tables Export to Excel!
Vikram A wrote: Hi, I would like to export my table structure from MYSQL from a particular db. Is there any tool for doing this? There are several ways to get structure information from within MySQL: the SHOW COLUMNS... command the SHOW INDEXES... command the SHOW CREATE TABLE... command you can also write queries against the tables in the INFORMATION_SCHEMA You can also use an external utility such as mysqldump. Use the --no-data option to get just a dump of your table definitions. If you also want to see triggers and events and stored procedures, you also need to use the --triggers, --events, and --procedure options, too. Details are in the fine manual: http://dev.mysql.com/doc/refman/5.1/en/show.html http://dev.mysql.com/doc/refman/5.1/en/information-schema.html http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html I guess the final answer depends on which information are you looking for and in what format you want to see it. Got any details you want to share? -- Shawn Green MySQL Principle Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Problem with installing MySQL
alba.albetti wrote: I've just installed MySQL on Windows 2000. I've opened the MS-DOS windows and I've written C:\Programs\MySQL\...\bin\mysqladmin -u root -p password mysql2010 After the enter the prompt says Enter password: and I've given enter and I get mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: NO)' After installing MySQL what do I have to do left? Sorry my this is my firt time with MySQL If you have not set a password for the root user, yet, then there is no password. Leave off the -p option and see if it allows you to set it that way. -- Shawn Green MySQL Principle Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
How to corrupt a database please???
Hi all, I am hiring a few new junior DBA's and I want to put them thru a simple db repair training. Does anyone know how I can deliberately corrupt a MyISAM and InnoDB database in different ways please? So what I want to do is corrupt 3 MyISAM 100gb databases, 3 InnoDB 100gb databases - ALL WITH DIFFERENT ERROR MESSAGES and have these newbies fix them in a 2 hour period :-) I have fixed oodles of db's but NEVER thought I would see the say where I would WANT to corrupt a db on purpose, but that day is here and am looking for advise please. Thanks... Nunu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to corrupt a database please???
open the file and remove some data and close it for both data file and index files, So the tables will be corrupted when access. On Sun, Apr 18, 2010 at 9:55 PM, Nurudin Javeri nsjav...@idh.com wrote: Hi all, I am hiring a few new junior DBA's and I want to put them thru a simple db repair training. Does anyone know how I can deliberately corrupt a MyISAM and InnoDB database in different ways please? So what I want to do is corrupt 3 MyISAM 100gb databases, 3 InnoDB 100gb databases - ALL WITH DIFFERENT ERROR MESSAGES and have these newbies fix them in a 2 hour period :-) I have fixed oodles of db's but NEVER thought I would see the say where I would WANT to corrupt a db on purpose, but that day is here and am looking for advise please. Thanks... Nunu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com -- Thanks Suresh Kuna MySQL DBA
Re: How to corrupt a database please???
You can remove the innodb logs and/or the innodb data file. You can also remove some of the individual .idb files (if you're using file-per-table option). On Sun, Apr 18, 2010 at 11:25 AM, Nurudin Javeri nsjav...@idh.com wrote: Hi all, I am hiring a few new junior DBA's and I want to put them thru a simple db repair training. Does anyone know how I can deliberately corrupt a MyISAM and InnoDB database in different ways please? So what I want to do is corrupt 3 MyISAM 100gb databases, 3 InnoDB 100gb databases - ALL WITH DIFFERENT ERROR MESSAGES and have these newbies fix them in a 2 hour period :-) I have fixed oodles of db's but NEVER thought I would see the say where I would WANT to corrupt a db on purpose, but that day is here and am looking for advise please. Thanks... Nunu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=jlyons4...@gmail.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Re: How to corrupt a database please???
On Sun, Apr 18, 2010 at 10:39 AM, Suresh Kuna sureshkumar...@gmail.com wrote: open the file and remove some data and close it for both data file and index files, So the tables will be corrupted when access. On Sun, Apr 18, 2010 at 9:55 PM, Nurudin Javeri nsjav...@idh.com wrote: Hi all, I am hiring a few new junior DBA's and I want to put them thru a simple db repair training. Does anyone know how I can deliberately corrupt a MyISAM and InnoDB database in different ways please? So what I want to do is corrupt 3 MyISAM 100gb databases, 3 InnoDB 100gb databases - ALL WITH DIFFERENT ERROR MESSAGES and have these newbies fix them in a 2 hour period :-) I have fixed oodles of db's but NEVER thought I would see the say where I would WANT to corrupt a db on purpose, but that day is here and am looking for advise please. Thanks... Nunu Umm, shouldn't you train your Junion DBA to: 1. fail off of the corrupted servers. 2. restore from backup. 3. or at least get a non-junior dba and then have them shadow? I have a problem with the idea of repairing Innodb. Depending on where the corruption (checksum mismatch) has occurred it can be very difficult to get all the original data out. Don't get me wrong, there are way to do it, but it is a nasty endeavor. For that mater I don't trust repairing MyISAM all that much either. I try my very best to keep MyISAM out of production. In my opinion MyISAM should be treated as something one step higher than the blackhole engine. Put data in and you might be able to pull it out later. (don't get me wrong, packed myisam has its place...) -- Rob Wultsch wult...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to corrupt a database please???
What if the DBA ask for the backup? And those recommendations can be fixed or they have a very high chance of making recovery impossible? On Sun, Apr 18, 2010 at 1:09 PM, Rob Wultsch wult...@gmail.com wrote: On Sun, Apr 18, 2010 at 10:39 AM, Suresh Kuna sureshkumar...@gmail.com wrote: open the file and remove some data and close it for both data file and index files, So the tables will be corrupted when access. On Sun, Apr 18, 2010 at 9:55 PM, Nurudin Javeri nsjav...@idh.com wrote: Hi all, I am hiring a few new junior DBA's and I want to put them thru a simple db repair training. Does anyone know how I can deliberately corrupt a MyISAM and InnoDB database in different ways please? So what I want to do is corrupt 3 MyISAM 100gb databases, 3 InnoDB 100gb databases - ALL WITH DIFFERENT ERROR MESSAGES and have these newbies fix them in a 2 hour period :-) I have fixed oodles of db's but NEVER thought I would see the say where I would WANT to corrupt a db on purpose, but that day is here and am looking for advise please. Thanks... Nunu Umm, shouldn't you train your Junion DBA to: 1. fail off of the corrupted servers. 2. restore from backup. 3. or at least get a non-junior dba and then have them shadow? I have a problem with the idea of repairing Innodb. Depending on where the corruption (checksum mismatch) has occurred it can be very difficult to get all the original data out. Don't get me wrong, there are way to do it, but it is a nasty endeavor. For that mater I don't trust repairing MyISAM all that much either. I try my very best to keep MyISAM out of production. In my opinion MyISAM should be treated as something one step higher than the blackhole engine. Put data in and you might be able to pull it out later. (don't get me wrong, packed myisam has its place...) -- Rob Wultsch wult...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mr.crip...@gmail.com
Re: How to corrupt a database please???
On Sun, Apr 18, 2010 at 11:07 AM, Jim Lyons jlyons4...@gmail.com wrote: You can remove the innodb logs and/or the innodb data file. You can also remove some of the individual .idb files (if you're using file-per-table option). He originally asked about how to provide a training excise about repairing a db. How the hell do you repair from not having data files? For that matter the recovery from lacking log files (and assuming a crashed state) is imho ugly as hell. -- Rob Wultsch wult...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to corrupt a database please???
A couple good tests are. 1. Corrupt a relay log. For this you can stop the sql thread, cat /dev/urandom over the newest relay log, start the sql thread and watch it fail. 2. Change the innodb_log_file_size in my.cnf without going through the proper procedure to remove the old log files. In 5.0 this will cause incorrect information in frm file errors for queries which will take a little bit of work to track down. 3. Can some random data over myisam files and run a check tables so mysql marks them as crashed. On Sun, Apr 18, 2010 at 11:13 AM, Rob Wultsch wult...@gmail.com wrote: On Sun, Apr 18, 2010 at 11:07 AM, Jim Lyons jlyons4...@gmail.com wrote: You can remove the innodb logs and/or the innodb data file. You can also remove some of the individual .idb files (if you're using file-per-table option). He originally asked about how to provide a training excise about repairing a db. How the hell do you repair from not having data files? For that matter the recovery from lacking log files (and assuming a crashed state) is imho ugly as hell. -- Rob Wultsch wult...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=eric.ber...@gmail.com -- Eric Bergen eric.ber...@gmail.com http://www.ebergen.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to corrupt a database please???
On Sun, Apr 18, 2010 at 11:13 AM, Andrés Tello mr.crip...@gmail.com wrote: What if the DBA ask for the backup? And those recommendations can be fixed or they have a very high chance of making recovery impossible? Who is the dba going to ask for a backup? Himself? The guy that puts backups on tape? One way or another the DBA damn well better know how to get a backup. Failing off of a server gets you on to a slave which should be sync'd with the master. If you restore from backup then you can run a pitr . In my opinion both of these options are usually superior to running repair table on a production server. That is if you like uptime. For the record innodb corruption is quite rare, at least in comparison to MyISAM corruption. If I get a call at 2AM and find a server having died due to innodb corruption I would fail off of the server. No ifs, no ands, not buts. I would assume: 1. Possible, perhaps even probably hardware issues if there is Innodb corruptions. 2. A failover takes a set amount of time. Repairing corruptions will usually take longer, perhaps much much longer. -- Rob Wultsch wult...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Recommended swap partition size
Linux will normally swap out a few pages of rarely used memory so it's a good idea to have some swap around. 2G seems excessive though. Usually I prefer to have linux kill processes rather than excessively swapping. I've worked on machines before that have swapped so badly that it took minutes just to ssh to them. This is effectively a failure scenario that can last for a lot longer than it takes to restart/failover mysqld. For apache it means the clients will see errors until the load balancer health check drops the server out of rotation. The best solution in all cases is to keep an eye on swap in/out and memory usage so neither the crash nor the excessive swapping becomes a problem. On Wed, Apr 14, 2010 at 3:06 AM, Glyn Astill glynast...@yahoo.co.uk wrote: --- On Wed, 14/4/10, Dan Nelson dnel...@allantgroup.com wrote: Hammerman said: My organization has a dedicated MySQL server. The system has 32Gb of memory, and is running CentOS 5.3. The default engine will be InnoDB. Does anyone know how much space should be dedicated to swap? I say zero swap, or if for some reason you NEED swap (for crashdumps maybe, but I didn't think Linux supported that), no more than 2GB. With that much RAM, you don't ever want to be in the state where the OS decides to page out 8GB of memory (for example) to swap. We have a few Oracle servers with between 32 and 48 GB of memory and they all live just fine without swap. But surely better to have a server that is paging out and has slowed to a crawl than one where the oom killer starts killing off your processes, with no swap I'd be turning overcommit off. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=eric.ber...@gmail.com -- Eric Bergen eric.ber...@gmail.com http://www.ebergen.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to corrupt a database please???
Rob Wultsch wrote: On Sun, Apr 18, 2010 at 11:13 AM, Andrés Tello mr.crip...@gmail.com wrote: What if the DBA ask for the backup? And those recommendations can be fixed or they have a very high chance of making recovery impossible? Who is the dba going to ask for a backup? Himself? The guy that puts backups on tape? One way or another the DBA damn well better know how to get a backup. Failing off of a server gets you on to a slave which should be sync'd with the master. If you restore from backup then you can run a pitr . In my opinion both of these options are usually superior to running repair table on a production server. That is if you like uptime. For the record innodb corruption is quite rare, at least in comparison to MyISAM corruption. If I get a call at 2AM and find a server having died due to innodb corruption I would fail off of the server. No ifs, no ands, not buts. I would assume: 1. Possible, perhaps even probably hardware issues if there is Innodb corruptions. 2. A failover takes a set amount of time. Repairing corruptions will usually take longer, perhaps much much longer. I agree with Rob. InnoDB failures are nearly always caused by OS-level or HW-level failures. The worst-case scenario is to need to rebuild part of your data from whatever information remains in the corrupted file. It is much better to restore from backup or rebuild from a slave than to go through the pain of rebuilding a corrupted tablespace. But, here are some ideas on ways to screw one up: 1) Put it on an NFS drive then read from it using another user's account while the database is trying to write to it. 2) Scan it with an antivirus program while it is online and actively making changes. 3) Use a hex editor and manually zero out a page of data or index 4) Delete the active log file (or both of them) 5) Turn on two MySQL instances to the same files at the same time. 6) Delete the .frm file for a table 7) Take a backup of the tablespace, change a few things, the restore the tablespace but not the logs. While I can't predict what kind of problem you will create for yourself, these are all things that have created problems for others in the past. -- Shawn Green MySQL Principle Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Recommended swap partition size
On Sun, Apr 18, 2010 at 12:04 PM, Eric Bergen eric.ber...@gmail.com wrote: Linux will normally swap out a few pages of rarely used memory so it's a good idea to have some swap around. 2G seems excessive though. Usually I prefer to have linux kill processes rather than excessively swapping. I've worked on machines before that have swapped so badly that it took minutes just to ssh to them. This is effectively a failure scenario that can last for a lot longer than it takes to restart/failover mysqld. For apache it means the clients will see errors until the load balancer health check drops the server out of rotation. The best solution in all cases is to keep an eye on swap in/out and memory usage so neither the crash nor the excessive swapping becomes a problem. Umm, you were probably horribly over io utilized. Swapping by itself will not kill perforance I have some boxes where mysql has leaked a metric crap ton of memory and swapping is ok. The leaked memory is swapped out and sits out in swap. Every now and a again I create more swap to keep the server happy. Swapping is often preferable to crash with unplanned downtime. Note that innodb_flush_method can implact this... -- Rob Wultsch wult...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Recommended swap partition size
The impact of swap activity on performance is dependent on the rate at which things are being swapped and the speed of swapping. A few pages per second probably won't kill things but in this case it was swapping hundreds of pages per second which killed performance. Disks are much slower than ram. In my environment mysqld_safe will failover the cluster if it detects mysqld has crashed so I prefer the quick crash and failover to the long period of slow response time. Many operators prefer the long slow response time, I don't but it's a religious debate. On Sun, Apr 18, 2010 at 12:13 PM, Rob Wultsch wult...@gmail.com wrote: On Sun, Apr 18, 2010 at 12:04 PM, Eric Bergen eric.ber...@gmail.com wrote: Linux will normally swap out a few pages of rarely used memory so it's a good idea to have some swap around. 2G seems excessive though. Usually I prefer to have linux kill processes rather than excessively swapping. I've worked on machines before that have swapped so badly that it took minutes just to ssh to them. This is effectively a failure scenario that can last for a lot longer than it takes to restart/failover mysqld. For apache it means the clients will see errors until the load balancer health check drops the server out of rotation. The best solution in all cases is to keep an eye on swap in/out and memory usage so neither the crash nor the excessive swapping becomes a problem. Umm, you were probably horribly over io utilized. Swapping by itself will not kill perforance I have some boxes where mysql has leaked a metric crap ton of memory and swapping is ok. The leaked memory is swapped out and sits out in swap. Every now and a again I create more swap to keep the server happy. Swapping is often preferable to crash with unplanned downtime. Note that innodb_flush_method can implact this... -- Rob Wultsch wult...@gmail.com -- Eric Bergen eric.ber...@gmail.com http://www.ebergen.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Multiple table engine
This can become a problem when using replication. For example if you do: begin; insert into innodb_table; insert into myisam_table; insert into innodb_table; rollback; The innodb rows won't be replicated but the myisam row will. There is more info at: http://dev.mysql.com/doc/refman/5.0/en/innodb-and-mysql-replication.html On Thu, Apr 8, 2010 at 4:02 AM, Jigal van Hemert ji...@xs4all.nl wrote: Tompkins Neil wrote: Just looking for some confirmation that under a single database - I assume it is perfectly normal to have both MyISAM and InnoDB engines for different tables ? Is there anything I need to be aware of ? In most case no problems. MySQL can mix engines without problems. Every engine uses it's own specific buffers, so if your database becomes big and memory becomes an issue (large buffers needed) it might be handy to use only one engine (so you can set the buffers for the other engine(s) to a very low number). On a daily basis I use databases with mixed MyISAM and InnoDB tables. -- Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=eric.ber...@gmail.com -- Eric Bergen eric.ber...@gmail.com http://www.ebergen.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysqld_safe
It's distributed as part of mysql. The script is responsible for restarting mysqld if it exits with a non zero return code such as when it crashes. On Mon, Mar 29, 2010 at 2:40 PM, Colin Streicher co...@obviouslymalicious.com wrote: Yeah, its just a shell script that acts as a wrapper around the mysql processes on debian systems( maybe others, I'm not sure) You can read it at 'less /usr/bin/mysqld_safe' Colin On March 29, 2010 11:51:36 am Glyn Astill wrote: --- On Mon, 29/3/10, Brown, Charles cbr...@bmi.com wrote: Hello All. when I issued this command: ps -e | grep, I noticed that mysqld_safe was up running in my system. My question is: what is mysqld_safe and why was it running. Please help. [sp...@naxbmisq03 ~]$ ps -e | grep -i mysql 11989 ? 00:00:00 mysqld_safe 12025 ? 13:28:39 mysqld My understanding is that mysqld_safe is the process used to start mysqld and handle any runtime errors, crashes etc. -- Anyone who has had a bull by the tail knows five or six more things than someone who hasn't. -- Mark Twain -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=eric.ber...@gmail.com -- Eric Bergen eric.ber...@gmail.com http://www.ebergen.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Mysql - Tables Export to Excel!
use can use mysqldump with option *-no-data* eg. *mysqldump -u user -ppassword wordpress user --no-data Dumpdata.txt * where wordpress is my database and user is my table. Thanks On Sun, Apr 18, 2010 at 9:48 PM, Shawn Green shawn.l.gr...@oracle.comwrote: Vikram A wrote: Hi, I would like to export my table structure from MYSQL from a particular db. Is there any tool for doing this? There are several ways to get structure information from within MySQL: the SHOW COLUMNS... command the SHOW INDEXES... command the SHOW CREATE TABLE... command you can also write queries against the tables in the INFORMATION_SCHEMA You can also use an external utility such as mysqldump. Use the --no-data option to get just a dump of your table definitions. If you also want to see triggers and events and stored procedures, you also need to use the --triggers, --events, and --procedure options, too. Details are in the fine manual: http://dev.mysql.com/doc/refman/5.1/en/show.html http://dev.mysql.com/doc/refman/5.1/en/information-schema.html http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html I guess the final answer depends on which information are you looking for and in what format you want to see it. Got any details you want to share? -- Shawn Green MySQL Principle Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=aim.prab...@gmail.com -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: Mysql - Tables Export to Excel!
Note : if you table name it will export all tables from given database; eg:* mysqldump -u user -ppassword wordpress --no-data Dumpdata.txt * It will export all tables from db wordpress. On Mon, Apr 19, 2010 at 8:57 AM, Prabhat Kumar aim.prab...@gmail.comwrote: use can use mysqldump with option *-no-data* eg. *mysqldump -u user -ppassword wordpress user --no-data Dumpdata.txt * where wordpress is my database and user is my table. Thanks On Sun, Apr 18, 2010 at 9:48 PM, Shawn Green shawn.l.gr...@oracle.comwrote: Vikram A wrote: Hi, I would like to export my table structure from MYSQL from a particular db. Is there any tool for doing this? There are several ways to get structure information from within MySQL: the SHOW COLUMNS... command the SHOW INDEXES... command the SHOW CREATE TABLE... command you can also write queries against the tables in the INFORMATION_SCHEMA You can also use an external utility such as mysqldump. Use the --no-data option to get just a dump of your table definitions. If you also want to see triggers and events and stored procedures, you also need to use the --triggers, --events, and --procedure options, too. Details are in the fine manual: http://dev.mysql.com/doc/refman/5.1/en/show.html http://dev.mysql.com/doc/refman/5.1/en/information-schema.html http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html I guess the final answer depends on which information are you looking for and in what format you want to see it. Got any details you want to share? -- Shawn Green MySQL Principle Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=aim.prab...@gmail.com -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
InnoDB Default Storage Engine
I want to change the mysql default storage engine from MyISAM to InnoDB. What are the steps involved .Is it edit my.cnf file and add a line default-storage-engine=innodb and restart the mysql server? How I can bring my databases with mixed storage engine down without any data loss. What steps I have to take if I encounter a page corruption in innodb tables. why I am getting a message *Error*: *No query specified* when I run a show engines\g commands -version (5.0.45) Thanks, Arsh Paul
mysql-bin log file
How can I remove old mysql-bin log file in log directory? A mysql full backup will clear the old mysql bin log file or not? Thanks, Arshu Paul
Re: mysql-bin log file
On Sun, Apr 18, 2010 at 8:58 PM, Angelina Paul arshup...@gmail.com wrote: How can I remove old mysql-bin log file in log directory? A mysql full backup will clear the old mysql bin log file or not? Thanks, Arshu Paul You probably want http://mysql2.mirrors-r-us.net/doc/refman/5.1/en/purge-master-logs.html -- Rob Wultsch wult...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: InnoDB Default Storage Engine
On Sun, Apr 18, 2010 at 8:31 PM, Angelina Paul arshup...@gmail.com wrote: I want to change the mysql default storage engine from MyISAM to InnoDB. What are the steps involved .Is it edit my.cnf file and add a line default-storage-engine=innodb and restart the mysql server? If you do not want to change any existing tables all you need to do is add the line to your cnf. How I can bring my databases with mixed storage engine down without any data loss. mysqladmin shutdown What steps I have to take if I encounter a page corruption in innodb tables. The right answer is restore from backup or failover to a slave. The answer you probably want is http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html why I am getting a message *Error*: *No query specified* when I run a show engines\g commands -version (5.0.45) Exactly what are you running? -- Rob Wultsch wult...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysql-bin log file
You can add a *expire_logs_days* Variable in my.cnf during the configuration of replication server. #* expire_logs_days = 7* It will purged binary logs older than 7 days.The old logs will be purged during the next bin-log switch. Or, You can also delete bin-log manually using command : PURGE BINARY LOGS TO 'mysql-bin.010'; PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26'; but before you purge please make sure that slave is on sync with master or confirm the current status from slaves. Thanks, On Mon, Apr 19, 2010 at 9:47 AM, Rob Wultsch wult...@gmail.com wrote: On Sun, Apr 18, 2010 at 8:58 PM, Angelina Paul arshup...@gmail.com wrote: How can I remove old mysql-bin log file in log directory? A mysql full backup will clear the old mysql bin log file or not? Thanks, Arshu Paul You probably want http://mysql2.mirrors-r-us.net/doc/refman/5.1/en/purge-master-logs.html -- Rob Wultsch wult...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=aim.prab...@gmail.com -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: mysql-bin log file
On Sun, Apr 18, 2010 at 9:40 PM, Prabhat Kumar aim.prab...@gmail.com wrote: You can add a expire_logs_days Variable in my.cnf during the configuration of replication server. # expire_logs_days = 7 It will purged binary logs older than 7 days.The old logs will be purged during the next bin-log swittch. And if your slave's IO lags badly enough this will hose you. Further it might well come in handy to an arbitrary number of bin logs for pirt purposes. -- Rob Wultsch wult...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: InnoDB Default Storage Engine
You need to locate the mySQL config file (helpfully named) my.cnf file. On linux it is located at /etc/my.cnf Then under the [mysqld] add the following line as shown below! *[mysqld] default-storage_engine = InnoDB* And don't forget to restart mysql. After this whenever you create a table its default storage engine is InnoDB. but it will not change any existing table storage engine to Innodb. To do this there is two way. a. You can change storage engine by alter command of tables, but one by one. eg. *Alter table [tablename] engine=myisam; * b. export the database , an then replace MyISAM with InnoDB in dump file (sed -i 's/MyISAM/InnoDB/g' dbdump.sql) and import again. Thanks, On Mon, Apr 19, 2010 at 9:53 AM, Rob Wultsch wult...@gmail.com wrote: On Sun, Apr 18, 2010 at 8:31 PM, Angelina Paul arshup...@gmail.com wrote: I want to change the mysql default storage engine from MyISAM to InnoDB. What are the steps involved .Is it edit my.cnf file and add a line default-storage-engine=innodb and restart the mysql server? If you do not want to change any existing tables all you need to do is add the line to your cnf. How I can bring my databases with mixed storage engine down without any data loss. mysqladmin shutdown What steps I have to take if I encounter a page corruption in innodb tables. The right answer is restore from backup or failover to a slave. The answer you probably want is http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html why I am getting a message *Error*: *No query specified* when I run a show engines\g commands -version (5.0.45) Exactly what are you running? -- Rob Wultsch wult...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=aim.prab...@gmail.com -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
