MySQL主从

最新推荐文章于 2021-03-27 14:40:08 发布

xou6363

最新推荐文章于 2021-03-27 14:40:08 发布

阅读量187

点赞数

本文链接：https://blog.csdn.net/xou6363/article/details/81129915

版权

1、MySQL主从介绍

Mysql主从又叫Replication,AB复制。A、B两台机器做主从后，A上写数据，B上也会跟着写数据，A和B的数据实时同步。

Mysql的主从实现是基于binlog的，要开启binlog才能够实现主从功能。

Mysql主从的原理：

1、主将更改操作记录到binlog里。

2、从将主的binlog事件（sql语句）同步到从的机器上，并记录在relaylog里（中继日志）。

3、根据relaylog里的sql语句，按顺序执行。

主上有一个logdump线程，用来与从的I/O线程传递binlog。

mysql主从使用场景：

1、单独的数据备份：主机器宕机后，备份的从机器可以给Web服务提供数据

2、读备份库：web服务从主上读写数据，当主机器访问压力大的时候，可以在从上读数据，缓解主压力，但是从机器不写入数据。

2、准备工作

mysql主从准备工作：

准备两台机器，每台机器安装msyql服务，并启动mysql服务；
mysql详细安装参考：https://blog.csdn.net/xou6363/article/details/80796230
启动前：修改my.cnf配置文件的basedir、datadir，拷贝启动脚本启动前：修改my.cnf配置文件的basedir、datadir，拷贝启动脚本

[root@localhost ~]# cp /usr/local/mysql/support-files/my-default.cnf /etc/my.cnf
[root@localhost ~]# vim /etc/my.cnf

定义 datadir=/data/mysql     
定义 socket=/tmp/mysql.sock

[root@localhost ~]#cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@localhost ~]#vim /etc/init.d/mysqld

指定basedir的路径 /usr/local/mysql  
指定datadir的路径 /data/mysql

改变/data/mysql属主和属组，命令 chomd mysql:mysql /data/mysql,并启动服务

[root@aminglinux ~]# ll /data/mysql
总用量 110636
-rw-rw----. 1 mysql mysql    13144 7月  19 08:53 aming1.err
-rw-rw----. 1 mysql mysql     6755 7月  19 09:09 aminglinux.err
-rw-rw----. 1 mysql mysql        5 7月  19 09:09 aminglinux.pid
-rw-rw----. 1 mysql mysql       56 7月  17 14:34 auto.cnf
-rw-rw----. 1 mysql mysql 12582912 7月  19 09:09 ibdata1
-rw-rw----. 1 mysql mysql 50331648 7月  19 09:09 ib_logfile0
-rw-rw----. 1 mysql mysql 50331648 7月  17 14:29 ib_logfile1
drwx------. 2 mysql mysql     4096 7月  17 14:29 mysql
drwx------. 2 mysql mysql     4096 7月  17 14:29 performance_schema
drwx------. 2 mysql mysql        6 7月  17 14:29 test
drwx------. 2 mysql mysql     4096 7月  17 15:21 zrlog

[root@localhost ~]# /etc/init.d/mysqld start

3、主从配置 - 主上操作

修改my.cnf：设置服务id、定义binlog日志名字，重启mysqld

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

datadir=/data/mysql     
socket=/tmp/mysql.sock
server_id= 110   //服务id;这个id可以自定义，这里根据ip来定义 aminglinux.index和aminglinux.0000011
log_bin=aminglinux1 //定义binlog日志名字
[root@aminglinux ~]# /etc/init.d/mysqld restart
Shutting down MySQL... SUCCESS! 
Starting MySQL......... SUCCESS!

/data/mysql/目录下，会生成一些文件

[root@aminglinux ~]#  ll /data/mysql
总用量 110648
-rw-rw----. 1 mysql mysql    13144 7月  19 08:53 aming1.err
-rw-rw----. 1 mysql mysql      120 7月  19 09:21 aminglinux1.000001
-rw-rw----. 1 mysql mysql       21 7月  19 09:21 aminglinux1.index
-rw-rw----. 1 mysql mysql    11969 7月  19 09:21 aminglinux.err
-rw-rw----. 1 mysql mysql        5 7月  19 09:21 aminglinux.pid
-rw-rw----. 1 mysql mysql       56 7月  17 14:34 auto.cnf
-rw-rw----. 1 mysql mysql 12582912 7月  19 09:21 ibdata1
-rw-rw----. 1 mysql mysql 50331648 7月  19 09:21 ib_logfile0
-rw-rw----. 1 mysql mysql 50331648 7月  17 14:29 ib_logfile1
drwx------. 2 mysql mysql     4096 7月  17 14:29 mysql
drwx------. 2 mysql mysql     4096 7月  17 14:29 performance_schema
drwx------. 2 mysql mysql        6 7月  17 14:29 test
drwx------. 2 mysql mysql     4096 7月  17 15:21 zrlog

其中 .index 索引页，这个文件是必须要有的
其中 .000001 这个是二进制日志文件，会持续生成2、3、4等等（这个文件是实现主从配置的根本，没有这个文件根本没有办法完成主从）
创建一个库，恢复填充数据进去用作实验:zc01库

[root@aminglinux ~]# mysqldump -uroot -p123456 ultrax >/tmp/blog.sql   //备份ultrax库到 /tmp/目录下
Warning: Using a password on the command line interface can be insecure.
[root@aminglinux ~]# mysql -uroot -p123456 -e "create database zc01"   //创建zc01的库
Warning: Using a password on the command line interface can be insecure.
[root@aminglinux ~]# mysql -uroot -p123456 zc01 </tmp/blog.sql   //用备份的blog库文件的数据，恢复到zc01库中
Warning: Using a password on the command line interface can be insecure.

