mysql主从

╰+哭是因爲堅強的太久メ 2022-06-08 01:39 136阅读 0赞

# 1 MySQL主从复制原理 #

为了减轻主库的压力，应该在系统应用层面做读写分离，写操作走主库，读操作走从库，下图为MySQL官网给出的主从复制的原理图，从图中可以简单的了解读写分离及主从同步的过程，分散了数据库的访问压力，提升整个系统的性能和可用性，降低了大访问量引发数据库宕机的故障率。

![Center][]

mysql主从复制需要三个线程，master（binlog dump thread）、slave（I/O thread 、SQL thread）。

**master**

（1）binlog dump线程：当主库中有数据更新时，那么主库就会根据按照设置的binlog格式，将此次更新的事件类型写入到主库的binlog文件中，此时主库会创建log dump线程通知slave有数据更新，当I/O线程请求日志内容时，会将此时的binlog名称和当前更新的位置同时传给slave的I/O线程。

**slave**

（2）I/O线程：该线程会连接到master，向log dump线程请求一份指定binlog文件位置的副本，并将请求回来的binlog存到本地的relay log中，relay log和binlog日志一样也是记录了数据更新的事件，它也是按照递增后缀名的方式，产生多个relay log（ host\_name-relay-bin.000001）文件，slave会使用一个index文件（ host\_name-relay-bin.index）来追踪当前正在使用的relay log文件。

（3）SQL线程：该线程检测到relay log有更新后，会读取并在本地做redo操作，将发生在主库的事件在本地重新执行一遍，来保证主从数据同步。此外，如果一个relay log文件中的全部事件都执行完毕，那么SQL线程会自动将该relay log 文件删除掉。

下面是整个复制过程的原理图：

![Center 1][]

# 2 配置主从复制 #

主从复制，步骤如下：  
1、主从服务器分别作以下操作：  
  1.1、版本一致  
  1.2、初始化表，并在后台启动mysql  
  1.3、修改root的密码  
  
2、修改主服务器master:

#vi /etc/my.cnf
           [mysqld]
           log-bin=mysql-bin   //[必须]启用二进制日志
           server-id=222      //[必须]服务器唯一ID，默认是1，一般取IP最后一段

3、修改从服务器slave:

#vi /etc/my.cnf
           [mysqld]
           log-bin=mysql-bin   //[不是必须]启用二进制日志
           server-id=226      //[必须]服务器唯一ID，默认是1，一般取IP最后一段

4、重启两台服务器的mysql  
   /etc/init.d/mysql restart  
  
5、在主服务器上建立帐户并授权slave:

#/usr/local/mysql/bin/mysql -uroot -pmttang 
       msyql> ？ grant   //查看 grant 的详细用法   
       mysql>GRANT REPLICATION SLAVE ON *.* to 'mysync'@'%' identified by 'q123456';

//一般不用root帐号，“%”表示所有客户端都可能连，只要帐号，密码正确，此处可用具体客户端IP代替，如192.168.145.226，加强安全。  
查看 Mysql 的用户表msyql> select user, host, password from mysql.user;  
  
6、登录主服务器的mysql，查询master的状态

注：执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化  
  
  
7、配置从服务器Slave：

mysql>change master to master_host='192.168.145.222',master_user='mysync',master_password='q123456',
             master_log_file='mysql-bin.000004',master_log_pos=308;   //注意不要断开，308数字前后无单引号。
       Mysql>start slave;    //启动从服务器复制功能

8、检查从服务器复制功能状态：

mysql> show slave status\G
       *************************** 1. row ***************************
                  Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.2.222  //主服务器地址
                  Master_User: mysync   //授权帐户名，尽量避免使用root
                  Master_Port: 3306    //数据库端口，部分版本没有此行
                  Connect_Retry: 60
                  Master_Log_File: mysql-bin.000004
                  Read_Master_Log_Pos: 600     //#同步读取二进制日志的位置，大于等于Exec_Master_Log_Pos
                  Relay_Log_File: ddte-relay-bin.000003
                  Relay_Log_Pos: 251
                  Relay_Master_Log_File: mysql-bin.000004
                  Slave_IO_Running: Yes    //此状态必须YES
                  Slave_SQL_Running: Yes     //此状态必须YES

