Mysql 多主一从
1)在Mysql中有一mysqld_multi命令，可用于在一台物理服务器运行多个Mysql服务
2)根据Mysql管理手册中提到:每个Mysql的服务都可为独立的，所以它都调用一个my.cnf中各自不同的启动选项--就是下文中将提到的GNR值,使用不同的端口,生成各自的套接文件,服务的数据库都是独立
的
3)mysqld_multi是管理多个mysqld的服务进程，这些服务进程程序不同的unix socket或是监听于不同的端口。他可以启动、停止和监控当前的服务状态。
4)程序在my.cnf(或是在--config-file自定义的配置文件)中搜索[mysqld#]段，"#"可以是任意的正整数。这个正整数就是在下面提及的段序列，即GNR。段的序号做为mysqld_multi的参数，来区别不同
的段，这样你就可以控制特定mysqld进程的启动、停止或得到他的报告信息。这些组里的参数就像启动一个mysqld所需要的组的参数一样。但是，如果使用多服务，必须为每个服务指定一个unix socket
或端口
##版本 5.1.36
1.安装
groupadd mysql
useradd mysql -c "start mysqld's account" -d /dev/null -g mysql -s /sbin/nologin
cd /usr/local/src
tar -xzvf $mysqlcode
cd "mysql-5.1.36"
#编译
./configure \
--prefix=/usr/local/mysql \
--datadir=/usr/local/mysql/data1 \
--with-mysqld-user=mysql \
--with-charset=utf8 \
--with-collation=utf8_bin \
--with-extra-charsets=big5,ascii,gb2312,gbk,utf8,latin1 \
--without-debug \
--with-client-ldflags=-all-static \
--with-mysqld-ldflags=-all-static \
--disable-shared \
--without-isam \
--without-innodb \
--enable-assembler
./configure --prefix=/usr/local/mysql \
--datadir=/usr/local/mysql/data1
--enable-thread-safe-client \
--with-charset=gbk \
--with-extra-charsets=all \
--enable-assembler
#编译参数按需求修改
make && make install
cp support-files/my-medium.cnf /etc/my.cnf
cd /usr/local/mysql
chown -R mysql .
bin/mysql_install_db --user=mysql
chown -R root .
chown -R mysql var
#bin/mysqld_safe --user=mysql &
#首个数据库生成指定 --datadir=/usr/local/mysql/data1
其他的库则直接 复制 data1即可
cd /usr/local/mysq/
mkdir data2
cp -rp data1/* data2/
#权限的设置同于 data1
二：主从配置文件
###################START#############
[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
user = shutdown
password = shutdown
log = /usr/local/mysql/etc/multi.log
#以上用户为使用mysqld_multi关闭数据库时使用的用户
#需要分别进入两个实例后执行grant给shutdown 赋权限
#grant shutdown on *.* to shutdown@localhost identified by "shutdown"
#flush privileges;
[mysqld1]
port = 3306
socket = /tmp/mysql.sock
skip-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
pid-file = /usr/local/mysql/data1/db-app1.pid
datadir = /usr/local/mysql/data1
#log-bin=mysql-bin1
replicate-do-db=act
replicate-do-db=fso_log
replicate-do-db=fso
replicate-do-db=info_game
replicate-do-db=info_account
#binlog_format=mixed
lower_case_table_names = 1
event_scheduler = 1
skip-name-resolve
server-id = 100
master-host = 192.168.1.138
master-user = slave
master-password = password
master-port = 3306
slave-skip-errors=all
#log-slave-updates
master-connect-retry=10
skip-locking
master-info-file = relay1-master.info
relay-log = relay1-relay-bin
relay-log-index = relay1-relay-bin.index
relay-log-info-file=relay1-relay-log.info
[mysqld2]
port = 3307
socket = /tmp/mysql2.sock
skip-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
pid-file = /usr/local/mysql/data2/db-app2.pid
datadir = /usr/local/mysql/data2
replicate-do-db=act
replicate-do-db=fso_log
replicate-do-db=fso
#log-bin=mysql-bin2
#binlog_format=mixed
lower_case_table_names = 1
event_scheduler = 1
skip-name-resolve
server-id = 100
master-host = 192.168.1.138
master-user = slave
master-password = password
master-port = 3306
#log-slave-updates
master-connect-retry=10
slave-skip-errors=all
master-info-file = relay2-master.info
relay-log = relay2-relay-bin
relay-log-index = relay2-relay-bin.index
relay-log-info-file=relay2-relay-log.info
[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
###################END############################
以上仅为两个实例．可以按数据量及机器的负载情况增加<>
###
具体同于MYSQL一主一从步骤
###
三,mysqld_multi命令.
使用如下参数来启动mysqld_multi: (注:该命令在mysql的bin目录中,根据上面所提到./configure --prefix=/usr/local/mysql ,所以该文件应该在 /usr/local/mysq/bin, 这得根据你安装时所指定的
路径 )
db-app:/ # mysqld_multi [options] {start|stop|report} [GNR[,GNR]...]
start,stop和report是指你想到执行的操作。你可以在单独的服务或是多服务上指定一个操作，区别于选项后面的GNR列表。如果没有指定GNR列表，那么mysqld_multi将在所有的服务中根据选项文件进
行操作。
# /usr/local/mysql/bin/mysqld_multi --config-file=/etc/my.cnf start 1 只启动 第一个mysql服务,相关文件由my.cnf中mysql1设定.
# /usr/local/mysql/bin/mysqld_multi --config-file=/etc/my.cnf stop 1 启止 第一个mysql服务
# /usr/local/mysql/bin/mysqld_multi --config-file=/etc/my.cnf start 1-2 启动 第1至2mysql服务,其实就是我这里的全部.
# /usr/local/mysql/bin/mysqld_multi --config-file=/etc/my.cnf report 1-2
#关闭
# /usr/local/mysql/bin/mysqld_multi stop 1
当需要在其中随便一个实例中可看到另外一个实例同步过来的库时
则将两个实例都放在同一个目录当中
因同步时每个实例都会产生同步的信息
所以需要将日志信息指定到不同的文件当中
master-info-file = relay1-master.info
relay-log = relay1-relay-bin
relay-log-index = relay1-relay-bin.index
relay-log-info-file=relay1-relay-log.info
访问某个实例
mysql -u root -S /tmp/mysql.sock1 -p
