Mysql-5.6 master-slave replication and errors

Mysql-5.6 master-slave replication and encountered errors mysql replication process: each write operation, it will save a copy to its own database, at the same time, this write operation will also be stored in a binary log file and saved as events. Therefore, in this database, each time the front-end data executes a write operation or an operation that may cause modifications, it will save an event. We will send this event to another server through port 3306 of the mysql server, another server receives the event and saves it to a local log file. Then, it reads the event and executes it locally, and save it in the database. This process is called mysql replication. We will not talk about the installation of mysql. We will directly start the configuration process of master-slave replication: 1. Enable the binary log function of master and slave, that is, in the mysql master configuration file/usr/local/mysql/etc/my. in cnf, add log_bin = mysql-bin, set server_id of master to 1, and server_id of slave to 2. the following is the master configuration file [root @ localhost ~] # Cat/usr/local/mysql/etc/my. cnf | grep-v ^ # | grep-v ^ $ [mysqld] server_id = 1 log_bin = mysql-bin log-bin-index = mysql-bin.index log-error =/var/log/ mysql/mysql-error.log general_log = 1 general_log_file =/var/log/mysql. log user = mysql basedir =/usr/local/mysql datadir =/datadir port = 3306 socket =/var/lib/mysql/mysql5.socksql _ mode = NO_ENGINE_SUBSTITUTION, STRICT_TRANS_TABLES Configuration File [root @ wordpress ~] # Cat/usr/local/mysql/etc/my. cnf | grep-v ^ # | grep-v ^ $ [mysqld] server_id = 2 log_bin = mysql-bin log-bin-index = mysql-bin.index general_log = 1 general_log_file =/var/log /mysql. log-error =/var/log/mysql. error basedir =/usr/local/mysql datadir =/database port = 3306 socket =/var/run/mysqld/mysql. socksql_mode = NO_ENGINE_SUBSTITUTION, STRICT_TRANS_TABLES 2. On the master server, set an account from the database and use REP Lication slave grant permissions mysql> grant replication slave on *. * to 'TT' @ '192. 168.254.153 'identified by '123456'; Query OK, 0 rows affected (123456 sec) mysql> show master status \ G; view the status and start point of the binary log of the current master ************************** 1. row ************************* File: mysql-bin.000011 Position: 330 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.01 sec) mysql> flush tables with read Lock; lock the mysql database table and only allow read to ensure data consistency Query OK, 0 rows affected (0.06 sec) [root @ localhost ~] # Mysqldump-uroot-p123 blog> blog. SQL exports the blog library as a script to facilitate the import of slave mysql> unlock tables; unlocks Query OK, 0 rows affected (0.00 sec) 3. Operations on slave [root @ wordpress ~] # Mysql-u root-p123456 <blog. SQL mysql> change master to master_host = '2017. 168.254.46 ', master_user = 'master', master_password = 'master', master_log_file = 'mysql-bin.000011', master_log_pos = 330; Query OK, 0 rows affected, 2 warnings (0.13 sec) mysql> show slave status \ G; ***************************** 1. row ************************* Slave_IO_State: Master_Host: 192.168.254.46 Master_User: master Master_P Ort: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000011 Read_Master_Log_Pos: 330 Relay_Log_File: wordpress-relay-bin.000001 Relay_Log_Pos: 4 rows: mysql-bin.000011 rows: No rows: Replicate_Do_Table: rows: last_Errno: 0 Last_Error: Skip_Cou Nter: 0 character: 330 Relay_Log_Space: 120 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 character: No character: Master_SSL_Cert: character: Master_SSL_Key: character: No character: 0 character: last_ SQL _Errno: 0 Last_ SQL _Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 0 Master_UUID: Master_Info_File:/database/master.info SQL _Delay: 0 keys: NULL keys: Master_Retry_Count: 86400 Master_Bind: Keys: Master_SSL_Crl: Master_SSL_Crlpath: Keys: Auto_Position: 01 row in set (0.00 sec) mysql> start slave; Query OK, 0 rows affected (0.05 sec) 4. Test creating a table on the master, mysq L> use blog; Database changedmysql> create table hi_tb (id int (3), name char (10); Query OK, 0 rows affected (0.14 sec) mysql> show tables; + ---------------- + | Tables_in_blog | + ---------------- + | hi_tb | + ---------------- + 1 row in set (0.00 sec) view mysql> use blog in slave; Database changedmysql> show tables; empty set (0.00 sec) mysql slave does not find the new table, it indicates that there is a problem in the master-slave replication, so you need to check the mysql Error log tail-f/var/log/mysql. e Rror 15:39:21 2150 [Warning] Storing MySQL user name or password information in the master.info repository is not secure and is therefore not recommended. please see the MySQL Manual for more about this issue and possible alternatives.2013-09-22 15:39:21 2150 [Note] Slave I/O thread: connected to master 'master @ 192.168.254.46: 100 ', replication started in log 'mysql-bin.000011' at positi On 3302013-09-22 15:39:21 2150 [Warning] Slave SQL: If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 02013-09-22 15:39:21 2150 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000011 'at position 330, relay log '. /wordpress-relay-bin.000001 'position: 42013-09-22 15:39:21 2150 [ERROR] Slave I/O: Master comma Nd COM_REGISTER_SLAVE failed: Access denied for user 'master' @ '192. 168.254.153 '(using password: YES) (Errno: 1045), Error_code: 15972013-09-22 15:39:21 2150 [ERROR] Slave I/O thread couldn't register on master2013-09-22 15:39:21 2150 [Warning] Slave I/O: Master command COM_REGISTER_SLAVE failed: failed registering on master, reconnecting to try again, log 'mysql-bin.000011 'at position 330, Error _ Code: 15972013-09-22 15:39:21 2150 [Warning] Storing MySQL user name or password information in the master.info repository is not secure and is therefore not recommended. please see the MySQL Manual for more about this issue and possible alternatives.2013-09-22 15:39:21 2150 [ERROR] Slave I/O: Master command COM_REGISTER_SLAVE failed: access denied for user 'master' @ '192. 168.254.153 '(using passw Ord: YES) (Errno: 1045), Error_code: 15972013-09-22 15:39:21 2150 [ERROR] Slave I/O thread couldn't register on master ERROR cause, the master user is not authorized on the master !! Mysql> select * from hi_tb; (master) + ------ + | id | name | + ------ + | 1 | bobu | 2 | dsfa | + ------ + 2 rows in set (0.00 sec) mysql> select * from hi_tb; (slave) + ------ + | id | name | + ------ + | 1 | bobu | 2 | dsfa | + ------ + 2 rows in set (0.01 sec) synchronization is implemented. Finally, compare the binary logs of the master and slave nodes.