Setting the Mysql into a ReadOnly mode..

whole database to read only mode by this commands:

In the MySQL Prompt

FLUSH TABLES WITH READ LOCK;
SET GLOBAL read_only = 1;

and back to normal mode with:
SET GLOBAL read_only = 0;
UNLOCK TABLES;

MSSQL Connection Testpage

<?php
$Server = "localhost";
$User = "your_name";
$Pass = "your_password";
$DB = "examples";

//connection to the database
$dbconn = mssql_connect($Server, $User, $Pass)
or die("Couldn't connect to SQL Server on $Server");

//select a database to work with
$selected = mssql_select_db($DB, $dbconn)
or die("Couldn't open database $myDB");

//declare the SQL statement that will query the database
$query = "SELECT name from test ";

//execute the SQL query and return records
$result = mssql_query($query);

$numRows = mssql_num_rows($result);
echo "<h1>" . $numRows . " Row" . ($numRows == 1 ? "" : "s") . " Returned </h1>";

//display the results
while($row = mssql_fetch_array($result))
{
echo "<br>" . $row["name"];
}
//close the connection
mssql_close($dbconn);
?>

Setting password for mysql user in .my.cnf

Setting password for mysql user in .my.cnf

Sometimes you want automated access for root on your MySQL database. One way of accomplishing that is by doing this:

# cd /root
# touch .my.cnf
# chmod 640 .my.cnf

And put in it:

