MySQL database installation, configuration My.ini file

I recently used MySQL database in project development, and after reading some articles about MySQL, I quickly started using it. There are still some problems in the use of the process, because the use of the green version of the installation 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.

First, MySQL overview

MySQL database is developed by the 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, Latest: http://dev.mysql.com/downloads/mysql/, download completed the next installation deployment, the content of the installation of the deployment on the Web to view the next tutorial.

Second, MySQL configuration

Since MySQL is based on SQL, then he contains basic DML, DDL, DAL, these basic database language is easy to use, and MySQL also encapsulates a lot of database operations commands, these commands are running in the DOS system, which is he and SQL Server is different, the MySQL environment is the resume on the DOS system, to use the DOS command. It's a bit like Java, which can be said to be built on a virtual machine and can be used once and for all. To easily use the MySQL command also requires a few prerequisites to set, set the method is similar to the Java environment variables, the following method to avoid the installation version of MySQL as an example to demonstrate its configuration method.

1. mysql Environment configuration

You can use MySQL commands anywhere by configuring the decompression path of MySQL into a system variable.

Note: This is a configured system variable, and any third-party commands that use console commands can be added to system variables, which are a link to the system variables when using commands.

　　

2. mysql server configuration

After configuring the system's environment variables, you can use all the services provided under MySQL's Bin, and then you will need to install MySQL in the system.

2.1 Installing the MySQL server

Open the Unzip file directory, locate the file with the suffix. ini, copy the name to My.ini, and replace the original content with the content below.

1. [mysqld]
2. basedir=d:/program files (x86)/mysql # Set up the installation directory for MYSQL
3. Datadir=d:/program Files (x86)/ Mysql/data # Set the data storage directory for the MySQL database, must be either data, or//xxx/data
4. ************************* Split Line *******************
5. port = 3306
6. socket =/tmp/mysql.sock
7. DEFAULT-CHARACTER-SET=GBK # Set the character set of the MySQL server
8. s Kip-locking
9. key_buffer = 16K
10. max_allowed_packet = 1M
11. table_cache = 4
12. sort_buffer_s ize = 64K
13. read_buffer_size = 256K
14. read_rnd_buffer_size = 256K
15. net_buffer_length = 2 k
Li>thread_stack = 64K
16. [client]
17. #password = Your_password
18. port = 3306
19. socket =/tmp /mysql.sock
20. default-character-set=gbk
21. ************************* Split Line *******************

NOTE:[MYSQLD] The following basedir and DataDir need to be set to the path after the file decompression, where I put the file in D:\Program files (x86) \mysql. In addition, the contents of the above split line are optional and can be reset when the database is created, and it is recommended not to be added at the time of creation because there are many uncertainties.

In the My.ini file, I configure the option to run as follows:

# for advice The change settings

# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

# * * * Don't EDIT this FILE. It ' s a template which'll be copied to the

# * * * default location during install, and would be replaced if you

# * * * * Upgrade to a newer version of MySQL.

[Client]

#password = Your_password

Port = 3306

Socket =/tmp/mysql.sock

Default-character-set=utf-8

[Mysqld]

Port = 3306

Basedir= "C:/Program Files/mysql"

# set up the MySQL installation directory

Datadir= "C:/Program Files/mysql/data"

# Set the data storage directory of the MySQL database, must be either data, or//xxx/data

Default-storage-engine=innodb

# The default storage engine that will be used when creating a new table

Socket =/tmp/mysql.sock

Sql-mode= "Strict_trans_tables,no_auto_create_user,no_engine_substitution"

# SQL mode is strict mode

#default-character-set=utf-8

# set up the MySQL server's character set

#character-set-server=utf-8

# The character set used by the service side defaults to the 8-bit encoded latin1 character set

max_connections=100

# The maximum number of concurrent connections supported by the MySQL server (number of users). However, one of the connections is always reserved for administrators to log on with super privileges, even if the number of connections reaches the maximum limit. If the settings are too small and the user is more, the "Too many connections" error will often occur.

Query_cache_size=0

# query cache size, used to cache select query results. If you have many select queries that return the same query results, and you rarely change the table, you can set query_cache_size greater than 0, which can greatly improve query efficiency. And if the table data changes frequently, do not use this, it will backfire

#table_cache =256

# This parameter is called Table_open_cache in the version after 5.1.3, and is used to set the number of table caches. Because each client connection accesses at least one table, the value of this parameter is related to Max_connections. When a connection accesses a table, MySQL checks the number of tables that are currently cached. If the table is already open in the cache, direct access to the tables in the cache speeds up the query, and if the table is not cached, the current table is added to the cache and queried. Before performing a cache operation, Table_cache is used to limit the maximum number of cache tables: If the currently cached table does not reach Table_cache, the new table will be added, and if this value is reached, MySQL will release the previous cache based on rules such as the last query time of the cache table, query rate, and so on.

tmp_table_size=34m

# The maximum size allowed for each temporary table in memory. If the temporary table size exceeds this value, the temporary table is automatically converted to a disk Based table.

Thread_cache_size=8

# The maximum number of threads to cache. When the client connection is broken, the thread that handles the client task is put back into the cache if the total number of client connections is less than that value. In high concurrency situations, if the value is set too small, many threads are created frequently, the overhead of thread creation becomes larger, and the query efficiency decreases. In general, if there is good multithreading on the application side, this parameter will not improve the performance much.

#------------------------------MyISAM related parameters begin------------------------------------------------

myisam_max_sort_file_size=100g

