MySQL主从复制技术（纯干货）

1.复制配置

    主机一定要开启二进制日志（这里建议配置RBR）

    每个主机和每个从机一定要配置一个位移的id，即server-id

    每个从机配置一定要包含主机名称，日志名称，和位置号，在5.5以后已经只允许使用session动态配置。

 

1.1 怎样配置主从复制

       

        复制主机配置

        [mysqld]

        log-bin=mysql-bin

        server-id=1 范围 1-2^32-1   如果设置成0 那么拒绝所有从服务器连接

        innodb_flush_log_at_trx_commit=1

        sync_binlog=1

 

        复制从机配置

        [mysqld]

        server-id=2

 

1.2 在主机上创建复制账户

       mysql>CREATE USER 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';

       mysql>GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.mydomain.com';

 

1.3已经存在数据时候搭建主从复制集群，使用mysqldump 创建数据快照

      mysql>mysqldump --all-databases --master-data > dbdump.db

      单一数据库

      mysql> mysqldump -B database_name --master-data > database_name.sql

     如果没有master-data 那么就要使用FLUSH TABLES WITH READ LOCK 锁住所有表

 

1.4 5.5版本在从机上面配置主机要使用CHANGE MASTER TO 语句：

    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;

 

注释：以下为复制指定数据库选项

--replicate-do-db, --replicateignore-db, and --replicate-rewrite-db

 

 

1.5  GTID模式的研究与维护

 

同步过程中修改停机维护行为步骤，或者添加GTIDs 功能

step 1 mysql> SET @@global.read_only = ON; 确保已经完全同步然后进行步骤2

step 2 shell mysqladmin --uusername -p shutdown 

step 3 (使用GTIDs)shell> mysqld_safe --gtid_mode=ON --log-bin --log-slave-updates --enforce-gtid-consistency &  后面的可以在配置文件中配置

 

注入一个空事务

SET GTID_NEXT='aaa-bbb-ccc-ddd:N';

BEGIN;

COMMIT;

SET GTID_NEXT='AUTOMATIC';

 

一旦已经事务标识正常以后要在从机上面刷新并清理日志 N 是当前正在使用的二进制文件

FLUSH LOGS;（刷新日志会强制生成一个新的二进制文件，下面的N便是指这个文件）

PURGE BINARY LOGS TO 'master-bin.00000N';（注：清理的二进制文件并不包括当前N这个文件）

 

 

GTIDs的限制：（重点）

因为GTID复制是基于事务的，一些mysql本身特点在使用GTID时候可能不支持。

 

1.涉及到关于非事务存储引擎的更新操作，因为混合使用可能导致同一个事物出现多个GTIDs值

 解决：也就只使用Innodb就可以

 

2.CREATE TABLE ... SELECT 语句，这个语句在GTID模式下是被禁止使用的，因为这对于GTID是不安全的，当使用基于行的复制时，这个语句实际是是两个事件，一个创建表的事件，一个是从原表插入行，实际上是两个事件，但是在从机会使用一个GTID 去接收，也就是只接收了创建表的命令，而丢失了导入数据，因此这个语句是不被支持的

 

3.临时表  CREATE TEMPORARY TABLE 和 DROP TEMPORARY TABLE 在事务内部不被支持，只能在事务外部并且autocommit=1 才可以

 

如果开启了--enforce-gtid-consistency 选项 那么在GTID模式下任何时候都不可以

 

4.开启--enforce-gtid-consistency 就可以防止以上错误语句的执行，会以错误形式返回。

 

GTID 由于临时表的限制不可取。使用一般复制

 

 

Command-Line Format --read-only

Name read_only

Variable

Scope

Global

System Variable

Dynamic

Variable

Yes

Permitted Values Type boolean

Default false

Cause the slave to permit no updates except from slave threads or from users having the SUPER

privilege. On a slave server, this can be useful to ensure that the slave accepts updates only from its

master server and not from clients. This variable does not apply to TEMPORARY tables.

 

--read-only 建议用在从机配置上，非主机和不具有super权限的用户无法更新从机

 

1.6 检查复制状态

    SHOW SLAVE STATUS

 

 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  # IO线程正常

Slave_SQL_Running: Yes  # SQL线程正常  知道原理就知道这两个的作用了

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 # 这个不能用来作为主从延迟的判断标准，不准确的， 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

 

1.7 暂停主从复制ÿ