MySQL master-Slave several parameters

Log-slave-updates cascade replication with--log-bin

Master-connect-retry Master Library out-of-union retry time

Read-only Read Only

Replicate-do-db

Replicate-do-table

Replicate-ignore-db

Replicate-ignore-table

Replicate-wild-do-table

Slave-skip-errors Auto-Skip Error number

Let the main library stop and chase from the library:

Main Library:

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>

From library:

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>

This SELECT statement blocks until the specified log file and offset is reached from the library, and the query returns 0, the master-slave synchronization.

Main Library:

mysql> unlock tables;

Query OK, 0 rows Affected (0.00 sec)

mysql> Set Global Sql_slave_skip_counter = N Skips two update statements from the main library

Resolving the handling of Log event entry exceeded Max_allowed_packet

Increase in master/slave library;

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>

Resolution of self-growth variables

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)

Instance:

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>

Starting from 7, add 20 each time.

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>

How to improve the performance of replication:

1. By splitting down a table that requires data synchronization from the library, different libraries are copied from the library.

2. Updates from the library in parallel, starting multiple SQL threads when updating from the library.

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 master-Slave several parameters