Recover password MySQL

Step # 1 : Stop mysql service
# /etc/init.d/mysql stop

Step # 2: Start to MySQL server w/o password:
# mysqld_safe –skip-grant-tables &

Or

edit my.cnf

[mysqld]

skip-grant-tables

start the server

service mysqld start

Step # 3: Connect to mysql server using mysql client:
# mysql -u root

Step # 4: Setup new MySQL root user password
mysql> use mysql;
mysql> update user set password=PASSWORD(”NEW-ROOT-PASSWORD”) where User=’root’;
mysql> flush privileges;
mysql> quit

Step # 5: Stop MySQL Server:
# /etc/init.d/mysql stop

Step # 6: Start MySQL server and test it
# /etc/init.d/mysql start
# mysql -u root -p

MySQL - Cheat Sheet

MySQL is a small, fast and highly configurable DBMS. It supports a number of different table fileformats, depending on the requirements of the user.

These are the main MySQL clients and processes (mysqld):

• mysqld - MySQL server daemon
• safe_mysqld - Server process monitor
• mysqlaccess - Tool for creating MySQL users
• mysqladmin - Utility for administering MySQL
• mysqldump - Tool for dumping the contents of a MySQL database. Useful for backing up a database from within the console.
  
• mysql - Command line interface to MySQL
• mysqlshow - List all MySQL database
• Mysql Administrator - This is a GUI tool which makes administering mysql database a painless task. Read more about it here.
  

Field Types in SQL

INTEGER - A whole number
VARCHAR(10) - Up to 10 characters.
CHAR(10) - Fixed number of characters
DATE - A date
DATETIME - Date and time
FLOAT - Floating point numbers

Field Types specific to MySQL

TEXT - Allows up to 65535 characters
DECIMAL(10,2) - Up to 10 digits before the point, 2 after.

Create a database

$ mysqladmin --user=ravi --password=xxx create database addressdb

Using the database

$ mysql --user=ravi --password=xxx
mysql> USE addressdb

Create a table

mysql> CREATE TABLE p_addr (i INTEGER PRIMARY KEY,address TEXT,email VARCHAR(30),pincode DECIMAL(10),phone DECIMAL(15),website TEXT);

Add a column called "name" to the table

mysql> ALTER TABLE p_addr ADD name VARCHAR(30);

Inserting values into table

mysql> INSERT INTO p_addr VALUES (1,"My, present, address","ravi@localhost",681024,2122536, "http://linuxhelp.blogspot.com","Ravi");

List the contents of the table

mysql> SELECT * FROM p_addr;

Delete a row from the table

mysql> DELETE FROM p_addr WHERE i=1;

Rename a column in the table from "address" to "home_address"

mysql> ALTER TABLE p_addr CHANGE address home_address INTEGER;

Note: You cannot use this method to rename a column which is a primary key.

Change an existing record in the table

mysql> UPDATE p_addr SET name="Sumitra" WHERE i=2;

Delete the table from the database

mysql> DROP TABLE p_addr;

List the databases

$ mysqlshow --user=ravi --password=xxx
+-----------+
| Databases |
+-----------+
| addressdb |
| myblog    |
| mysql     |
| test      |
+-----------+

List the tables in the database "addressdb"

$ mysqlshow --user=ravi --password=xxx addressdb

Database: addressdb
+---------+
| Tables  |
+---------+
| p_addr  |
| mytble  |
| phonebk |
+---------+

These are only a subset of the commands in mysql. But this will be enough for creating and maintaining a simple database.

Sun acquires MySQL

Sun acquires MySQL arguably the most widely used database on the net. Read the announcement here. Jonathan Schwartz the CEO of Sun Microsystems, on his blog, has termed this acquisition as putting a billion dollars behind the 'M' in 'LAMP'.

Personally I feel this will turn out to be a very good thing for Open Source. I would rather it be Sun Microsystems than some other company. So does this sound the death knell for proprietary databases like Oracle?

Beginner MySQL, PHP and Perl tutorials in pictures

Inpics - short for In Pictures is a website with a difference. While most websites provide tutorials and howtos which are quite verbose, Inpics takes a different path to teach the basics of technology related subjects. It explains things in pictures. And indeed, a picture is worth a 1000 words. The pictures are simple black and white photos.

They have a growing collection of tutorials which are quite easy for beginners. Inpics began as part of a research study conducted for the U.S. Department of Education. The goal was to make it easier for people with learning disabilities to grasp computer subjects.

As part of the study, they created simple, illustration-based tutorials. Everyone who tested them—not just people with learning disabilities— are claimed to have said the new tutorials enabled them to learn faster and easier than conventional text-heavy books.

For the Linux enthusiast, they have a couple of tutorials on MySQL,PHP and Perl which are quite refreshing to follow. Do check them out.