MySQL: HOW TO GET THE DATABASE SIZE FROM THE MYSQL QUERY

Run the below query you can get the Data Base Size in MySQL. 
If you run the query which is given below in MySQL Query Browser then you will get the two columns first will display the Data Base Name and the second will display the Data Base Size in MB.

mysql# SELECT table_schema "Data Base Name", sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema ;


And if you want to know How to view the Free space available for my Data Base in MySQL?
Run the below query:


mysql# SELECT table_schema "Data Base Name", 
sum( data_length + index_length ) / 1024 / 
1024 "Data Base Size in MB", 
sum( data_free )/ 1024 / 1024 "Free Space in MB" 
FROM information_schema.TABLES 
GROUP BY table_schema ;

.

MYSQL: Add a column to an existing MySQL table

MySQL tables are easy to extend with additional columns.

Use the following SQL statement:

ALTER TABLE contacts ADD email VARCHAR(60);

This first statement will add the email column to the end of the table. To insert the new column after a specific column, such as name, use this statement:

ALTER TABLE contacts ADD email VARCHAR(60) AFTER name;

If you want the new column to be first, use this statement:

ALTER TABLE contacts ADD email VARCHAR(60) FIRST;

..

MySQL: Selectively dumping data with mysqldump

By default mysqldump will dump all data from a table, but it is possible to select which data to be exported with mysqldump


Examples: db= test   &  table = mytable
  columns: mytable_id, category_id and name

Using mysqldump to dump all data from the table would look like this, subsituting [username] for your username (the -t flag suppresses the table creation sql from the dump):

mysqldump -t -u [username] -p test mytable

The output from my example table looks like this, once we remove all the extra SQL commands (I've added linebreaks to make it more legible):

INSERT INTO `mytable` VALUES 
  (1,1,'Lorem ipsum dolor sit amet'),
  (2,1,'Ut purus est'),
  (3,2,'Leo sed condimentum semper'),
  (4,2,'Donec velit neque'),
  (5,3,'Maecenas ullamcorper');

If we only wanted to dump data from mytable in category_id 1, we would do this:

mysqldump -t -u [username] -p test mytable --where=category_id=1

which would output this:

INSERT INTO `mytable` VALUES 
  (1,1,'Lorem ipsum dolor sit amet'),
  (2,1,'Ut purus est');

An example of dumping data from two tables using the same where clause could look like this, where we are selecting category_id from tables "mytable" and "anothertable":

mysqldump -t -u [username] -p test mytable anothertable --where="category_id = 1"

Refer: http://www.electrictoolbox.com/mysql-execute-statements-text-file/
...

MySQL : Use mysqldump to get the schema only

Dumping the database structure for all tables with no data

Add the -d flag to signify that no data should be included in the output like so, where "mydatabase" is the name of the database to dump, and "someuser" is the login name used to connect to the database

mysqldump -d -u someuser -p mydatabase

Dumping the database structure for one table with no data

mysqldump -d -u someuser -p mydatabase products

Dumping the database structure for several table with no data

mysqldump -d -u someuser -p mydatabase products categories users

Dumping the structure to a file

mysqldump -d -u someuser -p mydatabase > mydatabase.sql

Restore the structure file to mysql

mysql -u someuser -p anotherdatabase < mydatabase.sql

Reference:  http://www.electrictoolbox.com/mysqldump-schema-only/
..

Mysql: Repairing mysql database

Run this command:

# mysqlcheck -u root -p --auto-repair --check --optimize --all-databases