Mysql-5.6.16-win32 installation-free configuration method

Reprinted from: http://blog.csdn.net/fzhmoive/article/details/20042437,http://blog.csdn.net/leili0806/article/details/8573636

1. Download MySQL Community Server 5.6.16
2. decompress the MySQL package
Decompress the downloaded MySQL package to the custom directory. The decompressed directory is:
"D: \ Program Files \ MySQL \ mysql-5.6.16-win32"

Copy the default file my-default.ini in the unzipped directory and change it to my. ini.
Copy the following configuration information to my. ini and save it.
# If there is no my-default.ini, you can create my. ini yourself or get it from somewhere else

######################################## #################
[Client]
Port = 3306
Default-character-set = utf8

[Mysqld]
Port = 3306

Character_set_server = utf8
# Character_set_server = utf8 must be written like this;

Basedir = D: \ Program Files \ MySQL \ mysql-5.6.16-win32
# Decompress the Directory

Datadir = D: \ Program Files \ MySQL \ mysql-5.6.16-win32 \ data
# Extract the data directory under the Directory, which must be the data directory

# SQL _mode = NO_ENGINE_SUBSTITUTION, STRICT_TRANS_TABLES. An error is returned when a new user is logged on.
SQL _mode = NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION
######################################## #################

3. Add Environment Variables
The procedure is as follows:

Add: D: \ Program Files \ MySQL \ mysql-5.6.16-win32 under path Environment Variable

4. Register a windows system service
Register mysql as a windows system service

The procedure is as follows:

1) log on to the bin directory under the MySQL decompression directory from the console:

2) enter the service installation command:

Mysqld install MySQL -- defaults-file = "D: \ Program Files \ MySQL \ mysql-5.6.16-win32 \ my. ini"
# Decompress the my. ini file modified in the directory

After the installation is successful, a message is displayed, indicating that the service is successfully installed.

# Note: Put the my. ini file in the root directory after MySQL Decompression

# The command for removing a service is mysqld remove.

5. Start the MySQL Service
Run the following command to start the service: net start mysql.

If an error is reported: MySQL service cannot start error 1067; check the err file in the data directory:
10:50:12 5360 [ERROR] InnoDB:. \ ibdata1 can't be opened in read-write mode
10:50:12 5360 [ERROR] InnoDB: The system tablespace must be writable!
10:50:12 5360 [ERROR] Plugin 'innodb' init function returned error.
10:50:12 5360 [ERROR] Plugin 'innodb' registry as a storage engine failed.
10:50:12 5360 [ERROR] Unknown/unsupported storage engine: InnoDB
10:50:12 5360 [ERROR] Aborting

Solution: Delete the ib_logfile0 and ib_logfile1 files under the data Directory.
Restart, normal

6. Modify the password of the root account

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-u root

Mysql> show databases;

Mysql> use mysql;

Mysql> UPDATE user SET password = PASSWORD ("123456") WHERE user = 'root ';

Mysql> flush privileges;

Mysql> QUIT

How to create a new user in Mysql

1. CREATE USER

Syntax:

Create user 'username' @ 'host' identified by 'Password ';

Example: create user 'dog '@ 'localhost' identified by '123 ';

Create user 'pig' @ '192. 168.1.101 _ 'idendified BY '123 ';

Create user 'pig' @ '%' identified by '000000 ';

Create user 'pig' @ '%' identified '';

Create user 'pig' @ '% ';

Instance 1:

Mysql> create user jss;

In this way, you can create a connection from any machine on which the mysql client is installed and can access the target server without a password. For example, execute a connection from the client with ip Address: 10.0.0.99:

Mysql-ujss-h 172.16.1.110

View this user:

Mysql> select user, host, password from user where user = 'jss ';

Select user (); // displays the current USER

Instance 2:

Mysql> create user jss_ps identified by 'jss ';

You must specify a password when connecting to a user. You can set the password by specifying the identified by clause when creating a user.

Login with password:

Mysql-ujss_ps-p-h 172.16.1.110

If you want the specified user to only access from a specified domain or host, you can specify the host when creating the user. For example, the specified user can only access from 10.0.0.99

Mysql> create user jss_ip@10.0.0.99 identified by password '20140901 ';

 

2. Use the GRANT statement

Syntax: mysql> grant permission 1, permission 2,... Permission n on database name. Table name to user name @ user address identified by 'Connection password ';

Permission 1, permission 2,... Permission n stands

