MYSQL-主从同步

MYSQL-主从同步

1.使用mysql自带的master-salve功能，做数据库主从备份。达到master节点实时同步数据到备份节点。一下记录操作步骤。

a.使用docker搭建mysql环境

—- 启动mysql 挂载到宿主机目录，建立数据库，root密码。mysql用户
docker run --name mysql3307 -p 3307:3306 --privileged=true -ti -e MYSQL_ROOT_PASSWORD=123456 -e MYSQL_DATABASE=enjoy -e MYSQL_USER=user -e MYSQL_PASSWORD=pass -v /home/mysql/docker-data/3307/conf:/etc/mysql/conf.d -v /home/mysql/docker-data/3307/data/:/var/lib/mysql -v /home/mysql/docker-data/3307/logs/:/var/log/mysql -d mysql:5.7


docker run --name mysql3311 -p 3311:3306 --privileged=true -ti -e MYSQL_ROOT_PASSWORD=123456 -e MYSQL_DATABASE=consult -e MYSQL_USER=user -e MYSQL_PASSWORD=pass -v /home/mysql/docker-data/3311/conf:/etc/mysql/conf.d -v /home/mysql/docker-data/3311/data/:/var/lib/mysql -v /home/mysql/docker-data/3311/logs/:/var/log/mysql -d mysql:5.7

b.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=/home/mysql/docker-data/3307/data
#socket=/home/mysql/docker-data/3307/mysql.sock


character_set_server=utf8
init_connect='SET NAMES utf8'


# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0


#log-error=/home/mysql/docker-data/3307/logs/mysqld.log
#pid-file=/home/mysql/docker-data/3307/mysqld.pid
lower_case_table_names=1
server-id=1403311
log-bin=mysql-bin
auto_increment_increment=2
auto_increment_offset=1


#rpl_semi_sync_master_enabled=1
#rpl_semi_sync_master_timeout=10000

c.主mysql master节点开启权限

—- 创建用户 并赋予slave相关权限
GRANT REPLICATION SLAVE,FILE,REPLICATION CLIENT ON *.* TO 'repluser'@'%' IDENTIFIED BY '123456';

-- 刷新权限
FLUSH PRIVILEGES; 

-- 查看master节点状态 og_pas master_log_file信息等信息
show master status;

—- 查看状态
SHOW PROCESSLIST;

d.slave节点 配置 开启slave

—- 设置salve配置 置顶master节点地址。端口。用户 密码。log_pas master_log_file信息 需要master使用命令查看
change master to master_host='192.168.236.135',master_port=3310,master_user='repluser',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=631;

—- 开启slave
start slave;

—- 查看状态
SHOW PROCESSLIST;

结果

到这里主从同步就完成了！可以尝试修改master节点数据来验证了