首页 / MYSQL / mysqld_multi多实例部署

mysqld_multi多实例部署

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了mysqld_multi多实例部署，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含11929字，纯文字阅读大概需要18分钟。

内容图文

mysql多实例部署

mysql多实例部署
- 下载软件
- 配置用户和组并解压二进制程序至/usr/local下
- 创建实例数据存放的目录
- 初始化各实例
- 配置配置文件/etc/my.cnf
- 启动各实例
- 初始化密码
- 关闭实例

下载软件

[root@localhost ~]# ls
anaconda-ks.cfg  mysql-5.7.29-linux-glibc2.12-x86_64.tar.gz
#安装perl
[root@localhost ~]# yum -y install perl

配置用户和组并解压二进制程序至/usr/local下

#创建用户和组
[root@localhost ~]# groupadd -r mysql
[root@localhost ~]# useradd -r -M -s /sbin/nologin -g mysql mysql

#解压mysql到/usr/local/
[root@localhost ~]# tar xf mysql-5.7.29-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@localhost ~]# ls /usr/local/
bin  games    lib    libexec                              sbin   src
etc  include  lib64  mysql-5.7.29-linux-glibc2.12-x86_64  share
[root@localhost local]# ln -s mysql-5.7.29-linux-glibc2.12-x86_64/ mysql
[root@localhost local]# ll
total 0
drwxr-xr-x. 2 root root   6 Apr 11  2018 bin
drwxr-xr-x. 2 root root   6 Apr 11  2018 etc
drwxr-xr-x. 2 root root   6 Apr 11  2018 games
drwxr-xr-x. 2 root root   6 Apr 11  2018 include
drwxr-xr-x. 2 root root   6 Apr 11  2018 lib
drwxr-xr-x. 2 root root   6 Apr 11  2018 lib64
drwxr-xr-x. 2 root root   6 Apr 11  2018 libexec
lrwxrwxrwx. 1 root root  36 Jun 18 22:20 mysql -> mysql-5.7.29-linux-glibc2.12-x86_64/
drwxr-xr-x. 9 root root 129 Jun 18 22:20 mysql-5.7.29-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root   6 Apr 11  2018 sbin
drwxr-xr-x. 5 root root  49 Apr  7 04:48 share
drwxr-xr-x. 2 root root   6 Apr 11  2018 src

#修改目录/usr/local/mysql的属主属组
[root@localhost ~]# chown -R mysql.mysql /usr/local/mysql
[root@localhost ~]# ll -d /usr/local/mysql 
lrwxrwxrwx. 1 mysql mysql 36 Jun 18 22:20 /usr/local/mysql -> mysql-5.7.29-linux-glibc2.12-x86_64

#配置环境变量
[root@localhost ~]# echo ‘export PATH=/usr/local/mysql/bin:$PATH‘ > /etc/profile.d/mysql.sh
[root@localhost ~]# . /etc/profile.d/mysql.sh
[root@localhost ~]# echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin

创建实例数据存放的目录

[root@localhost ~]# mkdir -p /opt/data/{3306,3307,3308}
[root@localhost ~]# chown -R mysql.mysql /opt/data/

初始化各实例

[root@localhost ~]# /usr/local/mysql/bin/mysqld --initialize --datadir=/opt/data/3306/ --user=mysql
2020-06-19T02:26:51.119557Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-06-19T02:26:51.289336Z 0 [Warning] InnoDB: New log files created, LSN=45790
2020-06-19T02:26:51.317903Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2020-06-19T02:26:51.373831Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 557e31d8-b1d4-11ea-8616-000c29d7d941.
2020-06-19T02:26:51.374438Z 0 [Warning] Gtid table is not ready to be used. Table ‘mysql.gtid_executed‘ cannot be opened.
2020-06-19T02:26:51.639794Z 0 [Warning] CA certificate ca.pem is self signed.
2020-06-19T02:26:51.679383Z 1 [Note] A temporary password is generated for root@localhost: rs3+GHrW/qo)
[root@localhost ~]# echo ‘rs3+GHrW/qo)‘ > 3306_pass


