select version(); #版本查看:
mysql -V 
mysql> status
mysql --help | grep Distrib
>mysql --help | grep my.cnf 查看配置文件在什么地方

生成一个同步用户在主库
GRANT REPLICATION SLAVE,FILE ON *.* TO "lsync"@"%" IDENTIFIED BY "password"
  • 1.主配置
[mysqld]
server-id=1
log-bin=mysql-bin
binlog-do-db=test      //要同步的数据库,多个就添加多行
binlog-ignore-db=mysql  #跳过的数据库
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema #如果库不存在跳过
binlog_format=mixed #日志的格式化
  • 2.配置从数据库的
[mysqld] 
#log-bin=mysql-bin
server-id=2  #检查现有的id之类的是否有,注释掉  skip-slave-start也注释掉
#binlog-ignore-db=mysql #忽略的数据库
#binlog-ignore-db=information_schema
#binlog-ignore-db=performance_schema
#replicate-do-db=test  /要同步的mstest数据库
#replicate-ignore-db=mysql   //要忽略的数据库
#replicate-ignore-db=performance_schema
#replicate-ignore-db=performance_schema  
#replicate-ignore_table=ndj01.log_coin //要忽略的表
log-slave-updates
slave-skip-errors=all
slave-net-timeout=60
  • 注: 5.6以后host pwd需要在命令行里指定 同步主机和密码
mysql -u root -p mysql
change master to master_host="192.168.10.99",master_user="*",master_password="*",master_log_file="mysql-bin.000004",master_log_pos=28125;
change master to master_host="192.168.10.218",master_user="root",master_password="password";
stop slave;
start slave;
  • 5、验证是否成功
show master status;    主服务器状态 主查
show slave status;  从服务器状态 要先有表 在从上或者数据为里查
表中查看错误原因和是否运行
show slave hosts; #查看从库连接主机

字段:Slave_SQL_Running是运行成功   Last_sql_errr是错误

出错处理

  • server_id重复
  1. 检查server_id
  2. 检查server_uuid
show variables like '%server_id%';
show variables like '%server_uuid%';

select uuid();  #重新生成uuid

#/data/auto.cnf
server-uuid=xxxx-xxx-xxx-xxxx
  • log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master

    主从库的同步允许包大小改大即可

#my.inimax_allowed_packet=1024M
  • Client requested master to start replication from position > file size

    从库的日志读取位置不对

show master status; #查看主库的文件和pos
#从库更新
change master to master_log_file="mysql-bin.000006",master_log_pos=154
start slave;
show slave status;