mysql-多实例 -云博客

mysql-多实例

# 数据库 2024-05-05 14:40 0 28 来源：云博客

一、MySQL 多实例

1、简介

什么使多实例呢？

多实例就是在一台机器上开启多个不同的服务端口（如：3306,3307）；

运行多个MySQL服务进程，这些进程通过不同的socket监听不同的服务端口来提供各自的服务。

MySQL多实例共用一个 MySQL 的安装程序，使用不同（也可相同）的 my.cnf 配置文件，启动程序和数据文件。

在提供服务时，多实例 MySQL 在逻辑上是各自独立的，多个实例的本身是根据配置文件对应的设定值，来获得服务器的相关硬件资源多少。

2、安装配置前操作

安装配置前的一些配置少不了

文件的目录最好也规矩，这样才专业

/data/ops/app/mysql-5.6.23/ ###mysql的安装路径mkdir /data/ops/app/mysql-5.6.23/{3306,3307}vim /data/ops/app/mysql-5.6.23/etc/3306.cnf[client]character-set-server = utf8[mysql]character-set-server = utf8[mysqld]#bind-address=127.0.0.1character-set-server = utf8datadir=/data/ops/app/mysql-5.6.23/3306socket=/data/ops/app/mysql-5.6.23/3306/3306.sockuser=mysqlskip-grant-tablessymbolic-links=0skip-name-resolve#init-connect=‘insert into accesslog.accesslog(id, time, localname, matchname) values(connection_id(), now(), user(),current_user());‘slow_query_log = 1long_query_time = 4#slow-query-log-file = /data/ops/app/mysql-5.6.23/logs/3306/3306_slow.loglog-queries-not-using-indexes = falselower-case-table-names = 1server-id = 1max_connections =5000max_allowed_packet = 16Mconnect_timeout = 60log-slave-updateslog-bin = 3306-binexpire_logs_days = 7slave-skip-errors = 1062#binlog_do_db =back_log = 200max_connect_errors = 10table_open_cache = 10240binlog_cache_size = 1Mmax_heap_table_size = 64Msort_buffer_size = 4Mjoin_buffer_size = 4Mthread_cache_size = 8thread_concurrency = 8query_cache_size = 512Mquery_cache_limit = 2Mft_min_word_len = 4thread_stack = 192Ktransaction_isolation = REPEATABLE-READtmp_table_size = 64Mbinlog_format = mixedlog-short-formatkey_buffer_size = 512Mread_buffer_size = 16Mread_rnd_buffer_size = 16Mbulk_insert_buffer_size = 64Minnodb_file_per_tableinnodb_additional_mem_pool_size = 100Minnodb_buffer_pool_size = 1Ginnodb_data_file_path = ibdata1:12M;ibdata2:12M:autoextendinnodb_file_io_threads = 4innodb_thread_concurrency = 8innodb_flush_log_at_trx_commit = 2innodb_log_buffer_size = 8Minnodb_log_file_size = 256Minnodb_log_files_in_group = 3innodb_max_dirty_pages_pct = 90innodb_lock_wait_timeout = 120slave-skip-errors = 1062[mysqld_safe]log-error=/data/ops/app/mysql-5.6.23/logs/3306/3306.logpid-file=/data/ops/app/mysql-5.6.23/3306/3306.pid

