How to reset Password in mysql

Change you own password
login into mysql

at the "mysql>" prompt, type:
set password = password("yournewpassword");

Change other user password
SET PASSWORD FOR 'bob'@'%' = PASSWORD('yournewpassword')

Reference
12.4.1.6. SET PASSWORD Syntax

Addition information
Friend feedback and say he follow the instruction but change password fail! why ??
SET PASSWORD FOR 'user1'@'%localhost' = PASSWORD('yournewpassword')

We can check from here
login into mysql server
mysql> use mysql;
mysql> select user,host from user;

user | host |
+-------------+----------------------+
| root | mysqlserver
| user1 | localhost
| user2 | %

If we login and root and want change password for user1,
we should run this
SET PASSWORD FOR 'user1'@'localhost' = PASSWORD('yournewpassword')

if we want change password for user2,
we should run this
SET PASSWORD FOR 'user2'@'%' = PASSWORD('yournewpassword')

How to know which MYSQL Version I Using?

How to know which MYSQL Version I using

mysql -e status|grep 'Server version'
or
mysql -u root -p -e status|grep 'Server version'


Windows users:
mysql -e status

MySQL Magazine - Issue 6

New magazine from the site

Issue Six

SQL monitoring

We have MYSQL in production, and want have something like monitor or feedback on SQL status.

In MSSQL, we can do lot thing
Example : Monitor blocking, long query, job status

Google and find

1. mtop :
mytop is a console-based (non-gui) tool for monitoring the threads and overall performance of a MySQL 3.22.x, 3.23.x, and 4.x server.

more information

2. Find this script:


#!/bin/sh
# Variables
DBUSER=someone
DBPASS=something
SECONDS=15
# Main loop
while true
do
mysqladmin -u$DBUSER -p$DBPASS processlist |
egrep -vw 'Sleep|processlist|Binlog Dump' |
awk -F'|' '{print $6, $7, $8, $9}'
# First line of vmstat is historical, so take the second
vmstat 1 2 | tail -1
# Sleep for a while
sleep $SECONDS
done

3. MYSQLReport
mysqlreport makes a friendly report of important MySQL status values. mysqlreport transforms the values from SHOW STATUS into an easy-to-read report that provides an in-depth understanding of how well MySQL is running. mysqlreport is a better alternative (and practically the only alternative) to manually interpreting SHOW STATUS.

Anything else?

MySQL Magazine

I found this site which provide free MYSQL magazines
Currently they have 4 downloaded magazine issue

Issue One - Summer 2007
Issue Two - Fall 2007
Issue Three - Winter 2007/2008
Issue Four - Spring 2008

MYSQL Performance Slide

| View | Upload your own

Different way to install MYSQL

Different way to install MYSQL
1. Install through MYSQL RPM
- Download and run rpm rpmfile.
- The program file will install into file predefine by rpm file
- Example : Red Hat Enterprise Linux 4 RPM (x86)

2. Compile mysql from MYSQL source and install
- We can compile feature we need.
- We can predefine target install folder as well.
- Example : Source

3. Install MYSQL binary version
- Download and copy the code into target folder
- Advantage : We can download into folder we specific
- Example : Linux (non RPM packages) or
Linux (non RPM, Intel C/C++ compiled, glibc-2.3)

4. Use Operating System MYSQL compile version
- Most Linux come with mysql compile version.
- MYSQL version and start and using

MYSQL download page http://dev.mysql.com/downloads/mysql/5.0.html

reset MYSQL "root" password

How to reset MYSQL "root" password :

Stop mysqld and restart it with the --skip-grant-tables --user=root options
(Windows users omit the --user=root portion).

Connect to the mysqld server with this command:

shell> mysql -u root
Issue the following statements in the mysql client:

mysql> UPDATE mysql.user SET Password=PASSWORD('newpwd')
-> WHERE User='root';
mysql> FLUSH PRIVILEGES;

How to create two instance mysql in server

My practices
use binary version - Linux (non RPM packages)
tar to particular folder
change the port (3306.2207) and point socket to individual folder

Install MYSQL gui Tool - Failed dependencies

Problem when install MYSQL administrator

