Mysql 5.1

Mysql 5.1

Create a user and authorize mysql:

Format: grant permission on database name. Table name to user @ login host identified by "User Password ";

Grant [English] [gr privileges: nt] acknowledge; agree; permit; grant;

Example 1: Allow mk users to log on from localhost

Mysql> grant all on book. * to mk1 @ localhost identified by "123456"; # allow access to all tables in the book database. Only users of the same server can access the book table.

Allow mk2 users to connect to the mysql server from any remote host:

Mysql> grant all privileges on *. * to mk2 @ '%' identified by '000000' with grant option; # with grant option means that mk2 users can grant their permissions to new users. In addition, you can add or not add privileges. % Refers to any remote host, excluding the local address and localhostFlush privileges; refresh the database

Test:

[Root @ xuegod64 ~] # Mysql-u mk2-h 192.168.1.63-p123456mysql> # normal Login

However:

[Root @ xuegod63 ~] # Mysql-u mk2-h 192.168.1.63-p123456 # login unavailable

Solution:

Mysql> grant all privileges on *. * to 'mk2' @ '192. 168.1.63 'identified by '100' with grant option; [root @ xuegod63 ~] # Mysql-u mk2-p123456 # login unavailable

Solution:

mysql> grant all privileges on *.* to 'mk2'@'localhost' identified by '123456' with grant option;

Summary: % refers to any remote host, excluding the local address and localhost. In addition, grant takes effect immediately. No need to execute: mysql> flush privileges; # manually update the command

You must run mysql> flush privileges only after you manually modify the mysql fields;

Only some permissions are authorized:

mysql> grant select,insert,update,delete,create,drop on aa.* to 'custom'@'localhost' identified by '123456';

Method 2: directly modify the permission file in the table:

mysql> use mysql；mysql> insert into user (Host,User,Password) values('localhost','grace','123456');mysql> select Host,User,Password from user where User="grace";+-----------+-------+----------+| Host   | User | Password |+-----------+-------+----------+| localhost | grace | 123456  |+-----------+-------+----------+

You can see that the password is stored in plaintext and is stored in encrypted mode:

Mysql> insert into user (Host, User, Password) values ('localhost', 'grace1 ', password ("123456"); Query OK, 1 row affected, 3 warnings (0.00 sec) mysql> select Host, User, Password from user where User = "grace1 "; + ----------- + -------- + Host + | Host | User | Password | + ----------- + -------- + Host + | localhost | grace1 | * cursor | + ----------- + -------- + cursor + 1 row in set (0.01 sec) mysql> flush privileges; # refresh the permission table to make the configuration file take effect.

Or restart the mysql database.

[root@xuegod63 ~]# service mysqld restart

Test:

[Root @ xuegod63 ~] # Mysql-u grace-p123456 # logon Failure ERROR 1045 (28000): Access denied for user 'grace '@ 'localhost' (using password: YES) [root @ xuegod63 ~] # Mysql-u grace1-p123456 # logon successful

Change account password:

Method 1: Use mysqladmin to change the password

Example 1: When the root user does not have a password:

[root@xuegod63 mysql]# mysqladmin -u root -h 192.168.1.63 password '123'[root@xuegod63 mysql]# mysql -u root -h 192.168.1.63 -p123

Example 2: When the root user already has a password:

[root@xuegod63 ~]# mysqladmin -u root password '123456' -p123

Method 2: Use set password to change the password:

Mysql> set password for 'grace1 '@ 'localhost' = PASSWORD ('000000'); # note that your mysql database has records: grace1 '@ 'localhostmysql> set password = password ('000000'); mysql> flush privileges;

Reset the root password:

[root@xuegod63 mysql]# /etc/init.d/mysqld stop[root@xuegod63 mysql]# mysqld_safe --skip-grant-tables --skip-networking

Valid only in MySQL

Re-open another terminal: You can directly go in and use update to change the password.

[Root @ xuegod63 aa] # mysql # execute mysql> update mysql. user set password = password ('200') where host = 'localhost' and user = 'root'; [root @ xuegod63 aa] #/etc/init. d/mysqld restartStopping mysqld: [OK]

The above section describes how to change the password of mysql 5.1 and remotely log on to the mysql database. I hope it will be helpful to you. If you have any questions, please leave a message for me, the editor will reply to you in a timely manner. Thank you very much for your support for the help House website!