MySQL 5.7.18 多实例配置

最新推荐文章于 2023-01-17 11:34:15 发布
最新推荐文章于 2023-01-17 11:34:15 发布
阅读量236 收藏
点赞数 1
文章标签： 数据库 python 网络
原文链接：https://my.oschina.net/xxj123go/blog/887971
版权

2019独角兽企业重金招聘Python工程师标准>>> hot3.png

为了配置MRG省几台机器，打算使用多实例，捎带整理下文档，以备查阅。

环境说明

主机: Centos6.5
MySQL: 5.7.18
安装方式:二进制 
安装包:/data/src 
安装位置: /user/local/mysql

解压

tar zxvf /data/src/mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz  -C /usr/local/
cd /usr/local/;mv mysql-5.7.18-linux-glibc2.5-x86_64/  mysql

创建目录结构

mkdir -p /var/run/mysqld  ;
mkdir -p /usr/local/mysql/data330{6,7,8} ;
chmod 750 /var/run/mysqld /usr/local/mysql/data330*;
chown -R mysql:mysql /usr/local/mysql/ /var/run/mysqld ;

准备配置文件(精简版，完整版在后面)

[mysqld_multi]  
mysqld     = /usr/local/mysql/bin/mysqld_safe  
mysqladmin = /usr/local/mysql/bin/mysqladmin  
#user       = root  
#pass  = 123456  
 
[mysqld3306]    
port = 3306  
server_id = 3306  
basedir =/usr/local/mysql  
datadir =/usr/local/mysql/data3306  
socket =/tmp/mysql3306.sock    
log-error =/var/log/mysqld3306.log  
pid-file =/var/run/mysqld/mysqld3306.pid  
  
[mysqld3307]    
port = 3307  
server_id = 3307  
basedir =/usr/local/mysql  
datadir =/usr/local/mysql/data3307  
socket =/tmp/mysql3307.sock    
log-error =/var/log/mysqld3307.log  
pid-file =/var/run/mysqld/mysqld3307.pid  

[mysqld3308]    
port = 3308  
server_id = 3308  
basedir =/usr/local/mysql  
datadir =/usr/local/mysql/data3308
socket =/tmp/mysql3308.sock    
log-error =/var/log/mysqld3308.log  
pid-file =/var/run/mysqld/mysqld3308.pid

初始化

cd /usr/local/mysql;
bin/mysqld --initialize --user=mysql --datadir=/usr/local/mysql/data3306 --explicit_defaults_for_timestamp
bin/mysqld --initialize --user=mysql --datadir=/usr/local/mysql/data3307 --explicit_defaults_for_timestamp
bin/mysqld --initialize --user=mysql --datadir=/usr/local/mysql/data3308 --explicit_defaults_for_timestamp

生成的随机密码
3306 -->  7rp6k2)QdOHM
3307-->   :MWTA=)vQ8re
3308-->   qONzkgy)o31p
  

测试

[root@localhost mysql]mysqld_multi start;
[root@localhost mysql]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

修改密码

mysqladmin -uroot -p"7rp6k2)QdOHM"  password "123456" -S /tmp/mysql3306.sock    
mysqladmin -uroot -p":MWTA=)vQ8re"  password "123456" -S /tmp/mysql3307.sock 
mysqladmin -uroot -p"qONzkgy)o31p"  password "123456" -S /tmp/mysql3308.sock

重启实例

killall mysqld
ps -ef|grep mysqld 

确认没有运行实例后修改 my.cnf ,将密码写入配置文件

user = root  
pass = 123456 

再次测试

[root@localhost mysql]mysqld_multi start;
[root@localhost mysql]mysqld_multi report;
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3307 is running
MySQL server from group: mysqld3308 is running
[root@localhost mysql]# mysqld_multi stop
[root@localhost mysql]# mysqld_multi report 
Reporting MySQL servers
MySQL server from group: mysqld3306 is not running
MySQL server from group: mysqld3307 is not running
MySQL server from group: mysqld3308 is not running
[root@localhost mysql]# mysqld_multi start 3306
[root@localhost mysql]# 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 not running