注：Slave\_IO及Slave\_SQL进程必须正常运行，即YES状态，否则都是错误的状态(如：其中一个NO均属错误)。以上操作过程，主从服务器配置完成。  
    
9、主从服务器测试  
主服务器Mysql，建立数据库，并在这个库中建表插入一条数据：

mysql> create database hi_db;
      Query OK, 1 row affected (0.00 sec)
    
    
      mysql> use hi_db;
      Database changed
    
    
      mysql>  create table hi_tb(id int(3),name char(10));
      Query OK, 0 rows affected (0.00 sec)
     
      mysql> insert into hi_tb values(001,'bobu');
      Query OK, 1 row affected (0.00 sec)
    
    
      mysql> show databases;
       +--------------------+
       | Database           |
       +--------------------+
       | information_schema |
       | hi_db                |
       | mysql                |
       | test                 |
       +--------------------+
       4 rows in set (0.00 sec)

从服务器Mysql查询：

官方：https://dev.mysql.com/doc/refman/5.7/en/replication-configuration.html

# 3 数据同步 #

数据同步前，首先确保 Mysql 用户授权已经完成以及 Mysql bin-log 日志已经成功开启，并确保每台服务器的 server-id 是唯一的

## 3.1 mysqlbinlog二进制日志增量备份 ##

### 3.1.1 命令 ###

mysqlbinlog : Dumps a MySQL binary log in a format usable for viewing or for piping to the mysql command line client.  
Usage: mysqlbinlog \[options\] log-files

Default options are read from the following files in the given order:  
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf  
  
从mysqldump备份文件恢复数据会丢失掉从备份点开始的更新数据，所以还需要结合mysqlbinlog二进制日志增量备份。确保my.ini或者my.cnf中包含下面的配置以启用二进制日志，或者mysqld ---log-bin：  
\[mysqld\]  
log-bin=mysql-bin

mysqldump命令必须带上--flush-logs选项以生成新的二进制日志文件：

mysqldump --single-transaction --flush-logs --master-data=2 > backup.sql

这样生成的增量二进制日志文件比如为mysql-bin.000003，那么恢复数据时如下：

shell> mysql -uroot -pPwd < backup_sunday_1_PM.sql
    shell> mysqlbinlog mysql-bin.000003 | mysql -uroot -pPwd

### 3.1.2 **binlog简介** ###

MySQL主从同步是基于binlog文件主从复制实现，为了更好的理解主从同步过程，这里简单介绍一下binlog日志文件。

binlog日志用于记录所有更新了数据或者已经潜在更新了数据（例如，没有匹配任何行的一个DELETE）的所有语句。语句以“事件”的形式保存，它描述数据更改，它是以二进制的形式保存在磁盘中。我们可以通过mysql提供的查看工具mysqlbinlog查看文件中的内容，例如 mysqlbinlog mysql-bin.00001 | more，这里注意一下binlog文件的后缀名00001，binlog文件大小和个数会不断的增加，当MySQL停止或重启时，会产生一个新的binlog文件，后缀名会按序号递增，例如mysql-bin.00002、mysql-bin.00003，并且当binlog文件大小超过 max\_binlog\_size系统变量配置时也会产生新的binlog文件。

**（一）binlog日志格式**

（1） statement ： 记录每一条更改数据的sql;

*  优点：binlog文件较小，节约I/O，性能较高。
 *  缺点：不是所有的数据更改都会写入binlog文件中，尤其是使用MySQL中的一些特殊函数（如LOAD\_FILE()、UUID()等）和一些不确定的语句操作，从而导致主从数据无法复制的问题。

（2） row ： 不记录sql，只记录每行数据的更改细节

*  优点：详细的记录了每一行数据的更改细节，这也意味着不会由于使用一些特殊函数或其他情况导致不能复制的问题。
 *  缺点：由于row格式记录了每一行数据的更改细节，会产生大量的binlog日志内容，性能不佳，并且会增大主从同步延迟出现的几率。

（3） mixed：一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种。

**（二）binlog日志内容**

mysqlbinlog命令查看的内容如下：

![linkedkeeper0_bebc4db8-c555-4944-9e02-ee58d47b70dd.jpg][]

根据事件类型查看的binlog内容：

mysql> show binlog events in 'mysql-bin.000001';