# The maximum size of temporary files allowed to be used when MySQL rebuilds indexes

Myisam_sort_buffer_size=68m

key_buffer_size=54m

# Key buffer size, used to cache the index block of the MyISAM table. Determines the speed of database index processing (especially indexed reads)

read_buffer_size=64k

# The buffer size used for full table scan of MyISAM table. Allocated for each thread (provided a full table scan is performed). When you sort a query, MySQL scans the buffer first to avoid disk searches, improve query speed, and, if you need to sort large amounts of data, raise the value appropriately. However, MySQL will issue this buffer space for each client connection, so you should set this value as appropriately as possible to avoid excessive memory overhead.

read_rnd_buffer_size=256k

sort_buffer_size=256k

# Connection-level parameters (configured for each thread), 500 threads will consume 500*256k sort_buffer_size.

#------------------------------MyISAM Related parameters end------------------------------------------------

#-------------------------------# InnoDB related parameters begin---------------------------------------

innodb_additional_mem_pool_size=3m

# InnoDB memory pool size for storing metadata information, usually without modification

Innodb_flush_log_at_trx_commit =1

# transaction-related parameters, if the value is 1, InnoDB writes the transaction log to disk at each commit (disk IO consumption is large), which guarantees full acid performance. If set to 0, the transaction log writes to the memory log and the memory log writes to the disk at a frequency of 1 times per second. If set to 2, the transaction log is written to memory log at each commit, but the memory log is written to disk 1 times per second.

Innodb_log_buffer_size=2m

# InnoDB The log data buffer size, if the buffer is full, the log data in the buffer will be written to disk (flush). Since the disk is usually written at least 1 seconds, there is no need to set it too large, even for a long transaction.

innodb_buffer_pool_size=105m

# InnoDB uses a buffer pool to cache indexes and row data. The larger the value is set, the less disk IO. This value is typically set to 80% of physical memory.

innodb_log_file_size=53m

# The size of each INNODB transaction log. Generally set to 25% to 100% of Innodb_buffer_pool_size

Innodb_thread_concurrency=9

# InnoDB Cores Maximum number of concurrent threads

#-------------------------------# InnoDB related parameter end---------------------------------------

After the My.ini file is configured, you can install the Mysqld service in CMD, run the command in cmd: 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. After the installation is complete, the following information is prompted: Service successfully installed, that is, the successful installation, the successful installation will be in the system's service Group Policy to add the service, in use only need to open.

　　

Note: When running the installation command, be sure to pay attention to the path problem in the CMD, the path must be in the path of the MySQL bin, such as my MySQL extracted to the D:\Program Files (x86) \mysql folder, then the cmd current path must be d:\ Program Files (x86) \mysql\bin, or an error message occurs when you start the service after the installation is complete: System error 2. The system cannot find the file specified.

2.2 Starting the server

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

　　

2.3 Stopping the server

After use is complete, you can stop the server from running by command by running the command in cmd: net stop MySQL,

2.4 View Design server name and password

Server 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. Alternatively, 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 directory Mysql\bin under DOS, and then type the following command: mysqladmin-u root-p password ab12.

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

2, then change the root password 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 Service

Here are two ways to connect to local and remote connections, respectively.

3.1.1 Local Area Connection

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

　　

3.1.2 Remote Connection

Assuming the IP address of the remote host is: 219.243.79.8, the user name is root, 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 users

Add a user test1 password to ABC so that he can log on on any host and have access to queries, insertions, modifications, and deletions to all databases. First connect to MySQL with the root user, and then type the following command:

Grant Select,insert,update,delete on * * to [[email protected] '%][email protected] '%[/email] ' identified by ' ABC ';

But the added user is very dangerous, you want to like someone to know test1 password, then he can be on any computer on the Internet to log into your MySQL database and your data can do whatever you like, solution see 2. 3.2.2 Native User

Add a user test2 password to ABC, so that he can only login on localhost, and the database mydb can be queried, inserted, modified, deleted operations (localhost refers to the local host, that is, the MySQL database is located on the host), This allows the user to use a password that knows test2, and he cannot access the database directly from the Internet, but only through a Web page on the MySQL host.

Grant Select,insert,update,delete on mydb.* to [[E-mail Protected]][email protected][/email] identified by "ABC";

If you do not want to test2 have a password, you can call another command to erase the password.

Grant Select,insert,update,delete on mydb.* to [[E-mail Protected]][email protected][/email] identified by "";

3.3 Show Command

The show command is meant to be viewed and can be used to view some of the list information in MySQL, such as: show databases displays the names of all the databases; Show tables displays all the table names in a database.

3.4 Operational databases

Before the operation to enter the relevant database, you can use command, such as: using TestDB into the database named TestDB, after entering the database can be both in the database operation of the object, the corresponding operation command using SQL statements, DDL, DML, DAL.

3.4.1 Viewing the contents of a database

1. View field information for a table in the database: Desc table name;

　　

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

　　

3.4.2 Modifying column types and names in a table

(1) Modify column type only

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

1. ALTER TABLE T_animal modify sex Boolean NOT null

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

1. ALTER TABLE t_animal change column sex Ani_sex boolean NOT null

Conclusion

This article has made a preliminary summary of the configuration and use of MySQL, MySQL has a lot of content in the use of slowly accumulated, and the article will also add new content, mainly for the development process of the situation and update. The article commands the author have carried on the test, there is what wrong place also please point out to learn from each other.

Technology sharing: www.kaige123.com

MySQL database installation, configuration My.ini file