Linux-MySQL5.7-主从复制架构

实验环境

机器

centos7       地址10.0.0.7      主机名master.org

centso7  地址10.0.0.77    主机名slave.org

已安装MySQL5.7数据库

主从配置

修改主机名区分主次

[root@centos7-liyj ~]#hostnamectl set-hostname master.org [root@centos7-liyj ~]#exit logout  Connection closed.  Disconnected from remote host(10.0.0.7) at 15:18:26.  Type `help' to learn how to use Xshell prompt. [C:\~]$  Reconnecting in 1 seconds. Press any key to exit local shell. .  Connecting to 10.0.0.7:22... Connection established. To escape to local shell, press 'Ctrl+Alt+]'.  Last login: Sat May 14 15:08:21 2022 from 10.0.0.1 [root@master ~]#

从节点

[root@centos7-liyj ~]#hostnamectl set-hostname slave.org [root@centos7-liyj ~]#exit logout  Connection closed.  Disconnected from remote host(10.0.0.77) at 15:19:40.  Type `help' to learn how to use Xshell prompt. [C:\~]$  Reconnecting in 3 seconds. Press any key to exit local shell. ...  Connecting to 10.0.0.77:22... Connection established. To escape tzhujieo local shell, press 'Ctrl+Alt+]'.  Last login: Sat May 14 15:11:21 2022 from 10.0.0.1 [root@slave ~]#

修改master主节点的配置

配置文件修改

server-id=7                          #添加节点全局唯一编号ID,一般为IP地址的最后的地址数 10.0.0.7,防止出现重复ID编号
log-bin=/data/mysql/logbin/mysql-bin #添加二进制日志路径

 

[root@master ~]#vim /etc/my.cnf [mysqld] datadir=/data/mysql skip_name_resolve=1 socket=/data/mysql/mysql.sock log-error=/data/mysql/mysql.log pid-file=/data/mysql/mysql.pid  server-id=7                               log-bin=/data/mysql/logbin/mysql-bin       [client] socket=/data/mysql/mysql.sock

创建日志路径

[root@master ~]#mkdir -p /data/mysql/logbin [root@master /data/mysql]#chown -R mysql.mysql /data/mysql/     #修改文件夹权限,属组属主都是mysql,否则重启数据库服务报错

不修改文件权限报错

[root@master ~]#systemctl restart mysqld Job for mysqld.service failed because the control process exited with error code. See systemctl status mysqld.service and journalctl -xe for details.

重启数据库服务

[root@master ~]#systemctl restart mysqld

登录数据库

[root@master ~]#mysql -uroot -p Enter password:  Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.7.37 Source distribution  Copyright (c) 2000, 2022, Oracle and/or its affiliates.  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> show databases; +--------------------+ | Database           | +--------------------+ | information_schema | | mysql              | | performance_schema | | sys                | +--------------------+ 4 rows in set (0.02 sec)  mysql> 

创建复制用户并授权

mysql> create user  [email protected].% identified by 123456   创建用户并给予密码     -> ; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id:    2 Current database: *** NONE ***  Query OK, 0 rows affected (0.00 sec)  mysql> grant replication slave on *.* to [email protected].%;      授权用户 Query OK, 0 rows affected (0.00 sec)

修改lave从节点的配置

修改配置文件

[root@slave ~]#vim /etc/my.cnf [root@slave ~]#cat /etc/my.cnf [mysqld] datadir=/data/mysql skip_name_resolve=1 socket=/data/mysql/mysql.sock         log-error=/data/mysql/mysql.log pid-file=/data/mysql/mysql.pid  server-id =77                        #全局唯一ID编号 read-only                 # 只读,不可写 [client] socket=/data/mysql/mysql.sock [root@slave ~]# systemctl restart mysqld  #重启 [root@slave ~]#

登录数据 

使用有复制权限的用户账号连接至主服务器,并启动复制线程

mysql> CHANGE MASTER TO
-> MASTER_HOST='10.0.0.7',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='123456',
-> MASTER_PORT=3306;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

从节点开始复制

