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