创建主从同步数据的用户:repl

[root@aminglinux ~]# mysql -uroot -p123456
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.6.35-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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 replication slave on *.* to 'repl'@'192.168.222.112' identified by '123456';   
Query OK, 0 rows affected (0.01 sec)

指定权限，replication slave权限针对repl这个用户针对从的那个IP，指定来源（若是写所有的IP会很危险）
锁定表，目的是不让表继续写，因为一会需要做从机器配置，需要进行一个同步，让两台机器同步，保证两台机器的数据一致，同步才不会出错：flush tables with read lock;

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

查看一下binlog的文件和大小，并记住binlog的filename：show master status;

mysql> show master status;
+--------------------+----------+--------------+------------------+-------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------------+----------+--------------+------------------+-------------------+
| aminglinux1.000004 |    10989 |              |                  |                   |
+--------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)

查看/data/mysql/下有哪些库，主上有哪些库，一会从上也得有哪些库，同步这些库，就意味着这些数据都得备份过去

[root@aminglinux ~]# ll /data/mysql
总用量 177624
-rw-rw----. 1 mysql mysql    13144 7月  19 08:53 aming1.err
-rw-rw----. 1 mysql mysql      143 7月  19 10:07 aminglinux1.000001
-rw-rw----. 1 mysql mysql    69447 7月  19 10:12 aminglinux1.000002
-rw-rw----. 1 mysql mysql  1373667 7月  19 10:12 aminglinux1.000003
-rw-rw----. 1 mysql mysql    10989 7月  19 11:15 aminglinux1.000004
-rw-rw----. 1 mysql mysql       84 7月  19 10:32 aminglinux1.index
-rw-rw----. 1 mysql mysql    22393 7月  19 10:32 aminglinux.err
-rw-rw----. 1 mysql mysql        5 7月  19 10:32 aminglinux.pid
-rw-rw----. 1 mysql mysql       56 7月  17 14:34 auto.cnf
-rw-rw----. 1 mysql mysql 79691776 7月  19 10:49 ibdata1
-rw-rw----. 1 mysql mysql 50331648 7月  19 10:49 ib_logfile0
-rw-rw----. 1 mysql mysql 50331648 7月  17 14:29 ib_logfile1
drwx------. 2 mysql mysql     4096 7月  17 14:29 mysql
drwx------. 2 mysql mysql     4096 7月  19 10:12 performance_schema
drwx------. 2 mysql mysql        6 7月  17 14:29 test
drwx------. 2 mysql mysql     4096 7月  19 10:49 zc01
drwx------. 2 mysql mysql     4096 7月  17 15:21 zrlog

备份zc01库文件，拷贝到从机器上去：在从上新建库，用zc01库文件数据恢复到新建的库中去。

[root@aminglinux ~]# mysqldump -uroot -p123456 zc01 >/tmp/zc01.sql
Warning: Using a password on the command line interface can be insecure.
[root@aminglinux ~]# ls -l /tmp/zc01.sql
-rw-r--r--. 1 root root 10002 7月  19 11:40 /tmp/zc01.sql

4、配置从

修改my.cnf文件：设置服务id，与主的服务id不一样。重启mysqld

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

datadir=/data/mysql
socket=/tmp/mysql.sock
server_id=112

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

把主上的zc01.sql库文件拷贝到从上：放/tmp/目录下

[root@aming3 ~]# ls /tmp/
hsperfdata_root
mysql.sock
systemd-private-3160a027cf9d4baa9890a04972fe3863-chronyd.service-sFYrJK
systemd-private-3160a027cf9d4baa9890a04972fe3863-vgauthd.service-7D0k9d
systemd-private-3160a027cf9d4baa9890a04972fe3863-vmtoolsd.service-40MAAH
systemd-private-f69f151bd7da4020a2f794920b374bd3-chronyd.service-31vyFg
systemd-private-f69f151bd7da4020a2f794920b374bd3-vgauthd.service-TUVgJ9
systemd-private-f69f151bd7da4020a2f794920b374bd3-vmtoolsd.service-JDisN2
test.txt
yum_save_tx.2018-07-17.11-12.Q_HOyv.yumtx
zc01.sql
[root@aming3 ~]# ls -l /tmp/zc01.sql
-rw-r--r--. 1 root root 10002 7月  19 12:29 /tmp/zc01.sql

创建一个库，把zc01的数据恢复进去

mysql> create database zc01;
Query OK, 1 row affected (0.02 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| zc01               |
| zrlogbak           |
+--------------------+
6 rows in set (0.03 sec）

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

实现主从：

mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> change master to master_host='192.168.222.110',master_user='repl',master_password='123456',master_log_file='amingglinux1.000004',master_log_pos=10989; 
Query OK, 0 rows affected, 2 warnings (0.08 sec)

配置主机器相关配置

change master to master_host='192.168.222.110', master_user='repl', master_password='123456', master_log_file='aminglinux1.000004', master_log_pos=10989;

master_host='192.168.222.110'，指定主机器host
master_user='repl'，指定主机器用户
master_password='123456'，指定主机器密码
master_log_file='aminglinux1.0000041'，指定binlog文件名
master_log_pos=10989，指定binlog文件大小
开始slave :start slave;通过 show slave status\G 判断主从是否配置成功

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.222.110
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: aminglinux1.000004
          Read_Master_Log_Pos: 10989
               Relay_Log_File: aming3-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: aminglinux1.000004
             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: 10989