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;

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

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

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';

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';

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

Sum of two columns in 1 query MySQL

select sum(column,column1) from table;

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

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.

access violation at address 10002593

Getting the error while starting the WinMySQLadmin :

access violation at address 10002593 in module ‘LIBMYSQL.dll’ read at address 00000000.”

Solution :

Start > All Programs > Startup > WinMySQLadmin > Click on the my.ini Setup tab

Change the below default line at the bottom :
user=root
password=password

to

user=root
password=

Useful Tips:
From the comments by Bobby B
right click on the stoplight icon in the system tray and select “show me,” the window will stay visible while the popup error messages occur, leaving you time enough to navigate to my.ini, find where to edit, and then save the modifications… all the while closing error messages

Follow

Get every new post delivered to your Inbox.

Join 268 other followers

%d bloggers like this: