住房和城乡建设部网站八大员,手机app制作报价,北京专业网站外包公司,前端程序员培训班mysql的多实例有两种方式可以实现#xff0c;两种方式各有利弊。第一种是使用多个配置文件启动不同的进程来实现多实例#xff0c;这种方式的优势逻辑简单#xff0c;配置简单#xff0c;缺点是管理起来不太方便。第二种是通过官方自带的mysqld_multi使用单独的配置文件来实…mysql的多实例有两种方式可以实现两种方式各有利弊。第一种是使用多个配置文件启动不同的进程来实现多实例这种方式的优势逻辑简单配置简单缺点是管理起来不太方便。第二种是通过官方自带的mysqld_multi使用单独的配置文件来实现多实例这种方式定制每个实例的配置不太方面优点是管理起来很方便集中管理。 下面就分别来实战这两种多实例的安装和管理 先来学习第一种使用多个配置文件启动多个不同进程的情况 环境介绍 mysql 版本5.1.50 操作系统SUSE 11 mysql实例数3个 实例占用端口分别为3306、3307、3308 创建mysql用户 点击(此处)折叠或打开 /usr/sbin/groupadd mysql/usr/sbin/useradd -g mysql mysql 编译安装mysql 点击(此处)折叠或打开 tar xzvf mysql-5.1.50.tar.gzcd mysql-5.1.50./configure --prefix/usr/local/mysql --with-charsetutf8 --with-extra-charsetscomplex --with-pthread --enable-thread-safe-client --with-ssl --with-client-ldflags-all-static --with-mysqld-ldflags-all-static --with-pluginspartition,innobase,blackhole,myisam,innodb_plugin,heap,archive --enable-shared --enable-assemblermakemake install 初始化数据库 点击(此处)折叠或打开 /usr/local/mysql/bin/mysql_install_db --basedir/usr/local/mysql --datadir/data/dbdata_3306 --usermysql/usr/local/mysql/bin/mysql_install_db --basedir/usr/local/mysql --datadir/data/dbdata_3307 --usermysql/usr/local/mysql/bin/mysql_install_db --basedir/usr/local/mysql --datadir/data/dbdata_3308 --usermysql 创建配置文件 vim /data/dbdata_3306/my.cnf 3306的配置文件如下 点击(此处)折叠或打开 [client]port 3306socket /data/dbdata_3306/mysql.sock[mysqld]datadir/data/dbdata_3306/skip-name-resolvelower_case_table_names1innodb_file_per_table1port 3306socket /data/dbdata_3306/mysql.sockback_log 50max_connections 300max_connect_errors 1000table_open_cache 2048max_allowed_packet 16Mbinlog_cache_size 2Mmax_heap_table_size 64Msort_buffer_size 2Mjoin_buffer_size 2Mthread_cache_size 64thread_concurrency 8query_cache_size 64Mquery_cache_limit 2Mft_min_word_len 4default-storage-engine innodbthread_stack 192Ktransaction_isolation REPEATABLE-READtmp_table_size 64Mlog-binmysql-binbinlog_formatmixedslow_query_loglong_query_time 1server-id 1key_buffer_size 8Mread_buffer_size 2Mread_rnd_buffer_size 2Mbulk_insert_buffer_size 64Mmyisam_sort_buffer_size 128Mmyisam_max_sort_file_size 10Gmyisam_repair_threads 1myisam_recoverinnodb_additional_mem_pool_size 16Minnodb_buffer_pool_size 200Minnodb_data_file_path ibdata1:10M:autoextendinnodb_file_io_threads 8innodb_thread_concurrency 16innodb_flush_log_at_trx_commit 1innodb_log_buffer_size 16Minnodb_log_file_size 512Minnodb_log_files_in_group 3innodb_max_dirty_pages_pct 60innodb_lock_wait_timeout 120[mysqldump]quickmax_allowed_packet 256M[mysql]no-auto-rehashprompt\\u\\d \\R:\\m[myisamchk]key_buffer_size 512Msort_buffer_size 512Mread_buffer 8Mwrite_buffer 8M[mysqlhotcopy]interactive-timeout[mysqld_safe]open-files-limit 8192 vim /data/dbdata_3307/my.cnf 3307的配置文件如下 点击(此处)折叠或打开 [client]port 3307socket /data/dbdata_3307/mysql.sock[mysqld]datadir/data/dbdata_3307/skip-name-resolvelower_case_table_names1innodb_file_per_table1port 3307socket /data/dbdata_3307/mysql.sockback_log 50max_connections 300max_connect_errors 1000table_open_cache 2048max_allowed_packet 16Mbinlog_cache_size 2Mmax_heap_table_size 64Msort_buffer_size 2Mjoin_buffer_size 2Mthread_cache_size 64thread_concurrency 8query_cache_size 64Mquery_cache_limit 2Mft_min_word_len 4default-storage-engine innodbthread_stack 192Ktransaction_isolation REPEATABLE-READtmp_table_size 64Mlog-binmysql-binbinlog_formatmixedslow_query_loglong_query_time 1server-id 1key_buffer_size 8Mread_buffer_size 2Mread_rnd_buffer_size 2Mbulk_insert_buffer_size 64Mmyisam_sort_buffer_size 128Mmyisam_max_sort_file_size 10Gmyisam_repair_threads 1myisam_recoverinnodb_additional_mem_pool_size 16Minnodb_buffer_pool_size 200Minnodb_data_file_path ibdata1:10M:autoextendinnodb_file_io_threads 8innodb_thread_concurrency 16innodb_flush_log_at_trx_commit 1innodb_log_buffer_size 16Minnodb_log_file_size 512Minnodb_log_files_in_group 3innodb_max_dirty_pages_pct 60innodb_lock_wait_timeout 120[mysqldump]quickmax_allowed_packet 256M[mysql]no-auto-rehashprompt\\u\\d \\R:\\m[myisamchk]key_buffer_size 512Msort_buffer_size 512Mread_buffer 8Mwrite_buffer 8M[mysqlhotcopy]interactive-timeout[mysqld_safe]open-files-limit 8192 vim /data/dbdata_3308/my.cnf 3308的配置文件如下 点击(此处)折叠或打开 [client]port 3308socket /data/dbdata_3308/mysql.sock[mysqld]datadir/data/dbdata_3308/skip-name-resolvelower_case_table_names1innodb_file_per_table1port 3308socket /data/dbdata_3308/mysql.sockback_log 50max_connections 300max_connect_errors 1000table_open_cache 2048max_allowed_packet 16Mbinlog_cache_size 2Mmax_heap_table_size 64Msort_buffer_size 2Mjoin_buffer_size 2Mthread_cache_size 64thread_concurrency 8query_cache_size 64Mquery_cache_limit 2Mft_min_word_len 4default-storage-engine innodbthread_stack 192Ktransaction_isolation REPEATABLE-READtmp_table_size 64Mlog-binmysql-binbinlog_formatmixedslow_query_loglong_query_time 1server-id 1key_buffer_size 8Mread_buffer_size 2Mread_rnd_buffer_size 2Mbulk_insert_buffer_size 64Mmyisam_sort_buffer_size 128Mmyisam_max_sort_file_size 10Gmyisam_repair_threads 1myisam_recoverinnodb_additional_mem_pool_size 16Minnodb_buffer_pool_size 200Minnodb_data_file_path ibdata1:10M:autoextendinnodb_file_io_threads 8innodb_thread_concurrency 16innodb_flush_log_at_trx_commit 1innodb_log_buffer_size 16Minnodb_log_file_size 512Minnodb_log_files_in_group 3innodb_max_dirty_pages_pct 60innodb_lock_wait_timeout 120[mysqldump]quickmax_allowed_packet 256M[mysql]no-auto-rehashprompt\\u\\d \\R:\\m[myisamchk]key_buffer_size 512Msort_buffer_size 512Mread_buffer 8Mwrite_buffer 8M[mysqlhotcopy]interactive-timeout[mysqld_safe]open-files-limit 8192 创建自动启动文件 vim /data/dbdata_3306/mysqld 3306的启动文件如下 点击(此处)折叠或打开 #!/bin/bashmysql_port3306mysql_usernameadminmysql_passwordpassword function_start_mysql(){printf Starting MySQL...\n/bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file/data/dbdata_${mysql_port}/my.cnf 21 /dev/null } function_stop_mysql(){printf Stoping MySQL...\n/usr/local/mysql/bin/mysqladmin -u ${mysql_username} -p${mysql_password} -S /data/dbdata_${mysql_port}/mysql.sock shutdown} function_restart_mysql(){printf Restarting MySQL...\nfunction_stop_mysqlfunction_start_mysql} function_kill_mysql(){kill -9 $(ps -ef | grep bin/mysqld_safe | grep ${mysql_port} | awk {printf $2})kill -9 $(ps -ef | grep libexec/mysqld | grep ${mysql_port} | awk {printf $2})} case $1 instart)function_start_mysql;;stop)function_stop_mysql;;kill)function_kill_mysql;;restart)function_stop_mysqlfunction_start_mysql;;*)echo Usage: /data/dbdata_${mysql_port}/mysqld {start|stop|restart|kill};;esac vim /data/dbdata_3307/mysqld 3307的启动文件如下 点击(此处)折叠或打开 #!/bin/bashmysql_port3307mysql_usernameadminmysql_passwordpassword function_start_mysql(){printf Starting MySQL...\n/bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file/data/dbdata_${mysql_port}/my.cnf 21 /dev/null } function_stop_mysql(){printf Stoping MySQL...\n/usr/local/mysql/bin/mysqladmin -u ${mysql_username} -p${mysql_password} -S /data/dbdata_${mysql_port}/mysql.sock shutdown} function_restart_mysql(){printf Restarting MySQL...\nfunction_stop_mysqlfunction_start_mysql} function_kill_mysql(){kill -9 $(ps -ef | grep bin/mysqld_safe | grep ${mysql_port} | awk {printf $2})kill -9 $(ps -ef | grep libexec/mysqld | grep ${mysql_port} | awk {printf $2})} case $1 instart)function_start_mysql;;stop)function_stop_mysql;;kill)function_kill_mysql;;restart)function_stop_mysqlfunction_start_mysql;;*)echo Usage: /data/dbdata_${mysql_port}/mysqld {start|stop|restart|kill};;esac vim /data/dbdata_3308/mysqld 3308的启动文件如下 点击(此处)折叠或打开 #!/bin/bashmysql_port3308mysql_usernameadminmysql_passwordpassword function_start_mysql(){printf Starting MySQL...\n/bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file/data/dbdata_${mysql_port}/my.cnf 21 /dev/null } function_stop_mysql(){printf Stoping MySQL...\n/usr/local/mysql/bin/mysqladmin -u ${mysql_username} -p${mysql_password} -S /data/dbdata_${mysql_port}/mysql.sock shutdown} function_restart_mysql(){printf Restarting MySQL...\nfunction_stop_mysqlfunction_start_mysql} function_kill_mysql(){kill -9 $(ps -ef | grep bin/mysqld_safe | grep ${mysql_port} | awk {printf $2})kill -9 $(ps -ef | grep libexec/mysqld | grep ${mysql_port} | awk {printf $2})} case $1 instart)function_start_mysql;;stop)function_stop_mysql;;kill)function_kill_mysql;;restart)function_stop_mysqlfunction_start_mysql;;*)echo Usage: /data/dbdata_${mysql_port}/mysqld {start|stop|restart|kill};;esac 启动3306、3307、3308的mysql 点击(此处)折叠或打开 /data/dbdata_3306/mysqld start/data/dbdata_3307/mysqld start/data/dbdata_3308/mysqld start 更改原来密码处于安全考虑还需要删除系统中没有密码的帐号这里省略了 点击(此处)折叠或打开 /usr/local/mysql/bin/mysqladmin -uroot password password -S /data/dbdata_3306/mysql.sock/usr/local/mysql/bin/mysqladmin -uroot password password -S /data/dbdata_3307/mysql.sock/usr/local/mysql/bin/mysqladmin -uroot password password -S /data/dbdata_3308/mysql.sock 登录测试并创建关闭mysql的帐号权限mysqld脚本要用到 点击(此处)折叠或打开 /usr/local/mysql/bin/mysql -uroot -ppassword -S /data/dbdata_3308/mysql.sockGRANT SHUTDOWN ON *.* TO adminlocalhost IDENTIFIED BY password;flush privileges;/usr/local/mysql/bin/mysql -uroot -ppassword -S /data/dbdata_3308/mysql.sockGRANT SHUTDOWN ON *.* TO adminlocalhost IDENTIFIED BY password;flush privileges;/usr/local/mysql/bin/mysql -uroot -ppassword -S /data/dbdata_3308/mysql.sockGRANT SHUTDOWN ON *.* TO adminlocalhost IDENTIFIED BY password;flush privileges; 创建了admin帐号以后脚本的stop功能和restart功能就正常了 更改环境变量 点击(此处)折叠或打开 vim /etc/profile 添加下面一行内容PATH${PATH}:/usr/local/mysql/bin/source /etc/profile 添加到自动启动 点击(此处)折叠或打开 vim /etc/init.d/boot.local/data/dbdata_3306/mysqld start/data/dbdata_3307/mysqld start/data/dbdata_3308/mysqld start 如果是rhel或者centos系统的话自启动文件/etc/rc.local 管理的话在本地都是采用 -S /data/dbdata_3308/mysql.sock如果在远程可以通过不同的端口连接上去坐管理操作。其他的和单实例的管理没什么区别 再来看第二种通过官方自带的mysqld_multi来实现多实例实战 这里的mysql安装以及数据库的初始化和前面的步骤一样就不再赘述。 mysqld_multi的配置 vim /etc/my.cnf 点击(此处)折叠或打开 [mysqld_multi]mysqld /usr/local/mysql/bin/mysqld_safemysqladmin /usr/local/mysql/bin/mysqladminuser adminpassword password [mysqld1]socket /data/dbdata_3306/mysql.sockport 3306pid-file /data/dbdata_3306/3306.piddatadir /data/dbdata_3306user mysqlskip-name-resolvelower_case_table_names1innodb_file_per_table1back_log 50max_connections 300max_connect_errors 1000table_open_cache 2048max_allowed_packet 16Mbinlog_cache_size 2Mmax_heap_table_size 64Msort_buffer_size 2Mjoin_buffer_size 2Mthread_cache_size 64thread_concurrency 8query_cache_size 64Mquery_cache_limit 2Mft_min_word_len 4default-storage-engine innodbthread_stack 192Ktransaction_isolation REPEATABLE-READtmp_table_size 64Mlog-binmysql-binbinlog_formatmixedslow_query_loglong_query_time 1server-id 1key_buffer_size 8Mread_buffer_size 2Mread_rnd_buffer_size 2Mbulk_insert_buffer_size 64Mmyisam_sort_buffer_size 128Mmyisam_max_sort_file_size 10Gmyisam_repair_threads 1myisam_recoverinnodb_additional_mem_pool_size 16Minnodb_buffer_pool_size 200Minnodb_data_file_path ibdata1:10M:autoextendinnodb_file_io_threads 8innodb_thread_concurrency 16innodb_flush_log_at_trx_commit 1innodb_log_buffer_size 16Minnodb_log_file_size 512Minnodb_log_files_in_group 3innodb_max_dirty_pages_pct 60innodb_lock_wait_timeout 120 [mysqld2]socket /data/dbdata_3307/mysql.sockport 3307pid-file /data/dbdata_3307/3307.piddatadir /data/dbdata_3307user mysqlskip-name-resolvelower_case_table_names1innodb_file_per_table1back_log 50max_connections 300max_connect_errors 1000table_open_cache 2048max_allowed_packet 16Mbinlog_cache_size 2Mmax_heap_table_size 64Msort_buffer_size 2Mjoin_buffer_size 2Mthread_cache_size 64thread_concurrency 8query_cache_size 64Mquery_cache_limit 2Mft_min_word_len 4default-storage-engine innodbthread_stack 192Ktransaction_isolation REPEATABLE-READtmp_table_size 64Mlog-binmysql-binbinlog_formatmixedslow_query_loglong_query_time 1server-id 1key_buffer_size 8Mread_buffer_size 2Mread_rnd_buffer_size 2Mbulk_insert_buffer_size 64Mmyisam_sort_buffer_size 128Mmyisam_max_sort_file_size 10Gmyisam_repair_threads 1myisam_recoverinnodb_additional_mem_pool_size 16Minnodb_buffer_pool_size 200Minnodb_data_file_path ibdata1:10M:autoextendinnodb_file_io_threads 8innodb_thread_concurrency 16innodb_flush_log_at_trx_commit 1innodb_log_buffer_size 16Minnodb_log_file_size 512Minnodb_log_files_in_group 3innodb_max_dirty_pages_pct 60innodb_lock_wait_timeout 120 [mysqld3]socket /data/dbdata_3308/mysql.sockport 3308pid-file /data/dbdata_3308/3308.piddatadir /data/dbdata_3308user mysqlskip-name-resolvelower_case_table_names1innodb_file_per_table1back_log 50max_connections 300max_connect_errors 1000table_open_cache 2048max_allowed_packet 16Mbinlog_cache_size 2Mmax_heap_table_size 64Msort_buffer_size 2Mjoin_buffer_size 2Mthread_cache_size 64thread_concurrency 8query_cache_size 64Mquery_cache_limit 2Mft_min_word_len 4default-storage-engine innodbthread_stack 192Ktransaction_isolation REPEATABLE-READtmp_table_size 64Mlog-binmysql-binbinlog_formatmixedslow_query_loglong_query_time 1server-id 1key_buffer_size 8Mread_buffer_size 2Mread_rnd_buffer_size 2Mbulk_insert_buffer_size 64Mmyisam_sort_buffer_size 128Mmyisam_max_sort_file_size 10Gmyisam_repair_threads 1myisam_recoverinnodb_additional_mem_pool_size 16Minnodb_buffer_pool_size 200Minnodb_data_file_path ibdata1:10M:autoextendinnodb_file_io_threads 8innodb_thread_concurrency 16innodb_flush_log_at_trx_commit 1innodb_log_buffer_size 16Minnodb_log_file_size 512Minnodb_log_files_in_group 3innodb_max_dirty_pages_pct 60innodb_lock_wait_timeout 120 [mysqldump]quickmax_allowed_packet 256M[mysql]no-auto-rehashprompt\\u\\d \\R:\\m[myisamchk]key_buffer_size 512Msort_buffer_size 512Mread_buffer 8Mwrite_buffer 8M[mysqlhotcopy]interactive-timeout[mysqld_safe]open-files-limit 8192 mysqld_multi启动 点击(此处)折叠或打开 /usr/local/mysql/bin/mysqld_multi start 1/usr/local/mysql/bin/mysqld_multi start 2/usr/local/mysql/bin/mysqld_multi start 3 或者采用一条命令的形式 点击(此处)折叠或打开 /usr/local/mysql/bin/mysqld_multi start 1-3 更改原来密码处于安全考虑还需要删除系统中没有密码的帐号这里省略了 点击(此处)折叠或打开 /usr/local/mysql/bin/mysqladmin -uroot password password -S /data/dbdata_3306/mysql.sock/usr/local/mysql/bin/mysqladmin -uroot password password -S /data/dbdata_3307/mysql.sock/usr/local/mysql/bin/mysqladmin -uroot password password -S /data/dbdata_3308/mysql.sock 登录测试并创建admin密码(停止mysql的时候需要使用到) 点击(此处)折叠或打开 /usr/local/mysql/bin/mysql -uroot -ppassword -S /data/dbdata_3308/mysql.sockGRANT SHUTDOWN ON *.* TO adminlocalhost IDENTIFIED BY password;flush privileges;/usr/local/mysql/bin/mysql -uroot -ppassword -S /data/dbdata_3308/mysql.sockGRANT SHUTDOWN ON *.* TO adminlocalhost IDENTIFIED BY password;flush privileges;/usr/local/mysql/bin/mysql -uroot -ppassword -S /data/dbdata_3308/mysql.sockGRANT SHUTDOWN ON *.* TO adminlocalhost IDENTIFIED BY password;flush privileges; 更改环境变量 点击(此处)折叠或打开 vim /etc/profilePATH${PATH}:/usr/local/mysql/bin/source /etc/profile 添加到自动启动 点击(此处)折叠或打开 vim /etc/init.d/boot.local/usr/local/mysql/bin/mysqld_multi start 1-3 如果是rhel或者centos系统的话自启动文件/etc/rc.local 管理的话在本地都是采用 -S /data/dbdata_3308/mysql.sock如果在远程可以通过不同的端口连接上去坐管理操作。其他的和单实例的管理没什么区别 大家在管理多实例的话可以使用mysqlmanager实例管理器管理器来会比较方面限于篇幅这里就不在多做介绍 转载于:https://www.cnblogs.com/liqing1009/p/4863310.html
