MySQL5.6.17 how to configure the My. ini file for database installation, mysql5.6.17my. ini

MySQL5.6.17 how to configure the My. ini file for database installation, mysql5.6.17my. ini

I recently used the MySql database for 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.

In the my. ini file, you can configure the following running 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

# *** Default location during install, 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 data storage directory of the mysql database, which must be data or // xxx/data

Default-storage-engine = INNODB

# The default storage engine used to create a new table

Socket =/tmp/mysql. sock

SQL-mode = "STRICT_TRANS_TABLES, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION"

# The SQL mode is strict.

# 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

# Maximum number of concurrent connections supported by the mysql server (number of users ). However, a connection is always reserved for the Administrator to log on with super permissions, even if the maximum number of connections is reached. If the configuration is Too small and there are many users, the "Too connector connections" error will often occur.

Query_cache_size = 0

# Query cache size, used to cache SELECT query results. If many SELECT queries that return the same query results and rarely change the table, you can set query_cache_size to be greater than 0, which greatly improves the query efficiency. If the table data changes frequently, do not use this option, which is counterproductive.

# Table_cache = 256

# In Versions later than 5.1.3, this parameter is called table_open_cache, which is used to set the number of table high-speed caches. Since 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 cached tables. If the table has been opened in the cache, it will directly access the table in the cache to speed up the query. If the table is not cached, it will add the current table to the cache for query. Before caching, table_cache is used to limit the maximum number of cached tables. If the number of cached tables does not reach table_cache, a new table is added. If this value is reached, mySQL releases the cache based on the last query time and query rate of the cache table.

Tmp_table_size = 34 M

# Maximum size allowed for each temporary table in the memory. If the temporary Table size exceeds this value, the temporary Table is automatically converted to a Disk-Based Table ).

Thread_cache_size = 8

# Maximum number of cached threads. When the client connection is disconnected, if the total number of client connections is less than this value, the thread that processes the client task is put back into the cache. In the case of high concurrency, if this value is set too small, many threads will be created frequently, the thread creation overhead will increase, and the query efficiency will also decrease. In general, if the application side has a good multi-threaded processing, this parameter will not greatly improve the performance.

# ---------------------------- Begin ------------------------------------------------

Myisam_max_sort_file_size = 100G

# Max size of temporary files allowed during index reconstruction in mysql

Myisam_sort_buffer_size = 68 M

Key_buffer_size = 54 M

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

Read_buffer_size = 64 K

# The buffer size used for full table scan of the MyISAM table. Allocate each thread (on the premise that a full table scan is performed ). During sorting query, MySql first scans the buffer to avoid disk search and increase the query speed. If you need to sort a large amount of data, you can increase the value accordingly. However, MySql will issue this buffer space for each client connection. Therefore, set this value as much as possible to avoid excessive memory overhead.

Read_rnd_buffer_size = 256 K

Sort_buffer_size = 256 K

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

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

# ----------------------------- # InnoDB related parameter begin ---------------------------------------

Innodb_additional_mem_pool_size = 3 M

# InnoDB memory pool size used to store metadata, which generally does not need to be modified

Innodb_flush_log_at_trx_commit = 1

# Transaction-related parameters. If the value is 1, InnoDB writes transaction logs to the disk at each commit (High Disk IO consumption), which guarantees the complete ACID feature. If the value is set to 0, the transaction log is written to the memory log and the memory log is written to the disk once per second. If it is set to 2, transaction logs are written to the memory log at each commit, but the memory log is written to the disk once per second.

Innodb_log_buffer_size = 2 M

# InnoDB log data buffer size. If the buffer is full, the log data in the buffer is written to the disk (flush ). Generally, a disk is written at least once per second, so it is not necessary to set a large value, even for long transactions.

Innodb_buffer_pool_size = 105 M

# InnoDB uses the buffer pool to cache indexes and row data. The larger the value, the less disk I/O. Generally, this value is set to 80% of the physical memory.

Innodb_log_file_size = 53 M

# The size of each InnoDB Transaction log. Generally, it is 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 parameters end ---------------------------------------

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.

Note: when running the installation command, you must pay attention to the path problem in cmd. The path must be in the path of mysql bin. For example, decompress mysql to D: \ Program Files (x86) \ MySql folder, the current cmd path must be D: \ Program Files (x86) \ MySql \ bin, otherwise, the system error 2 is displayed when you start the service after the installation is complete. The system cannot find the specified file.

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 type 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.

2.5 Delete A service: mysqld -- remove MySQL

Use the remove command, followed by the name of the Database Service to be deleted.

Iii. MySql Common commands

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 new users added

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.

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 name data type. For example, you can set the sex column of the t_animal table to the boolean Type:

Alter table t_animal modify sex boolean not null
(2) modify the column name and column data type simultaneously. alter table name change column old column name new column name data type. For example, rename the sex column of t_animal table to ani_sex, 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. 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.

The above is all the content of this article. I hope it will be helpful for your learning and support for helping customers.