✨ mysql多实例
简单的说,就是在一台机器上开启多个不同的服务端口,运行多个mysql服务进程,这些服务进程通过不同的socket监听不同的服务端口来提供各自的服务。
公用一套安装程序
使用不同的my.cnf配置文件,启动程序,数据文件。
当服务器资源有剩余时,可以充分利用剩余的资源提供更多的服务
当公司资金紧张,但是数据库又需要各自尽量独立提供服务,而且需要主从同步等技术时,多实例就再好不过了
多实例的问题:
当某个服务器多实例并发很高或者有慢查询时,整个实例会消耗整个更多的内存,CPU,磁盘io资源,导致服务器上的其他的实例提供服务的质量下降。
安装:
依赖:yum install -y ncurses-devel libaio-devel
采用data目录作为mysql多实例的根目录
停掉运行的数据库,删除或者备份启动文件
mkdir -p /data/{3306,3307}/data
data/ #总的多实例目录
├── 3306 #3306实例的目录
│ └── data #3306的数据目录
├── 3307 #3307实例的目录
│ └── data #3307的数据目录
多实例的启动方式的原理 核心
启动:mysqld_safe --defaults-file=/data/3307/my.cnf 2>&1 >/dev/null &
停止:mysqladmin -u root -p密码 -S /data/3307/mysql.sock shutdown
配置文件
[client] port = 3308 socket = /data/3308/mysql.sock [mysql] no-auto-rehash [mysqld] user = mysql port = 3308 socket = /data/3308/mysql.sock basedir = /usr/local/mysql datadir = /data/3308/data open_files_limit = 1024 read-only back_log = 600 max_connections = 800 max_connect_errors = 3000 #table_cache = 614 external-locking = FALSE max_allowed_packet = 8M sort_buffer_size = 1M join_buffer_size = 1M thread_cache_size = 100 thread_concurrency = 2 query_cache_size = 2M query_cache_limit = 1M query_cache_min_res_unit = 2K #default_table_type = InnoDB thread_stack = 192K #transaction_isolation = READ-COMMITTED tmp_table_size = 2M max_heap_table_size = 2M long_query_time =1 pid-file = /data/3308/mysql.pid relay-log = /data/3308/relay-bin relay-log-info-file = /data/3307/relay-log.info binlog_cache_size = 1M max_binlog_cache_size = 1M max_binlog_size = 2M key_buffer_size = 16M read_rnd_buffer_size = 1M bulk_insert_buffer_size = 1M lower_case_table_names = 1 skip-name-resolve slave-skip-errors = 1032,1062 replicate-ignore-db=mysql server-id = 1 #不同实例的server-id不一样的,3306端口的server_id我设置为52了,这里不能是52 #innodb_additional_mem_pool_size = 4M innodb_buffer_pool_size = 32M innodb_data_file_path = ibdata1:128M:autoextend innodb_file_io_threads = 4 #innodb_threads_concurrency = 8 innodb_flush_log_at_trx_commit = 2 innodb_log_buffer_size = 2M innodb_log_file_size = 4M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 90 innodb_lock_wait_timeout = 120 innodb_file_per_table = 0 [mysqldump] quick max_allowed_packet = 2M [mysql_safe] log-error=/data/3308/mysql_pcm3308.err pid-file=/data/3308/mysqld.pid #所有3308修改成另一个实例端口
初始化
/usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/data/3308/data/ --user=mysql #datadir 修改成实例数据目录
启动脚本
#!/bin/bash #init port=3308 mysql_user="root" mysql_pwd="123123" CmdPath="/usr/local/mysql/bin" mysql_sock="/data/${port}/mysql.sock" #startup function function_start_mysql(){ if [ ! -e "$mysql_sock" ];then printf "Starting Mysql...\n" /bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 >/dev/null & #启动命令 else printf "Mysql is running...\n" exit fi } #stop function function_stop_mysql(){ if [ ! -e "$mysql_sock" ];then printf "Mysql is stopped...\n" exit else printf "Stoping Mysql...\n" ${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdown #停止命令 fi } #restart function function_restart_mysql(){ printf "Restart Mysql...\n" function_stop_mysql sleep 2 function_start_mysql } case $1 in start) function_start_mysql ;; stop) function_stop_mysql ;; restart) function_restart_mysql ;; *) printf "Usage:/data/${port}/mysql.sh {start|stop|restart}\n" esac #修改相关端口 密码
登陆实例mysql
mysql -S /data/3307/mysql.sock
多实例mysql加密码
mysqladmin -u root -S /data/3307/mysql.sock password '123123'