mysql同步多主,MySQL多主一从同步配置

========================================================================

MySQL多主一从同步配置：

========================================================================

1、服务器情况：

Master A：10.70.14.200

Master B：10.70.14.201

Slave ：10.70.14.80

2、数据同步情况：

Master A ——> Slave ：hcx.table1、hcx.table2

Master B ——> Slave ：hcx.table3、hcx.table4

3、同步账号密码：slave/slave

4、在3台服务器上都安装上MySQL服务，这里以安装路径为 /usr/local/mysql 来进行说明，并把 /usr/local/mysql/bin 添加到 $PATH 环境变量里

5、在两台 Master 上分别创建同步账号：

mysql> grant replication slave,reload,super on *.* to slave@'%' identified by 'slave';

6、初始化 Slave 数据目录：

# mysql_install_db --datadir=/opt/mysql/data1 --user=mysql

# mysql_install_db --datadir=/opt/mysql/data2 --user=mysql

# chown -R mysql:mysql /opt/mysql

7、配置多实例数据库配置文件 /etc/http://www.doczj.com/doc/d984877c79563c1ec5da71b0.htmlf ，修改后的内容如下：

------------------------------------------------------------------------

[mysqld_multi]

mysqld = /usr/local/mysql/bin/mysqld_safe

mysqladmin = /usr/local/mysql/bin/mysqladmin

#user = mysql

#password = 000000

log = /usr/local/mysql/log/multi.log

[mysqld1] # 实例1，可以是其他数字

port = 3306 # 服务端口

socket = /opt/mysql/data1/mysql1.sock

pid-file = /opt/mysql/data1/test-db1.pid

datadir = /opt/mysql/data1

log = /opt/mysql/data1/test-db1.log

user = mysql

log-slow-queries = /opt/mysql/data1/slow-query.log

server-id = 2 # 不能跟 Master A 上的一样

skip-locking

key_buffer_size = 16M

max_allowed_packet = 100M

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

default-character-set = gbk

slow_query_log

long_query_time = 10

lower_case_table_names = 1

skip-name-resolve

log-bin = slave1-bin

replicate-wild-do-table = hcx.table1 # 需要从 Master A 上同步过来的数据

replicate-wild-do-table = hcx.table2

replicate-wild-ignore-table = mysql.% # 不需要从 Master A 上同步过来的数据

binlog_format=mixed

[mysqld2] # 实例2，可以是其他数字，但不能跟上面的重复

port = 3307 # 服务端口，但不能跟上面的重复

socket = /opt/mysql/data2/mysql2.sock

pid-file = /opt/mysql/data2/test-db2.pid

datadir = /opt/mysql/data2

log = /opt/mysql/data2/test-db2.log

user = mysql

log-slow-queries = /opt/mysql/data2/slow-query.log

server-id = 2

skip-locking

key_buffer_size = 16M

max_allowed_packet = 100M

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

default-character-set = gbk

slow_query_log

long_query_time = 10

lower_case_table_names = 1

skip-name-resolve

log-bin = slave2-b