MySQL installation with MySQL add users, remove users and authorizations

Source: Internet
Author: User
Tags mysql client

1. Install MySQLCurrently MySQL has two forms of files, one in MSI format and one in ZIP format. The direct click setup.exe in MSI Format is just fine, follow the steps. But many people under the ZIP format decompression found no setup.exe, I downloaded the same way, do not know how to install, point where there is no response.      Can only seek the Niang help, then only then understand, this kind of file installs the way.      1) Unzip the file to a location that you think is appropriate. 2) Create a new My.ini in the directory (there is already a Mydefault.ini file in the file), and the original file will be overwritten by the new one. Paste the code in the file:

[MySQL]
# Set the MySQL client default character set
Default-character-set=utf8
[Mysqld]
#设置3306端口
Port = 3306
# set up the MySQL installation directory
basedir=D:\mysql\mysql-5.6.24-win32
# Set up a storage directory for MySQL database data
datadir=D:\mysql\mysql-5.6.24-win32\data
# Maximum number of connections allowed
max_connections=200
# The character set used by the service side defaults to the 8-bit encoded latin1 character set
Character-set-server=utf8
# The default storage engine that will be used when creating a new table
Default-storage-engine=innodb

The above Code markup section needs to be modified to its own directory, that 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 System variables
Input variable name: mysql_home
Input variable Value: D:\mysql\mysql-5.6.24-win32
#the custom unzip directory for 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 this variable is added after the value of the original variable, separated, cannot delete the original value of the variable, 4) run cmd as administrator (be sure to run as administrator, or not enough permissions),

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

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

Installation Successful

5) Start the MySQL service

Method One:
The Start service command is: net start MySQL
Method Two:
Open the Administration Tools service and locate the MySQL service.
Start the service by right-clicking to start or by tapping on the left-hand boot directly.

6) The default password for the root account is empty when the installation is complete, and you can modify 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;

The configuration is complete.

2. Add Users and authorize

MySQL add user, new database, user authorization, delete user, change password (note that each line followed by A; indicates that a command statement ends):

1. Create a new user

1.1 Log in to MySQL:

@>mysql-u root-p

@> Password

1.2 Create User:

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

This creates a user with the username: 1234.

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

1.3 Then log in:

mysql>exit;

@>mysql-u test-p

@> Enter password

Mysql> Login Successful

2. Authorizing the user

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

2.1 Log in to MySQL (rooted), log in as root here:

@>mysql-u root-p

@> Password

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

Mysql>create database MyDB;

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

Mysql>grant all privileges in mydb.* to [e-mail protected] identified by ' 1234 ';

Mysql>flush privileges;//Refresh System Permissions Table

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

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

Mysql>grant select,update on mydb.* to [e-mail protected] identified by ' 1234 ';

Mysql>flush privileges; Refresh System Permissions Table

2.5 Authorization test The user has some permissions for all databases:

Mysql>grant Select,delete,update,create,drop On * * to [e-mail protected] "%" identified by "1234";

The test user has Select,delete,update,create,drop permissions on all databases.

@ "%" indicates authorization for all non-local hosts, excluding localhost. (The localhost address is set to 127.0.0.1, if set to the real local address, do not know whether it can, no authentication.) )

Authorization to localhost: plus a grant all privileges on mydb.* to [email protected] identified by ' 1234 ';

3. Delete a 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 @ '% ';

>drop user username @ localhost;

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

DELETE from Mysql.user WHERE user= "Username" and host= "localhost";

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. Switching the database

Mysql>use ' database name ';

7. List all Tables

Mysql>show tables;

8. Display the data table structure

Mysql>describe table name;

9. Deleting databases and data tables

Mysql>drop database name;

Mysql>drop table Data Sheet name;

MySQL installation with MySQL add users, remove users and authorizations

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.