经常听说mysql数据库是集成在系统中，也一直不太明白集成的概念。今天才明白集成的概念就是将mysql所有的文件放到一个文件夹下放到系统中，也就是将mysql采用目录迁移部署的方式进行安装。在上一篇研究了mysql的数据存储结构之后，也研究了mysql作为目录部署以及安装为mysql服务的方法。
1.mysql目录安装
mysql目录安装也就是将mysql必须的一些文件放到一起，然后通过配置文件的配置即可实现。
mysql主要的目录也就是data目录以及bin(存mysql的可执行文件)、lib(存放mysql的dll库)、share(存放mysql的语言支持库)、uploads(一般作为mysql的导出目录)、my.ini(mysql的端口以及其他全局配置文件)。
例如我的目录安装的一个目录:
bin、lib、share、Uploads是直接从mysql5.7的安装目录下拷贝过来的，Data目录也是拷贝的，上篇文章已经讲解过Data目录下存放具体的Mysql的数据库以及表结构与表数据，查看Data的目录如下(是在原来的数据库迁移古来，所以如果集成部署成功数据库应该与原来一样):
my.ini文件:(注意标红位置的修改)
[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:mysql572Data # 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:mysql572Uploads" 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
2.启动上面的mysql并且进行连接
G:mysql572in>mysqld --defaults-file=G:mysql572my.ini
新开cmd窗口查看进程信息:
C:Usersliqiang>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:Usersliqiang>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
连接上面启动的3307端口的mysql:
C:Usersliqiang>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:mysql572Data | +---------------+-------------------+ 1 row in set, 1 warning (0.00 sec)
3.将上面目录安装的mysql安装为windows服务
需以管理员身份执行cmd
C:Windowssystem32>G:mysql572inmysqld --install mysql572 --defaults-file="G:mysql572my.ini"
Service successfully installed.
查看上面安装的服务:
C:Windowssystem32>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:mysql572inmysqld --defaults-file=G:mysql572 my.ini mysql572 LOAD_ORDER_GROUP : TAG : 0 DISPLAY_NAME : mysql572 DEPENDENCIES : SERVICE_START_NAME : LocalSystem
安装成功之后即可像正常的服务一样使用，启动可以用sc start mysql
删除服务也与平时删除服务一样:
sc delete mysql572
或者采用mysqld方式移除系统服务
mysqld --remove MySQL57
至此mysql集成安装完成，在实际的项目中我们可以采用这种方式将mysql嵌入到系统中进行部署安装。至于my.ini的配置在需要啥全局变量设置的时候在这里设置。
补充：查看mysql数据目录可以查看全局参数datadir
show global variables like '%datadir%'