# mysql的多實例有兩種方式可以實現，兩種方式各有利弊。 第一種是使用多個配置文件啟動不同的進程來實現多實例，這種方式的優勢邏輯簡單，配置簡單，缺點是管理起來不太方便。 > 環境介紹： > mysql 版本：5.1.59 > 操作系統：Centos 5.5~5.6 > mysql實例數：3個 > 實例占用端口分別為：3306、3307、3308 ## 必要軟件包 ``` yum -y install ncurses-devel gcc gcc-c++ gcc-g77 autoconf automake zlib* fiex* libxml* libmcrypt* libtool-ltdl-devel* libtool make ``` ## 創建mysql用戶 ``` /usr/sbin/groupadd mysql /usr/sbin/useradd -g mysql mysql -s /sbin/nologin ``` ## 編譯安裝MySQL ``` cd /usr/local/src/mysql-5.1.59 ./configure \ '--prefix=/usr/local/mysql' \ '--with-charset=utf8' \ '--with-extra-charsets=complex' \ '--with-pthread' \ '--enable-thread-safe-client' \ '--with-ssl' \ '--with-client-ldflags=-all-static' \ '--with-mysqld-ldflags=-all-static' \ '--with-plugins=partition,innobase,blackhole,myisam,innodb_plugin,heap,archive' \ '--enable-shared' '--enable-assembler' # 接著執行編譯 make &&　make install ``` ## 初始化數據庫 ``` /usr/local/mysql/bin/mysql_install_db --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data/3306/data --user=mysql /usr/local/mysql/bin/mysql_install_db --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data/3307/data --user=mysql /usr/local/mysql/bin/mysql_install_db --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data/3308/data --user=mysql ``` *為什么要初始化數據庫? * 答:初始化的主要目的就是創建基礎的數據庫文件,例如生成MySQL庫,表等. ## 授權數據庫權限給mysql用戶 ``` setfacl -m u:mysql:rwx -R /usr/local/mysql setfacl -m d:u:mysql:rwx -R /usr/local/mysql ``` 第一種使用多個配置文件啟動多個不同進程的情況： ## 創建3個實例的配置文件 ### 3306端口的配置文件 ``` vim /usr/local/mysql/data/3306/my.cnf ``` ``` [client] port = 3306 socket = /usr/local/mysql/data/3306/mysql.sock [mysqld] datadir = /usr/local/mysql/data/3306/data/ skip-name-resolve lower_case_table_names=1 innodb_file_per_table=1 port = 3306 socket = /usr/local/mysql/data/3306/mysql.sock back_log = 50 max_connections = 300 max_connect_errors = 1000 table_open_cache = 2048 max_allowed_packet = 16M binlog_cache_size = 2M max_heap_table_size = 64M sort_buffer_size = 2M join_buffer_size = 2M thread_cache_size = 64 thread_concurrency = 8 query_cache_size = 64M query_cache_limit = 2M ft_min_word_len = 4 default-storage-engine = innodb thread_stack = 192K transaction_isolation = REPEATABLE-READ tmp_table_size = 64M log-bin=mysql-bin binlog_format=mixed slow_query_log long_query_time = 1 server-id = 3306 key_buffer_size = 8M read_buffer_size = 2M read_rnd_buffer_size = 2M bulk_insert_buffer_size = 64M myisam_sort_buffer_size = 128M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 myisam_recover innodb_additional_mem_pool_size = 16M innodb_buffer_pool_size = 200M innodb_data_file_path = ibdata1:10M:autoextend innodb_file_io_threads = 8 innodb_thread_concurrency = 16 innodb_flush_log_at_trx_commit = 1 innodb_log_buffer_size = 16M innodb_log_file_size = 512M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 60 innodb_lock_wait_timeout = 120 [mysqldump] quick max_allowed_packet = 256M [mysql] no-auto-rehash prompt=\\u@\\d \\R:\\m> [myisamchk] key_buffer_size = 512M sort_buffer_size = 512M read_buffer = 8M write_buffer = 8M [mysqlhotcopy] interactive-timeout [mysqld_safe] log-error=/usr/local/mysql/data/3306/mysql_error3306.err pid-file=/usr/local/mysql/data/3306/mysqld.pid open-files-limit = 8192 ``` ### 3307端口的配置文件 ``` vim /usr/local/mysql/data/3307/my.cnf ``` ``` [client] port = 3307 socket = /usr/local/mysql/data/3307/mysql.sock [mysqld] datadir=/usr/local/mysql/data/3307/data/ skip-name-resolve lower_case_table_names=1 innodb_file_per_table=1 port = 3307 socket = /usr/local/mysql/data/3307/mysql.sock back_log = 50 max_connections = 300 max_connect_errors = 1000 table_open_cache = 2048 max_allowed_packet = 16M binlog_cache_size = 2M max_heap_table_size = 64M sort_buffer_size = 2M join_buffer_size = 2M thread_cache_size = 64 thread_concurrency = 8 query_cache_size = 64M query_cache_limit = 2M ft_min_word_len = 4 default-storage-engine = innodb thread_stack = 192K transaction_isolation = REPEATABLE-READ tmp_table_size = 64M log-bin=mysql-bin binlog_format=mixed slow_query_log long_query_time = 1 server-id = 3307 key_buffer_size = 8M read_buffer_size = 2M read_rnd_buffer_size = 2M bulk_insert_buffer_size = 64M myisam_sort_buffer_size = 128M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 myisam_recover innodb_additional_mem_pool_size = 16M innodb_buffer_pool_size = 200M innodb_data_file_path = ibdata1:10M:autoextend innodb_file_io_threads = 8 innodb_thread_concurrency = 16 innodb_flush_log_at_trx_commit = 1 innodb_log_buffer_size = 16M innodb_log_file_size = 512M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 60 innodb_lock_wait_timeout = 120 [mysqldump] quick max_allowed_packet = 256M [mysql] no-auto-rehash prompt=\\u@\\d \\R:\\m> [myisamchk] key_buffer_size = 512M sort_buffer_size = 512M read_buffer = 8M write_buffer = 8M [mysqlhotcopy] interactive-timeout [mysqld_safe] log-error=/usr/local/mysql/data/3307/mysql_error3307.err pid-file=/usr/local/mysql/data/3307/mysqld.pid open-files-limit = 8192 ``` ### 3308端口的配置文件 ``` vim /usr/local/mysql/data/3308/my.cnf ``` ``` [client] port = 3308 socket = /usr/local/mysql/data/3308/mysql.sock [mysqld] datadir=/usr/local/mysql/data/3308/data skip-name-resolve lower_case_table_names=1 innodb_file_per_table=1 port = 3308 socket = /usr/local/mysql/data/3308/mysql.sock back_log = 50 max_connections = 300 max_connect_errors = 1000 table_open_cache = 2048 max_allowed_packet = 16M binlog_cache_size = 2M max_heap_table_size = 64M sort_buffer_size = 2M join_buffer_size = 2M thread_cache_size = 64 thread_concurrency = 8 query_cache_size = 64M query_cache_limit = 2M ft_min_word_len = 4 default-storage-engine = innodb thread_stack = 192K transaction_isolation = REPEATABLE-READ tmp_table_size = 64M log-bin=mysql-bin binlog_format=mixed slow_query_log long_query_time = 1 server-id = 3308 key_buffer_size = 8M read_buffer_size = 2M read_rnd_buffer_size = 2M bulk_insert_buffer_size = 64M myisam_sort_buffer_size = 128M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 myisam_recover innodb_additional_mem_pool_size = 16M innodb_buffer_pool_size = 200M innodb_data_file_path = ibdata1:10M:autoextend innodb_file_io_threads = 8 innodb_thread_concurrency = 16 innodb_flush_log_at_trx_commit = 1 innodb_log_buffer_size = 16M innodb_log_file_size = 512M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 60 innodb_lock_wait_timeout = 120 [mysqldump] quick max_allowed_packet = 256M [mysql] no-auto-rehash prompt=\\u@\\d \\R:\\m> [myisamchk] key_buffer_size = 512M sort_buffer_size = 512M read_buffer = 8M write_buffer = 8M [mysqlhotcopy] interactive-timeout [mysqld_safe] log-error=/usr/local/mysql/data/3308/mysql_error3308.err pid-file=/usr/local/mysql/data/3308/mysqld.pid open-files-limit = 8192 ``` ## 創建自啟動文件 `vim /usr/local/mysql/data/3306/mysqld` , 3306的啟動文件 ``` #!/bin/bash mysql_port=3306 mysql_username="admin" mysql_password="password" function_start_mysql(){ printf "Starting MySQL...\n" /bin/bash /usr/local/mysql/bin/mysqld_safe --defaults-file=/usr/local/mysql/data/${mysql_port}/my.cnf 2>&1 /dev/null & } function_stop_mysql(){ printf "Stoping MySQL...\n" /usr/local/mysql/bin/mysqladmin -u ${mysql_username} -p${mysql_password} -S /usr/local/mysql/data/${mysql_port}/mysql.sock shutdown } function_restart_mysql() { printf "Restarting MySQL...\n" function_stop_mysql function_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 in start) function_start_mysql;; stop) function_stop_mysql;; kill) function_kill_mysql;; restart) function_stop_mysql function_start_mysql;; *) echo "Usage: /usr/local/mysql/data/${mysql_port}/mysqld {start|stop|restart|kill}";; esac ``` `vim /usr/local/mysql/data/3307/mysqld` , 3307的啟動文件 ``` #!/bin/bash mysql_port=3307 mysql_username="admin" mysql_password="password" function_start_mysql() { printf "Starting MySQL...\n" /bin/bash /usr/local/mysql/bin/mysqld_safe --defaults-file=/usr/local/mysql/data/${mysql_port}/my.cnf 2>&1 /dev/null & } function_stop_mysql() { printf "Stoping MySQL...\n" /usr/local/mysql/bin/mysqladmin -u ${mysql_username} -p${mysql_password} -S /usr/local/mysql/data/${mysql_port}/mysql.sock shutdown } function_restart_mysql() { printf "Restarting MySQL...\n" function_stop_mysql function_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 in start) function_start_mysql;; stop) function_stop_mysql;; kill) function_kill_mysql;; restart) function_stop_mysql function_start_mysql;; *) echo "Usage: /usr/local/mysql/data/${mysql_port}/mysqld {start|stop|restart|kill}";; esac ``` `vim /usr/local/mysql/data/3308/mysqld` , 3308的啟動文件 ``` #!/bin/bash mysql_port=3308 mysql_username="admin" mysql_password="password" function_start_mysql() { printf "Starting MySQL...\n" /bin/bash /usr/local/mysql/bin/mysqld_safe --defaults-file=/usr/local/mysql/data/${mysql_port}/my.cnf 2>&1 /dev/null & } function_stop_mysql() { printf "Stoping MySQL...\n" /usr/local/mysql/bin/mysqladmin -u ${mysql_username} -p${mysql_password} -S /usr/local/mysql/data/${mysql_port}/mysql.sock shutdown } function_restart_mysql() { printf "Restarting MySQL...\n" function_stop_mysql function_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 in start) function_start_mysql;; stop) function_stop_mysql;; kill) function_kill_mysql;; restart) function_stop_mysql function_start_mysql;; *) echo "Usage: /usr/local/mysql/data/${mysql_port}/mysqld {start|stop|restart|kill}";; esac ``` 上面的三個啟動文件中都包含了數據庫密碼等敏感信息,所以為了安全考慮,將權限改為只有root可以操作，執行下面的命令 ``` find /usr/local/mysql/data -name 'mysqld'|xargs chmod 700 && find /usr/local/mysql/data -name 'mysqld'|xargs setfacl -b && find /usr/local/mysql/data -name 'mysqld'|xargs chown root.root ``` ## 啟動3306 3307 3308的MySQL ``` cd /usr/local/mysql/data && ./3306/mysqld start cd /usr/local/mysql/data && ./3307/mysqld start cd /usr/local/mysql/data && ./3308/mysqld start ``` > **啟動報錯** > 151016 06:36:48 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data/3307/data/ > /usr/local/mysql/bin/mysqld: File './mysql-bin.~rec~' not found (Errcode: 28) > 151016 6:36:48 [ERROR] MYSQL_BIN_LOG::open_purge_index_file failed to open register file. > 151016 6:36:48 [ERROR] MYSQL_BIN_LOG::open_index_file failed to sync the index file. > 151016 6:36:48 [ERROR] Aborting > 151016 6:36:48 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete > 151016 06:36:48 mysqld_safe mysqld from pid file /usr/local/mysql/data/3307/mysqld.pid ended ***錯誤代碼 (Errcode: 28)*** **產生錯誤原因(磁盤空間已滿)** ``` # df -Th 文件系統 類型 容量 已用 可用 已用% 掛載點 /dev/mapper/VolGroup00-LogVol00 ext3 8.9G 8.4G 0 100% / /dev/sda1 ext3 99M 12M 82M 13% /boot tmpfs tmpfs 1014M 0 1014M 0% /dev/shm ``` **解決方案** 釋放硬盤空間，重新啟動即可。 ## 將mysql的bin加入到path中 ``` ln -s /usr/local/mysql/bin/mysql /usr/local/sbin/mysql cd ~ #或者，把path添加到當前用戶目錄的bashrc中，如果需要全局設定，請修改`/etc/profile` vi .bashrc #加入以下內容 PATH=/usr/local/mysql/bin:$PATH source /etc/profile ``` ## 修改默認root密碼(指定sock文件) ``` mysqladmin -uroot password 'aaaaaa' -S /usr/local/mysql/data/3306/mysql.sock mysqladmin -uroot password 'aaaaaa' -S /usr/local/mysql/data/3307/mysql.sock mysqladmin -uroot password 'aaaaaa' -S /usr/local/mysql/data/3308/mysql.sock ``` ##登錄數據庫增加進程關閉`admin(password)`賬號 ``` /usr/local/mysql/bin/mysql -uroot -ppassword -S /data/dbdata_3306/mysql.sock GRANT SHUTDOWN ON *.* TO 'admin'@'localhost' IDENTIFIED BY 'password'; flush privileges; ``` ## 本機登錄指定sock登錄 ``` mysql -uroot -paaaaaa -S /usr/local/mysql/data/3306/mysql.sock mysql -uroot -paaaaaa -S /usr/local/mysql/data/3307/mysql.sock mysql -uroot -paaaaaa -S /usr/local/mysql/data/3308/mysql.sock ``` # 后期增加一個實例的情況 ## 初始化數據庫 `/usr/local/mysql/bin/mysql_install_db --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data/3309/data --user=mysql` ## 新建一個默認配置文件 ``` vim /usr/local/mysql/data/3309/my.cnf ``` ``` [client] port = 3309 socket = /usr/local/mysql/data/3309/mysql.sock [mysqld] datadir = /usr/local/mysql/data/3309/data/ skip-name-resolve lower_case_table_names=1 innodb_file_per_table=1 port = 3309 socket = /usr/local/mysql/data/3309/mysql.sock back_log = 50 max_connections = 300 max_connect_errors = 1000 table_open_cache = 2048 max_allowed_packet = 16M binlog_cache_size = 2M max_heap_table_size = 64M sort_buffer_size = 2M join_buffer_size = 2M thread_cache_size = 64 thread_concurrency = 8 query_cache_size = 64M query_cache_limit = 2M ft_min_word_len = 4 default-storage-engine = innodb thread_stack = 192K transaction_isolation = REPEATABLE-READ tmp_table_size = 64M log-bin=mysql-bin binlog_format=mixed slow_query_log long_query_time = 1 server-id = 3309 key_buffer_size = 8M read_buffer_size = 2M read_rnd_buffer_size = 2M bulk_insert_buffer_size = 64M myisam_sort_buffer_size = 128M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 myisam_recover innodb_additional_mem_pool_size = 16M innodb_buffer_pool_size = 200M innodb_data_file_path = ibdata1:10M:autoextend innodb_file_io_threads = 8 innodb_thread_concurrency = 16 innodb_flush_log_at_trx_commit = 1 innodb_log_buffer_size = 16M innodb_log_file_size = 512M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 60 innodb_lock_wait_timeout = 120 [mysqldump] quick max_allowed_packet = 256M [mysql] no-auto-rehash prompt=\\u@\\d \\R:\\m> [myisamchk] key_buffer_size = 512M sort_buffer_size = 512M read_buffer = 8M write_buffer = 8M [mysqlhotcopy] interactive-timeout [mysqld_safe] log-error=/usr/local/mysql/data/3309/mysql_error3309.err pid-file=/usr/local/mysql/data/3309/mysqld.pid open-files-limit = 8192 ``` ## 創建自啟動文件 ``` #!/bin/bash mysql_port=3309 mysql_username="admin" mysql_password="password" function_start_mysql() { printf "Starting MySQL...\n" /bin/bash /usr/local/mysql/bin/mysqld_safe --defaults-file=/usr/local/mysql/data/${mysql_port}/my.cnf 2>&1 > /dev/null & } function_stop_mysql() { printf "Stoping MySQL...\n" /usr/local/mysql/bin/mysqladmin -u ${mysql_username} -p${mysql_password} -S /usr/local/mysql/data/${mysql_port}/mysql.sock shutdown } function_restart_mysql() { printf "Restarting MySQL...\n" function_stop_mysql function_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 in start) function_start_mysql;; stop) function_stop_mysql;; kill) function_kill_mysql;; restart) function_stop_mysql function_start_mysql;; *) echo "Usage: /usr/local/mysql/data/${mysql_port}/mysqld {start|stop|restart|kill}";; esac ``` ## 授權啟動文件可執行 ``` chmod a+x /usr/local/mysql/data/3309/mysqld ``` 相關博文參考：? [mysql多實例的配置和管理](http://blog.chinaunix.net/uid-20639775-id-3438560.html)