mysql复制

复制原理：

(1)    master将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events）；
      (2)    slave将master的binary log events拷贝到它的中继日志(relay log)；
      (3)    slave重做中继日志中的事件，将更改应用到自己的数据上。

master配置：

[mysqld]
log_bin=mysql-bin
server-id=135
1. log-bin:开启二进制日志，该日志是在事务提交时写日志文件的。默认是1G，后面加001,002这样的后缀
2. server-id，唯一标识主机，mysql主从每个mysql实例配置都不一样就行。这个值默认是0，如果是0，主服务器拒绝任何从服务器的连接。
3. innodb_flush_log_at_trx_commit=1 and sync_binlog=1 这两个参数控制着二进制日志刷新速度
4. binlog_format=ROW控制着日志格式

5，expire_logs_days=1 # 二进制日志自动删除的天数

slave配置：
[mysqld]
server-id=2
唯一，并且非0，否则不连接主服务器

创建复制用户
-- 主从都需要 如果需要从接管主时
grant replication slave,replication client on *.* to repl@'%' identified by '123456'; -- 如果要控制复制的主机请在用户名后添加主机限定
或者：
CREATE USER 'repl'@'%' IDENTIFIED BY '123456';GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
在master获取日志坐标：
FLUSH TABLES WITH READ LOCK;
另开会话：
SHOW MASTER STATUS;
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
创建master数据快照：
mysqldump --all-databases --master-data -uroot -p > /tmp/dbdump.sql
1.-master-data此选项会在备份中添加 CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=154;
2.当然还有冷备，关master服务，copy data所有文件，删除auto.cnf，复制到slave

创建salve复制：
1，首先解锁master
  unlock tables;
2,上传master的快照备份dbdump.sql到slave的/tmp下
3，salve的my.cnf配置mysqld块中增加
skip-slave-start=true
read_only=ON
relay-log=relay-bin
relay-log-index=relay-bin.index

# expire_logs_days=1 # 二进制日志自动删除的天数

# log-slave-updates=1

# log_bin=mysql-bin

# replicate_do_db=xxx


说明:skip-slave-start=true,跳过slave线程启动，方便调试
    其他的中断日志配置，方便复制到其他salve,因为默认是主机名开关的文件。

    read_only，我开启的只读模式

slave没有必要开启二进制日志，但是如果slave为其它slave的master，需要设置，并且log-slave-updates需要设置(因为从库通过I0线程读取主库二进制日志文件，然后通过SQL线程写入的数据，是不会记录binlog日志)

注意：

为了避免,跨库忽略第二条语句问题
use mysql;
update db1.t1 set col1=xxx;
需要避免在复制参数中设置replicate_do_db或者replicate_ignore_db
可以在slave上设置replicate_wild_do_table和replicate_wild_ignore_table
如：
replicate_wild_do_table=db1.%
replicate_wild_ignore_table=db2.%

4，启动slave
 进入mysql，show processlist;是看不到复制线程的，show slave status \G显示也是空
5，设置slave的master信息
CHANGE MASTER TO
MASTER_HOST='192.168.9.119',
MASTER_USER='repl',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=154;

最后的MASTER_LOG_FILE='mysql-bin.000002',

MASTER_LOG_POS=154可以省略，因为后面的导入数据中如果有CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=154;

如果想重置配置，使用reset slave;

6.导入数据到slave，注意这里要备份下auto.cnf 防止后来出现问题
bin/mysql -uroot -p < /tmp/dbdump.sql
7，查看slave状态
在slave中执行
mysql> show slave status \G
mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 192.168.97.118
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 154
               Relay_Log_File: relay-bin.000008
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: No
            Slave_SQL_Running: No
              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: 154
              Relay_Log_Space: 734
              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: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 135
                  Master_UUID: 49bb8923-8eb2-11e6-b687-000c29b86831
             Master_Info_File: /usr/local/mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State:
           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
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)
注意：Slave_IO_Running: No
    Slave_SQL_Running: No

复制线程还没有启动

解释：

上边的Slave_IO_Running和Slave_SQL_Running就是这两个线程的状态了，IO线程负责从主服务器上拉取日志，SQL线程负责解析执行日志中的内容

如果出现：Error_code: 1032; ha ndler error HA_ERR_KEY_NOT_FOUND; the event's master，可以忽略一条错误的操作如下：

STOP SLAVE; 
SET GLOBAL sql_slave_skip_counter =1; #表示跳过一步错误，后面的数字可变 
START SLAVE;  

8：启动slave
start slave;
再次查看：
mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.97.118
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 154
               Relay_Log_File: relay-bin.000008
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql-bin.000002
             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: 154
              Relay_Log_Space: 734
              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: 135
                  Master_UUID: 49bb8923-8eb2-11e6-b687-000c29b86831
             Master_Info_File: /usr/local/mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           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
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)
看看复制进程
mysql> show processlist;
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User        | Host      | db   | Command | Time | State                                                  | Info             |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
|  5 | root        | localhost | NULL | Query   |    0 | starting                                               | show processlist |
|  6 | system user |           | NULL | Connect |   22 | Waiting for master to send event                       | NULL             |
|  7 | system user |           | NULL | Connect |  109 | Slave has read all relay log; waiting for more updates | NULL             |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
3 rows in set (0.00 sec)

最后创建数据库，表，执行一些操作，看看效果

9，一些命令

停止主从服务，对应的也有开启服务

STOP SLAVE IO_THREAD;    #停止IO进程
STOP SLAVE SQL_THREAD;    #停止SQL进程
STOP SLAVE;        #停止IO和SQL进程

10，辅助工具

percona-toolkit