MySQL environment variable settings (Windows environment)
1, for Command control, in the CMD command under the operation is very convenient, but sometimes need to directly in the command line of the bed empty to execute MySQL such commands, will appear "MySQL is not internal or external command" such as the error hint, because MySQL is actually an exe file, stored in the MYQL installation path/ Bin, you need to configure the MySQL installation path to the PATH environment variable so that you don't have to go to the MySQL path every time.
2. We'll try-cmd-->mysql after the setup is complete.
Already connected to MySQL, we can then use DML, SQL language to create our own database;
3, first, to create a database name for Test_user;
Create success!
4. Create a data table and use a script.
Note that you must use databasename before creating the table;
You can use this command to get the MYSQLD server default buffer size:
Shell> mysqld--help
This command generates a table of all mysqld options and configurable variables. The output includes default values and looks like something like this:
Possible Variables for option--set-variable (-O) are:
Back_log??????? Current Value:5
Connect_timeout??? Current Value:5
Delayed_insert_timeout?current value:300
Delayed_insert_limit?current value:100
Delayed_queue_size?? Current value:1000
Flush_time?????? Current value:0
Interactive_timeout? Current value:28800
Join_buffer_size??? Current value:131072
Key_buffer_size??? Current value:1048540
Lower_case_table_names?current value:0
Long_query_time??? Current Value:10
Max_allowed_packet?? Current value:1048576
Max_connections??? Current value:100
Max_connect_errors?? Current Value:10
Max_delayed_threads? Current VALUE:20
Max_heap_table_size? Current value:16777216
Max_join_size???? Current value:4294967295
Max_sort_length??? Current value:1024
Max_tmp_tables???? Current value:32
Max_write_lock_count?current value:4294967295
Net_buffer_length?? Current value:16384
Query_buffer_size?? Current value:0
Record_buffer???? Current value:131072
Sort_buffer????? Current value:2097116
Table_cache????? Current value:64
Thread_concurrency?? Current Value:10
Tmp_table_size???? Current value:1048576
Thread_stack????? Current value:131072
Wait_timeout????? Current value:28800
If there is a mysqld server running, by executing this command, you can see the value of the variable it actually uses:
Shell> mysqladmin variables
Each option is described below. The value of the buffer size, length, and stack size is given in bytes, and you can use the suffix "k" or "M" to indicate that the value is displayed in K-byte or megabytes. For example, 16M points out 16 megabytes. The case of the suffix letter is not related; 16M and 16m are the same.
You can also use the command show status to see some statistics from a running server. See 7.21 Show syntax (get table, column information).
Back_log
Require MySQL to have the number of connections. This works when the main MySQL thread gets a lot of connection requests in a very short time, and then the main thread takes some time (albeit very short) to check the connection and start a new thread. The Back_log value indicates how many requests can be on the stack within a short time before MySQL temporarily stops answering the new request. Only if you expect to have a lot of connections in a short time, you need to increase it, in other words, this value is the size of the listening queue for incoming TCP/IP connections. Your operating system has its own limits on the size of this queue. Unix Listen (2) system calls to the hand album should have more details. Check your OS documentation to find the maximum value for this variable. Attempting to set Back_log above your operating system will be ineffective.
Connect_timeout
The number of seconds the MYSQLD server is waiting for a connection message before it answers with bad handshake (poor handshake).
Delayed_insert_timeout
An insert delayed thread should wait for the INSERT statement time before terminating.
Delayed_insert_limit
After inserting the Delayed_insert_limit line, the insert delayed processor checks to see if any SELECT statements have not been executed. If so, allow these statements to be executed before continuing.
Delayed_queue_size
How many queues (in rows) should be allocated for processing insert delayed. If the queue is full, any customer who inserts delayed will wait until there is room in the queue.
Flush_time
If this is set to a value other than 0, all tables will be closed for every flush_time second (to free resources and sync to disk).
Interactive_timeout
The number of seconds the server waits for action on an interactive connection before closing it. An interactive customer is defined as a customer who uses the client_interactive option for Mysql_real_connect (). Also visible wait_timeout.
Join_buffer_size
The buffer size (not an indexed join) for the full join (join). The buffer allocates a buffer to each of the 2 tables, and when the index is added it is not possible to increase the value to get a faster full join. (Often the best way to get a quick connection is to add an index.) ）
Key_buffer_size
The index block is buffered and is shared by all threads. Key_buffer_size is the size of the buffer used for the index block, adding that it can be better processed by indexing (for all read and multiple writes), to the extent that you can afford that much. If you make it too big, the system will start to change pages and it really slows down. Remember that since MySQL does not cache read data, you will have to leave some room for the OS file system cache. To get more speed when writing multiple rows, use lock TABLES. See 7.24LOCK tables/unlock TABLES syntax.
Long_query_time
If a query takes more time than it (in seconds), the Slow_queries register is incremented.
Max_allowed_packet
The maximum size of a package. The message buffer is initialized to net_buffer_length bytes, but can be incremented to max_allowed_packet bytes as needed. By default, this value is too small to catch large (possibly wrong) packages. If you are using a large BLOB column, you must increase the value. It should be as big as the largest blob you want to use.
Max_connections
The number of simultaneous customers allowed. Increase this value to increase the number of file descriptors required by mysqld. See below for comments with file descriptor restrictions. See 18.2.4 Too many connections error.
Max_connect_errors
If there are more than that number of disconnected connections from one host, this host blocks further connections. You can use flush hosts command to dredge a host.
Max_delayed_threads
Do not start a thread with more than this number to handle the insert delayed statement. If you attempt to insert data into a new table after all insert delayed threads are used, the row is inserted, just as the delayed attribute is not specified.
Max_join_size
A join that may be reading more than Max_join_size records will return an error. If your users want to perform a join that does not have a WHERE clause, takes a long time and returns millions of rows, set it up.
Max_sort_length
The number of bytes to use when ordering a blob or text value (each value is used only by the header max_sort_length byte; the rest is ignored).
Max_tmp_tables
(the option is not doing anything yet). The maximum number of temporary tables that a customer can keep open at the same time.
Net_buffer_length
The communication buffer is reset between queries to that size. Usually this should not be changed, but if you have very little memory, you can set it to the size of the query you want. (that is, the desired length of the SQL statement that the customer emits.) If the statement exceeds this length, the buffer is automatically enlarged until the Max_allowed_packet byte. ）
Record_buffer
Each thread that carries out a sequential scan assigns a buffer of this size to each table it scans. If you do a lot of sequential scans, you might want to add that value.
Sort_buffer
Each thread that needs to be sorted allocates a buffer of that size. Increase this value to accelerate the order by or group by action. See where 18.5 MySQL stores temporary files.
Table_cache
The number of tables opened for all threads. Increasing this value can increase the number of file descriptors required by mysqld. MySQL requires 2 file descriptors for each unique open table, see below for comments that face file descriptor restrictions. For information about how the table cache works, see how 10.2.4 MySQL opens and closes the table.
Tmp_table_size
If a temporary table exceeds that size, MySQL produces a full form of the table Tbl_name error, and if you do many advanced group by queries, increase the tmp_table_size value.
Thread_stack
The stack size of each thread. Many of the limitations detected by the CRASH-ME test depend on this value. The default team's general operation is large enough. See 10.8 using your own benchmark.
Wait_timeout
The number of seconds the server waits for action on a connection before closing it. Also visible interactive_timeout.
MySQL uses a scalable algorithm, so you can usually run with little memory or save MySQL for better performance.
If you have a lot of memory and a lot of tables and have a medium number of customers, want maximum performance, you should have something like this:
shell> safe_mysqld-o key_buffer=16m-o table_cache=128 \
????? -O sort_buffer=4m-o record_buffer=1m &
If you have less memory and a lot of connections, use something like this:
shell> safe_mysqld-o key_buffer=512k-o sort_buffer=100k \
????? -O record_buffer=100k &
or even:
shell> safe_mysqld-o key_buffer=512k-o sort_buffer=16k \
????? -O table_cache=32-o record_buffer=8k-o net_buffer=1k &
If there are many connections, "swap issues" can occur unless mysqld has been configured to use very little memory for each connection. Of course, if you have enough memory for all the connections, MYSQLD performs better.
Note that if you change the MYSQLD option, it is actually only maintained for that example of the server.
To understand the effect of a parameter change, do this:
shell> Mysqld-o key_buffer=32m--help
Ensure that the--HELP option is the last one; otherwise, the effect of any options listed after it on the command line will not be reflected in the output.