[root@localhost ~]# /usr/local/mysql/bin/mysqld --initialize --datadir=/opt/data/3307/ --user=mysql
2020-06-19T02:28:04.740186Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-06-19T02:28:04.905282Z 0 [Warning] InnoDB: New log files created, LSN=45790
2020-06-19T02:28:04.934119Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2020-06-19T02:28:04.988177Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 815eda45-b1d4-11ea-883b-000c29d7d941.
2020-06-19T02:28:04.988744Z 0 [Warning] Gtid table is not ready to be used. Table ‘mysql.gtid_executed‘ cannot be opened.
2020-06-19T02:28:05.572008Z 0 [Warning] CA certificate ca.pem is self signed.
2020-06-19T02:28:05.629436Z 1 [Note] A temporary password is generated for root@localhost: Zf&stNghV6Kl
[root@localhost ~]# echo ‘Zf&stNghV6Kl‘ > 3307_pass


[root@localhost ~]# /usr/local/mysql/bin/mysqld --initialize --datadir=/opt/data/3308/ --user=mysql
2020-06-19T02:28:50.562599Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-06-19T02:28:50.724362Z 0 [Warning] InnoDB: New log files created, LSN=45790
2020-06-19T02:28:50.751944Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2020-06-19T02:28:50.806578Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 9cae2f9d-b1d4-11ea-8b25-000c29d7d941.
2020-06-19T02:28:50.807144Z 0 [Warning] Gtid table is not ready to be used. Table ‘mysql.gtid_executed‘ cannot be opened.
2020-06-19T02:28:51.476355Z 0 [Warning] CA certificate ca.pem is self signed.
2020-06-19T02:28:51.808583Z 1 [Note] A temporary password is generated for root@localhost: 5fh/s=!924IX
[root@localhost ~]# echo ‘5fh/s=!924IX‘ > 3308_pass

配置配置文件/etc/my.cnf

[root@localhost ~]# vim /etc/my.cnf
[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
user       = multi_admin
password   = my_password

[mysqld3306]
datadir = /opt/data/3306
port = 3306
socket = /tmp/mysql3306.sock
pid-file = /opt/data/3306/mysql_3306.pid
log-error=/var/log/3306.log

[mysqld3307]
datadir = /opt/data/3307
port = 3307
socket = /tmp/mysql3307.sock
pid-file = /opt/data/3307/mysql_3307.pid
log-error=/var/log/3307.log

[mysqld3308]
datadir = /opt/data/3308
port = 3308
socket = /tmp/mysql3308.sock
pid-file = /opt/data/3308/mysql_3308.pid
log-error=/var/log/3308.log

启动各实例

[root@localhost ~]# /usr/local/mysql/bin/mysqld_multi start 3306
[root@localhost ~]# /usr/local/mysql/bin/mysqld_multi start 3307
[root@localhost ~]# /usr/local/mysql/bin/mysqld_multi start 3308
[root@localhost ~]# /usr/local/mysql/bin/mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3307 is running
MySQL server from group: mysqld3308 is running
[root@localhost ~]# ss -tanl
State      Recv-Q Send-Q    Local Address:Port                   Peer Address:Port              
LISTEN     0      100           127.0.0.1:25                                *:*                  
LISTEN     0      128                   *:22                                *:*                  
LISTEN     0      100               [::1]:25                             [::]:*                  
LISTEN     0      80                 [::]:3306                           [::]:*                  
LISTEN     0      80                 [::]:3307                           [::]:*                  
LISTEN     0      80                 [::]:3308                           [::]:*                  
LISTEN     0      128                [::]:22                             [::]:*

初始化密码

[root@localhost ~]# ls
3306_pass  3307_pass  3308_pass  anaconda-ks.cfg  mysql-5.7.29-linux-glibc2.12-x86_64.tar.gz
[root@localhost ~]# cat 3306_pass 
rs3+GHrW/qo)
[root@localhost ~]# /usr/local/mysql/bin/mysql -uroot -p‘rs3+GHrW/qo)‘ -S /tmp/mysql3306.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.29

Copyright (c) 2000, 2020, 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> 
mysql> quit
Bye



