MySQL 5.6.17 green version (install-free) installation configuration tutorial _mysql

Recently in the development of the project to use the MySQL database, after reading some articles about MySQL, quickly started to use. In the use of the process, there are still some problems, because the use of the green installation version of MySQL, so there are some problems in the configuration, the article is mainly for the MySQL green version of the configuration and its use for discussion.

A, MySQL overview
the MySQL database is developed by a Swedish MySQL AB company and is now owned by Oracle.       Like SQL Server, it is also a database management system based on relational databases, and MySQL is one of the best RDBMS in Web applications because it belongs to a lightweight RDBMS. Now the latest version of MySQL is 5.6.17, the latest download address:http://dev.mysql.com/downloads/mysql/, Download complete the next installation deployment, the content of the installation of the deployment on the Internet to view the tutorial on it.

Second, MySQL configuration

Since MySQL is based on SQL, he contains basic DML, DDL, DAL, these basic database languages are easy to use, and MySQL also encapsulates a lot of database operation commands, which are run in the DOS system, this is his and SQL The server is different, the MySQL environment is the resume on the DOS system, to use the DOS command. It is a bit like Java, it can be said that it is based on the virtual machine, can be built around the use of. To easily use the MySQL command also requires a number of prerequisites to set up a method similar to the Java environment variables, the following method to avoid installing version of MySQL as an example to demonstrate its configuration method.

1. mysql Environment configuration

The MySQL command can be used anywhere by configuring MySQL's decompression path to the system variable.

Note : This is a configured system variable, and any third party command that uses console commands can be added to the system variable, which is a link that takes precedence over the system variables when using commands.

2. mysql server configuration

After you configure the system's environment variables, you can use all the services provided under MySQL's Bin, and then you need to install MySQL on your system.
2.1 Installing the MySQL server

Open the Extract file directory, find a file with a suffix of. ini, copy a name renamed to My.ini, and replace the original content with the following.

[Mysqld]
Basedir=d:/program Files (x86)/mysql # set MySQL installation directory
datadir=d:/program Files (x86)/mysql/data # Set up a repository of MySQL database data , must be data, or//xxx/data

************************* split line *******************
port = 3306
socket =/tmp/ Mysql.sock
DEFAULT-CHARACTER-SET=GBK # Set the MySQL server's character set
skip-locking
key_buffer = 16K
max_allowed_ Packet = 1M
Table_cache = 4
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 64K

[client] 
#password = your_password
port = 3306
socket =/tmp/mysql.sock
DEFAULT-CHARACTER-SET=GBK 

Split Line *************************

Note: [Mysqld] The following basedir and DataDir need to be set to the path after the file is unpacked, where I put the file under D:\Program files (x86) \mysql. In addition, the contents of the above split line are optional and can be reset when setting up the database, it is recommended not to add when creating, because there will be a lot of uncertainties.

My.ini file configuration can be installed in CMD mysqld service, in CMD to run the command:mysqld--install MySQL--defaults-file= "D:\Program files (x86) \mysql\ My.ini ", where MySQL is the name of the installation server, you can specify any name. When the installation is complete, the following information will be prompted: Service successfully installed, which means successful installation, will be installed after the successful installation of the system in the Service Group Policy to add the service, in use only need to open.

Note: When you run the installation command must be aware of the path within the CMD problem, the path must be in the MySQL bin path, such as my MySQL extracted to the D:\Program Files (x86) \mysql folder, then the current path to CMD must be d:\ Program Files (x86) \mysql\bin, otherwise an error message occurs when the service is started after the installation completes: System error 2. The system could not find the specified file.

2.2 Start the server

Start the MySQL server, run the command in cmd: net start MySQL.

2.3 Stop the server

After the use is complete, you can stop the operation of the server by command, running the command in CMD:net stop MySQL.

2.4 View Design server name and password

