Mysql Password Management

Mysql Password Management

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

A. Change password using command: In Linux: 

Login to the MySQL server from the command line with the following command:

mysql -u root -p

Now switch to the appropriate MySQL database with the following command:

use mysql;

Next we’ll update the password for all MySQL users with the name root . Be sure to replace your_new_password with the actual new password:

update user set password=PASSWORD('your_new_password') where User='root';

Note: You can change the password for any user with the above command. Simply specify that user’s username in place of root .

Finally, reload the privileges:

flush privileges;

Now you’re all set to exit MySQL!

quit

Src: http://www.liquidweb.com/kb/change-a-password-for-mysql-on-linux-via-command-line/

B. Recover MySQL root Password

1. Stop mysql service

       /etc/init.d/mysql stop

2. Start to MySQL server w/o password:

Start the MySQL (mysqld) server/daemon process with the --skip-grant-tables option so that it will not prompt for password

       mysqld_safe --skip-grant-tables &

3. Connect to mysql server using mysql client as root:

         mysql -u root

4. Setup new MySQL root user password:

Setup new mysql root account password

mysql> use mysql;
mysql> update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
mysql> flush privileges;
mysql> quit

5. Stop MySQL Server:

     /etc/init.d/mysql stop

6. Start MySQL server and test it

       /etc/init.d/mysql start
       mysql -u root -p


References

How to Reset the Root Password

C. Reset Passwd using Mysql Manual

1. Log on to your system as Administrator. 

2. Stop the MySQL server if it is running. For a server that is running as a Windows service, go to the Services manager: From the Start menu, select Control Panel, then Administrative Tools, then Services. Find the MySQL service in the list and stop it. 
If your server is not running as a service, you may need to use the Task Manager to force it to stop. 

3. Create a text file containing the following statements. Replace the password with the password that you want to use.


UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';

FLUSH PRIVILEGES;
Write the UPDATE and FLUSH statements each on a single line. The UPDATE statement resets the password for all root accounts, and the FLUSH statement tells the server to reload the grant tables into memory so that it notices the password change.

4. Save the file. For this example, the file will be named C:\mysql-init.txt. 

5. Open a console window to get to the command prompt: From the Start menu, select Run, then enter cmd as the command to be run. 

6. Start the MySQL server with the special --init-file option (notice that the backslash in the option value is doubled):

C:\> C:\mysql\bin\mysqld --init-file=C:\\mysql-init.txt

If you installed MySQL to a location other than C:\mysql, adjust the command accordingly.



The server executes the contents of the file named by the --init-file option at startup, changing each root account password.
You can also add the --console option to the command if you want server output to appear in the console window rather than in a log file.

If you installed MySQL using the MySQL Installation Wizard, you may need to specify a --defaults-file option:


C:\> "C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld.exe"

         --defaults-file="C:\\Program Files\\MySQL\\MySQL Server 5.5\\my.ini"

         --init-file=C:\\mysql-init.txt

The appropriate --defaults-file setting can be found using the Services Manager: From the Start menu, select Control Panel, then Administrative Tools, then Services. Find the MySQL service in the list, right-click it, and choose the Properties option. The Path to executable field contains the --defaults-file setting.


7. After the server has started successfully, delete C:\mysql-init.txt.

Add  mysql to startup of linux

To start mysqld at boot time you have to copy

support-files/mysql.server to the right place for your system

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'

Alternatively you can run:

/usr/bin/mysql_secure_installation

which will also give you the option of removing the test

databases and anonymous user created by default.  This is

strongly recommended for production servers.

Password Setting for mysql in xampp

Method 1: reset XAMPP MySQL root password through web interface:

After you started your XAMPP server, go to the browser and Open http://localhost/security/ (incase you've modified XAMPP server port, you need to include that port number also). The security page will be shown where you can change the root password for MySQL. This will update the phpMyAdmin config also.

Method 2: reset XAMPP MySQL root password through SQL update:

1. Start the Apache Server and MySQL instances from the XAMPP control panel.
2. After the server started, open any web browser and open http://localhost/phpmyadmin/. This will open the phpMyAdmin interface. Using this interface we can manager the MySQL server from the web browser.
3. In the phpMyAdmin window, select SQL tab from the upper/right panel. This will open the SQL tab where we can run the SQL queries.
4. Now type the following query in the textarea and click Go
   UPDATE mysql.user SET Password=PASSWORD('XYZ') WHERE User='root'; FLUSH PRIVILEGES;
