mysql5.7 gtid copy installation and troubleshooting

$$$$$$$$$$$$$$$$$$$$$$$$$ Environment Description $$$$$$$$$$$$$$$$$$$$$$$$$

Master: 192.168.31.71

From: 192.168.31.72

Operating system: CentOS 6.6

mysql:5.7.10

Mysql_base/usr/local/mysql

configuration file See attachment, master-slave difference is server_id different, remember to ensure that the master-slave server_id different

&&&&&&&&&&&&&&&&&&&&&& &&& Master-Slave operation $$$$$$$$$$$$$$$$$$$$$$$$$$$$$

Main:

• Initialization

[Email protected] data]#/usr/local/mysql/bin/mysqld--defaults-file=/data/mysql/mysql_3306/etc/my.cnf-- Initialize--user=mysql

• Generate SSL files, 5.7 with OpenSSL secure encryption, can not use this step

[Email protected] data]#/usr/local/mysql/bin/mysql_ssl_rsa_setup--defaults-file=/data/mysql/mysql_3306/etc/ My.cnf

• Change permissions

[Email protected] data]# chown-r mysql:mysql/data/mysql/mysql_3306/data/*

• Start MySQL

[Email protected] data]#/usr/local/mysql/bin/mysqld_safe--DEFAULTS-FILE=/DATA/MYSQL/MYSQL_3306/ETC/MY.CNF &

• Get root temporary password, 5.7 a big improvement, the previous version root default no password

[Email protected] data]# Cat/data/mysql/mysql_3306/data/error.log |grep Temporary

2016-01-25t11:18:48.302304+08:00 1 [Note] A temporary password is generated for [email protected]: wl3jdkl_9_7c

2016-01-25t11:24:12.307468+08:00 0 [Note] innodb:creating shared tablespace for temporary tables

• Change root temporary password

  Mysql> mysql-s/tmp/mysql.sock-uroot-p

  Alter user ' root ' @ ' localhost ' identified by ' password ';

• Create a copy Account

  Mysql> Grant replication Slave,replication Client on * * to ' repl ' @ '% ' identified by ' slavepass ';

From:

• Initialization

[Email protected] data]#/usr/local/mysql/bin/mysqld--defaults-file=/data/mysql/mysql_3306/etc/my.cnf-- Initialize--user=mysql

• Generate SSL files, 5.7 with OpenSSL secure encryption, can not use this step

[Email protected] data]#/usr/local/mysql/bin/mysql_ssl_rsa_setup--defaults-file=/data/mysql/mysql_3306/etc/ My.cnf

• Change permissions

[Email protected] data]# chown-r mysql:mysql/data/mysql/mysql_3306/data/*

• Start MySQL

[Email protected] data]#/usr/local/mysql/bin/mysqld_safe--DEFAULTS-FILE=/DATA/MYSQL/MYSQL_3306/ETC/MY.CNF &

• Get root temporary password, 5.7 a big improvement, the previous version root default no password

[Email protected] data]# Cat/data/mysql/mysql_3306/data/error.log |grep Temporary

2016-01-25t11:18:48.302304+08:00 1 [Note] A temporary password is generated for [email protected]: wl3jdkl_9_7c

2016-01-25t11:24:12.307468+08:00 0 [Note] innodb:creating shared tablespace for temporary tables

• Change root temporary password

  Mysql> mysql-s/tmp/mysql.sock-uroot-p

  Alter user ' root ' @ ' localhost ' identified by ' password ';

• Create a replication relationship

  Change Master to master_host= ' 192.168.31.71 ', master_port=3306,master_user= ' repl ', master_password= ' Slavepass ', Master_auto_position=1;

• Start slave

• Check the replication status, note that at this time Slave_sql_running:no, from the library does not work properly
  

Mysql> show Slave status\g;

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

Slave_io_state:waiting for Master to send event

master_host:192.168.31.71

Master_user:repl

master_port:3306

Connect_retry:60

master_log_file:mysql-bin.000002

read_master_log_pos:741

relay_log_file:relay.000002

relay_log_pos:27063

relay_master_log_file:mysql-bin.000001

Slave_io_running:yes

Slave_sql_running:no

replicate_do_db:

replicate_ignore_db:

Replicate_do_table:

Replicate_ignore_table:

Replicate_wild_do_table:

Replicate_wild_ignore_table:

last_errno:1062

Last_error:could not execute Write_rows event on table mysql.server_cost; Duplicate entry ' row_evaluate_cost ' for key ' PRIMARY ', error_code:1062; Handler error Ha_err_found_dupp_key; The event ' s master Log mysql-bin.000001, End_log_pos 27118

skip_counter:0

exec_master_log_pos:26850

relay_log_space:100809

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

Master_ssl_verify_server_cert:no

last_io_errno:0

Last_io_error:

last_sql_errno:1062

Last_sql_error:could not execute Write_rows event on table mysql.server_cost; Duplicate entry ' row_evaluate_cost ' for key ' PRIMARY ', error_code:1062; Handler error Ha_err_found_dupp_key; The event ' s master Log mysql-bin.000001, End_log_pos 27118

Replicate_ignore_server_ids:

master_server_id:713306

master_uuid:0fbdce0d-c495-11e5-9748-0800279bd495

Master_Info_File:mysql.slave_master_info

sql_delay:0

Sql_remaining_delay:null

Slave_sql_running_state:

master_retry_count:86400

Master_bind:

Last_io_error_timestamp:

last_sql_error_timestamp:160127 09:29:07