[client]
user=root
password=<password of mysql root user>
Once you have done the following steps, the root user can login to mysql as root user without giving password.
root@server [~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 749
Server version: 5.1.68-cll MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

mysql>

Complile and install MYSQL

Complile and install MYSQL
Preliminary installations

[root@vps ~]# yum -y install ncurses-devel
Complile and install Mysql

[root@vps ~]# groupadd mysql
[root@vps ~]# useradd -r -g mysql mysql
[root@vps ~]# cd /usr/local/src/
[root@vps src]# wget http://downloads.mysql.com/archives/mysql-5.5/mysql-5.5.0-m2.tar.gz
[root@vps src]# tar -xzf mysql-5.5.0-m2.tar.gz
[root@vps src]# cd mysql-5.5.0-m2

[root@vps mysql-5.5.0-m2]# ./configure --prefix=/usr/local/mysql --localstatedir=/usr/local/mysql/data --disable-maintainer-mode --with-mysqld-user=mysql --with-unix-socket-path=/tmp/mysql.sock --without-comment --without-debug --without-bench

[root@vps mysql-5.5.0-m2]# make && make install

[root@vps mysql-5.5.0-m2]# ./scripts/mysql_install_db

[root@vps mysql-5.5.0-m2]# chown -R root:mysql /usr/local/mysql
[root@vps mysql-5.5.0-m2]# chown -R mysql:mysql /usr/local/mysql/data
-To set the proper ownership for the MySQL directories and data files, so that only MySQL (and root) can do anything with them.

***Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
[root@vps mysql-5.5.0-m2]# cp support-files/my-medium.cnf /etc/my.cnf
[root@vps mysql-5.5.0-m2]# chown root:sys /etc/my.cnf
[root@vps mysql-5.5.0-m2]# chmod 644 /etc/my.cnf
[root@vps ~]# cd /usr/local/mysql/bin
[root@vps ~]# for file in *; do ln -s /usr/local/mysql/bin/$file /usr/bin/$file; done
-To set up symlinks for all the MySQL binaries, so they can be run from anyplace without having to include/specify long paths.
Create Startup service for mysql
[root@vps ~]# echo "/usr/local/mysql/lib/mysql" >> /etc/ld.so.conf
***Do not issue the above command more than once.

[root@vps ~]# ldconfig
[root@vps ~]# cp ./support-files/mysql.server /etc/rc.d/init.d/mysql
[root@vps ~]# chmod +x /etc/rc.d/init.d/mysql
[root@vps ~]# chkconfig mysql on

You can now start/stop mysql using the following commands.

[root@vps ~]# /etc/rc.d/init.d/mysql start
[root@vps ~]# /etc/rc.d/init.d/mysql stop
***If you are encounter any problems in start and stop mysql, you can find the reason from the error log of mysql. Error log name is in the <hostname>.err format.
Here my server hostname is vps.arun.com and therefore the error log name og mysql is vps.arun.com.err . You can use find the error log in the var directory of mysql installation. Here in my case it is /usr/local/mysql/var/vps.arun.com.err.
You can set new mysql root password using the followiing command.
[root@vps ~]# mysqladmin -u root password <newpassword>

Notes:-

Error:
Configure: error: No curses termcap library found
Fix:
[root@vps mysql]# yum install ncurses-devel -y
Error:
Error in /usr/local/mysql/var/vps.arun.com.err
/usr/local/mysql/libexec/mysqld: File './mysql-bin.index' not found (Errcode: 13)
fix:
Change the ownership of var directory in mysql's installation directory, as like follows.
chown -R mysql:mysql /usr/local/mysql/var

Error while connecting PHPMyAdmin

Error while connecting PHPMyAdmin
Getting the following error when connecting to PhpMyAdmin :

"#2013 Cannot log in to the MySQL server"

Solution:

# vi /usr/local/cpanel/base/3rdparty/phpMyAdmin/config.inc.php // Configuration file of PhpMyAdmin

Add localhost as follows

$cfg['Servers'][$i]['host'] = 'localhost';

save and quit the file.

MYSQL

mysql> create database kerala_wp1;
Query OK, 1 row affected (0.00 sec)

mysql> create user wp1;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL ON kerala_wp1.* TO 'wp1'@localhost IDENTIFIED BY 'keralainasia';
Query OK, 0 rows affected (0.00 sec)

mysql> REVOKE ALL PRIVILEGES ON kerala_wp1.* FROM 'wp1'@localhost;
Query OK, 0 rows affected (0.00 sec)

 

# [mysql dir]/bin/mysql -h hostname -u root -p

mysql> create database [databasename];
mysql> show databases;

mysql> use [db name];
mysql> show tables;

mysql> describe [table name];

mysql> drop database [database name];
mysql> drop table [table name];

mysql> SELECT * FROM [table name];
mysql> show columns from [table name];
grant usage on *.* to bob@localhost identified by ‘passwd’;
grant all privileges on databasename.* to username@localhost;
flush privileges;

SET PASSWORD FOR ‘user’@'hostname’ = PASSWORD(‘passwordhere’);

+ Check, Repair and Optimize All tables in All Databases when you’re running a MySQL server on Linux.
# mysqlcheck –auto-repair –check –optimize –all-databases

OR
# mysqlcheck –all-databases -r #repair databases
# mysqlcheck –all-databases -a #analyze databases
# mysqlcheck –all-databases -o #optimize databases

=> Check, Repair and Optimize Single Database Tables.
# mysqlcheck –auto-repair –check –optimize CpanelUsername_Databasename
# mysqlcheck -ro CpanelUsername_Databasename

=> To repair One Table in database:
# mysqlcheck -ro CpanelUsername_Databasename table_name
Shows you if any need repair:
# myisamchk –check /var/lib/mysql/*/*.MYI

Then try ‘safe-recover’ first:
# myisamchk –safe-recover /var/lib/mysql/*/*.MYI

and, if neither “safe-recover” or “recover” option works:
# myisamchk –recover /var/lib/mysql/*/*.MYI

Then use the ‘force’ flag:
# myisamchk –recover –extend-check –force /var/lib/mysql/*/*.MYI
mysql> REVOKE INSERT,UPDATE,DELETE ON DATABASENAME.* FROM user1@localhost;

REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user]…

REVOKE ALL PRIVILEGES OPTION FROM 'wp1'@'localhost';

REVOKE ALL PRIVILEGES ON kerala_wp1.* FROM 'wp1'@localhost;
Dump a table from a database.
[mysql dir] mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql

Restore database (or database table) from backup.
[mysql dir] mysql -u username -ppassword databasename < /tmp/databasename.sql
mysql> create database kerala_wp1;
Query OK, 1 row affected (0.00 sec)

