Mysql-5.6 master-slave replication and error bitsCN.com
Mysql-5.6 master-slave replication and errors
Mysql replication process: each time a write operation is executed, it stores a copy in its own database. at the same time, this write operation is also stored in a binary log file, and save them as events. Therefore, each write operation or modification operation on the front-end data in this database will save an event, we will send the event to another server through Port 3306 of the mysql server. The other server will receive the event and save it to a local log file, then read an event from this log file and execute 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 the 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.
Below 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.sock
SQL _mode = NO_ENGINE_SUBSTITUTION, STRICT_TRANS_TABLES
Next is the slave 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
Log-error =/var/log/mysql. error
Basedir =/usr/local/mysql
Datadir =/database
Port = 3306
Socket =/var/run/mysqld/mysql. sock
SQL _mode = NO_ENGINE_SUBSTITUTION, STRICT_TRANS_TABLES
2. on the master server, set an account for the SLAVE database and grant permissions using replication slave.
Mysql> grant replication slave on *. * to 'tt' @ '192. 168.254.153 'identified by '123 ';
Query OK, 0 rows affected (0.06 sec)
Mysql> show master status/G; view the binary log status and start point 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 export the blog library as a script to facilitate the import of slave
Mysql> unlock tables; unlock
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. 192
54.46 ', master_user = 'master', master_password ='m
Aster ', 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_Port: 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
Relay_Master_Log_File: mysql-bin.000011
Slave_IO_Running: No
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: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 330
Relay_Log_Space: 120
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: 0
Last_ SQL _Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_UUID:
Master_Info_File:/database/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:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
Mysql> start slave;
Query OK, 0 rows affected (0.05 sec)
4. test
Create a table on the master,
Mysql> use blog;
Database changed
Mysql> 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 In slave
Mysql> use blog;
Database changed
Mysql> show tables;
Empty set (0.00 sec)
Mysql slave does not find the new table, which indicates that there is a problem in the master-slave replication process, so you need to view the mysql error log
Tail-f/var/log/mysql. error
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.
2150 15:39:21 3306 [Note] Slave I/O thread: connected to master 'master @ 192.168.254.46: 100', replication started in log 'MySQL-bin.000011 'at position 330
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: 0
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: 4
15:39:21 2150 [ERROR] Slave I/O: Master command COM_REGISTER_SLAVE failed: Access denied for user 'master' @ '192. 168.254.153 '(using password: YES) (Errno: 1045), Error Code: 1597
15:39:21 2150 [ERROR] Slave I/O thread couldn't register on master
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: 1597
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.
15:39:21 2150 [ERROR] Slave I/O: Master command COM_REGISTER_SLAVE failed: Access denied for user 'master' @ '192. 168.254.153 '(using password: YES) (Errno: 1045), Error Code: 1597
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.
BitsCN.com