[root@localhost mysql]# mysqld_multi stop 3306 
[root@localhost mysql]# mysqld_multi report   
Reporting MySQL servers
MySQL server from group: mysqld3306 is not running
MySQL server from group: mysqld3307 is not running
MySQL server from group: mysqld3308 is not running

[root@localhost log]# netstat  -nltp|grep 33
tcp        0      0 :::3306                     :::*                        LISTEN      14990/mysqld        
tcp        0      0 :::3307                     :::*                        LISTEN      3263/mysqld         
tcp        0      0 :::3308                     :::*                        LISTEN      7372/mysqld

完整 my.cnf

[mysqld_multi]  
mysqld     = /usr/local/mysql/bin/mysqld_safe  
mysqladmin = /usr/local/mysql/bin/mysqladmin  
#user = root  
#pass = 123456 
  
#demo,this is a simple case!  
#[mysqld3306]    
#port = 3306  
#server_id = 3306  
#basedir =/usr/local/mysql  
#datadir =/usr/local/mysql/data3306  
#socket =/tmp/mysql3306.sock    
#log-error =/var/log/mysqld3306.log  
#pid-file =/var/run/mysqld/mysqld3306.pid  


[mysqld3306]  
#
#*basic
#  
port = 3306 
server_id = 3306 
basedir =/usr/local/mysql  
datadir =/usr/local/mysql/data3306
socket =/tmp/mysql3306.sock    
pid-file =/var/run/mysqld/mysqld3306.pid  
transaction_isolation = READ-COMMITTED
sql_mode=''

#
#*undo
#
innodb_undo_log_truncate=1 
innodb_undo_tablespaces=3

#
# * log
#
slow_query_log=1 
slow_query_log_file = /var/log/slow3306.log
long_query_time=5
log-error =/var/log/mysqld3306.log  

#
#* sys
#
connect_timeout = 3600
default_time_zone = SYSTEM
log_queries_not_using_indexes = OFF
max_allowed_packet = 124M
max_connect_errors = 100
max_length_for_sort_data = 1024
max_prepared_stmt_count = 16382
max_write_lock_count = 102400
net_read_timeout = 30
net_retry_count = 10
net_write_timeout = 60
open_files_limit = 65535
query_prealloc_size = 16384
slow_launch_time = 2
#400 + (table_open_cache / 2)
table_definition_cache = 1500
table_open_cache = 2200
wait_timeout = 86400
sort_buffer_size=10M
join_buffer_size=2M
max_heap_table_size = 64M
#tmp_table_size = 200M
key_buffer_size = 8388600
myisam_sort_buffer_size = 262144
innodb_sort_buffer_size=3M

#
# * binlog
#
log_bin=mysql-bin
log-bin-index=mysql-bin.index
binlog-format=row
expire_logs_days=0
max_binlog_size=700M
binlog_cache_size = 256KB
binlog_checksum = CRC32
binlog_row_image = full
binlog_stmt_cache_size = 32768
sync_binlog=1

#
# * innodb
#
default-storage-engine=InnoDB
default_tmp_storage_engine=InnoDB
character_set_server = utf8
innodb_file_per_table=1
innodb_flush_log_at_trx_commit = 1
innodb_data_file_path = ibdata1:12m:autoextend
#80%MEM
#innodb_buffer_pool_size = 200m
innodb_thread_concurrency = 0
#innodb_default_row_format=dynamic
#innodb_log_buffer_size = 16M
#innodb_log_file_size = 800M
innodb_log_files_in_group = 4
innodb_adaptive_hash_index = ON
innodb_max_dirty_pages_pct = 75
innodb_old_blocks_pct = 37
innodb_open_files = 3000
#default value was changed from 1 to 4 in MySQL 5.7.8.
innodb_purge_threads = 4
innodb_stats_on_metadata = OFF
innodb_stats_transient_sample_pages = 15
innodb_strict_mode = ON
innodb_table_locks = ON
innodb_thread_sleep_delay = 10000
innodb_write_io_threads = 4
#the server waits for activity on an interactive connection before closing it 
interactive_timeout = 7200
innodb_flush_method = O_DIRECT
relay_log_info_repository=TABLE  
master_info_repository=TABLE  
relay_log_recovery=on  

