MySQL主从复制搭建

两台以上mysql实例 ,server_id,server_uuid不同

清理主库数据
rm -rf /data/3307/data/*
mysqld --initialize-insecure --user=mysql --basedir=/app/mysql --datadir=/data/3307/data
systemctl start mysqld3307
systemctl start mysqld3308
mysql -uroot -p -S /data/3307/mysql.sock -e "select @@server_id";
mysql -uroot -p -S /data/3308/mysql.sock -e "select @@server_id";
mysql -uroot -p -S /data/3307/mysql.sock -e "select @@server_uuid";
mysql -uroot -p -S /data/3308/mysql.sock -e "select @@server_uuid";

主库开启二进制日志

cat /data/3307/my.cnf
server_id=7
log_bin=/data/3307/mysql-bin
cat /data/3308/my.cnf
socket=/data/3308/mysql.sock
log_error=/data/3308/mysql.log

主库建立专用的复制用户（replication slave）

mysql -uroot -p -S /data/3307/mysql.sock
grant replication slave on *.* to repl@'10.0.0.%' identified by '123';
select user,host from mysql.user;

主库全备,从库数据补齐

类似于补课，适合于主库用了很长时间，需要构建从库时，从库数据是和主库一致
mysqldump -uroot -p123456 -S /data/3307/mysql.sock -A -R --triggers -E --master-data=2 
--single-transaction >/tmp/full.sql
vim /tmp/full.sql
##-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=444;
[root@xiaoyong tmp]# mysql -uroot -p -S /data/3308/mysql.sock
mysql [(none)]>set sql_log_bin=0;
mysql [(none)]>source /tmp/full.sql

（change master to）告知从库,复制主库信息

[root@xiaoyong tmp]# mysql -uroot -p -S /data/3308/mysql.sock
[mysql]>help change master to
CHANGE MASTER TO
MASTER_HOST='10.0.0.200',
MASTER_USER='repl',
MASTER_PASSWORD='',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000004',
MASTER_LOG_POS=316,
MASTER_CONNECT_RETRY=10;

(start slave)开启专用的复制线程

线程(三个):主库：binlog_dump_T,从库：IO_T,SQL_T.
[root@xiaoyong tmp]# mysql -uroot -p -S /data/3308/mysql.sock
mysql [(none)]>start slave;
mysql [(none)]>show slave  status \G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes