【图文教程】MySQL 主从

程序员文章站 2022-03-10 16:08:55

MySQL 主从MySQL主从介绍1. 配置MySQL（master）2. 配置MySQL（slave）3. 测试主从同步4. 分别在 sc01 和 sc02 机器上进行主从测试MySQL主从介绍MySQL 主从又叫做 Replication、AB 复制。简单讲就是A和B两台机器做主从后，在 A 上写数据，另外一台B也会跟着写数据，两者数据实时同步的，MySQL 主从基于 binlog，主上须开启 binlog 才能进行主从。MySQL 主从过程大致有3个步骤：1）主将更改操作记录到 bin...

MySQL 主从

MySQL主从介绍

MySQL主从介绍

主从复制原理图

【图文教程】MySQL 主从

MySQL 主从又叫做 Replication、AB 复制。简单讲就是A和B两台机器做主从后，在 A 上写数据，另外一台B也会跟着写数据，两者数据实时同步的，MySQL 主从基于 binlog，主上须开启 binlog 才能进行主从。

MySQL 主从过程大致有3个步骤：
1）主将更改操作记录到 binlog 里。
2）从将主的 binlog 事件(sql语句)同步到从本机上并记录在 relaylog 里。
3）从根据 relaylog 里面的 sql 语句按顺序执行。

MySQL 主从有 3 个线程：
主上有一个 log dump 线程，用来和从的 I/O 线程传递 binlog
从上有两个线程，其中 I/O 线程用来同步主的 binlog 并生成 relaylog，另外一个 SQL 线程用来把 relaylog 里面的 sql 语句落地

mysql 主从使用场景：
1）数据备份，主机器宕机，从机器还能随时对 web 提供服务
2）作为一个从库，读的库，减轻主库的压力，数据备份且可以分担主机器被调用数据时的压力，mysql 主从，是有方向性的，写数据，必须从主机器开始；如果不依照原理会导致数据紊乱

两台服务器做 mysql AB复制
master 192.168.72.130
slave 192.168.72.131

1. 配置 master

【图文教程】MySQL 主从

编辑 master 配置文件

[root@sc01 ~]# vim /etc/my.cnf     #打开配置文件

[mysqld]
datadir=/data/mysql
socket=/tmp/mysql.sock
server-id=130                      #需要添加，id任意数字
log_bin=sc-01                      #创建数据库账号

重启 mysql 服务；查看数据目录有 sc-01.000001和 sc.index 两个文件

