mysql主从复制教程

最新推荐文章于 2023-06-27 10:43:08 发布

快乐呀小银

最新推荐文章于 2023-06-27 10:43:08 发布

阅读量1.1k

点赞数

文章标签： mysql

本文链接：https://blog.csdn.net/qq_37212755/article/details/108691073

版权

首先，对新建的虚拟机需要进行修改hostname（个人习惯），以及修改静态IP（有必要）。

1.修改静态IP（主服务器和从服务器的区别在于IPADDR）

#centos7的网络IP地址配置文件在  /etc/sysconfig/network-scripts 文件夹下
cd /etc/sysconfig/network-scripts
#ens33网卡对应的配置文件为ifcfg-ens33，使用vim编辑如下:
vim ifcfg-ens33
TYPE="Ethernet"
PROXY_METHOD="none"
BROWSER_ONLY="no"
BOOTPROTO="static"
DEFROUTE="yes"
IPV4_FAILURE_FATAL="no"
IPV6INIT="yes"
IPV6_AUTOCONF="yes"
IPV6_DEFROUTE="yes"
IPV6_FAILURE_FATAL="no"
IPV6_ADDR_GEN_MODE="stable-privacy"
NAME="ens33"
UUID="e9a25968-9dda-4324-915a-3066ca90018a"
DEVICE="ens33"
ONBOOT="yes"
主机一：IPADDR="192.168.42.131"    # 设置的静态IP地址   
主机二：IPADDR="192.168.42.132"
NETMASK="255.255.255.0"    # 子网掩码
GATEWAY="192.168.42.2"    # 网关地址
DNS1="192.168.42.2"       # DNS服务器
#然后使用service network restart命令即可配置静态IP地址
service network restart

2.修改hostname

主机一：hostnamectl set-hostname name1
[root@localhost network-scripts]# hostnamectl set-hostname name1
[root@localhost network-scripts]# hostname
name1
#地址映射
vim /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.42.131 name1
主机二：[root@localhost network-scripts]# hostnamectl set-hostname name2
[root@localhost network-scripts]# hostname
name2
#地址映射
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.42.132 name2

现在开始进行mysql主从复制我们需要了解以下问题。

一、什么是主从复制?

主从复制，是用来建立一个和主数据库完全一样的数据库环境，称为从数据库；主数据库一般是准实时的业务数据库。

二、主从复制的作用？

1、做数据的热备，作为后备数据库，主数据库服务器故障后，可切换到从数据库继续工作，避免数据丢失。
2、架构的扩展。业务量越来越大，I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O性能。
3、读写分离，使数据库能支撑更大的并发。在报表中尤其重要。由于部分报表sql语句非常的慢，导致锁表，影响前台服务。如果前台使用master，报表使用slave，那么报表sql将不会造成前台锁，保证了前台速度。

三、主从复制的原理

1.数据库有个bin-log二进制文件，记录了所有sql语句。
2.我们的目标就是把主数据库的bin-log文件的sql语句复制过来。
3.让其在从数据的relay-log重做日志文件中再执行一次这些sql语句即可。
4.下面的主从配置就是围绕这个原理配置
5.具体需要三个线程来操作：
1.binlog输出线程:每当有从库连接到主库的时候，主库都会创建一个线程然后发送binlog内容到从库。在从库里，当复制开始的时候，从库就会创建两个线程进行处理：
2.从库I/O线程:当START SLAVE语句在从库开始执行之后，从库创建一个I/O线程，该线程连接到主库并请求主库发送binlog里面的更新记录到从库上。从库I/O线程读取主库的binlog输出线程发送的更新并拷贝这些更新到本地文件，其中包括relay log文件。

3.从库的SQL线程:从库创建一个SQL线程，这个线程读取从库I/O线程写到relay log的更新事件并执行。

可以知道，对于每一个主从复制的连接，都有三个线程。拥有多个从库的主库为每一个连接到主库的从库创建一个binlog输出线程，每一个从库都有它自己的I/O线程和SQL线程。

四、主从复制的步骤

步骤一：主库db的更新事件(update、insert、delete)被写到binlog
步骤二：从库发起连接，连接到主库
步骤三：此时主库创建一个binlog dump thread线程，把binlog的内容发送到从库
步骤四：从库启动之后，创建一个I/O线程，读取主库传过来的binlog内容并写入到relay log.
步骤五：还会创建一个SQL线程，从relay log里面读取内容，从Exec_Master_Log_Pos位置开始执行读取到的更新事件，将更新内容写入到slave的db.

1.环境信息

操作系统：Centos7
#mysql版本
mysql-community-client-5.7.31-1.el7.x86_64.rpm
mysql-community-server-5.7.31-1.el7.x86_64.rpm
#下载地址
http://dev.mysql.com/downloads/mysql/

