Fun at work!

Posts tagged ‘MySQL’

How to check MySQL version?

On the command line, enter the command:

 

# mysqladmin -uroot -p -hlocalhost version

Enter password: ********

mysqladmin  Ver 8.42 Distrib 5.1.73, for redhat-linux-gnu on x86_64
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Server version          5.1.73
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/lib/mysql/mysql.sock
Uptime:                 16 hours 18 min 39 sec

Threads: 1  Questions: 296  Slow queries: 0  Opens: 25  Flush tables: 1  Open tables: 18  Queries per second avg: 0.5

 

There you can get the version of MySQL.

Link

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:

ALTER TABLE contacts ADD email VARCHAR(60) FIRST;

– See more at: http://www.tech-recipes.com/rx/378/add-a-column-to-an-existing-mysql-table/#sthash.bGG7f6EK.dpuf

How to fix: “Another MySQL daemon already running with the same unix socket.”

You can see this error when restarting MySQL. The system has detected that MySQL is running when it is not. This is probably due to and hard reboot of the system.

To fix this problem, remove the file /var/lib/mysql/mysql.sock as root user:

# rm /var/lib/mysql/mysql.sock

Then start the MySQL daemon:

# service mysqld start

 or

# /etc/init.d/mysqld start

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