The server you just installed its default name is root, there is no password at this time, you can use the cmd command to set the name and password. The corresponding command is: Mysql-u root. In addition, you can modify the root password by using the UPDATE statement in CMD, as shown in the following code:

1), add a password to root ab12

First enter the directory Mysql\bin in DOS, and then type the following command: mysqladmin-u root-p password ab12 .

Note: because Root does not have a password at the beginning, so the old password of-p can be omitted.

2), and then change the password of the root to djg345:mysqladmin-u root-p ab12 password djg345

2.5 Delete Service: mysqld--remove MySQL

Use the Remove command, followed by the name of the database service you want to delete.

Third, MySQL common commands

3.1 Connection Services

Here is a description of the two connection methods for local and remote connections, respectively.

3.1.1 Local Area Connection

Enter and run the command in cmd: Mysql-u root-p, and then enter the appropriate password. Note that there can be no spaces between the username-U and the username, that is, the-uroot is equally correct, but there must be a space between the password and the-p. If it is just installed MySQL, the default root username is no password, direct input mysql-u root can enter MySQL, MySQL prompts for:mysql>.

3.1.2 Remote Connection

Assuming the IP address of the remote host is: 219.243.79.8, the username is root and the password is 123, run the following command in CMD: mysql-h219.243.79.8-uroot-p 123.

3.1.3 Exit MySQL command: Exit

3.2 Adding new users

3.2.1 Super User

Add a user test1 password to ABC, so that he can log on any host, and all databases have query, insert, modify, delete permissions. First connect to MySQL with root user, and then type the following command:

Grant Select,insert,update,delete on *.* to [email=test1@ '%]test1@ '%[/email] ' identified by ' ABC ';

But the added user is very dangerous and you want someone who knows Test1 's password so that he can log on to your MySQL database on any computer on the Internet and get your data right, see 2.

3.2.2 Native users add a user test2 password to ABC, so that he can only log on the localhost, and the database MyDB query, insert, modify, delete operations (localhost refers to the local host, that is, the MySQL database hosting the host), This allows the user to use a password that knows test2, and he cannot access the database directly from the Internet, only through a Web page on the MySQL host.

Grant Select,insert,update,delete on mydb.* to [Email=test2@localhost]test2@localhost[/email] identified by "ABC";

If you do not want to test2 the password, you can make another command to eliminate the password.

Grant Select,insert,update,delete on mydb.* to [Email=test2@localhost]test2@localhost[/email] identified by "";

3.3 Show Command

Show commands are views that can be used to view some of the list information in MySQL, such as show databases displays the names of all the databases; shows the names of all the tables in a database.

3.4 Operation Database

To enter the relevant database before the operation, you can use the using command, such as: uses TestDB into the database named TestDB, after entering the database, you can manipulate the objects in the database, the corresponding operation commands are SQL statements, DDL, DML, DAL.

3.4.1 View Database Contents

1), view the field information of a table in the database: Desc table name;

2, view the creation statement of the database table: Show create table name; Of course, you can use the same method to view other SQL statements that create content, such as viewing a database creation statement, show create database name.

3.4.2 Modify the column type and name in the table

(1) Modify only the column type

ALTER TABLE database name. Table name Modify column name data type , for example : The sex column of the T_animal table is a Boolean type:

ALTER TABLE T_animal modify sex Boolean NOT null

(2) Modify the column name and column data type at the same time
ALTER TABLE name change column old column name new column name data type , for example: Rename the Sex column of the T_animal table to Ani_sex, and the data type to a Boolean type:

ALTER TABLE t_animal change column sex Ani_sex boolean NOT null

Conclusion

This article on the MySQL configuration and use of methods to do a preliminary summary, MySQL also has a lot of content in the use of accumulation, and the article will also be irregular add new content, mainly for the development process in the update.

The author of the article has been tested, what are the wrong places also please point to learn from each other.

The above is the entire content of this article, I hope to help you learn, but also hope that we support the script