cmake安裝mysql及多執行個體配置方法

標籤：sla   mysq   arp   federated   class   cmd   bundle   socket   without   

一、安裝mysql

1、 生產環境如何選擇MySQL版本

1. 選擇社區版的穩定GA版本
2.可以選擇5.1或5.5.互連網公司主流5.5， 其次是5.1和5.6
3.選擇發布後6個月以上的GA版
4.要選擇前後幾個月沒有打的BUG修複的版本，而不是大量修複BUG的幾種版本
5.最好選擇向後較長時間沒有更新發布的版本
6.考慮開發人員開發程式使用的版本是否相容你選的版本
7.企業非核心業務採用新版本的資料庫GA版本軟體
8.作為內部開發測試資料庫環境，跑大概3-6個月時間
9.向DBA高手請教，或者在技術氛圍好的群裡和大家一起交流，使用真正高手用過的好用的GA版本產品
10.經過上述工序後，若沒有重要的功能BUG或效能瓶頸，則可以開始考慮作為任何業務資料服務的後端資料庫軟體。

2、 MySql資料庫的多種安裝方法

1. yum/rpm安裝
2.常規方式編譯安裝
3.採用cmake方式編譯安裝
4.二進位方式免編譯安裝

3、 需要的軟體

cmake-2.8.8.tar.gzrubymysql-5.5.32.tar.gz

4、編譯安裝cmake

mkdir /home/oldboy/toolscd /home/oldboy/tools/tar xf cmake-2.8.8.tar.gzcd cmake-2.8.8./configure make &make install

5、編譯安裝mysql

tar xf mysql-5.5.32.tar.gzyum install ncurses-devel -ygroupadd mysqluseradd mysql -s /sbin/nologin -M -g mysql

6、cmake編譯安裝mysql

cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.5.32 -DMYSQL_DATADIR=/application/mysql-5.5.32/data -DMYSQL_UNIX_ADDR=/application/mysql-5.5.32/tmp/mysql.sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii -DENABLED_LOCAL_INFILE=ON -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_FEDERATED_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 -DWITHOUT_PARTITION_STORAGE_ENGINE=1 -DWITH_FAST_MUTEXES=1 -DWITH_ZLIB=bundled -DENABLED_LOCAL_INFILE=1 -DWITH_READLINE=1 -DWITH_EMBEDDED_SERVER=1 -DWITH_DEBUG=0make && make installexport PATH="/application/mysql/bin:$PATH"echo ‘export PATH=/usr/local/mysql/bin:$PATH‘ >> /etc/profile

7、檢查是否安裝成功

echo $?0   （輸出0表示成功）

8、建立軟串連

ln -s /application/mysql-5.5.32 /application/mysql

 

9、複製mysql設定檔

cd /home/oldboy/tools/mysql-5.5.32cp support-files/my-small.cnf /etc/my.cnf

 

二、部署多執行個體的MySQL資料庫

1、使用建立以下目錄及檔案

[[email protected] tools]#mkdir -pv /data/{3306,3307}/data[[email protected] tools]# tree datadata├── 3306│   ├── my.cnf│   └── mysql└── 3307    ├── my.cnf    └── mysql2 directories, 4 files[[email protected] tools]# 