mysql> create user wp1;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL ON kerala_wp1.* TO 'wp1'@localhost IDENTIFIED BY 'keralainasia';
Query OK, 0 rows affected (0.00 sec)

mysql> REVOKE ALL PRIVILEGES ON kerala_wp1.* FROM 'wp1'@localhost;
Query OK, 0 rows affected (0.00 sec)

Mysql

To login (from unix shell) use -h only if needed.

# [mysql dir]/bin/mysql -h hostname -u root -p

 

Create a database on the sql server.

mysql> create database [databasename];

 

List all databases on the sql server.

mysql> show databases;

 

Switch to a database.

mysql> use [db name];

 

To see all the tables in the db.

mysql> show tables;

To see database’s field formats.

mysql> describe [table name];

 

To delete a db.

mysql> drop database [database name];

 

To delete a table.

mysql> drop table [table name];

 

Show all data in a table.

mysql> SELECT * FROM [table name];

 

Returns the columns and column information pertaining to the designated table.

mysql> show columns from [table name];

 

Show certain selected rows with the value “whatever”.

mysql> SELECT * FROM [table name] WHERE [field name] = “whatever”;

 

Show all records containing the name “Bob” AND the phone number ’3444444′.

mysql> SELECT * FROM [table name] WHERE name = “Bob” AND phone_number = ’3444444′;

 

Show all records not containing the name “Bob” AND the phone number ’3444444′ order by the phone_number field.

mysql> SELECT * FROM [table name] WHERE name != “Bob” AND phone_number = ’3444444′ order by phone_number;

 

Show all records starting with the letters ‘bob’ AND the phone number ’3444444′.

mysql> SELECT * FROM [table name] WHERE name like “Bob%” AND phone_number = ’3444444′;

 

Show all records starting with the letters ‘bob’ AND the phone number ’3444444′ limit to records 1 through 5.

mysql> SELECT * FROM [table name] WHERE name like “Bob%” AND phone_number = ’3444444′ limit 1,5;

 

Use a regular expression to find records. Use “REGEXP BINARY” to force case-sensitivity. This finds any record beginning with a.

mysql> SELECT * FROM [table name] WHERE rec RLIKE “^a”;

 

Show unique records.

mysql> SELECT DISTINCT [column name] FROM [table name];

 

Show selected records sorted in an ascending (asc) or descending (desc).

mysql> SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;

 

Return number of rows.

mysql> SELECT COUNT(*) FROM [table name];

 

Sum column.

mysql> SELECT SUM(*) FROM [table name];

 

Join tables on common columns.

mysql> select lookup.illustrationid, lookup.personid,person.birthday from lookup left join person on lookup.personid=person.personid=statement to join birthday in person table with primary illustration id;

 

Creating a new user. Login as root. Switch to the MySQL db. Make the user. Update privs.

