MySQL 5.7.9 Master-Slave and enhanced multi-threaded slave configuration

1.master/slave Hosts file

Root Login

Hosts (Master/slave)

127.0.0.1 localhost

10.8.1.5 MDB01

10.8.1.6 SDB01

CP SOFT/MYSQL-5.7.9/SUPPORT-FILES/MY-DEFAULT.CNF/ETC/MY.CNF

2.master Server-id

Root Login

Vi/etc/my.cnf

[Mysqld]

Log-bin=mysql-bin (Datadir/mysql-bin)

Server-id=1

3.Slave Server-id

Vi/etc/my.cnf

[Mysqld]

server-id=2

4.master copy user, lock table prohibit insert

mysql> CREATE USER ' repl ' @ '% ' identified by ' repl ';

Query OK, 0 rows affected (0.18 sec)

Mysql> GRANT REPLICATION SLAVE on * * to ' repl ' @ '% ';

Query OK, 0 rows affected (0.05 sec)

mysql> flush Privileges;

Query OK, 0 rows affected (0.09 sec)

Lock table

Mysql> FLUSH TABLES with READ LOCK;

Query OK, 0 rows affected (0.44 sec)

In master a different session

Mysql> Show master status;

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

| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |

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

|      mysql-bin.000006 |              154 |                  |                   | |

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

1 row in Set (0.00 sec)

Mysql>

5. If there is a synchronous master-slave data

Master

Mysql> UNLOCK TABLES;

6.Slave Set Log Start copy location

Mysql> Change MASTER to

Master_host= ' Mdb01 ',

Master_user= ' Repl ',

Master_password= ' Repl ',

Master_log_file= ' mysql-bin.000006 ',

master_log_pos=154;

Query OK, 0 rows affected, 2 warnings (0.09 sec)

7. Start slave

Mysql> START SLAVE;

Query OK, 0 rows affected (0.07 sec)

8. Check the slave status

Mysql> show Slave status\g;

1. Row ***************************

Slave_io_state:waiting for Master to send event

Master_host:mdb01

Master_user:repl

master_port:3306

Connect_retry:60

master_log_file:mysql-bin.000006

read_master_log_pos:319

relay_log_file:sdb01-relay-bin.000003

relay_log_pos:485

relay_master_log_file:mysql-bin.000006

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:319

relay_log_space:692

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:1

master_uuid:42ad8740-7e88-11e5-83de-000c29270868

Master_info_file:/opt/mysql/data/master.info

sql_delay:0

Sql_remaining_delay:null

Slave_sql_running_state:slave have 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:

1 row in Set (0.00 sec)

ERROR:

No query specified

Mysql>

Configuration issues that you may encounter:

Master-Slave The slave_io_running:null process may not start if the two auto.cnf files generated by the same virtual machine copy are server-uuid the same.

Error log prompt:

[ERROR] Slave I/O for Channel ': Fatal error:the Slave I/O thread stops because master and Slave hav

e equal MySQL server UUIDs; These uuids must is different for replication to work. error_code:1593

You need to modify the Auto.cnf file in any of the MySQL database datadir directories to change to a different value, and then stop slave after you restart the MySQL service, and then start the slave to run normally.

[Auto]

server-uuid=42ad8740-7e88-11e5-83de-000c29270869

9. master-Slave test

1). master

mysql> CREATE DATABASE TestDB;

mysql> use TestDB;

Mysql> CREATE TABLE User (

UID int auto_increment,

Data json,primary key (UID)

);

Query OK, 0 rows affected (0.27 sec)

mysql> INSERT into user values (NULL, ' {' "name": "David", "Mail": "[email protected]", "Address": "Shangahai"} ');

Query OK, 1 row affected (0.64 sec)

mysql> INSERT into user values (NULL, ' {' "name": "Amy", "Mail": "[email protected]"} ');

Query OK, 1 row affected (0.14 sec)

Mysql> Select Data-> "$.name" from user;

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

| Data-> "$.name" |

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

| "David" |

