MySql参考手册
目录 搜索
前言 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连接多实例MySQL MySQL语句大全 用户创建、权限、删除 数据库与表显示、创建、删除 Mysql表复制及备份还原 数据库表中数据操作 修改表中的指定一条数据 查询表 日志 批量修改Mysql表引擎为InnoDB的方法 数据库抽象层 PDO PDO对象常用方法 PDO 事务处理 PDO 与 MySQLi 二者效率简单比较 大小写敏感性 lower_case_table_names CentOS7安装MySQL5.7密码查看与修改

©
本文档使用 php中文网手册 发布

文字

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多实例的配置和管理


上一篇： 下一篇：