MySQL主从复制

       

简介：本次搭建实在多实例环境下搭建，mysql是5.7版本

架构图：

准备环境：

主机名	ip地址	MySQL数据库
db01	10.0.0.51	mysqld3307 、mysqld3308、mysqld3309

搭建

实例准备

systemctl start mysqld3307
systemctl start mysqld3308
systemctl start mysqld3309
netstat -tulnp

检查server_id

#数据库7
mysql -S /tmp/mysql3307.sock -e "select @@server_id";
+-------------+
| @@server_id |
+-------------+
|           7 |
+-------------+
#数据库8
mysql -S /tmp/mysql3308.sock -e "select @@server_id";
+-------------+
| @@server_id |
+-------------+
|           8 |
+-------------+
#数据库9
mysql -S /tmp/mysql3309.sock -e "select @@server_id";
+-------------+
| @@server_id |
+-------------+
|           9 |
+-------------+

主库binlog

mysql -S /tmp/mysql3307.sock -e "select @@log_bin";
+-----------+
| @@log_bin |
+-----------+
|         1 |
+-----------+

主库建立复制用户

mysql -S /tmp/mysql3307.sock -e "grant replication slave on *.* to repl@'10.0.0.%' identified by '123'";
mysql -S /tmp/mysql3307.sock -e "select user,host from mysql.user"

主库备份恢复到从库

mysqldump -S /tmp/mysql3307.sock -A --master-data=2 --single-transaction >/tmp/all.sql
mysql -S /tmp/mysql3308.sock </tmp/all.sql
mysql -S /tmp/mysql3309.sock </tmp/all.sql

告知从库复制信息

help change master to 

CHANGE MASTER TO
  MASTER_HOST='10.0.0.51',
  MASTER_USER='repl',
  MASTER_PASSWORD='123',
  MASTER_PORT=3307,
  MASTER_LOG_FILE='mysql-bin.000002',
  MASTER_LOG_POS=444,
  MASTER_CONNECT_RETRY=10;

[root@db01 ~]# grep "-- CHANGE MASTER TO" /tmp/all.sql 
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=444;

[root@db01 ~]# mysql -S /tmp/mysql3308.sock
CHANGE MASTER TO
  MASTER_HOST='10.0.0.51',
  MASTER_USER='repl',
  MASTER_PASSWORD='123',
  MASTER_PORT=3307,
  MASTER_LOG_FILE='mysql-bin.000002',
  MASTER_LOG_POS=444,
  MASTER_CONNECT_RETRY=10;


[root@db01 ~]# mysql -S /tmp/mysql3309.sock
CHANGE MASTER TO
 MASTER_HOST='10.0.0.51',
 MASTER_USER='repl',
 MASTER_PASSWORD='123',
 MASTER_PORT=3307,
 MASTER_LOG_FILE='mysql-bin.000002',
 MASTER_LOG_POS=444,
 MASTER_CONNECT_RETRY=10;

在从库中开启专用复制线程

[root@db01 ~]# mysql -S /tmp/mysql3308.sock
start slave;

[root@db01 ~]# mysql -S /tmp/mysql3309.sock
start slave;

验证主从状态

[root@db01 ~]# mysql -S /tmp/mysql3308.sock -e "show slave statusG"|grep Running: 
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
[root@db01 ~]# mysql -S /tmp/mysql3309.sock -e "show slave statusG"|grep Running: 
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
[root@db01 ~]# 

注：如果搭建不成，可以执行以下命令，从3.1-3.8步骤重新来过。（没问题别整！！！！！）

[root@db01 ~]# mysql -S /tmp/mysql3308.sock -e "stop slave;reset slave all;"
[root@db01 ~]# mysql -S /tmp/mysql3309.sock -e "stop slave;reset slave all;"
#关闭在重启M.info里面的主库信息就没有了