复制的粒度

  • 可以复制整个库、单个库或者某个库中的某个表

应用

  • 可以复制到多个从库,从而提高读取数据的性能,同时由于降低了主库的压力,提高主库的写入性能
  • 复制到从库,在从库上做数据备份,避免对主库造成影响
  • 复制到从库,在从库上进行数据分析,避免对主库造成影响
  • 远程传输数据

复制方法

  • binary log file position based replication
  • GTID-based replication

同步方法

replication format

Replication Notes and Tips

MySQL 5.7 FAQ: Replication

Replication Implementation

binary log file position based replication

  • master configuration
    • 设置log-bin
    • 设置a unique server ID.
    • 重启服务器
    [mysqld]
    log-bin=mysql-bin
    server-id=1
    
  • Obtaining the Replication Master Binary Log Coordinates (获取master log-bin filename 和 position)
    • 如果master没有开启过bin-log,那么slave的log-bin file 和position 分别设置为 '' 和 0
    • 以下是master曾经开启过bin-log的设置
    #加读锁,阻止所有写入,只允许读取
    mysql> FLUSH TABLES WITH READ LOCK;
    mysql > SHOW MASTER STATUS;
    +------------------+----------+--------------+------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    +------------------+----------+--------------+------------------+
    | mysql-bin.000003 | 73       | test         | manual,mysql     |
    +------------------+----------+--------------+------------------+
    # File 列是slave所需要的 bin-log的 file name。
    # Position 列是slave所需要的position
    # 如果master有数据此时接下来需要先把旧数据导入到slave中
    
  • Creating a User for Replication
    • 设置Replication User不是必须的,但是这样可以防止非replication用户暴露在master info 文件中
    mysql> CREATE USER 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';
    mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.mydomain.com';
    
  • slave configuration
    • Before you proceed
      • Setting the Replication Master Configuration
      • Obtaining the Replication Master Binary Log Coordinates
      • on the Master
      mysql> UNLOCK TABLES;
      
    • Setting the Replication Slave Configuration
    [mysqld]
    server-id=2
    
    • restart server to make changes
    • Setting the Master Configuration on the slave
    mysql> CHANGE MASTER TO
    ->     MASTER_HOST='master_host_name',
    ->     MASTER_USER='replication_user_name',
    ->     MASTER_PASSWORD='replication_password',
    ->     MASTER_LOG_FILE='recorded_log_file_name',
    ->     MASTER_LOG_POS=recorded_log_position;
    
    • Setting up Replication with Existing Data
      • Start the slave, using the --skip-slave-start option so that replication does not start.
      • Import the dump file
      shell> mysql < fulldb.dump
      
      • Start the slave threads
      mysql> START SLAVE;
      
    • Depends on --master-info-repository or --master-info-repository=FILE, the Master Slave info stored in file master.info and relay-log.info Or table master_slave_info

Replication and Binary Logging Options and Variables”.

Common Replication Administration Tasks

  • Once started, the replication process should require little administration or monitoring. However, for advice on common tasks that you may want to execute, see Section 16.1.7, “Common Replication Administration Tasks”.
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: master1
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 931
               Relay_Log_File: slave1-relay-bin.000056
                Relay_Log_Pos: 950
        Relay_Master_Log_File: mysql-bin.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: 931
              Relay_Log_Space: 1365
              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: 0
  • The key fields from the state report

  • Slave_IO_State: The current status of the slave.

  • Slave_IO_Running: Whether the I/O thread for reading the master's binary log is running.

  • Slave_SQL_Running: Whether the SQL thread for executing events in the relay log is running.

  • Last_IO_Error, Last_SQL_Error: The last errors registered by the I/O and SQL threads when processing the relay log.

  • Seconds_Behind_Master: The number of seconds that the slave SQL thread is behind processing the master binary log.

  • (Master_Log_file, Read_Master_Log_Pos): Coordinates in the master binary log indicating how far the slave I/O thread has read events from that log.

  • (Relay_Master_Log_File, Exec_Master_Log_Pos): Coordinates in the master binary log indicating how far the slave SQL thread has executed events received from that log.

  • (Relay_Log_File, Relay_Log_Pos): Coordinates in the slave relay log indicating how far the slave SQL thread has executed the relay log. These correspond to the preceding coordinates, but are expressed in slave relay log coordinates rather than master binary log coordinates.

mysql> SHOW SLAVE HOSTS;
+-----------+--------+------+-------------------+-----------+
| Server_id | Host   | Port | Rpl_recovery_rank | Master_id |
+-----------+--------+------+-------------------+-----------+
|        10 | slave1 | 3306 |                 0 |         1 |
+-----------+--------+------+-------------------+-----------+
1 row in set (0.00 sec)
  • Pausing Replication on the Slave
mysql> STOP SLAVE;
mysql> STOP SLAVE IO_THREAD;
mysql> STOP SLAVE SQL_THREAD;

mysql> START SLAVE;
mysql> START SLAVE IO_THREAD;
mysql> START SLAVE SQL_THREAD;

GTID-based replication

Replication Solutions

mysql 精彩博客

http://cenalulu.github.io/

参考

http://blog.csdn.net/hguisu/article/details/7325124
https://www.howtoforge.com/how-to-repair-mysql-replication