MySQL environment variable settings (windows environment)
1. For command control, it is very convenient to operate under the cmd command line, but sometimes you need to execute commands like mysql directly on the command line, and an error message like "mysql is not an internal or external command" will appear. The reason is that mysql is actually an exe file stored in the myql installation path/bin. Therefore, the installation path of mysql needs to be configured into the path environment variable, so you don't need to cd to the mysql path every time in the future;
2. After the setup is completed, let's try again -cmd-->mysql
MySQL has been connected to it, and next we can use dml and sql languages to create our own database;
3. First, create a database name test_user;
Created successfully!
4. Create a data table and use a script.
Note that before creating a table, you must use databasename;
shell> mysqld --help
This command generates a table of all mysqld options and configurable variables. The output includes the default value 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 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 the value displayed in K bytes or megabytes. For example, 16M indicates 16 megabytes. The case of the suffix letters does not matter; 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 and column information).
back_log
Requires the number of connections that MySQL can have. 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 short) to check the connection and starts a new thread. The back_log value indicates how many requests can be stored on the stack in a short time before MySQL temporarily stops answering a new request. Only if you expect 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 the incoming TCP/IP connection. Your operating system has its own limitations on this queue size. The man page of Unix listen(2) system calls should have more details. Check your OS documentation to find the maximum value of this variable. Trying to set the back_log to be higher than your operating system limit will be invalid.
connect_timeout
The number of seconds the mysqld server is waiting for a connection message before responding with Bad handshake.
delayed_insert_timeout
An Insert DELAYED thread should wait for the Insert statement before termination.
delayed_insert_limit
After inserting the delayed_insert_limit line, the Insert DELAYED processor will check if any Select statements are not executed. If so, execute the allow these statements before proceeding.
delayed_queue_size
How large a queue (in terms of row count) should be allocated for processing Insert DELAYED. If the queue is full, any customer who conducts Insert DELAYED will wait until the queue has room again.
flush_time
If this is set to a non-zero value, then every flush_time second all tables will be closed (to free resources and sync to disk).
interactive_timeout
The number of seconds the server waits for action on an interactive connection before shutting it down. An interactive client is defined as a client that uses the CLIENT_INTERACTIVE option for mysql_real_connect(). You can also see wait_timeout.
join_buffer_size
The buffer size for all joins (not indexed joins). Buffer allocates a buffer to each of the two tables once. When increasing the index is impossible, increasing the value can result in a faster all connection. (The best way to get a quick link is usually 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 indexing blocks, increasing it can get better processed indexes (for all reads and multiple rewrites) to as much as you can afford. If you make it too large, the system will start changing pages and it's really slowed down. 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 lines, 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 counter will be incremented.
max_allowed_packet
Maximum size of a bag。 The message buffer is initialized to net_buffer_length bytes, but can be added to max_allowed_packet bytes when needed. By default, if this value is too small, it will catch large (possibly wrong) packets. If you are using a large BLOB column, you have to increase the value. It should be as large as the largest BLOB you want to use.
max_connections
The number of customers allowed at the same time. Increasing this value increases the number of file descriptors required by mysqld. See the comments on file descriptor restrictions below. See 18.2.4 Too many connections error.
max_connect_errors
If there are more than that number of connections interrupted from one host, this host blocks further connections. You can use the FLUSH HOSTS command to clear a host.
max_delayed_threads
Do not start threads with more than this number to process the Insert DELAYED statement. If you try to insert data into a new table after all Insert DELAYED threads, the row will be inserted, just as the DELAYED attribute is not specified.
max_join_size
Joints that may be reading more than max_join_size records will return an error. If your user wants to execute a join that does not have a Where clause, takes a long time and returns a million rows, set it.
max_sort_length
The number of bytes used when sorting BLOB or TEXT values (only header max_sort_length bytes per value is used; the rest is ignored).
max_tmp_tables
(This choice does nothing yet). A client can keep the maximum number of temporary tables open at the same time.
net_buffer_length
The communication buffer is reset to this size between queries. Usually this should not be changed, but if you have very little memory, you can set it to the query the expected size. (i.e., the expected length of the SQL statement issued by the client. If the statement exceeds this length, the buffer is automatically expanded until max_allowed_packet bytes.)
record_buffer
Each thread performs a sequential scan allocates a buffer of this size to 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 does MySQL store temporary files?
table_cache
The number of tables to open 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 the comments on the file descriptor limit below. For information on how table cache works, see 10.2.4 How MySQL opens and closes tables.
tmp_table_size
If a temporary table exceeds this size, MySQL generates an error in the form of The table tbl_name is full. If you do a lot of advanced GROUP BY queries, increase the tmp_table_size value.
thread_stack
The stack size of each thread. Many limitations detected by crash-me tests depend on this value. The default team's general operation is large enough. See 10.8 Use your own benchmark.
wait_timeout
The number of seconds the server waits for action on a connection before closing it. Interactive_timeout can also be seen.
MySQL is a very scalable algorithm, so you can usually run with very little memory or give MySQL more storage for better performance.
If you have a lot of memory and a lot of tables and have a medium number of customers who 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, a "swap problem" can occur unless mysqld has been configured to use very little memory per connection. Of course, if you have enough memory for all connections, mysqld executes better.
Note that if you change one option of mysqld, it actually only keeps on that example of the server.
To understand the effect of a parameter change, do this:
shell> mysqld -O key_buffer=32m --help
The --help option is guaranteed to be the last one; otherwise, the effect of any options listed after it on the command line will not be reflected in the output.