1、在主库配置文件添加如下配置:
#Server ID,一般设置成IP地址的最后一位,如下测试就按后两位
server_id=93
#开启log bin,名字最好有意义用来区分
log-bin=/data/br/base/my_3306/binlog/binlog
#需要进行复制的数据库,可以指定数据库
#binlog-do-db=DB_master
#不需要备份的数据库,可以设置多个数据库,一般不会同步mysql这个库
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
#为每个session 分配的内存,在事务过程中用来存储二进制日志的缓存
binlog_cache_size=1m
#二进制日志自动删除/过期的天数。默认值为0,表示不自动删除。
expire_logs_days=7
# 跳过主从复制中遇到的所有错误或指定类型的错误,避免slave端复制中断。
# 如:1062错误是指一些主键重复,1032错误是因为主从数据库数据不一致
slave_skip_errors=1062
2、重启数据库
3、新建同步用户,并授权
CREATE USER 'replication'@'192.168.1.51' IDENTIFIED BY 'bonree365';
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'10.240.49.94';
flush privileges;
4、查看主库状态:
show master status;
5、在从库机器上面验证同步用户是否能够登陆主库,能够正常登陆说明同步用户创建没问题
6、在从库配置文件添加如下配置,并重启数据库
#add slave-server
server_id=94
#binlog-ignore-db=mydql
#binlog-ignore-db=information_schema
#binlog-ignore-db=performance_schema
#log-bin=dev-slave-bin
binlog_cache_size=1M
binlog_format=mixed
expire_logs_days=7
slave_skip_errors=1062
relay_log=/data/br/base/my_3306/binlog/dev-relay-bin
#log_slave_updates=1
read_only=1
7、添加链接到主库同步复制的账户
change master to master_host='10.240.49.93',master_user='replication',master_password='Bonree@365',master_port=33306,master_log_file='binlog.000001',master_log_pos=0;
8、启动从库同步
start slave;
9、查看从库同步状态
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.240.49.93
Master_User: replication
Master_Port: 33306
Connect_Retry: 60
Master_Log_File: binlog.000001
Read_Master_Log_Pos: 1281
Relay_Log_File: dev-relay-bin.000003
Relay_Log_Pos: 1488
Relay_Master_Log_File: binlog.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1281
Relay_Log_Space: 1870
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 93
Master_UUID: d50ed5d5-9a29-11e8-ac8a-02420a630002
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: d50ed5d5-9a29-11e8-ac8a-02420a630002:1-5
Executed_Gtid_Set: d50ed5d5-9a29-11e8-ac8a-02420a630002:1-5
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
验证同步状态,在主库创建表,插入数据。然后在从库上面查看是否同步过来
create table slave_t(id int(10) not null, name varchar(20)) ;
insert into slave_t values(1,'name01');
mysql> select * from slave_t;
+----+--------+
| id | name |
+----+--------+
| 1 | name01 |
+----+--------+
1 row in set (0.00 sec)
错误排查:
如果Slave_IO_Running状态为No,查看日志出现Error_code:1593报错,说明主从库的UUID一样。
删除从库data目录下的auto.cnf,然后重启数据库,发现两边的uuid不一样了,就可以了