默认及非默认端口的动态监听/静态监听实验汇总-mysql教程-PHP中文网

首页

数据库

mysql教程

默认及非默认端口的动态监听/静态监听实验汇总

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 03:28 PM

动态实验步骤汇总监听端口配置静态默认

步骤大致是：1.配置一个非默认的动态监听，运行在1525端口 2.默认动态监听同时要运行 3.配置一个静态监听，运行在1526端口 4.打开这些监听，并使用sqlplus登陆测试数据库内一些参数： SYS@ bys3show parameter service NAME TYPE VALUE --------------------

步骤大致是：1.配置一个非默认的动态监听，运行在1525端口
2.默认动态监听同时要运行
3.配置一个静态监听，运行在1526端口
4.打开这些监听，并使用sqlplus登陆测试

数据库内一些参数：
SYS@ bys3>show parameter service
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string bys3
SYS@ bys3>show parameter db_na
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string bys3
SYS@ bys3>show parameter db_unique
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string bys3
SYS@ bys3>show parameter db_domain
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_domain string
SYS@ bys3>show parameter instance_na

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_name string bys3
关于这几个参数的设置及替换，详见：ORACLE监听之动态监听与静态监听特点

配置默认及非默认动态监听

1.配置listener.ora，增加非默认动态监听的地址、端口条目

[oracle@bys3 admin]$ more listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = bys3.bys.com)(PORT = 1525))
(ADDRESS = (PROTOCOL = TCP)(HOST = bys3.bys.com)(PORT = 1521))
)
)

ADR_BASE_LISTENER = /u01

2.在数据库中配置 local_listener参数,语句如下：--就是把listener.ora中的几行复制过来，注意换行的括号不要少了。

alter system set local_listener='(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = bys3.bys.com)(PORT = 1525))
(ADDRESS = (PROTOCOL = TCP)(HOST = bys3.bys.com)(PORT = 1521)))';
SYS@ bys3>alter system register; --手动注册，或者等上一分钟，让PMON自动进行动态监听注册。

3.查看监听状态，动态监听已经注册成功：

[oracle@bys3 ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 14-JAN-2014 22:20:16
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=bys3.bys.com)(PORT=1525)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 14-JAN-2014 22:02:22
Uptime 0 days 0 hr. 17 min. 54 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/diag/tnslsnr/bys3/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=bys3.bys.com)(PORT=1525)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=bys3.bys.com)(PORT=1521)))
Services Summary...
Service "bys3" has 1 instance(s).
Instance "bys3", status READY, has 1 handler(s) for this service...
The command completed successfully

4.使用SQLPLUS进行登陆测试：

[oracle@bys3 admin]$ cat tnsnames.ora
bys3 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = bys3.bys.com)(PORT =1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = bys3)
)
)
test =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = bys3.bys.com)(PORT =1525))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = bys3)
)
)
[oracle@bys3 admin]$ tnsping test
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 14-JAN-2014 22:30:42
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = bys3.bys.com)(PORT =1525)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = bys3)))
OK (0 msec)
[oracle@bys3 admin]$ tnsping bys3
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 14-JAN-2014 22:30:46
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = bys3.bys.com)(PORT =1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = bys3)))
OK (10 msec)
[oracle@bys3 admin]$ sqlplus bys/bys@bys3
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 14 22:32:02 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
BYS@ bys3>exit

[oracle@bys3 admin]$ sqlplus bys/bys@test
BYS@ test>exit
###############################################################

配置静态监听：

1.配置listener.ora，增加相应条目

[oracle@bys3 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = bys3.bys.com)(PORT = 1525))
(ADDRESS = (PROTOCOL = TCP)(HOST = bys3.bys.com)(PORT = 1521))
)
)

l3 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = bys3.bys.com)(PORT = 1526))
)
)
SID_LIST_l3=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=bys9)
(SID_NAME=bys3)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)))
ADR_BASE_LISTENER = /u01

2.启动监听，查看状态：

