MySQL复制

复制介绍

MySQL复制:

• MySQL replication=异步复制
• 完全同步复制,PXC是同步复制--master和slave,没有区别

复制是单向的,,只能从master复制到slave上

slave上对于master包含的数据不能进行写操作

一组复制结构中可以有多个slave,对于master一般场景推荐只有一个

原理:

1.master用户写入数据,生成event记到binary log中

2.slave I/O thread接收master传来的binlog写入relay log中

3.slave SQL thread按顺序应用,重现master上的用户操作

　　slave 在做日志重放的是串行的io线程是一个(是串行的),sql线程和db一样多(5.6基于库的并行同步)

复制的使用价值

利用从库做读能力的提升

利用从库做master故障的接管

利用从库做备份减少对业务的影响

利用复制升级

利用slave进行特殊SQL统计

 

环境规范

1.1组主从里面端口号要一致

2.要求是每个实例上的server-id为IP最后一位+端口号

3.主从尽可能是版本一致，严禁主的版本高从的版本低，binlog传输到slave有可能应用不了

4.复制建一个帐号：grant replication slave on *.* to 'repl'@'%' identified by 'repl4slave';

5.拿到主库的一致性备份

     

/usr/local/mysql/bin/mysqldump -u root -p -h 127.0.0.1 --master-data=2  --single-transaction --databases glc > dumpglc.sql
 
general_log内容：
###########################
160421 14:49:51          2 Connect         root@127.0.0.1 on
                       2 Connect         Access denied for user 'root'@'127.0.0.1' (using password: YES)
160421 14:50:13          3 Connect         root@127.0.0.1 on
                       3 Query    /*!40100 SET @@SQL_MODE='' */                                                                      #设置SQL模式
                       3 Query    /*!40103 SET TIME_ZONE='+00:00' */                                                                 #设置时区
                       3 Query    SHOW STATUS LIKE 'binlog_snapshot_%'                                                               #查看当前binlog文件名及pos点，该信息用于复制。（ --master-data=2 参数）
                       3 Query    SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ                                            #设置当前会话的事物隔离级别为RR，确保本次会话(dump)时，不会看到其他会话已经提交了的数据。
                       3 Query    START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */    
                       3 Query    SHOW VARIABLES LIKE 'gtid\_mode'                                                                   #查看gtid模式状态
                       3 Query    SHOW STATUS LIKE 'binlog_snapshot_%'                                                               #查看当前binlog文件名及pos
                       3 Query    UNLOCK TABLES                                                                                      #对表进行解锁
                       3 Query    SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG'
AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('glc'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE ORDER BY LOGFILE_GROUP_NAME       #做什么用？
                       3 Query    SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_T
YPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('glc')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME       #做什么用？
                       3 Query    SHOW VARIABLES LIKE 'ndbinfo\_version'
                       3 Init DB  glc
                       3 Query    SHOW CREATE DATABASE IF NOT EXISTS `glc`
                       3 Query    SAVEPOINT sp
                       3 Query    show tables
                       3 Query    show table status like 'MyClass'
                       3 Query    SET SQL_QUOTE_SHOW_CREATE=1     
 #sql_quote_show_create，有两个值（1，0），默认是1，表示表名和列名会用``包着的。这个服务器参数只可以在session级别设置，不支持global设置的(不支持my.cnf设置)。
                       3 Query    SET SESSION character_set_results = 'binary'                  #设置会话级别的查询结果字符集
                       3 Query    show create table `MyClass`
                       3 Query    SET SESSION character_set_results = 'utf8'                    #设置会话级别的查询结果字符集
                       3 Query    show fields from `MyClass`
                       3 Query    SELECT /*!40001 SQL_NO_CACHE */ * FROM `MyClass`              #获取表的数据，SQL_NO_CACH  确保不会读取缓存里的数据
                       3 Query    SET SESSION character_set_results = 'binary'
                       3 Query    use `glc`
                       3 Query    select @@collation_database                                   #查看排序
                       3 Query    SHOW TRIGGERS LIKE 'MyClass'                                  #查看触发器
                       3 Query    SET SESSION character_set_results = 'utf8'                    #设置查询结果集
                       3 Query    ROLLBACK TO SAVEPOINT sp                                      #回滚到 SAVEPOINT sp
                       3 Query    show table status like 't1'
                       3 Query    SET SQL_QUOTE_SHOW_CREATE=1
                       3 Query    SET SESSION character_set_results = 'binary'
                       3 Query    show create table `t1`
                       3 Query    SET SESSION character_set_results = 'utf8'
                       3 Query    show fields from `t1`
                       3 Query    SELECT /*!40001 SQL_NO_CACHE */ * FROM `t1`
                       3 Query    SET SESSION character_set_results = 'binary'
                       3 Query    use `glc`
                       3 Query    select @@collation_database
                       3 Query    SHOW TRIGGERS LIKE 't1'
                       3 Query    SET SESSION character_set_results = 'utf8'
                       3 Query    ROLLBACK TO SAVEPOINT sp
                       3 Query    RELEASE SAVEPOINT sp   
                       3 Quit      
Dump表结构的时间字符集改成
SET SESSION character_set_results = 'binary'
           3 Query   use `glc`
           3 Query   select @@collation_database
           3 Query   SHOW TRIGGERS LIKE 't1'
           3 Query   SET SESSION character_set_results = 'utf8'
           3 Query   ROLLBACK TO SAVEPOINT sp
 
mysqldump --master-data=2  --single-transaction

简单的主从配置

基本环境介绍

MySQL版本：MySQL5.6.27

角色	Ip：port	Server-id	必备条件
Master	192.168.247.12	123316	启用log-bin主库上创建复制用户
Slave	192.168.247.27	273316

 

核心配置

Master

log-bin
server-id
#禁掉gtid
gtid_mode=off
主库上创建复制用户
grant replication slave on *.* to ‘repl’@‘%’ identified by ‘’
flush privileges
# mysqldump -S /tmp/mysql3316.sock  --master-data=2 --single-transaction -A >20160516full.sql
# scp 20160516full.sql  192.168.247.12:/tmp/

Slave

# mysql -S /tmp/mysql3316.sock  </tmp/20160516full.sql  
--/tmp/20160516full.sql 文件中找到-- -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=215;
master 创建用户用于复制
grant replication slave on *.* to 'repl'@'192.168.247.%' identified by '123456'
slave执行change master语句
CHANGE MASTER TO  master_host='192.168.247.12',
    master_user='repl',
    master_password='123456',
master_port=3316, MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=215;
start slave;
show slave statusG;
*root@localhost:mysql3316.sock  09:27:10 [(none)]>show slave statusG;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.247.12
                  Master_User: repl
                  Master_Port: 3316
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 425
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 493
        Relay_Master_Log_File: mysql-bin.000003
             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: 425
              Relay_Log_Space: 666
              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: 123316
                  Master_UUID: f9f3216c-1865-11e6-b1f4-000c29b01c31
             Master_Info_File: /data/mysql/mysql3316/data/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)

ERROR:
No query specified