软件
- 数据库: MySQL 5.7
- 系统:centos 7.6

文章插图
【CentOS环境下MySQL 5.7主从复制搭建】
主从复制架构

文章插图
MySQL主从复制
主从复制配置
- 我创建了两台MySQL服务器分别是Master(IP:172.16.20.199)、Slave(IP:172.16.20.26);
- 修改Master的MySQL配置:
[root@localhost ~]# vim /etc/my.cnf #将server-id修改为1 默认为1 #开启blog日志,在server-id上面新增 # binlog 配置server-id=1 #注意主从server-id不能一样log-slave-updates=true #一般用于联机复制中,log-bin=master-bin #指定binlog日志名[root@localhost ~]# service mysqld restartRedirecting to /bin/systemctl restart mysqld.service

文章插图
修改Master的ID号和开启blog日志
[root@localhost ~]# vim /etc/my.cnf #将server-id修改为2(默认为1) #开启blog日志,在server-id上面新增 # binlog 配置server-id=2 #server-id修改为2log-bin=mysql-bin #slave的二进制日志relay-log=relay-log-bin #中继日志文件名relay-log-index=slave-relay-bin.index #中继日志的索引文件名[root@localhost ~]# service mysqld restartRedirecting to /bin/systemctl restart mysqld.service

文章插图
修改Slave的ID号和开启blog日志
配置主数据库
[root@localhost ~]# mysql -u root -pEnter password: Welcome to the MySQL monitor. Commands end with ; or g.Your MySQL connection id is 2Server version: 5.7.27 Source distributionCopyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.mysql>
mysql> GRANT REPLICATION SLAVE ON *.* TO 'root'@'172.16.20.226' IDENTIFIED BY '111111';mysql> FLUSH PRIVILEGES;查询master的状态mysql> show master status;+-------------------+----------+--------------+------------------+-------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+-------------------+----------+--------------+------------------+-------------------+| master-bin.000001 | 154 | | | |+-------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)mysql> 修改从数据库配置
[root@localhost ~]# mysql -uroot -pEnter password: Welcome to the MySQL monitor. Commands end with ; or g.Your MySQL connection id is 2Server version: 5.7.27-log Source distributionCopyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.mysql>
CHANGE MASTER TOMASTER_HOST='172.16.20.199',MASTER_USER='root',MASTER_PASSWORD='111111',MASTER_LOG_FILE='master-bin.000001',MASTER_LOG_POS=154;#配置主节点mysql> CHANGE MASTER TO MASTER_HOST='172.16.20.199', MASTER_USER='root', MASTER_PASSWORD='111111', MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=154;Query OK, 0 rows affected, 2 warnings (0.02 sec)mysql> start slave;Query OK, 0 rows affected (0.04 sec)mysql> show slave status;+----------------+---------------+-------------+-------------+---------------+-------------------+---------------------+----------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+----------------+----------------+-----------------------------+------------------+-------------+-----------------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id | Master_UUID | Master_Info_File | SQL_Delay | SQL_Remaining_Delay | Slave_SQL_Running_State | Master_Retry_Count | Master_Bind | Last_IO_Error_Timestamp | Last_SQL_Error_Timestamp | Master_SSL_Crl | Master_SSL_Crlpath | Retrieved_Gtid_Set | Executed_Gtid_Set | Auto_Position | Replicate_Rewrite_DB | Channel_Name | Master_TLS_Version |+----------------+---------------+-------------+-------------+---------------+-------------------+---------------------+----------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+----------------+----------------+-----------------------------+------------------+-------------+-----------------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+| | 172.16.20.199 | root | 3306 | 60 | master-bin.000001 | 154 | relay-log-bin.000001 | 4 | master-bin.000001 | No | Yes | | | | | | | 0 | | 0 | 154 | 154 | None | | 0 | No | | | | | | NULL | No | 1593 | Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work. | 0 | | | 1 | | /usr/local/mysql/data/master.info | 0 | NULL | Slave has read all relay log; waiting for more updates | 86400 | | 191021 04:59:03 | | | | | | 0 | | | |+----------------+---------------+-------------+-------------+---------------+-------------------+---------------------+----------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+----------------+----------------+-----------------------------+------------------+-------------+-----------------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+1 row in set (0.00 sec)mysql>
推荐阅读
-
孝子|俗语为何说“久病床前无孝子”?其实还有后半句,让人深思
-
毕节交警|贵州毕节小客车追尾大客车起火燃烧 致7人遇难
-
搞笑小恶魔|搞笑GIF趣图:你以为你爸想找我这样的吗?
-
-
扒点时尚|戚薇真是好刚一女的,穿浴袍出席活动,也就她有这胆量了
-
-
事实证明,55岁久居日本的郑伊健,已经走上了另一条康庄大道
-
王者荣耀|世冠决赛为什么会同时有“两头猪”?懂了巅峰对决赛制后就明白了
-
Lisa和三公子游巴黎迪士尼,感情稳定好甜蜜,两保镖陪同阵仗大!
-
资讯早知道■可惜少了魏大勋,《密室大逃脱2》录制了!杨幂、邓伦强势回归
-
-
【美企图借疫情干涉俄内政】美企图借疫情干涉俄内政 俄外长提了这些观点
-
【育儿小手册】白嫖月卡真的现实吗?,魔兽世界怀旧服中
-
-
一片叶子阿梅用来养花用处多,养护神器:塑料瓶千万别扔
-
-
-
如果人的生命变成无限,你会在啥情况下欣然走向死亡还是永远活下去如果选择死亡,愿意以啥方式死亡
-
央视新闻|巴西逾25万医护人员确诊新冠肺炎 平均每分钟就有1人被感染
-
ai应该咋地才可以填色,ai外观描边填色怎么移到顶层