# mysql -u root -p
mysql> use mysql;
mysql> INSERT INTO user (Host,User,Password) VALUES(‘%’,'username’,PASSWORD(‘password’));
mysql> flush privileges;

 

Change a users password from unix shell.

# [mysql dir]/bin/mysqladmin -u username -h hostname.blah.org -p password ‘new-password’

 

Change a users password from MySQL prompt. Login as root. Set the password. Update privs.

# mysql -u root -p
mysql> SET PASSWORD FOR ‘user’@'hostname’ = PASSWORD(‘passwordhere’);
mysql> flush privileges;

 

Recover a MySQL root password. Stop the MySQL server process. Start again with no grant tables. Login to MySQL as root. Set new password. Exit MySQL and restart MySQL server.

# /etc/init.d/mysql stop
# mysqld_safe –skip-grant-tables &
# mysql -u root
mysql> use mysql;
mysql> update user set password=PASSWORD(“newrootpassword”) where User=’root’;
mysql> flush privileges;
mysql> quit
# /etc/init.d/mysql stop
# /etc/init.d/mysql start

 

Set a root password if there is on root password.

# mysqladmin -u root password newpassword

 

Update a root password.

# mysqladmin -u root -p oldpassword newpassword

 

Allow the user “bob” to connect to the server from localhost using the password “passwd”. Login as root. Switch to the MySQL db. Give privs.
Update privs.

# mysql -u root -p
mysql> use mysql;
mysql> grant usage on *.* to bob@localhost identified by ‘passwd’;
mysql> flush privileges;

 

Give user privilages for a db. Login as root. Switch to the MySQL db. Grant privs. Update privs.

# mysql -u root -p
mysql> use mysql;
mysql> INSERT INTO db (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES (‘%’,'databasename’,'username’,'Y’,'Y’,'Y’,'Y’,'Y’,'N’);
mysql> flush privileges;

or

mysql> grant all privileges on databasename.* to username@localhost;
mysql> flush privileges;

 

To update info already in a table.

mysql> UPDATE [table name] SET Select_priv = ‘Y’,Insert_priv = ‘Y’,Update_priv = ‘Y’ where [field name] = ‘user’;

 

Delete a row(s) from a table.

mysql> DELETE from [table name] where [field name] = ‘whatever’;

 

Update database permissions/privilages.

mysql> flush privileges;

 

Delete a column.

mysql> alter table [table name] drop column [column name];

 

Add a new column to db.

mysql> alter table [table name] add column [new column name] varchar (20);

 

Change column name.

mysql> alter table [table name] change [old column name] [new column name] varchar (50);

 

Make a unique column so you get no dupelicates.

mysql> alter table [table name] add unique ([column name]);

 

Make a column bigger.

mysql> alter table [table name] modify [column name] VARCHAR(3);

 

Delete unique from table.

mysql> alter table [table name] drop index [colmn name];

 

Load a CSV file into a table.

mysql> LOAD DATA INFILE ‘/tmp/filename.csv’ replace INTO TABLE [table name] FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’ (field1,field2,field3);

 

Dump all databases for backup. Backup file is sql commands to recreate all db’s.

# [mysql dir]/bin/mysqldump -u root -ppassword –opt >/tmp/alldatabases.sql

 

Dump one database for backup.

# [mysql dir]/bin/mysqldump -u username -ppassword –databases databasename >/tmp/databasename.sql

 

Dump a table from a database.

# [mysql dir]/bin/mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql

 

Restore database (or database table) from backup.

# [mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql

 

Create Table Example 1.

mysql> CREATE TABLE [table name] (firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35),suffix VARCHAR(3),officeid VARCHAR(10),userid VARCHAR(15),username VARCHAR(8),email VARCHAR(35),phone VARCHAR(25), groups VARCHAR(15),datestamp DATE,timestamp time,pgpemail VARCHAR(255));

 

Create Table Example 2.

mysql> create table [table name] (personid int(50) not null auto_increment primary key,firstname varchar(35),middlename varchar(50),lastnamevarchar(50) default ‘bato’);

 

Help and Show Commands

$ mysql –help | less
$ mysqld –help
$ mysqlshow –help | less
$ mysqldump –help | less
$ mysqlshow – show all databases.
$ mysqlshow db_name – all tables in particular database.
$ mysqlshow db_name BA* – all tables which start from BA letters.mysql> \?
mysql> use db_name;
mysql> show databases;
mysql> show databases like ‘ba%’
mysql> show tables;
mysql> describe table_name;
mysql> select user(), now(), version(), database();
+—————+———————+—————-+————+
| user()        | now()               | version()      | database() |
+—————+———————+—————-+————+
| ana@localhost | 2003-01-05 21:24:27 | 4.0.1-alpha-nt | test       |
+—————+———————+—————-+————+mysql> show tables from db_name
mysql> show tables from db_name like ‘__ab%’
mysql> show columns from table_name
mysql> show columns from table_name from db_name
mysql> show grants for user_name
mysql> show index from table_name
mysql> show index from table_name from db_name
mysql> show processlist
mysql> show status
mysql> show table status from db_name
mysql> show variables

MYSQL Issue:- ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

[root@servert1 ~]# /etc/init.d/mysqld stop
Stopping MySQL: [ OK ]
[root@servert1 ~]# mysqld_safe --skip-grant-tables &
[1] 13694
[root@servert1 ~]# Starting mysqld daemon with databases from /var/lib/mysql

root@servert1 ~]# mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.77 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> update user set password=PASSWORD("testpass") where User='root';
ERROR 1046 (3D000): No database selected
mysql> show databases;

