Initial install and give password: [[email protected] html]# yum install-y MySQL mysql-server #安装mysql可与段与服务器端 [[email protected] ~]#/usr/bin/mysqladmin-u root passwor D "Gslixiong" #赋予root管理员密码! Change Password into database and resolve password complexity: mysql> set global validate_password_policy=0; #定义复杂度mysql > set Global validate_password_length=1; #定义长度 Default is 8mysql>set password for ' root ' @ ' localhost ' =password (' 123456 '); Authorize and create users (local) mysql> grant all privileges on 51baohumo.* to ' Baohumo ' identified by ' Ghuxi3 '; (entire network) authorization and creation User mysql> flush privileges; separate user password: set PASSWORD for ' Zabbix ' @ ' localhost ' =password (' Zabbix '); UPDATE mysql.user SET password=password (' New password ') WHERE user= ' root '; view user list select DISTINCT CONCAT (' User: ', user, ' ' @ ', ' host ', '; ') As query from mysql.user; View the permissions of a specific user in the database mysql> show grants for ; password Settings related 1, mysql> set PASSWORD for ' root ' @ ' localhost ' = PASSWORD (' NewPassword '); 2, mysql> use MySQL; mysql> UPDATE Usersetpassword=password (' newpassword ') WHERE user= ' root '; mysql> flush Privileges;3, # mysqladmin-u root password OldPassword ' NewPassword ' (not first set) 4, # mysqladmin-u root password ' newpassword ' (first set) 5, # [mysqld] --skip-grant-tables mysql-u root mysql (using a blank password to log in with the root user, Also use the system named MySQL built-in database--root and MySQL must have a space between mysql> UPDATE Usersetpassword=password (' newpassword ') WHERE user= ' root '; mysql> flush privileges; mysql password problem mysql One of the recovery methods for password 1. First verify that the server is in a secure state, that is, no one can connect to the MySQL database arbitrarily. Because the MySQL database is completely out of password-protected state during the reset of the root password of MySQL, other users can log in and modify the MySQL information arbitrarily. It is possible to implement the server's quasi-security state by enclosing MySQL's external ports and stopping Apache and all user processes. The safest state is to operate on the console of the server and unplug the network cable. 2. To modify the login settings for MySQL: # vi/etc/my.cnf in the paragraph of [mysqld] Add: skip-grant-tables Save and Exit VI. 3. Restart mysqld#/etc/init.d/mysqld restart (Service mysqld restart) 4. Log in and modifyMySQL root password mysql> use MySQL;mysql> UPDATE user SET Password = Password (' New-password ') WHERE user = ' root '; mys Ql> flush Privileges;mysql> quit5. Change the login settings for MySQL back to # vi/etc/my.cnf add the skip-grant-tables that you just added in the [mysqld] section to save and Exit VI. 6. Restart mysqld#/etc/init.d/mysqld restart (Service mysqld restart) 7. Restoring the server's normal working state reverses the operation in step one. Restores the server's working status. mysql Password recovery Method b If you forget the root password of MySQL, you can reset it in the following ways: 1. Kill the MySQL process in the system; Killall-term mysqld2. Start MySQL with the following command to start without checking permissions; Safe_mysqld--skip-grant-tables &3. Then use the root user login Mysql;mysql-u root4 with a blank password. Modify the root user's password;mysql> update mysql.user set Password=password (' New password ') where user= ' root ';mysql> flush privileges; Mysql> quit restart MySQL, you can use the new password to login to the mysql password recovery method Three possible your system does not have SAFE_MYSQLD program (such as I am now using the Ubuntu operating system, Apt-get installed MySQL) , the following method can recover 1. Stop Mysqld;/etc/init.d/mysql Stop (you may have other methods, anyway stop mysqld running on it) 2. Start MySQL with the following command to start without checking permissions; mysqld--skip-grant-tables &3. Then use the root user login Mysql;mysql-u root4 with a blank password. Modify the root user's password;mysql> update mySql.user set Password=password (' NewPassword ') where user= ' root ';mysql> flush privileges;mysql> quit restart mysql/ Etc/init.d/mysql Restart will be able to log in with the new password newpassword. 1. Installation optimization # mysql need to run Mysql_secure_installation script after installation is complete mysql> Flush privileges; Query OK, 0 rows Affected (0.00 sec) Manual Refresh permissions table command mysql user Management and authorization (case insensitive) 1. You can assign a library administrative rights to a user or some execution statements for this library (statements such as Select Update) mysql> CREATE USER username identified by ' password '; #创建用户 mysql>grant all privileges in alinx.* to ' Lijie ' @localhost identified by ' password '; Authorization and password change mysql> drop user [email protected]; #删除用户 2. View version: mysql> Select version () \g 3. ViewAuthorization for one user mysql> show grants for tom; 4. Go to database &NB sp; mysql> use alinx; 5. Query user list mysql> Select User, Host from user; 6. Change Password individually mysql> set password for Alinx=password (' [email& Nbsp;protected] '); Query OK, 0 rows Affected (0.00 sec) 7. Remove permissions for a user mysql> revoke all on *. * From Alinx; Query OK, 0 rows Affected (0.00 sec) 8. Current database view mysql> Select database (); 9. Querying the current data mysql> select * from books; 10. Display the database in rows mysql> show databases \g 11. Polygon Interactive Query (more used in scripts) [[email protected] ~]# mysql-e ' show databases '-uroot-pgslixiong+------------------ --+| database |+--------------------+| Information_schema | | ha &NBsp | | alinx | | alinx-test | | book | | mysql | | school |+--------------------+ 12. Create DATABASE create database name 13. The Delete database drop command is used to delete a database. drop command format: Drop databases < database name >; Note: Modifications must be done to restart the service or refresh the Permissions table command: Mysql> Flush privileges;
MySQL Operations common operation
