环境搭建之mysql主从配置

[mysql]
 default-character-set=utf8
[client]
 default-character-set=utf8
 port=3307


[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M


# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin


# These are commonly set, remove the # and set as required.
basedir = D:\mysql\mysql-5.6.11-winx64
datadir = D:\mysql\mysql-5.6.11-winx64\data
tmpdir = D:\mysql\mysql-5.6.11-winx64\tmp
character-set-server=utf8
port = 3307


#        master配置
# 主服务器唯一id(必填)
server_id = 1
# 启用二进制日志(必填)
log-bin = D:\mysql\mysql-5.6.11-winx64\data\mysql-bin
# 启用错误日志(非必填)
log-error = D:\mysql\mysql-5.6.11-winx64\data\mysql-err
# 主机 读写都可以(0 读写   1 只读)
read-only=0
# 设置不要复制的数据库(非必填)
binlog-ignore-db = dbname
# 设置需要复制的数据库(非必填)
binlog-do-db = dbname





#        slave配置
# 从服务器唯一id(必填)
server_id = 2
# 启用二进制日志(非必填)
log-bin = D:\mysql\mysql-5.6.11-winx64\data\mysql-bin
# 启用错误日志(非必填)
log-error = D:\mysql\mysql-5.6.11-winx64\data\mysql-err
# 从机 暂时只读吧(0 读写   1 只读)
read-only=1




mysql主从配置步骤
1.主从配置文件如上
2.主机建立从机复制账户
mysql > grant replication slave on *.* to 'slave'@'主机ip' identified by 'slave';
mysql > flush privileges;
mysql > show master status\G;---记录File和Pos的值
3.从机启动复制功能
mysql > change master to master_host='主机ip‘,master_port='3307',master_user='slave',master_password=
'slave',master_log_file='主机show master status中',master_log_pos='主机show master status中';
    mysql > start slave
mysql > show slave status\G;---Slave_IO_Running:Yes Slave_SQL_Running:Yes 这两个都为Yes表示配置成功
(如果由于某些原因不需要复制master可以先stop slave 直到需要再次同步时再执行上面change master... 注意此时pos一定要是最新值 最后start slave)


# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M 


sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES