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;
MySQL: Query to get the MySQL database size in MB

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

The total number of locks exceeds the lock table size

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

Backuping a single table using mysqldump

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 in csv file using command

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: query result to file using console

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

mysql: ERRIR 3 : Error writing file ‘C:\WINDOWS\TEMP\MYD62.tmp’ 13631488

Sum of two columns in 1 query MySQL

select sum(column,column1) from table;
Sum of two columns in 1 query MySQL