MySql installation and MySQL add users, delete users and authorization, mysql add

MySql installation and MySQL add users, delete users and authorization, mysql add
1. Install MySqlCurrently, MySQL has two types of files: msi and zip. Click setup.exe directly in the msiformat and follow the steps. However, many people found that there was no setup.exe in zipformat, And I downloaded it myself. I don't know how to install it. I didn't respond to any point. You can only seek the help of DU Niang before learning about the installation method of this file. 1) decompress the file to the desired location. 2) create a new my. ini file in the directory (the file already contains a mydefault. ini file). The new file will overwrite the original file. Paste the code in the file:

[Mysql]
# Set the default Character Set of the mysql client
Default-character-set = utf8
[Mysqld]
# Set port 3306
Port = 3306
# Set the installation directory of mysql
Basedir = D :\mysql \ mysql-5.6.24-win32
# Set the directory for storing mysql database data
Datadir = D: \ mysql \ mysql-5.6.24-win32 \ data
# Maximum number of connections allowed
Max_connections = 200
# The default character set used by the server is the 8-bit latin1 character set.
Character-set-server = utf8
# The default storage engine used to create a new table
Default-storage-engine = INNODB

The above Code marked part needs to be modified to its own directory, that is, the decompressed directory. 3) add Environment Variables
The procedure is as follows:
(1) Right-click my computer-> properties-> advanced system settings (advanced)-> Environment Variables
Click new Under System Variables
Input variable name: MYSQL_HOME
Input variable value: D: \ mysql \ mysql-5.6.24-win32
# This is the custom extract directory of mysql.
(2) Select the Path in the system variable
Click Edit
Add the variable value % MYSQL_HOME % \ bin to the variable value.
Note that this variable is added after the original variable value and separated by;. The original variable value cannot be deleted. 4) Run cmd as an administrator (Be sure to run it as an administrator, otherwise, the permission is insufficient ),

Enter cd C: \ Program Files \ MySQL Server 5.6 \ bin to enter the mysql bin folder (whether or not environment variables are configured, enter the bin folder, otherwise, an error will still be reported when the service is started. 2)

Enter mysqld-install (if you do not need to run it as an administrator, the following error will occur due to insufficient permissions: Install/Remove of the Service Denied !)

Installed successfully

5) Start the MySQL Service

Method 1:
Run the following command to start the service: net start mysql.
Method 2:
Open the management tools service and find the MySQL service.
Right-click Start or click Start on the left to start the service.

6) When the installation is complete, the default password of the root account is blank. You can change the password to the specified password. Example: 123456
C:> mysql-uroot
Mysql> show databases;
Mysql> use mysql;
Mysql> UPDATE user SET password = PASSWORD ("123456") WHERE user = 'root ';
Mysql> flush privileges;
Mysql> QUIT;

So far, the configuration is complete.

  2. Add and authorize users

Add a user to MySql, create a database, authorize the user, delete the user, and modify the password (note that each line is followed by a command; it indicates that a command statement ends ):

1. Create a user

1.1 log on to MYSQL:

@> Mysql-u root-p

@> Password

1.2 create a user:

Mysql> insert into mysql. user (Host, User, Password) values ("localhost", "user", password ("1234 "));

In this way, a user named: user Password: 1234 is created.

Note: The "localhost" here means that the user can only log on locally and cannot log on remotely on another machine. If you want to log on remotely, change "localhost" to "%", which means you can log on to any computer. You can also specify that a machine can log on remotely.

1.3 log on to the system:

Mysql> exit;

@> Mysql-u test-p

@> Enter the password

Mysql> logon successful

 

2. Authorize the user

Authorization format: grant permission on database. * to username @ login host identified by "password ";

2.1 log on to MYSQL (with ROOT permission). Log On As ROOT:

@> Mysql-u root-p

@> Password

2.2 first create a database (myDB) for the user ):

Mysql> create database myDB;

2.3 authorize the user to have all permissions of the myDB database (all permissions of a database ):

Mysql> grant all privileges on myDB. * to test @ localhost identified by '123 ';

Mysql> flush privileges; // refresh the system permission list

Format: grant permission on database. * to username @ login host identified by "password ";

2.4 if you want to assign some permissions to a user, you can write as follows:

Mysql> grant select, update on myDB. * to test @ localhost identified by '123 ';

Mysql> flush privileges; // refresh the system permission list

2.5 authorize the test user to have certain permissions on all databases:

Mysql> grant select, delete, update, create, drop on *. * to user @ "%" identified by "1234 ";

// The test user has the select, delete, update, create, and drop permissions on all databases.

// @ "%" Indicates authorizing all non-local hosts, excluding localhost. (The localhost address is set to 127.0.0.1. If it is set to a real local address, you do not know if it is OK. No verification is performed .)

// Authorize localhost: add the grant all privileges on myDB. * to test @ localhost identified by '20160301.

 

3. delete a user

Method 2:

@> Mysql-u root-p

@> Password

Mysql> Delete FROM user Where User = 'USER' and Host = 'localhost ';

Mysql> flush privileges;

Mysql> drop database myDB; // delete a user's database

Delete account and permissions:> drop user username @ '% ';

> Drop user username @ localhost;

If no database is assigned to a user, delete the user as follows:

Delete from mysql. user WHERE User = "Username" and Host = "localhost ";

4. change the password of a specified user

@> Mysql-u root-p

@> Password

Mysql> update mysql. user set password = password ('new password') where User = "user" and Host = "localhost ";

Mysql> flush privileges;

 

5. list all databases

Mysql> show database;

 

6. Switch the database

Mysql> use 'database name ';

 

7. list all tables

Mysql> show tables;

 

8. display the data table structure

Mysql> describe table name;

 

9. Delete databases and data tables

Mysql> drop database name;

Mysql> drop table data table name;