[oracle@bys3 ~]$ lsnrctl status l3
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 14-JAN-2014 22:19:49
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=bys3.bys.com)(PORT=1526)))
STATUS of the LISTENER
------------------------
Alias l3
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 14-JAN-2014 22:15:15
Uptime 0 days 0 hr. 4 min. 34 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/diag/tnslsnr/bys3/l3/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=bys3.bys.com)(PORT=1526)))
Services Summary...
Service "bys9" has 1 instance(s).
Instance "bys3", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
#################

3.使用SQLPLUS进行登陆测试

[oracle@bys3 admin]$ cat tnsnames.ora
test9 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = bys3.bys.com)(PORT =1526))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = bys9)
)
)
[oracle@bys3 ~]$ tnsping test9
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 14-JAN-2014 22:37:56
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = bys3.bys.com)(PORT =1526))(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = bys9)))
OK (10 msec)
[oracle@bys3 ~]$ sqlplus bys/bys@test9
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 14 22:38:05 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
BYS@ test9>

声明

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn

说明InnoDB重做日志和撤消日志的作用。Apr 15, 2025 am 12:16 AM

InnoDB使用redologs和undologs确保数据一致性和可靠性。1.redologs记录数据页修改，确保崩溃恢复和事务持久性。2.undologs记录数据原始值，支持事务回滚和MVCC。

在解释输出（类型，键，行，额外）中要查找的关键指标是什么？Apr 15, 2025 am 12:15 AM

EXPLAIN命令的关键指标包括type、key、rows和Extra。1）type反映查询的访问类型，值越高效率越高，如const优于ALL。2）key显示使用的索引，NULL表示无索引。3）rows预估扫描行数，影响查询性能。4）Extra提供额外信息，如Usingfilesort提示需要优化。

在解释中使用临时状态以及如何避免它是什么？Apr 15, 2025 am 12:14 AM

Usingtemporary在MySQL查询中表示需要创建临时表，常见于使用DISTINCT、GROUPBY或非索引列的ORDERBY。可以通过优化索引和重写查询避免其出现，提升查询性能。具体来说，Usingtemporary出现在EXPLAIN输出中时，意味着MySQL需要创建临时表来处理查询。这通常发生在以下情况：1)使用DISTINCT或GROUPBY时进行去重或分组；2)ORDERBY包含非索引列时进行排序；3)使用复杂的子查询或联接操作。优化方法包括：1)为ORDERBY和GROUPB

描述不同的SQL交易隔离级别（读取未读取，读取，可重复的读取，可序列化）及其在MySQL/InnoDB中的含义。Apr 15, 2025 am 12:11 AM

MySQL/InnoDB支持四种事务隔离级别：ReadUncommitted、ReadCommitted、RepeatableRead和Serializable。1.ReadUncommitted允许读取未提交数据，可能导致脏读。2.ReadCommitted避免脏读，但可能发生不可重复读。3.RepeatableRead是默认级别，避免脏读和不可重复读，但可能发生幻读。4.Serializable避免所有并发问题，但降低并发性。选择合适的隔离级别需平衡数据一致性和性能需求。

MySQL与其他数据库：比较选项Apr 15, 2025 am 12:08 AM

MySQL适合Web应用和内容管理系统，因其开源、高性能和易用性而受欢迎。1)与PostgreSQL相比，MySQL在简单查询和高并发读操作上表现更好。2)相较Oracle，MySQL因开源和低成本更受中小企业青睐。3)对比MicrosoftSQLServer，MySQL更适合跨平台应用。4)与MongoDB不同，MySQL更适用于结构化数据和事务处理。

MySQL索引基数如何影响查询性能？Apr 14, 2025 am 12:18 AM

MySQL索引基数对查询性能有显着影响：1.高基数索引能更有效地缩小数据范围，提高查询效率；2.低基数索引可能导致全表扫描，降低查询性能；3.在联合索引中，应将高基数列放在前面以优化查询。

MySQL：新用户的资源和教程Apr 14, 2025 am 12:16 AM

MySQL学习路径包括基础知识、核心概念、使用示例和优化技巧。1)了解表、行、列、SQL查询等基础概念。2)学习MySQL的定义、工作原理和优势。3)掌握基本CRUD操作和高级用法，如索引和存储过程。4)熟悉常见错误调试和性能优化建议，如合理使用索引和优化查询。通过这些步骤，你将全面掌握MySQL的使用和优化。