mac 本机配置mysql主从

主配置文件

[client]
default-character-set=utf8
port        = 3306 
socket      = /tmp/mysql.sock  
[mysqld]
character-set-server=utf8
init_connect='SET NAMES utf8
port        = 3306 
socket      = /tmp/mysql.sock 
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 
character-set-server=utf8
sql_mode='NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,PIPES_AS_CONCAT,ANSI_QUOTES' 
init_connect='SET NAMES utf8'
#log-bin = /usr/local/var/mysql/logs/mysql-bin.log
expire-logs-days = 14
max-binlog-size = 500M
log-bin=master-bin
binlog_format=mixed 
server_id = 1 

# 表示只备份modou数据库
binlog-do-db=doushang
# 表示忽略备份mysql （系统数据库）
binlog_ignore_db=mysql
binlog_ignore_db=sys
 
[mysqldump] 
quick 
max_allowed_packet = 16M 
 
[mysql] 
no-auto-rehash 
default-character-set=utf8  
 
[myisamchk] 
key_buffer_size = 20M 
sort_buffer_size = 20M 
read_buffer = 2M 
write_buffer = 2M 
 
[mysqlhotcopy] 
interactive-timeout


[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-server = utf8mb4

从配置文件

[client]
port = 3307
socket = /usr/local/mysql-master/tmp/mysql.sock

[mysqld]
user = root
port = 3307
socket = /usr/local/mysql-master/tmp/mysql.sock
basedir = /usr/local/mysql
datadir = /usr/local/mysql-master/data

server_id=211

[mysqld_safe]
log-error = /usr/local/mysql-master/data/mysqld.local.err
pid-file = /usr/local/mysql-master/data/mysqld.local.pid

#开启二进制日志
log-bin=mysql-bin
binlog-format=mixed
#设置只读权限
read-only =1

#日志格式（Statement，Row，Mixedlevel）
binlog_format  = ROW

#操作日志
log-bin-index      = mysql-bin.index
relay-log        = relay-log
relay_log_index     = relay-log.index

#GTID配置
#是否开启GTID功能
gtid_mode=on
enforce_gtid_consistency=on
#同步主库操作日志
log-slave-updates = 1

# 只对modou这个数据库进行镜像处理
replicate-do-db = doushang
# 不对mysql数据库进行镜像处理
replicate-ignore-db = mysql
replicate-ignore-db = modou
replicate-ignore-db = febs_base
replicate-ignore-db = febs_quartz
replicate-ignore-db = sys
replicate-ignore-db = example
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema

#跳过所有错误
slave-skip-errors=all

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

开启主从步骤

==进入主mysql==
mysql -uroot -p123456 -h127.0.0.1 -P3307
create user 'sync'@'127.0.0.1' identified by '123456';
show variables like 'server_id';
grant REPLICATION SLAVE,Replication client,reload on *.* to 'sync'@'127.0.0.1' identified by '123456';
==进入从mysql==
change master to master_host='127.0.0.1', master_user='sync', master_password='123456', master_port=3306 ,master_log_file='master-bin.000009' , MASTER_LOG_POS= 0;
start slave;
show slave status \G;