MySQL如何管理与配置

2024-04-02 19:55

短信预约 -IT技能 免费直播动态提醒

这篇文章主要介绍MySQL如何管理与配置，文中介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们一定要看完！

mysqladmin [OPTIONS] command command....

下面是一些比较有用的命令：

status 返回服务器状态信息
password 修改用户口令
shutdown 关闭MySQL服务器
reload 重载MySQL授权表
refresh 重置所有高速缓存和日志
variables 返回所有服务器变量值
version 返回服务器版本
processlist 返回服务器上所有进程的列表
kill 取消一个服务器进程
ping 测试服务器是否在活动状态

[@more@]
1.启动和关闭服务器

Windows环境中，MySQL可以在后台作为一个服务来启动：

D:Program FilesMySQLMySQL Server 5.1bin>net start mysql
MySQL 服务正在启动 .
MySQL 服务已经启动成功。

D:Program FilesMySQLMySQL Server 5.1bin>mysqladmin -u root -p ping
Enter password: ****
mysqld is alive

而关闭服务器，可以使用mysqladmin的shutdown命令：

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'

2.检查MySQL服务器状态

使用mysqladmin的status命令可以查看服务器的当前状态：

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

输出结果各列的含义如下：

Uptime MySQL服务器已经运行的秒数
Threads 活跃线程（客户）的数量
Questions 从mysqld启动起来自客户问题的数量
Slow queries 已经超过long_query_time秒的查询数量
Opens mysqld已经打开了多少表
Flush tables flush ..., refresh和reload命令数量
Open tables 现在被打开的表数量

而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

还可以使用extended-status命令或mysql命令行的show status命令得到扩展的状态信息。

3.管理MySQL客户机进程

使用mysqladmin的processlist命令，或mysql命令行的show processlist命令，可以获得连接到服务器上所有客户机进程的列表：

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 |
+----+------+----------------+----+---------+------+-------+------------------+

然后可以用mysqladmin的kill命令，或mysql命令行的kill命令中止进程。

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.修改服务器配置

1)使用选项文件

选项文件的名称和位置：

Windows下，选项文件名为my.cnf或my.ini。位置为，%WINDIR%；C:；MySQL安装目录，如D:Program FilesMySQLMySQL Server 5.1；用--defaults-extra-file=path指定的文件。
Unix下，选项文件名为my.cnf。位置为，/etc；$MYSQL_HOME/；用--defaults-extra-file=path指定的文件；~/.my.cnf。
详细说明可查阅MySQL参考手册。

MySQL的各种程序可以从相同的选项文件读取选项，文件中选项划分成不同的组，如[client]、[mysql]、[mysqld]等等。例如，进行如下修改：

[client]

port=3306

# Added by yuegao Jul 7, 2009
user=root
password=pccw

那么客户机连接服务器时，就可以使用默认的用户名和口令：

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

类似地，可以通过设置[mysql]和[mysqld]中的选项，修改服务器进程的配置。
下面是一些配置MySQL比较常用的选项：

ansi 使用标准(ANSI)SQL语法代替MySQL语法。
basedir MySQL安装目录的路径。通常所有路径根据该路径来解析。
datadir 数据目录的路径。
debug 如果MySQL配置了--with-debug，你可以使用该选项来获得一个跟踪文件，跟踪mysqld正进行的操作。
default-character-set 设置默认字符集。
default-table-type 设置表的默认类型。
flush 执行SQL语句后向硬盘上写更改。
init-file 启动时从该文件读SQL语句。每个语句必须在同一行中并且不应包括注释。
language 用给定语言给出客户端错误消息。
log 指定记录MySQL信息的日志文件。
log-error 指定记录错误信息的日志文件。
log-warnings 在记录错误信息的日志文件中记录警告信息。
log-slow-queries 将所有执行时间超过long_query_time 秒的查询记入日志文件。
log-bin 指定二进制日志文件。将更改数据的所有查询记入该文件。
port 侦听TCP/IP连接时使用的端口号。
skip-bdb 禁用BDB存储引擎。这样可以节省内存，并可能加速某些操作。
skip-innodb 禁用InnoDB存储引擎。这样可以节省内存，并可能加速某些操作。
skip-grant-tables 该选项使服务器不使用权限系统。该权限允许访问服务器的用户不受限制地访问所有数据库。
skip-networking 不帧听TCP/IP连接。必须通过命名管道或共享内存(在Windows中)或Unix套接字文件(在Unix中)完成mysqld的相互操作。
socket Windows中，该选项指定本地连接所使用的管道名，默认值是MySQL；Unix中，该选项指定用于本地连接的Unix套接字文件，默认值是/tmp/mysql.sock。
transaction-isolation 设置默认事务隔离级别。
user 运行服务器的用户，可以通过用户名或者用户ID指定。
tmpdir 创建临时文件的目录路径。

