mysql主从配置

mysql 主库配置

[mysqld]
character-set-server=utf8
port=3306
connect_timeout=60
wait_timeout=1814400

innodb_buffer_pool_size=4096M

key_buffer_size=256M
max_allowed_packet=10M
thread_stack=256K

sort_buffer_size=6M
read_buffer_size=4M
join_buffer_size=256M

myisam_sort_buffer_size=64M
thread_cache_size=64
query_cache_size=64M

tmp_table_size = 256M
max_heap_table_size = 256M

max_connections=1000
thread_concurrency=24

log-bin=mysql-bin
server-id=1
binlog-do-db=data   --需要主从配置的数据库
binlog-ignore-db=mysql
log_slave_updates=1
sync_binlog=1
binlog_format=MIXED

[client]
character-set-server=utf8
port=3306

然后备份一个完整库，复制到从库上
从库配置

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.

[mysqld]
character-set-server=utf8
port=3306
connect_timeout=60
wait_timeout=1814400

innodb_buffer_pool_size=4096M

key_buffer_size=256M
max_allowed_packet=10M
thread_stack=256K

sort_buffer_size=6M
read_buffer_size=4M
join_buffer_size=256M

myisam_sort_buffer_size=64M
thread_cache_size=64
query_cache_size=64M

tmp_table_size = 256M
max_heap_table_size = 256M

max_connections=1000
thread_concurrency=24

server_id         = 2
relay_log         = mysql-relay-bin
log_slave_updates = 1
replicate-do-db=data --主从备份的库

[client]
#character-set-server=utf8
port=3306

主库重启后执行sql
GRANT REPLICATION SLAVE ON *.* to 'mysync'@'192.168.10.101' identified by 'mysync1234';
SHOW MASTER STATUS
得到mysql-bin.文件名

备库执行
    CHANGE MASTER TO MASTER_HOST='192.168.10.115',
    MASTER_PORT=3306,
     MASTER_USER='mysync',
     MASTER_PASSWORD='mysync1234',
     MASTER_LOG_FILE='mysql-bin.000001', --对应主库查询的文件
     MASTER_LOG_POS=100;--对应主库查询的数据位置

stop slave;
--同步设置
change master to master_heartbeat_period = 10; 
set global slave_net_timeout = 25;
start slave;
SHOW SLAVE STATUS