[root@sc01 ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS!

[root@sc01]# ls -l /data/mysql/
total 110740
-rw-rw----. 1 mysql mysql       56 May 13 11:05 auto.cnf
-rw-rw----. 1 mysql mysql 50331648 May 29 10:50 ib_logfile0
-rw-rw----. 1 mysql mysql 50331648 May 13 10:54 ib_logfile1
-rw-rw----. 1 mysql mysql 12582912 May 29 10:50 ibdata1
drwx------. 2 mysql mysql     4096 May 13 10:54 mysql
drwx------. 2 mysql mysql     4096 May 21 18:17 mysql2
drwx------. 2 mysql mysql     4096 May 13 10:54 performance_schema
-rw-rw----. 1 mysql mysql      120 May 29 10:50 sc-01.000001            #生成的新文件
-rw-rw----. 1 mysql mysql       15 May 29 10:50 sc-01.index             #生成的新文件
-rw-rw----. 1 mysql mysql   103846 May 28 11:26 sc.err
-rw-rw----. 1 mysql mysql    11972 May 29 10:50 sc01.err
-rw-rw----. 1 mysql mysql        5 May 29 10:50 sc01.pid
drwx------. 2 mysql mysql        6 May 13 10:54 test
drwx------. 2 mysql mysql     4096 May 27 23:57 zrlog
[root@sc01 mysql]#

先把 zrlog 数据库做备份，并创建 sc01 新的数据库

[root@sc01 mysql]# mysqldump -uroot -p123 zrlog > /tmp/zrlog.sql           #先备份
Warning: Using a password on the command line interface can be insecure.


[root@sc01 mysql]# mysql -uroot -p123 -e "create database sc01"            #创建 sc01 数据库
Warning: Using a password on the command line interface can be insecure.


[root@sc01 mysql]# mysql -uroot -p123 sc01 < /tmp/zrlog.sql                #再把备份的写入到 sc01
Warning: Using a password on the command line interface can be insecure.

进入 mysql 创建用作同步数据的用户，并且先锁定数据库以防止写入数据

mysql> grant replication slave on *.* to 'repl'@'192.168.85.131' identified by '123';     #创建用作同步数据的用户
Query OK, 0 rows affected (0.00 sec)

mysql> flush tables with read lock;      #先锁定数据库以防止写入数据
Query OK, 0 rows affected (0.01 sec)



grant 权限 on 数据库对象 to 用户名词解释：
replication slave：从复制权限
*.*：数据库.表
repl：主从复制的用户
IP：slave的IP

查看 master 状态

mysql> show master status;
+--------------+----------+--------------+------------------+-------------------+
| File         | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------+----------+--------------+------------------+-------------------+
| sc-01.000001 |    10471 |              |                  |                   |
+--------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

2. 配置 slave

【图文教程】MySQL 主从

编辑 slave 配置文件

[root@sc02 ~]# vim /etc/my.cnf        

[mysqld]
datadir=/data/mysql
socket=/tmp/mysql.sock
server-id=136           #随便写个数字，但是要注意，不能跟master的id一样

重启 mysql 服务；并利用 scp 命令，复制 master 上备份文件到 slave 上 /tmp 下

[root@sc02 ~]# scp 192.168.85.130:/tmp/*.sql /tmp/
root@192.168.85.130's password: 
my2.sql                                                                                                                                  100%  632KB  13.8MB/s   00:00    
zrlog.sql                                                                                                                                100% 9879    12.0MB/s   00:00

创建与 master 上相同的数据库，并把 /tmp/ 下相应的备份数据库，拷贝到刚创建的数据库，需要一一对应

mysql> create database sc01;
Query OK, 1 row affected (0.00 sec)


zrlog、mysql2                   #因为是克隆关系，之前已经创建好了
mysql> create database zrlog;
mysql> create database mysql2;


mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| mysql2             |
| performance_schema |
| sc01               |
| test               |
| zrlog              |
+--------------------+
7 rows in set (0.33 sec)


[root@sc02 ~]# mysql -uroot -p123 sc01 < /tmp/zrlog.sql
Warning: Using a password on the command line interface can be insecure.

[root@sc02 ~]# mysql -uroot -p123 zrlog < /tmp/zrlog.sql
Warning: Using a password on the command line interface can be insecure.

[root@sc02 ~]# mysql -uroot -p123 mysql2 < /tmp/my2.sql
Warning: Using a password on the command line interface can be insecure.

[root@sc02 ~]# ls /data/mysql/
auto.cnf  ib_logfile0  ib_logfile1  ibdata1  mysql  mysql2  performance_schema  sc.err  sc01  sc02.err  sc02.pid  test  zrlog

3. 查看 slave 主从同步

【图文教程】MySQL 主从

若 slave io 及 sql 线程已经启动，需要先 stop slave ，change master 命令设置 slave 从机与 master 主机进行通信，开启从机上slave io及sql线程

mysql> stop slave;                #停止 从机
Query OK, 0 rows affected, 1 warning (0.00 sec)


mysql> change master to master_host='192.168.85.130',master_user='repl',master_password='123',master_log_file='sc-01.000002',master_log_pos=120;    #这一句很关键
Query OK, 0 rows affected, 2 warnings (0.05 sec)


mysql> start slave;               #开启 从机
Query OK, 0 rows affected (0.01 sec)


mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.85.130
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: sc-01.000002
          Read_Master_Log_Pos: 336
               Relay_Log_File: sc02-relay-bin.000003
                Relay_Log_Pos: 495
        Relay_Master_Log_File: sc-01.000002
             Slave_IO_Running: Yes                       #如果是yes，证明主从同步成功，注意AB服务器关闭防火墙
            Slave_SQL_Running: Yes                       #如果是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: 336
              Relay_Log_Space: 667
              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: 130
                  Master_UUID: 080ec641-752c-11e9-9eea-000c2988e3ff
             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 the slave I/O thread to update it
           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
1 row in set (0.00 sec)



语句释义：
master_host 与 master_port：分别代表master主机名（或IP地址）及mysql实例端口号。端口默认为3306,则可以省略；
master_user 与 master_password：连接到master主机复制账户所对应的用户名及密码。
master_log_file 与 master_log_pos：确定slave的io线程下次开始执行时从master开始读取的位置坐标，如果二者都没有指定，slave使用上次slave sql线程保存的位置。

注意：change master to可以不带参数，如：只改变了用于复制的用户密码，那么change master to只需针对
master_password选项作出修改即可。

master_log_file 与 master_log_pos 对应的下图两个数字

【图文教程】MySQL 主从

配置成功后，然后到 master 上，解锁数据表

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

4. 分别在 master 和 slave 上进行主从测试

【图文教程】MySQL 主从

数据库及表同步也可以指定，主从上命令不同：

主服务器：

binglog-do-db=1,2                         #仅同步指定的库1和2，多个库中间要用  ，号分隔  一个库直接就写名字  
                                           例如：blog，discuz

bing-ignore-db=3                          #忽略指定的库3，其他库全都要同步
        
从服务器：

replicate-do-db=                          #复制指定的库
replicate-ignore-db=                      #忽略指定的库，其他库全都要同步
replicate-do-table=                       #复制指定的表（尽量不要使用这两个）
replicate-ignore-table=                   #忽略指定的表，其他表全都要同步（尽量不要使用这两个）
replicate-wild-do-table= // 常用           #针对某一个库下的所有表进行同步，支持通配符 % 号 
                                           例如：库名 . 表名
replicate-wild-ignore-table= // 常用       #忽略指定的表，其他表全部同步

master 上测试

mysql> use sc01
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_sc01 |
+----------------+
| comment        |
| link           |
| log            |
| lognav         |
| plugin         |
| tag            |
| type           |
| user           |
| website        |
+----------------+
9 rows in set (0.00 sec)


mysql> select count(*) from website;    #查看 website 表的行数
+----------+
| count(*) |
+----------+
|        9 |
+----------+
1 row in set (0.04 sec)


mysql> truncate table website;          #清空 website 表内容
Query OK, 0 rows affected (0.41 sec)


mysql> select count(*) from website;    #查看 website 表的行数
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> drop table website;              #删除 website 表
Query OK, 0 rows affected (0.12 sec)

mysql>

slave 上测试

mysql> use sc01;
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_sc01 |
+----------------+
| comment        |
| link           |
| log            |
| lognav         |
| plugin         |
| tag            |
| type           |
| user           |
| website        |
+----------------+
9 rows in set (0.00 sec)



mysql> select count(*) from website;    #查看 website 的行数，与主端一致
+----------+
| count(*) |
+----------+
|        9 |
+----------+
1 row in set (0.00 sec)



mysql> select count(*) from website;    #在主端清空 website 表的内容，此时与主端一致
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)



