溫馨提示×
您好，登錄后才能下訂單哦！
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
MySQL數(shù)據(jù)庫多實(shí)例安裝
簡(jiǎn)單的說，MySQL多實(shí)例就是在一臺(tái)服務(wù)器上同時(shí)開啟多個(gè)不同的服務(wù)端口（如：3306、3307），同時(shí)運(yùn)行多個(gè)MySQL服務(wù)進(jìn)程，這些服務(wù)進(jìn)程通過不同的socket監(jiān)聽不同的服務(wù)端口來提供服務(wù)。
這些MySQL多實(shí)例共用一套MySQL安裝程序，使不同的my.cnf（也可以相同）配置文件、啟動(dòng)程序（也可以相同）和數(shù)據(jù)文件。在提供服務(wù)時(shí)，多實(shí)例MySQL在邏輯上看來是各自獨(dú)立的，它們根據(jù)配置文件的對(duì)應(yīng)設(shè)定值，獲得服務(wù)器相應(yīng)數(shù)量的硬件資源。
打個(gè)比方，MySQL多實(shí)例就相當(dāng)于房子的多個(gè)臥室，每個(gè)實(shí)例可以看作一間臥室，整個(gè)服務(wù)器就是一套房子，服務(wù)器的硬件資源（cpu,mem,disk）、軟件資源（Centos操作系統(tǒng)）可以看做房子的衛(wèi)生間、廚房、客廳，是房子的公用資源。
q 多實(shí)例的作用
1）有效利用服務(wù)器資源
當(dāng)單個(gè)服務(wù)器資源有剩余時(shí)，可以充分利用剩余的資源提供更多的服務(wù)，且可以實(shí)現(xiàn)資源的邏輯隔離。
2）節(jié)約服務(wù)器資源
當(dāng)公司資金緊張，但是數(shù)據(jù)庫又需要各自盡量獨(dú)立地提供服務(wù)，而且，需要主從復(fù)制等技術(shù)時(shí)，多實(shí)例就再好不過了。
q 多實(shí)例的弊端
MySQL多實(shí)例有它的好處，但也有弊端，比如，會(huì)存在資源互相搶占的問題。
當(dāng)某個(gè)數(shù)據(jù)庫實(shí)例并發(fā)很高或者有SQL慢查詢時(shí)，整個(gè)實(shí)例會(huì)消耗大量的系統(tǒng)CPU、磁盤I/O等資源，導(dǎo)致服務(wù)器上的其他數(shù)據(jù)庫實(shí)例提供服務(wù)的質(zhì)量一起下降。不同實(shí)例獲取的資源是相對(duì)獨(dú)立的，無法像虛擬化一樣完全隔離。
是MySQL官網(wǎng)推薦的配置方法，即在同一個(gè)配置文件里面有多個(gè)實(shí)例的配置。對(duì)于該方案，缺點(diǎn)是耦合度太高，一個(gè)配置文件不好管理。工作開發(fā)和運(yùn)維的統(tǒng)一原則：降低耦合度。
不同的實(shí)例擁有不同的配置文件。
[root@mysql02 scripts]# tree /data/ /data/ ├── 3306 │ ├── data #<==3306實(shí)例的配置及數(shù)據(jù)文件 │ ├── my.cnf │ └── mysql └── 3307 ├── data #<==3307實(shí)例的數(shù)據(jù)及配置文件 ├── my.cnf └── mysql 4 directories, 4 files
[root@server ~]# cat /etc/redhat-release CentOS release 6.5 (Final) [root@server ~]# uname -r 2.6.32-431.el6.x86_64 [root@server ~]# uname -m x86_64
mysql-5.5.32.tar.gz
下載地址： https://downloads.mysql.com/archives/community/?tpl=files&os=src&version=5.5.32
q 安裝MySQL需要的依賴包
安裝MySQL之前，最好先安裝MySQL需要的依賴包，不然后面會(huì)出現(xiàn)很多報(bào)錯(cuò)信息，安裝命令如下：
yum -y install ncurses-devel libaio-devel rpm -qa ncurses-devel libaio-devel
提示：安裝出現(xiàn)下面兩條提示說明成功！
ncurses-devel-5.7-4.20090207.el6.x86_64 libaio-devel-0.3.107-10.el6.x86_64
q 安裝編譯MySQL需要的軟件
yum -y install cmake
q 開始安裝MySQL
以下步驟包括了創(chuàng)建mysql用戶,下載mysql源碼包，編譯參數(shù)，編譯安裝，創(chuàng)建軟連接。安裝之前先自行下載mysql-5.5.32.tar.gz版本（5.5.55.tar.gz有BUG，不要使用）。
useradd mysql -s /sbin/nologin -M id mysql cd /usr/local/src/ tar xf mysql-5.5.32.tar.gz cd mysql-5.5.32 cmake . -DCMAKE_INSTALL_PREFIX=/opt/mysql-5.5.32 \ -DMYSQL_DATADIR=/opt/mysql-5.5.32/data \ -DMYSQL_UNIX_ADDR=/opt/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=0 make make install cd .. ln -s /opt/mysql-5.5.32 /opt/mysql ls /opt/mysql/
q 創(chuàng)建多實(shí)例數(shù)據(jù)目錄（以端口區(qū)分）
mkdir -p /data/{3306,3307}/data
chown -R mysql.mysql /data/*[root@localhost 3306]# tree -L 1 /data/3306 /data/3306 ├── data #<==這是在初始化數(shù)據(jù)庫時(shí)自動(dòng)生成的，不用管 ├── my.cnf #<==這個(gè)是單實(shí)例的配置文件 └── mysql #<==這個(gè)事自己編寫的啟動(dòng)腳本 1 directory, 2 files
q 單實(shí)例配置文件my.cnf內(nèi)容（3306）：
[client] port = 3306 socket = /data/3306/mysql.sock default-character-set=gbk [mysql] no-auto-rehash [mysqld] user = mysql port = 3306 socket = /data/3306/mysql.sock basedir = /opt/mysql datadir = /data/3306/data open_files_limit = 1024 back_log = 600 max_connections = 800 max_connect_errors = 3000 table_open_cache = 614 external-locking = FALSE max_allowed_packet = 8M sort_buffer_size = 1M join_buffer_size = 1M thread_cache_size = 100 thread_concurrency = 2 query_cache_size = 2M query_cache_limit = 1M query_cache_min_res_unit = 2k #default_table_type = InnoDB thread_stack = 192k #transaction_isolation = READ-COMMITTED tmp_table_size = 2M max_heap_table_size = 2M long_query_time = 1 #log_long_format #log-error = /data/3306/error.log #log-slow-queries = /data/3306/slow.log pid-file = /data/3306/mysql.pid log-bin = /data/3306/mysql-bin relay-log = /data/3306/relay-log.info binlog_cache_size = 1M max_binlog_cache_size = 1M max_binlog_size = 2M expire_logs_days = 7 key_buffer_size = 16M read_buffer_size = 1M read_rnd_buffer_size = 1M bulk_insert_buffer_size = 1M #myisam_sort_buffer_size = 128M #myisam_max_sort_file_size = 10G #myisam_repair_threads = 1 #myisam_recover character_set_server=gbk lower_case_table_name = 1 skip-name-resolve slave-skip-errors = 1032,1062 replicate-ignore-db = mysql server-id = 1 innodb_additional_mem_pool_size = 4M innodb_buffer_pool_size = 32M innodb_data_file_path = ibdata1:128M:autoextend innodb_write_io_threads = 4 innodb_read_io_threads = 8 innodb_thread_concurrency = 16 innodb_flush_log_at_trx_commit = 2 innodb_log_buffer_size = 2M innodb_log_file_size = 4M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 90 innodb_lock_wait_timeout = 120 innodb_file_per_table = 0 [mysqldump] quick max_allowed_packet = 2M [mysqld_safe] log-error = /data/3306/mysql_3306.err pid-file = /data/3306/mysqld.pid [myisamchk] key_buffer_size = 512M sort_buffer_size = 512M read_buffer = 8M write_buffer = 8M [mysqlhotcopy] interactive-timeout [mysqld_safe] open-files-limit = 8192
q 單實(shí)例配置文件my.cnf內(nèi)容（3307）：
[client] port = 3307 socket = /data/3307/mysql.sock default-character-set=gbk [mysql] no-auto-rehash [mysqld] user = mysql port = 3307 socket = /data/3307/mysql.sock basedir = /opt/mysql datadir = /data/3307/data open_files_limit = 1024 back_log = 600 max_connections = 800 max_connect_errors = 3000 table_open_cache = 614 external-locking = FALSE max_allowed_packet = 8M sort_buffer_size = 1M join_buffer_size = 1M thread_cache_size = 100 thread_concurrency = 2 query_cache_size = 2M query_cache_limit = 1M query_cache_min_res_unit = 2k #default_table_type = InnoDB thread_stack = 192k #transaction_isolation = READ-COMMITTED tmp_table_size = 2M max_heap_table_size = 2M #long_query_time = 1 #log_long_format #log-error = /data/3307/error.log #log-slow-queries = /data/3307/slow.log pid-file = /data/3307/mysql.pid #log-bin = /data/3307/mysql-bin relay-log = /data/3307/relay-log.info binlog_cache_size = 1M max_binlog_cache_size = 1M max_binlog_size = 2M expire_logs_days = 7 key_buffer_size = 16M read_buffer_size = 1M read_rnd_buffer_size = 1M bulk_insert_buffer_size = 1M #myisam_sort_buffer_size = 128M #myisam_max_sort_file_size = 10G #myisam_repair_threads = 1 #myisam_recover character_set_server=gbk lower_case_table_name = 1 skip-name-resolve slave-skip-errors = 1032,1062 replicate-ignore-db = mysql server-id = 3 innodb_additional_mem_pool_size = 4M innodb_buffer_pool_size = 32M innodb_data_file_path = ibdata1:128M:autoextend innodb_write_io_threads = 4 innodb_read_io_threads = 8 innodb_thread_concurrency = 16 innodb_flush_log_at_trx_commit = 2 innodb_log_buffer_size = 2M innodb_log_file_size = 4M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 90 innodb_lock_wait_timeout = 120 innodb_file_per_table = 0 [mysqldump] quick max_allowed_packet = 2M [mysqld_safe] log-error = /data/3307/mysql_3307.err pid-file = /data/3307/mysqld.pid [myisamchk] key_buffer_size = 512M sort_buffer_size = 512M read_buffer = 8M write_buffer = 8M [mysqlhotcopy] interactive-timeout [mysqld_safe] open-files-limit = 8192
注：以上只是一個(gè)單實(shí)例的配置文件，如果實(shí)例不相同，則配置略有變化
q 啟動(dòng)文件mysql
#!/bin/bash
#init
port=3307
mysql_user="root"
mysql_pwd="123456"
CmdPath="/opt/mysql/bin"
mysql_sock="/data/${port}/mysql.sock"
#start mysql function
function start_mysql() {
if [ ! -e "${mysql_sock}" ];then
printf "Starting MySQL......\n"
/bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 >/dev/null &
else
printf "MySQL is running ......\n"
exit
fi
}
#stop mysql function
function stop_mysql() {
if [ ! -e "${mysql_sock}" ];then
printf "MySQL is stopped......\n"
exit
else
printf "Stoping MySQL......\n"
${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdown
fi
}
#restart mysql function
function restart_mysql() {
printf "Restarting MySQL......\n"
stop_mysql
sleep 2
start_mysql
}
case $1 in
start)
start_mysql
;;
stop)
stop_mysql
;;
restart)
restart_mysql
;;
*)
printf "Usage: /data/${port}/mysql {start|stop|restart}\n"
esac有多少個(gè)實(shí)例就執(zhí)行多少次，只需要換3306
cd /opt/mysql/scripts ./mysql_install_db --basedir=/opt/mysql --datadir=/data/3306/data/ --user=mysql --collation-server=utf8_general_ci ./mysql_install_db --basedir=/opt/mysql --datadir=/data/3307/data/ --user=mysql --collation-server=utf8_general_ci Installing MySQL system tables... OK Filling help tables... OK To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: /opt/mysql/bin/mysqladmin -u root password 'new-password' /opt/mysql/bin/mysqladmin -u root -h localhost.localdomain password 'new-password' Alternatively you can run: /opt/mysql/bin/mysql_secure_installation which will also give you the option of removing the test databases and anonymous user created by default. This is strongly recommended for production servers. See the manual for more instructions. You can start the MySQL daemon with: cd /opt/mysql ; /opt/mysql/bin/mysqld_safe & You can test the MySQL daemon with mysql-test-run.pl cd /opt/mysql/mysql-test ; perl mysql-test-run.pl Please report any problems with the /opt/mysql/scripts/mysqlbug script!
注：1、如果~/scripts/目錄下沒有mysql_install_db則說明編譯沒有成功。
2、有兩個(gè)OK出現(xiàn)表示初始化成功，如果有WARINING或者ERROR，需要先解決。
啟動(dòng)命令：直接在mysql 后跟start參數(shù)
[root@localhost 3306]# /data/3306/mysql start Starting MySQL...... [root@localhost 3306]# /data/3307/mysql start Starting MySQL...... [root@localhost 3306]# netstat -anp |grep 330 tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 17531/mysqld tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 16784/mysqld unix 2 [ ACC ] STREAM LISTENING 52308 16784/mysqld /data/3306/mysql.sock unix 2 [ ACC ] STREAM LISTENING 52809 17531/mysqld /data/3307/mysql.sock
可能出現(xiàn)錯(cuò)誤1：
[root@mysql02 3306]# 180124 01:24:15 mysqld_safe error: log-error set to '/data/3306/mysql_3306.err', however file don't exists. Create writable for user 'mysql'.
解決方式：放棄吧少年，5.5.55這個(gè)版本可能不適合你編譯呀。
可能出現(xiàn)錯(cuò)誤2：
180126 22:27:40 [ERROR] COLLATION 'latin1_swedish_ci' is not valid for CHARACTER SET 'utf8' 180126 22:27:40 [ERROR] Aborting
這個(gè)在配置文件里面加兩行就搞定了：
[client] default-character-set=gbk [mysqld] character_set_server=gbk
q 多實(shí)例本地登錄
多實(shí)例本地登錄一般是通過socket文件來指定具體登錄到哪個(gè)實(shí)例的，此文件的具體位置是在mysql編譯過程中或者my.cnf指定的。在本地登錄數(shù)據(jù)庫時(shí)，登錄程序會(huì)通過socket文件來判斷登錄的是哪個(gè)數(shù)據(jù)庫實(shí)例。
cp /opt/mysql/bin/* /usr/local/sbin/ #<==首先把命令拷貝到PATH下 [root@localhost 3307]# mysql -uroot -p -S /data/3306/mysql.sock Enter password: #<==這里默認(rèn)沒有密碼，直接回車進(jìn)入數(shù)據(jù)庫。 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.5.32-log Source distribution Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
q 遠(yuǎn)程登錄mysql實(shí)例
遠(yuǎn)程登錄MySQL多實(shí)例中的一個(gè)實(shí)例時(shí)，通過TCP端口（port）來指定所要登錄的MySQL實(shí)例，此端口的配置是在mysql配置文件my.cnf中指定的。
例如：mysql -uroot -p123456 -h20.0.0.15 -P 3307 ，當(dāng)然是需要提前賦予登錄的權(quán)限。
修改密碼時(shí)也需要指定sock文件，命令如下：
mysqladmin password 123456 -S /data/3306/mysql.sock mysqladmin password 123456 -S /data/3307/mysql.sock [root@localhost 3306]# mysql -uroot -p123456 -S /data/3306/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.5.32-log Source distribution Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
q 刪除多余的數(shù)據(jù)庫
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.02 sec) mysql> drop database test; Query OK, 0 rows affected (0.03 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 3 rows in set (0.00 sec)
q 刪除多余的用戶
mysql> select user,host from mysql.user; +------+-----------------------+ | user | host | +------+-----------------------+ | root | 127.0.0.1 | | root | ::1 | | | localhost | | root | localhost | | | localhost.localdomain | | root | localhost.localdomain | +------+-----------------------+ 6 rows in set (0.00 sec) mysql> drop user ""@localhost; Query OK, 0 rows affected (0.02 sec) mysql> drop user "root"@"::1"; Query OK, 0 rows affected (0.00 sec) mysql> drop user "root"@"localhost.localdomain"; Query OK, 0 rows affected (0.02 sec) mysql> drop user ""@"localhost.localdomain"; Query OK, 0 rows affected (0.00 sec) mysql> select user,host from mysql.user; +------+-----------+ | user | host | +------+-----------+ | root | 127.0.0.1 | | root | localhost | +------+-----------+ 2 rows in set (0.00 sec)
注：這里只留余root.127.0.0.1和root.localhost一共2個(gè)就好了。
q 安全要領(lǐng)
1）啟動(dòng)程序設(shè)置700，屬主和用戶組為mysql；
2）為MySQL超級(jí)用戶root設(shè)置密碼；
3）可以刪除root用戶，創(chuàng)建其他管理用戶，例如：admin；
4）登錄時(shí)盡量不要在命令行暴露密碼，腳本備份中如果有密碼，給設(shè)置700屬主和用戶組為mysql或root；
5）刪除默認(rèn)存在的test庫；
6）刪除無用的用戶，只保留：root.127.0.0.1和root.localhost；
7）授權(quán)用戶對(duì)應(yīng)的主機(jī)盡量不要用%，權(quán)限不要給all，最小化授權(quán)，從庫只給select；
8）清理mysql操作日志文件~/.mysql_history；
9）服務(wù)器禁止設(shè)置外網(wǎng)IP。
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號(hào)
手機(jī)網(wǎng)站二維碼