1、主库配置文件内容
[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
server_id=1
log-bin=mysql-bin
sync-binlog=1
read-only=0
binlog-do-db=yuxuntoo_test
# 不主从复制的数据库
replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
2、从库配置文件内容
[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
# skip-name-resolve一定要加,否则会很慢
server_id=2
log-bin=mysql-bin
read-only=1
binlog-do-db=yuxuntoo_test
# 不主从复制的数据库
replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
3、主库、从库重启服务
4、主从库所在的主机关闭防火墙(避免连接失败)
5、主机上建立账户并授权slave
授权命令如下:
GRANT REPLICATION SLAVE ON *.* to 'backup'@'%' identified by '123456'
查询master状态,命令( show master status;)
结果如下:
6、在从机上配置需要复制的主机,执行如下命令:
CHANGE MASTER TO MASTER_HOST='192.168.0.109',
MASTER_USER='backup',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000005',
MASTER_LOG_POS=154;
MASTER_HOST:主库所在主机的ip
MASTER_USER、MASTER_PASSWORD:5步中所创建的授权用户记密码
MASTER_LOG_FILE:5步中查询主机状态中的File列对应的bin-log信息(注意保持一致)
MASTER_LOG_POS:5步中查询主机状态中的Position列对应的复制起点信息(注意保持一致)
# 执行完了复制命令后,需要启动从机:
start slave;
# 查看从机的状态:
show slave status\G
结果如下图(第一个红框很重要,两个Yes。第二个红框是错误信息):
至此,主从已搭建完成。