7 MySQL程序概述

以下的文章主要讲述的是MySQL命令提示符程序,不断完善的实际操作过程,如果你对完善MySQL命令提示符程序的实际操作过程有不懂之处的话,你就可以通过我们文章对其进行了解,以下就是文章的主要内容。

MySQL AB提供了几种类型的程序:

  • MYSQL服务器和服务器启动脚本:
    1: mysqld是MySQL服务器
    2: mysqld_safe、mysql.server和mysqld_multi是服务器启动脚本
    3: mysql_install_db初始化数据目录和初始数据库

  • 访问服务器的客户程序:
    1:
    mysql是一个命令行客户程序,用于交互式或以批处理模式执行SQL语句。
    2: mysqladmin是用于管理功能的客户程序。
    3: mysqlcheck执行表维护操作。
    4: mysqldump和mysqlhotcopy负责数据库备份。
    5: mysqlimport导入数据文件。
    6: mysqlshow显示信息数据库和表的相关信息。

  • 独立于服务器操作的工具程序:
    1: myisamchk执行表维护操作。
    2: myisampack产生压缩、只读的表。
    3: mysqlbinlog是处理二进制日志文件的实用工具。
    4: perror显示错误代码的含义

以下的文章主要介绍的是重启MySQL数据库的实际操作方案,当我们修改了my.cnf,就需要对MySQL数据库进行重启服务,以下的文章就是对重启MySQL数据库的实际操作方案的具体描述,望你会有所收获。

MySQL服务器和服务器启动脚本:

调用MySQL程序

要想从命令行调用MySQL程序(即从shell或命令提示),应输入程序名,并随后输入指导操作发的选项或其它参量。下面的命令显示了一些程序调用的例子。“shell>”表示命令解释符提示;并不是输入的部分。你所看见的具体提示取决于命令解释符。典型提示符:sh或bash为$,csh或tcsh为%,Windows
command.com或cmd.exe为C:>。

  • C:\Users\Administrator> mysql -h localhost -u root -p123456
    test

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.5.49 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  • C:\Users\Administrator> mysqladmin -h localhost -u root
    -p123456 extended-status variables

| long_query_time                                   | 10.000000

             |
| low_priority_updates                              | OFF

             |
| lower_case_file_system                            | ON

             |
| lower_case_table_names                            | 1

             |
| max_allowed_packet                                | 1048576

             |
| max_binlog_cache_size                             | 18446744073709547520

             |
| max_binlog_size                                   | 1073741824

             |
| max_binlog_stmt_cache_size                        | 18446744073709547520

             |
| max_connect_errors                                | 10

             |
| max_connections                                   | 100

             |
| max_delayed_threads                               | 20

             |
| max_error_count                                   | 64

             |
| max_heap_table_size                               | 16777216

             |
| max_insert_delayed_threads                        | 20

             |
| max_join_size                                     | 18446744073709551615

             |
| max_length_for_sort_data                          | 1024

             |
| max_long_data_size                                | 1048576

             |
| max_prepared_stmt_count                           | 16382

             |
| max_relay_log_size                                | 0

             |
| max_seeks_for_key                                 | 4294967295

             |
| max_sort_length                                   | 1024

             |
| max_sp_recursion_depth                            | 0

             |
| max_tmp_tables                                    | 32

             |
| max_user_connections                              | 0

             |
| max_write_lock_count                              | 4294967295

             |
| metadata_locks_cache_size                         | 1024

             |
| min_examined_row_limit                            | 0

             |
| multi_range_count                                 | 256

             |
| myisam_data_pointer_size                          | 6

             |
| myisam_max_sort_file_size                         | 107374182400

             |
| myisam_mmap_size                                  | 4294967295

             |
| myisam_recover_options                            | OFF

             |
| myisam_repair_threads                             | 1

             |
| myisam_sort_buffer_size                           | 72351744

             |
| myisam_stats_method                               | nulls_unequal

             |
| myisam_use_mmap                                   | OFF

             |
| named_pipe                                        | OFF

             |
| net_buffer_length                                 | 16384

             |
| net_read_timeout                                  | 30

             |
| net_retry_count                                   | 10

             |
| net_write_timeout                                 | 60

             |
| new                                               | OFF

             |
| old                                               | OFF

             |
| old_alter_table                                   | OFF

             |
| old_passwords                                     | OFF

             |
| open_files_limit                                  | 2670

             |
| optimizer_prune_level                             | 1

             |
| optimizer_search_depth                            | 62

             |
| optimizer_switch                                  | index_merge=on,index_merge
_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition
_pushdown=on |
| performance_schema                                | OFF

             |
| performance_schema_events_waits_history_long_size | 10000

             |
| performance_schema_events_waits_history_size      | 10

             |
| performance_schema_max_cond_classes               | 80

             |
| performance_schema_max_cond_instances             | 1000

             |
| performance_schema_max_file_classes               | 50

             |
| performance_schema_max_file_handles               | 32768

             |
| performance_schema_max_file_instances             | 10000

             |
| performance_schema_max_mutex_classes              | 200

             |
| performance_schema_max_mutex_instances            | 1000000

             |
| performance_schema_max_rwlock_classes             | 30

             |
| performance_schema_max_rwlock_instances           | 1000000

             |
| performance_schema_max_table_handles              | 100000

             |
| performance_schema_max_table_instances            | 50000

             |
| performance_schema_max_thread_classes             | 50

             |
| performance_schema_max_thread_instances           | 1000

             |
| pid_file                                          | C:\ProgramData\MySQL\MySQL
 Server 5.5\Data\PC201510271.pid
             |
| plugin_dir                                        | C:\Program Files (x86)\MyS
QL\MySQL Server 5.5\lib\plugin\
             |
| port                                              | 3306

             |
| preload_buffer_size                               | 32768

             |
| profiling                                         | OFF

             |
| profiling_history_size                            | 15

             |
| protocol_version                                  | 10

             |
| query_alloc_block_size                            | 8192

             |
| query_cache_limit                                 | 1048576

             |
| query_cache_min_res_unit                          | 4096

             |
| query_cache_size                                  | 0

             |
| query_cache_type                                  | ON

             |
| query_cache_wlock_invalidate                      | OFF

             |
| query_prealloc_size                               | 8192

             |
| range_alloc_block_size                            | 4096

             |
| read_buffer_size                                  | 65536

             |
| read_only                                         | OFF

             |
| read_rnd_buffer_size                              | 262144

             |
| relay_log                                         |

             |
| relay_log_index                                   |

             |
| relay_log_info_file                               | relay-log.info

             |
| relay_log_purge                                   | ON

             |
| relay_log_recovery                                | OFF

             |
| relay_log_space_limit                             | 0

             |
| report_host                                       |

             |
| report_password                                   |

             |
| report_port                                       | 3306

             |
| report_user                                       |

             |
| rpl_recovery_rank                                 | 0

             |
| secure_auth                                       | OFF

             |
| secure_file_priv                                  |

             |
| server_id                                         | 0

             |
| shared_memory                                     | OFF

             |
| shared_memory_base_name                           | MYSQL

             |
| skip_external_locking                             | ON

             |
| skip_name_resolve                                 | OFF

             |
| skip_networking                                   | OFF

             |
| skip_show_database                                | OFF

             |
| slave_compressed_protocol                         | OFF

             |
| slave_exec_mode                                   | STRICT

             |
| slave_load_tmpdir                                 | C:\Windows\TEMP

             |
| slave_max_allowed_packet                          | 1073741824

             |
| slave_net_timeout                                 | 3600

             |
| slave_skip_errors                                 | OFF

             |
| slave_transaction_retries                         | 10

             |
| slave_type_conversions                            |

             |
| slow_launch_time                                  | 2

             |
| slow_query_log                                    | OFF

             |
| slow_query_log_file                               | C:\ProgramData\MySQL\MySQL
 Server 5.5\Data\PC201510271-slow.log
             |
| socket                                            | MySQL

             |
| sort_buffer_size                                  | 262144

             |
| sql_auto_is_null                                  | OFF

             |
| sql_big_selects                                   | ON

             |
| sql_big_tables                                    | OFF

             |
| sql_buffer_result                                 | OFF

             |
| sql_log_bin                                       | ON

             |
| sql_log_off                                       | OFF

             |
| sql_low_priority_updates                          | OFF

             |
| sql_max_join_size                                 | 18446744073709551615

             |
| sql_mode                                          | STRICT_TRANS_TABLES,NO_AUT
O_CREATE_USER,NO_ENGINE_SUBSTITUTION
             |
| sql_notes                                         | ON

             |
