search
HomeDatabaseMysql Tutorial单用户模式连接以及故障排除

单用户模式连接以及故障排除

Jun 07, 2016 pm 02:55 PM
single userScenesexcludeFaultmodeluserconnect

场景 :这是一个命名实例,使用动态端口,需要进入单用户模式进行维护。 一、启动实例 我们将直接使用命令行窗口启动单用户模式。 1. 找到 sqlservr.exe 的路径 在Windows的“服务”中找到这个命名实例,查出“可执行文件的路径”。例如: "C:\Program Files

场景:这是一个命名实例,使用动态端口,需要进入单用户模式进行维护。


一、启动实例

  我们将直接使用命令行窗口启动单用户模式。


1. 找到 sqlservr.exe 的路径

  在Windows的“服务”中找到这个命名实例,查出“可执行文件的路径”。例如:

"C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Binn\sqlservr.exe" -sSQL2008R2

wKioL1SsnQvyfkXYAAMykCpd-lk127.jpg


2. 启动实例

  打开一个命令行窗口。将“可执行文件的路径”复制到这个命令行窗口,然后添加“ -m "sqlcmd" ” 参数,指定单用户模式启动而且只有 sqlcmd 可以连接到这个实例。

C:\Users\Administrator>  "C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Binn\sqlservr.exe" -sSQL2008R2 -m "sqlcmd"

  启动后,这个窗口将显示一大堆的启动信息。


注意:为了保证启动帐户有足够的权限,建议改为 Local System 启动。请在 SQL Server 配置管理器中修改。

wKiom1SsqtuAvxaKAAIz0UKowfA670.jpg



二、确认端口配置

  针对 TCP/IP 端口,面临两种选择。一种是统一配置本机所有的 IP 的端口,另一种是单独为本机每个 IP 地址配置端口。


1. 选择1:启用“全部侦听”

  在前面的步骤中,在命令行窗口中注意寻找端口信息。例如:

2015-01-07 10:42:49.09 服务器         Server is listening on [ 'any' 49157].

2015-01-07 10:42:49.09 服务器         Server is listening on [ 'any' 49157].

2015-01-07 10:42:49.10 服务器         Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\SQL2008R2 ].

2015-01-07 10:42:49.11 服务器         Server local connection provider is ready to accept connection on [ \\.\pipe\MSSQL$SQL2008R2\sql\query ].

2015-01-07 10:42:49.11 服务器         Server is listening on [ ::1 49163].

2015-01-07 10:42:49.11 服务器         Server is listening on [ 127.0.0.1  49163].

2015-01-07 10:42:49.12 服务器         Dedicated admin connection support was established for listening locally on port 49163.


  注意:由于每个实例的配置不尽相同,所以不一定都会看到本例完全一样的信息。“Server is listening on [ 'any' 49157]”表示全部侦听,而且侦听的端口号为 49157 。

wKiom1SsoHOiESEFAAIX639s_QQ495.jpg


  一旦“全部侦听”启用了,那么就只有最底下一栏“IPAll”生效,其它的IP单独的配置都无效!

wKiom1SsoNWihqcVAAGyl1fKfVY642.jpg

说明:

(1)“TCP动态端口”如果留空,表示使用静态端口;如果在配置时设置为0,表示使用动态端口。使用动态端口时,在实例启动之后,才会显示当前的端口。每次重启实例都可能换成另一个端口。

(2)如果在“TCP端口”中填写一个固定的数据,同时将“TCP动态端口”留空,那么重启后,实例将使用这个静态端口。对于服务器上的默认实例,默认使用 TCP1433 端口。


2. 选择2:不启用“全部侦听”

  下面的信息则显示只侦听127.0.0.1端口。

2015-01-07 11:03:53.76 服务器         Server is listening on [ 127.0.0.1 49168].

2015-01-07 11:03:53.77 服务器         Dedicated admin connection support was established for listening locally on port 49168.


  在配置界面,仅针对127.0.0.1这个IP,将“活动”和“已启用”都设为“是”。可以分别为每个IP配置不同的端口以及设置端口的活动状态。

wKiom1SsouuQ3_wRAAHLyePh2-M769.jpg


故障排除:

  如果本机曾经修改过IP地址,导致在不启用“全部侦听”时会将错误的旧IP地址绑定到该实例,这样会报错并导致启动失败。请参考 《修改 SQL Server 服务器的 IP 地址》 http://jimshu.blog.51cto.com/3171847/1120377 



三、客户端连接

  由于前面的步骤仅允许使用 SQLCMD 方式连接,所以需要另外打开一个命令行窗口。使用“-S”参数,后面加上IP地址以及端口号(两者之间用逗号隔开)。例如,下面的命令已经成功地建立了连接。

C:\Users\Administrator> sqlcmd -S 127.0.0.1,49163

1>


  连接成功后,即可参考《忘记管理员密码的补救办法》 http://jimshu.blog.51cto.com/3171847/1563207 进行一些维护操作。例如,重设sa的密码。

1> Alter Login [sa] with password='newpassword';

2> GO


注:在本例中,我们直接使用端口号。如果使用实例名(127.0.0.1\SQL2008R2),那么还需要启用 SQL Server Browser服务,并为防火墙打开 UDP1434 端口。请参考《SQL Server 客户端连接的问题》  http://jimshu.blog.51cto.com/3171847/1395199



