log-slave-updates  级联复制 配合--log-bin一起使用

master-connect-retry 主库失联重试时间

read-only 只读

replicate-do-db

replicate-do-table

replicate-ignore-db

replicate-ignore-table

replicate-wild-do-table

slave-skip-errors 自动跳过错误号


让主库停下来,从库追一下:

主库:

mysql> flush tables with read lock;

Query OK, 0 rows affected (0.00 sec)


mysql> show master status;

+----------------------+----------+--------------+------------------+-------------------+

| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+----------------------+----------+--------------+------------------+-------------------+

| mysql3306-bin.000012 |     1166 |              |                  |                   |

+----------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)


mysql> 

从库:

mysql> select master_pos_wait('mysql3306-bin.000012','1166');

+------------------------------------------------+

| master_pos_wait('mysql3306-bin.000012','1166') |

+------------------------------------------------+

|                                              0 |

+------------------------------------------------+

1 row in set (0.00 sec)


mysql> 

这个select语句会阻塞直到从库达到指定的日志文件和偏移量,查询返回0,则主从同步。

主库:

mysql> unlock tables;

Query OK, 0 rows affected (0.00 sec)




mysql> set global sql_slave_skip_counter = N 跳过主库的两个更新语句


解决log event entry exceeded max_allowed_packet的处理

主从库上增加;

mysql> show variables like "%max_allowed_packet%";

+--------------------------+------------+

| Variable_name            | Value      |

+--------------------------+------------+

| max_allowed_packet       | 4194304    |

| slave_max_allowed_packet | 1073741824 |

+--------------------------+------------+

2 rows in set (0.00 sec)


mysql> set @@global.max_allowed_packet  = 16777216;

Query OK, 0 rows affected (0.00 sec)


mysql> 


自增长变量的解决

master1:auto_increment_increment = 2,auto_increment_offset = 1;(1,3,5,7)

master2:auto_increment_increment = 2,auto_increment_offset = 0;(2,4,6,8)


实例:

mysql> create table auto_table(id int(5) not null auto_increment,num int(2) ,primary key (id));

Query OK, 0 rows affected (0.34 sec)


mysql> show variables like "%auto_increment%";

+--------------------------+-------+

| Variable_name            | Value |

+--------------------------+-------+

| auto_increment_increment | 1     |

| auto_increment_offset    | 1     |

+--------------------------+-------+

2 rows in set (0.00 sec)


mysql> set session auto_increment_increment = 20;        

Query OK, 0 rows affected (0.00 sec)


mysql> set session auto_increment_offset  = 7;      

Query OK, 0 rows affected (0.00 sec)


mysql> show variables like "%auto_increment%";   

+--------------------------+-------+

| Variable_name            | Value |

+--------------------------+-------+

| auto_increment_increment | 20    |

| auto_increment_offset    | 7     |

+--------------------------+-------+

2 rows in set (0.00 sec)


mysql> 

mysql> insert into auto_table(num)  values(1),(2);

Query OK, 2 rows affected (0.14 sec)

Records: 2  Duplicates: 0  Warnings: 0


mysql> insert into auto_table(num)  values(3),(4);

Query OK, 2 rows affected (0.25 sec)

Records: 2  Duplicates: 0  Warnings: 0


mysql> select * from auto_table; 

+----+------+

| id | num  |

+----+------+

|  7 |    1 |

| 27 |    2 |

| 47 |    3 |

| 67 |    4 |

+----+------+

4 rows in set (0.00 sec)


mysql> 


从7开始每次加20.


mysql> alter table auto_table add column createtime datetime;

Query OK, 0 rows affected (0.62 sec)

Records: 0  Duplicates: 0  Warnings: 0


mysql> insert into  auto_table(num,createtime) values(5,now()) ;

Query OK, 1 row affected (0.04 sec)


mysql> select * from auto_table;                                

+----+------+---------------------+

| id | num  | createtime          |

+----+------+---------------------+

|  7 |    1 | NULL                |

| 27 |    2 | NULL                |

| 47 |    3 | NULL                |

| 67 |    4 | NULL                |

| 87 |    5 | 2016-05-10 18:32:18 |

+----+------+---------------------+

5 rows in set (0.00 sec)


mysql> 


如何提高复制的性能:

  1. 通过拆分减少一个从库上需要数据同步的表来解决,不同的从库复制不同的库。

  2. 从库并行更新,从库更新时启动多个SQL线程。

mysql>  show variables like "%workers%";

+------------------------+-------+

| Variable_name          | Value |

+------------------------+-------+

| slave_parallel_workers | 0     |

+------------------------+-------+

1 row in set (0.00 sec)


mysql> set global slave_parallel_workers = 2; 

Query OK, 0 rows affected (0.00 sec)


mysql>  show variables like "%workers%";      

+------------------------+-------+

| Variable_name          | Value |

+------------------------+-------+

| slave_parallel_workers | 2     |

+------------------------+-------+

1 row in set (0.00 sec)