MASTER_SSL_CRL:

Master_ssl_crlpath:

retrieved_gtid_set:0fbdce0d-c495-11e5-9748-0800279bd495:1-135

executed_gtid_set:0ee5a5bd-c495-11e5-82f1-0800279bd495:1-134,

0fbdce0d-c495-11e5-9748-0800279bd495:2-30

Auto_position:1

replicate_rewrite_db:

Channel_name:

1 row in Set (0.00 sec)

• Error cause Analysis

  Error 1062, primary key conflict error, the main reason is that at the time of initialization, MySQL metabase will be created and inserted table, master and slave will execute, when doing these actions, all transactions will be assigned the Gtid number, so when the start from the Lord's events will be played back again, when playback to the transaction number is 31 o'clock, From the library primary key error, then why not start error from 1? Because slave_skip_errors = ddl_exist_errors is enabled in the configuration file, the transaction ID before number 30th is the build table statement, followed by the Insert insertion statement.

• Error resolution method One

  Execute from Library

  mysql> stop Slave;

  mysql> Reset Master;

  Mysql> set gtid_purged= "0ee5a5bd-c495-11e5-82f1-0800279bd495:1-134,0fbdce0d-c495-11e5-9748-0800279bd495:1-134 "

  mysql> start slave;

  Principle: Skipping repetitive gtid transactions

• Error Resolution Method II

  Specify Gtid-mode=off during master-slave initialization:

  /usr/local/mysql/bin/mysqld--defaults-file=/data/mysql/mysql_3306/etc/my.cnf--initialize--user=mysql-- Gtid-mode=off
  

  Using Mysqlbinlog to view the Binlog log, you can see that the default Gtid is anonymous:

  [Email protected] logs]# mysqlbinlog-v--base64-output=decode-row./mysql-bin.000001
  

  #160127 10:41:49 server ID 713306 end_log_pos 99259 CRC32 0XF89F3FAF anonymous_gtid last_committed=132 sequence_number=133
  

  SET @ @SESSION. gtid_next= ' ANONYMOUS '/*!*/;

  # at 99259

  The next operation is consistent with the master-slave operation above.

  Principle: The operation of MySQL metadata during initialization is not logged Gtid, all operation Records Gtid after startup, and the Gtid transaction number starts from 1, thus avoiding 1062 errors.

  
  

[Email protected] logs]# mysqlbinlog-v--base64-output=decode-row./mysql-bin.000002

/*!50530 SET @ @SESSION. pseudo_slave_mode=1*/;

/*!50003 SET @[email protected] @COMPLETION_TYPE, completion_type=0*/;

DELIMITER/*!*/;

# at 4

#160127 10:49:41 server ID 713306 end_log_pos 123 CRC32 0x35ee3e2d start:binlog v 4, server v 5.7.9-log created 160127 10:49:41 at startup

# Warning:this Binlog is either on use or was not closed properly.

rollback/*!*/;

# at 123

#160127 10:49:41 Server ID 713306 end_log_pos 154 CRC32 0x21d84665 previous-gtids

# [Empty]

# at 154

#160127 10:50:38 Server ID 713306 end_log_pos 219 CRC32 0x2a912c89 GTID last_committed=0 Sequence_number=1

SET @ @SESSION. gtid_next= ' 7f2c189e-c49f-11e5-b3f9-0800279bd495:1 '/*!*/;

# at 219

#160127 10:50:38 Server ID 713306 end_log_pos 398 CRC32 0x9bf46a0f Querythread_id=2exec_time =0error_code=0

SET timestamp=1453863038/*!*/;

SET @ @session. pseudo_thread_id=2/*!*/;

SET @ @session. Foreign_key_checks=1, @ @session. sql_auto_is_null=0, @ @session. Unique_checks=1, @ @session. autocommit= 1/*!*/;

SET @ @session. sql_mode=1436549120/*!*/;

SET @ @session. auto_increment_increment=1, @ @session. auto_increment_offset=1/*!*/;

/*!\c UTF8 *//*!*/;

SET @ @session. character_set_client=33,@ @session. collation_connection=33,@ @session. collation_server=45/*!*/;

SET @ @session. lc_time_names=0/*!*/;

SET @ @session. collation_database=default/*!*/;

ALTER USER ' root ' @ ' localhost ' identified with ' Mysql_native_password ' as ' *ccddc44f45a76b3b3c5e46a5b0d17e88abc2b4ba '

/*!*/;

# at 398

#160127 10:51:13 Server ID 713306 end_log_pos 463 CRC32 0xaf535af9 GTID last_committed=1 sequence_number=2

SET @ @SESSION. gtid_next= ' 7f2c189e-c49f-11e5-b3f9-0800279bd495:2 '/*!*/;

# at 463

#160127 10:51:13 Server ID 713306 end_log_pos 701 CRC32 0x60a0eced Querythread_id=2exec_time =0error_code=0

SET timestamp=1453863073/*!*/;

GRANT REPLICATION SLAVE, REPLICATION CLIENT on * * to ' repl ' @ '% ' identified with ' Mysql_native_password ' as ' *809534247d21 Ac735802078139d8a854f45c31f3 '

/*!*/;

SET @ @SESSION. gtid_next= ' AUTOMATIC '/* Added by Mysqlbinlog *//*!*/;

DELIMITER;

# End of log file

/*!50003 SET [email protected]_completion_type*/;

/*!50530 SET @ @SESSION. pseudo_slave_mode=0*/;

mysql5.7 gtid copy installation and troubleshooting