mysql 主從的幾個參數

標籤：mysql 主從的幾個參數

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)

mysql 主從的幾個參數