MySQL common commands and questions summary

1, added password after the login format as follows: Mysql-u root-p

2. Modify Login Password
1) mysqladmin-u username-p Old password password new password
Example: mysqladmin-u root password 21century
Note: Because Root does not have a password at the beginning, the-p old password can be omitted.
2) directly modify the user table root password:
mysql> user MySQL;
mysql> Update user Set Pasword=password (' 21century ') where user= ' root ';
mysql> flush Privileges;
Note: Flush privileges means forcing the memory authorization table to be flushed or the password in the buffer is used.

3, test password is modified successfully
 1) login without password
 [[email protected] local]# mysql
 error 1045:access denied For user: ' [email protected]　　 ' (Using password:no)
  displays an error stating that the password has been modified.
2) Log in with the modified password
 [[email protected] local]# mysql-u root-p
 enter Password: (Enter the modified password 21century )
 welcome to the MySQL Monitor. Commands End With; or \g.   
 your MySQL Connection ID is 177 to server version:3.23.48   
  Type ' help ', ' or ' \h ' for help.　　Type ' \c ' to clear the buffer.　　
 mysql> Success!
  This is to modify the password with the mysqladmin command, or you can change the password by modifying the library

4. Start and stop
Startup: MySQL has been changed since version 3.23.15, the default post-installation service is started with a MySQL user and does not allow the root user to start.
If you want to start with the root user, you must add the--user=root parameter
(./safe_mysqld--user=root &)
Stop: mysqladmin-u root-p shutdown

5. Export meeting Database:
Mysqldump-uroot-p21century Meeting > Db_meeting.sql Guide dbname database
Mysqldump-uroot-p21century dbname < Xxx.sql
The import database can also execute a large number of SQL statements at once, similar to @my_script.sql in Oracle, which is useful when using mysqldump does not work.
Example: #./mysql-uroot-p

6, the table renamed RENAME table Ztemp to Ztemp4;

7. Modify the field properties alter TABLE bbabase change news_id ID VARCHAR (5) is not NULL;

8. Add a field after the content in the Table ALTER table bbabase add leave_time DATETIME not NULL after content;

9. Add user GRANT Select,update,insert on database name. * To User name @ login host identified by "password"
Example: Grant Select,update,insert,delete on * * to [E- Mail protected] identified by "ABC";

10, only need to use the MySQL service, at startup can also add--skip-networking parameters so that MySQL does not listen to any TCP/IP connection
(./safe_mysqld--skip-networking &) for added security. (very recommended)

11. What if I forget the root password?
When you start the MySQL server, add the parameter--skip-grant-tables to bypass the authorization table validation (./safe_mysqld--skip-grant-tables &) so that we can log in directly to the MySQL server,
Then change the root user's password and restart MySQL to log in with the new password.

12, solve the MySQL without user name and password can directly log on the problem:
mysql>use MySQL;
mysql>delete from user where user= ';
Mysql>flush privileges; (required)

/////////////////////////////

MYSQL1130 the wrong solution

In the remote connection to the MySQL server database, regardless of how it is connected, the error code is 1130,error 1130:host 10.0.0.1 are not allowed to connect to the this MySQL server
Conjecture is a problem with user permissions that cannot be given to remote connections.

As a result, the MySQL library can be solved.

After you log in to MySQL, change the" host "entry in the" User "table in the" MySQL "Database, and rename"% "from" localhost ".  
mysql-u root-p  
Mysql&gt;use mysql; 
mysql&gt; select ' host ' from user where user= ' root ';  
Mysql&gt; update user set host = '% ' where user = ' root ';  
Mysql&gt; flush privileges; 
mysql&gt;select ' host '    from user where user= ' root ';

The first sentence is to log in as the user root with permissions
Second sentence: select MySQL Library
The third sentence: View the host value of the user table in the MySQL library (hosts/IP names that can be accessed by the connection)
Clause Four: Modify the host value (increase the hostname/IP address with the wildcard%), or you can directly add
Clause five: Refresh MySQL system permissions related table
The sixth sentence: when you re-view the user table, there are modifications.
Restart the MySQL service to complete.

MySQL encountered ERROR 1044 (42000): Access denied for user solution

2011-05-05 11:51

This problem is mainly caused by the insufficient permission of the authorized user itself. As an example of root users, we need to be aware of the following areas:

The user table for 1.MySQL is important. The root user must be guaranteed to have two records in the users table, i.e.
Root localhost .....
Root 127.0.0.1 ....

2. Ensure that the root user has all the permissions, that is, all the fields in the user table are the corresponding content Y

3. In My.ini the latter my.cnf there is this configuration item in the time
Bind-address=localhost
Enable this configuration item to ensure security

4. When running MySQL on the server, try not to give% permission to the host field in the user table for security purposes .

To ensure that the above points are not a problem, this error can basically avoid

In addition, if the user cannot enter the MySQL system, this method can be used to enter:

If MySQL is running, stop first.
Start Mysql:bin/safe_mysqld--skip-grant-tables &
You can go to MySQL without a password.
Then there is
>use MySQL
>update User Set Password=password ("New_pass") where user= "root";
>flush privileges;

MySQL common commands and questions summary