MySQL installation and configuration method (MySQL add user, delete user and authorization) _mysql

1. Install MySQL

Currently MySQL has two forms of file, one is MSI format, one is in the zip format. MSI format directly click Setup.exe, follow the steps. But many people under the zip format of the decompression found no setup.exe, I downloaded is also the case, do not know how to install, the point where there is no response. Can only seek the Niang help, then learned that this kind of file installs the way.

1 Extract the file to the location you think fit.

2 Create a new My.ini in the directory (there is already a Mydefault.ini file in the file), the new will overwrite the effect of the original file. Paste the code in the file:

[MySQL]
# set MySQL client default character set
Default-character-set=utf8 
[mysqld]
#设置3306端口
port = 3306 
# set MySQL installation directory 
    Basedir=d:\mysql\mysql-5.6.24-win32
# Set up a repository of data for MySQL database
datadir=d:\mysql\mysql-5.6.24-win32\data<  c12/> # allows maximum number of connections
max_connections=200
# Server uses a character set that defaults to a 8-bit encoded latin1 character set
Character-set-server=utf8
# Default storage engine to be used when creating a new table
Default-storage-engine=innodb

The above code tag section needs to be modified to its own directory, which is the extracted directory.

3) Add Environment variables

The operation is as follows:

(1) Right-click My Computer-> Properties-> advanced system settings (Advanced)-> environment variables

Click the New button under the system variable

Input variable name: mysql_home

Input variable Value: D:\mysql\mysql-5.6.24-win32

#即为mysql的自定义解压目录.

(2) Select path in the system variable

Click the Edit button

To add a variable value to a variable value:%mysql_home%\bin

Note that after the original value of the variable plus this variable, with; separated, cannot delete the original variable value

4 run cmd as an administrator (be sure to run as an administrator, or not enough authority),

Input: cd C:\Program files\mysql\mysql Server 5.6\bin into the MySQL Bin folder (regardless of whether the environment variable has been configured, also enter the Bin folder, otherwise the start service will still report error 2)

Enter Mysqld-install (if you do not run as an administrator, there will be an error due to insufficient permissions: Install/remove of the Service denied!)

Installation Successful

5) Start MySQL service

Method One:

Start service command for: net start MySQL

Method Two:

Open the admin tools service and find the MySQL service.
Start the service by right-clicking on the startup or clicking on the left side of the boot directly.

6 when the installation is complete, the root account default password is blank, the password can be modified to the specified password. such as: 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;

This configuration is complete.

2. Add Users and authorize

MySQL add users, create a new database, user authorization, delete users, modify password (note that each line is followed by the following; Represents the end of a command statement):

1. New User

1.1 Login MySQL:

@>mysql-u root-p
@> Password

1.2 Creating users:

mysql> INSERT INTO Mysql.user (Host,user,password) VALUES ("localhost", "User", Password ("1234"));

This creates a user with a username password of: 1234.

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

1.3 and then log in:

mysql>exit;
@>mysql-u test-p
@> Enter password
mysql> login succeeded

2. Authorize the user

Authorization format: Grant permission on database. * To User name @ login host identified by "password";

2.1 Login MySQL (with root privileges), here as root login:

@>mysql-u root-p
@> Password

2.2 First create a database for the user (MyDB):

Mysql>create database MyDB;

2.3 Authorize the user to have all the permissions of the MyDB database (all permissions for a database):

Mysql>grant all privileges in mydb.* to test@localhost identified by ' 1234 ';
Mysql>flush privileges;//Refresh System Permission table

Format: Grant permission on database. * To User name @ login host identified by "password";

2.4 If you want to specify partial permissions to a user, you can write this:

Mysql>grant select,update on mydb.* to test@localhost identified by ' 1234 ';
Mysql>flush privileges; Refresh System Permission Table

2.5 Authorize test users to have certain permissions for all databases:

Mysql>grant Select,delete,update,create,drop on *.* to user@ "%" identified by "1234";
Test user has Select,delete,update,create,drop permissions on all databases.
//@ "%" represents all non-local host authorizations, excluding localhost. (localhost address set to 127.0.0.1, if set to true local address, do not know whether it can, no validation.)
//To localhost authorization: Add a Word grant all privileges on mydb.* to test@localhost identified by ' 1234 ';

3. Delete User

When you do not distribute the database to users, use 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 @ '% ';

When you do not assign a database to a user, you need to delete it in the following ways:

4. Modify the specified user password

@>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 databases

Mysql>use ' database name ';

7. List all Tables

Mysql>show tables;

8. Display data table structure

Mysql>describe table name;

9. Delete database and datasheet

Mysql>drop database name;
Mysql>drop table datasheet name;

The above is a small series to introduce the MySQL installation and configuration method (MySQL Add users, delete users and authorization), I hope to help you, if you have any questions please give me a message, small series will promptly reply to everyone. Here also thank you very much for the cloud Habitat Community website support!