mysql 技术交流群_MySQL主从复制（运维技术交流群：926402931，欢迎大家一起来交流。）...

1.环境规划

主机名

角色

IP地址

db01

MySQL主库

10.0.0.51

db02

MySQL从库

10.0.0.52

2.所有MySQL都开启binlog功能，确保所有MySQL的server-id不同

主库：

[mysqld]

basedir=/application/mysql

datadir=/application/mysql/data

socket=/application/mysql/tmp/mysql.sock

port=3306

server_id=10

log-error=/var/log/mysql.log

log-bin=/data/binlog/mysql-bin

sync_binlog=1

binlog_format=row

从库：

[mysqld]

basedir=/application/mysql

datadir=/application/mysql/data

socket=/application/mysql/tmp/mysql.sock

port=3306

server_id=11

log-error=/var/log/mysql.log

log-bin=/data/binlog/my-bin

sync_binlog=1

binlog_format=row

3.主库授权主从复制用户

mysql> grant replication slave on *.* to rep@"10.0.0.%" identified by "123456";

4.主库将数据库数据做全备，然后将备份文件推送到从库

[root@db01 ~]# mysqldump -uroot -p123 -A -B -R --master-data=2 --single-transaction |gzip >/backup/full_$(date +%F).sql.gz

[root@db01 ~]# scp /backup/full_2020-04-09.sql.gz root@10.0.0.52:/backup

5.从库将备份文件恢复到数据库

[root@db02 ~]# gunzip /backup/full_2020-04-09.sql.gz

mysql> source /backup/full_2020-04-09.sql

mysql> show databases;

6.从库上找到binlog位置点

[root@db02 ~]# sed -n "22p" /backup/full_2020-04-09.sql

7.从库配置主从复制参数

mysql> change master to

-> master_host="10.0.0.51",

-> master_port=3306,

-> master_user="rep",

-> master_password="123456",

-> master_log_file="mysql-bin.000008",

-> master_log_pos=1100;

mysql> start slave;

mysql> show slave statusG

8.主库创建数据库，然后到从库验证数据是否同步

db01：

mysql> create database test1;

db02：

mysql> show databases;