MySQL environment variable settings (Windows environment)
1, for Command control, in the CMD command downstream operation is very convenient, but sometimes need to directly in the command line bed empty execution mysql Such a command, there will be "MySQL is not internal or external command" such error prompt, because MySQL is actually an exe file, stored in the MYQL installation path/ Bin, so you need to configure the MySQL installation path to the PATH environment variable, so that you do not have to CD to the MySQL path every time;
2. We'll try again after the setup is complete-cmd-->mysql
Already connected to MySQL, then we can use DML, SQL language to create our own database;
3, first, to create a database named Test_user;
Create success!
4. Create a data table and use a script.
Note that you must use databasename before creating a 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 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 that it actually uses:
Shell> mysqladmin variables
Each option is described below. For buffer size, length, and stack size values given in bytes, you can use the suffix "k" or "M" to indicate that values are displayed in either K-bytes or megabytes. For example, 16M indicates 16 gigabytes. 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
The number of connections required for MySQL to be available. When the primary MySQL thread gets very many connection requests in a very short time, this works, 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 present in the stack for a short period of time before MySQL temporarily stops answering a new request. Only if you expect to have a lot of connections in a short period of time, you need to increase it, in other words, the size of the listening queue for incoming TCP/IP connections. Your operating system has its own limits on this queue size. Unix Listen (2) system calls to the manual pages should have more details. Check your OS documentation to find out the maximum value for this variable. Attempting to set a limit of back_log above your operating system will be invalid.
Connect_timeout
The number of seconds that the MYSQLD server is waiting for a connection message before answering with bad handshake (poor handshake).
Delayed_insert_timeout
The time at which an insert delayed thread should wait for an INSERT statement before terminating.
Delayed_insert_limit
After inserting the Delayed_insert_limit line, the insert delayed processor checks to see if any of the SELECT statements have not been executed. If so, execute before continuing to allow these statements.
Delayed_queue_size
The number of queues (in rows) that should be allocated for processing the insert delayed. If the queue is full, any customer who makes an insert delayed waits until the queue has space again.
Flush_time
If this is set to a value other than 0, then all tables will be closed every flush_time seconds (to free up resources and sync to disk).
Interactive_timeout
The number of seconds the server waits for an action on an interactive connection before shutting it down. 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 size of the buffer used for all joins (not an indexed junction). The buffer allocates a buffer to each of the 2 tables, and when the index is not possible, increase the value to get a faster full join. (usually the best way to get a quick coupling is to increase the index.) ）
Key_buffer_size
The index block is buffered and shared by all threads. Key_buffer_size is the buffer size used for the index block, increasing the index (for all reads and multiple writes) that can be better processed, to the extent that you can afford it. If you make it too big, the system will start to change pages and really become slow. Remember that since MySQL does not cache read data, you will have to leave some space 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 increased to max_allowed_packet bytes when needed. By default, this value is too small to catch large (possibly wrong) packets. If you are using a large BLOB column, you must increase the value. It should be as large 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 the mysqld. See the comment that faces the file descriptor limit. See 18.2.4 Too many connections error.
Max_connect_errors
If there is more than that number of disconnected connections from one host, this host blocks further connections. You can use the flush hosts command to unblock a host computer.
Max_delayed_threads
Do not start a thread with more than this number to handle the insert delayed statement. If you try to insert data into a new table after all insert delayed threads are used, the row is inserted, as if the delayed property was not specified.
Max_join_size
A junction that may be read into more than Max_join_size records will return an error. If your user wants to execute a junction 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 used when sorting the Blob or text value (only the first max_sort_length bytes per value are used; the rest is ignored).
Max_tmp_tables
(The choice 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 buffers are reset to that size between queries. Normally this should not be changed, but if you have very little memory, you can set it to the desired size of the query. (That is, the length that the client expects from the SQL statement.) If the statement exceeds this length, the buffer is automatically enlarged until Max_allowed_packet bytes. ）
Record_buffer
Each thread that makes a sequential scan allocates a buffer of that size for each table it scans. If you do a lot of sequential scans, you may want to increase the 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 operation. See 18.5 where 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 the mysqld. MySQL requires 2 file descriptors for each unique open table, as shown below for comments on file descriptor limitations. For information on how table caching works, see 10.2.4 MySQL How to open and close tables.
Tmp_table_size
If a temporary table exceeds that size, MySQL produces an error in the form of the table tbl_name is, 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 general operation is large enough. See 10.8 using your own benchmark.
Wait_timeout
The number of seconds the server waits for an action on a connection before shutting it down. Also visible interactive_timeout.
MySQL is a very scalable algorithm, so you can usually run with very little memory or give MySQL more to be saved for better performance.
If you have a lot of memory and a lot of tables and have a medium number of customers that 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, the "swap problem" may occur unless MYSQLD has been configured to use very little memory for each connection. Of course, if you have enough memory for all connections, MYSQLD performs better.
Note that if you change an option for mysqld, it is actually only for the server that the example remains.
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.
The above describes the Java environment variable MySQL environment variable setting method, including the contents of the Java environment variables, I hope the PHP tutorial interested in a friend helpful.
