Mysql数据库主从复制环境搭建

最新推荐文章于 2024-04-07 23:53:23 发布

荣书

最新推荐文章于 2024-04-07 23:53:23 发布

阅读量1k

点赞数 2

分类专栏：数据库学习文章标签： mysql 数据库服务器

本文链接：https://blog.csdn.net/u013029598/article/details/121776594

版权

数据库学习专栏收录该内容

2 篇文章 0 订阅

订阅专栏

Mysql主从同步搭建

一、搭建环境介绍

1.1、主机

主机名称	IP地址	备注
mysql1	192.168.70.10	主服务器
mysql2	192.168.70.11	从服务器

1.2、系统环境

# cat /etc/redhat-release 
CentOS Linux release 7.9.2009 (Core)

# uname -r
3.10.0-1160.el7.x86_64

#在安装Mysql前关闭系统防火墙或者配置放行3306端口的防火墙策略，本例关闭防火墙
# systemctl stop firewalld
# systemctl disable firewalld

二、Mysql安装及密码修改

2.1 安装mysql

本例使用mysql5.7版本的rpm包安装，安装包在官网下载即可,将下载的安装包上传到mysql1和mysql2主机中，安装如下：

# rpm -ivh --nodeps --force *.rpm
警告：mysql-community-client-5.7.35-1.el7.x86_64.rpm: 头V3 DSA/SHA256 Signature, 密钥 ID 5072e1f5: NOKEY
准备中...                          ################################# [100%]
正在升级/安装...
   1:mysql-community-client-5.7.35-1.e################################# [ 50%]
   2:mysql-community-server-5.7.35-1.e################################# [100%]

2.2 mysql安装后修改密码的操作

由于mysql5.7安全的限制安装启动后查询到的log中的密码可以登陆，但不能做任何操作，需安装后需修改数据库root用户的密码，操作如下：

#修改mysql配置文件,在文件中增加一行skip-grant-tables
# vi /etc/my.cnf
skip-grant-tables

启动mysql服务,并确认服务启动正常，命令如下：

启动mysql
# systemctl start mysqld	

查看启动状态
# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since 日 2021-11-21 14:32:43 CST; 53s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 1580 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 1529 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 1582 (mysqld)
   CGroup: /system.slice/mysqld.service
           └─1582 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

11月 21 14:32:28 mysql1 systemd[1]: Starting MySQL Server...
11月 21 14:32:43 mysql1 systemd[1]: Started MySQL Server.

登录mysql
# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, 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>

登录mysql修改密码，操作如下：

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
mysql> 
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> set password for root@localhost = password('Rong@$12345');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> exit
Bye

修改my.cnf注释或删除skip-grant-tables重启mysql，至此Mysql安装完毕。

# vi /etc/my.cnf
#skip-grant-tables

# systemctl restart mysqld

三、修改Mysql主从同步的配置

3.1、主服务器的配置

3.1.1、修改主服务器mysql配置文件添加与主从同步有关的配置

#编辑/etc/目录下的my.cnf文件在文件中添加log-bin的几行配置。
# cat /etc/my.cnf

#日志文件名称
log-bin=master-rs-bin
#二进制日志的格式有row、statement和mixed三种类型
binlog-format=Row
#要求各个服务器的这个id必须不一样
server-id=1
#同步的数据库名称
binlog-do-db=rongshu

3.1.2、配置任意主机登录授权和从服务器登录主服务器的账号授权

# mysql -uroot -p	--登录mysql

#配置数据库任何主机的可访问权限
mysql> grant all privileges on *.* to 'root'@'%' identified by 'Rong@$12345' with grant option;
Query OK, 0 rows affected, 1 warning (0.02 sec)

#配置从服务器登录主服务器的账号授权
mysql> grant replication slave on *.* to 'root'@'192.168.70.11' identified by 'Rong@$12345';
Query OK, 0 rows affected, 1 warning (0.00 sec)

#刷新授权
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

3.2、从服务器的配置

3.2.1、修改从服务器mysql配置文件添加与主从同步有关的配置

#编辑/etc/目录下的my.cnf文件在文件中添加log-bin的几行配置,注意server_id的配置
# vi /etc/my.cnf

#日志文件名称
log-bin=master-rs-bin
#二进制日志的格式，跟主服务器一样
binlog-format=Row
#要求各个服务器的这id必须不一样
server-id=2
#中继日志执行之后，这些变化是否需要计入自己的binarylog。当你的从服务器需要作为另外一个服务器的主服务器的时候需要打开。就是双主互相备份，或者多主循环备份。
log-slave-updates=true

四、重启Mysql服务并开启主从同步

4.1、重启主服务器mysql并查看master状态

# service mysqld restart		--重启Mysql				
Redirecting to /bin/systemctl restart mysqld.service
# mysql -uroot -p				--登录Mysql
mysql> show master status;		--查看Mysql Master状态，需要记录file和Position的值
+----------------------+----------+--------------+------------------+-------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| master-rs-bin.000001 |      154 | rongshu      |                  |                   |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

4.2、重启从服务器mysql并开启slave同步

# service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service

# mysql -uroot -p

#配置从服务器读取主服务器的配置
mysql> change master to master_host='192.168.70.10',master_user='root',master_password='Rong@$12345',master_port=3306,master_log_file='master-rs-bin.000001',master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

#开启从服务器同步
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

#查看从服务器同步要注意“Slave_IO_Running”、“ Slave_SQL_Running”应都为Yes
mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.70.10
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-rs-bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql2-relay-bin.000002
                Relay_Log_Pos: 324
        Relay_Master_Log_File: master-rs-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: 154
              Relay_Log_Space: 532
              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: 1
                  Master_UUID: d0389777-4a94-11ec-9492-000c296b9c6b
             Master_Info_File: /var/lib/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)

五、在主服务器操作数据库，测试主从同步

5.1、在主服务器创建数据库及插入数据

mysql> create database rongshu;			--创建数据库
Query OK, 1 row affected (0.00 sec)

mysql> use rongshu;		--切换到刚创建的数据库
Database changed
mysql> create table rong_tab (id int(10));	--创建一个字段为id长度10的表
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;		--查看创建的表
+-------------------+
| Tables_in_rongshu |
+-------------------+
| rong_tab          |
+-------------------+
1 row in set (0.00 sec)

mysql> select * from rong_tab;	--先确认表中无数据
Empty set (0.16 sec)

mysql> insert into rong_tab (id) values (1);	--插入一条值为1的记录
Query OK, 1 row affected (0.00 sec)

mysql> select * from rong_tab;		--确认记录存在
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> insert into rong_tab (id) values (2); 	--插入一条值为2的记录
Query OK, 1 row affected (0.00 sec)

5.2、在从服务器验证数据库已创建及数据已同步

mysql> show databases;		--确认rognshu的库已存在
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| rongshu            |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use rongshu;		--切换到"rognshu"的数据库
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_rongshu |
+-------------------+
| rong_tab          |
+-------------------+
1 row in set (0.00 sec)

mysql> select * from rong_tab;		--查询在主库创建的记录
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> select * from rong_tab;		--查询在主库创建的第二条记录
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)