Fun at work!

Posts tagged ‘database’


Repost: Add a column to an existing MySQL table

Repost: Add a column to an existing MySQL table

MySQL tables are easy to extend with additional columns.

To add a column called email to the contacts table created in Create a basic MySQL table with a datatype of VARCHAR(80), 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:


– See more at:

How to delete spam comments on WordPress?

I am self-hosting a WordPress blog site (not this one). And though I did not allow “Comments”, my site received hundred and thousands of spam comments. To delete these unapproved comments all at once, I directly access my database and issue the command:

mysql> DELETE FROM wp_comments WHERE comment_approved = '0';

Note: I have not installed a plugin for spam protection like Akismet.

How to reset MySQL root password?

It is easy to forget the root password as it is advised to use the user account for security reason. Today, I needed to access my MySQL database as root, but I could not remember the password. After searching for a solution, I found this method to be awesome:

1. Run the server with –skip-grant-tables option so that you can log in without a password.

# /etc/init.d/mysqld stop
# mysqld_safe --skip-grant-tables &
$ mysql -u root

2. Reset the root password.

mysql> use mysql;
mysql> update user set password=PASSWORD("YOUR-NEW-ROOT-PASSWORD") where user='root';
mysql> flush privileges;
mysql> quit

3. Restart MySQL and log in using your new root password.

# /etc/init.d/mysqld stop
# /etc/init.d/mysqld start
# mysql -u root -p

Tag Cloud