[root@localhost ~]# cat 3307_pass 
Zf&stNghV6Kl
[root@localhost ~]# /usr/local/mysql/bin/mysql -uroot -p‘Zf&stNghV6Kl‘ -S /tmp/mysql3307.sock --connect-expired-password -e ‘set password=password("qwer!@#$")‘
mysql: [Warning] Using a password on the command line interface can be insecure.



[root@localhost ~]# cat 3308_pass 
5fh/s=!924IX
[root@localhost ~]# /usr/local/mysql/bin/mysql -uroot -p‘5fh/s=!924IX‘ -S /tmp/mysql3308.sock --connect-expired-password -e ‘set password=password("qwer!@#$")‘
mysql: [Warning] Using a password on the command line interface can be insecure.

关闭实例

#关闭3307实例
//需要在数据库实例上创建一个账户用来关闭数据库，该账户的用户名和密码和配置文件中[mysqld_multi] 中配置的一样
[root@localhost ~]# mysql -uroot -p‘qwer!@#$‘ -S /tmp/mysql3307.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.29 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, 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> GRANT SHUTDOWN ON *.* TO ‘multi_admin‘@‘localhost‘ IDENTIFIED BY ‘my_password‘;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

#复制222行注释，在原本的defaults后加上-s
[root@localhost ~]# vim /usr/local/mysql/bin/mysqld_multi
# my $com= join ‘ ‘, ‘my_print_defaults‘, @defaults_options, $group;
  my $com= join ‘ ‘, ‘my_print_defaults -s‘, @defaults_options, $group;

#关闭3307实例
[root@localhost ~]# mysqld_multi stop 3307
[root@localhost ~]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3307 is not running
MySQL server from group: mysqld3308 is running
[root@localhost ~]# ss -tanl
State      Recv-Q Send-Q    Local Address:Port                   Peer Address:Port              
LISTEN     0      100           127.0.0.1:25                                *:*                  
LISTEN     0      128                   *:22                                *:*                  
LISTEN     0      100               [::1]:25                             [::]:*                  
LISTEN     0      80                 [::]:3306                           [::]:*                  
LISTEN     0      80                 [::]:3308                           [::]:*                  
LISTEN     0      128                [::]:22                             [::]:*                  

#再启用3307实例
[root@localhost ~]# mysqld_multi start 3307
[root@localhost ~]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3307 is running
MySQL server from group: mysqld3308 is running
[root@localhost ~]# ss -tanl
State      Recv-Q Send-Q    Local Address:Port                   Peer Address:Port              
LISTEN     0      100           127.0.0.1:25                                *:*                  
LISTEN     0      128                   *:22                                *:*                  
LISTEN     0      100               [::1]:25                             [::]:*                  
LISTEN     0      80                 [::]:3306                           [::]:*                  
LISTEN     0      80                 [::]:3307                           [::]:*                  
LISTEN     0      80                 [::]:3308                           [::]:*                  
LISTEN     0      128                [::]:22                             [::]:*

mysqld_multi多实例部署

标签：stop time 环境 and nod mys unity tmp set

本文系统来源：https://www.cnblogs.com/shipment/p/13161931.html

内容总结

以上是互联网集市为您收集整理的mysqld_multi多实例部署全部内容，希望文章能够帮你解决mysqld_multi多实例部署所遇到的程序开发问题。如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/524463.html

来源：【匿名】

【上一篇】MySQLdb 操作数据库【下一篇】用IE远程创建Mysql数据库的简易程序

更多 ►

【mysqld_multi多实例部署】教程文章相关的互联网学习教程文章

Use 'mysqld --thread_stack=#' to specify a bigger stack.

执行sql语句，报错了：ERROR 1436 (HY000): Thread stack overrun: 6656 bytes used of a 131072 byte stack, and 128000 bytes needed. Use ‘mysqld --thread_stack=#‘ to specify a bigger stack.错误原因是 thread_stack太小，默认的128K，建议调整到500K ————————————————版权声明：本文为CSDN博主「rongge2008」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。原文链接：https:...

Mysql备份还原数据库之mysqldump实例及参数详细说明【代码】

