PROTOTYPE. Заметки о дизайне, рекламе и жизни.

Останавливаем демон MySQL и запускаем его без чтения таблицы юзеров (This option causes the server not to use the privilege system at all, which gives anyone with access to the server unrestricted access to all databases):

sudo /etc/init.d/mysqld stop
/usr/libexec/mysqld --skip-grant-tables --user=root

Для Дебияна команда примет следующий вид:

/usr/sbin/mysqld --skip-grant-tables --user=root

Теперь, как нам стал доступен MySQL с полным доступом, сбрасываем пароль на единицу:

mysql -u root -e "UPDATE mysql.user SET Password=PASSWORD('1') WHERE User='root'; FLUSH PRIVILEGES;"

После этого останавливаем пущенный нами инстанс MySQL и запускаем обычный сервер:

killall -9 -r mysqld
sudo /etc/init.d/mysqld start

Ну вот и всё:

mysql -uroot -p1
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.45 Source distribution

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

mysql>

По информации с: http://www.centos.org/modules/newbb/viewtopic.php?viewmode=thread&topic_id=18433&forum=41&post_id=67253

--------------

I have a database table with a auto increment column for primary key. As the records being add and delete many times, the auto increment value will keep increasing.

Problem One:
If I have entered 10 records, and deleted 9th, 10th records. The next auto increment value will be 11, not 9.

Solution:
Run a query: ALTER TABLE tablename AUTO_INCREMENT = 1

This will reset the next auto increment value to current largest value in the auto increment column + 1. So, the auto increment value of next inserted record will start from 9.

Problem Two:
If I have entered 10 records, and deleted center records – 4th, 5th. I want to insert next record as 4th not 11th.

Solution:
Run the following query:
SET insert_id = 4;
INSERT INTO tablename VALUES ('blah', '...');

This will add the next record into record 4th.

The SET insert_id = #(where # is the next auto increment value you want to use) will reset the next auto increament value, the next query(INSERT) you run will use your choice of value.

Note: only effective for the immediate next query, one time.

To re number the ID instead you can do this magic trick:

SET @var_name = 0;
UPDATE Tablename SET ID = (@var_name := @var_name +1);

You can change @var_name to start from 80, and your ID will be:
81, 82,83, ...

then combine it with

ALTER TABLE Tablename AUTO_INCREMENT = 6;

So the next auto_increment will be 6 , and all your ID is 1,2,3,4,5.
Hope this helps.