+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.13 sec)
mysql> use mysql; 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+---------------------------+

| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| func |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| proc |
| procs_priv |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
17 rows in set (0.00 sec)


mysql> update user set password=PASSWORD("testpass") where User='root';
Query OK, 3 rows affected (0.05 sec)
Rows matched: 3 Changed: 3 Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.04 sec)

mysql> quit
Bye
[root@servert1 ~]# /etc/init.d/mysql restart
bash: /etc/init.d/mysql: No such file or directory
[root@servert1 ~]# /etc/init.d/mysqld restart
STOPPING server from pid file /var/run/mysqld/mysqld.pid
101120 04:17:15 mysqld ended
Stopping MySQL: [ OK ]
Starting MySQL: [ OK ]

[1]+ Done mysqld_safe --skip-grant-tables
root@servert1 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.77 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.


mysql> quit
Bye

Check Repair & Optimize mysql Databases:

You can use either Mysqlcheck or Myisamchk to Check and/or Repair database tables. Mysqlcheck and Myisamchk are similar in purpose, there are some essential differences. Mysqlcheck as well as Myisamchk can Check, Repair and Analyze MyISAM tables. Mysqlcheck can also check InnoDB tables, so if database engine used for the databases is other than MyISAM, i.e InnoDB then try to use Mysqlcheck cmd.
———————————————————————————————————
++  Check, Repair and Optimize Using mysqlcheck cmd:
———————————————————————————————————

+  Check, Repair and Optimize All tables in All Databases when you’re running a MySQL server on Linux.
# mysqlcheck –auto-repair –check –optimize –all-databases

OR
# mysqlcheck –all-databases -r   #repair databases
# mysqlcheck –all-databases -a   #analyze databases
# mysqlcheck –all-databases -o   #optimize databases

=> Check, Repair and Optimize Single Database Tables.
# mysqlcheck –auto-repair –check –optimize CpanelUsername_Databasename
# mysqlcheck -ro CpanelUsername_Databasename

=> To repair One Table in database:
# mysqlcheck -ro CpanelUsername_Databasename table_name

———————————————————————————————————
++ Check, Repair and Optimize Using mysqlcheck myisamchk cmd:
———————————————————————————————————
=> For All tables in All Databases:

Shows you if any need repair:
# myisamchk –check /var/lib/mysql/*/*.MYI

Then try ‘safe-recover’ first:
# myisamchk –safe-recover /var/lib/mysql/*/*.MYI

and, if neither “safe-recover” or “recover” option works:
# myisamchk –recover /var/lib/mysql/*/*.MYI

Then use the ‘force’ flag:
# myisamchk –recover –extend-check –force /var/lib/mysql/*/*.MYI