所有的选项都可以在服务器进程启动时指定（在前面加上“--”），这样指定的选项优先级高于选项文件中选项，更多选项及详细说明可查阅MySQL参考手册。

2)使用SET命令

服务器运行时，可以使用SET命令设置环境变量。例如：

mysql> set table_type = innodb;
Query OK, 0 rows affected, 1 warning (0.05 sec)

可以使用GLOBAL或SESSION关键字，指定进行全局设置（所有会话）或会话级设置（当前会话）。

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)

使用GLOBAL关键字需要具有SUPER权限。
下面是一些比较重要的服务器变量：

autocommit SESSION 是否打开自动提交模式。
key_buffer_size GLOBAL MyISAM表的索引块分配了缓冲区，由所有线程共享。key_buffer_size是索引块缓冲区的大小。键值缓冲区即为键值缓存。
table_cache GLOBAL 所有线程打开的表的数目。
table_type GLOBAL | SESSION 默认表类型(存储引擎)。
concurrent_insert GLOBAL 是否允许INSERT和SELECT语句在中间没有空数据块的MyISAM表中并行运行。
interactive_timeout GLOBAL | SESSION 服务器关闭交互式连接前等待活动的秒数。
lower_case_table_names GLOBAL | SESSION 是否将表名保存为小写形式。
sort_buffer_size GLOBAL | SESSION 每个排序线程分配的缓冲区的大小。
read_buffer_size GLOBAL | SESSION 每个线程连续扫描时为扫描的每个表分配的缓冲区的大小(字节)。
max_binlog_size GLOBAL 如果二进制日志写入的内容超出给定值，日志就会发生滚动。不能将该变量设置为大于1GB或小于4096字节。默认值是1GB。
max_connections GLOBAL 允许的并行客户端连接数目。增大该值则增加mysqld 需要的文件描述符的数量。
max_user_connections GLOBAL 任何给定的MySQL账户允许的最大同时连接数。0值表示“没有限制”。
max_tmp_tables GLOBAL | SESSION 客户端可以同时打开的临时表的最大数。（但该选项还未生效。）
query_cache_type GLOBAL | SESSION 设置查询缓存类型。
query_cache_size GLOBAL 为缓存查询结果分配的内存的数量。默认值是0，即禁用查询缓存。即使query_cache_type设置为0也将分配此数量的内存。
tx_isolation GLOBAL | SESSION 默认事务隔离级别。默认值为REPEATABLE-READ。

3)检索变量值

可以使用mysqladmin的variables命令，或mysql命令行的show variables命令查看服务器变量的值。
LIKE关键字可以对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)

而mysqladmin variables的输出则可以用操作系统命令find（Windows）、grep（Unix）等达到类似的效果：

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
|

此外，还可以SELECT @@variable语句获取单个变量的值。

mysql> select @@log_error;
+------------------------------------+
| @@log_error |
+------------------------------------+
| E:MySQL DatafilesDatayuegao.err |
+------------------------------------+
1 row in set (0.00 sec)

5.使用错误日志排错

错误日志里保存了服务器启动和关闭过程的记录，关键的错误和受损表的警告等重要信息。
可以通过上面的例子中的方法确定错误日志的位置。