MySQL维护与实际应用的设计笔记简介-Mysql Tutorial-php.cn

Home

Database

Mysql Tutorial

MySQL维护与实际应用的设计笔记简介

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 04:09 PM

mysqlactualarticleusenotesIntroductionmaintaindesign

以下的文章主要讲述是正确使用MySQL(和PHP搭配之最佳组合)服务的实际经验汇总，以及MySQL维护与其实际应用设计笔记的相关内容描述，此文章主要下面的几个实际方面考虑的MySQL(和PHP搭配之最佳组合)服务规划设计。 1 MySQL(和PHP搭配之最佳组合)服务的安装/配

以下的文章主要讲述是正确使用MySQL(和PHP搭配之最佳组合)服务的实际经验汇总，以及MySQL维护与其实际应用设计笔记的相关内容描述，此文章主要下面的几个实际方面考虑的MySQL(和PHP搭配之最佳组合)服务规划设计。

1 MySQL(和PHP搭配之最佳组合)服务的安装/配置的通用性；

2 系统的升级和数据迁移方便性；

3 备份和系统快速恢复；

MySQL(和PHP搭配之最佳组合)服务器的规划

为了以后MySQL维护，升级备份的方便和数据的安全性，最好将MySQL(和PHP搭配之最佳组合)程序文件和数据分别安装在“不同的硬件”上。

/usr 硬盘1

/home/MySQL(和PHP搭配之最佳组合)

...

/data/app_1/ 硬盘2

/data/app_2/

/data/app_3/

MySQL(和PHP搭配之最佳组合)服务的安装和服务的启动：

MySQL(和PHP搭配之最佳组合)一般使用当前STABLE的版本，尽量不使用--with-charset=选项，我感觉with-charset只在按字母排序的时候才有用，这些选项会对数据的迁移带来很多麻烦。

configure --prefix=/home/MySQL(和PHP搭配之最佳组合)

make

make install

服务的启动和停止

1 复制缺省的MySQL(维护和PHP搭配之最佳组合)/var/MySQL(和PHP搭配之最佳组合)到 /data/app_1/目录下

2 MySQL(和PHP搭配之最佳组合)D的启动脚本：

start_MySQL(和PHP搭配之最佳组合).sh

<ol class="dp-xml">
<li class="alt"><span><span>#!/bin/sh  </span></span></li>
<li>
<span class="attribute">rundir</span><span>=`dirname "$0"`  </span>
</li>
<li class="alt"><span>echo "$rundir" </span></li>
</ol>

/home/MySQL(和PHP搭配之最佳组合)/bin/safe_MySQL(和PHP搭配之最佳组合)d --user=MySQL(和PHP搭配之最佳组合) --pid-file="$rundir"/MySQL(和PHP搭配之最佳组合).pid --datadir="$rundir"/var "$@"\

-O max_connections=500 -O wait_timeout=600 -O key_buffer=32M --port=3402 --socket="$rundir"/MySQL(和PHP搭配之最佳组合).sock &

注释：

--pid-file="$rundir"/MySQL(和PHP搭配之最佳组合).pid --socket="$rundir"/MySQL(和PHP搭配之最佳组合).sock --datadir="$rundir"/var

目的都是将相应数据和应用临时文件放在一起；

-O 后面一般是服务器启动全局变量优化参数，有时候需要根据具体应用调整；

--port: 不同的应用使用PORT参数分布到不同的服务上去，一个服务可以提供的连接数一般是MySQL维护(和PHP搭配之最佳组合)服务的主要瓶颈；

修改不同的服务到不同的端口后，在rc.local文件中加入：

/data/app_1/start_MySQL(和PHP搭配之最佳组合).sh

/data/app_2/start_MySQL(和PHP搭配之最佳组合).sh

/data/app_3/start_MySQL(和PHP搭配之最佳组合).sh

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

MySQL: BLOB and other no-sql storage, what are the differences?May 13, 2025 am 12:14 AM

MySQL'sBLOBissuitableforstoringbinarydatawithinarelationaldatabase,whileNoSQLoptionslikeMongoDB,Redis,andCassandraofferflexible,scalablesolutionsforunstructureddata.BLOBissimplerbutcanslowdownperformancewithlargedata;NoSQLprovidesbetterscalabilityand

MySQL Add User: Syntax, Options, and Security Best PracticesMay 13, 2025 am 12:12 AM

ToaddauserinMySQL,use:CREATEUSER'username'@'host'IDENTIFIEDBY'password';Here'showtodoitsecurely:1)Choosethehostcarefullytocontrolaccess.2)SetresourcelimitswithoptionslikeMAX_QUERIES_PER_HOUR.3)Usestrong,uniquepasswords.4)EnforceSSL/TLSconnectionswith

MySQL: How to avoid String Data Types common mistakes?May 13, 2025 am 12:09 AM

ToavoidcommonmistakeswithstringdatatypesinMySQL,understandstringtypenuances,choosetherighttype,andmanageencodingandcollationsettingseffectively.1)UseCHARforfixed-lengthstrings,VARCHARforvariable-length,andTEXT/BLOBforlargerdata.2)Setcorrectcharacters

MySQL: String Data Types and ENUMs?May 13, 2025 am 12:05 AM

MySQloffersechar, Varchar, text, Anddenumforstringdata.usecharforfixed-Lengthstrings, VarcharerForvariable-Length, text forlarger text, AndenumforenforcingdataAntegritywithaetofvalues.

MySQL BLOB: how to optimize BLOBs requestsMay 13, 2025 am 12:03 AM

Optimizing MySQLBLOB requests can be done through the following strategies: 1. Reduce the frequency of BLOB query, use independent requests or delay loading; 2. Select the appropriate BLOB type (such as TINYBLOB); 3. Separate the BLOB data into separate tables; 4. Compress the BLOB data at the application layer; 5. Index the BLOB metadata. These methods can effectively improve performance by combining monitoring, caching and data sharding in actual applications.

Adding Users to MySQL: The Complete TutorialMay 12, 2025 am 12:14 AM

Mastering the method of adding MySQL users is crucial for database administrators and developers because it ensures the security and access control of the database. 1) Create a new user using the CREATEUSER command, 2) Assign permissions through the GRANT command, 3) Use FLUSHPRIVILEGES to ensure permissions take effect, 4) Regularly audit and clean user accounts to maintain performance and security.

Mastering MySQL String Data Types: VARCHAR vs. TEXT vs. CHARMay 12, 2025 am 12:12 AM

ChooseCHARforfixed-lengthdata,VARCHARforvariable-lengthdata,andTEXTforlargetextfields.1)CHARisefficientforconsistent-lengthdatalikecodes.2)VARCHARsuitsvariable-lengthdatalikenames,balancingflexibilityandperformance.3)TEXTisidealforlargetextslikeartic

MySQL: String Data Types and Indexing: Best PracticesMay 12, 2025 am 12:11 AM

Best practices for handling string data types and indexes in MySQL include: 1) Selecting the appropriate string type, such as CHAR for fixed length, VARCHAR for variable length, and TEXT for large text; 2) Be cautious in indexing, avoid over-indexing, and create indexes for common queries; 3) Use prefix indexes and full-text indexes to optimize long string searches; 4) Regularly monitor and optimize indexes to keep indexes small and efficient. Through these methods, we can balance read and write performance and improve database efficiency.

See all articles