5. Now you will see a message saying that the query has been executed successfully.
6. If you refresh the page, you will be getting a error message. This is because the phpMyAdmin configuration file is not aware of our newly set root password. To do this we have to modify the phpMyAdmin config file.
7. Open the file [XAMPP Installation Path] / phpmyadmin / config.inc.php in your favorite text editor.
8. Search for the string$cfg\['Servers'\]\[$i\]['password'] = ''; and change it to like this,$cfg\['Servers'\]\[$i\]['password'] = 'XYZ';Here the 'XYZ' is what we set to the root user using the SQL query.
9. Now all set to go. Save the config.inc.php file and restart the XAMPP server.

Method 3: Command line

With the “XAMPP Shell” (command prompt) you can also reset the password. Open the shell and execute this command
mysqladmin.exe -u root password newpassword

Change an Element's CSS Properties with JavaScript

To modify single property: 

Change the CSS setting directly using the element's style property:

var elem = document.getElementById("elem");elem.style.width = "500px";

To modify one or more values:

 Use the element’s setAttribute method:

elem.setAttribute("style","width: 500px; background-color: yellow;");

An element’s CSS properties can be modified in Javascript using one of two approaches. 
The simplest approach is to set the property’s value directly using the element’s style property:

elem.style.width = "500px";

If the CSS property contains a hyphen, such as font-family or background-color, use a CamelCase notation for the property:

elem.style.fontFamily = "Courier";
elem.style.backgroundColor = "rgb(255,0,0)";

You can also use the element’s setAttribute method to set the style property:

elem.setAttribute("style","font-family: Courier; background-color: yellow");

However, when you set the style property using setAttribute, it erases any previously set values in the Javascript.

Example: setting and retrieving CSS style settings 
It demonstrates how the style-setting techniques work, including the impact of using setAttribute. Various techniques are used to set and get style attributes, including a cross-browser approach to access the computed style for the attribute.
<!DOCTYPE html>

<head>
<title>Changing style</title>
<meta charset="utf-8" />
<style>
#elem
{
  width: 200px; background-color: lime;
}
</style>
<script type="text/javascript">

function getStyle(elem, cssprop, cssprop2){

 // IE
 if (elem.currentStyle) {
   return elem.currentStyle[cssprop];

 // other browsers
 } else if (document.defaultView &&
                   document.defaultView.getComputedStyle) {
   return document.defaultView.getComputedStyle(elem,
null).getPropertyValue(cssprop2);

 // fallback
 } else {
   return null;
 }
}
window.onload=function() {

   // setting and accessing style properties
   var elem = document.getElementById("elem");

   var color = getStyle(elem,"backgroundColor", "background-color");
   alert(color); // rgb(0,255,0)

   elem.style.width = "500px";
   elem.style.backgroundColor="yellow";

   alert(elem.style.width); // 500px
   alert(elem.style.backgroundColor); // yellow

   // array notation
   elem.style["fontFamily"] = "Courier";

   // demonstrating overwriting properties
   var style = elem.getAttribute("style");
   alert(style); // should display color: purple; width: 500px;
                 // background-color: yellow;

   elem.setAttribute("style","height: 100px");
   var style = elem.getAttribute("style");
   alert(style); // now only displays height, resets styles

   var font = getStyle(elem,"fontFamily", "font-family");
   alert(font); // default font family
}
</script>
</head>
<body>
<div id="elem" style="color: purple">
testing</div>
</body>

As soon as the page loads, the div element is accessed using getElementById, and its background-color is retrieved using a cross-browser function that gets the computed style for the attribute. The message output is “rgb(0,255,0)”, representing the lime color set in the page’s stylesheet.

Next, two CSS properties are set using the element’s style property: the width and background-color. Now the div element has a yellow background and is 500, rather than 200, pixels wide. Both of the modified values are accessed and printed out, so we can confirm that yes, the values have changed.

Next, the font-family for the element is set to Courier, using the array notation, which is another approach you can use to set and get style property values. Now the div element is 500 pixels wide, with a yellow background, and its font family is Courier.

The style property is accessed using getAttribute. A string of the values set using the style property is returned for all browsers:

color: purple; width: 500px; background-color: yellow;
font-family: Courier;

The purple font color is set inline within a style attribute in the div element.

Next, I’m using the setAttribute method to change the element’s height. A couple of things happen when I used the setAttribute method in the example. The height of the element is changed to 100 pixels, but the previously set style properties (color, width, background-color, and font-family) have been “erased,” and revert back to the original settings in the stylesheet, or the defaults by the user agent. The element is now 200 pixels wide, 100 pixels tall, with a green background, and the font reverts back to the default font for the browser (typically a serif value), and the default font color, black.

