How to configure the My.ini file when installing MySQL5.6.17 database

I recently used the MySql database when developing a project. After reading some articles about MySql, I quickly got started using it. There are still some problems in the process of use. Because the green free installation version of MySql is used, there are some problems during configuration. This article mainly discusses the configuration and use of the green version of MySql.

1. MySQL Overview

The MySql database was developed by the Swedish company MySql AB, which is now acquired by Oracle and owned by Oracle. Similar to SQL Server, it is also a database management system based on a relational database. In terms of Web applications, MySQL is one of the best RDBMS because it is a lightweight RDBMS.

The latest version of MySql is 5.6.17. The latest download address is: http://dev.mysql.com/downloads/mysql/. After the download is complete, the next step is to install and deploy it. For information about installation and deployment, just check the tutorial online.

2. MySql Configuration

Since MySql is based on SQL, it includes basic DML, DDL, and DAL. These basic database languages ​​are very easy to use. In addition, MySql also encapsulates many database operation commands, which are run in the DOS system. This is the difference between it and SQL Server. The MySql environment is based on the DOS system and requires the use of DOS commands. It is somewhat similar to Java. It can be said that it is also built on a virtual machine, which can be built once and used everywhere. In order to use the MySql command conveniently, some prerequisites need to be set up. The setting method is similar to the Java environment variables. The following method uses the non-installed version of MySql as an example to demonstrate its configuration method.

1. MySql environment configuration

By configuring the MySQL decompression path to the system variable, you can use the MySQL command anywhere.

Note: This is a configured system variable. Any third-party command that uses console commands can be added to the system variable. The system variable acts as a link and will be searched first when using commands.

2. MySQL server configuration

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

2.1 Install MySQL Server

Open the unzipped file directory, find the file with the suffix .ini, copy a copy and rename it to my.ini, and replace the original content with the following content.

[mysqld]
basedir=D:/Program Files (x86)/MySql # Set the installation directory of MySQL datadir=D:/Program Files (x86)/MySql/data # Set the storage directory of MySQL database data, which must be data, or //xxx/data
*************************Dividing line*******************
port = 3306
socket = /tmp/mysql.sock
default-character-set=gbk # Set the character set skip-locking of the mysql server
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
*************************Dividing line*******************

Note: [mysqld] under the basedir and datadir need to be set to the path after the file is unzipped. Here, the author put the file in D:\Program Files (x86)\MySql. In addition, the content within the dividing line above is optional and can be reset when creating the database. It is recommended not to add it when creating it because there will be many uncertain factors.

In the my.ini file, I configured the following executable options:

# For advice on how to change settings please see

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

# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the

# *** default location during installation, and will 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 the installation directory of mysql

datadir="C:/Program Files/Mysql/data"

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

default-storage-engine=INNODB

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

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 the character set of the mysql server

#character-set-server=utf-8

# The default character set used by the server is the 8-bit latin1 character set

max_connections=100

# The maximum number of concurrent connections (number of users) supported by the MySQL server. However, one connection is always reserved for the administrator to log in with super privileges, even if the maximum number of connections is reached. If it is set too small and there are many users, the "Too many connections" error will often occur.

query_cache_size=0

# Query cache size, used to cache SELECT query results. If there are many SELECT queries that return the same query results and the table is rarely changed, setting query_cache_size greater than 0 can greatly improve query efficiency. If the table data changes frequently, don't use this, it will be counterproductive

#table_cache=256

# This parameter is called table_open_cache in versions 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 currently cached tables. If the table has been opened in the cache, the table in the cache will be accessed directly to speed up the query; if the table is not cached, the current table will be added to the cache and queried. Before performing a cache operation, table_cache is used to limit the maximum number of cached tables: if the currently cached tables do not reach table_cache, new tables will be added; if this value has been reached, MySQL will release the previous cache based on the last query time, query rate and other rules of the cached table.

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 will be automatically converted to a disk-based table.

thread_cache_size=8

# Maximum number of threads to cache. When a client connection is disconnected, if the total number of client connections is less than this value, the thread processing the client task is put back into the cache. In high concurrency situations, if this value is set too small, many threads will be created frequently, the thread creation overhead will increase, and query efficiency will decrease. Generally speaking, if there is good multi-threading processing on the application side, this parameter will not improve performance much.

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

myisam_max_sort_file_size=100G

# The maximum size of temporary files allowed when mysql rebuilds the index

myisam_sort_buffer_size=68M

key_buffer_size=54M

# Key Buffer size, used to cache index blocks of MyISAM tables. Determines the speed of database index processing (especially index reads)

read_buffer_size=64K