#
#* QC
#
query_cache_type = 0
query_alloc_block_size = 8192
query_cache_limit = 1048576
query_cache_size = 3145728

#
#* replication
#
gtid_mode=on
#>5,7,6 enum(OFF,ON,WARN)
enforce_gtid_consistency=on
relay-log=mysql-relay-bin
relay-log-index=mysql-relay-bin.index
replicate-ignore-db = mysql
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
replicate_wild_ignore_table=mysql.%
replicate_wild_ignore_table=information_schema.%
replicate_wild_ignore_table=performance_schema.%
log_bin_trust_function_creators=1
 
#----------------------------------------------------------------------------------

[mysqld3307]  
#
#*basic
#  
port = 3307  
server_id = 3307  
basedir =/usr/local/mysql  
datadir =/usr/local/mysql/data3307 
socket =/tmp/mysql3307.sock    
pid-file =/var/run/mysqld/mysqld3307.pid  
transaction_isolation = READ-COMMITTED
sql_mode=''

#
#*undo
#
innodb_undo_log_truncate=1 
innodb_undo_tablespaces=3 

#
# * log
#
slow_query_log=1 
slow_query_log_file = /var/log/slow3307.log
long_query_time=5
log-error =/var/log/mysqld3307.log  

#
#* sys
#
connect_timeout = 3600
default_time_zone = SYSTEM
log_queries_not_using_indexes = OFF
max_allowed_packet = 124M
max_connect_errors = 100
max_length_for_sort_data = 1024
max_prepared_stmt_count = 16382
max_write_lock_count = 102400
net_read_timeout = 30
net_retry_count = 10
net_write_timeout = 60
open_files_limit = 65535
query_prealloc_size = 16384
slow_launch_time = 2
#400 + (table_open_cache / 2)
table_definition_cache = 1500
table_open_cache = 2200
wait_timeout = 86400
sort_buffer_size=10M
join_buffer_size=2M
max_heap_table_size = 64M
#tmp_table_size = 200M
key_buffer_size = 8388600
myisam_sort_buffer_size = 262144
innodb_sort_buffer_size=3M

#
# * binlog
#
log_bin=mysql-bin
log-bin-index=mysql-bin.index
binlog-format=row
expire_logs_days=0
max_binlog_size=700M
binlog_cache_size = 256KB
binlog_checksum = CRC32
binlog_row_image = full
binlog_stmt_cache_size = 32768
sync_binlog=1

#
# * innodb
#
default-storage-engine=InnoDB
default_tmp_storage_engine=InnoDB
character_set_server = utf8
innodb_file_per_table=1
innodb_flush_log_at_trx_commit = 1
innodb_data_file_path = ibdata1:12m:autoextend
#80%MEM
#innodb_buffer_pool_size = 200m
innodb_thread_concurrency = 0
#innodb_default_row_format=dynamic
#innodb_log_buffer_size = 16M
#innodb_log_file_size = 800M
innodb_log_files_in_group = 4
innodb_adaptive_hash_index = ON
innodb_max_dirty_pages_pct = 75
innodb_old_blocks_pct = 37
innodb_open_files = 3000
#default value was changed from 1 to 4 in MySQL 5.7.8.
innodb_purge_threads = 4
innodb_stats_on_metadata = OFF
innodb_stats_transient_sample_pages = 15
innodb_strict_mode = ON
innodb_table_locks = ON
innodb_thread_sleep_delay = 10000
innodb_write_io_threads = 4
#the server waits for activity on an interactive connection before closing it 
interactive_timeout = 7200
innodb_flush_method = O_DIRECT
relay_log_info_repository=TABLE  
master_info_repository=TABLE  
relay_log_recovery=on  