[root@localhost src]#rpm -iv mysql-administrator-5.0r5-1fc5.i386.rpm
error: Failed dependencies:
libatkmm-1.6.so.1 is needed by mysql-administrator-5.0r5-1fc5.i386
libgdkmm-2.4.so.1 is needed by mysql-administrator-5.0r5-1fc5.i386
libglibmm-2.4.so.1 is needed by mysql-administrator-5.0r5-1fc5.i386
libgtkmm-2.4.so.1 is needed by mysql-administrator-5.0r5-1fc5.i386
libpangomm-1.4.so.1 is needed by mysql-administrator-5.0r5-1fc5.i386
libsigc-2.0.so.0 is needed by mysql-administrator-5.0r5-1fc5.i386

Problem when Install MYSQL Query Browser
[root@localhost src]# rpm -iv mysql-query-browser-5.0r5-1fc5.i386.rpm
error: Failed dependencies:
libatkmm-1.6.so.1 is needed by mysql-query-browser-5.0r5-1fc5.i386
libgdkmm-2.4.so.1 is needed by mysql-query-browser-5.0r5-1fc5.i386
libglibmm-2.4.so.1 is needed by mysql-query-browser-5.0r5-1fc5.i386
libgtkmm-2.4.so.1 is needed by mysql-query-browser-5.0r5-1fc5.i386
libpangomm-1.4.so.1 is needed by mysql-query-browser-5.0r5-1fc5.i386
libsigc-2.0.so.0 is needed by mysql-query-browser-5.0r5-1fc5.i386

how to solve?
yum install libatkmm-1.6.so.1

Result :
Successful Intall MYSQL Administrator and Browser

Post Installation MYSQL Testing

Test connection to mysql
1) connect to mysql
[root@localhost src]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.27-standard

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>show databases
+-----------------------+
Database
+---------------------- +
information_schema
mysql
test
+-----------------------+
3 rows in set (0.02 sec)

2. ps aux grep mysql

3. mysqladmin -p ping
[root@localhost ~]# mysqladmin -p ping
Enter password :
mysql is alive

Install MYSQL Client

Install MySQL client

rpm -i MySQL-client-standard-5.0.27-0.rhel4.i386.rpm
Preparing... ###########################################
[100%]
1:MySQL-client-standard ###########################################
[100%]

Install MySQL rpm version

[root@localhost src]# rpm -i MySQL-server-standard-5.0.27.rhel4.i386.rpm
PLEASE REMEBER TO SET A PASSSWORD FOR THE MySQL root USER!
To do so, sert the server, then issue the follwing commands:
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h locahost.localdomain password 'new-password'
See the manual for more instruction
Note: If you are upgrading from a MySQL <=3.22.10 you should runthe /usr/bin/mysql_fix_rilivege_tables. Otherwise you will not be able to use the new GRANT command! Please report any problems with the /usr/bin/mysqlbug script! The latest information about MySQL is available on the web at http://www.mysql.com
Support MySQL by buying support/licenses at http://shop.mysql.com
Starting MySQL [ok]

MySQL RPMs unusable when SELinux is enabled

MYSQL Setup FAILED WITH SELinux enabled

"Starting MySQL...................................[FAILED]"

Run MySQL Setup
-----------------
[root@localhost src]# rpm -ivh MySQL-server-standard-5.0.27-0.rhel4.i386.rpm
Preparing... ###########################################
[100%]
1:MySQL-server-standard ###########################################
[100%]
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h localhost.localdomain password 'new-password'
See the manual for more instructions.

NOTE: If you are upgrading from a MySQL <= 3.22.10 you should run the /usr/bin/mysql_fix_privilege_tables. Otherwise you will not be able to use the new GRANT command! Please report any problems with the /usr/bin/mysqlbug script! The latest information about MySQL is available on the web at http://www.mysql.com Support MySQL by buying support/licenses at http://shop.mysql.com Starting MySQL...................................[FAILED] What do to : From System->Administrator->Security and FireWall
Security Level Configuration
SElinux Setting : Enforcing
Modify Selinux Policy
SELinuxService Protection
Change this "Disable SELinux protection for mysqld daemon"

The Problem should solve

more information
http://bugs.mysql.com/bug.php?id=12676