| sql_quote_show_create                             | ON

             |
| sql_safe_updates                                  | OFF

             |
| sql_select_limit                                  | 18446744073709551615

             |
| sql_slave_skip_counter                            | 0

             |
| sql_warnings                                      | OFF

             |
| ssl_ca                                            |

             |
| ssl_capath                                        |

             |
| ssl_cert                                          |

             |
| ssl_cipher                                        |

             |
| ssl_key                                           |

             |
| storage_engine                                    | InnoDB

             |
| stored_program_cache                              | 256

             |
| sync_binlog                                       | 0

             |
| sync_frm                                          | ON

             |
| sync_master_info                                  | 0

             |
| sync_relay_log                                    | 0

             |
| sync_relay_log_info                               | 0

             |
| system_time_zone                                  |

             |
| table_definition_cache                            | 400

             |
| table_open_cache                                  | 256

             |
| thread_cache_size                                 | 8

             |
| thread_concurrency                                | 10

             |
| thread_handling                                   | one-thread-per-connection

             |
| thread_stack                                      | 196608

             |
| time_format                                       | %H:%i:%s

             |
| time_zone                                         | SYSTEM

             |
| timed_mutexes                                     | OFF

             |
| tmp_table_size                                    | 36700160

             |
| tmpdir                                            | C:\Windows\TEMP

             |
| transaction_alloc_block_size                      | 8192

             |
| transaction_prealloc_size                         | 4096

             |
| tx_isolation                                      | REPEATABLE-READ

             |
| unique_checks                                     | ON

             |
| updatable_views_with_limit                        | YES

             |
| version                                           | 5.5.49

             |
| version_comment                                   | MySQL Community Server (GP
L)
             |
| version_compile_machine                           | x86

             |
| version_compile_os                                | Win32

             |
| wait_timeout                                      | 28800

             |
+---------------------------------------------------+---------------------------
--------------------------------------------------------------------------------
-------------+
  • C:\Users\Administrator> mysqlshow –help

mysqlshow  Ver 9.10 Distrib 5.5.49, for Win32 (x86)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Shows the structure of a MySQL database (databases, tables, and columns).

Usage: mysqlshow [OPTIONS] [database [table [column]]]

If last argument contains a shell or SQL wildcard (*,?,% or _) then only
what's matched by the wildcard is shown.
If no database is given then all matching databases are shown.
If no table is given, then all matching tables in database are shown.
If no column is given, then all matching columns and column types in table
are shown.

Default options are read from the following files in the given order:
C:\Windows\my.ini C:\Windows\my.cnf C:\my.ini C:\my.cnf C:\Program Files (x86)\M
ySQL\MySQL Server 5.5\my.ini C:\Program Files (x86)\MySQL\MySQL Server 5.5\my.cn
f
The following groups are read: mysqlshow client
The following options may be given as the first argument:
--print-defaults        Print the program argument list and exit.
--no-defaults           Don't read default options from any option file.
--defaults-file=#       Only read default options from the given file #.
--defaults-extra-file=# Read this file after the global files are read.
  -c, --character-sets-dir=name
                      Directory for character set files.
  --default-character-set=name
                      Set the default character set.
  --count             Show number of rows per table (may be slow for non-MyISAM
                      tables).
  -C, --compress      Use compression in server/client protocol.
  -#, --debug[=name]  Output debug log. Often this is 'd:t:o,filename'.
  --debug-check       Check memory and open file usage at exit.
  --debug-info        Print some debug info at exit.
  --default-auth=name Default authentication client-side plugin to use.
  --enable-cleartext-plugin
                      Enable/disable the clear text authentication plugin.
  -?, --help          Display this help and exit.
  -h, --host=name     Connect to host.
  -i, --status        Shows a lot of extra information about each table.
  -k, --keys          Show keys for table.
  -p, --password[=name]
                      Password to use when connecting to server. If password is
                      not given, it's solicited on the tty.
  --plugin-dir=name   Directory for client-side plugins.
  -P, --port=#        Port number to use for connection or 0 for default to, in
                      order of preference, my.cnf, $MYSQL_TCP_PORT,
                      /etc/services, built-in default (3306).
  -W, --pipe          Use named pipes to connect to server.
  --protocol=name     The protocol to use for connection (tcp, socket, pipe,
                      memory).
  --shared-memory-base-name=name
                      Base name of shared memory.
  -t, --show-table-type
                      Show table type column.
  -S, --socket=name   The socket file to use for connection.
  --ssl               Enable SSL for connection (automatically enabled with
                      other flags).
  --ssl-ca=name       CA file in PEM format (check OpenSSL docs, implies
                      --ssl).
  --ssl-capath=name   CA directory (check OpenSSL docs, implies --ssl).
  --ssl-cert=name     X509 cert in PEM format (implies --ssl).
  --ssl-cipher=name   SSL cipher to use (implies --ssl).
  --ssl-key=name      X509 key in PEM format (implies --ssl).
  --ssl-verify-server-cert
                      Verify server's "Common Name" in its cert against
                      hostname used when connecting. This option is disabled by
                      default.
  --ssl-mode=name     SSL connection mode.
  -u, --user=name     User for login if not current user.
  -v, --verbose       More verbose output; you can use this multiple times to
                      get even more verbose output.
  -V, --version       Output version information and exit.

