mysql主从拷贝原理

        环境：
 
       3306 主库
 
       3307 从库
 
1 设置server-id值并开启binlog功能参数
 
      编辑mysql的配置文件/data/3306/my.cnf
 
      [mysqld]
 
      server-id = 6              ----用于同步的每台机器或实例sever-id都不能相同
 
log_bin =/data/3306/mysql-bin  ----可以省略
 
重启服务
 
/data/3306/mysql restart
 
检查思路一
 
[root@db02 data]# egrep "log_bin|server-id" 330*/my.cnf
 
3306/my.cnf:log_bin = /data/3306/mysql-bin
 
3306/my.cnf:server-id = 6
 
3307/my.cnf:server-id = 7
 
检查思路二：
 
检查思路2：
 
2 建账号授权【主库】
 
grant replication slave on *.* to 'rep'@'172.16.1.%' identified by 'oldboy123';
 
flush privileges;
 
3锁表导出数据
 
mysql> flush table with read lock;
 
Query OK, 0 rows affected (0.00 sec)
 
查看位置：
 
mysql> show master status;
 
+------------------+----------+--------------+------------------+-------------------+
 
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
 
+------------------+----------+--------------+------------------+-------------------+
 
| mysql-bin.000001 |      405 |              |                  |                   |
 
+------------------+----------+--------------+------------------+-------------------+
 
1 row in set (0.00 sec)
 
1 row in set (0.00 sec)
 
mysql> unlock tables;
 
Query OK, 0 rows affected (0.01 sec)
 
如下命令可替代3的所有步骤
 
mysqldump -uroot -p'oldboy123' --master-data=2 -S /data/3306/mysql.sock -A -B
 
4、将数据导入到从库
 
[root@db02 backup]# mysql -S /data/3307/mysql.sock <mysql_bak.2017-05-04.sql
 
5、让从库从主库锁表时刻记录的binlog位置点开始向下同步
 
CHANGE MASTER TO  
 
MASTER_LOG_FILE='mysql-bin.000001',
 
MASTER_LOG_POS=405;