Mysql-5.6 master-slave replication and encountered errors _ MySQL

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