=> For Single Database:
myisamchk -r /var/lib/mysql/[CpanelUsername_Databasename]/*
OR

cd /var/lib/mysql/[CpanelUsername_Databasename]/
To check the tables:
# myisamchk *.MYI
To repair tables:
# myisamchk -r *.MYI

Note: You can use Mysqlcheck or Myisamchk cmd line options as per your requirenemt.

Mysql -> add/drop/grant/revoke/backup/restore.

mysql -u <username> -p
Enter password:

Create database command:
--------------------------------

mysql> CREATE DATABASE <database>;

eg:

mysql> CREATE DATABASE ACCOUNTS;


We can now check for the presence of this database by typing:

mysql> SHOW DATABASES;

+-------------+
| Database |
+-------------+
| mysql |
| accounts |
+-------------+

USE Database:
-----------------

The USE db_name statement tells MySQL to use the db_name database as the default (current) database for subsequent statements. The database remains the default until the end of the session or until another USE statement is issued:

mysql> USE accounts;
mysql> SELECT COUNT(*) FROM mytable; # selects from db1.mytable
mysql> USE sales;
mysql> SELECT COUNT(*) FROM mytable; # selects from db2.mytable

Making a particular database current by means of the USE statement does not preclude you from accessing tables in other databases. The following example accesses the author table from the db1 database and the editor table from the db2 database:

mysql> USE accounts;
mysql> SELECT author_name,editor_name FROM author,sales.editor
-> WHERE author.editor_id = sales.editor.editor_id;



Delete / Remove database command:
--------------------------------------------

DROP DATABASE <database>

eg:

DROP DATABASE accounts;


Granting Privileges on the new database:
-----------------------------------------------

mysql> GRANT ALL PRIVILEGES ON DatabaseName.* TO Username@localhost

or

mysql> GRANT ALL PRIVILEGES ON DatabaseName.* TO Username@localhost IDENTIFIED BY 'newpassword';

mysql> GRANT SELECT,INSERT,UPDATE,DELETE ON vworks.* TO newuser@localhost IDENTIFIED BY 'newpassword';


mysql> GRANT ALL PRIVILEGES ON DatabaseName.* TO Username@192.168.0.2 IDENTIFIED BY 'newpassword';

Now a user on the machine '192.168.0.2' can connect to the database. To allow a user to connect from anywhere you would use a wildcard '%'

mysql> GRANT ALL PRIVILEGES ON DatabaseName.* TO Username@localhost IDENTIFIED BY 'newpassword' WITH GRANT OPTION;

This would allow the user 'newuser' to log into the database and give their friend privileges to SELECT,INSERT,UPDATE or DELETE from the database.


REVOKING Privileges:
-------------------------

For example to REVOKE the privileges assigned to a user called 'user1':

mysql> REVOKE ALL PRIVILEGES ON DATABASENAME.* FROM user1@localhost;

Or just to remove UPDATE, INSERT and DELETE privileges to that data cannot be changed.

mysql> REVOKE INSERT,UPDATE,DELETE ON DATABASENAME.* FROM user1@localhost;


Backing Up DataBase:
-------------------------

mysqlhotcopy -u <username> -p <database> /backup/location/


Which SHOULD copy all the tables (*.frm, *.MYI, *.MYD) into the new directory - the script does require the DBI perl module though. To restore these backup files simply copy them back into your MySQL data directory.


This is my preferred method of backing up. This outputs the table structure and data in series of SQL commands stored in a text file. The simplified syntax is

mysqldump -u <username> -p <database> > file.sql

eg:

mysqldump -u user1 -p accounts > dump.sql


Restoring a DataBase from Dump:
---------------------------------------

mysqldump -u <username> -p <database> < file.sql

eg:

mysqldump -u user1 -p accounts < dump.sql

shell script to backup mysql databases

#!/bin/bash
#Script for mysql database backup
cd /var/lib/mysql
for DBs in $(ls -d */ |tr -d /)
do

cd /mysqlbackup

`mysqldump -u root -p'password' $DBs > $DBs.sql`

done

Updating mysql user password

root#mysql
mysql> use mysql;
mysql>

SET PASSWORD FOR 'user-name-here'@'hostname-name-here' = PASSWORD('new-password-here');  
or
UPDATE mysql.user SET Password=PASSWORD('new-password-here') WHERE User='user-name-here' AND Host='host-name-here';  

mysql>flush privileges
mysql>quit

Now restart mysqld

MySQL- basic commands in Mysql

To login from unix shell.
[mysql dir]/bin/mysql -h hostname -u root -p

*Note: use -h only if needed.
Create, List, Use and Delete Databases Commands

create database
This command is used to create a Database on the sql server..
Syntax: create [db name];

Eg:
create employees;

show databases
This command id used to list all databases on the sql server.
Syntax: show databases;

use database
This command is used to switch to a database.
Syntax: use [db name];

drop database
This command is used to delete a database.
Syntax: drop [db name];
Creating tables and working with MySQL

create tabe
This command is used to create tables in a database.

