mysql5.5数据库多实例部署，我们可以分以下几个步骤来完成。
1、 mysql多实例的原理
2、 mysql多实例的特点
3、 mysql多实例应用场景
4、 mysql5.5多实例部署方法
一、mysql多实例的原理
mysql多实例，简单的说，就是在一台服务器上开启多个不同的mysql服务端口（如3306，3307），运行多个mysql服务进程。这些服务进程通过不同的socket监听不同的服务端口，来提供各自的服务。
这些mysql实例共用一套mysql安装程序，使用不同的my.cnf配置文件、启动程序、数据文件。在提供服务时，mysql多实例在逻辑上看来是各自独立的，各个实例之间根据配置文件的设定值，来取得服务器的相关硬件资源。
二、mysql多实例的特点
2.1 有效的利用服务器资源
当单个服务器资源有剩余时，可以充分利用剩余的服务器资源来提供更多的服务。
2.2 节约服务器资源
当公司资金紧张，但是数据库需要各自提供独立服务，而且需要主从同步等技术时，使用多实例就最好了。
2.3 出现资源互相抢占问题
当某个实例服务并发很高或者有慢查询时，会消耗服务器更多的内存、CPU、磁盘IO等资源，这时就会导致服务器上的其它实例提供访问的质量下降，出现服务器资源互相抢占的现象。
三、mysql多实例应用场景
3.1 资金紧张型公司的选择
当公司业务访问量不太大，又舍不得花钱，但同时又希望不同业务的数据库服务各自独立，而且需要主从同步进行等技术提供备份或读写分离服务时，使用多实例是最好不过的。
3.2 并发访问不是特别大的业务
当公司业务访问量不太大，服务器资源基本闲置的比较多，这是就很适合多实例的应用。如果对SQL语句优化的好，多实例是一个很值得使用的技术。即使并发很大，只要合理分配好系统资源，也不会有太大问题。
四、mysql5.5多实例部署方法
4.1 mysql5.5多实例部署方法
mysql5.5多实例部署方法一个是通过多个配置文件启动多个不同进程的方法，第二个是使用官方自带的mysqld_multi来实现。
第一种方法我们可以把各个实例的配置文件分开，管理比较方便。第二种方法就是把多个实例都放到一个配置文件中，这个管理不是很方便。所以在此我们选择第一种方法，而且以下实验我们全部是在此方法下进行的。
4.2 mysql5.5的安装及配置
要配置mysql5.5多实例，我们首先要安装mysql5.5，有关mysql5.5的安装可以查看《烂泥：mysql5.5数据库cmake源码编译安装》这篇文章。
mysql5.5安装完毕后，我们不要启动mysql，因为此时mysql是单实例的。
4.3 创建mysql多实例的数据目录
现在我们来创建mysql5.5多实例的数据目录，在此我们创建两个mysql实例3306和3307。创建各自的数据目录，如下：
mkdir -p /data/{3306,3307}/data
tree -L 2 /data/
4.4 修改mysql5.5多实例my.cnf文件
实例3306和3307的数据目录创建完毕后，我们来配置实例3306与3307的my.cnf配置文件。
复制mysql5.5安装目录support-files下的my-medium.cnf为my.cnf，并把内容修改为下。现在以3306这个实例为例，如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
|
[client]
port =
3306
socket = /data/
3306
/mysql.sock
[mysqld]
port =
3306
socket = /data/
3306
/mysql.sock
basedir = /usr/local/mysql
datadir = /data/
3306
/data
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache =
64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
skip-name-resolve
log-bin=mysql-bin
binlog_format=mixed
max_binlog_size = 500M
server-id =
1
[mysqld_safe]
log-error=/data/
3306
/ilanni.err
pid-file=/data/
3306
/ilanni.pid
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
|
注意图中***标记出来的部分，就是我们主要修改的，其他默认保持不变。
以上是实例3306的my.cnf配置文件，现在我们来配置实例3307的my.cnf。实例3307的配置文件my.cnf我们直接复制实例3306的my.cnf文件，然后通过sed命令把该文件中的3306修改为3307即可。如下：
cp /data/3306/my.cnf /data/3307/my.cnf
sed -i 's/3306/3307/g' /data/3307/my.cnf
或者
sed -e 's/3306/3307/g' /data/3306/my.cnf >/data/3307/my.cnf
![clip_image001[4]](https://www.2cto.com/uploadfile/Collfiles/20141128/2014112809192859.jpg "clip_image001[4]")
4.5 初始化mysql多实例
实例3306和3307的my.cnf配置文件修改完毕后，我们需要来初始化这两个实例，使用mysql_install_db命令。如下：
/usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/data/3306/data --user=mysql
/usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/data/3307/data --user=mysql
注意mysql5.5的mysql_install_db在mysql5.5的/usr/local/mysql/scripts/mysql_install_db目录下。
查看实例初始化后的情况，如下：
tree -L 3 /data/
通过上图我们可以看到mysql实例在初始化后会创建基本的数据库。
现在再来看看初始化创建文件的属性，如下：
通过上图可以看到初始化创建的文件都是属于mysql这个用户的。
为什么会是这样呢？
这个是因为我们初始化加入--user=mysql这个选项。当然这个也是我们所需要的，因为这增加了mysql的安全性。
4.6 修改mysql实例的数据库目录权限
mysql实例初始化完毕后，我们现在把实例3306和实例3307的数据目录权限重新赋予给mysql用户。如下：
chown -R mysql:mysql /data/3306
chown -R mysql:mysql /data/3307
这个地方建议一定要操作一遍，否则在启动mysql实例时，会提示出错。导致mysql实例无法启动。
4.7 启动mysql5.5多实例
我们现在来启动实例。使用如下命令：
/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/3306/my.cnf &
/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/3307/my.cnf &
ps aux |grep mysqld
通过上图，我们可以看到实例3306和3307 都已经正常启动。也说明我们的mysql5.5多实例已经配置成功。
其实单实例mysql的启动也是通过mysqld_safe命令来启动。它默认会加载/etc/my.cnf文件。
4.8 登录mysql5.5多实例
登录多实例数据库时，我们需要加入该实例的socket文件，才能正常登录。现在以3306实例为例。
本地登录3306实例，如下：
mysql -uroot -p -S /data/3306/mysql.sock
本地登录进去后，我们在实例3306上创建一个ilanni3306的数据库。如下：
create database ilanni3306;
show databases;
现在我们远程登录实例3306，并查看刚刚新建的数据库。如下：
mysql -h192.168.1.213 -uroot -p -S /data/3306/mysql.sock
或者：mysql -h192.168.1.213 -uroot -p –P 3306
通过上图，我们可以看到远程也是可以连接3306实例的。
4.9 修改mysql5.5多实例root密码
修改实例3306的root密码，使用mysqladmin命令。如下：
mysqladmin -uroot -p password 123456 -S /data/3306/mysql.sock
到此我们的mysql多实例部署就已经完成。
mysql启动脚本：
#init
port=3306
mysql_user="root"
mysql_pwd="cancer"
CmdPath="/application/mysql/bin"
mysql_sock="/data/${port}/mysql.sock"
#startup 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 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 function
function_restart_mysql()
{
printf "Restarting MySQL...\n"
function_stop_mysql
sleep 2
function_start_mysql
}
case $1 in
start)
function_start_mysql
;;
stop)
function_stop_mysql
;;
restart)
function_restart_mysql
;;
*)
printf "Usage: /data/${port}/mysql {start|stop|restart}\n"
esac一. Mysql多实例编译安装
-------------------------------------
1.多实例定义：
同一套mysql程序，不同的“my.cnf,启动程序，端口”。属于逻辑上面的多实例。
2.下载mysql-5.5.32.tar.gz
3.安装相关依赖：
yum install ncurses-devel libaio-devel cmake gcc-c++
4.创建用户：
useradd mysql -s /sbin/nologin -M
5.解压缩开始编译：
cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql-5.5.32/ -DMYSQL_DATADIR=/data/mysql \
-DMYSQL_UNIX_ADDR=/usr/local/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=0make&&make install
6.创建实例目录：
mkdir -p /data/{3306,3307}/data
7.创建配置文件：
PS：server-id 每个实例要不一样。
vim /data/3306/my.cnf
[client]
port = 3306
socket = /data/3306/mysql.sock
[mysql]
no-auto-rehash
[mysqld]
user = mysql
port = 3306
socket = /data/3306/mysql.sock
basedir = /usr/local/mysql-5.5.32
datadir = /data/3306/data
open_files_limit = 1024
character-set-server = utf8
back_log = 600
max_connections = 800
max_connect_errors = 3000
table_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-bin
relay-log-info-file = /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 = 1M
#myisam_max_sort_file_size = 10G
#myisam_max_extra_sort_file_size = 10G
#myisam_repair_threads = 1
#myisam_recover
lower_case_table_names = 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 = 16G #设置成内存的一半最好
innodb_data_file_path = ibdata1:128M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 8
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
vim /data/3307/my.cnf
[client]
port = 3307
socket = /data/3307/mysql.sock
[mysql]
no-auto-rehash
[mysqld]
user = mysql
port = 3307
socket = /data/3307/mysql.sock
basedir = /usr/local/mysql-5.5.32
datadir = /data/3307/data
open_files_limit = 1024
character-set-server = utf8
back_log = 600
max_connections = 800
max_connect_errors = 3000
table_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-bin
relay-log-info-file = /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 = 1M
#myisam_max_sort_file_size = 10G
#myisam_max_extra_sort_file_size = 10G
#myisam_repair_threads = 1
#myisam_recover
lower_case_table_names = 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 = 16G #设置成内存的一半
innodb_data_file_path = ibdata1:128M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 8
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
vim /data/3306/mysql
#3307只要改动port参数就可以
#!/bin/sh
#init
port=3306
mysql_user="root"
mysql_pwd="Root123"
cmdpath="/usr/local/mysql-5.5.32/bin"
mysql_sock="/data/${port}/mysql.sock"
#startup function
function_start_mysql()
{
if [ ! -e "$mysql_sock" ];then
echo "starting mysql…"
/bin/sh ${cmdpath}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 > /devull &
else
echo "mysql is running…"
exit
fi
}
#stop function
function_stop_mysql()
{
if [ ! -e "$mysql_sock" ];then
echo "mysql is stopped…"
exit
else
echo "stoping mysql…"
${cmdpath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdown
fi
}
#restart function
function_restart_mysql()
{
echo "restarting mysql…"
function_stop_mysql
sleep 2
function_start_mysql
}
case $1 in
start)
function_start_mysql
;;
stop)
function_stop_mysql
;;
restart)
function_restart_mysql
;;
*)
echo "usage: /data/${port}/mysql {start|stop|restart}"
esac
chown -R mysql:mysql /data/
find /data -type f -name mysql -exec chmod 700 {} \;
10.添加环境变量：
echo 'export PATH=/usr/local/mysql-5.5.32/bin/:$PATH' >> /etc/profile
11.初始化数据库：
cd /usr/local/mysql-5.5.32/scripts/
./mysql_install_db --basedir=/usr/local/mysql-5.5.32/ --datadir=/data/3306/data/ --user=mysql
./mysql_install_db --basedir=/usr/local/mysql-5.5.32/ --datadir=/data/3307/data/ --user=mysql
12.启动数据库：
/data/3306/mysql start
/data/3307/mysql start
13.设置数据库root密码：
mysqladmin -uroot password Root123 -S /data/3306/mysql.sock
mysqladmin -uroot password Root456 -S /data/3307/mysql.sock
14.登陆数据库：
mysql -uroot -pRoot123 -S /data/3306/mysql.sock
mysql -uroot -pRoot456 -S /data/3307/mysql.sock
15.远程登陆数据库：
mysql -uroot -pRoot123 -h 172.16.1.214 -P 3306
二. Mysql主从复制基础
-------------------------------------
1.主动复制架构图：
2.主从复制流程图：
3.主从复制条件：
#我们下面的操作是多实例主从复制，3306为主库，3307为从库。
主库要开启log-bin，主库和从库的server-id要不一样。
4.在主库上面创建同步用户：
mysql> grant replication slave on *.* to 'rep'@'172.16.1.%' identified by 'Root1234'; #后面的密码要设置复杂些。
mysql> flush privileges;
5.导出主库数据文件：
一。常规方法：
mysql> flush table with read lock; #先锁表，锁表后不能退出mysql窗口，否则失效
mysql> show master status; #记录bin-log的位置信息
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 | 560 | | |
+------------------+----------+--------------+------------------+
mysqldump -uroot -pRoot123 -S /data/3306/mysql.sock --events -A -B|gzip >/tmp/bak_$(date +%F).sql.gz
mysql> unlock tables; #解锁数据库
二。快捷方法：
mysqldump -uroot -pRoot123 -S /data/3306/mysql.sock --events -A -B -F --master-data=2 -x|gzip >/tmp/bak_f_$(date +%F).sql.gz
-x 自动锁表
-F --master-data=2 以备注形式记录bin-log的位置。（可以打开文件看头部备注信息）6.还原备份内容到从库：
gzip -d bak_2016-12-02.sql.gz
mysql -uroot -pRoot456 -S /data/3307/mysql.sock < bak_2016-12-02.sql
7.在从库创建master_info文件,并启动同步：
mysql -uroot -pRoot456 -S /data/3307/mysql.sock <<EOF
CHANGE MASTER TO
MASTER_HOST='172.16.1.214',
MASTER_PORT=3306,
MASTER_USER='rep',
MASTER_PASSWORD='Root1234',
MASTER_LOG_FILE='mysql-bin.000004',
MASTER_LOG_POS=560;
EOF
mysql> start slave; #开启同步
mysql> show slave status \G ; #查看同步状态信息
8.监控mysql主从复制的状态指标：
mysql -uroot -pRoot456 -S /data/3307/mysql.sock -e "show slave status \G ;" | egrep -i "_Running|_Behind"
Slave_IO_Running: Yes #同步进程是否启动
Slave_SQL_Running: Yes #sql进程是否启动
Seconds_Behind_Master: 0 #从库同步完成需要的时间
三. Mysql主主复制+读写分离进阶
--------------------------------------------------------------------
1.如果出现同步错误，需要忽略错误，进行下条语句继续同步。
mysql> stop slave;
mysql> set global sql_slave_skip_counter =1;
mysql> start slave
vim /data/3307/my.cnf
slave-skip-errors = 1032,1062,1007,1008
3.让从库开启bin_log的办法：
vim /data/3307/my.cnf
log-slave-updates
log-bin = /data/3307/mysql-bin
expire_logs_days = 7 #日志只保留7天
4,.双主库同步方案：
a.让表的ID自增，让主1写1,3,5 主2 写2,4,6。
A增加：
auto_increment_increment = 2
auto_increment_offset=1
B增加：
b.不让表的ID自增，通过WEB程序去seq服务器取ID，写入双主。
5.读写分离的方案：
a.通过开发的程序控制读写的位置。 （推荐）
b.通过开源软件实现。（Amoeba，Mysql_proxy）
c.自己开发DAL层。（大公司用）
6.增量备份步骤：
一。选择一台从库，不对外提供业务，专门用了做备份。此从库开启bin_log功能，做增量备份。
二。备份的时候需要停止sql线程,在用mysqldump备份：
stop slave sql_thread; #停止sql线程,不会停止同步IO进程
三。然后将备份文件和bin_log文件远程保存起来。
下面命令可以用来远程备份bin_log:
mysqlbinlog --read-from-remote-server --raw --host=172.16.1.214 --port=3306 --user=rep --password=Root1234 --stop-never mysql-bin.000001
7.主从复制延时原因：
a.从库太多，一般要3-5个
b.从库配置太低。
c.慢sql过多。
d.高版本mysql支持多线程复制
8.同一账号在主从库拥有不同权限方案：
a.在主库上创建拥有读写权限账号，然后再从库上面收回写入权限。
mysql> grant insert,update,delete,select on *.* to web@'172.16.1.%' identified by '123'; #创建web用户对所有表拥有读写权限，主库执行。
mysql> show grants form web@'172.16.1.%'; #查看用户权限
mysql> REVOKE insert,update,delete ON *.* FROM 'web'@'172.16.1.%'; #回收web用户写入权限，从库执行
b.从库不同步mysql授权表：（推荐方案）
my.cnf 里面加：
replicate-ignore-db = mysql
binlog-ignore-db = mysql
c.在从库设置read-only:
mysql> set global read_only=1;
或者 my.cnf 加 read-only
四. Mysql 灾难恢复演练
--------------------------------------------------------------------
1.主库当机从库接管方案：
a.半同步从库(谷歌半同步插件)。b.选一台从库什么都不干，等着接管。
c.当机后选举一台从库来当主库。
2.查看从库的同步状态：
mysql> show processlist\G;
3.手动从库提升成主库的步骤：
a.停止同步，重置master。
mysql> stop slave ;
mysql> reset master;
b.删除目录文件里面的master.info 和 relay-bin。
c.取消read-only设置，确认bin-log是否开启。
d.重启数据库。
PS: 有一个软件“MHA”可以自动实现上面操作。
4.mysql的高可用方案：
MYSQL+HA+DRBD
MYSQL+MHA
扫一扫
675
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