As you can see, using setAttribute to change the style element property can significantly impact on previous settings, including any inline CSS settings. You should only use setAttribute if you’re changing many values at once, and you don’t use any inline style attributes or haven’t modified the element’s style settings previously in your application.

The effects demonstrated in this recipe work the same with all of the book’s target browsers, except for IE7. The style property is an actual object in IE7, so when you access style with getAttribute, you’ll get an object, not a string. Since it is an object, it’s read only, which means you can’t use setAttribute with IE7.

Mysql Important commands

Short List of MySQL Commands

Conventions used here:

• MySQL key words are shown in CAPS, but can be used small also, as it case-insensitive
• User-specified names are in small letters
• Optional items are enclosed in square brackets [ ]
• Items in parentheses must appear in the command, along with the parentheses
• Items that can be repeated as often as desired are indicated by an ellipsis ...

Quoting in MySQL statments

• Don't quote database, table, or column names
• Don't quote column types or modifiers
• Don't quote numerical values
• Quote (single or double) non-numeric values
• Quote file names and passwords
• User names are NOT quoted in GRANT or REVOKE statements, but they are quoted in other statements.

General Commands
USE database_name
	Change to this database. You need to change to some database when you first connect to MySQL.
SHOW DATABASES
	Lists all MySQL databases on the system.
SHOW TABLES [FROM database_name]
	Lists all tables from the current database or from the database given in the command.
DESCRIBE table_name
SHOW FIELDS FROM table_name
SHOW COLUMNS FROM table_name
	These commands all give a list of all columns (fields) from the given table, along with column type and other info.
SHOW INDEX FROM table_name
	Lists all indexes from this tables.
SET PASSWORD=PASSWORD('new_password')
	Allows the user to set his/her own password.
Table Commands
CREATE TABLE table_name (create_clause1, create_clause2, ...)
	Creates a table with columns as indicated in the create clauses.
	create_clause
	column name followed by column type, followed optionally by modifiers. For example, "gene_id INT AUTO_INCREMENT PRIMARY KEY" (without the quotes) creates a column of type integer with the modifiers described below.
	create_clause modifiers
	AUTO_INCREMENT : each data record is assigned the next sequential number when it is given a NULL value. PRIMARY KEY : Items in this column have unique names, and the table is indexed automatically based on this column. One column must be the PRIMARY KEY, and only one column may be the PRIMARY KEY. This column should also be NOT NULL. NOT NULL : No NULL values are allowed in this column: a NULL generates an error message as the data is inserted into the table. DEFAULT value : If a NULL value is used in the data for this column, the default value is entered instead.
DROP TABLE table_name
	Removes the table from the database. Permanently! So be careful with this command!
ALTER TABLE table_name ADD (create_clause1, create_clause2, ...)
	Adds the listed columns to the table.
ALTER TABLE table_name DROP column_name
	Drops the listed columns from the table.
ALTER TABLE table_name MODIFY create_clause
	Changes the type or modifiers to a column. Using MODIFY means that the column keeps the same name even though its type is altered. MySQL attempts to convert the data to match the new type: this can cause problems.
ALTER TABLE table_name CHANGE column_name create_clause
	Changes the name and type or modifiers of a column. Using CHANGE (instead of MODIFY) implies that the column is getting a new name.
ALTER TABLE table_name ADD INDEX [index_name] (column_name1, column_name2, ...)
CREATE INDEX index_name ON table_name (column_name1, column_name2, ...)
	Adds an index to this table, based on the listed columns. Note that the order of the columns is important, because additional indexes are created from all subsets of the listed columns reading from left to write. The index name is optional if you use ALTER TABLE, but it is necesary if you use CREATE INDEX. Rarely is the name of an index useful (in my experience).
Data Commands
INSERT [INTO] table_name VALUES (value1, value2, ...)
	Insert a complete row of data, giving a value (or NULL) for every column in the proper order.
INSERT [INTO] table_name (column_name1, column_name2, ...) VALUES (value1, value2, ...)
INSERT [INTO] table_name SET column_name1=value1, column_name2=value2, ...
	Insert data into the listed columns only. Alternate forms, with the SET form showing column assignments more explicitly.
INSERT [INTO] table_name (column_name1, column_name2, ...) SELECT list_of_fields_from_another_table FROM other_table_name WHERE where_clause
	Inserts the data resulting from a SELECT statement into the listed columns. Be sure the number of items taken from the old table match the number of columns they are put into!
