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：

登录：

[root@db02 data]# mysql -S /data/3306/mysql.sock

查看变量：

mysql> show variables like 'log_bin%';

+---------------------------------+----------------------------+

| Variable_name          | Value            |

+---------------------------------+----------------------------+

| log_bin             | ON             |

| log_bin_basename         | /data/3306/mysql-bin    |

| log_bin_index          | /data/3306/mysql-bin.index |

| log_bin_trust_function_creators | OFF             |

| log_bin_use_v1_row_events    | OFF             |

+---------------------------------+----------------------------+

5 rows in set (0.00 sec)

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)

新开窗口备份：

mysqldump -uroot -p'oldboy123' -S /data/3306/mysql.sock -A -B |gzip >/server/backup/mysql_bak.$(date +%F).sql.gz

原窗口解锁：

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)

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

5、让从库从主库锁表时刻记录的binlog位置点开始向下同步

CHANGE MASTER TO

MASTER_HOST='172.16.1.52',

MASTER_PORT=3306,

MASTER_USER='rep',

MASTER_PASSWORD='oldboy123',

MASTER_LOG_FILE='mysql-bin.000001',

MASTER_LOG_POS=405;

另外有需要云服务器可以了解下创新互联cdcxhl.cn，海内外云服务器15元起步，三天无理由+7*72小时售后在线，公司持有idc许可证，提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案，具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势，专为企业上云打造定制，能够满足用户丰富、多元化的应用场景需求。