Home Database Mysql Tutorial 烂泥:mysql5.5多实例部署

烂泥:mysql5.5多实例部署

Jun 07, 2016 pm 02:55 PM
world Example deploy starter

本文 首发于 烂泥行天下 。 mysql5.5数据库多实例部署,我们可以分以下几个步骤来完成。 1、 mysql多实例的原理 2、 mysql多实例的特点 3、 mysql多实例应用场景 4、 mysql5.5多实例部署方法 一、mysql多实例的原理 mysql多实例,简单的说,就是在一台服务器

本文首发于烂泥行天下

mysql5.5数据库多实例部署,我们可以分以下几个步骤来完成。

1、 mysql多实例的原理

2、 mysql多实例的特点

3、 mysql多实例应用场景

4、 mysql5.5多实例部署方法

一、mysql多实例的原理

mysql多实例,简单的说,就是在一台服务器上开启多个不同的mysql服务端口(如3306,3307),运行多个mysql服务进程。这些服务进程通过不同的socket监听不同的服务端口,来提供各自的服务。

这些mysql实例共用一套mysql安装程序,使用不同的my.cnf配置文件、启动程序、数据文件。在提供服务时,mysql多实例在逻辑上看来是各自独立的,各个实例之间根据配置文件的设定值,来取得服务器的相关硬件资源。

二、mysql多实例的特点

2.1 有效的利用服务器资源

当单个服务器资源有剩余时,可以充分利用剩余的服务器资源来提供更多的服务。

2.2 节约服务器资源

当公司资金紧张,但是数据库需要各自提供独立服务,而且需要主从同步等技术时,使用多实例就最好了。

2.3 出现资源互相抢占问题

当某个实例服务并发很高或者有慢查询时,会消耗服务器更多的内存、CPU、磁盘IO等资源,这时就会导致服务器上的其它实例提供访问的质量下降,出现服务器资源互相抢占的现象。

三、mysql多实例应用场景

3.1 资金紧张型公司的选择

当公司业务访问量不太大,又舍不得花钱,但同时又希望不同业务的数据库服务各自独立,而且需要主从同步进行等技术提供备份或读写分离服务时,使用多实例是最好不过的。

3.2 并发访问不是特别大的业务

当公司业务访问量不太大,服务器资源基本闲置的比较多,这是就很适合多实例的应用。如果对SQL语句优化的好,多实例是一个很值得使用的技术。即使并发很大,只要合理分配好系统资源,也不会有太大问题。

四、mysql5.5多实例部署方法

4.1 mysql5.5多实例部署方法

mysql5.5多实例部署方法一个是通过多个配置文件启动多个不同进程的方法,第二个是使用官方自带的mysqld_multi来实现。

第一种方法我们可以把各个实例的配置文件分开,管理比较方便。第二种方法就是把多个实例都放到一个配置文件中,这个管理不是很方便。所以在此我们选择第一种方法,而且以下实验我们全部是在此方法下进行的。

4.2 mysql5.5的安装及配置

要配置mysql5.5多实例,我们首先要安装mysql5.5,有关mysql5.5的安装可以查看《烂泥:mysql5.5数据库cmake源码编译安装》这篇文章。

mysql5.5安装完毕后,我们不要启动mysql,因为此时mysql是单实例的。

4.3 创建mysql多实例的数据目录

现在我们来创建mysql5.5多实例的数据目录,在此我们创建两个mysql实例3306和3307。创建各自的数据目录,如下:

mkdir -p /data/{3306,3307}/data

tree -L 2 /data/

clip_image001

4.4 修改mysql5.5多实例my.cnf文件

实例3306和3307的数据目录创建完毕后,我们来配置实例3306与3307的my.cnf配置文件。

复制mysql5.5安装目录support-files下的my-medium.cnf为my.cnf,并把内容修改为下。现在以3306这个实例为例,如下:

[client]

port = 3306

socket = /data/3306/mysql.sock

[mysqld]

port = 3306

socket = /data/3306/mysql.sock

basedir = /usr/local/mysql

datadir = /data/3306/data

skip-external-locking

key_buffer_size = 16M

max_allowed_packet = 1M

table_open_cache = 64

sort_buffer_size = 512K

net_buffer_length = 8K

read_buffer_size = 256K

read_rnd_buffer_size = 512K

myisam_sort_buffer_size = 8M

