前言
MySQL的使用
MySQL多表同時(shí)刪除方案
MySQL跨表、多表更新SQL語句總結(jié)
MySQL存儲(chǔ)引擎
安裝
常規(guī)方式編譯安裝MySQL
采用cmake方式編譯安裝MySQL
使用rpm包安裝MySQL
使用yum方式安裝MySQL
采用二進(jìn)制方式免編譯安裝MySQL
多實(shí)例的安裝
什么是多實(shí)例
多實(shí)例的作用、問題以及應(yīng)用場景
多實(shí)例安裝01【推薦】
多實(shí)例官方安裝方案02
啟動(dòng)、用戶和權(quán)限管理
單實(shí)例MySQL的啟動(dòng)和關(guān)閉的方法
設(shè)置及修改MySQL root用戶密碼
找回丟失的MySQL root用戶密碼
創(chuàng)建MySQL用戶及用戶權(quán)限管理
基礎(chǔ)命令的操作
MySQL庫和表相關(guān)操作
MySQL中的索引操作
MySQL常用命令
MySQL的錯(cuò)誤代碼
MySQL復(fù)習(xí)秘籍
備份與恢復(fù)
備份
恢復(fù)
mysqlbinlog命令
服務(wù)日志
主從復(fù)制
主從復(fù)制部署配置問題匯總
主從復(fù)制讀寫分離
災(zāi)難恢復(fù)
配置phpmyadmin連接多實(shí)例MySQL
MySQL語句大全
用戶創(chuàng)建、權(quán)限、刪除
數(shù)據(jù)庫與表顯示、創(chuàng)建、刪除
Mysql表復(fù)制及備份還原
數(shù)據(jù)庫表中數(shù)據(jù)操作
修改表中的指定一條數(shù)據(jù)
查詢表
日志
批量修改Mysql表引擎為InnoDB的方法
數(shù)據(jù)庫抽象層 PDO
PDO對象常用方法
PDO 事務(wù)處理
PDO 與 MySQLi 二者效率簡單比較
大小寫敏感性 lower_case_table_names
CentOS7安裝MySQL5.7密碼查看與修改
?
This document uses PHP Chinese website manual Release
characters
再來看第二種通過官方自帶的mysqld_multi實(shí)現(xiàn)多實(shí)例實(shí)戰(zhàn)：
通過官方自帶的mysqld_multi使用單獨(dú)的配置文件來實(shí)現(xiàn)多實(shí)例，這種方式定制每個(gè)實(shí)例的配置不太方面，優(yōu)點(diǎn)是管理起來很方便，集中管理。下面就分別來實(shí)戰(zhàn)這兩種多實(shí)例的安裝和管理
環(huán)境介紹：
mysql 版本：5.5.29
操作系統(tǒng)：Centos 5.5
mysql實(shí)例數(shù)：3個(gè)
實(shí)例占用端口分別為：3306、3307、3308
這里的mysql安裝以及數(shù)據(jù)庫的初始化和前面的步驟一樣。
必要軟件包
yum -y install ncurses-devel gcc gcc-c++ gcc-g77 autoconf automake zlib* fiex* libxml* libmcrypt* libtool-ltdl-devel* libtool make cmake
創(chuàng)建mysql用戶
/usr/sbin/groupadd mysql /usr/sbin/useradd -g mysql mysql -s /sbin/nologin
編譯安裝MySQL
tar -zxvf mysql-5.5.29.tar.gz && cd mysql-5.5.29 #默認(rèn)情況下是安裝在/usr/local/mysql cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \ -DMYSQL_DATADIR=/usr/local/mysql/data \ -DSYSCONFDIR=/etc \ -DWITH_MYISAM_STORAGE_ENGINE=1 \ -DWITH_INNOBASE_STORAGE_ENGINE=1 \ -DWITH_MEMORY_STORAGE_ENGINE=1 \ -DWITH_READLINE=1 \ -DMYSQL_UNIX_ADDR=/var/lib/mysql/mysql.sock \ -DMYSQL_TCP_PORT=3306 \ -DENABLED_LOCAL_INFILE=1 \ -DWITH_PARTITION_STORAGE_ENGINE=1 \ -DEXTRA_CHARSETS=all \ -DDEFAULT_CHARSET=utf8 \ -DDEFAULT_COLLATION=utf8_general_ci make && make install
初始化數(shù)據(jù)庫
/usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/data/dbdata_3306/data --user=mysql /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/data/dbdata_3307/data --user=mysql /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/data/dbdata_3308/data --user=mysql
為什么要初始化數(shù)據(jù)庫?
答:初始化的主要目的就是創(chuàng)建基礎(chǔ)的數(shù)據(jù)庫文件,例如生成MySQL庫,表等.
授權(quán)數(shù)據(jù)庫權(quán)限給mysql用戶
setfacl -m u:mysql:rwx -R /data setfacl -m d:u:mysql:rwx -R /data
mysqld_multi的配置
vim /etc/my.cnf
[mysqld_multi] mysqld = /usr/local/mysql/bin/mysqld_safe mysqladmin = /usr/local/mysql/bin/mysqladmin user = admin password = password [mysqld1] socket = /data/dbdata_3306/mysql.sock port = 3306 pid-file = /data/dbdata_3306/3306.pid datadir = /data/dbdata_3306/data user = mysql skip-name-resolve lower_case_table_names=1 innodb_file_per_table=1 back_log = 50 max_connections = 300 max_connect_errors = 1000 table_open_cache = 2048 max_allowed_packet = 16M binlog_cache_size = 2M max_heap_table_size = 64M sort_buffer_size = 2M join_buffer_size = 2M thread_cache_size = 64 thread_concurrency = 8 query_cache_size = 64M query_cache_limit = 2M ft_min_word_len = 4 default-storage-engine = innodb thread_stack = 192K transaction_isolation = REPEATABLE-READ tmp_table_size = 64M log-bin=mysql-bin binlog_format=mixed slow_query_log long_query_time = 1 server-id = 3306 key_buffer_size = 8M read_buffer_size = 2M read_rnd_buffer_size = 2M bulk_insert_buffer_size = 64M myisam_sort_buffer_size = 128M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 myisam_recover innodb_additional_mem_pool_size = 16M innodb_buffer_pool_size = 200M innodb_data_file_path = ibdata1:10M:autoextend innodb_file_io_threads = 8 innodb_thread_concurrency = 16 innodb_flush_log_at_trx_commit = 1 innodb_log_buffer_size = 16M innodb_log_file_size = 512M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 60 innodb_lock_wait_timeout = 120 [mysqld2] socket = /data/dbdata_3307/mysql.sock port = 3307 pid-file = /data/dbdata_3307/3307.pid datadir = /data/dbdata_3307/data user = mysql skip-name-resolve lower_case_table_names=1 innodb_file_per_table=1 back_log = 50 max_connections = 300 max_connect_errors = 1000 table_open_cache = 2048 max_allowed_packet = 16M binlog_cache_size = 2M max_heap_table_size = 64M sort_buffer_size = 2M join_buffer_size = 2M thread_cache_size = 64 thread_concurrency = 8 query_cache_size = 64M query_cache_limit = 2M ft_min_word_len = 4 default-storage-engine = innodb thread_stack = 192K transaction_isolation = REPEATABLE-READ tmp_table_size = 64M log-bin=mysql-bin binlog_format=mixed slow_query_log long_query_time = 1 server-id = 3307 key_buffer_size = 8M read_buffer_size = 2M read_rnd_buffer_size = 2M bulk_insert_buffer_size = 64M myisam_sort_buffer_size = 128M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 myisam_recover innodb_additional_mem_pool_size = 16M innodb_buffer_pool_size = 200M innodb_data_file_path = ibdata1:10M:autoextend innodb_file_io_threads = 8 innodb_thread_concurrency = 16 innodb_flush_log_at_trx_commit = 1 innodb_log_buffer_size = 16M innodb_log_file_size = 512M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 60 innodb_lock_wait_timeout = 120 [mysqld3] socket = /data/dbdata_3308/mysql.sock port = 3308 pid-file = /data/dbdata_3308/3308.pid datadir = /data/dbdata_3308/data user = mysql skip-name-resolve lower_case_table_names=1 innodb_file_per_table=1 back_log = 50 max_connections = 300 max_connect_errors = 1000 table_open_cache = 2048 max_allowed_packet = 16M binlog_cache_size = 2M max_heap_table_size = 64M sort_buffer_size = 2M join_buffer_size = 2M thread_cache_size = 64 thread_concurrency = 8 query_cache_size = 64M query_cache_limit = 2M ft_min_word_len = 4 default-storage-engine = innodb thread_stack = 192K transaction_isolation = REPEATABLE-READ tmp_table_size = 64M log-bin=mysql-bin binlog_format=mixed slow_query_log long_query_time = 1 server-id = 3308 key_buffer_size = 8M read_buffer_size = 2M read_rnd_buffer_size = 2M bulk_insert_buffer_size = 64M myisam_sort_buffer_size = 128M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 myisam_recover innodb_additional_mem_pool_size = 16M innodb_buffer_pool_size = 200M innodb_data_file_path = ibdata1:10M:autoextend innodb_file_io_threads = 8 innodb_thread_concurrency = 16 innodb_flush_log_at_trx_commit = 1 innodb_log_buffer_size = 16M innodb_log_file_size = 512M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 60 innodb_lock_wait_timeout = 120 [mysqldump] quick max_allowed_packet = 256M [mysql] no-auto-rehash prompt=\\u@rose \\r:\\m:\\s> [myisamchk] key_buffer_size = 512M sort_buffer_size = 512M read_buffer = 8M write_buffer = 8M [mysqlhotcopy] interactive-timeout [mysqld_safe] open-files-limit = 8192
更改環(huán)境變量
export PATH=/usr/local/mysql/bin:$PATH
# 或者
vim /etc/profile
PATH=/usr/local/mysql/bin:${PATH}
source /etc/profile
mysqld_multi啟動(dòng)
/usr/local/mysql/bin/mysqld_multi start 1 /usr/local/mysql/bin/mysqld_multi start 2 /usr/local/mysql/bin/mysqld_multi start 3 # 或采用一條命令的形式 /usr/local/mysql/bin/mysqld_multi start 1-3
更改原來密碼（出于安全考慮，還需要?jiǎng)h除系統(tǒng)中沒有密碼的帳號）
/usr/local/mysql/bin/mysqladmin -uroot password 'password' -S /data/dbdata_3306/mysql.sock /usr/local/mysql/bin/mysqladmin -uroot password 'password' -S /data/dbdata_3307/mysql.sock /usr/local/mysql/bin/mysqladmin -uroot password 'password' -S /data/dbdata_3308/mysql.sock
登錄MySQL并創(chuàng)建admin賬戶密碼(停止mysql的時(shí)候需要使用)
/usr/local/mysql/bin/mysql -uroot -ppassword -S /data/dbdata_3306/mysql.sock GRANT SHUTDOWN ON *.* TO 'admin'@'localhost' IDENTIFIED BY 'password'; flush privileges; /usr/local/mysql/bin/mysql -uroot -ppassword -S /data/dbdata_3307/mysql.sock GRANT SHUTDOWN ON *.* TO 'admin'@'localhost' IDENTIFIED BY 'password'; flush privileges; /usr/local/mysql/bin/mysql -uroot -ppassword -S /data/dbdata_3308/mysql.sock GRANT SHUTDOWN ON *.* TO 'admin'@'localhost' IDENTIFIED BY 'password'; flush privileges;
mysqld_multi關(guān)閉進(jìn)程(測試無效)
/usr/local/mysql/bin/mysqld_multi stop 1 /usr/local/mysql/bin/mysqld_multi stop 1-3
管理的話，在本地都是采用 -S /data/dbdata_3308/mysql.sock，如果在遠(yuǎn)程可以通過不同的端口連接上去做管理操作。其他的和單實(shí)例的管理沒什么區(qū)別！
相關(guān)博文參考：
mysql多實(shí)例的配置和管理