# The buffer size used for full table scan of MyISAM table. Allocation is done per thread (assuming a full table scan is performed). When performing a sort query, MySql will first scan the buffer to avoid disk searches and increase query speed. If a large amount of data needs to be sorted, this value can be appropriately increased. However, MySql will allocate this buffer space for each client connection, so you should try to set this value appropriately 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 of sort_buffer_size.

#------------------------------MyISAM related parametersend------------------------------------------------

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

innodb_additional_mem_pool_size=3M

# The size of the memory pool used by InnoDB to store metadata information. Generally, no modification is required.

innodb_flush_log_at_trx_commit = 1

# Transaction related parameters. If the value is 1, InnoDB will write the transaction log to disk at each commit (which consumes more disk IO), thus ensuring complete ACID characteristics. If it is set to 0, it means that the frequency of writing transaction logs to memory logs and memory logs to disk is 1 time per second. If it is set to 2, it means that the transaction log is written to the memory log at each commit, but the frequency of writing the memory log to disk is 1 time/second.

innodb_log_buffer_size=2M

# The size of the InnoDB log data buffer. If the buffer is full, the log data in the buffer will be written to the disk (flush). Since the disk is generally written at least once a second, there is no need to set it too large, even for long transactions.

innodb_buffer_pool_size=105M

# InnoDB uses a buffer pool to cache indexes and row data. The larger the value is, the less disk IO is required. This value is generally 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

# Maximum number of concurrent threads in the InnoDB kernel

#-------------------------------# InnoDB related parametersend ---------------------------------------

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 and you can specify any name. After the installation is complete, the following message will be displayed: Service successfully installed, indicating that the installation is successful. After successful installation, the service will be added to the system's service group policy. When using it, you only need to enable it.

Note: When running the installation command, you must pay attention to the path problem in cmd. The path must be in the path where mysql's bin is located. For example, if I unzip mysql to the D:\Program Files(x86)\MySql folder, then the current path of cmd must be D:\Program Files(x86)\MySql\bin. Otherwise, an error message will appear when you start the service after the installation is complete: System error 2. The system cannot find the file specified.

2.2 Start the server

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

2.3 Stop the server

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

2.4 View the design server name and password

The default name of the newly installed server is root, and there is no password. You can set the name and password through the cmd command. The corresponding 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 shown in the following code:

1. Add a password ab12 to root

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

Note: Because root has no password at the beginning, the -p old password item can be omitted.

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

2.5 Remove the service: mysqld --remove MySQL

Use the remove command followed by the name of the database service to be removed.

3. Common MySQL commands

3.1 Connection Service

There are two connection methods introduced here: local connection and remote connection.

3.1.1 Local Connection

Enter and run the command in cmd: mysql -u root -p, and then enter the corresponding password. Note that there can be no space between -u and the username, i.e. -uroot is also correct, but there must be a space between the password and -p. If MYSQL has just been installed, the default root username has no password. You can enter MYSQL by directly entering mysql -u root. The prompt of MYSQL is: mysql>.

3.1.2 Remote Connection

Assume that the IP address of the remote host is: 219.243.79.8, the user name is root, and the password is 123, then 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 with password abc, so that he can log in on any host and have the permissions to query, insert, modify, and delete all databases. First, connect to MYSQL as the root user, and then type the following command:

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

But adding users is very dangerous. If someone knows the password of test1, he can log in to your MySQL database from any computer on the Internet and do whatever he wants with your data. See 2 for the solution. 3.2.2 Local User

Add a user test2 with password abc, so that he can only log in on 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, even if the user knows the password of test2, he cannot access the database directly from the Internet and can only access it 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 don't want test2 to have a password, you can type another command to delete 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 means to view, and can be used to view some list information in MySql, such as: show databases displays the names of all databases; show tables displays the names of all tables in a database.

3.4 Operating Database

Before the operation, you need to enter the relevant database. You can use the use command, such as: use testdb to enter the database named testdb. After entering the database, you can operate on the 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 creation statement of the database table: show create table table name; of course, the same method can also be used to view the SQL statements for creating other content, such as viewing the creation statement of the database, show create database database name.

3.4.2 Modify the column type and name in the table

(1) Only modify the column type

alter table database name.table name modify column column name data type, for example: change the sex column of the t_animal table to 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 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 change the data type to boolean:

alter table t_animal change column sex ani_sex boolean not null
Conclusion

This article makes a preliminary summary of the configuration and usage of MySql. There is still a lot of content about MySql that is slowly accumulated during use, and this article will add new content from time to time, mainly updated according to the situation in the development process. The author has tested all the commands in the article. If there are any errors, please point them out so that we can learn from each other.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.