skip-name-resolve

log-bin=mysql-bin

binlog_format=mixed

max_binlog_size = 500M

server-id = 1

[mysqld_safe]

log-error=/data/3306/ilanni.err

pid-file=/data/3306/ilanni.pid

[mysqldump]

quick

max_allowed_packet = 16M

[mysql]

no-auto-rehash

[myisamchk]

key_buffer_size = 20M

sort_buffer_size = 20M

read_buffer = 2M

write_buffer = 2M

[mysqlhotcopy]

interactive-timeout

clip_image002

注意图中***标记出来的部分,就是我们主要修改的,其他默认保持不变。

以上是实例3306的my.cnf配置文件,现在我们来配置实例3307的my.cnf。实例3307的配置文件my.cnf我们直接复制实例3306的my.cnf文件,然后通过sed命令把该文件中的3306修改为3307即可。如下:

cp /data/3306/my.cnf /data/3307/my.cnf

sed -i 's/3306/3307/g' /data/3307/my.cnf

或者

sed -e 's/3306/3307/g' /data/3306/my.cnf >/data/3307/my.cnf

clip_image001[4]

4.5 初始化mysql多实例

实例3306和3307的my.cnf配置文件修改完毕后,我们需要来初始化这两个实例,使用mysql_install_db命令。如下:

/usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/data/3306/data --user=mysql

/usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/data/3307/data --user=mysql

注意mysql5.5的mysql_install_db在mysql5.5的/usr/local/mysql/scripts/mysql_install_db目录下。

clip_image004

clip_image005

查看实例初始化后的情况,如下:

tree -L 3 /data/

clip_image006

通过上图我们可以看到mysql实例在初始化后会创建基本的数据库。

现在再来看看初始化创建文件的属性,如下:

clip_image007

通过上图可以看到初始化创建的文件都是属于mysql这个用户的。

为什么会是这样呢?

这个是因为我们初始化加入--user=mysql这个选项。当然这个也是我们所需要的,因为这增加了mysql的安全性。

4.6 修改mysql实例的数据库目录权限

mysql实例初始化完毕后,我们现在把实例3306和实例3307的数据目录权限重新赋予给mysql用户。如下:

chown -R mysql:mysql /data/3306

chown -R mysql:mysql /data/3307

clip_image008

这个地方建议一定要操作一遍,否则在启动mysql实例时,会提示出错。导致mysql实例无法启动。

4.7 启动mysql5.5多实例

我们现在来启动实例。使用如下命令:

/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/3306/my.cnf &

/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/3307/my.cnf &

ps aux |grep mysqld

clip_image009

通过上图,我们可以看到实例3306和3307 都已经正常启动。也说明我们的mysql5.5多实例已经配置成功。

其实单实例mysql的启动也是通过mysqld_safe命令来启动。它默认会加载/etc/my.cnf文件。

4.8 登录mysql5.5多实例

登录多实例数据库时,我们需要加入该实例的socket文件,才能正常登录。现在以3306实例为例。

本地登录3306实例,如下:

mysql -uroot -p -S /data/3306/mysql.sock

clip_image010

本地登录进去后,我们在实例3306上创建一个ilanni3306的数据库。如下:

create database ilanni3306;

show databases;

clip_image011

现在我们远程登录实例3306,并查看刚刚新建的数据库。如下:

mysql -h192.168.1.213 -uroot -p -S /data/3306/mysql.sock

clip_image012

或者:mysql -h192.168.1.213 -uroot -p –P 3306

wKiom1R2ytKwKGhPAAHVloWUMaY090.jpg

通过上图,我们可以看到远程也是可以连接3306实例的。

4.9 修改mysql5.5多实例root密码

修改实例3306的root密码,使用mysqladmin命令。如下:

mysqladmin -uroot -p password 123456 -S /data/3306/mysql.sock

clip_image013

到此我们的mysql多实例部署就已经完成。

Statement of this Website
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

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

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

How to solve the problem of inaccessibility after Tomcat deploys war package How to solve the problem of inaccessibility after Tomcat deploys war package Jan 13, 2024 pm 12:07 PM

How to solve the problem that Tomcat cannot successfully access the war package after deploying it requires specific code examples. As a widely used Java Web server, Tomcat allows developers to package their own developed Web applications into war files for deployment. However, sometimes we may encounter the problem of being unable to successfully access the war package after deploying it. This may be caused by incorrect configuration or other reasons. In this article, we'll provide some concrete code examples that address this dilemma. 1. Check Tomcat service

