MySQL Study之--MySQL集群之mysql 主从复制

水深无声 2022-08-01 15:55 201阅读 0赞

**MySQL Study之--MySQL集群之mysql 主从复制**

MySQL 的数据库的高可用性的架构大概有以下几种：集群，读写分离，主备。而后面两种都是通过复制来实现的。下面将简单介绍复制的原理及配置

**复制的原理**

MySQL 复制基于主服务器在二进制日志中跟踪所有对数据库的更改(更新、删除等等)。每个从服务器从主服务器接收主服务器已经记录到其二进制日志的保存的更新，以便从服务器可以对其数据拷贝执行相同的更新。

将主服务器的数据拷贝到从服务器的一个途径是使用LOAD DATA FROM MASTER语句。请注意LOAD DATA FROM MASTER目前只在所有表使用MyISAM存储引擎的主服务器上工作。并且，该语句将获得全局读锁定。

MySQL 使用3个线程来执行复制功能,其中1个在主服务器上，另两个在从服务器上。当发出START SLAVE时，从服务器创建一个I/O线程，以连接主服务器并让它发送记录在其二进制日志中的语句。  
主服务器创建一个线程将二进制日志中的内容发送到从服务器。该线程可以识别为主服务器上SHOW PROCESSLIST的输出中的Binlog Dump线程。  
从服务器I/O线程读取主服务器Binlog Dump线程发送的内容并将该数据拷贝到从服务器数据目录中的本地文件中，即中继日志。     
第3个线程是SQL线程，是从服务器创建用于读取中继日志并执行日志中包含的更新。  
有多个从服务器的主服务器创建为每个当前连接的从服务器创建一个线程；每个从服务器有自己的I/O和SQL线程。

![Center][]

**实施案例：**

**主服务器（Master）：                                                 从服务器（Slave）：**

**hostname： rh6                                                          hostname:   mysrv**

**ip\_addr： 192.168.8.245                                           ip\_addr:    192.168.8.240**

**操作系统： RedHat EL6**

**MySQL：    mysql 5.6**

**1、配置mysql配置文件：**

**master：**

**\[root@rh6 oracle\]\# cat /etc/my.cnf **

\[mysqld\]  
datadir=/var/lib/mysql  
socket=/tmp/mysql.sock  
\#socket=/var/lib/mysql/mysql.sock  
user=mysql  
\# Disabling symbolic-links is recommended to prevent assorted security risks  
symbolic-links=0  
**log-bin=mysql-bin  
server-id=1**  
\#binlog\_format=mixed  
  
**slave：**  
**\[root@mysrv bin\]\# cat /etc/my.cnf**  
\[mysqld\]  
port            = 3306  
socket          = /tmp/mysql.sock  
skip-external-locking  
key\_buffer\_size = 16M  
max\_allowed\_packet = 1M  
table\_open\_cache = 64  
sort\_buffer\_size = 512K  
net\_buffer\_length = 8K  
read\_buffer\_size = 256K  
read\_rnd\_buffer\_size = 512K  
myisam\_sort\_buffer\_size = 8M  
datadir=/usr/local/mysql/data/mysql  
**log-bin=mysql-bin  
server-id=2**

**2、重新启动mysql server：**  
**master：**

[root@rh6 oracle]# mysqld_safe --user=mysql &
    [1] 2704
    [root@rh6 oracle]# 150630 17:24:17 mysqld_safe Logging to '/var/log/mysqld.log'.
    150630 17:24:18 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
    
    [root@rh6 oracle]# netstat -an |grep 3306
    tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      
    [root@rh6 oracle]# mysql -u root -p
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 1
    Server version: 5.6.4-m7-log Source distribution
    Copyright (c) 2000, 2011, 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>

**slave：**

[root@mysrv bin]# mysqld_safe --user=mysql&
    [1] 18191
    [root@mysrv bin]# 150630 17:26:43 mysqld_safe Logging to '/usr/local/mysql/data/mysql/mysrv.err'.
    150630 17:26:43 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data/mysql
    [root@mysrv bin]# mysql -u root -p
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 1
    Server version: 5.6.4-m7-log Source distribution
    Copyright (c) 2000, 2011, 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>

**3、在主服务器上授权从服务器用户访问**  
**master 授权：**  
**mysql> GRANT REPLICATION SLAVE ON \*.\* to 'tom'@'%' identified by 'oracle';**  
Query OK, 0 rows affected (0.01 sec)  
**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  
Database changed  
**mysql> show tables;**

**5、配置从服务器：**  
**mysql> change master to master\_host='192.168.8.245',master\_user='tom',master\_password='oracle',master\_log\_file='mysql-bin.,000010',master\_log\_pos=307;**  
Query OK, 0 rows affected, 2 warnings (0.07 sec)