| "Amy" |

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

2 rows in Set (0.16 sec)

Mysql>

2). Slave

mysql> show databases;

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

| Database |

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

| Information_schema |

| MySQL |

| Performance_schema |

| SYS |

| TestDB |

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

5 rows in Set (0.00 sec)

Mysql> use TestDB

Database changed

Mysql> Show tables;

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

| Tables_in_testdb |

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

| user |

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

1 row in Set (0.00 sec)

mysql> desc User;

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

| Field | Type | Null | Key | Default | Extra |

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

| UID | Int (11) | NO | PRI | NULL | auto_increment |

| Data | JSON |     YES | |                NULL | |

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

2 rows in Set (0.01 sec)

mysql> SHOW CREATE TABLE user\g;

1. Row ***************************

Table:user

Create table:create Table ' user ' (

' UID ' int (one) not NULL auto_increment,

' Data ' json DEFAULT NULL,

PRIMARY KEY (' uid ')

) Engine=innodb auto_increment=3 DEFAULT Charset=utf8

1 row in Set (0.00 sec)

ERROR:

No query specified

Mysql> Select data, "$.name" from user;

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

| Data, "$.name" |

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

| "David" |

| "Amy" |

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

2 rows in Set (0.03 sec)

Mysql>

10.Slave Parallel Replication Configuration

Enhanced multi-threaded slave configuration, configure and restart the MySQL server.

Said so much, to open enhanced multi-threaded slave is actually very simple, just according to the following settings:

# slave

Slave-parallel-type=logical_clock

Slave-parallel-workers=16

Master_info_repository=table

Relay_log_info_repository=table

Relay_log_recovery=on

Parallel Replication Monitoring

Replication monitoring is still available through show SLAVE status\g, but MySQL 5.7 has the following metadata tables in the Performance_schema architecture, where users can monitor more carefully:

Mysql> Show tables like ' replication% ';

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

| Tables_in_performance_schema (replication%) |

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

| replication_applier_configuration |

| Replication_applier_status |

| Replication_applier_status_by_coordinator |

| Replication_applier_status_by_worker |

| replication_connection_configuration |

| Replication_connection_status |

| Replication_group_member_stats |

| Replication_group_members |

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

8 rows in Set (0.00 sec)

Slave Status:

mysql> SHOW SLAVE status\g;

1. Row ***************************

Slave_io_state:waiting for Master to send event

Master_host:mdb01

Master_user:repl

master_port:3306

Connect_retry:60

master_log_file:mysql-bin.000006

read_master_log_pos:1196

relay_log_file:sdb01-relay-bin.000005

relay_log_pos:320

relay_master_log_file:mysql-bin.000006

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:1196

relay_log_space:527

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:1

master_uuid:42ad8740-7e88-11e5-83de-000c29270868

Master_Info_File:mysql.slave_master_info

sql_delay:0

Sql_remaining_delay:null

Slave_sql_running_state:slave have 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:

1 row in Set (0.00 sec)

ERROR:

No query specified

mysql> show databases;

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

| Database |

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

| Information_schema |

| MySQL |

| Performance_schema |

| SYS |

| TestDB |

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

5 rows in Set (0.01 sec)

Mysql> Use Performance_schema

Reading table information for completion of table and column names

Can turn off this feature to get a quicker startup with-a

Database changed

Mysql> Show tables like ' replication% ';

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

| Tables_in_performance_schema (replication%) |

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

| replication_applier_configuration |

| Replication_applier_status |

| Replication_applier_status_by_coordinator |

| Replication_applier_status_by_worker |

| replication_connection_configuration |

| Replication_connection_status |

| Replication_group_member_stats |

| Replication_group_members |

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

8 rows in Set (0.00 sec)

Mysql>

Reference documents

Http://www.innomysql.net/article/16317.html

This article is from the "Yiyi" blog, make sure to keep this source http://heyiyi.blog.51cto.com/205455/1708255

MySQL 5.7.9 Master-Slave and enhanced multi-threaded slave configuration