Yolov10: Detailed explanation, deployment and application all in one place! Yolov10: Detailed explanation, deployment and application all in one place! Jun 07, 2024 pm 12:05 PM

1. Introduction Over the past few years, YOLOs have become the dominant paradigm in the field of real-time object detection due to its effective balance between computational cost and detection performance. Researchers have explored YOLO's architectural design, optimization goals, data expansion strategies, etc., and have made significant progress. At the same time, relying on non-maximum suppression (NMS) for post-processing hinders end-to-end deployment of YOLO and adversely affects inference latency. In YOLOs, the design of various components lacks comprehensive and thorough inspection, resulting in significant computational redundancy and limiting the capabilities of the model. It offers suboptimal efficiency, and relatively large potential for performance improvement. In this work, the goal is to further improve the performance efficiency boundary of YOLO from both post-processing and model architecture. to this end

Gunicorn Deployment Guide for Flask Applications Gunicorn Deployment Guide for Flask Applications Jan 17, 2024 am 08:13 AM

How to deploy Flask application using Gunicorn? Flask is a lightweight Python Web framework that is widely used to develop various types of Web applications. Gunicorn (GreenUnicorn) is a Python-based HTTP server used to run WSGI (WebServerGatewayInterface) applications. This article will introduce how to use Gunicorn to deploy Flask applications, with

Best practices and common problem solutions for deploying web projects on Tomcat Best practices and common problem solutions for deploying web projects on Tomcat Dec 29, 2023 am 08:21 AM

Best practices for deploying Web projects with Tomcat and solutions to common problems Introduction: Tomcat, as a lightweight Java application server, has been widely used in Web application development. This article will introduce the best practices and common problem solving methods for Tomcat deployment of web projects, and provide specific code examples to help readers better understand and apply. 1. Project directory structure planning Before deploying a Web project, we need to plan the directory structure of the project. Generally speaking, we can organize it in the following way

Deploy the unbeatable combination of Gunicorn and Flask Deploy the unbeatable combination of Gunicorn and Flask Jan 17, 2024 am 10:24 AM

Gunicorn and Flask: The perfect deployment combination, specific code examples required Overview: It is very important for developers to choose the appropriate deployment method, especially for Python web applications. Among Python web frameworks, Flask is a very popular choice, and Gunicorn is a server for deploying Python applications. This article will introduce the combination of Gunicorn and Flask and provide some specific code examples to help readers

PHP Jenkins 101: The only way to get started with CI/CD PHP Jenkins 101: The only way to get started with CI/CD Mar 09, 2024 am 10:28 AM

Introduction Continuous integration (CI) and continuous deployment (CD) are key practices in modern software development that help teams deliver high-quality software faster and more reliably. Jenkins is a popular open source CI/CD tool that automates the build, test and deployment process. This article explains how to set up a CI/CD pipeline with Jenkins using PHP. Set up Jenkins Install Jenkins: Download and install Jenkins from the official Jenkins website. Create project: Create a new project from the Jenkins dashboard and name it to match your php project. Configure source control: Configure your PHP project's git repository as Jenkin

How to deploy and maintain a website using PHP How to deploy and maintain a website using PHP May 03, 2024 am 08:54 AM

To successfully deploy and maintain a PHP website, you need to perform the following steps: Select a web server (such as Apache or Nginx) Install PHP Create a database and connect PHP Upload code to the server Set up domain name and DNS Monitoring website maintenance steps include updating PHP and web servers, and backing up the website , monitor error logs and update content.

How to solve the problem of inaccessibility after Tomcat deploys war package How to solve the problem of inaccessibility after Tomcat deploys war package Jan 13, 2024 am 11:43 AM

The solution to the problem that Tomcat cannot be accessed after deploying the war package requires specific code examples. Introduction: In Web development, Tomcat is one of the most widely used Java Web servers. However, sometimes after we deploy the war package to Tomcat, there is an inaccessible problem. This article will introduce several situations that may lead to inaccessibility, and give corresponding solutions and code examples. 1. Ensure that the war package has been deployed correctly. The first step is to ensure that the war package has been correctly deployed to Tomcat’s webapp.

See all articles