vim /data/ops/app/mysql-5.6.23/etc/3307.cnf ### 配置文件[client]character-set-server = utf8[mysql]character-set-server = utf8[mysqld]#bind-address=127.0.0.1 character-set-server = utf8datadir=/data/ops/app/mysql-5.6.23/3307socket=/data/ops/app/mysql-5.6.23/3307/3307.sockuser=mysqlskip-grant-tablessymbolic-links=0skip-name-resolve#init-connect=‘insert into accesslog.accesslog(id, time, localname, matchname) values(connection_id(), now(), user(),current_user());‘slow_query_log = 1long_query_time = 4slow-query-log-file = /data/ops/app/mysql-5.6.23/logs/3307/3307_slow.loglog-queries-not-using-indexes = falselower-case-table-names = 1server-id = 1max_connections =5000max_allowed_packet = 16Mconnect_timeout = 60log-slave-updateslog-bin = 3307-binexpire_logs_days = 7slave-skip-errors = 1062#binlog_do_db =back_log = 200max_connect_errors = 10table_open_cache = 10240binlog_cache_size = 1Mmax_heap_table_size = 64Msort_buffer_size = 4Mjoin_buffer_size = 4Mthread_cache_size = 8thread_concurrency = 8query_cache_size = 512Mquery_cache_limit = 2Mft_min_word_len = 4thread_stack = 192Ktransaction_isolation = REPEATABLE-READtmp_table_size = 64Mbinlog_format = mixedlog-short-formatkey_buffer_size = 512Mread_buffer_size = 16Mread_rnd_buffer_size = 16Mbulk_insert_buffer_size = 64Minnodb_file_per_tableinnodb_additional_mem_pool_size = 100Minnodb_buffer_pool_size = 1Ginnodb_data_file_path = ibdata1:12M;ibdata2:12M:autoextendinnodb_file_io_threads = 4innodb_thread_concurrency = 8innodb_flush_log_at_trx_commit = 2innodb_log_buffer_size = 8Minnodb_log_file_size = 256Minnodb_log_files_in_group = 3innodb_max_dirty_pages_pct = 90innodb_lock_wait_timeout = 120slave-skip-errors = 1062[mysqld_safe]log-error=/data/ops/app/mysql-5.6.23/logs/3307/3307.logpid-file=/data/ops/app/mysql-5.6.23/3307/3307.pid

###创建初始化脚本以及启动文件

vim /data/ops/app/mysql-5.6.23/scripts/init/3306.sh#/bin/bash# 3306.sh can create a 3306 instance# create directoriesif [ ! -d ‘/data/ops/app/mysql-5.6.23/3306‘ ];then mkdir -p ‘/data/ops/app/mysql-5.6.23/3306‘;fiif [ ! -d ‘/data/ops/app/mysql-5.6.23/logs/3306‘ ];then mkdir -p ‘/data/ops/app/mysql-5.6.23/logs/3306‘;fi# set envconf="/data/ops/app/mysql-5.6.23/etc/3306.cnf"basedir="/data/ops/app/mysql-5.6.23"datadir="/data/ops/app/mysql-5.6.23/3306"pid="/data/ops/app/mysql-5.6.23/3306/3306.pid"sock="/data/ops/app/mysql-5.6.23/3306/3306.sock"port=‘3000‘user=‘mysql‘# create instance#/data/ops/app/mysql-5.6.23/scripts/mysql_install_db --user=$user --basedir=$basedir --datadir=$datadir# boot instance/data/ops/app/mysql-5.6.23/bin/mysqld_safe --defaults-file=$conf --user=$user --basedir=$basedir --datadir=$datadir --port=$port --pid-file=$pid --socket=$sock &

vim /data/ops/app/mysql-5.6.23/scripts/init/3307.sh#/bin/bash# 3307.sh can create a 3307 instance# create directoriesif [ ! -d ‘/data/ops/app/mysql-5.6.23/3307‘ ];then mkdir -p ‘/data/ops/app/mysql-5.6.23/3307‘;fiif [ ! -d ‘/data/ops/app/mysql-5.6.23/logs/3307‘ ];then mkdir -p ‘/data/ops/app/mysql-5.6.23/logs/3307‘;fi# set envconf="/data/ops/app/mysql-5.6.23/etc/3307.cnf"basedir="/data/ops/app/mysql-5.6.23"datadir="/data/ops/app/mysql-5.6.23/3307"pid="/data/ops/app/mysql-5.6.23/3307/3307.pid"sock="/data/ops/app/mysql-5.6.23/3307/3307.sock"port=‘3002‘user=‘mysql‘# create instance#/data/ops/app/mysql-5.6.23/scripts/mysql_install_db --user=$user --basedir=$basedir --datadir=$datadir# boot instance/data/ops/app/mysql-5.6.23/bin/mysqld_safe --defaults-file=$conf --user=$user --basedir=$basedir --datadir=$datadir --port=$port --pid-file=$pid --socket=$sock &

#####注意初始化脚本完毕以后mysql才能启动

su --shell=/bin/bash mysql -c "/data/ops/app/mysql-5.6.23/scripts/init/3306.sh" ###启动脚本可以放在/etc/rc.local下开机自启

su --shell=/bin/bash mysql -c "/data/ops/app/mysql-5.6.23/scripts/init/3307.sh"

####mysql关闭

killall mysqld 不建议这样操作

mysqladmin -u mysql -P 3002 -S /data/ops/app/mysql-5.6.23/3307/3307.sock shutdown ###建议这样关闭数据库