#
#* QC
#
query_cache_type = 0
query_alloc_block_size = 8192
query_cache_limit = 1048576
query_cache_size = 3145728

#
#* replication
#
gtid_mode=on
#>5,7,6 enum(OFF,ON,WARN)
enforce_gtid_consistency=on
relay-log=mysql-relay-bin
relay-log-index=mysql-relay-bin.index
replicate-ignore-db = mysql
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
replicate_wild_ignore_table=mysql.%
replicate_wild_ignore_table=information_schema.%
replicate_wild_ignore_table=performance_schema.%
log_bin_trust_function_creators=1


#------------------------------------------------------------------------
[mysqld3308]  
#
#*basic
#  
port = 3308  
server_id = 3308  
basedir =/usr/local/mysql  
datadir =/usr/local/mysql/data3308 
socket =/tmp/mysql3308.sock    
pid-file =/var/run/mysqld/mysqld3308.pid  
transaction_isolation = READ-COMMITTED
sql_mode=''

#
#*undo
#
innodb_undo_log_truncate=1 
innodb_undo_tablespaces=3

#
# * log
#
slow_query_log=1 
slow_query_log_file = /var/log/slow3308.log
long_query_time=5
log-error =/var/log/mysqld3308.log  

#
#* sys
#
connect_timeout = 3600
default_time_zone = SYSTEM
log_queries_not_using_indexes = OFF
max_allowed_packet = 124M
max_connect_errors = 100
max_length_for_sort_data = 1024
max_prepared_stmt_count = 16382
max_write_lock_count = 102400
net_read_timeout = 30
net_retry_count = 10
net_write_timeout = 60
open_files_limit = 65535
query_prealloc_size = 16384
slow_launch_time = 2
#400 + (table_open_cache / 2)
table_definition_cache = 1500
table_open_cache = 2200
wait_timeout = 86400
sort_buffer_size=10M
join_buffer_size=2M
max_heap_table_size = 64M
#tmp_table_size = 200M
key_buffer_size = 8388600
myisam_sort_buffer_size = 262144
innodb_sort_buffer_size=3M

#
# * binlog
#
log_bin=mysql-bin
log-bin-index=mysql-bin.index
binlog-format=row
expire_logs_days=0
max_binlog_size=700M
binlog_cache_size = 256KB
binlog_checksum = CRC32
binlog_row_image = full
binlog_stmt_cache_size = 32768
sync_binlog=1

#
# * innodb
#
default-storage-engine=InnoDB
default_tmp_storage_engine=InnoDB
character_set_server = utf8
innodb_file_per_table=1
innodb_flush_log_at_trx_commit = 1
innodb_data_file_path = ibdata1:12m:autoextend
#80%MEM
#innodb_buffer_pool_size = 200m
innodb_thread_concurrency = 0
#innodb_default_row_format=dynamic
#innodb_log_buffer_size = 16M
#innodb_log_file_size = 800M
innodb_log_files_in_group = 4
innodb_adaptive_hash_index = ON
innodb_max_dirty_pages_pct = 75
innodb_old_blocks_pct = 37
innodb_open_files = 3000
#default value was changed from 1 to 4 in MySQL 5.7.8.
innodb_purge_threads = 4
innodb_stats_on_metadata = OFF
innodb_stats_transient_sample_pages = 15
innodb_strict_mode = ON
innodb_table_locks = ON
innodb_thread_sleep_delay = 10000
innodb_write_io_threads = 4
#the server waits for activity on an interactive connection before closing it 
interactive_timeout = 7200
innodb_flush_method = O_DIRECT
relay_log_info_repository=TABLE  
master_info_repository=TABLE  
relay_log_recovery=on  

#
#* QC
#
query_cache_type = 0
query_alloc_block_size = 8192
query_cache_limit = 1048576
query_cache_size = 3145728

