In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-01-18 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)05/31 Report--
This article mainly introduces how to manage and configure MySQL. It is very detailed and has a certain reference value. Friends who are interested must finish reading it.
Mysqladmin [OPTIONS] command command....
Here are some useful commands:
Status returns server status information
Password modifies user password
Shutdown shuts down the MySQL server
Reload overload MySQL Authorization Table
Refresh resets all caches and logs
Variables returns the values of all server variables
Version returns the server version
Processlist returns a list of all processes on the server
Kill cancels a server process
Ping tests whether the server is active
[@ more@]
1. Start and shut down the server
In a Windows environment, MySQL can be started as a service in the background:
D:Program FilesMySQLMySQL Server 5.1bin > net start mysql
The MySQL service is starting.
The MySQL service has started successfully.
D:Program FilesMySQLMySQL Server 5.1bin > mysqladmin-u root-p ping
Enter password: *
Mysqld is alive
To shut down the server, you can use mysqladmin's shutdown command:
D:Program FilesMySQLMySQL Server 5.1bin > mysqladmin-u root-p shutdown
Enter password: *
D:Program FilesMySQLMySQL Server 5.1bin > mysqladmin-u root-p ping
Enter password: *
Mysqladmin: connect to server at 'localhost' failed
Error: 'Can't connect to MySQL server on' localhost' (10061)'
Check that mysqld is running on localhost and that the port is 3306.
You can check this by doing 'telnet localhost 3306'
two。 Check MySQL server status
Use the status command of mysqladmin to view the current status of the server:
D:Program FilesMySQLMySQL Server 5.1bin > mysqladmin-u root-p status
Enter password: *
Uptime: 770 Threads: 1 Questions: 8 Slow queries: 0 Opens: 15 Flush tables: 1 Open tables: 8
Queries per second avg: 0.10
The meaning of each column of the output is as follows:
Number of seconds the Uptime MySQL server has been running
Number of Threads active threads (customers)
The number of customer questions that Questions started from mysqld
The number of queries whose Slow queries has exceeded long_query_time seconds
How many tables has Opens mysqld opened?
Number of Flush tables flush..., refresh and reload commands
Number of tables now opened by Open tables
The version command, on the other hand, outputs more detailed information such as the server version:
D:Program FilesMySQLMySQL Server 5.1bin > mysqladmin-u root-p version
Enter password: *
Mysqladmin Ver 8.42 Distrib 5.1.34, for Win32 on ia32
Copyright 2000-2008 MySQL AB, 2008 Sun Microsystems, Inc.
This software comes with ABSOLUTELY NO WARRANTY. This is free software
And you are welcome to modify and redistribute it under the GPL license
Server version 5.1.34-community
Protocol version 10
Connection localhost via TCP/IP
TCP port 3306
Uptime: 17 min 42 sec
Threads: 1 Questions: 9 Slow queries: 0 Opens: 15 Flush tables: 1 Open tables: 8 Queries per s
Econd avg: 0.8
You can also use the extended-status command or the show status command of the mysql command line to get extended status information.
3. Manage MySQL client processes
Using the processlist command of mysqladmin, or the show processlist command of the mysql command line, you can get a list of all client processes connected to the server:
D:Program FilesMySQLMySQL Server 5.1bin > mysqladmin-u root-p processlist
Enter password: *
+-+-
| | Id | User | Host | db | Command | Time | State | Info |
+-+-
| | 6 | root | localhost:1612 | | Query | 0 | | show processlist |
+-+-
You can then abort the process with the kill command of mysqladmin or the kill command of the mysql command line.
D:Program FilesMySQLMySQL Server 5.1bin > mysqladmin-u root-p processlist
Enter password: *
+-+-
| | Id | User | Host | db | Command | Time | State | Info |
+-+-
| | 7 | root | localhost:1616 | | Sleep | 7 |
| | 8 | root | localhost:1617 | | Query | 0 | | show processlist |
+-+-
D:Program FilesMySQLMySQL Server 5.1bin > mysqladmin-u root-p kill 7
Enter password: *
D:Program FilesMySQLMySQL Server 5.1bin >
4. Modify server configuration
1) use options file
Name and location of the options file:
Under Windows, the options file name is my.cnf or my.ini. The location is the% WINDIR%;C:;MySQL installation directory, such as D:Program FilesMySQLMySQL Server 5.1;, with the file specified by-- defaults-extra-file=path.
Under Unix, the options file is named my.cnf. The location is, / etc;$MYSQL_HOME/; uses the file specified by-- defaults-extra-file=path; ~ / .my.cnf.
For more information, please refer to the MySQL reference manual.
Various programs in MySQL can read options from the same options file, which is divided into different groups, such as [client], [mysql], [mysqld], and so on. For example, make the following modifications:
[client]
Port=3306
# Added by yuegao Jul 7, 2009
User=root
Password=pccw
Then when the client connects to the server, you can use the default user name and password:
D:Program FilesMySQLMySQL Server 5.1bin > mysql
Welcome to the MySQL monitor. Commands end with; or g.
Your MySQL connection id is 13
Server version: 5.1.34-community MySQL Community Server (GPL)
Type 'help;' or' h' for help. Type 'c'to clear the current input statement.
Mysql > select user ()
+-+
| | user () |
+-+
| | root@localhost |
+-+
1 row in set (0.03 sec)
Mysql > exit
Bye
D:Program FilesMySQLMySQL Server 5.1bin > mysqladmin status
Uptime: 3044 Threads: 1 Questions: 24 Slow queries: 0 Opens: 15 Flush tables: 1 Open tables:
Queries per second avg: 0.7
Similarly, you can modify the configuration of the server process by setting the options in [mysql] and [mysqld].
Here are some common options for configuring MySQL:
Ansi uses the standard (ANSI) SQL syntax instead of the MySQL syntax.
The path to the basedir MySQL installation directory. Usually all paths are resolved according to that path.
The path to the datadir data directory.
Debug if MySQL is configured with-- with-debug, you can use this option to get a trace file that tracks what mysqld is doing.
Default-character-set sets the default character set.
Default-table-type sets the default type of the table.
Flush writes changes to the hard disk after executing the SQL statement.
The SQL statement is read from this file when init-file starts. Each statement must be on the same line and should not include comments.
Language gives client-side error messages in the given language.
Log specifies the log file that records MySQL information.
Log-error specifies the log file that records the error message.
Log-warnings logs warnings in a log file that logs error messages.
Log-slow-queries logs all queries that take more than long_query_time seconds to execute in the log file.
Log-bin specifies the binary log file. Record all queries that change the data in this file.
The port number that port uses when listening for TCP/IP connections.
Skip-bdb disables the BDB storage engine. This saves memory and may speed up some operations.
Skip-innodb disables the InnoDB storage engine. This saves memory and may speed up some operations.
Skip-grant-tables this option prevents the server from using the permissions system. This permission allows users who access the server to have unrestricted access to all databases.
Skip-networking does not frame to listen to TCP/IP connections. Mysqld interoperability must be done through named pipes or shared memory (in Windows) or Unix socket files (in Unix).
In socket Windows, this option specifies the name of the pipe used by the local connection, the default value is MySQL;Unix, this option specifies the Unix socket file for the local connection, and the default value is / tmp/mysql.sock.
Transaction-isolation sets the default transaction isolation level.
The user under which user runs the server can be specified by user name or user ID.
The directory path where tmpdir creates temporary files.
All options can be specified when the server process starts (preceded by "- -"), so that the specified options take precedence over the options in the options file. More options and detailed instructions can be found in the MySQL reference manual.
2) use the SET command
When the server is running, you can use the SET command to set the environment variables. For example:
Mysql > set table_type = innodb
Query OK, 0 rows affected, 1 warning (0.05 sec)
You can use the GLOBAL or SESSION keyword to specify global settings (all sessions) or session-level settings (current sessions).
Mysql > set global table_type = innodb
Query OK, 0 rows affected, 1 warning (0.00 sec)
Mysql > set session table_type = innodb
Query OK, 0 rows affected, 1 warning (0.00 sec)
SUPER permission is required to use the GLOBAL keyword.
Here are some of the more important server variables:
Whether autocommit SESSION turns on autocommit mode.
The index block of the key_buffer_size GLOBAL MyISAM table allocates a buffer that is shared by all threads. Key_buffer_size is the size of the index block buffer. The key buffer is the key cache.
The number of tables opened by all threads of the table_cache GLOBAL.
Table_type GLOBAL | SESSION default table type (storage engine).
Whether concurrent_insert GLOBAL allows INSERT and SELECT statements to run in parallel in an MyISAM table with no empty data blocks in the middle.
Interactive_timeout GLOBAL | the number of seconds the SESSION server waits for activity before closing the interactive connection.
Lower_case_table_names GLOBAL | whether SESSION saves the table name in lowercase.
Sort_buffer_size GLOBAL | the size of the buffer allocated by each sort thread in SESSION.
Read_buffer_size GLOBAL | the size (in bytes) of the buffer allocated for each table scanned by each thread when it is scanned continuously.
Max_binlog_size GLOBAL if the binary log writes more than a given value, the log scrolls. This variable cannot be set to greater than 1GB or less than 4096 bytes. The default value is 1GB.
The number of parallel client connections allowed by max_connections GLOBAL. Increasing this value increases the number of file descriptors required by mysqld.
Max_user_connections GLOBAL the maximum number of simultaneous connections allowed for any given MySQL account. A value of 0 means "no limit".
Max_tmp_tables GLOBAL | the maximum number of temporary tables that can be opened by SESSION clients at the same time. (however, this option has not yet taken effect. )
Query_cache_type GLOBAL | SESSION sets the query cache type.
The amount of memory allocated by query_cache_size GLOBAL to cache query results. The default value is 0, which disables query caching. Even if query_cache_type is set to 0, this amount of memory will be allocated.
Tx_isolation GLOBAL | SESSION default transaction isolation level. The default is REPEATABLE-READ.
3) retrieve the value of the variable
You can use the variables command of mysqladmin or the show variables command of the mysql command line to view the value of the server variable.
The LIKE keyword can restrict the output of show variables:
Mysql > show variables like'% cache_size%'
+-+ +
| | Variable_name | Value |
+-+ +
| | binlog_cache_size | 32768 | |
| | max_binlog_cache_size | 4294963200 | |
| | query_cache_size | 15728640 | |
| | thread_cache_size | 8 |
+-+ +
4 rows in set (0.00 sec)
The output of mysqladmin variables can achieve a similar effect with operating system commands find (Windows), grep (Unix), and so on:
D:Program FilesMySQLMySQL Server 5.1bin > mysqladmin variables | find "cache_size"
| | binlog_cache_size | 32768 |
| |
| | max_binlog_cache_size | 4294963200 |
| |
| | query_cache_size | 15728640 |
| |
| | thread_cache_size | 8 |
| |
In addition, you can get the value of a single variable by the SELECT @ @ variable statement.
Mysql > select @ @ log_error
+-+
| | @ @ log_error |
+-+
| | E:MySQL DatafilesDatayuegao.err |
+-+
1 row in set (0.00 sec)
5. Use the error log to troubleshoot
Important information such as server startup and shutdown processes, key errors and warnings of damaged tables are kept in the error log.
You can use the method in the above example to determine the location of the error log.
These are all the contents of the article "how to manage and configure MySQL". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!
Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.