开发笔记:mysql集成部署

[client]
no-beep
port=3307
[mysql]
default-character-set=utf8
# server_type=3
[mysqld]
# The TCP/IP Port the MySQL Server will listen on
port=3307
# Path to installation directory. All paths are usually resolved relative to this.
# basedir="C:/Program Files/MySQL/MySQL Server 5.7/"
# Path to the database root
datadir=G:\\mysql572\\Data
# The default character set that will be used when a new schema or table is
# created and no character set is defined
character-set-server=utf8
# The default storage engine that will be used when create new tables when
default-storage-engine=INNODB
# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
# Enable Windows Authentication
# plugin-load=authentication_windows.dll
# General and Slow logging.
log-output=FILE
general-log=0
general_log_file="MicroWin10-1535.log"
slow-query-log=1
slow_query_log_file="MicroWin10-1535-slow.log"
long_query_time=10
# Binary Logging.
log-error="MicroWin10-1535.err"
# Server Id.
server-id=1
# Secure File Priv.
secure-file-priv="G:\\mysql572\\Uploads"
max_connections=151
query_cache_size=0
table_open_cache=2000
tmp_table_size=34M
thread_cache_size=10
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=60M
key_buffer_size=8M
read_buffer_size=64K
read_rnd_buffer_size=256K
#*** INNODB Specific options ***
# innodb_data_home_dir=0.0
# Use this option if you have a MySQL server with InnoDB support enabled
# but you do not plan to use it. This will save memory and disk space
# and speed up some things.
# skip-innodb
# If set to 1, InnoDB will flush (fsync) the transaction logs to the
# disk at each commit, which offers full ACID behavior. If you are
# willing to compromise this safety, and you are running small
# transactions, you may set this to 0 or 2 to reduce disk I/O to the
# logs. Value 0 means that the log is only written to the log file and
# the log file flushed to disk approximately once per second. Value 2
# means the log is written to the log file at each commit, but the log
# file is only flushed to disk approximately once per second.
innodb_flush_log_at_trx_commit=1
# The size of the buffer InnoDB uses for buffering log data. As soon as
# it is full, InnoDB will have to flush it to disk. As it is flushed
# once per second anyway, it does not make sense to have it very large
# (even with long transactions).
innodb_log_buffer_size=1M
# InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and
# row data. The bigger you set this the less disk I/O is needed to
# access data in tables. On a dedicated database server you may set this
# parameter up to 80% of the machine physical memory size. Do not set it
# too large, though, because competition of the physical memory may
# cause paging in the operating system. Note that on 32bit systems you
# might be limited to 2-3.5G of user level memory per process, so do not
# set it too high.
innodb_buffer_pool_size=8M
# Size of each log file in a log group. You should set the combined size
# of log files to about 25%-100% of your buffer pool size to avoid
# unneeded buffer pool flush activity on log file overwrite. However,
# note that a larger logfile size will increase the time needed for the
# recovery process.
innodb_log_file_size=48M
# Number of threads allowed inside the InnoDB kernel. The optimal value
# depends highly on the application, hardware as well as the OS
# scheduler properties. A too high value may lead to thread thrashing.
innodb_thread_concurrency=9
# The increment size (in MB) for extending the size of an auto-extend InnoDB system tablespace file when it becomes full.
innodb_autoextend_increment=64
# The number of regions that the InnoDB buffer pool is divided into.
# For systems with buffer pools in the multi-gigabyte range, dividing the buffer pool into separate instances can improve concurrency,
# by reducing contention as different threads read and write to cached pages.
innodb_buffer_pool_instances=8
# Determines the number of threads that can enter InnoDB concurrently.
innodb_concurrency_tickets=5000
# Specifies how long in milliseconds (ms) a block inserted into the old sublist must stay there after its first access before
# it can be moved to the new sublist.
innodb_old_blocks_time=1000
# It specifies the maximum number of .ibd files that MySQL can keep open at one time. The minimum value is 10.
innodb_open_files=300
# When this variable is enabled, InnoDB updates statistics during metadata statements.
innodb_stats_on_metadata=0
# When innodb_file_per_table is enabled (the default in 5.6.6 and higher), InnoDB stores the data and indexes for each newly created table
# in a separate .ibd file, rather than in the system tablespace.
innodb_file_per_table=1
innodb_checksum_algorithm=0
back_log=80
flush_time=0
join_buffer_size=256K
max_allowed_packet=4M
max_connect_errors=100
open_files_limit=4161
query_cache_type=0
sort_buffer_size=256K
table_definition_cache=1400
binlog_row_event_max_size=8K
sync_master_info=10000
sync_relay_log=10000
sync_relay_log_info=10000

G:\\mysql572\\bin>mysqld --defaults-file=G:\\mysql572\\my.ini

C:\\Users\\liqiang>tasklist |findstr mysql
mysqld.exe 1912 Services 0 228,348 K
mysqld.exe 325752 Console 9 208,488 K
mysql.exe 325984 Console 9 5,328 K
C:\\Users\\liqiang>netstat -ano | findstr 3307
TCP 0.0.0.0:3307 0.0.0.0:0 LISTENING 325752
TCP [::]:3307 [::]:0 LISTENING 325752
TCP [::1]:3307 [::1]:61234 ESTABLISHED 325752
TCP [::1]:61234 [::1]:3307 ESTABLISHED 325984

C:\\Users\\liqiang>mysql -uroot -p -P3307
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \\g.
Your MySQL connection id is 2
Server version: 5.7.10-log MySQL Community Server (GPL)
Copyright (c) 2000, 2015, 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.
mysql> show variables like \'log_error\';
+---------------+-----------------------+
| Variable_name | Value |
+---------------+-----------------------+
| log_error | .\\MicroWin10-1535.err |
+---------------+-----------------------+
1 row in set, 1 warning (0.01 sec)
mysql> show variables like \'datadir\';
+---------------+-------------------+
| Variable_name | Value |
+---------------+-------------------+
| datadir | G:\\mysql572\\Data\\ |
+---------------+-------------------+
1 row in set, 1 warning (0.00 sec)

C:\\Windows\\system32>G:\\mysql572\\bin\\mysqld --install mysql572 --defaults-file="G:\\mysql572\\my.ini"
Service successfully installed.

C:\\Windows\\system32>sc qc mysql572
[SC] QueryServiceConfig 成功
SERVICE_NAME: mysql572
TYPE : 10 WIN32_OWN_PROCESS
START_TYPE : 2 AUTO_START
ERROR_CONTROL : 1 NORMAL
BINARY_PATH_NAME : G:\\mysql572\\bin\\mysqld --defaults-file=G:\\mysql572\\
my.ini mysql572
LOAD_ORDER_GROUP :
TAG : 0
DISPLAY_NAME : mysql572
DEPENDENCIES :
SERVICE_START_NAME : LocalSystem

sc delete mysql572
或者采用mysqld方式移除系统服务

mysqld --remove MySQL57

show global variables like \'%datadir%\'