1:环境准备
master:192.168.0.222
slave:192.168.0.223
测试数据库版本: 5.7.29
2、master配置
在/etc/my.cnf配置文件[mysqld]段中加入如下代码:
server-id=1
log-bin=mysql-bin
master端/etc/my.cnf完整配置代码如下:
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server-id=1
log-bin=mysql-bin
推荐配置/etc/my.cnf:
稍后补齐
在master数据库创建同步用户并设置权限,并查看bin-log文件及position点
set global validate_password_policy=0;
set global validate_password_length=1;
grant replication slave on *.* to 'tongbu'@'%' identified by '123456';
show master status;
slave配置:
在/etc/my.cnf配置文件[mysqld]段中加入如下代码:
server-id=2
slave端/etc/my.cnf完整配置代码如下:
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server-id=2
推荐配置/etc/my.cnf:
稍后补齐
指定master IP、用户名、密码、bin-log文件名及position(mysql-bin.000001,及position439;在主库中执行show master status获取该数据)
change master to
master_host='192.168.0.222',master_user='tongbu',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=439;
在slave执行:start slave;(版本5.7.29,其他版本可尝试slave start;)
测试:
在master创建数据库、表、插入数据,然后在slave查看是否存在对应的数据库、表和数据
CREATE DATABASE `test2` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE test2;
CREATE TABLE `t2` (
`id` int(11) DEFAULT NULL,
`name` char(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into t2 values(1,'test');
报错处理:
The slave I/O thread stops because master and slave have equal MySQL server UUIDs