MYSQL主从配置

安装好相同的两个相同版本的mysql后

my.conf 配置

基本配置

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
init_connect='SET collation_connection =utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
skip-name-resolve

master主从复制部分配置

server_id=1
log-bin=mysql-bin
read-only=0
#要复制的库
binlog-do-db=db_test_copy
#排除的库
replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema

slave主从复制部分配置

server_id=2
log-bin=mysql-bin
read-only=1
#要复制的库
binlog-do-db=db_test_copy
#排除的库
replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema

重启master和slave

主从绑定同步

1、在master授权用户来同步数据

 GRANT REPLICATION SLAVE ON *.*  TO 'backup'@'%' identified by '123456';

查看主库状态

show master status

2、在从库中，设置主库连接

 change master to master_host='192.168.50.10',master_user=''backup',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=0,master_port=3307;

3、启动从库同步

start slave

4、查看从库状态

show slave status