mysql> select * from website;  
ERROR 1146 (42S02): Table 'sc01.website' doesn't exist    #在主端删除 website 表，此时也与主端一致，此表已经不存在


mysql> show variables like '%server_id%';                 #查看 slave 上 server-id 
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| server_id      | 130   |
| server_id_bits | 32    |
+----------------+-------+
2 rows in set (0.00 sec)

5. 扩展

mysql的版本：https://www.cnblogs.com/ivictor/p/9807284.html
mariadb的版本：https://downloads.mariadb.org/
主从：
https://blog.csdn.net/xiaoyi23000/article/details/80521423
https://blog.csdn.net/u013399093/article/details/70568837
https://www.cnblogs.com/abobo/p/4242417.html
MySQL Cloud Service 总结 http://isky000.com/database/mysql-perfornamce-tuning-cache-parameter


1.有的同学，遇到主从不能正常同步，提示uuid相同的错误。这是因为克隆机器导致。
https://www.2cto.com/database/201412/364479.html
2.不停库不锁表在线主从配置
http://seanlook.com/2015/12/14/mysql-replicas/
3.主从不同步
http://www.rfyy.net/archives/2309.html
http://blog.51cto.com/storysky/259280
4.主主
关于 auto_increment  https://blog.csdn.net/leshami/article/details/39779509
5.http://www.cnblogs.com/ygqygq2/p/6045279.html
6.mysql-proxy 实现读写分离 http://blog.51cto.com/zzclinux/1980487
7.mysql-proxy类似的产品有：
8.mycat  基于阿里的开源软件cobar，官网 www.mycat.io，https://my.oschina.net/ruoli/blog/1789370
9.mycat实现分库分表  https://www.cnblogs.com/joylee/p/7513038.html
10.atlas   出自于360，不维护不更新了  https://blog.csdn.net/AnPHPer/article/details/80566385
11.mysql环形主从 http://ask.apelearn.com/question/11437
12.mysql架构演变 http://www.aminglinux.com/bbs/thread-8025-1-1.html
13.MHA架构 http://blog.51cto.com/xiaoshuaigege/2060768
14.比较复杂的mysql集群架构 http://ask.apelearn.com/question/17026


xtrabackup --> innobackupex
mariadb备份和恢复
几篇和mysql主从有关的文章 关键词 GTID
https://blog.csdn.net/xiaoyi23000/article/details/80521423
https://blog.csdn.net/u013399093/article/details/70568837
https://www.cnblogs.com/abobo/p/4242417.html


了解几个关键词
读写分离
分库分表

本文地址：https://blog.csdn.net/weixin_44816331/article/details/108844093

上一篇： js正则表达式总结

下一篇： Vmware虚拟机搭建Web服务器

【图文教程】MySQL 主从

MySQL 主从

MySQL主从介绍

1. 配置 master

2. 配置 slave

3. 查看 slave 主从同步

4. 分别在 master 和 slave 上进行主从测试

5. 扩展

使用FbinstTool制作U盘版PE系统教程[图文]

彩影怎么做下雪效果?彩影制作出下雪效果的图文教程

MacBook Air上安装OS X 10.9的图文教程

mac如何解压缩文件？mac解压缩文件图文教程

mac如何提高网速?macOS dns设置图文教程

第一次启动idea设置方法（图文教程）

Mysql实现主从配置和多主多从配置

MySql数据类型教程示例详解

easyrecovery 使用教程[图文详解]

HDDreg 硬盘坏道修复程序图文使用教程