MySQL

Delete (Drop) MySQL database

It is very easy to drop a MySQL database:

drop database database_name

Tons of blogs and articles online mention this command. Recently, however, I came across a database name which had spaces in them. For example:

company balances
electronics inventory
store employees
...

If I do:

drop database company balances

MySQL would complain that the databases company and balances do not exists. Naturally, the first thought on my mind was to enclose them the database.

MySQL Alter Table

Alter table command is used for altering tables, table fields and table indexes

To rename a table:

ALTER TABLE mytable RENAME yourtable;

To add a column:

ALTER TABLE mytable ADD COLUMN update TIMESTAMP FIRST
ALTER TABLE mytable ADD COLUMN update TIMESTAMP AFTER id
ALTER TABLE mytable ADD COLUMN update TIMESTAMP LAST

The first example create a column in the first position. The second command creates a column after the column id. The last example creates a column in the last position.

Copying a large database from one server to another

Recently, I had to copy a large database from one server to another. I had 100G free on the source server. Unfortunately, the server would run out of memory before the mysqldump would complete. So I had to find a way to make the mysqldumps smaller. This can be done by creating separate dumps for each table. Then I had to compress the files to conserve memory. Following is the script I wrote to accomplish this.

Listing all tables and their table counts in a MySQL database

The Following SQL query will list all tables in a MySQL database and also list the row counts for each.

SELECT TABLE_NAME, TABLE_ROWS 
FROM `information_schema`.`tables` 
WHERE `table_schema` = 'mydatabase';

where mydatabase is the name or your database. The output will like something like the following:

+------------+------------+
| table_name | table_rows |
+------------+------------+
| lines      |       2271 |
| links      |        484 |
| word       |      25004 |
+------------+------------+

Pages