我们在运营项目的过程中肯定会遇到备份数据库，还原数据库的情况，我们一般用一下两种方式来处理：1.使用into outfile 和 load data infile导入导出备份数据这种方法的好处是，导出的数据可以自己规定格式，并且导出的是纯数据，不存在建表信息，你可以直接导入另外一个同数据库的不同表中，相对于mysqldump比较灵活机动。我们来看下面的例子：（1）下面的mysql命令是把select的mytable表中的数据导出到/home/db_bak2012文件。sele...

mysqldump 备份直接至压缩文件，提高备份及压缩时间

https://www.cnblogs.com/dbcloud/p/6289577.html 1、备份及压缩分开1备份 mysqldump --single-transaction -hlocalhost --all-databases --triggers --routines --events -P3306 -uroot -pxxxx >/data/test.sql1tar czvf test.tar.gz /data/test.sql --remove-files1 还原 tar xzvf test.tar.gz 1mysql -hlocalhost -uroot -pxxxxx </data/test.sql2、备份后直接压缩写入磁盘1备份mysqldump...

Communications link failure mysql自动停止连接拒绝 mysqld dead but sub。。。

服务器环境中JAVA 连接数据库 Communications link failure， Contection refused网上很多这种情况，解决基本上是将127.0.0.1换成localhost但是我这个比较傻b，原因也很无奈。值得纪念，以下是我解决的过程，过程还是值得学习的。1、症状：java 连接数据库访问拒绝连接拒绝。　　原因：基本上是围绕着 IP，PORT，DB，USERNAME，PASSWORD这几个参数，只能是这里出了问题。　　　过程：将127.0.0.1换成localhost，还是不行，修改ti...

python MySQLdb 对mysql基本操作方法【代码】

1#!/usr/bin/env python 2# -*- coding:utf-8 -*- 3import MySQLdb4 5 conn = MySQLdb.connect(host=‘192.168.1.101‘,user=‘root‘,passwd=‘123‘,db=‘host‘)6 cur = conn.cursor(cursorclass=MySQLdb.cursors.DictCursor)7 reCout = cur.execute(‘select ip,name from host,user where user.name = "alex" and user.id=host.id‘)8 nRet = cur.fetchall()9conn.commit() 10cur.close() 11conn.close() 12print reCout 13pr...

Mysqldump 常用备份操作命令

1.1.1 备份操作create user ‘backup’@’localhost’ identifiedby ‘’;grant select, reload, lock tables,replication client, show view, event, view, process on *.* to‘backup’@’localhost’;mysqldump –ubackup –p –master-data=2–single-transaction –routines –triggers –events mc_orderdb >mc_orderdb.sql 备份一个库mysqldump –ubackup –p –master-data=2–single-transaction –routines –trigge...

auto_mysqldump_data.sh

#!/bin/bash#2019年10月31日22:57:25#auto back mysql‘s databaseIPADDR="10.0.0.7"BAK_TIME=date +%F-%TMYSQL_USER="backup"MYSQL_PASS="123456"MYSQL_COM1="mysqldump"MYSQL_COM2="mysql"SQL_DIR="/data/mysqlback/"SQL_DBS="$*"if [ $# -eq 0 ]; thenecho -e "\033[33m-----------------------------------\033[0m"echo -e "\033[31m-->{Usage:(eg:$0 jf1 | jf1 jf2 .. | all )}\033[0m"echo -e "\033[33m--------------------...

MySqlDB基本操作程序一览【代码】

import MySQLdbconn = MySQLdb.connect(host = "localhost", user = "root" ,passwd = "gongbo0801")cur = conn.cursor()#创建数据库# cur.execute("drop database iamgongbo")cur.execute("create database if not exists iamgongbo")cur.execute("use iamgongbo")#创建数据库表cur.execute("""create table if not exists one( id INT(20), name CHAR(20) ...

mysqldb【代码】

#mysqldb #import time, MySQLdb ##连接 #conn=MySQLdb.connect(host="localhost",user="root",passwd="",db="test",charset="utf8") #cursor = conn.cursor() ##删除表 #sql = "drop table if exists user" #cursor.execute(sql)##创建 #sql = "create table if not exists user(name varchar(128) primary key, created int(10))" #cursor.execute(sql)##写入 #sql = "insert into user(name,created) value...

