0717-7821348
新闻中心

欢乐彩app安卓版

您现在的位置: 首页 > 新闻中心 > 欢乐彩app安卓版
欢乐彩手机版下载-CentOS环境下MySQL 5.7主从复制建立
2019-12-19 01:53:15

软件

  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-slave-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 mysqld 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 respective
owners.
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 -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> 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 | Conn欢乐彩手机版下载-CentOS环境下MySQL 5.7主从复制建立ect_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_Re欢乐彩手机版下载-CentOS环境下MySQL 5.7主从复制建立try_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 | | | |
+----------------+---------------+-------------+-------------+---------------+-------------------+---------------------+----------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+欢乐彩手机版下载-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>

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