DELETE FROM table_name WHERE where_clause
	Delete rows that meet the conditions of the where_clause. If the WHERE statement is omitted, the table is emptied, although its structure remains intact.
UPDATE table_name SET column_name1=value1, column_name2=value2, ... [WHERE where_clause]
	Alters the data within a column based on the conditions in the where_clause.
LOAD DATA LOCAL INFILE 'path to external file' INTO TABLE table_name
	Loads data from the listed file into the table. The default assumption is that fields in the file are separated by tabs, and each data record is separated from the others by a newline. It also assumes that nothing is quoted: quote marks are considered to be part of the data. Also, it assumes that the number of data fields matches the number of table columns. Columns that are AUTO_INCREMENT should have NULL as their value in the file.
LOAD DATA LOCAL INFILE 'path to external file' [FIELDS TERMINATED BY 'termination_character'] [FIELDS ENCLOSED BY 'quoting character'] [LINES TERMINATED BY 'line termination character'] FROM table_name
	Loads data from the listed file into the table, using the field termination character listed (default is tab \t), and/or the listed quoting character (default is nothing), and/or the listed line termination chacracter (default is a newline \n).
SELECT column_name1, column_name2, ... INTO OUTFILE 'path to external file' [FIELDS TERMINATED BY 'termination_character'] [FIELDS ENCLOSED BY 'quoting character'] [LINES TERMINATED BY 'line termination character'] FROM table_name [WHERE where_clause]
	Allows you to move data from a table into an external file. The field and line termination clauses are the same as for LOAD above. Several tricky features: Note the positions of the table_name and where_clause, after the external file is given. You must use a complete path, not just a file name. Otherwise MySQL attempts to write to the directory where the database is stored, where you don't have permission to write. The user who is writing the file is 'mysql', not you! This means that user 'mysql' needs permission to write to the directory you specify. The best way to do that is to creat a new directory under your home directory, then change the directory's permission to 777, then write to it. For example: mkdir mysql_output, chmod 777 mysql_output.
Privilege Commands
Most of the commands below require MySQL root access
GRANT USAGE ON *.* TO user_name@localhost [IDENTIFIED BY 'password']
	Creates a new user on MySQL, with no rights to do anything. The IDENTIFED BY clause creates or changes the MySQL password, which is not necessarily the same as the user's system password. The @localhost after the user name allows usage on the local system, which is usually what we do; leaving this off allows the user to access the database from another system. User name NOT in quotes.
GRANT SELECT ON *.* TO user_name@localhost
	In general, unless data is supposed to be kept private, all users should be able to view it. A debatable point, and most databases will only grant SELECT privileges on particular databases. There is no way to grant privileges on all databses EXCEPT specifically enumerated ones.
GRANT ALL ON database_name.* TO user_name@localhost
	Grants permissions on all tables for a specific database (database_name.*) to a user. Permissions are for: ALTER, CREATE, DELETE, DROP, INDEX, INSERT, SELECT, UPDATE.
FLUSH PRIVILEGES
	Needed to get updated privileges to work immediately. You need RELOAD privileges to get this to work.
SET PASSWORD=PASSWORD('new_password')
	Allows the user to set his/her own password.
REVOKE ALL ON [database_name.]* FROM user_name@localhost
	Revokes all permissions for the user, but leaves the user in the MySQL database. This can be done for all databases using "ON *", or for all tables within a specific databse, using "ON database_name.*".
DELETE FROM mysql.user WHERE user='user_name@localhost'
	Removes the user from the database, which revokes all privileges. Note that the user name is in quotes here.
UPDATE mysql.user SET password=PASSWORD('my_password') WHERE user='user_name'
	Sets the user's password. The PASSWORD function encrypts it; otherwise it will be in plain text.
SELECT user, host, password, select_priv, insert_priv, shutdown_priv, grant_priv FROM mysql.user
	A good view of all users and their approximate privileges. If there is a password, it will by an encrytped string; if not, this field is blank. Select is a very general privlege; insert allows table manipulation within a database; shutdown allows major system changes, and should only be usable by root; the ability to grant permissions is separate from the others.
SELECT user, host, db, select_priv, insert_priv, grant_priv FROM mysql.db
	View permissions for individual databases.

Referrence:
http://www.bios.niu.edu/johns/bioinform/mysql_commands.htm

Examples:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+