Variables (--variable-name=value)
and boolean options {FALSE|TRUE}  Value (after reading options)
--------------------------------- ----------------------------------------
character-sets-dir                (No default value)
default-character-set             auto
count                             FALSE
compress                          FALSE
debug-check                       FALSE
debug-info                        FALSE
default-auth                      (No default value)
enable-cleartext-plugin           FALSE
host                              (No default value)
status                            FALSE
keys                              FALSE
plugin-dir                        (No default value)
port                              3306
shared-memory-base-name           (No default value)
show-table-type                   FALSE
socket                            (No default value)
ssl                               FALSE
ssl-ca                            (No default value)
ssl-capath                        (No default value)
ssl-cert                          (No default value)
ssl-cipher                        (No default value)
ssl-key                           (No default value)
ssl-verify-server-cert            FALSE
user                              (No default value)
  • C:\Users\Administrator>mysqldump –user=root -p123456 personnel

-- MySQL dump 10.13  Distrib 5.5.49, for Win32 (x86)
--
-- Host: localhost    Database: personnel
-- ------------------------------------------------------
-- Server version       5.5.49

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0
*/;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
mysqldump: Got error: 1049: Unknown database 'personnel' when selecting the data
base
  • 以破折号开始的参数为选项参量。它们通常指定程序与服务器的连接类型或影响其操作模式。

  • 非选项参数(不以破折号开始的参量)可以为程序提供更详细的信息。例如,mysql程序将第一个非选项参量解释为数据库名,因此命令
    mysql -h localhost -u root -p123456 test表示你想要使用test数据库。

  • 部分选项对部分程序是通用的。最常用的是指定连接参数的-h、–u和-p选项。它们指示MySQL服务器运行的主机和MySQL账户的用户名和
    密码。所有MySQL客户程序可以理解这些选项;它们允许你指定连接哪个服务器,以及在该服务器上使用的
    账户。

你也许会发现需要使用MySQL程序安装的bin目录的路径名来调用MySQL程序。如果你试图不在bin目录下运行MySQL程序,可能会遇到“程序未找到”错误。为了更方便地使用MySQL,可以将bin目录的路径名添加到PATH环境变量中(详情请见安装MySQL教程中)。然后运行程序时只需要输入程序名,而不需要输入整个路径名。

由于是从源码包安装的MySQL,所以系统中是没有红帽常用的servcie MySQLd
restart这个脚本只好手工重启有人建议Killall
MySQL。这种野蛮的方法其实是不行的,强制终止的话,如果造成表损坏,损失是巨大的。

MySQLd是MySQL服务器

这里推荐安全的重启方法

例:MySQLd –skip-grant-tables非安全验证模式启动MySQL

$MySQL_dir/bin/MySQLadmin -u root -p shutdown  $MySQL_dir/bin/safe_MySQLd &  

MySQLd –verbose –help MySQL命令格式帮助

MySQLadmin和MySQLd_safe位于MySQL安装目录的bin目录下,很容易找到的。

MySQLd –console MySQL服务器控制台

windows下重启MySQL服务

MySQLd_safe、MySQL.server和MySQLd_multi是服务器启动脚本

对于没装MySQL图形管理端的用户来说启动和停止MySQL服务:

MySQL_install_db初始化数据目录和初始数据库

…\…\bin>net stop MySQL  …\…\bin>net start MySQL