MySQL change root password and set permissions

We have compiled the following four ways to change the root password in MySQL, which may help!

Method 1: Use the Set password command

Mysql-u Root

Mysql> SET PASSWORD for ' root ' @ ' localhost ' = PASSWORD (' Newpass ');

Method 2: Use Mysqladmin

mysqladmin-u root Password "Newpass"

If Root has already set a password, use the following method

mysqladmin-u root password Oldpass "Newpass"

Method 3: Edit the user table directly with update

Mysql-u Root

mysql> use MySQL;

mysql> UPDATE user SET Password = Password (' newpass ') WHERE user = ' root ';

mysql> FLUSH privileges;

This can be done when the root password is lost

Mysqld_safe--skip-grant-tables&

Mysql-u Root MySQL

mysql> UPDATE user SET Password=password ("New password") WHERE user= ' root ';

mysql> FLUSH privileges;

Set permissions:

GRANT all privileges on * * to ' root ' @ '% ' identified by ' admin123 ' with GRANT OPTION; flush privileges;

Root is not supported by default remote login, external network connection you have to give permission? GRANT all privileges on * * to ' username ' @ ' % ' identified by ' password ' with GRANT OPTION; you first create a Telnet account and then give it remote login permissions .

MySQL root account, I usually use the connection is localhost or 127.0.0.1, the company's test server mysql is also localhost so I want to access inaccessible, test paused.

Here's how to fix it:

1, modify the table, log in to the MySQL database, switch to the MySQL database, use the SQL statement to view "select Host,user from User;" Mysql-u root-pvmwaremysql>use MySQL; Mysql>update User Set host = '% ' where user = ' root '; Mysql>select host, user from user; Mysql>flush privileges; Note: The last sentence is important in order for the changes to take effect. If you do not write, you still cannot connect remotely.

2, authorized user, you want root to use password from any host to connect to MySQL server GRANT all privileges on * * to ' root ' @ '% ' identified by ' admin123 ' with GRANT OPTION; Flush privileges; If you want to allow user root to connect to the MySQL server from the IP 192.168.1.104 host, GRANT all privileges on * * to ' myuser ' @ ' 192.168.1.104 ' identified by ' Admin123 ' with GRANT OPTION; Flush privileges;

Router extranet access MySQL database

1. The following conditions are met
Public IP whether dynamic or static static better dynamic if too troublesome can be used DDNS services such as peanut shell
2. Open port
Whether you are routing connected or dial-up routing needs to do 3306 port mapping in the router dial-up broadband needs to allow 3306 port access in the firewall can be tested with the Telnet command
3.MYSQL User rights (more important here)
In the installation of MySQL is root by default is only local access permissions localhost can be installed at the time of the installation can be remote installation of the last step there is an option box to check this is the version of Windows if the Linux version with command and permissions
If you want to create a new user, you must have% remote permission to
4 Testing
Test mysql-h (IP address) from the command line with the-u user name-p (password) Enter if Mysql> appears
That is OK

It used to only use
Mysql> SELECT * from Mysql.user where user= ' username ';

Today it is more convenient to find this:
Mysql> Show grants for [email protected];

Show can see a lot of things
Show CREATE Database dbname; This allows you to see some of the parameters used to create the database.
Show create table tickets; You can see some of the parameters used to create the table

1, modify the table, log in to the MySQL database, switch to the MySQL database, using SQL statements to view
"Select Host,user from User;"
\mysql-u root-pvmwaremysql>use MySQL;
\mysql>update User Set host = '% ' where user = ' root ';
\mysql>select host, user from user;
\mysql>flush privileges;

Note: The last sentence is important in order for the changes to take effect. If you do not write, you still cannot connect remotely.

2, authorized users, you want to root with a password from any host to connect to the MySQL server
\grant all privileges on * * to ' root ' @ '% ' identified by ' admin123 ' with GRANT option;flush privileges;

If you want to allow user root to connect to a MySQL server from a host with IP 192.168.12.16
\grant all privileges on * * to ' root ' @ ' 192.168.1.16 ' identified by ' 123456 ' with GRANT OPTION;

You can restrict access to this connection account by authorizing a user, such as:
Grant Select on Db.table1 to [e-mail protected] ' 1.2.3.% ' identified by ' password ';

This allows User1 to access your MySQL server only within the 1.2.3.% range.

LZ is this meaning no?

Good text to the topfollow me to collect this article

MySQL change root password and set permissions