Category Archives: MySQL
MySQL: Query to get the MySQL database size in MB
SELECT table_schema "Database Name", SUM(data_length+index_length)/1024/1024 "Database Size in MB", SUM(data_free)/1024/1024 "Free Space in MB" FROM information_schema.TABLES GROUP BY table_schema;
Related articles
- PHP MySql Delete not working (daniweb.com)
- PHP & MySQL (daniweb.com)
The total number of locks exceeds the lock table size
This issue can be resolved by setting the higher values for the MySQL variable “innodb_buffer_pool_size”. The default value for innodb_buffer_pool_size will be 8,388,608.
To change the settings value for “innodb_buffer_pool_size” please see the below set.
1) Locate the file my.cnf from the server. For Linus servers this will be mostly at /etc/my.cnf
2) Add the line “innodb_buffer_pool_size=64MB” to this file
3) Restart the MySQL server
To restart the MySQL server, you can use anyone of the below 2 options:
1) service mysqld restart
2) /etc/init.d/mysqld restart
Related articles
- MySQL Tips For WordPress That Don’t Suck (bostinno.com)
- Troubleshooting MySQL Memory Usage (mysqlperformanceblog.com)
- Fixing MySQL tables in Fedora 17 (morefedora.blogspot.com)
Backuping a single table using mysqldump
Backuping a single table from a database
mysqldump -u USERNAME -p DATABASE_NAME TABLE_NAME > /path/to/folder/file_name.sql
Related Articles
- An argument for not using mysqldump (mysqlperformanceblog.com)
- 90% Answers (and when they’re wrong) (standalone-sysadmin.com)
- Learn To Program In 24 Hours (notes.torrez.org)
- Replication of MEMORY (HEAP) Tables (mysqlperformanceblog.com)
- Skyler Call: Backup Linux Server To Amazon S3 (skylercall.com)
- Baron Schwartz interviewed on WebPulp.tv (mysqlperformanceblog.com)
- Oracle updates MySQL 5.5 for Windows and web (v3.co.uk)
- MySQL in the cloud at Airbnb – Airbnb Engineering – Nerds Blog (nerds.airbnb.com)
- SetUp and test MySQL Replication in 20 easy steps (learnmysql.blogspot.com)
- How to Back Up a BlackBerry Enterprise Server (brighthub.com)
MySql: query result in csv file using command
Form the mysql prompt use the below command:
SELECT * FROM table_name INTO OUTFILE '/tmp/table_name.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
Related Articles
- Processing Tweets with LingPipe: Search and CSV Data Structures (lingpipe-blog.com)
- SQL Pocket Guide (oreilly.com)
- How to export data from a SQL database into a CSV file and add some new data? (ask.metafilter.com)
- Percona white paper: Forecasting MySQL Scalability (mysqlperformanceblog.com)
- Advanced index analysis with mk-index-usage (mysqlperformanceblog.com)
- 6 CSV Related Libraries/Functions In Actionscript Save Your Time – Ntt.cc (ntt.cc)
- ProjectTemplate Version 0.1-3 Released (r-bloggers.com)
- DBF Viewer 2000 v3.45: View and Edit DBF Files with a Really Fast Tool (prweb.com)
- Enabling InnoDB databases (myeasylinux.wordpress.com)
- Integrate disparate data sources with Semantic Web technology (ibm.com)
- MySQL in the cloud at Airbnb – Airbnb Engineering – Nerds Blog (nerds.airbnb.com)
- Shard-Query adds parallelism to queries (mysqlperformanceblog.com)
- Clementson’s Blog: CLSQL – Part 1 (bc.tech.coop)
- Reset MySql root password (myeasylinux.wordpress.com)
- MySQL: How to Selectively Update a Table from a CSV List? (ask.metafilter.com)
- Do MySQL privileges really work like this? (ask.metafilter.com)
- Connecting to a MySQL server using Visual Studio 2010 and VB.Net (edugeek.net)
- Comparing MySQL and Postgres 9.0 Replication (theserverside.com)
- imabonehead: Top 20 Design Tips for MySQL Data Architects (slideshare.net)
- Add NoSQL Capabilities to MySQL With the HandlerSocket Plugin (readwriteweb.com)
- What’s New in MySQL 5.5 (blogs.sitepoint.com)
- Use gkDebconf to reconfigure debconf-using programs (ghacks.net)
- Schedule for MySQL-and-beyond conference is live (xaprb.com)
- How to Move from MySQL to CouchDB: Part 1 (couchone.com)
- Iptables Logs Mapping on GoogleMaps (rootshell.be)
- ovigia: 12 Best MySQL Database #Books for Your Library (thegeekstuff.com)
MySQL: query result to file using console
Given a query like below to log the results to a text file
select id from accounts where deleted=1;
try using the command below to output the result to file to ‘C:\test.txt’
select id from accounts where deleted=1 into outfile 'C:\\test.txt';
try using the command below to output the reuslt to csv
select id from accounts where deleted=1 INTO OUTFILE 'C:\\test.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';
Related Articles
- SQL Group By Statement: Explanation & Examples (brighthub.com)
- The story of one MySQL Upgrade (mysqlperformanceblog.com)
- Amazon Scales MySQL for Heavy Traffic (java.sys-con.com)
- SQL Replace Function: Explanation & Examples (brighthub.com)
- http://dumptruck.rubyforge.org/svn/README (dumptruck.rubyforge.org)
- DBF Viewer 2000 v3.45: View and Edit DBF Files with a Really Fast Tool (prweb.com)
- Blind SQL Injector Tool (acunetix.com)
- Tips For Logging Your Own Statistics (readwriteweb.com)
- pgedit.com | Power Tools for PostgreSQL (pgedit.com)
- AWS Management Console Support for DB Engine Version Management (aws.typepad.com)
- Export MYSQL To Text File (edugeek.net)
- ARel 2.0: Active Record in Rails 3.0.2 (engineering.attinteractive.com)
- Using ioprofile with MySQL is cool ! (serge.frezefond.free.fr)
- JQuery 1.4.3 and jQuery Mobile Expected on October 16th (css.dzone.com)
mysql: ERRIR 3 : Error writing file ‘C:\WINDOWS\TEMP\MYD62.tmp’ 13631488
For me this was due to the temp folder was stored at a FAT32 drive. Changes the temp directory to NTFS drive and the problem solved.
On my.ini file change the variable tmpdir or create one using tmpdir = Your\path
Related Articles
- Howto setup mysql-cluster (beginners tutorial) (teabreak.pk)
- Mysql Essentials Part 1 (slideshare.net)
- Mysql pathological case? (almirkaric.com)
Sum of two columns in 1 query MySQL
select sum(column,column1) from table;Related Articles
- SQL Group By Statement: Explanation & Examples (brighthub.com)
- How LOCK TABLES interacts with the MySQL slow query log (xaprb.com)
- MySQL Limitations Part 2: The Binary Log (mysqlperformanceblog.com)
- How to Transpose a Table in SQL (brighthub.com)
- SQL Server Date field issues (edugeek.net)
- Pro PHP and jQuery (i-programmer.info)
- The story of one MySQL Upgrade (mysqlperformanceblog.com)
- The Boston MySQL Meetup – October 11th (infobright.org)
- I wish I could be at PGWest (xaprb.com)
- Hot Scalability Links For Oct 24, 2010 (highscalability.com)
- “Katy Perry and Russell Brand: Married!” and related posts (thehollywoodgossip.com)
- “Giants v. Rangers World Series is Good for Baseball” and related posts (overthemonster.com)
- “Manchester City 0-3 Arsenal: Premier League Highlights (Video)” and related posts (caughtoffside.com)
- “Juan Williams Got Screwed by NPR” and related posts (integral-options.blogspot.com)
- “Halle Berry, Olivier Martinez Make Red Carpet Debut” and related posts (usmagazine.com)
import cvs through mysql console
Use this command on mysql console
load data LOCAL infile 'C:/test.csv' into table table_name fields terminated by ',' lines terminated by '\n';Courtesy : How do I import delimited data into MySQL?
Related Articles
- Xeround announces MySQL for the Cloud (zdnet.com)
- How To: Backup and Restore Your MySQL Database (readwriteweb.com)
- Xeround scales MySQL for the cloud (news.cnet.com)
- Troubleshooting : mysql server has gone away (jayant7k.blogspot.com)
- Self-Service MySQL Help Desk from ProMend Connects Companies Worldwide to Certified MySQL Experts in the U.S. (eon.businesswire.com)
- MyWebSQL: Manage your MySQL Databases with this Free Open Source MySQL client (crenk.com)
- Free webinar on MySQL performance this Thursday (xaprb.com)
- Oracle is improving MySQL (xaprb.com)
change mysql password
- Click Start from the toolbar
- Click “Run”
- Type “cmd” and press “Enter”
- Browse to your xampp directory
- Change the directory to xampp\mysql\bin. (Ex: I have installed it on my E:\ so browse to the directory E:\xampp\mysql\bin)
- Type “mysqladmin -u root password NEWPASSWORD”
Your password for the mysql will be changed.
change mysql password to null
- Click Start from the toolbar
- Click “Run”
- Type “cmd” and press “Enter”
- Browse to your xampp directory
- Change the directory to xampp\mysql\bin. (Ex: I have installed it on my E:\ so browse to the directory E:\xampp\mysql\bin)
- Type “mysql -u root -p PASSWORD”. The prompt will change to mysql>
- Type “use mysql”. “The database will be changed to mysql”
- Type “update user set password=PASSWORD(“”") where User=’root’;
- Type “flush privileges;”
- Type “quit”
Your password for the mysql will be set to null.
Related Articles
- WordPress + Xampp web hosting. (edugeek.net)
- ovigia: Using digiKam with MySQL ” Scribbles and Snaps (scribblesandsnaps.wordpress.com)
- imabonehead: Python script to backup mysql databases on Debian servers | Once a geek, forever a geek… (mariusv.com)
- High availability for MySQL on Amazon EC2 – Part 5 – The instance monitoring script (mysqlperformanceblog.com)
- MySQL Limitations Part 2: The Binary Log (mysqlperformanceblog.com)
- The EU Was Nothing, Oracle’s Still Got to Fight for Control of MySQL (java.sys-con.com)
- How to Synchronize Your XBMC Media Center Between Every Room in the House [How To] (lifehacker.com)
- Change the Root Password in Ubuntu 10.04 (helpdeskgeek.com)
- Howto setup mysql-cluster (beginners tutorial) (teabreak.pk)
- How to use AJAX with MySQL (thoughtmechanics.com)
