MySql green edition configuration and usage _ MySQL

MySql green edition configuration and usage details recently used the MySql database during project development. after reading some articles about MySql, I quickly started using it. Some problems still occur in the process of use, because the green installation-free version of MySql is used, so some problems occur during configuration, this article mainly discusses the configuration and usage of MySql green edition.

I. MySql overview

MySql database is developed by MySql AB in Sweden and is now owned by Oracle. Similar to SQL Server, MySQL is also a relational database management system. In terms of Web applications, MySQL is one of the best RDBMS because it is a lightweight RDBMS.

The latest MySql version is 5.6.17. LATEST: latest.

II. MySql configuration

Since MySql is based on SQL, it includes basic DML, DDL, and DAL. these basic database languages are easy to use, in addition, MySql also encapsulates many database operation commands, which are run in the dossystem. this is different from SQL Server. the MySql environment is stored on the dossystem, use the doscommand. It is a bit similar to java. it can be said that it is also built on a virtual machine and can be used everywhere at a time. To use the MySql command conveniently, you also need to set some prerequisites. the setting method is similar to the Java environment variable. the following method prevents the MySql version from being installed as an example to demonstrate its configuration method.

1. MySql environment configuration

You can use the MySql command anywhere by configuring the MySql decompression path to the system variables.

Note: This is the configured system variable. any third-party command that uses console commands can be added to the system variable. The system variable is a link. when using the command, the system variable is preferentially searched.

2. MySql server configuration

After the system environment variables are configured, you can use all the services provided under MySql bin. Next, you need to install MySQL in the system.

2.1 install the MySql server

Open the decompressed file directory, find the file with the extension name. ini, copy a file named my. ini, and replace the original content with the following content.

[Mysqld] basedir = D:/Program Files (x86)/MySql # set the mysql installation directory datadir = D:/Program Files (x86) /MySql/data # set the data storage directory of the mysql database, which must be data, OR/xxx/data ******** * ********* port = 3306 socket =/tmp/mysql. sockdefault-character-set = gbk # set the character set skip-lockingkey_buffer for the mysql server = bytes = 1Mtable_cache = 4sort_buffer_size = 64Kread_buffer_size = bytes = 2Kthread_stack = 64 K [client] # password = your_passwordport = 3306 socket =/tmp/mysql. sockdefault-character-set = gbk ******* ************

Note: The basedir and datadir Files under [mysqld] must be set to the decompressed path of the file. here, the author places the file under D:/Program Files (x86)/MySql. In addition, the content in the preceding split line is optional. you can set it again when creating a database. we recommend that you do not add it when creating the database because there are many uncertainties.

My. after the INI file is configured, you can install the mysqld service in cmd. run the command: mysqld -- install MySQL -- defaults-file = "D:/Program Files (x86) in cmd) /MySql/my. ini ", in which MySQL is the name of the installation server, you can specify any name. After the installation is complete, the following message is displayed: Service successfully installed, indicating that the installation is successful. after the installation is successful, the Service is added to the system Service group policy. you only need to enable the Service when using the Service.

2.2 Start the server

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

2.3 stop the server

After use, you can run the command to stop the server, and run the command: net stop MySQL in cmd,

2.4 view the name and password of the design server

The default name of the newly installed server is root. there is no password at this time. you can use the cmd command to set the name and password. The command is mysql-u root. In addition, you can use the update statement in cmd to modify the root password. the specific setting method is as follows:

1. add a password ab12 to the root user. first enter the mysql/bin directory in DOS, and then enter the following command: Mysqladmin-u root-p password ab12 .

Note: Because the root account does not have a password at the beginning, the old-p password can be omitted.

2. change the root password to djg345: mysqladmin-u root-p ab12 password djg345.

III. MySql common command 3.1 connection service

The following describes two connection methods: local connection and remote connection.

3.1.1 Local connection

In cmd, enter and run the command mysql-u root-p and enter the password. Note that there can be no space between the user name-u and the user name, that is,-uroot is also correct, but there must be a space between the password and-p. If you have just installed MYSQL, the default root user name has no password. enter mysql-u root to enter MYSQL. the MYSQL prompt is: mysql>.

3.1.2 remote connection

If 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 add new user 3.2.1 superuser

Add a user named "test1" with the password "abc" so that he can log on to any host and have the permission to query, insert, modify, and delete all databases. First, use the root user to connect to MYSQL, and then type the following command:

Grant select, insert, update, delete on *. * to [email = test1 @ "%] test1 @" % [/email] "Identified by" abc ";

However, the added users are very dangerous. if someone knows the password of test1, then he can log on to your mysql database on any computer on the internet and do whatever he wants for your data. See solution 2.

3.2.2 Local User

Add a user named test2 "abc" so that the user can only log on to localhost and query, insert, modify, and delete the database mydb (localhost refers to the local host, that is, the host where the MYSQL database is located. in this way, the user knows the password of test2, and cannot directly access the database from the internet, but can only access the database through the 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 test2 to have a password, you can run another command to remove the password.

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

3.3 show command

The show command is used to view some list information in MySql. for example, show databases displays the names of all databases, and show tables displays the names of all tables in a database.

3.4 database operations

Before the operation, you need to enter the relevant database. you can use the use command, for example, use testdb to enter the database named testdb. after entering the database, you can operate on objects in the database, the corresponding operation commands use SQL statements, DDL, DML, and DAL.

3.4.1 View database content

1. view the field information of a table in the database: desc table name;

2. view the statement for creating a database table: show create table name. of course, you can use the same method to view other SQL statements for creating a database, such as the statement for creating a database, name of the show create database.

Conclusion
This article makes a preliminary summary of the configuration and usage of MySql. MySql still has a lot of content to accumulate in use, and this article will also add new content from time to time, it is mainly updated in the development process. I have tested all the commands in this article. please point out what is wrong with each other.