mysql> start slave; Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G; *************************** 1. row ***************************                Slave_IO_State: Waiting for master to send event                   Master_Host: 10.0.0.7                   Master_User: repluser                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File: mysql-bin.000001           Read_Master_Log_Pos: 1245                Relay_Log_File: slave-relay-bin.000002                 Relay_Log_Pos: 1458         Relay_Master_Log_File: mysql-bin.000001              Slave_IO_Running: Yes             Slave_SQL_Running: Yes               Replicate_Do_DB:            Replicate_Ignore_DB:             Replicate_Do_Table:         Replicate_Ignore_Table:        Replicate_Wild_Do_Table:    Replicate_Wild_Ignore_Table:                     Last_Errno: 0                    Last_Error:                   Skip_Counter: 0           Exec_Master_Log_Pos: 1245               Relay_Log_Space: 1665               Until_Condition: None                Until_Log_File:                  Until_Log_Pos: 0            Master_SSL_Allowed: No            Master_SSL_CA_File:             Master_SSL_CA_Path:                Master_SSL_Cert:              Master_SSL_Cipher:                 Master_SSL_Key:          Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No                 Last_IO_Errno: 0                 Last_IO_Error:                 Last_SQL_Errno: 0                Last_SQL_Error:    Replicate_Ignore_Server_Ids:               Master_Server_Id: 7                   Master_UUID: 6eba5dd5-d036-11ec-80f9-0050563aaca0              Master_Info_File: /data/mysql/master.info                     SQL_Delay: 0           SQL_Remaining_Delay: NULL       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates            Master_Retry_Count: 86400                   Master_Bind:        Last_IO_Error_Timestamp:       Last_SQL_Error_Timestamp:                 Master_SSL_Crl:             Master_SSL_Crlpath:             Retrieved_Gtid_Set:              Executed_Gtid_Set:                  Auto_Position: 0          Replicate_Rewrite_DB:                   Channel_Name:             Master_TLS_Version:  1 row in set (0.00 sec)  ERROR:  No query specified
从节点状态

测试主节点创建数据库,从节点查看是否同步

mysql> show databases; +--------------------+ | Database           | +--------------------+ | information_schema | | logbin             | | mysql              | | performance_schema | | sys                | +--------------------+ 5 rows in set (0.00 sec)  mysql> create database mydb1; Query OK, 1 row affected (0.00 sec)  mysql> create database mydb2; Query OK, 1 row affected (0.00 sec)

mysql> show databases; +--------------------+ | Database           | +--------------------+ | information_schema | | mydb1              | | mydb2              | | mysql              | | performance_schema | | sys                | +--------------------+ 6 rows in set (0.00 sec)
mysql> use mydb1; Database changed mysql> CREATE TABLE student (     -> id int UNSIGNED AUTO_INCREMENT PRIMARY KEY,     -> name VARCHAR(20) NOT NULL,     -> age tinyint UNSIGNED,     -> gender ENUM('M','F') default 'M'     -> )ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.01 sec)  mysql> desc student; +--------+---------------------+------+-----+---------+----------------+ | Field  | Type                | Null | Key | Default | Extra          | +--------+---------------------+------+-----+---------+----------------+ | id     | int(10) unsigned    | NO   | PRI | NULL    | auto_increment | | name   | varchar(20)         | NO   |     | NULL    |                | | age    | tinyint(3) unsigned | YES  |     | NULL    |                | | gender | enum('M','F')       | YES  |     | M       |                | +--------+---------------------+------+-----+---------+----------------+ 4 rows in set (0.03 sec)
主创建表student
mysql> show databases; +--------------------+ | Database           | +--------------------+ | information_schema | | mydb1              | | mydb2              | | mysql              | | performance_schema | | sys                | +--------------------+ 6 rows in set (0.00 sec)  mysql> use mydb1; 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> desc student; +--------+---------------------+------+-----+---------+----------------+ | Field  | Type                | Null | Key | Default | Extra          | +--------+---------------------+------+-----+---------+----------------+ | id     | int(10) unsigned    | NO   | PRI | NULL    | auto_increment | | name   | varchar(20)         | NO   |     | NULL    |                | | age    | tinyint(3) unsigned | YES  |     | NULL    |                | | gender | enum('M','F')       | YES  |     | M       |                | +--------+---------------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)  mysql> 
从-查看是否同步