mysqld-nt.exe - mysqld-nt是什么进程有什么用

进程文件： mysqld-nt or mysqld-nt.exe 进程名称： MySQL Daemon 进程类别：存在安全风险的进程英文描述： mysqld-nt.exe belongs to MySQL Daemon. It is a service that handles the access to MySQL databases. This process should not be removed to ensure that your MySQL database is working properly. 中文参考： mysqld-nt.exe是MySQL Daemon数据库服务相关程序。出品者：N/A 属于：N/A 系统进程：No 后台程序：Yes ...

MySQLdb autocommit的坑【代码】

今天写的一个小功能，里面要用MySQLdb更新数据库，语句如下sql = "update %s.account_operation set status=1 where username=‘%s‘" % (allResDBInfos[‘db‘], username) 变量替换后，是下面的样子update suspects.account_operation set status=1 where username=‘test@163.com‘ 语句没问题，数据库中也存在username为‘test@163.com‘的记录，并且手动执行也是正确的（status被正确的更新为1）但奇怪的就是在Python中用MySQ...

Linux环境下启动MySQL数据库出现找不到mysqld.sock的解决办法！

问题：　　在普通用户权限下运行：mysql -u root -p，回车之后如果会出现如下错误：ERROR 2002 (HY000): Can‘t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock‘ (2)此错误的意思就是没有找到mysqld.sock文件。解决办法：　　首先进入超级用户root权限 sudo -i 或者su - root　　切换到默认目录 cd /var/lib/mysql 下查看一下该文件是否存在(可以再次目录下执行以下操作)。　　执行命令 ps -au...

python mysql 简单总结（MySQLdb模块需另外下载）

python 通过DB-API规范了它所支持的不同的数据库，使得不同的数据库可以使用统一的接口来访问和操作。满足DB-API规范的的模块必须提供以下属性：属性名　　　　　　描述apilevel 　　　　DB-API 模块兼容的DB-API 版本号threadsafety　　线程安全级别paramstyle　　　该模块支持的SQL语句参数风格connect()　　　　连接函数（最常用）其中 threadsafety是一个整数，取值如下：0：不支持线程安全，多个线程不能共享此模块1：初级线程...

使用mysqldump迁移数据【代码】

1、先停止业务，使用MySQLdump的数据导出工具，将您线下原有数据库数据导出为数据文件mysqldump -hlocalhost -uroot --default-character-set=utf8 -R db_name -p*********** > ./tmp.sql2、查看原数据库上查看用户的授权信息，拷贝到新数据库select a.user,a.host from user a where a.user = ‘user_name;show grants for user_name@‘ip‘;3、在新数据库上执行GRANT USAGE ON *.* TO ‘user_name‘@‘ip‘ IDENTIFIED BY P...

使用mysqldump对数据库备份【代码】

mysqldump是一个简单而有效的数据库备份程序，它会将需要备份的数据库的结构转换为mysql语言组成的一个文本文件，这个文本文件包含了创建这个数据库所进行的所有mysql语句，所以当我们恢复时，便是程序读取这些语句重新创建数据库、创建表以及插入数据的过程，但因为其是单线程的，所以对于比较大的数据库操作会比较慢。mysqldump支持InnoDB热备，MyISAM的温备，可以基于二进制日志进行增量备份，根据二进制文件文件中记录的操作...

首页 / MYSQL / mysqld_multi多实例部署

mysqld_multi多实例部署

内容导读

内容图文

mysql多实例部署

下载软件

配置用户和组并解压二进制程序至/usr/local下

创建实例数据存放的目录

初始化各实例

配置配置文件/etc/my.cnf

启动各实例

初始化密码

关闭实例

内容总结

内容备注

内容手机端

【mysqld_multi多实例部署】教程文章相关的互联网学习教程文章

MYSQLD - 相关标签

实例 - 相关标签

MYSQL - 技术教程分类

MYSQL - 最新教程

MYSQL - 最热教程