前言MySQL的使用MySQL多表同时删除方案MySQL跨表、多表更新SQL语句总结MySQL存储引擎安装常规方式编译安装MySQL采用cmake方式编译安装MySQL使用rpm包安装MySQL使用yum方式安装MySQL采用二进制方式免编译安装MySQL多实例的安装什么是多实例多实例的作用、问题以及应用场景多实例安装01【推荐】多实例官方安装方案02启动、用户和权限管理单实例MySQL的启动和关闭的方法设置及修改MySQL root用户密码找回丢失的MySQL root用户密码创建MySQL用户及用户权限管理基础命令的操作MySQL库和表相关操作MySQL中的索引操作MySQL常用命令MySQL的错误代码MySQL复习秘籍备份与恢复备份恢复mysqlbinlog命令服务日志主从复制主从复制部署配置问题汇总主从复制读写分离灾难恢复配置phpmyadmin连接多实例MySQLMySQL语句大全用户创建、权限、删除数据库与表显示、创建、删除Mysql表复制及备份还原 数据库表中数据操作修改表中的指定一条数据查询表日志批量修改Mysql表引擎为InnoDB的方法数据库抽象层 PDOPDO对象常用方法PDO 事务处理PDO 与 MySQLi 二者效率简单比较大小写敏感性 lower_case_table_namesCentOS7安装MySQL5.7密码查看与修改
©
本文档使用
php.cn手册 发布
文字
mysql的多实例有两种方式可以实现，两种方式各有利弊。
第一种是使用多个配置文件启动不同的进程来实现多实例，这种方式的优势逻辑简单，配置简单，缺点是管理起来不太方便。
环境介绍：
mysql 版本：5.1.59
操作系统：Centos 5.5~5.6
mysql实例数：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
创建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' # 接着执行编译 make && make install
初始化数据库
/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
*为什么要初始化数据库? *
答:初始化的主要目的就是创建基础的数据库文件,例如生成MySQL库,表等.
授权数据库权限给mysql用户
setfacl -m u:mysql:rwx -R /usr/local/mysql setfacl -m d:u:mysql:rwx -R /usr/local/mysql
第一种使用多个配置文件启动多个不同进程的情况：
创建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
创建自启动文件
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
上面的三个启动文件中都包含了数据库密码等敏感信息,所以为了安全考虑,将权限改为只有root可以操作，执行下面的命令
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)
产生错误原因(磁盘空间已满)
# df -Th 文件系统 类型 容量 已用 可用 已用% 挂载点 /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中，如果需要全局设定，请修改`/etc/profile` vi .bashrc #加入以下内容 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
##登录数据库增加进程关闭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
后期增加一个实例的情况
初始化数据库
/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
创建自启动文件
#!/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
授权启动文件可执行
chmod a+x /usr/local/mysql/data/3309/mysqld
相关博文参考：
mysql多实例的配置和管理