標籤：set 管理 size [1] software user describes 實現 0.11
轉自：http://www.cnblogs.com/xuchenliang/p/6843990.html
1.MySQL多執行個體介紹
1.1.什麼是MySQL多執行個體MySQL多執行個體就是在一台機器上開啟多個不同的服務連接埠（如：3306,3307），運行多個MySQL服務進程，通過不同的socket監聽不同的服務連接埠來提供各自的服務:；
1.2.MySQL多執行個體的特點有以下幾點1：有效利用伺服器資源，當單個伺服器資源有剩餘時，可以充分利用剩餘的資源提供更多的服務。2：節約伺服器資源3：資源互相搶佔問題，當某個服務執行個體服務並發很高時或者開啟慢查詢時，會消耗更多的記憶體、CPU、磁碟IO資源，導致伺服器上的其他執行個體提供服務的品質下降；
1.3.部署mysql多執行個體的兩種方式第一種是使用多個設定檔啟動不同的進程來實現多執行個體，這種方式的優勢邏輯簡單，配置簡單，缺點是管理起來不太方便；第二種是通過官方內建的mysqld_multi使用單獨的設定檔來實現多執行個體，這種方式定製每個執行個體的配置不太方面，優點是管理起來很方便，集中管理；
1.4.同一開發環境下安裝兩個資料庫，必須處理以下問題
- 設定檔安裝路徑不能相同
- 資料庫目錄不能相同
- 啟動指令碼不能同名
- 連接埠不能相同
- socket檔案的產生路徑不能相同
2.Mysql多執行個體安裝部署
2.1.部署環境Red Hat Enterprise Linux Server release 6.4
2.2.安裝mysql軟體版本
2.2.1.免編譯二進位包mysql-5.6.21-linux-glibc2.5-x86_64.tar.gz
2.3.解壓和遷移tar -xvf mysql-5.6.21-linux-glibc2.5-x86_64.tar.gzmv mysql-5.6.21-linux-glibc2.5-x86_64 /usr/local/mysql
2.4.關閉iptables臨時關閉：service iptables stop 永久關閉：chkconfig iptables off
2.5.關閉selinuxvi /etc/sysconfig/selinux 將SELINUX修改為DISABLED，即SELINUX=DISABLED
2.6.建立mysql使用者groupadd -g 27 mysqluseradd -u 27 -g mysql mysqlid mysqluid=501(mysql) gid=501(mysql) groups=501(mysql)
2.7.建立相關目錄mkdir -p /data/mysql/ {mysql_3306,mysql_3307}mkdir /data/mysql/mysql_3306/ {data,log,tmp}mkdir /data/mysql/mysql_3307/ {data,log,tmp}
2.8.更改目錄許可權chown -R mysql:mysql /data/mysql/ chown -R mysql:mysql /usr/local/mysql/
2.9. 添加環境變數echo ‘export PATH=$PATH:/usr/local/mysql/bin‘ >> /etc/profile source /etc/profile
2.10.複製my.cnf檔案到etc目錄cp /usr/local/mysql/support-files/my-default.cnf /etc/my.cnf
2.11.修改my.cnf（在一個檔案中修改即可）[client] port=3306 socket=/tmp/mysql.sock [mysqld_multi] mysqld = /usr/local/mysql /bin/mysqld_safe mysqladmin = /usr/local/mysql /bin/mysqladmin log = /data/mysql/mysqld_multi.log [mysqld] user=mysql basedir = /usr/local/mysql sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES [mysqld3306] mysqld=mysqld mysqladmin=mysqladmin datadir=/data/mysql/mysql_3306/data port=3306 server_id=3306 socket=/tmp/mysql_3306.sock log-output=file slow_query_log = 1 long_query_time = 1 slow_query_log_file = /data/mysql/mysql_3306/log/slow.log log-error = /data/mysql/mysql_3306/log/error.log binlog_format = mixed log-bin = /data/mysql/mysql_3306/log/mysql3306_bin [mysqld3307] mysqld=mysqld mysqladmin=mysqladmin datadir=/data/mysql/mysql_3307/data port=3307 server_id=3307 socket=/tmp/mysql_3307.sock log-output=file slow_query_log = 1 long_query_time = 1 slow_query_log_file = /data/mysql/mysql_3307/log/slow.log log-error = /data/mysql/mysql_3307/log/error.log binlog_format = mixed log-bin = /data/mysql/mysql_3307/log/mysql3307_bin
2.12. 初始化資料庫
2.12.1. 初始化3306資料庫 /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/data/mysql/mysql_3306/data --defaults-file=/etc/my.cnf
2.12.2. 初始化3307資料庫 /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/data/mysql/mysql_3307/data --defaults-file=/etc/my.cnf
2.12.3. 檢查資料庫是否初始化成功出現兩個”OK”
2.12.4. 查看資料庫是否初始化成功（2）查看3306資料庫[[email protected] ~]# cd /data/mysql/mysql_3306/data[[email protected] data]# lsauto.cnf ibdata1 ib_logfile0 ib_logfile1 mysql mysql.pid performance_schema test 查看3307資料庫[[email protected] ~]# cd /data/mysql/mysql_3307/data[[email protected] data]# lsauto.cnf ibdata1 ib_logfile0 ib_logfile1 mysql mysql.pid performance_schema test
2.13.設定開機檔案cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
2.14.mysqld_multi進行多執行個體管理啟動全部執行個體：/usr/local/mysql/bin/mysqld_multi start查看全部執行個體狀態：/usr/local/mysql/bin/mysqld_multi report 啟動單個執行個體：/usr/local/mysql/bin/mysqld_multi start 3306 停止單個執行個體：/usr/local/mysql/bin/mysqld_multi stop 3306 查看單個執行個體狀態：/usr/local/mysql/bin/mysqld_multi report 3306
2.14.1.啟動全部執行個體[[email protected] ~]# /usr/local/mysql/bin/mysqld_multi start[[email protected] ~]# /usr/local/mysql/bin/mysqld_multi reportReporting MySQL serversMySQL server from group: mysqld3306 is runningMySQL server from group: mysqld3307 is running
2.15.查看啟動進程
2.16.修改密碼mysql的root使用者初始密碼是空，所以需要登入mysql進行修改密碼，下面以3306為例： mysql -S /tmp/mysql_3306.sock set password for [email protected]‘localhost‘=password(‘123456‘); flush privileges; 下次登入：[[email protected] ~]# mysql -S /tmp/mysql_3306.sock -pEnter password:
2.17.建立使用者及授權一般建立資料庫都需要新增一個使用者，用於程式串連，這類使用者只需要insert、update、delete、select許可權。新增一個使用者，並授權如下： grant select,delete,update,insert on *.* to [email protected]‘192.168.0.%‘ identified by ‘123456‘; flush privileges
2.18.外部軟體登入資料庫
2.19.測試成功
3.源碼安裝常見報錯資訊1：安裝mysql報錯checking for tgetent in -lncurses... nochecking for tgetent in -lcurses... nochecking for tgetent in -ltermcap... nochecking for tgetent in -ltinfo... nochecking for termcap functions library... configure: error: No curses/termcap library found原因：缺少ncurses安裝包解決方案：yum list|grep ncursesyum -y install ncurses-develyum install ncurses-devel2：.../depcomp: line 571: exec: g++: not foundmake[1]: *** [my_new.o] 錯誤 127make[1]: Leaving directory `/home/justme/software/mysql-5.1.30/mysys‘make: *** [all-recursive] 錯誤 1解決方案：yum install gcc-c++3：.../include/my_global.h:909: error: redeclaration of C++ built-in type `bool‘make[2]: *** [my_new.o] Error 1make[2]: Leaving directory `/home/tools/mysql-5.0.22/mysys‘make[1]: *** [all-recursive] Error 1make[1]: Leaving directory `/home/tools/mysql-5.0.22‘make: *** [all] Error 2是因為gcc-c++是在configure之後安裝的，此時只需重新configure後再編譯make即可。4：初始化資料庫報錯報錯現象：[email protected] mysql-6.0.11-alpha]# scripts/mysql_install_db --basedir=/usr/local/mysql/ --user=mysqlInstalling MySQL system tables...ERROR: 1136 Column count doesn‘t match value count at row 1150414 7:15:56 [ERROR] Aborting150414 7:15:56 [Warning] Forcing shutdown of 1 plugins150414 7:15:56 [Note] /usr/local/mysql//libexec/mysqld: Shutdown completeInstallation of system tables failed! Examine the logs in/var/lib/mysql for more information.You can try to start the mysqld daemon with:shell> /usr/local/mysql//libexec/mysqld --skip-grant &and use the command line tool /usr/local/mysql//bin/mysqlto connect to the mysql database and look at the grant tables:shell> /usr/local/mysql//bin/mysql -u root mysqlmysql> show tablesTry ‘mysqld --help‘ if you have problems with paths. Using --loggives you a log in /var/lib/mysql that may be helpful.The latest information about MySQL is available on the web athttp://www.mysql.com/. Please consult the MySQL manual section‘Problems running mysql_install_db‘, and the manual section thatdescribes problems on your OS. Another information source are theMySQL email archives available at http://lists.mysql.com/.Please check all of the above before mailing us! And remember, ifyou do mail us, you MUST use the /usr/local/mysql//scripts/mysqlbug script!原因：原有安裝的mysql資訊沒有刪除乾淨解決方案：刪除/var/lib/mysql目錄
linux下mysql多執行個體安裝（轉）
