mysql資料庫多執行個體部署

標籤：mysql5.6   多執行個體   資料庫   mysql   

本文系統：rhel5.8

ip : 192.168.100.150

資料庫版本：mysql-5.6.15

1、建立部署mysql服務帳號：

[[email protected] ~]# useradd -d /opt/mysql mysql[[email protected] ~]# echo "mysql" |passwd --stdin mysqlChanging password for user mysql.passwd: all authentication tokens updated successfully.

2、解壓無需免裝包：配置環境變數

[[email protected] ~]$ tar zxvf mysql-5.6.15-linux-glibc2.5-x86_64.tar.gz[[email protected] ~]$ mv mysql-5.6.15-linux-glibc2.5-x86_64 mysql-5.6.15[[email protected] mysql-5.6.15]$ vim ~/.bash_profileMYSQL_HOME=/opt/mysql/mysql-5.6.15export MYSQL_HOMEPATH=$MYSQL_HOME/bin:$PATHexport PATH

3、配置多執行個體資料庫設定檔

[[email protected] ~]# mkdir /Data_for_Mysql[[email protected] ~]# chown mysql.mysql -R /Data_for_Mysql/[[email protected] ~]# su - mysql[[email protected] ~]$ mkdir -p /Data_for_Mysql/3301/data /Data_for_Mysql/3301/logs[[email protected] ~]$ touch /Data_for_Mysql/3301/my.cnf[[email protected] ~]$ mkdir -p /Data_for_Mysql/3302/data /Data_for_Mysql/3302/logs[[email protected] ~]$ touch /Data_for_Mysql/3302/my.cnf

目錄結構：

