Virtual Brain/MySQL 1 Changing MySQL root password Start mysql with the --skip-grant-tables option: {code} [root@vs /]# service mysql stop Shutting down MySQL. SUCCESS! [root@vs /]# /usr/sbin/mysqld --skip-grant-tables --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/vs.vserver.de.pid --skip-external-locking --socket=/var/lib/mysql/mysql.sock {code} And (in another shell) change the root password: {code} [root@vs /]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 to server version: 4.1.22-standard Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> UPDATE mysql.user SET Password=PASSWORD('xxxx') WHERE User='root'; Query OK, 2 rows affected (0.02 sec) Rows matched: 2 Changed: 2 Warnings: 0 mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) mysql> exit Bye {code} Restart the mysql service, and see: Login as root works again: {code} [root@vs /]# mysql -u root --password=xxxx Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.1.22-standard Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> {code} 1 Creating new databases/users with skip-grant-tables option Use the mysql startup command above to run the DB with skip-grant-tables option and connect as root in another shell. The following code creates a new database and assigns one user SELECT, INSERT, UPDATE, DELETE, CREATE and DROP privileges on this database. The user is only allowed to connect from localhost. The SQL uses the OLD_PASSWORD() hash function because the PHP version (4.3.11) running against the DB (MySQL 4.1.22) does not support the new password hashes. {code} mysql> use mysql mysql> create database usr_web5_1 -> ; Query OK, 1 row affected (0.00 sec) --doesn't work because DB is started with --skip-grant-tables option GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON usr_web5_1.* TO 'web5'@'localhost' IDENTIFIED BY 'somepw'; --Replacement for the grant statement above INSERT INTO user (Host,User,Password) VALUES('localhost','web5',OLD_PASSWORD('obscure')); INSERT INTO db (Host,Db,User,Select_priv,Insert_priv, Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES('localhost','usr_web5_1','web5', 'Y','Y','Y','Y','Y','Y'); -- updating a password UPDATE mysql.user SET Password=OLD_PASSWORD('somepw') WHERE User='web5'; {code} Nicely kill database deamon running in the other shell and start the mysql service again. 2010-01-31 00:06:14.79 2009-03-10 00:01:21.421