#主机信息
192.168.42.129（主）
192.168.42.130（从）

2.安装MySQL数据库

#查看是否装过Mysql(安装过是会报错的)
rpm -qa | grep -i mysql
find / -name mysql 2>/dev/null（过滤掉无权限文件）
[root@cluster1 home]# rpm -qa | grep -i mysql
[root@cluster1 home]# find / -name mysql 2>/dev/null（2>/dev/null的意思是“过滤掉没有查看权限的文件”）
/etc/selinux/targeted/active/modules/100/mysql
/usr/lib64/mysql
/usr/share/mysql
#发现有相关mysql文件，则删除
[root@cluster1 home]# rm -rf /etc/selinux/targeted/active/modules/100/mysql
[root@cluster1 home]# rm -rf /usr/lib64/mysql
[root@cluster1 home]# rm -rf /usr/share/mysql
#再次查找进行确认
[root@cluster1 home]# find / -name mysql 2>/dev/null
#安装server
[root@cluster1 home]# rpm -ivh mysql-community-server-5.7.31-1.el7.x86_64.rpm --force --nodeps
warning: mysql-community-server-5.7.31-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql-community-server-5.7.31-1.e################################# [100%]
#安装client
[root@cluster1 home]# rpm -ivh mysql-community-client-5.7.31-1.el7.x86_64.rpm --force --nodeps
warning: mysql-community-client-5.7.31-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql-community-client-5.7.31-1.e################################# [100%]

3.登录mysql

#登录mysql
[root@cluster1 home]# mysql -u root -p
Enter password: 
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
#修改免密登录
vi /etc/my.cnf
#登录不成功设置成跳过MySQL的授权
[mysqld]
skip-grant-tables
#重启mysql的服务
service mysqld restart
[root@cluster1 home]# service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service
#再次登录
mysql -u root -p
#查看所有数据库
show databases;
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)
#修改mysql登录密码
#刷新系统权限相关表
flush PRIVILEGES;
#设置新的密码
alter user 'root'@'localhost' identified by 'root';
#刷新系统权限相关表
flush PRIVILEGES;
如下：
mysql> flush PRIVILEGES;
Query OK, 0 rows affected (0.02 sec)
mysql> alter user 'root'@'localhost' identified by 'root';
Query OK, 0 rows affected (0.00 sec)
mysql> flush PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
#修改文件，使用密码登录
注释掉vi /etc/my.cnf中的skip-grant-tables如下：
[mysqld]
#skip-grant-tables
#再次登录mysql，输入密码root,如下：
[root@cluster1 home]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.31 MySQL Community Server (GPL)
Copyright (c) 2000, 2020, 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.

4.配置主从复制同步（先配置主服务器）

#在[mysqld]中添加：
log_bin=master-a-bin   #启动mysql二进制文件，即数据同步语句，从数据库会一条一条的执行这些语句。   
binlog-format=ROW
server-id=1   #服务器唯一标识
binlog_do_db=test   #指定记录二进制日志的数据库，即需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可。
#binlog_ignore_db    #指定不记录二进制日志的数据库，即不需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可。
#其中需要注意的是，binlog_do_db和binlog_ignore_db为互斥选项，一般只需要一个即可。

日志文件格式说明：https://www.cnblogs.com/longren/p/12205924.html

5.配置从服务器登录主服务器的账号授权

目的：是让从服务器能够发现主服务器，步骤是在主服务器中实现的

#进入mysql数据库
[root@cluster1 home]# mysql -u root -p
Enter password: 

#创建从数据库的masterbackup用户和权限
mysql>grant replication slave on *.* to 'root'@'192.168.42.129' identified by 'root';
#备注
#192.168.42.%通配符，表示0-255的IP都可以访问主服务器，正式环境请配置制定从服务器IP
#若将192.168.42.%改为%，则任何IP均可作为其从数据库来访问主服务器
#刷新
mysql> flush PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
#退出，配置从服务器登录主服务器的账号授权完成
mysql> exit;
Bye

6.配置从服务器（vi /etc/my.cnf）

#日志文件名称
log_bin=master-a-bin
#二进制日志的格式，跟主服务器一样
binlog-format=ROW
#各个服务器的id，这个必须是唯一的，和主服务器不一样
server-id=2
#双主互相备份,“从服务器可以作为主机存在，将数据传给其他服务器”
#log-slave-updates=true

7.重启主服务器

#重启mysql的服务
[root@cluster1 home]#service mysqld restart

[root@cluster1 home]# mysql -u root -p
#查看主服务器的状态
[root@cluster1 home]# service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service
[root@cluster1 home]# 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.31-log MySQL Community Server (GPL)
Copyright (c) 2000, 2020, 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> show master status;
mysql> show master status;
+---------------------+----------+--------------+------------------+----------------+
| File                | Position | Binlog_Do_DB |Binlog_Ignore_DB |Executed_Gtid_Set|
+---------------------+----------+--------------+------------------+----------------+
| master-a-bin.000001 |      154 | test         |                  |                |
+---------------------+----------+--------------+------------------+----------------+
1 row in set (0.00 sec)
mysql>