3 rows in set (0.00 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                        |

| event                     |

| func                      |

| general_log               |

| help_category             |

| help_keyword              |

| help_relation             |

| help_topic                |

| host                      |

| ndb_binlog_index          |

| plugin                    |

| proc                      |

| procs_priv                |

| servers                   |

| slow_log                  |

| tables_priv               |

| time_zone                 |

| time_zone_leap_second     |

| time_zone_name            |

| time_zone_transition      |

| time_zone_transition_type |

| user                      |

+---------------------------+

23 rows in set (0.00 sec)

mysql> describe user;

+-----------------------+-----------------------------------+------+-----+---------+-------+

| Field                 | Type                              | Null | Key | Default | Extra |

+-----------------------+-----------------------------------+------+-----+---------+-------+

| Host                  | char(60)                          | NO   | PRI |         |       |

| User                  | char(16)                          | NO   | PRI |         |       |

| Password              | char(41)                          | NO   |     |         |       |

| Select_priv           | enum('N','Y')                     | NO   |     | N       |       |

| Insert_priv           | enum('N','Y')                     | NO   |     | N       |       |

| Update_priv           | enum('N','Y')                     | NO   |     | N       |       |

| Delete_priv           | enum('N','Y')                     | NO   |     | N       |       |

| Create_priv           | enum('N','Y')                     | NO   |     | N       |       |

| Drop_priv             | enum('N','Y')                     | NO   |     | N       |       |

| Reload_priv           | enum('N','Y')                     | NO   |     | N       |       |

| Shutdown_priv         | enum('N','Y')                     | NO   |     | N       |       |

| Process_priv          | enum('N','Y')                     | NO   |     | N       |       |

| File_priv             | enum('N','Y')                     | NO   |     | N       |       |

| Grant_priv            | enum('N','Y')                     | NO   |     | N       |       |

| References_priv       | enum('N','Y')                     | NO   |     | N       |       |

| Index_priv            | enum('N','Y')                     | NO   |     | N       |       |

| Alter_priv            | enum('N','Y')                     | NO   |     | N       |       |

| Show_db_priv          | enum('N','Y')                     | NO   |     | N       |       |

| Super_priv            | enum('N','Y')                     | NO   |     | N       |       |

| Create_tmp_table_priv | enum('N','Y')                     | NO   |     | N       |       |

| Lock_tables_priv      | enum('N','Y')                     | NO   |     | N       |       |

| Execute_priv          | enum('N','Y')                     | NO   |     | N       |       |

| Repl_slave_priv       | enum('N','Y')                     | NO   |     | N       |       |

| Repl_client_priv      | enum('N','Y')                     | NO   |     | N       |       |

| Create_view_priv      | enum('N','Y')                     | NO   |     | N       |       |

| Show_view_priv        | enum('N','Y')                     | NO   |     | N       |       |

| Create_routine_priv   | enum('N','Y')                     | NO   |     | N       |       |

| Alter_routine_priv    | enum('N','Y')                     | NO   |     | N       |       |

| Create_user_priv      | enum('N','Y')                     | NO   |     | N       |       |

| Event_priv            | enum('N','Y')                     | NO   |     | N       |       |

| Trigger_priv          | enum('N','Y')                     | NO   |     | N       |       |

| ssl_type              | enum('','ANY','X509','SPECIFIED') | NO   |     |         |       |

| ssl_cipher            | blob                              | NO   |     | NULL    |       |

| x509_issuer           | blob                              | NO   |     | NULL    |       |

| x509_subject          | blob                              | NO   |     | NULL    |       |

| max_questions         | int(11) unsigned                  | NO   |     | 0       |       |

| max_updates           | int(11) unsigned                  | NO   |     | 0       |       |

| max_connections       | int(11) unsigned                  | NO   |     | 0       |       |

| max_user_connections  | int(11) unsigned                  | NO   |     | 0       |       |

+-----------------------+-----------------------------------+------+-----+---------+-------+

39 rows in set (0.00 sec)

Error Rporting in php

Error Control Operators @

 PHP supports one error control operator: the at sign (@). When prepended to an expression in PHP, any error messages that might be generated by that expression will be ignored.



For example:

$x=@mysqli_query($con, $abc);




error_reporting (PHP 4, PHP 5)

error_reporting — Sets which PHP errors are reported





   int error_reporting ([ int $level  ] )







The error_reporting() function sets the    error_reporting    directive at runtime.  PHP has manylevels of errors, using this function sets that level for the duration (runtime) of    your script. If the optional level is    not set, error_reporting() will just return    the current error reporting level.

Don't show the warning as well as error user



        // Turn off all error reporting




        error_reporting(0);