**启动slave service：  
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.8.245  
                  Master\_User: tom  
                  Master\_Port: 3306  
                Connect\_Retry: 60  
              Master\_Log\_File: mysql-bin.,000010  
          Read\_Master\_Log\_Pos: 307  
               Relay\_Log\_File: mysrv-relay-bin.000001  
                Relay\_Log\_Pos: 4  
        Relay\_Master\_Log\_File: mysql-bin.,000010  
             Slave\_IO\_Running: No  
            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: 307  
              Relay\_Log\_Space: 114  
              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: 1593  
                Last\_IO\_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).  
               Last\_SQL\_Errno: 0  
               Last\_SQL\_Error:   
  Replicate\_Ignore\_Server\_Ids:   
             Master\_Server\_Id: 1  
                  Master\_UUID:   
             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: 150630 17:37:40  
     Last\_SQL\_Error\_Timestamp:   
               Master\_SSL\_Crl:   
           Master\_SSL\_Crlpath:   
1 row in set (0.00 sec)  
**-----slave I/O启动失败 ，提示“master 和 slave拥有相同的server-id！**  
**错误检查：**  
**master：**  
**mysql> show variables like '%server%';**  
\+---------------------------------+--------------------------------------+  
| Variable\_name                   | Value                                |  
\+---------------------------------+--------------------------------------+  
| character\_set\_server            | latin1                               |  
| collation\_server                | latin1\_swedish\_ci                    |  
| innodb\_ft\_server\_stopword\_table |                                      |  
| **server\_id                       | 1    **                                 |  
| server\_uuid                     | 94adecc3-a6cf-11e4-87f0-080027ac3540 |  
\+---------------------------------+--------------------------------------+  
5 rows in set (0.00 sec)  
**slave：**  
**mysql> show variables like '%server%';**  
\+---------------------------------+--------------------------------------+  
| Variable\_name                   | Value                                |  
\+---------------------------------+--------------------------------------+  
| character\_set\_server            | latin1                               |  
| collation\_server                | latin1\_swedish\_ci                    |  
| innodb\_ft\_server\_stopword\_table |                                      |  
| **server\_id                       | 1  **                                  |  
| server\_uuid                     | 32d0a37f-1f08-11e5-97e1-080027d5b17c |  
\+---------------------------------+--------------------------------------+  
5 rows in set (0.00 sec)  
  
**检查my.cnf文件，发现有两个server-id参数，并且其中一个server-id为1，删除一个server-id=1的配置参数 ！**  
**\[root@mysrv ~\]\# cat /etc/my.cnf**  
\[mysqld\]  
**server-id = 2**  
socket          = /tmp/mysql.sock  
skip-external-locking  
key\_buffer\_size = 16M  
max\_allowed\_packet = 1M  
table\_open\_cache = 64  
sort\_buffer\_size = 512K  
net\_buffer\_length = 8K  
read\_buffer\_size = 256K  
read\_rnd\_buffer\_size = 512K  
myisam\_sort\_buffer\_size = 8M  
datadir=/usr/local/mysql/data/mysql  
log-bin=mysql-bin  
  
**重新启动mysqld 服务：**  
\[root@mysrv ~\]\# ps -ef |grep mysqld  
root      2221  1877  0 17:53 pts/2    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld\_safe --user=mysql  
mysql     2528  2221  0 17:53 pts/2    00:00:00 /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=/usr/local/mysql/data/mysql/mysrv.err --pid-file=/usr/local/mysql/data/mysql/mysrv.pid --socket=/tmp/mysql.sock --port=3306  
root      2575  1877  0 17:55 pts/2    00:00:00 grep mysqld  
\[root@mysrv ~\]\# kill -9 2221 2528  
\[root@mysrv ~\]\# ps -ef |grep mysqld  
root      2583  1877  0 17:55 pts/2    00:00:00 grep mysqld  
\[1\]+  Killed                  mysqld\_safe --user=mysql  
**\[root@mysrv ~\]\# mysqld\_safe --user=mysql &**  
\[1\] 2584  
\[root@mysrv ~\]\# 150630 17:56:15 mysqld\_safe Logging to '/usr/local/mysql/data/mysql/mysrv.err'.  
150630 17:56:16 mysqld\_safe Starting mysqld daemon with databases from /usr/local/mysql/data/mysql  
**\[root@mysrv ~\]\# 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.6.4-m7-log Source distribution  
Copyright (c) 2000, 2011, 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 variables like '%server%';**  
\+---------------------------------+--------------------------------------+  
| Variable\_name                   | Value                                |  
\+---------------------------------+--------------------------------------+  
| character\_set\_server            | latin1                               |  
| collation\_server                | latin1\_swedish\_ci                    |  
| innodb\_ft\_server\_stopword\_table |                                      |  
**| server\_id                       | 2**                                     |  
| server\_uuid                     | 32d0a37f-1f08-11e5-97e1-080027d5b17c |  
\+---------------------------------+--------------------------------------+  
5 rows in set (0.00 sec)  
**继续同步，同步错误：**  
mysql> show slave status\\G  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
               Slave\_IO\_State:   
                  Master\_Host: 192.168.8.245  
                  Master\_User: tom  
                  Master\_Port: 3306  
                Connect\_Retry: 60  
              Master\_Log\_File: mysql-bin.,000010  
          Read\_Master\_Log\_Pos: 307  
               Relay\_Log\_File: mysrv-relay-bin.000004  
                Relay\_Log\_Pos: 4  
        Relay\_Master\_Log\_File: mysql-bin.,000010  
             Slave\_IO\_Running: No  
            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: 114  
              Relay\_Log\_Space: 114  
              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: 1236  
                Last\_IO\_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'  
               Last\_SQL\_Errno: 0  
               Last\_SQL\_Error:   
  Replicate\_Ignore\_Server\_Ids:   
             Master\_Server\_Id: 1  
                  Master\_UUID: 94adecc3-a6cf-11e4-87f0-080027ac3540  
             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: 150630 17:57:44  
     Last\_SQL\_Error\_Timestamp:   
               Master\_SSL\_Crl:   
           Master\_SSL\_Crlpath:   