8.从服务器配置

#重启mysql服务器
service mysqld restart
[root@cluster1 home]# service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service
#登录mysql
[root@cluster1 home]# mysql -u root -p
Enter password:
#设置主服务器的日志和偏移量
change master to master_host='192.168.42.129',
                 master_port=3306,
                 master_user='root',
                 master_password='root',
                 master_log_file='master-a-bin.000001',
                 master_log_pos=154;
#master_host  主服务器主机
#master_port  主服务器端口

#启动slave数据同步
mysql>start slave;
#停止slave数据同步(若有需要)
mysql>stop slave;
#查看slave信息
show slave status\G;
mysql>show slave status\G;
操作如下所示：
mysql> change master to master_host='192.168.42.129',
    ->                  master_port=3306,
    ->                  master_user='root',
    ->                  master_password='root',
    ->                  master_log_file='master-a-bin.000001',
    ->                  master_log_pos=154;
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: Connecting to master
                  Master_Host: 192.168.42.129
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-a-bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: cluster1-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: master-a-bin.000001
             Slave_IO_Running: Connecting
            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: 154
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 2003
                Last_IO_Error: error connecting to master 'root@192.168.42.129:3306' - retry-time: 60  retries: 1
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 0
                  Master_UUID: 
             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: 200724 14:07:51
     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> 
#（看情况是否符合要设置这个）
###出现正在连接而且客户端没法连接的情况
1.防火墙的原因
2.服务器中并没有给外界提供3306的开放端口
#设施3306为开放端口
firewall-cmd --zone=public --add-port=3306/tcp --permanent
#重新加载防火墙
firewall-cmd --reload
#如下所示：
[root@cluster1 home]# firewall-cmd --zone=public --add-port=3306/tcp --permanent
success
[root@cluster1 home]# firewall-cmd --reload
success
[root@cluster1 home]# 
###发现还是出现如下问题：

#在主服务器中配置以下信息
#设置远程访问账号，若最后加上with grant option，则同时可以赋予权限的
[root@cluster1 home]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.7.31-log MySQL Community Server (GPL)
Copyright (c) 2000, 2020, 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> grant all privileges on *.* to root@'%' identified by 'root' with grant option;
ERROR 1819 (HY000): Unknown error 1819
mysql>
#出现问题解决方案
mysql> select @@validate_password_policy;
+----------------------------+
| @@validate_password_policy |
+----------------------------+
| MEDIUM                     |
+----------------------------+
1 row in set (0.00 sec)

mysql> show variables like 'validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_check_user_name    | OFF    |
| validate_password_dictionary_file    |        |
| validate_password_length             | 8      |
| validate_password_mixed_case_count   | 1      |
| validate_password_number_count       | 1      |
| validate_password_policy             | MEDIUM |
| validate_password_special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set (0.03 sec)
mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.01 sec)
mysql> set global validate_password_mixed_case_count=0;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_number_count=3;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_special_char_count=0;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_length=3;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'validate_password%';
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| validate_password_check_user_name    | OFF   |
| validate_password_dictionary_file    |       |
| validate_password_length             | 3     |
| validate_password_mixed_case_count   | 0     |
| validate_password_number_count       | 3     |
| validate_password_policy             | LOW   |
| validate_password_special_char_count | 0     |
+--------------------------------------+-------+
7 rows in set (0.00 sec)
mysql>  
#错误解决完成，重新进行修改密码
#alter user 'root'@'localhost' identified with mysql_native_password by 'root';
mysql> alter user 'root'@'localhost' identified with mysql_native_password by 'root';
#重新授权
mysql> grant all privileges on *.* to root@'%' identified by 'root' with grant option;
#重新刷新：flush privileges;
mysql> flush privileges;
再连接发现连接成功！

在主服务器test数据库中新建user表，如下；

在主服务器中查询：

说明user表建立成功，再到从服务器查看，看是否有user表？

说明mysql主从复制配置成功！！！

快乐呀小银

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
mysql主从复制教程

首先，对新建的虚拟机需要进行修改hostname（个人习惯），以及修改静态IP（有必要）。1.修改静态IP（主服务器和从服务器的区别在于IPADDR）#centos7的网络IP地址配置文件在 /etc/sysconfig/network-scripts 文件夹下cd /etc/sysconfig/network-scripts#ens33网卡对应的配置文件为ifcfg-ens33，使用vim编辑如下:vim ifcfg-ens33TYPE="Ethernet"PROXY_METHOD="no
复制链接

扫一扫