Refer to this portion
[12 Oct 2005 2:18] michael chester
If you are using rhel 4 you can work around this issue by running the security
GUI system-config-securitylevel and exempting the MySQL service from the SELinux
configuration. You should then be able to run the RPM install as per the MySQL
instructions.

Uninstall MYSQL in Linux

Type below command :
rpm -qa | grep -i mysql
It display
MySQL-server-standard-5.0.27-0.rhe14

Type below to uninstall
rpm -e MySQL-server-standard-5.0.27-0.rhe14

verify with
rpm -qa | grep -i mysql

Result :
Mysql uninstall

PHP : Install Smarty template engine

What is Smarty
Smarty is Template Engine which separate presentation framework and program logic.

"Is Smarty right for me" from smarty.com

Setup Smarty and testing
Below step done in Windows XP and using Apache Web Server.
1. Download Smarty
2. Unzip Smarty-2.6.14.tar with using commercial software winzip or freeware 7-ZIP
3. Extract and copy Smarty directory outside of root directory. Example c:\smarty
4. Locate php.ini path location with phpinfo.php
5. Include this in php.ini
include_path = ".;c:\smarty\libs"
6. Restart IIS/Apache
7. Setup 2 folder in root directory
smarty/templates
smarty/configs
8. Testing
A. Create smartytest.php

template_dir = 'c:/program files/Apache Group/Apache2/htdocs/smarty/templates';
$smarty->config_dir = 'c:/program files/Apache Group/Apache2/htdocs/smarty/config';
$smarty->cache_dir = 'c:/smarty/smarty_cache';
$smarty->compile_dir = 'c:/smarty/smarty_templates_c';

$smarty->assign('name','world!');
$smarty->display('index.tpl');
?>

B. create template smartytest.tpl
put into \smarty\template

put the below code into page which contain html and body and save as smartytest.tpl
Hello, {$name}!

C. Create folder
c:\smarty\smarty_cache
c:\smarty\smarty_templates_c

D. testing
http://localhost/smartytest.php

E. Result
Hello, World!!

Where php.ini locate?

Create a phpinfo.php inside root directory
with the below content
// Show all information, defaults to INFO_ALL
phpinfo();
// Show just the module information.//
phpinfo(8) yields identical results.
phpinfo(INFO_MODULES);
?>

run from IE
http://localhost/phpinfo.php

You will get php information from the page display
one of the information is php.ini path
"Configuration File (php.ini) Path"

Install Apache for Windows XP as a Service

After install Apache, I found Apache program didn't start. I check apache status from Apache service monitor and can't find Apache services from Apache service monitor. I open the window services from control panel -> Administrator -> Services and also can't found "Apache service" from the services windows.

I run apache from console and it successful
What I do
from dos prompt type : "apache path"\bin\apache and enter.
Test web: http://localhost/ and apache website display
It show I already successful install apache web services in my computer

Do some search in Google and successful install apache as service
What I do
from "apache path"\bin\apache -k install "Apache"
Apache service install successful in windows services
I can found Apache service appear at "Apache services monitor" and ”Windows Services"

"Could not connect to mySQL server: Client does not support authentication protocol requested by server; consider upgrading MySQL client"

"Could not connect to mySQL server: Client does not support authentication protocol request consider upgrading MySQL client"

mysql
update mysql.user set password=OLD_PASSWORD('pass')
where User='user1';
Query OK, 0 rows affected (0.06 sec)Rows matched: 1 Changed: 0 Warnings: 0

This will solve this problem..
Detail about cause of this problem :
http://dev.mysql.com/doc/refman/5.0/en/password-hashing.html

php web program

After one week hardworking.. I finish one php program which host in apache webserver and use mysql as database. lot of problem face expecially with this php and mysql
Problem in mysql.. can't connect to mysql server using php.
Problem in php. how to design a page to let use keying data, edit data, and how to let user search existing form.

I read book (The definative guide to mysqsl second edition) , search internet(http://php.resourceindex.com/) and ask friends (thanks boon kit for help in mysql and kim fee in php) , and finally finish the draft

Show my brother and his friend and they request some change.

Next step
1. learn about mysql security
2. Put php code in object instead all in a php form.