**（三）binlog事件类型**

MySQL binlog记录的所有操作实际上都有对应的事件类型的，譬如STATEMENT格式中的DML操作对应的是QUERY\_EVENT类型，ROW格式下的DML操作对应的是ROWS\_EVENT类型，如果想了解更多请参考官方文档，有关binlog日志内容不在这里过多赘述，简单介绍一下是为了更好的理解主从复制的细节，下面我们进入正题。

## 3.2 mysqldump全量备份 ##

### 3.2.1 简介 ###

mysqldump - a database backup program. Dumping structure and contents of MySQL databases and tables.  
Usage:  
mysqldump \[OPTIONS\] database \[tables\]  
mysqldump \[OPTIONS\] --databases \[OPTIONS\] DB1 \[DB2 DB3...\]  
mysqldump \[OPTIONS\] --all-databases \[OPTIONS\]  
         
If you do not name any tables following db\_name or if you use the --databases or --all-databases option, entire databases are dumped.  
  
  
常见用法  
       A common use of mysqldump is for making a backup of an entire database:  
           shell> mysqldump db\_name > backup-file.sql  
       You can load the dump file back into the server like this:  
           shell> mysql db\_name < backup-file.sql  
  
       Or like this:  
           shell> mysql -e "source /path-to-backup/backup-file.sql" db\_name  
  
       mysqldump is also very useful for populating databases by copying data from one MySQL server to another:  
           shell> mysqldump --opt db\_name | mysql --host=remote\_host -C db\_name  
  
       It is possible to dump several databases with one command:  
           shell> mysqldump --databases db\_name1 \[db\_name2 ...\] > my\_databases.sql  
  
       To dump all databases, use the --all-databases option:  
           shell> mysqldump --all-databases > all\_databases.sql  
  
       For InnoDB tables, mysqldump provides a way of making an online backup:  
           shell> mysqldump --all-databases --single-transaction > all\_databases.sql  
  
     A. 最简单的用法：  
mysqldump -uroot -pPassword \[database name\]   
> \[dump file\]  
     上述命令将指定数据库备份到某dump文件（转储文件）中，比如：  
mysqldump -uroot -p123 test > test.dump  
     生成的test.dump文件中包含建表语句（生成数据库结构哦）和插入数据的insert语句。  
   
     B. --opt  
     如果加上--opt参数则生成的dump文件中稍有不同：  
     . 建表语句包含drop table if exists tableName  
     . insert之前包含一个锁表语句lock tables tableName write，insert之后包含unlock tables  
  
     C. 跨主机备份  
     使用下面的命令可以将host1上的sourceDb复制到host2的targetDb，前提是host2主机上已经创建targetDb数据库：  
mysqldump --host=host1 --opt sourceDb| mysql --host=host2 -C targetDb  
     -C指示主机间的数据传输使用数据压缩  
   
     D. 只备份表结构  
mysqldump --no-data --databases mydatabase1 mydatabase2 mydatabase3 > test.dump  
     将只备份表结构。--databases指示主机上要备份的数据库。如果要备份某个MySQL主机上的所有数据库可以使用--all-databases选项，如下：  
mysqldump --all-databases  
> test.dump  
   
     E. 从备份文件恢复数据库  
mysql \[database name\] < \[backup file name\]  
  
  
实例  
It is also possible to set up a slave by dumping an existing slave of the master. To do this, use the following procedure on the existing slave:  
  
            1. Stop the slave´s SQL thread and get its current status:  
                   mysql> STOP SLAVE SQL\_THREAD;  
                   mysql> SHOW SLAVE STATUS;  
  
  
            2. From the output of the SHOW SLAVE STATUS statement, the binary log coordinates of the master server from which the new slave should start replicating are  
               the values of the Relay\_Master\_Log\_File and Exec\_Master\_Log\_Pos fields. Denote those values as file\_name and file\_pos.  
  
            3. Dump the slave server:  
                   shell> mysqldump --master-data=2 --all-databases > dumpfile  
  
            4. Restart the slave:  
                   mysql> START SLAVE;  
  
            5. On the new slave, load the dump file:  
                   shell> mysql < dumpfile  
  
            6. On the new slave, set the replication coordinates to those of the master server obtained earlier:  
                   mysql> CHANGE MASTER TO  
                       -> MASTER\_LOG\_FILE = ´file\_name´, MASTER\_LOG\_POS = file\_pos;  
  
               The CHANGE MASTER TO statement might also need other parameters, such as MASTER\_HOST to point the slave to the correct master server host. Add any such  
               parameters as necessary.

