mysql主从复制实战

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

无法远程连接