How to Reset the MySQL Root Password

Follow the steps below if you have forgotten your mysql root password and wish to reset it.

1.  Create a text file with the following information in it.

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

Replace “NEW_PASSWORD” with your desired mysql password.  Save the file to /etc/mysql-pass-reset

2.  Stop MySQL

/etc/init.d/mysqld stop

3.  Start mysqld_safe with the –init-file option like so:

mysqld_safe –init-file=/etc/mysql-pass-reset &

This will start mysql and execute the query in the text file you created.

4.  Now stop and start mysql back up normally without the –init-file option.

/etc/init.d/mysqld stop
/etc/init.d/mysqld start

Don’t forget to delete the “mysql-pass-reset” file when you are done!

rm /etc/mysql-pass-reset

How to Change Autoincriment Value in MySQL

If you have a attribute in your table that automatically increments itself for every new row, you can change what the next value is very easily.

You can do so by either of the two following ways.

1. Change the autoincrement value with a simple sql query.

ALTER TABLE {table name} AUTO_INCREMENT = {some number};

Where {table name} is the name of the table of which you are modifying the auto_increment value and {some number} is the actual auto_increment attribute value that you wish the next row to have.

2. When inserting the next row in that table, manually set the value for the auto_increment attribute and it will automatically continue from that number for future entries.