多設定檔部署mysql單機多執行個體

標籤：

1.安裝gcc-c++、ncurses依賴包

# yum install gcc-c++  ncurses-devel

2.安裝cmake，用來編譯mysql

# tar -xvf cmake-3.2.0-.tar.gz# mv cmake-3.2.0 cmake# mv cmake /tmp/# cd /tmp/cmake/# ./bootstrap# make# make install# cmake --version

3.安裝bison

# tar -xvf bison-3.0.tar.gz# mv bison-3.0 bison# mv bison /tmp# cd /tmp/bison/# ./cofigure# make# make install# bison --version

4.建立mysql使用者

# /usr/sbin/groupadd mysql# useradd -s /sbin/nologin -g  mysql -M mysql

5.編譯安裝

配置

# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_UNIX_ADDR=/tmp/mysqld.sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DEXTRA_CHARSETS=all -DWITH_EXTRA_CHARSETS:STRING=utf8,gbk -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DENABLED_LOCAL_INFILE=1 -DMYSQL_TCP_PORT=3306 -DMYSQL_USER=mysql 

產生可執行檔

# make

安裝

# make install

6.建立多執行個體的資料檔案目錄

# mkdir -p /data/{3306,3307}/data/# mkdir -p /data/{3306,3307}/log/# tree /data/data                            #多執行個體的根目錄├── 3306                         #3306執行個體的根目錄│   ├── data                     #3306執行個體的資料目錄│   └── log                      #3306執行個體的日誌目錄│   └── tmp                      #3306執行個體的臨時檔案目錄└── 3307                         #3307執行個體的根目錄    ├── data                     #3307執行個體的資料目錄    └── log                      #3307執行個體的日誌目錄    └── tmp                      #3307執行個體的臨時檔案目錄

data  - 存放資料檔案：資料，索引，二進位日誌（bin log），重放日誌（replay log）。
log    - 存放記錄檔：慢查詢日誌，錯誤記錄檔。
tmp  - 存放臨時檔案：進程ID檔案。

7.配置多執行個體的設定檔
為每個執行個體建立一個設定檔

# vi /data/3306/my.cnf[client]default-character-set = utf8port = 3306socket = /data/3306/tmp/mysql.sock[mysqld]server-id = 1collation-server = utf8_unicode_ciinit-connect = ‘SET NAMES utf8‘character-set-server = utf8port        = 3306socket      = /data/3306/tmp/mysql.sockdatadir     = /data/3306/data/log-error   = /data/3306/log/mysql.errpid-file     = /data/3306/tmp/mysql.pidlog-bin     = /data/3306/data/mysql-binrelay_log     = /data/3306/data/relay-binslow_query_log_file = /data/3306/log/slowquery.loggeneral_log_file     = /data/3306/log/general.logskip-external-lockingkey_buffer_size = 16Mmax_allowed_packet = 1Mtable_open_cache = 64sort_buffer_size = 512Knet_buffer_length = 8Kread_buffer_size = 256Kread_rnd_buffer_size = 512Kmyisam_sort_buffer_size = 8Mbinlog_format = mixedslow_query_log = 1long_query_time = 1general_log = off[mysqldump]quickmax_allowed_packet = 16M[mysql]no-auto-rehash[myisamchk]key_buffer_size = 20Msort_buffer_size = 20Mread_buffer = 2Mwrite_buffer = 2M[mysqlhotcopy]interactive-timeout

# vi /data/3307/my.cnf[client]default-character-set = utf8port = 3307socket = /data/3307/tmp/mysql.sock[mysqld]server-id = 2collation-server = utf8_unicode_ciinit-connect = ‘SET NAMES utf8‘character-set-server = utf8port        = 3307socket      = /data/3307/tmp/mysql.sockdatadir     = /data/3307/data/log-error   = /data/3307/log/mysql.errpid-file     = /data/3307/tmp/mysql.pidlog-bin     = /data/3307/data/mysql-binrelay_log     = /data/3307/data/relay-binslow_query_log_file = /data/3307/log/slowquery.loggeneral_log_file     = /data/3307/log/general.logskip-external-lockingkey_buffer_size = 16Mmax_allowed_packet = 1Mtable_open_cache = 64sort_buffer_size = 512Knet_buffer_length = 8Kread_buffer_size = 256Kread_rnd_buffer_size = 512Kmyisam_sort_buffer_size = 8Mbinlog_format = mixedslow_query_log = 1long_query_time = 1general_log = off[mysqldump]quickmax_allowed_packet = 16M[mysql]no-auto-rehash[myisamchk]key_buffer_size = 20Msort_buffer_size = 20Mread_buffer = 2Mwrite_buffer = 2M[mysqlhotcopy]interactive-timeout

8.配置多執行個體的檔案許可權
授權mysql使用者和組管理多執行個體目錄/data

# chown -R mysql.mysql /data

9.配置mysql命令的環境變數
方法一：

# echo ‘export PATH=/usr/local/mysql/bin:$PATH‘ >> /etc/profile
# source /etc/profile

方法二：
把/usr/local/mysql/bin/下面的命令拷貝到全域系統命令路徑/usr/local/sbin/下，或者做個link

10.初始化資料庫,建立基礎的資料庫檔案

# cd /usr/local/mysql/scripts/#./mysql_install_db --defaults-file=/data/3306/my.cnf --user=mysql --basedir=/usr/local/mysql#./mysql_install_db --defaults-file=/data/3307/my.cnf --user=mysql --basedir=/usr/local/mysql

11.啟動

# mysqld_safe --defaults-file=/data/3306/my.cnf 2>&1 > /dev/null &# mysqld_safe --defaults-file=/data/3307/my.cnf 2>&1 > /dev/null &

12.修改的root使用者的密碼並且限制只能本機登陸

# mysqladmin -S /data/3306/tmp/mysql.sock -u root -h ‘localhost‘ password ‘xxxx‘# mysqladmin -S /data/3307/tmp/mysql.sock -u root -h ‘localhost‘ password ‘xxxx‘

13.使用root使用者登陸

# mysql -S  /data/3306/tmp/mysql.sock -u root -p# mysql -S  /data/3307/tmp/mysql.sock -u root -p

14.停止執行個體

# mysqladmin -S /data/3306/tmp/mysql.sock -u root -p shutdown# mysqladmin -S /data/3307/tmp/mysql.sock -u root -p shutdown

　　

多設定檔部署mysql單機多執行個體