MySQL account related

L1.Mysql User Creation:

Shell & GT; MySQL -User = Root mysql

Mysql & GT; Grant All Privileges on *. * To MonTy@LocalHost IDENTIFIED BY 'SometHing' H Grant Option;

Mysql & GT; GRANT ALL PRIVILEGES ON *.* TO monty@"%" IDENTIFIED BY 'something' WITH GRANT OPTION;

mysql> GRANT RELOAD,PROCESS ON *.* TO admin@localhost; *.* TO dummy@localhost;

These GRANT statements install 3 new users:

monty

A full superuser who can connect to the server from anywhere, but must use a password 'something' to do this. Note that we must issue a GRANT statement for monty@localhost and monty@"%". If we add the localhost entry, the anonymous user entry for localhost will take precedence over the entry created by mysql_install_db when we connect from localhost because it has a more specific Host field value and therefore comes earlier in the user table sort order.

admin

A user who can connect from localhost without a password and is granted reload and process administrative rights. This allows users to execute the mysqladmin reload, mysqladmin refresh and mysqladmin flush-* commands, as well as the mysqladmin processlist. No database-related permissions have been granted. They can be granted later by issuing another GRANT statement.

dummy

A user can connect without a password, but only from localhost. Global permissions are set to 'N' - the USAGE permission type allows you to set up a user without permission. It assumes that you will grant database-related permissions later.

2. Set user permissions by directly modifying the authorization table, run these commands (note, FLUSH PRIVILEGES at the end):

shell> mysql --user=root mysql

mysql> INSERT INTO user (Host,User,PassWord) VALUES('localhost','custom',PASSWORD('stupid'));

mysql> INSERT INTO user (Host,User,Password) VALUES('server.domain', 'custom',PASSWORD('stupid'));

mysql> INSERT INTO user (Host,User,Password) VALUES('whitehouse.gov','custom',PASSWORD('stupid'));

mysql> INSERT INTO db (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv, Create_priv,Drop_priv)

VALUES ('localhost','bankaccount','custom','Y','Y','Y ','Y','Y','Y');

mysql> INSERT INTO db

(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv, Create_priv,Drop_priv)

VALUES ( 'whitehouse.gov','expenses','custom','Y','Y','Y','Y','Y','Y');

mysql> INSERT INTO db ( Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv, Create_priv,Drop_priv)

  VALUES('%','customer','custom','Y','Y','Y','Y', 'Y','Y');

mysql> FLUSH PRIVILEGES;

3. When changing the password, sometimes you will forget the previous password. In this case, you can use the following methods:

Modify your my.ini configuration file,

Add the following line somewhere in the [mysqld] section

skip-grant-tables

Then save it and restart the mysqld service so that you can use any username as super The administrator's body is logged into the MySQL service.

Then go to the mysql database and modify the user table:

update user set password=PASSword('1234') where user='root';

Be sure to remember to use the password function, otherwise the password you will enter in the future is' 1234' ciphertext.

Finally delete the content added above and restart the service.

In addition, if you can find a MySql database that can be used normally from this machine or other machines, you can also copy the related file named user in its mysql library and overwrite the file with the same name in the database that you have forgotten the password. In this way, you can also log in with the account and password of the person being copied.

4. How to set up a MySql account so that it can access the server remotely

In version 5.0, there is actually a graphical interface configuration wizard tool. When setting the root account password, you can choose whether to allow it to access the server from a remote location. remote access. Not allowed by default.

In the command line, there are two similar methods to make this setting:

(1)mysql>GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED BY 'something' WITH GRANT OPTION;

mysql> GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY 'something' WITH GRANT OPTION;

The first sentence adds a root user to authorize access through the local machine (localhost) with the password "something".

The second sentence uses wildcards to grant access initiated by the root user from any other host.

(2) You can also directly use the update statement to modify the user table: log in to mysql using the root user or other users, and go to the mysql database

update user set host='localhost' where user='root';

flush privileges;//Only allow root to log in on this machine

update user set host='%' where user='root';

flush privileges;//Allow root remote access

Note that the above must be Add flush statement.

In addition, if you want to create a new user, it is more convenient to use the grant statement. Remember that the grant statement always creates a new user.

5. Regarding the 3306 port of MySql

When I newly installed 5.0.18 on the server, it was prompted that the 3306 port was occupied. If I forced to use this port, I found that the connected database was version 4.1.8. , the newly created server cannot be accessed. Later it was discovered that there was a mysql server installed on the machine, version 4.1.8.

At this time, you should reconfigure the new version of the database and set the port to something else, such as 3307. Then when accessing, you can access it by adding the option: --port=3307, also in the .net program. You must add port=3307 to the connection string.

The above is the content related to MySQL account. For more related articles, please pay attention to the PHP Chinese website (m.sbmmt.com)!