my.cnf檔案樣本，此例是3306連接埠的配置，3307執行個體要將以下兩個檔案中所有的3306替換成你的執行個體連接埠，此次是3307.
在vim編輯器下：%s/3306/3307/g
並修改my.cnf下server-id與其他執行個體不同id（server-id = 1或2總之不一樣就行）

 

 1 [client]  2 port            = 3306                                      3 socket          = /data/3306/mysql.sock  4   5 [mysql]  6 no-auto-rehash  7   8 [mysqld]  9 user    = mysql 10 port    = 3306 11 socket  = /data/3306/mysql.sock 12 basedir = /application/mysql 13 datadir = /data/3306/data 14 open_files_limit    = 1024 15 back_log = 600 16 max_connections = 800 17 max_connect_errors = 3000 18 table_cache = 614 19 external-locking = FALSE 20 max_allowed_packet =8M 21 sort_buffer_size = 1M 22 join_buffer_size = 1M 23 thread_cache_size = 100 24 thread_concurrency = 2 25 query_cache_size = 2M 26 query_cache_limit = 1M 27 query_cache_min_res_unit = 2k 28 #default_table_type = InnoDB 29 thread_stack = 192K 30 #transaction_isolation = READ-COMMITTED 31 tmp_table_size = 2M 32 max_heap_table_size = 2M 33 long_query_time = 1 34 #log_long_format 35 #log-error = /data/3306/error.log 36 #log-slow-queries = /data/3306/slow.log 37 pid-file = /data/3306/mysql.pid 38 log-bin = /data/3306/mysql-bin 39 relay-log = /data/3306/relay-bin 40 relay-log-info-file = /data/3306/relay-log.info 41 binlog_cache_size = 1M 42 max_binlog_cache_size = 1Mmax_binlog_size = 2M 44 expire_logs_days = 7 45 key_buffer_size = 16M 46 read_buffer_size = 1M 47 read_rnd_buffer_size = 1M 48 bulk_insert_buffer_size = 1M 49 #myisam_sort_buffer_size = 1M 50 #myisam_max_sort_file_size = 10G 51 #myisam_max_extra_sort_file_size = 10G 52 #myisam_repair_threads = 1 53 #myisam_recover 54  55 lower_case_table_names = 1 56 skip-name-resolve 57 slave-skip-errors = 1032,1062 58 replicate-ignore-db=mysql 59  60 server-id = 1 61  62 innodb_additional_mem_pool_size = 4M 63 innodb_buffer_pool_size = 32M 64 innodb_data_file_path = ibdata1:128M:autoextend 65 innodb_file_io_threads = 4 66 innodb_thread_concurrency = 8 67 innodb_flush_log_at_trx_commit = 2 68 innodb_log_buffer_size = 2M 69 innodb_log_file_size = 4M 70 innodb_log_files_in_group = 3 71 innodb_max_dirty_pages_pct = 90 72 innodb_lock_wait_timeout = 120 73 innodb_file_per_table = 0 74 [mysqldump] 75 quick 76 max_allowed_packet = 2M 77  78 [mysqld_safe] 79 log-error=/data/3306/mysql_oldboy3306.err 80 pid-file=/data/3306/mysqld.pid     

　　

 mysql檔案樣本

10 #init 11 port=3306 12 mysql_user="root" 13 mysql_pwd="oldboy" 14 CmdPath="/application/mysql/bin" 15 mysql_sock="/data/${port}/mysql.sock" 16 #startup function 17 function_start_mysql() 18 { 19     if [ ! -e "$mysql_sock" ];then 20       printf "Starting MySQL...\n" 21       /bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 > /dev/null & 22     else 23       printf "MySQL is running...\n" 24       exit 25     fi 26 } 27  28 #stop function 29 function_stop_mysql() 30 { 31     if [ ! -e "$mysql_sock" ];then 32        printf "MySQL is stopped...\n" 33        exit 34     else 35        printf "Stoping MySQL...\n" 36        ${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdown 37    fi 38 } 39  40 #restart function 41 function_restart_mysql() 42 { 43     printf "Restarting MySQL...\n" 44     function_stop_mysql 45     sleep 2 46     function_start_mysql 47 } 48  49 case $1 in 50 start) 51     function_start_mysql 52 ;; 53 stop) 54     function_stop_mysql 55 ;; 56 restart) 57     function_restart_mysql 58 ;; 59 *) 60     printf "Usage: /data/${port}/mysql {start|stop|restart}\n" 61 esac               

2、授權及增加執行許可權

chown -R mysql.mysql /datafind /data/ -type f -name "mysql"|xargs chmod +x

3、初始化資料庫（兩個執行個體分別初始化）

cd /application/mysql/scripts/./mysql_install_db --basedir=/application/mysql --datadir=/data/3306/data --user=mysql./mysql_install_db --basedir=/application/mysql --datadir=/data/3307/data --user=mysql

4、啟動多執行個體

/data/3306/mysql startStarting MySQL.../data/3307/mysql start Starting MySQL...

 5、多執行個體資料庫的登陸

本地登陸：mysql -S /data/3306/mysql.sock遠程登入：mysql –h主機名稱 -u使用者名稱 –p密碼 –P3306mysql> system mysql -S /data/3307/mysql.sock

 

6、 為使用者增加密碼，修改密碼

mysqladmin -S /data/3306/mysql.sock -uroot password ‘123.asd‘mysqladmin -S /data/3307/mysql.sock –uroot –p123.asd password ‘123456‘

　　

7、線上添加一個執行個體3308

 

mkdir /data/3308/data -p/application/mysql/scripts/mysql_install_db --basedir=/application/mysql/ --datadir=/data/3308/data/ --user=mysqlcp /data/3307/my.cnf /data/3308/cp /data/3307/mysql /data/3308/sed -i ‘s#3307#3308#g‘ /data/3308/my.cnfsed -i ‘s#3307#3308#g‘ /data/3308/mysqlchmod +x /data/3308/mysqlchown -R mysql.mysql /data/3308//data/3308/mysql start

　　

 

cmake安裝mysql及多執行個體配置方法