1.1、配置主数据库:
vim /etc/my.cnf
[mysqld]
server-id=1
log-bin=master-bin
log-bin-index=master-bin.index
binlog_do_db=test_slave ##要同步的数据库的名称
#不同步的数据库,可设置多个
binlog_ignore_db=mysql
binlog-ignore-db=information_schema
1.2、重启mysql:service mysqld restart
1.3、查看主服务器状态:mysql> show master status;
注意:记录好File和Position,后面要用
2.1、配置从数据库:
vim /etc/my.cnf
[mysqld]
server-id=2
log-bin=master-bin
relay-log=slave-relay-bin
relay-log-index=slave-relay-bin.index
replicate-do-db=test_slave ##要同步的数据库的名称,与主库一只
replicate-ignore-db=mysql
log-slave-updates
slave-skip-errors=all
2.2、重启数据库;
2.3、登录数据库:(复制上面1.3的信息)
mysql> stop slave; #设置连接主库信息
mysql> change master to master_host='主库IP',,master_port=3306,master_user='主库用户',master_password='主库密码',master_log_file='主库log_file',master_log_pos=主库POS;
mysql> start slave; #开启Slave
2.4、查看信息:mysql> show slave status\G;
主要是看:这两个都要是YES。
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 主库IP
Master_User: 主库用户
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000006
Read_Master_Log_Pos: 1491
Relay_Log_File: slave-relay-bin.000024
Relay_Log_Pos: 284
Relay_Master_Log_File: master-bin.000006
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: test_slave
Replicate_Ignore_DB: mysql
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: 1491
Relay_Log_Space: 621
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: 1
Master_UUID: ae0dd5de-3916-11e7-8c75-00163e13f64e
Master_Info_File: /www/server/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
2.5、碰到问题:mysql> show variables like 'log_error%'; 可查看mysql-error.log,以排查问题
Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
原因分析:
mysql 5.6的复制引入了uuid的概念,各个复制结构中的server_uuid得保证不一样,但是查看到直接copy data文件夹后server_uuid是相同的,show variables like '%server_uuid%';
解决方法:
找到/www/server/data文件夹下的auto.cnf文件,修改里面的uuid值,保证各个db的uuid不一样,重启db即可