[Technote] MySQL tips for Linux Ubuntu

I forgot my MySQL server root user password and wondered how I can reset or retrieve my old password in any possible ways. After all, if I couldn't figure out the password or how to reset it, I simply cannot use MySQL server. So here is what I found out about how:

First, to do some little troubleshooting, you might need to make sure your mysql server is running properly:

sudo netstat -tap | grep mysql

And you should see the following message on your terminal:

tcp  0  0  localhost:mysql     *:*     LISTEN      2556/mysqld

If the server is not running properly, you can type this:

sudo /etc/init.d/mysql restart

After that, you can also do some configurations by editing /etc/mysql/my.cnf file. Don't forget to restart your mysql server again after reconfiguration. 

Here comes the most important part - If you would like to change the MySQL root password, in a terminal enter:

sudo dpkg-reconfigure mysql-server-5.1

The mysql daemon will be stopped, and you will be prompted to enter a new password. And you are done. Log in your mysql server next time by entering:

$ mysql -u root -p

or

$ mysql -uroot -ppassword

SQL [CREATE INDEX]

The CREATE INDEX statement is used to create indexes in tables. Indexes allow the database application to find data fast; without reading the whole table. The users cannot see the indexes, they are just used to speed up searches/queries.

Note: Updating a table with indexes takes more time than updating a table without (because the indexes also need an update). So you should only create indexes on columns (and tables) that will be frequently searched against.

Look up its syntax.

SQL constraints concepts

Constraints are used to limit the type of data that can go into a table.

Constraints can be specified when a table is created (with the CREATE TABLE statement) or after the table is created (with the ALTER TABLE statement).

Here are several SQL constraints:

• NOT NULL
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY - A FOREIGN KEY in one table points to a PRIMARY KEY in another table.
• CHECK
• DEFAULT

SQL TOP clause and its MySQL equivalent

A note on SQL and MySQL:

SELECT TOP number|percent column_name(s)
FROM table_name

is equivalent to:

SELECT column_name(s)
FROM table_name
LIMIT number

Example:

SELECT *
FROM Persons
LIMIT 5

LAMP server stack installed

Today I installed full stack LAMP package and have them properly configured on my laptop. This server is only for my own development server and not for public use :)

LAMP rocks! Everything on my laptop now is opensource solution! I might try Drupal next and bundle it with my LAMP!

Here is howto:

https://help.ubuntu.com/community/ApacheMySQLPHP