Eg:
CREATE TABLE [table name] (firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35),suffix VARCHAR(3),officeid VARCHAR(10),userid VARCHAR(15),username VARCHAR(8),email VARCHAR(35),phone VARCHAR(25), groups
VARCHAR(15),datestamp DATE,timestamp time,pgpemail VARCHAR(255));

show tables
This command is see all the tables in the database.

describe table
To see database's field formats.
describe [table name];

drop table
To delete a table.
drop table [table name];

Show all data in a table.
SELECT * FROM [table name];

Returns the columns and column information pertaining to the designated table.
show columns from [table name];

Show certain selected rows with the value "something".
SELECT * FROM [table name] WHERE [field name] = "something";

Show all records containing the name "Telson" AND the phone number '2255'.
SELECT * FROM [table name] WHERE name = "Telson" AND phone_number = '2255';

Show all records not containing the name "Telson" AND the phone number '2255' order by the phone_number field.
SELECT * FROM [table name] WHERE name != "Telson" AND phone_number = '2255' order by phone_number;

Show all records starting with the letters 'Tel' AND the phone number '2255'.
SELECT * FROM [table name] WHERE name like "Tel%" AND phone_number = '2255';

Use a regular expression to find records. Use "REGEXP BINARY" to force case-sensitivity. This finds any record beginning with a.
SELECT * FROM [table name] WHERE rec RLIKE "^a$";

Show unique records.
SELECT DISTINCT [column name] FROM [table name];

Show selected records sorted in an ascending (asc) or descending (desc).
SELECT [column1],[column2] FROM [table name] ORDER BY [column2] DESC;

Return number of rows.
SELECT COUNT(*) FROM [table name];

Sum column.
SELECT SUM(*) FROM [table name];

Join tables on common columns.
select lookup.illustrationid, lookup.personid,person.birthday from lookup left join person on lookup.personid=person.personid=statement to join birthday in person table with primary illustration id;
Creating Mysql databse Users and changing Password

Switch to the mysql db.

Create a new user.
INSERT INTO [table name] (Host,User,Password) VALUES('%','user',PASSWORD('password'));

Change a users password.(from unix shell).
[mysql dir] mysqladmin -u root -h hostname.blah.org -p password 'new-password'

Change a users password.(from MySQL prompt).
SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere');

Allow the user "telson" to connect to the server from localhost using the password "passwd"
grant usage on *.* to telson@localhost identified by 'passwd';

Switch to mysql db.
Give user privilages for a db.
INSERT INTO [table name] (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES ('%','databasename','username','Y','Y','Y','Y','Y','N');

or

grant all privileges on databasename.* to username@localhost;
Modifying and Updating tables in MySQL

To update info already in a table.
UPDATE [table name] SET Select_priv = 'Y',Insert_priv = 'Y',Update_priv = 'Y' where [field name] = 'user'; Delete a row(s) from a table.
DELETE from [table name] where [field name] = 'something';

Update database permissions/privilages.
FLUSH PRIVILEGES;

Delete a column.
alter table [table name] drop column [column name];

Add a new column to db.
alter table [table name] add column [new column name] varchar (20);

Change column name.
alter table [table name] change [old column name] [new column name] varchar (50);

Make a unique column so you get no dupes.
alter table [table name] add unique ([column name]);

Make a column bigger.
alter table [table name] modify [column name] VARCHAR(4);

Delete unique from table.
alter table [table name] drop index [colmn name];

Load a CSV file into a table.
LOAD DATA INFILE '/tmp/filename.csv' replace INTO TABLE [table name] FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1,field2,field3);
Creating MySQL Database backup's and Restoring Databases

Dump all databases for backup. Backup file is sql commands to recreate all db's.
[mysql dir] mysqldump -u root -ppassword --opt >/tmp/alldatabases.sql

Dump one database for backup.
[mysql dir] mysqldump -u username -ppassword --databases databasename >/tmp/databasename.sql

Dump a table from a database.
[mysql dir] mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql

Restore database (or database table) from backup.
[mysql dir] mysql -u username -ppassword databasename < /tmp/databasename.sql