四、停止实例

  在启动实例的那个命令行窗口,使用 Ctrl-C 或者 Ctrl-Break 组合键,停止实例。

Do you wish to shutdown SQL Server (Y/N)? y

2015-01-07 11:36:37.76 spid7s      SQL Server shutdown due to Ctrl-C or Ctrl-Break signal. This is an informational message only. No user action is required.

2015-01-07 11:36:37.76 spid7s      SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.


  甚至,可以直接关闭这个命令行窗口。


Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Explain the InnoDB Buffer Pool and its importance for performance.Explain the InnoDB Buffer Pool and its importance for performance.Apr 19, 2025 am 12:24 AM

InnoDBBufferPool reduces disk I/O by caching data and indexing pages, improving database performance. Its working principle includes: 1. Data reading: Read data from BufferPool; 2. Data writing: After modifying the data, write to BufferPool and refresh it to disk regularly; 3. Cache management: Use the LRU algorithm to manage cache pages; 4. Reading mechanism: Load adjacent data pages in advance. By sizing the BufferPool and using multiple instances, database performance can be optimized.

MySQL vs. Other Programming Languages: A ComparisonMySQL vs. Other Programming Languages: A ComparisonApr 19, 2025 am 12:22 AM

Compared with other programming languages, MySQL is mainly used to store and manage data, while other languages ​​such as Python, Java, and C are used for logical processing and application development. MySQL is known for its high performance, scalability and cross-platform support, suitable for data management needs, while other languages ​​have advantages in their respective fields such as data analytics, enterprise applications, and system programming.

Learning MySQL: A Step-by-Step Guide for New UsersLearning MySQL: A Step-by-Step Guide for New UsersApr 19, 2025 am 12:19 AM

MySQL is worth learning because it is a powerful open source database management system suitable for data storage, management and analysis. 1) MySQL is a relational database that uses SQL to operate data and is suitable for structured data management. 2) The SQL language is the key to interacting with MySQL and supports CRUD operations. 3) The working principle of MySQL includes client/server architecture, storage engine and query optimizer. 4) Basic usage includes creating databases and tables, and advanced usage involves joining tables using JOIN. 5) Common errors include syntax errors and permission issues, and debugging skills include checking syntax and using EXPLAIN commands. 6) Performance optimization involves the use of indexes, optimization of SQL statements and regular maintenance of databases.

MySQL: Essential Skills for Beginners to MasterMySQL: Essential Skills for Beginners to MasterApr 18, 2025 am 12:24 AM

MySQL is suitable for beginners to learn database skills. 1. Install MySQL server and client tools. 2. Understand basic SQL queries, such as SELECT. 3. Master data operations: create tables, insert, update, and delete data. 4. Learn advanced skills: subquery and window functions. 5. Debugging and optimization: Check syntax, use indexes, avoid SELECT*, and use LIMIT.

MySQL: Structured Data and Relational DatabasesMySQL: Structured Data and Relational DatabasesApr 18, 2025 am 12:22 AM

MySQL efficiently manages structured data through table structure and SQL query, and implements inter-table relationships through foreign keys. 1. Define the data format and type when creating a table. 2. Use foreign keys to establish relationships between tables. 3. Improve performance through indexing and query optimization. 4. Regularly backup and monitor databases to ensure data security and performance optimization.

MySQL: Key Features and Capabilities ExplainedMySQL: Key Features and Capabilities ExplainedApr 18, 2025 am 12:17 AM

MySQL is an open source relational database management system that is widely used in Web development. Its key features include: 1. Supports multiple storage engines, such as InnoDB and MyISAM, suitable for different scenarios; 2. Provides master-slave replication functions to facilitate load balancing and data backup; 3. Improve query efficiency through query optimization and index use.

The Purpose of SQL: Interacting with MySQL DatabasesThe Purpose of SQL: Interacting with MySQL DatabasesApr 18, 2025 am 12:12 AM

SQL is used to interact with MySQL database to realize data addition, deletion, modification, inspection and database design. 1) SQL performs data operations through SELECT, INSERT, UPDATE, DELETE statements; 2) Use CREATE, ALTER, DROP statements for database design and management; 3) Complex queries and data analysis are implemented through SQL to improve business decision-making efficiency.

MySQL for Beginners: Getting Started with Database ManagementMySQL for Beginners: Getting Started with Database ManagementApr 18, 2025 am 12:10 AM

The basic operations of MySQL include creating databases, tables, and using SQL to perform CRUD operations on data. 1. Create a database: CREATEDATABASEmy_first_db; 2. Create a table: CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY, titleVARCHAR(100)NOTNULL, authorVARCHAR(100)NOTNULL, published_yearINT); 3. Insert data: INSERTINTObooks(title, author, published_year)VA

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Tools

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Dreamweaver Mac version

Dreamweaver Mac version

Visual web development tools

Atom editor mac version download

Atom editor mac version download

The most popular open source editor

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

MinGW - Minimalist GNU for Windows

MinGW - Minimalist GNU for Windows

This project is in the process of being migrated to osdn.net/projects/mingw, you can continue to follow us there. MinGW: A native Windows port of the GNU Compiler Collection (GCC), freely distributable import libraries and header files for building native Windows applications; includes extensions to the MSVC runtime to support C99 functionality. All MinGW software can run on 64-bit Windows platforms.