1 row in set (0.00 sec)

**问题原因：**

**mysql> change master to master\_host='192.168.8.245',master\_user='tom',master\_password='oracle',master\_log\_file='mysql-bin.,000010',master\_log\_pos=307;**

**---日志名输入错误！  
问题解决方法：**  
**mysql> stop slave;**  
Query OK, 0 rows affected (0.03 sec)  
**mysql> change master to master\_host='192.168.8.245',master\_user='tom',master\_password='oracle',master\_log\_file='mysql-bin.000010',master\_log\_pos=307;**  
Query OK, 0 rows affected, 2 warnings (0.08 sec)  
**mysql> start slave;**  
Query OK, 0 rows affected (0.00 sec)  
mysql> show slave status\\G  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
               Slave\_IO\_State: Waiting for master to send event  
                  Master\_Host: 192.168.8.245  
                  Master\_User: tom  
                  Master\_Port: 3306  
                Connect\_Retry: 60  
              Master\_Log\_File: mysql-bin.000010  
          Read\_Master\_Log\_Pos: 307  
               Relay\_Log\_File: mysrv-relay-bin.000002  
                Relay\_Log\_Pos: 267  
        Relay\_Master\_Log\_File: mysql-bin.000010  
            ** 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: 307  
              Relay\_Log\_Space: 430  
              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: 94adecc3-a6cf-11e4-87f0-080027ac3540  
             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:   
1 row in set (0.00 sec)  
**----------从以上可以看到，同步成功！  
同步测试：（master）**  
**mysql> create database prod;**  
Query OK, 1 row affected (0.09 sec)  
**mysql> show databases;**  
\+--------------------+  
| Database           |  
\+--------------------+  
| information\_schema |  
| mysql              |  
| performance\_schema |  
| prod               |  
| test               |  
\+--------------------+  
5 rows in set (0.00 sec)  
**mysql> create table t1 (id int,name varchar(10)) engine=innodb;**  
Query OK, 0 rows affected (0.06 sec)  
**mysql> insert into t1 values (1,'tom');**  
Query OK, 1 row affected (0.01 sec)  
**mysql> insert into t1 values (2,'scott');**  
Query OK, 1 row affected (0.06 sec)  
**mysql> commit;**  
Query OK, 0 rows affected (0.00 sec)  
**mysql> select \* from t1;**  
\+------+-------+  
| id   | name  |  
\+------+-------+  
|    1 | tom   |  
|    2 | scott |  
\+------+-------+  
2 rows in set (0.00 sec)  
**slave：**  
**mysql> show databases;**  
\+--------------------+  
| Database           |  
\+--------------------+  
| information\_schema |  
| mysql              |  
| performance\_schema |  
| prod               |  
| test               |  
\+--------------------+  
5 rows in set (0.02 sec)  
**mysql> use prod;**  
Database changed  
**mysql> show tables;**  
\+----------------+  
| Tables\_in\_prod |  
\+----------------+  
| t1             |  
\+----------------+  
1 row in set (0.00 sec)  
**mysql> select \* from t1;**  
\+------+-------+  
| id   | name  |  
\+------+-------+  
|    1 | tom   |  
|    2 | scott |  
\+------+-------+  
2 rows in set (0.00 sec)  
  
**------至此，mysql主从复制配置完成！**

[Center]: /images/20220731/5956a8ca3e4a449ba7ace2020bbe7e4f.png