#
#* replication
#
gtid_mode=on
#>5,7,6 enum(OFF,ON,WARN)
enforce_gtid_consistency=on
relay-log=mysql-relay-bin
relay-log-index=mysql-relay-bin.index
replicate-ignore-db = mysql
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
replicate_wild_ignore_table=mysql.%
replicate_wild_ignore_table=information_schema.%
replicate_wild_ignore_table=performance_schema.%
log_bin_trust_function_creators=1

 

转载于:https://my.oschina.net/xxj123go/blog/887971

weixin_33778778
关注
  • 1
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MySQL5.7版本配置
suiyujiangnan的博客
05-01 384
1、将下载的解压版MySQL安装文件解压到合适位置 2、配置环境变量，我的电脑- >属性- >高级- >环境变量，选择path，在最前面添加mysql bin文件夹路径 如：D:\Mysql\mysql-5.7.18-winx64\bin;不要忘记后面的分号 3、配置文件，MySQL5.7版本中没有my-default.ini文件，需要在所在目录手动创建一个my.ini文件 在其中添加：
Mysql配置多实例
baidu_29120075的博客
07-03 157
安装提供多实例服务的myslq数据库服务软件]# mv mysql-5.7.20-linux-glibc2.12-x86_64 /usr/local/mysql //glibc版本不需要make ]# echo "export PATH=/usr/local/mysql/bin:$PATH" >> /etc/profile //把命令放到PA...
MySQL 主从配置（数据同步）
Aaron
12-16 373
windows下安装mysql配置文件为my.ini 先准备两个数据库，一个做主库一个做从库。 1.修改主库（master）的my.ini配置文件 my.ini文件在安装路径根目录下面。 [mysqld] port = 3306 #设置master的serverid server-id=1 #要生成的日记文件名称 log-bin = mysql-ctmdb #要同步的数据库 binl...
MySql主从同步的配置
qq_43366664的博客
01-17 1585
数据库主从同步服务的配置
frameset标签用法
qq_23835463的博客
09-10 310
实例： <html> <frameset cols="25%,50%,25%"> <frame src="frame_1.htm" /> <frame src="frame_2.htm" /> <frame src="frame_3.htm" /> </frameset> </html>
Mysql（二）Mysql基于position的主从复制（即异步复制）和基于GTID的主从复制
yunyuzhu的博客
10-31 742
介绍主从复制前，应该先明白一个概念，就是数据备份数据备份是一种古老而有效的数据保护手段，早期的数据备份手段主要是数据冷备，即定期将数据复制到某种存储介质(磁带，光盘...)上并物理存档保管，如果系统存储损坏，那么就从冷备的存储中恢复数据冷备的优点是简单和廉价，成本和技术难度都较低，缺点是不能保证数据最终一致，由于数据是定期复制，所以不具有实施时性，所以备份的数据比系统中的数据陈旧。 ...
mysql 主从复制 position_MySQL 主从复制
weixin_30301989的博客
01-27 211
1. 主库创建允许远程连接 + 至少具备 replication、slave 权限的 MySQL 用户用户名解释这边的用户 'slave_mysql'@'192.168.%' 指的是只允许 ip 以 192.168. 开头的远程主机进行连接(本地测试用的)，新手建议是设置为 'slave_mysql'@'%'。注意数据库通配符的使用。mysql -u root -pmysql> grant ...
MySQL 5.7.18 免安装版配置教程
09-09
这个教程详细阐述了MySQL 5.7.18免安装版在Windows上的配置步骤，包括环境变量配置、初始化配置文件创建、数据目录生成、服务安装与启动，以及初次登录数据库的过程。遵循这些步骤，你可以在本地环境中快速搭建起一...
MySql5.7.18字符集配置图文详解
09-09
总结，配置 MySQL 5.7.18 字符集涉及多个步骤，包括下载安装、初始化、设置配置文件以及重启服务。通过上述步骤，您可以根据需求设置合适的字符集，确保数据的完整性和兼容性。在进行字符集配置时，一定要注意相关...
mysql5.7.18版本免安装配置教程
09-09
主要为大家详细介绍了mysql5.7.18版本免安装的配置教程，具有一定的参考价值，感兴趣的小伙伴们可以参考一下
Linux系统MySql5.7.18配置文件
11-08
Linux安装MySQL数据库时，版本高于5.7.17时，就没有my_default.cnf配置文件了，需要手动添加，这个配置文件就是，亲测有效
Windows下mysql5.7.18安装配置教程
09-09
在Windows环境下安装配置MySQL 5.7.18是一项基础但至关重要的任务，对于那些想要在本地开发或测试环境中使用MySQL数据库的用户来说。MySQL是世界上最受欢迎的开源关系型数据库管理系统之一，其5.7.18版本提供了许多...
mysql主从库同步设置
qq_38650808的博客
06-08 179
一、修改主库my.cnf文件 vi /etc/my.cnf #在[mysqld]下添加如下配置 log_bin=master-bin log_bin_index=master-bin.index innodb_file_per_table=ON binlog_format=MIXED server_id=1 sync_binlog=1 二、添加一个从服务器创建复制数据的用户 GRANT REPLICATION SLAVE ON *.* TO 'slave'@'从库的ip' IDENTIFIED BY '密
mysql+数据库ip+变更,MySQL数据库设置主从同步
weixin_39852688的博客
03-23 377
MySQL主从同步的机制：MySQL同步的流程大致如下:1、主服务器(master)将变更事件(更新、删除、表结构改变等等)写入二进制日志(master log)。2、从服务器(slave)的IO线程从主服务器(binlog dump线程)获取二进制日志，并在本地保存一份自己的二进制日志(relay log)3、从服务器的SQL线程读取本地日志(relay log)，并重演变更事件。MySQL主从...
MySQL-5.6.27两种方式部署多实例以及mysqld_multi脚本BUG处理
嗯！记录自己学习过程。
07-05 2290
部署MySQL多实例准备工作 1.1 创建各实例的data目录，以data’N’区分 [root@db1 mysql]# pwd /data/mysql [root@db1 mysql]# mkdir {data3306,data3307,data3308,data3309} [root@db1 mysql]# chown -R mysql:mysql data330*1.2 初始化各实
【MySQL】一台服务器上搭建两个mysql节点
zclinux的博客
08-27 1207
环境: CentOS 6.8  memory：1G Mysql 5.7 二进制安装包 1.安装相关的环境包 yum -y install gcc glibc libaio libstdc++ libstdc ncurses-libs  2.查看系统中是否存在已经安装的mysql，如果有的话，将其卸载掉 rpm -qa | grep mysql rpm -e mysql    #普通删除模式...
mysql双主启停_Mysql 多实例配置与启停
weixin_30601893的博客
01-19 121
1 安装目录[mysql@server ~]$ ll /mysqltotal 12drwxr-xr-x 5 mysql mysql 4096 Aug 3 23:08 data-rw-r--r-- 1 mysql mysql 1843 Aug 3 22:53 multi.logdrwxr-xr-x 13 mysql mysql 4096 Aug 3 22:49 software2 配置文件...
MySQL多实例配置
建伟博客
07-03 974
MySQL多实例配置
Mysql 多实例配置与初始化
道行尚浅（老道）的博客
11-10 1330
#my.cnf [client] port = 3306 socket = /tmp/mysql.sock [mysql] prompt="\\u@\\h \\D \\R:\\m:\\s [\\d]> #pager="less -i -n -S" #tee=/data/mysql/mysql_3306/data/query.log no-auto-reh
mysql5.7.18安装配置教程
最新发布
05-11
以下是MySQL 5.7.18安装配置教程： 1. 下载MySQL 5.7.18安装文件，下载地址：https://dev.mysql.com/downloads/mysql/5.7.html。 2. 安装MySQL，双击下载的MySQL安装文件，按照提示完成安装。在安装过程中，需要...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值