关闭
当前位置:首页 - 最新微博大事件 - 正文

眩晕,CentOS环境下MySQL 5.7主从复制建立-学生时代老师的经典语录

admin 2019-12-19 187°c

软件

  1. 数据库: MySQL 5.7
  2. 体系:CentOS 7.6

主从仿制架构

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-s送你一颗子弹lave-updates=true #一般用于联机仿制中,
log-bin=master-bin #指定binlog日志名
[root@localhost ~]# service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service

修正Master的ID号和敞开blog日志

  • 修正Slave的MySQL配汨罗气候置:
 [root@localhost ~]# vim /etc/my.cnf
#将server-id修正为2(默以为1)
#敞开blog日志,在server-id上面新增
# binlog 装备
server-id=2 #server-id修正为2
log-bin=mysql-bin #slave的二进制日志
relay-log=relay-log-bin #中继日志文件名
relay-log-index=slave-relay-bin.index #中继日志的索引文件名
[root@localhost ~]# service viewmysqld restart
Redirecting to /bin/systemctl restart mysqld.service

修正Slave的ID号和敞开blog日志

装备主数据库

  • 登录主数据库
[root@localhost ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.27 Source distribution
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights 石川纱彩reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their 中心5套节目表respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current inpu晕厥,CentOS环境下MySQL 5.7主从仿制树立-学生时代教师的经典语录t 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 -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.27-log Source distribution
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
  • 修正master信息
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;
#装备主节点
mysql> C复星医药HANGE 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_Runnin向美好动身g | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Er晕厥,CentOS环境下MySQL 5.7主从仿制树立-学生时代教师的经典语录rno | 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_Rboxewrite_DB | Channel_Nam晕厥,CentOS环境下MySQL 5.7主从仿制树立-学生时代教师的经典语录e | Master_TLS_Version |
+----------------+----晕厥,CentOS环境下MySQL 5.7主从仿制树立-学生时代教师的经典语录-----------+-------------+-------------+---------------+-------------------+---------------------+----------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-快眼看书主页----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+----------------+----------------+-----吴镇宇儿子眼睛受伤------------------------+------------------+-------------+-----------------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+
| | 172.16.20.199 | root | 3306 | 60 | master-bin.000001 | 154 | relay-log-bin.000001 | 4 | master-bin.000001 | No |晕厥,CentOS环境下MySQL 5.7主从仿制树立-学生时代教师的经典语录 Yes | | | | | | | 0 | | 0 | 154 |晕厥,CentOS环境下MySQL 5.7主从仿制树立-学生时代教师的经典语录 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 | | | |
+----------------+---------------+-------------+-------------+---------------+-------------------+---------------------+----------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-------------------晕厥,CentOS环境下MySQL 5.7主从仿制树立-学生时代教师的经典语录----------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+----------------+----------------+-----------------------------+------------------+-------------+-----------------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+
1 row in set (0.00 sec)
mysql>

找到字段Slave_IO_Running和Slave_SQL_Running,假如Slave_IO_Running=找到字段Slave_IO_Running和Slave_SQL_Running,假如Slave_IO_Running=No

Slave_SQL_Running=Yes

当时Slave_IO_Running:值为No,向下检查错误信息:

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.

修正auto.cnf中的UUID值,随意更改一个值即可。

[root@localhost ~]# vim /usr/local/mysql/data/auto.cnf
server-uuid=修正一下
[root@localhost ~]# systemctl restart mysqld
再次登录MySQL检查从节点信息
Slave_SQL_Running 和Slave_IO_Running值是否为Yes状况,表明装备成功

验证从全音符是否同步数据

  • 在主服务器上新建数据库test
mysql> create database test;
Query OK, 1 row affected (0.01 sec)
mysql> use test;
Database changed
  • 创立test表,并刺进数据
mysql> mysql> CREATE TABLE test
-> (
-> id int,
-> name varchar(255)
-> )
-> ;
Query OK, 0 rows affected (0.06 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------我在洪荒有个群------+
| test |
+----------------+
1 row in set (0.00 sec)
  • 在slave上检查是否更新数据
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.03 sec)
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test |
+----------------+
1 row in set (0.00 sec)
mysql>

祝贺主从仿制装备成功!!!

标签: 未定义标签
admin 14文章 0评论 主页

  用户登录