[[email protected] Data_for_Mysql]$ pwd/Data_for_Mysql[[email protected] Data_for_Mysql]$ tree.|-- 3301|   |-- data|   |-- logs|   `-- my.cnf`-- 3302    |-- data    |-- logs    `-- my.cnf6 directories, 2 files[[email protected] Data_for_Mysql]$

配置my.cnf ######本設定檔只供參考

[[email protected] 3301]$ vim /Data_for_Mysql/3301/my.cnf[client]port = 3301socket = /Data_for_Mysql/3301/data/mysql.sock[mysqld]user=msqlport=3301bind-address=192.168.100.150socket = /Data_for_Mysql/3301/data/mysql.sock.3301pid-file = /Data_for_Mysql/3301/data/mysql.pidbasedir = /opt/mysql/mysql-5.6.15datadir = /Data_for_Mysql/3301/dataserver-id=1log-bin=mysql-binlog-bin-index= mysql-bin.index# LOGGINGlog_error=/Data_for_Mysql/3301/logs/mysql-error.logslow_query_log_file= /Data_for_Mysql/3301/logs/mysql-slow.logslow_query_log=1character-sets-dir = /opt/mysql/mysql-5.6.15/share/charsetsback_log = 2000max_connections = 1000connect-timeout = 60wait-timeout = 28800net_buffer_length = 16384max_allowed_packet = 64Mthread_stack = 192Kthread_cache_size = 20thread_concurrency = 128query_cache_size = 256Mquery_cache_limit = 2Mquery_cache_min_res_unit = 2default-time-zone = systemcharacter-set-server = utf8default-storage-engine = InnoDBtmp_table_size = 512Mmax_heap_table_size = 512Mmax_binlog_size = 1Gmax_relay_log_size = 1G[mysql]disable-auto-rehashdefault-character-set = utf8[[email protected] 3301]$ vim my.cnf[[email protected] 3301]$[[email protected] 3301]$ vim my.cnf[[email protected] 3301]$[[email protected] 3301]$ cat my.cnf[client]port = 3301socket = /Data_for_Mysql/3301/data/mysql.sock[mysqld]user=mysqlport=3301bind-address=192.168.100.150socket = /Data_for_Mysql/3301/data/mysql.sock.3301pid-file = /Data_for_Mysql/3301/data/mysql.pidbasedir = /opt/mysql/mysql-5.6.15datadir = /Data_for_Mysql/3301/dataserver-id=1log-bin=mysql-binlog-bin-index= mysql-bin.index# LOGGINGlog_error=/Data_for_Mysql/3301/logs/mysql-error.logslow_query_log_file= /Data_for_Mysql/3301/logs/mysql-slow.logslow_query_log=1character-sets-dir = /opt/mysql/mysql-5.6.15/share/charsetsback_log = 2000max_connections = 1000connect-timeout = 60wait-timeout = 28800net_buffer_length = 16384max_allowed_packet = 64Mthread_stack = 192Kthread_cache_size = 20thread_concurrency = 128query_cache_size = 256Mquery_cache_limit = 2Mquery_cache_min_res_unit = 2default-time-zone = systemcharacter-set-server = utf8default-storage-engine = InnoDBtmp_table_size = 512Mmax_heap_table_size = 512Mmax_binlog_size = 1Gmax_relay_log_size = 1G[mysql]disable-auto-rehashdefault-character-set = utf8

4、初始化資料庫

[[email protected] scripts]$ pwd/opt/mysql/mysql-5.6.15/scripts[[email protected] scripts]$ ./mysql_install_db --defaults-file=/Data_for_Mysql/3301/my.cnf --user=mysql --basedir=/opt/mysql/mysql-5.6.15 --datadir=/Data_for_Mysql/3301/data/

5、啟動3301執行個體資料庫  ###啟動資料庫必須在mysql目錄；

[[email protected] mysql-5.6.15]$ pwd/opt/mysql/mysql-5.6.15[[email protected] mysql-5.6.15]$ mysqld_safe --defaults-file=/Data_for_Mysql/3301/my.cnf --user=mysql --basedir=/opt/mysql/mysql-5.6.15 --datadir=/Data_for_Mysql/3301/data/ &[[email protected] mysql-5.6.15]$ netstat -nat |grep 3301tcp        0      0 192.168.100.150:3301        0.0.0.0:*                   LISTEN

Ps:3302 執行個體操作和上面一樣，只需改動連接埠及相應目錄即可；

3302設定檔：

[[email protected] 3302]$ cat my.cnf[client]port = 3302socket = /Data_for_Mysql/3302/data/mysql.sock[mysqld]user=mysqlport=3302bind-address=192.168.100.150socket = /Data_for_Mysql/3302/data/mysql.sock.3302pid-file = /Data_for_Mysql/3302/data/mysql.pidbasedir = /opt/mysql/mysql-5.6.15datadir = /Data_for_Mysql/3302/dataserver-id=1log-bin=mysql-binlog-bin-index= mysql-bin.index# LOGGINGlog_error=/Data_for_Mysql/3302/logs/mysql-error.logslow_query_log_file= /Data_for_Mysql/3302/logs/mysql-slow.logslow_query_log=1character-sets-dir = /opt/mysql/mysql-5.6.15/share/charsetsback_log = 2000max_connections = 1000connect-timeout = 60wait-timeout = 28800net_buffer_length = 16384max_allowed_packet = 64Mthread_stack = 192Kthread_cache_size = 20thread_concurrency = 128query_cache_size = 256Mquery_cache_limit = 2Mquery_cache_min_res_unit = 2default-time-zone = systemcharacter-set-server = utf8default-storage-engine = InnoDBtmp_table_size = 512Mmax_heap_table_size = 512Mmax_binlog_size = 1Gmax_relay_log_size = 1G[mysql]disable-auto-rehashdefault-character-set = utf8

3302 執行個體初始化；

[[email protected] scripts]$ pwd/opt/mysql/mysql-5.6.15/scripts[[email protected] scripts]$ ./mysql_install_db --defaults-file=/Data_for_Mysql/3302/my.cnf --user=mysql --basedir=/opt/mysql/mysql-5.6.15 --datadir=/Data_for_Mysql/3302/data/

3302 啟動該執行個體：

[[email protected] mysql-5.6.15]$ mysqld_safe --defaults-file=/Data_for_Mysql/3302/my.cnf --user=mysql --basedir=/opt/mysql/mysql-5.6.15 --datadir=/Data_for_Mysql/3302/data/ &[[email protected] mysql-5.6.15]$ netstat -nat |grep 330tcp        0      0 192.168.100.150:3301        0.0.0.0:*                   LISTENtcp        0      0 192.168.100.150:3302        0.0.0.0:*                   LISTEN

6、建立root密碼，要區別執行個體

[[email protected] mysql-5.6.15]$ mysqladmin -uroot password ‘root123‘ -S /Data_for_Mysql/3301/data/mysql.sock.3301

6.1、關閉mysql，區別執行個體

[[email protected] mysql-5.6.15]$ mysqladmin -uroot -proot123 -S /Data_for_Mysql/3301/data/mysql.sock.3301 shutdownWarning: Using a password on the command line interface can be insecure.150402 15:44:52 mysqld_safe mysqld from pid file /Data_for_Mysql/3301/data/mysql.pid ended[1]-  Done                    mysqld_safe --defaults-file=/Data_for_Mysql/3301/my.cnf --user=mysql --basedir=/opt/mysql/mysql-5.6.15 --datadir=/Data_for_Mysql/3301/data/

本文出自 “大風” 部落格，請務必保留此出處http://lansgg.blog.51cto.com/5675165/1627717

mysql資料庫多執行個體部署