前言
MySQL的使用
MySQL多表同時刪除方案
MySQL跨表、多表更新SQL語句總結(jié)
MySQL存儲引擎
安裝
常規(guī)方式編譯安裝MySQL
采用cmake方式編譯安裝MySQL
使用rpm包安裝MySQL
使用yum方式安裝MySQL
采用二進制方式免編譯安裝MySQL
多實例的安裝
什么是多實例
多實例的作用、問題以及應(yīng)用場景
多實例安裝01【推薦】
多實例官方安裝方案02
啟動、用戶和權(quán)限管理
單實例MySQL的啟動和關(guān)閉的方法
設(shè)置及修改MySQL root用戶密碼
找回丟失的MySQL root用戶密碼
創(chuàng)建MySQL用戶及用戶權(quán)限管理
基礎(chǔ)命令的操作
MySQL庫和表相關(guān)操作
MySQL中的索引操作
MySQL常用命令
MySQL的錯誤代碼
MySQL復(fù)習秘籍
備份與恢復(fù)
備份
恢復(fù)
mysqlbinlog命令
服務(wù)日志
主從復(fù)制
主從復(fù)制部署配置問題匯總
主從復(fù)制讀寫分離
災(zāi)難恢復(fù)
配置phpmyadmin連接多實例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
mysql的多實例有兩種方式可以實現(xiàn)，兩種方式各有利弊。
第一種是使用多個配置文件啟動不同的進程來實現(xiàn)多實例，這種方式的優(yōu)勢邏輯簡單，配置簡單，缺點是管理起來不太方便。
環(huán)境介紹：
mysql 版本：5.1.59
操作系統(tǒng)：Centos 5.5~5.6
mysql實例數(shù)：3個
實例占用端口分別為：3306、3307、3308
必要軟件包
yum -y install ncurses-devel gcc gcc-c++ gcc-g77 autoconf automake zlib* fiex* libxml* libmcrypt* libtool-ltdl-devel* libtool make
創(chuàng)建mysql用戶
/usr/sbin/groupadd mysql /usr/sbin/useradd -g mysql mysql -s /sbin/nologin
編譯安裝MySQL
cd /usr/local/src/mysql-5.1.59 ./configure \ '--prefix=/usr/local/mysql' \ '--with-charset=utf8' \ '--with-extra-charsets=complex' \ '--with-pthread' \ '--enable-thread-safe-client' \ '--with-ssl' \ '--with-client-ldflags=-all-static' \ '--with-mysqld-ldflags=-all-static' \ '--with-plugins=partition,innobase,blackhole,myisam,innodb_plugin,heap,archive' \ '--enable-shared' '--enable-assembler' # 接著執(zhí)行編譯 make && make install
初始化數(shù)據(jù)庫
/usr/local/mysql/bin/mysql_install_db --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data/3306/data --user=mysql /usr/local/mysql/bin/mysql_install_db --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data/3307/data --user=mysql /usr/local/mysql/bin/mysql_install_db --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data/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 /usr/local/mysql setfacl -m d:u:mysql:rwx -R /usr/local/mysql
第一種使用多個配置文件啟動多個不同進程的情況：
創(chuàng)建3個實例的配置文件
3306端口的配置文件
vim /usr/local/mysql/data/3306/my.cnf
[client] port = 3306 socket = /usr/local/mysql/data/3306/mysql.sock [mysqld] datadir = /usr/local/mysql/data/3306/data/ skip-name-resolve lower_case_table_names=1 innodb_file_per_table=1 port = 3306 socket = /usr/local/mysql/data/3306/mysql.sock 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 [mysqldump] quick max_allowed_packet = 256M [mysql] no-auto-rehash prompt=\\u@\\d \\R:\\m> [myisamchk] key_buffer_size = 512M sort_buffer_size = 512M read_buffer = 8M write_buffer = 8M [mysqlhotcopy] interactive-timeout [mysqld_safe] log-error=/usr/local/mysql/data/3306/mysql_error3306.err pid-file=/usr/local/mysql/data/3306/mysqld.pid open-files-limit = 8192
3307端口的配置文件
vim /usr/local/mysql/data/3307/my.cnf
[client] port = 3307 socket = /usr/local/mysql/data/3307/mysql.sock [mysqld] datadir=/usr/local/mysql/data/3307/data/ skip-name-resolve lower_case_table_names=1 innodb_file_per_table=1 port = 3307 socket = /usr/local/mysql/data/3307/mysql.sock 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 [mysqldump] quick max_allowed_packet = 256M [mysql] no-auto-rehash prompt=\\u@\\d \\R:\\m> [myisamchk] key_buffer_size = 512M sort_buffer_size = 512M read_buffer = 8M write_buffer = 8M [mysqlhotcopy] interactive-timeout [mysqld_safe] log-error=/usr/local/mysql/data/3307/mysql_error3307.err pid-file=/usr/local/mysql/data/3307/mysqld.pid open-files-limit = 8192
3308端口的配置文件
vim /usr/local/mysql/data/3308/my.cnf
[client] port = 3308 socket = /usr/local/mysql/data/3308/mysql.sock [mysqld] datadir=/usr/local/mysql/data/3308/data skip-name-resolve lower_case_table_names=1 innodb_file_per_table=1 port = 3308 socket = /usr/local/mysql/data/3308/mysql.sock 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@\\d \\R:\\m> [myisamchk] key_buffer_size = 512M sort_buffer_size = 512M read_buffer = 8M write_buffer = 8M [mysqlhotcopy] interactive-timeout [mysqld_safe] log-error=/usr/local/mysql/data/3308/mysql_error3308.err pid-file=/usr/local/mysql/data/3308/mysqld.pid open-files-limit = 8192
創(chuàng)建自啟動文件
vim /usr/local/mysql/data/3306/mysqld , 3306的啟動文件
#!/bin/bash
mysql_port=3306
mysql_username="admin"
mysql_password="password"
function_start_mysql(){
printf "Starting MySQL...\n"
/bin/bash /usr/local/mysql/bin/mysqld_safe --defaults-file=/usr/local/mysql/data/${mysql_port}/my.cnf 2>&1 /dev/null &
}
function_stop_mysql(){
printf "Stoping MySQL...\n"
/usr/local/mysql/bin/mysqladmin -u ${mysql_username} -p${mysql_password} -S /usr/local/mysql/data/${mysql_port}/mysql.sock shutdown
}
function_restart_mysql()
{
printf "Restarting MySQL...\n"
function_stop_mysql
function_start_mysql
}
function_kill_mysql()
{
kill -9 $(ps -ef | grep 'bin/mysqld_safe' | grep ${mysql_port} | awk '{printf $2}')
kill -9 $(ps -ef | grep 'libexec/mysqld' | grep ${mysql_port} | awk '{printf $2}')
}
case $1 in
start)
function_start_mysql;;
stop)
function_stop_mysql;;
kill)
function_kill_mysql;;
restart)
function_stop_mysql
function_start_mysql;;
*)
echo "Usage: /usr/local/mysql/data/${mysql_port}/mysqld {start|stop|restart|kill}";;
esac
vim /usr/local/mysql/data/3307/mysqld , 3307的啟動文件
#!/bin/bash
mysql_port=3307
mysql_username="admin"
mysql_password="password"
function_start_mysql()
{
printf "Starting MySQL...\n"
/bin/bash /usr/local/mysql/bin/mysqld_safe --defaults-file=/usr/local/mysql/data/${mysql_port}/my.cnf 2>&1 /dev/null &
}
function_stop_mysql()
{
printf "Stoping MySQL...\n"
/usr/local/mysql/bin/mysqladmin -u ${mysql_username} -p${mysql_password} -S /usr/local/mysql/data/${mysql_port}/mysql.sock shutdown
}
function_restart_mysql()
{
printf "Restarting MySQL...\n"
function_stop_mysql
function_start_mysql
}
function_kill_mysql()
{
kill -9 $(ps -ef | grep 'bin/mysqld_safe' | grep ${mysql_port} | awk '{printf $2}')
kill -9 $(ps -ef | grep 'libexec/mysqld' | grep ${mysql_port} | awk '{printf $2}')
}
case $1 in
start)
function_start_mysql;;
stop)
function_stop_mysql;;
kill)
function_kill_mysql;;
restart)
function_stop_mysql
function_start_mysql;;
*)
echo "Usage: /usr/local/mysql/data/${mysql_port}/mysqld {start|stop|restart|kill}";;
esac
vim /usr/local/mysql/data/3308/mysqld , 3308的啟動文件
#!/bin/bash
mysql_port=3308
mysql_username="admin"
mysql_password="password"
function_start_mysql()
{
printf "Starting MySQL...\n"
/bin/bash /usr/local/mysql/bin/mysqld_safe --defaults-file=/usr/local/mysql/data/${mysql_port}/my.cnf 2>&1 /dev/null &
}
function_stop_mysql()
{
printf "Stoping MySQL...\n"
/usr/local/mysql/bin/mysqladmin -u ${mysql_username} -p${mysql_password} -S /usr/local/mysql/data/${mysql_port}/mysql.sock shutdown
}
function_restart_mysql()
{
printf "Restarting MySQL...\n"
function_stop_mysql
function_start_mysql
}
function_kill_mysql()
{
kill -9 $(ps -ef | grep 'bin/mysqld_safe' | grep ${mysql_port} | awk '{printf $2}')
kill -9 $(ps -ef | grep 'libexec/mysqld' | grep ${mysql_port} | awk '{printf $2}')
}
case $1 in
start)
function_start_mysql;;
stop)
function_stop_mysql;;
kill)
function_kill_mysql;;
restart)
function_stop_mysql
function_start_mysql;;
*)
echo "Usage: /usr/local/mysql/data/${mysql_port}/mysqld {start|stop|restart|kill}";;
esac
上面的三個啟動文件中都包含了數(shù)據(jù)庫密碼等敏感信息,所以為了安全考慮,將權(quán)限改為只有root可以操作，執(zhí)行下面的命令
find /usr/local/mysql/data -name 'mysqld'|xargs chmod 700 && find /usr/local/mysql/data -name 'mysqld'|xargs setfacl -b && find /usr/local/mysql/data -name 'mysqld'|xargs chown root.root
啟動3306 3307 3308的MySQL
cd /usr/local/mysql/data && ./3306/mysqld start cd /usr/local/mysql/data && ./3307/mysqld start cd /usr/local/mysql/data && ./3308/mysqld start
啟動報錯
151016 06:36:48 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data/3307/data/
/usr/local/mysql/bin/mysqld: File './mysql-bin.~rec~' not found (Errcode: 28)
151016 6:36:48 [ERROR] MYSQL_BIN_LOG::open_purge_index_file failed to open register file.
151016 6:36:48 [ERROR] MYSQL_BIN_LOG::open_index_file failed to sync the index file.
151016 6:36:48 [ERROR] Aborting
151016 6:36:48 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete
151016 06:36:48 mysqld_safe mysqld from pid file /usr/local/mysql/data/3307/mysqld.pid ended
錯誤代碼 (Errcode: 28)
產(chǎn)生錯誤原因(磁盤空間已滿)
# df -Th 文件系統(tǒng) 類型 容量 已用 可用 已用% 掛載點 /dev/mapper/VolGroup00-LogVol00 ext3 8.9G 8.4G 0 100% / /dev/sda1 ext3 99M 12M 82M 13% /boot tmpfs tmpfs 1014M 0 1014M 0% /dev/shm
解決方案
釋放硬盤空間，重新啟動即可。
將mysql的bin加入到path中
ln -s /usr/local/mysql/bin/mysql /usr/local/sbin/mysql cd ~ #或者，把path添加到當前用戶目錄的bashrc中，如果需要全局設(shè)定，請修改`/etc/profile` vi .bashrc #加入以下內(nèi)容 PATH=/usr/local/mysql/bin:$PATH source /etc/profile
修改默認root密碼(指定sock文件)
mysqladmin -uroot password 'aaaaaa' -S /usr/local/mysql/data/3306/mysql.sock mysqladmin -uroot password 'aaaaaa' -S /usr/local/mysql/data/3307/mysql.sock mysqladmin -uroot password 'aaaaaa' -S /usr/local/mysql/data/3308/mysql.sock
##登錄數(shù)據(jù)庫增加進程關(guān)閉admin(password)賬號
/usr/local/mysql/bin/mysql -uroot -ppassword -S /data/dbdata_3306/mysql.sock GRANT SHUTDOWN ON *.* TO 'admin'@'localhost' IDENTIFIED BY 'password'; flush privileges;
本機登錄指定sock登錄
mysql -uroot -paaaaaa -S /usr/local/mysql/data/3306/mysql.sock mysql -uroot -paaaaaa -S /usr/local/mysql/data/3307/mysql.sock mysql -uroot -paaaaaa -S /usr/local/mysql/data/3308/mysql.sock
后期增加一個實例的情況
初始化數(shù)據(jù)庫
/usr/local/mysql/bin/mysql_install_db --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data/3309/data --user=mysql
新建一個默認配置文件
vim /usr/local/mysql/data/3309/my.cnf
[client] port = 3309 socket = /usr/local/mysql/data/3309/mysql.sock [mysqld] datadir = /usr/local/mysql/data/3309/data/ skip-name-resolve lower_case_table_names=1 innodb_file_per_table=1 port = 3309 socket = /usr/local/mysql/data/3309/mysql.sock 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 = 3309 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@\\d \\R:\\m> [myisamchk] key_buffer_size = 512M sort_buffer_size = 512M read_buffer = 8M write_buffer = 8M [mysqlhotcopy] interactive-timeout [mysqld_safe] log-error=/usr/local/mysql/data/3309/mysql_error3309.err pid-file=/usr/local/mysql/data/3309/mysqld.pid open-files-limit = 8192
創(chuàng)建自啟動文件
#!/bin/bash
mysql_port=3309
mysql_username="admin"
mysql_password="password"
function_start_mysql()
{
printf "Starting MySQL...\n"
/bin/bash /usr/local/mysql/bin/mysqld_safe --defaults-file=/usr/local/mysql/data/${mysql_port}/my.cnf 2>&1 > /dev/null &
}
function_stop_mysql()
{
printf "Stoping MySQL...\n"
/usr/local/mysql/bin/mysqladmin -u ${mysql_username} -p${mysql_password} -S /usr/local/mysql/data/${mysql_port}/mysql.sock shutdown
}
function_restart_mysql()
{
printf "Restarting MySQL...\n"
function_stop_mysql
function_start_mysql
}
function_kill_mysql()
{
kill -9 $(ps -ef | grep 'bin/mysqld_safe' | grep ${mysql_port} | awk '{printf $2}')
kill -9 $(ps -ef | grep 'libexec/mysqld' | grep ${mysql_port} | awk '{printf $2}')
}
case $1 in
start)
function_start_mysql;;
stop)
function_stop_mysql;;
kill)
function_kill_mysql;;
restart)
function_stop_mysql
function_start_mysql;;
*)
echo "Usage: /usr/local/mysql/data/${mysql_port}/mysqld {start|stop|restart|kill}";;
esac
授權(quán)啟動文件可執(zhí)行
chmod a+x /usr/local/mysql/data/3309/mysqld
相關(guān)博文參考：
mysql多實例的配置和管理