### 3.2.2 使用MYSQL命令直接导入导出SQL文件 ###

**1.MYSQL中将数据库导出成SQL文件**  
mysqldump -h\[主机所在IP\] -u\[用户名\] -p \[要导出的数据库\]>\[导出的路径//\[文件名\].sql\]  
  
主从服务器分别为：192.168.50.101和192.168.50.102  
在从服务器上导出主服务器的所有数据库  
root@node102:~\# mysqldump -uroot -ppassword --all-databases -h 192.168.50.101 > alldb.sql  
注：没有锁定主服务器，这里记录的主服务器二进制日志position值可能会大于做mysqldump时的值，这将导致从服务器丢失在此期间的更新。  
  
  
\#清空一下主服务器的 bin-log 日志， （可选: 保险操作， 防止主从 bin-log 日志混乱）  
mysql> reset master;  
   
\#然后备份导出主服务器中现有的 test 数据库  
root@node102:~\# mysqldump -uroot -ppassword --all-databases -h 192.168.50.101 -l -F> alldb.sql  
   
\-F = flush logs, 生成新的日志文件， 包括 bin-log 日志  
\-l = lock 数据库， 防止在导出的时候被写入数据， 完成后自动解锁  
\--lock-all-tables, -x  
           Lock all tables across all databases. This is achieved by acquiring a global read lock for the duration of the whole dump. This option automatically turns off --single-transaction and --lock-tables.  
\#完成后把文件传输给从服务器  
shawn@Shawn:~$ scp /tmp/test.sql 192.168.10.2:/tmp/  
  
**2.从SQL文件中导入数据到MYSQL中**  
用 mysqldump 备份出来的文件是一个可以直接倒入的 SQL 脚本，有两种方法可以将数据导入。  
1 直接用 mysql 客户端导入  
root@node102:~\# mysql -uroot -ppassword <alldb.sql  
  
  
2 用 SOURCE 语法 （实验不成功！！！）  
其实这不是标准的 SQL 语法，而是 mysql 客户端提供的功能，例如：  
SOURCE /tmp/db\_name.sql;  
这里需要指定文件的绝对路径，并且必须是 mysqld 运行用户(例如 nobody)有权限读取的文件。  
  
  
\#清空一下从服务器的 bin-log 日志， （可选: 保险操作）  
mysql> reset master;  
   
\#然后导入主服务器中现有的数据

# 4 设置互为主从 #

## 4.1 设置101为主，102为从 ##

设置101服务器为主：

root@node101:~# mysql -uroot -ppassword
    MariaDB [(none)]> reset master;
    Query OK, 0 rows affected (0.25 sec)
    
    
    MariaDB [(none)]> show master status;
    +------------------+----------+--------------+------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    +------------------+----------+--------------+------------------+
    | mysql-bin.000001 |      245 |              |                  |
    +------------------+----------+--------------+------------------+
    1 row in set (0.00 sec)
    
    
    MariaDB [(none)]> slave stop;
    Query OK, 0 rows affected (0.01 sec)
    
    
    MariaDB [(none)]> reset master;
    Query OK, 0 rows affected (0.20 sec)
    
    
    MariaDB [(none)]> show master status;
    +------------------+----------+--------------+------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    +------------------+----------+--------------+------------------+
    | mysql-bin.000001 |     5641 |              |                  |
    +------------------+----------+--------------+------------------+
    1 row in set (0.00 sec)
    
    
    MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'gnetcp'@'%' IDENTIFIED BY 'password';
    Query OK, 0 rows affected (0.00 sec)
    
    
    MariaDB [(none)]> flush privileges;
    Query OK, 0 rows affected (0.01 sec)
    
    
    MariaDB [(none)]> flush tables with read lock;
    Query OK, 0 rows affected (0.01 sec)

再设置102服务器为从：

root@node102:~# mysql -uroot -ppassword 
    MariaDB [(none)]> slave stop;
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
    
    MariaDB [(none)]> reset master;
    Query OK, 0 rows affected (0.17 sec)
    
    
    MariaDB [(none)]> show master status\G
    *************************** 1. row ***************************
                File: mysql-bin.000001
            Position: 245
        Binlog_Do_DB: 
    Binlog_Ignore_DB: 
    1 row in set (0.00 sec)
    
    
    MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.50.101', MASTER_USER='gnetcp', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=245;
    Query OK, 0 rows affected (0.14 sec)
    
    
    MariaDB [(none)]> slave start;
    Query OK, 0 rows affected (0.00 sec)
    
    
    MariaDB [(none)]> 
    MariaDB [(none)]> 
    MariaDB [(none)]> 
    MariaDB [(none)]> show slave status\G
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.50.101
                      Master_User: gnetcp
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000001
              Read_Master_Log_Pos: 39292
                   Relay_Log_File: mysqld-relay-bin.000002
                    Relay_Log_Pos: 39576
            Relay_Master_Log_File: mysql-bin.000001
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes

## 4.2 再设置102为主，101为从 ##

再设置102为主

MariaDB [(none)]> slave stop;
    Query OK, 0 rows affected (0.11 sec)
    
    
    MariaDB [(none)]> 
    MariaDB [(none)]> reset master;
    Query OK, 0 rows affected (0.17 sec)
    
    
    MariaDB [(none)]> reset master;
    Query OK, 0 rows affected (0.17 sec)
    
    
    MariaDB [(none)]> show master status;
    +------------------+----------+--------------+------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    +------------------+----------+--------------+------------------+
    | mysql-bin.000001 |      245 |              |                  |
    +------------------+----------+--------------+------------------+
    1 row in set (0.01 sec)
    
    
    MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'gnetcp'@'%' IDENTIFIED BY 'password';
    Query OK, 0 rows affected (0.00 sec)
    
    
    MariaDB [(none)]> flush privileges;
    Query OK, 0 rows affected (0.00 sec)
    
    
    MariaDB [(none)]> 
    MariaDB [(none)]> flush tables with read lock;
    Query OK, 0 rows affected (0.00 sec)

再设置101为从

root@node101:~# mysql -uroot -ppassword 
    MariaDB [(none)]> slave stop;
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
    
    MariaDB [(none)]> reset master;
    Query OK, 0 rows affected (0.20 sec)
    
    
    MariaDB [(none)]> show master status;
    +------------------+----------+--------------+------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    +------------------+----------+--------------+------------------+
    | mysql-bin.000001 |      245 |              |                  |
    +------------------+----------+--------------+------------------+
    1 row in set (0.00 sec)
    
    
    MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.50.102', MASTER_USER='gnetcp', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=245;
    Query OK, 0 rows affected (0.16 sec)
    
    
    MariaDB [(none)]> slave start;
    Query OK, 0 rows affected (0.01 sec)
    
    
    MariaDB [(none)]> 
    MariaDB [(none)]> show slave status\G
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.50.102
                      Master_User: gnetcp
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000001
              Read_Master_Log_Pos: 466
                   Relay_Log_File: mysqld-relay-bin.000002
                    Relay_Log_Pos: 529
            Relay_Master_Log_File: mysql-bin.000001
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes

参考：

1  MySQL主从同步那点事儿：[http://linkedkeeper.com/detail/blog.action?bid=1028][http_linkedkeeper.com_detail_blog.action_bid_1028]

2 https://dev.mysql.com/doc/refman/5.7/en/replication.html

3 http://www.cnblogs.com/ximu/archive/2012/03/09/2387521.html

4 http://www.cnblogs.com/yxysuanfa/p/6856870.html

5 https://www.2cto.com/database/201502/374598.html

6 http://www.jb51.net/article/51517.htm

[Center]: /images/20220608/9e93abc410c64591bcbdaab731b462af.png
[Center 1]: /images/20220608/eabb562f58fc4e61ad793c5309ebc6c3.png
[linkedkeeper0_bebc4db8-c555-4944-9e02-ee58d47b70dd.jpg]: /images/20220608/f480719217b44fe08e0323f041a768f3.png
[http_linkedkeeper.com_detail_blog.action_bid_1028]: http://linkedkeeper.com/detail/blog.action?bid=1028