MySQL主从配置

喜欢ヅ旅行 2022-05-21 02:07 247阅读 0赞

# 1.MySQL主从介绍 #

MySQL主从又叫做MySQL Replication、AB复制。简单讲就是A和B两台机器做主从后，在A上写数据，另外一台B也会跟着写数据，两者数据实时同步的。  
MySQL主从是基于binlog的，主上须开启binlog才能进行主从。  
主从过程大致有3个步骤：  
1）主将更改操作记录到binlog里；  
2）从将主的binlog事件(sql语句)同步到从本机上并记录在relaylog里；  
3）从根据relaylog里面的sql语句按顺序执行；  
主上有一个log dump线程，用来和从的I/O线程传递binlog。  
从上有两个线程，其中I/O线程用来同步主的binlog并生成relaylog，另外一个SQL线程用来把relaylog里面的sql语句落地。

![70][]

先准备两台机器，我这里128作为主上，130作为从属，然后查询mysql服务是否启动；

[root@yuioplvlinux-128 ~]# ps aux |grep mysql
    root       873  0.0  0.0 115432   548 ?        S    19:22   0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/data/mysql --pid-file=/usr/local/mysql/data/mysql/yuioplvlinux-128.pid
    mysql     1014  0.1 40.4 1310920 407224 ?      Sl   19:22   0:04 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=yuioplvlinux-128.err --pid-file=/usr/local/mysql/data/mysql/yuioplvlinux-128.pid
    root     13477  0.0  0.0 112724   972 pts/1    S+   20:22   0:00 grep --color=auto mysql

[root@yuioplvlinux-130 ~]# ps aux |grep mysql
    root     23601  0.0  0.1  11816  1612 ?        S    08:21   0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/data/mysql --pid-file=/usr/local/mysql/data/mysql/yuioplvlinux-130.pid
    mysql    23736  2.5 44.6 1302724 449816 ?      Sl   08:21   1:35 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=yuioplvlinux-130.err --pid-file=/usr/local/mysql/data/mysql/yuioplvlinux-130.pid
    root     28646  0.0  0.0 112704   660 pts/0    R+   09:22   0:00 grep --color=auto mysql

# 2.设置主（master） #

编辑/etc/my.cnf文件，新增如下两行：

server-id=128
    log_bin=linux128

保存后重启mysql服务；

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

查看定义的/database/dir目录，是否有linux128.index和linux128.000001；

[root@yuioplvlinux-128 ~]# ll -t /usr/local/mysql/data/mysql
    总用量 110652
    -rw-rw---- 1 mysql mysql    34066 6月  29 21:11 yuioplvlinux-128.err
    -rw-rw---- 1 mysql mysql 50331648 6月  29 21:11 ib_logfile0
    -rw-rw---- 1 mysql mysql 12582912 6月  29 21:11 ibdata1
    -rw-rw---- 1 mysql mysql        6 6月  29 21:11 yuioplvlinux-128.pid
    -rw-rw---- 1 mysql mysql       18 6月  29 21:11 linux128.index
    -rw-rw---- 1 mysql mysql      120 6月  29 21:11 linux128.000001
    -rw-rw---- 1 mysql mysql       56 6月   6 22:46 auto.cnf
    drwx------ 2 mysql mysql     4096 6月   6 22:44 mysql
    drwx------ 2 mysql mysql     4096 6月   6 22:44 performance_schema
    -rw-rw---- 1 mysql mysql 50331648 6月   6 22:44 ib_logfile1
    drwx------ 2 mysql mysql        6 6月   6 22:44 test

创建一个测试主从同步的数据库；

[root@yuioplvlinux-128 ~]# mysql -uroot -p123456 -e "create database yu"   #创建库
    Warning: Using a password on the command line interface can be insecure.
    [root@yuioplvlinux-128 ~]# mysqldump -uroot -p123456 yu > /tmp/yu.sql   #备份为/tmp/yu.sql
    Warning: Using a password on the command line interface can be insecure.
    [root@yuioplvlinux-128 ~]# ll -t /usr/local/mysql/data/mysql
    总用量 110652
    -rw-rw---- 1 mysql mysql      350 6月  29 21:20 linux128.000001
    drwx------ 2 mysql mysql       20 6月  29 21:20 yu
    -rw-rw---- 1 mysql mysql    34066 6月  29 21:11 yuioplvlinux-128.err
    -rw-rw---- 1 mysql mysql 50331648 6月  29 21:11 ib_logfile0
    -rw-rw---- 1 mysql mysql 12582912 6月  29 21:11 ibdata1
    -rw-rw---- 1 mysql mysql        6 6月  29 21:11 yuioplvlinux-128.pid
    -rw-rw---- 1 mysql mysql       18 6月  29 21:11 linux128.index
    -rw-rw---- 1 mysql mysql       56 6月   6 22:46 auto.cnf
    drwx------ 2 mysql mysql     4096 6月   6 22:44 mysql
    drwx------ 2 mysql mysql     4096 6月   6 22:44 performance_schema
    -rw-rw---- 1 mysql mysql 50331648 6月   6 22:44 ib_logfile1
    drwx------ 2 mysql mysql        6 6月   6 22:44 test

进入数据库，锁定数据库的写操作，然后查看master的状态，这些数据是要记录下来的，一会在slave中会用到；

[root@yuioplvlinux-128 ~]# 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 5
    Server version: 5.6.40-log MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2018, 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.30.130' identified by '123456';   #设置从数据库的权限
    Query OK, 0 rows affected (0.12 sec)
    
    mysql> flush tables with read lock;   #锁定数据库的写操作
    Query OK, 0 rows affected (0.24 sec)
    
    mysql> show master status;   #查看master的状态
    +-----------------+----------+--------------+------------------+-------------------+
    | File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    +-----------------+----------+--------------+------------------+-------------------+
    | linux128.000001 |      561 |              |                  |                   |
    +-----------------+----------+--------------+------------------+-------------------+
    1 row in set (0.01 sec)

# 3.设置从（slave） #

编辑/etc/my.cnf文件，新增如下一行：

server_id =130

保存后重启mysql服务；

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

将主上的数据库备份文件复制至本地的/tmp目录下；

[root@yuioplvlinux-130 ~]# ls /usr/local/mysql/data/mysql/
    auto.cnf  ibdata1  ib_logfile0  ib_logfile1  mysql  performance_schema  test  yuioplvlinux-130.err  yuioplvlinux-130.pid
    [root@yuioplvlinux-130 ~]# scp 192.168.30.128:/tmp/*.sql /tmp
    The authenticity of host '192.168.30.128 (192.168.30.128)' can't be established.
    ECDSA key fingerprint is SHA256:zsVdMVBGsqjf0pneDuiwsE3yUVBbNAAlPQGqf0+lHLs.
    ECDSA key fingerprint is MD5:f3:6e:14:fc:83:8e:14:60:f7:72:72:8c:8a:dc:a1:fc.
    Are you sure you want to continue connecting (yes/no)? yes
    Warning: Permanently added '192.168.30.128' (ECDSA) to the list of known hosts.
    root@192.168.30.128's password: 180629 10:52:01 mysqld_safe A mysqld process with pid=28897 is already running. Aborting!!
    
    yu.sql                                                                                             100% 1257    57.2KB/s   00:00

进入数据库，创建一个备份文件对应的数据库，名称需要与主上保持一致；

[root@yuioplvlinux-130 ~]# 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 1
    Server version: 5.6.40 MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2018, 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> create database yu;
    Query OK, 1 row affected (0.00 sec)
    
    mysql> quit
    Bye

将备份文件还原至创建好的对应数据库，还原成功后，查看定义的/database/dir目录，确认与主上一致；

[root@yuioplvlinux-130 ~]# mysql -uroot -p123456 yu < /tmp/yu.sql 
    Warning: Using a password on the command line interface can be insecure.
    [root@yuioplvlinux-130 ~]# ls /usr/local/mysql/data/mysql/
    auto.cnf  ibdata1  ib_logfile0  ib_logfile1  mysql  performance_schema  test  yu  yuioplvlinux-130.err  yuioplvlinux-130.pid

进入数据库，进行配置；

[root@yuioplvlinux-130 ~]# 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 6
    Server version: 5.6.40 MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2018, 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> stop slave;   #关闭slave
    Query OK, 0 rows affected (0.07 sec)
    
    mysql> change master to master_host='192.168.30.128', master_user='repl', master_password='123456', master_log_file='linux128.000001', master_log_pos=561;   #

master_host为主上的IP地址，master_user是定义的用于主从同步的用户，master_password为登录密码，master_log_file是主上使用show master status命令查到的File名称，master_log_pos是主上使用show master status命令查到的Position值

Query OK, 0 rows affected, 2 warnings (0.01 sec)mysql> start slave; \#开启slaveQuery OK, 0 rows affected (0.00 sec)

查看slave的状态，确保Slave\_IO\_Running和Slave\_SQL\_Running的值都为Yes，如果这两个值都为Yes，就说明主从配置成功了。

mysql> show slave status\G
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.30.128
                      Master_User: repl
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: linux128.000001
              Read_Master_Log_Pos: 561
                   Relay_Log_File: yuioplvlinux-130-relay-bin.000002
                    Relay_Log_Pos: 282
            Relay_Master_Log_File: linux128.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: 561
                  Relay_Log_Space: 466
                  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: 128
                      Master_UUID: 6bebdb61-6998-11e8-8e18-000c2996179a
                 Master_Info_File: /usr/local/mysql/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)

在主上数据库中，进行解除锁定。

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

# 4.测试主从同步 #

主上数据库中切换至yu数据库，查看该数据库下有哪几个表，并查询t3表中有多少行；

mysql> use yu;
    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_yu |
    +--------------+
    | t1           |
    | t2           |
    | t3           |
    +--------------+
    3 rows in set (0.00 sec)
    
    mysql> select count(*) t3;
    +----+
    | t3 |
    +----+
    |  1 |
    +----+
    1 row in set (0.02 sec)

在从上数据库中，同样的切换至yu数据库下，查询t3表中的行数；

mysql> use yu;
    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> select count(*) t3;
    +----+
    | t3 |
    +----+
    |  1 |
    +----+
    1 row in set (0.06 sec)

在主上清空t3表，查看该表中的行数；

mysql> truncate table t3;
    Query OK, 0 rows affected (0.12 sec)
    
    mysql> select * from  t3;
    Empty set (0.05 sec)

在从上，查看t3表中的行数；

mysql> select count(*) from t3;
    +----------+
    | count(*) |
    +----------+
    |        0 |
    +----------+
    1 row in set (0.00 sec)
    
    mysql> select * from t3;
    Empty set (0.00 sec)

在主上删除t3表；

mysql> drop table t3;
    Query OK, 0 rows affected (0.07 sec)

在从上，查看t3表中的行数，就会提示yu.t3表不存在。

mysql> select * from t3;
    ERROR 1146 (42S02): Table 'yu.t3' doesn't exist

[70]: /images/20220521/5c01cd1fcad24a0488669a99667d2424.png