Select, insert, update, delete, create, drop, index, alter, grant, references, reload, shutdown, process, file, and other 14 Permissions

Instance:

Mysql> grant select, insert, update, delete, create, drop on vtdc. employee to joe@10.163.225.87 identified by '20140901 ';

Assign the user joe from 10.163.225.87 the permission to perform select, insert, update, delete, create, drop, and other operations on the database's vtdc employee table, and set the password to 123.

Mysql> grant all privileges on vtdc. * to joe@10.163.225.87 identified by '20140901 ';

Assign the user joe from 10.163.225.87 the permission to perform all operations on all tables in the database vtdc and set the password to 123.

Mysql> grant all privileges on *. * to joe@10.163.225.87 identified by '20140901 ';

Assign the user joe from 10.163.225.87 the permission to perform all operations on all tables in all databases and set the password to 123.

Mysql> grant all privileges on *. * to joe @ localhost identified by '20140901 ';

Grant the local user joe the permission to perform all operations on all tables in all databases and set the password to 123.

3. insert records directly to the mysql. user table:

Mysql> insert into user (host, user, password) values ('%', 'jss _ insert', password ('jss '));

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

4. Modify the mysql user password:

A. Use mysqladmin Syntax: mysqladmin-u username-p old password New password

For example, mysqladmin-u root-p 123 password 456;

B. directly modify the user password of the user table:

Syntax: update mysql. user set password = password ('new password') where User = "phplamp" and Host = "localhost ";

Instance: update user set password = password ('54netseek ') where user = 'root ';

Flush privileges;

C. Use the set password statement to modify the PASSWORD: Syntax:

Set password for 'username' @ 'host' = PASSWORD ('newpassword ');

If the current login user uses set password = PASSWORD ("newpassword ");

Instance:

Set password for root @ localhost = password ('');

Set password for name = PASSWORD ('new password ');

Set password for 'pig' @ '%' = PASSWORD ("123456 ");

5. delete users and revoke permissions:

A. Cancel an account and its permissions

Drop USER user;

Drop user username @ '%'

Drop user username @ localhost

B. cancelling authorized users:

Syntax: REVOKE privilege ON databasename. tablename FROM 'username' @ 'host ';

Example: revoke select on *. * FROM 'pig' @ '% ';

Revoke select on test. user FROM 'pig' @ '% ';

Revoke all on *. * from sss @ localhost;

Revoke all on user. * from 'admin' @ '% ';

Show grants for 'pig' @ '%'; // view authorization

C. delete a user:

Syntax: Delete from user where user = "user_name" and host = "host_name ";

Example: delete from user where user = 'sss' and host = 'localhost ';

 

Ii. database tables

1. view all databases: Database Directory:/usr/local/mysql/data

Mysql> show databases; // displays the database

Mysql> USE abccs // enter the database

Mysql> show tables; // display table

Mysql> DESCRIBE mytable; // displays the table structure.

Mysql> create database abccs; // CREATE a DATABASE

Mysql> create table mytable (name VARCHAR (20), sex CHAR (1), birth DATE, birthaddr VARCHAR (20); // CREATE a TABLE

Mysql> insert into mytable values ('abccs ', 'F', '2017-07-07', 'China'); // insert table data

Insert data using text:

{

Mysql.txt content: abccs f 1977-07-07 china

Mary f 1978-12-12 usa

Tom m 1970-09-02 usa

Mysql> load data local infile "mytable.txt" into table pet; // import TXT file DATA

}

 

2. delete a database:

Mysql> drop database drop_database; // delete a specified database

Alter table name ENGINE = storage ENGINE name; // modify the storage ENGINE of the table

Alter table Name drop attribute name; // delete a field

Alter table old table name rename to new table name; // Modify table Name

Alter table name modify attribute name data type; // modify Field Data Type

Alter table name change old property name new data type; // Modify Field name

Alter table Name drop foreing key foreign KEY alias; // Delete the foreign KEY constraint of the sub-table

Add Table fields:

{Alter table example add phone VACGAR (20); // add a unrestricted Field

Alter table example add age INT (4) not null; // add a field with a increment constraint

Alter table example add num INT (8) primary key first; // add a field to the FIRST position of the table

Alter table example add address VARCHAR (30) not null after phone; // add a field AFTER the specified position of the table

Alter table example modify name VARCHAR (20) FIRST; // modify the field to the FIRST place.

Alter table example modify num INT (8) ATER phone; // After modifying a field to a specified field

}