MySQL master-slave replication steps and common errors _ MySQL

This article describes the master-slave replication steps of MySQL and common error solutions. it advocates skipping temporary errors during synchronization and provides solutions for Slave_IO_Running: No errors, for more information, see mysql master-slave replication (replication synchronization), which is widely used and mature in enterprises. it has the following advantages:

1. reduce the pressure on the master server and perform query on the slave database.

2. back up the slave database to avoid affecting the master server service.

3. when a problem occurs in the master database, you can switch to the slave database.

However, using it for backup also has drawbacks. if the master database has misoperations, the slave database will also receive commands.

Directly go to the operation below. here we use the debian5 operating system, mysql5.0, default engine innodb

10.1.1.45 master database

10.1.1.43 slave database

1. set the master database

1) modify the master database my. cnf. here, the master and slave databases of server-id must not be set to the same. enable binlog

log-bin   = /opt/log.bin/45server-id  = 45

2) create a synchronization account on the master server

mysql> grant REPLICATION SLAVE ON *.* TO 'repl'@'10.1.1.43' IDENTIFIED BY 'replpass';

Note: the mysql permission system is easy to implement. The permission information is mainly stored in several system tables: mysql. user, mysql. db, mysql. host, mysql. table_priv, mysql. columm_priv. because the volume of permission information is small and access is frequent, mysql loads all permission information into the memory at startup, and stored in several specific structures. this requires flush privileges to be executed every time you manually modify the permission table to notify mysql to reload the permission information of mysql. of course, if you use the grants, revoke, or drop user command to modify related permissions, you do not need to manually execute the flush privileges command.

3) export the database snapshot on the master server and pass it to the slave database.

Root@10.1.1.45: mysql # mysqldump-uroot-p -- single-transaction -- flush-logs -- master-data -- all-databases> all. SQL
-- Single-transaction: This option keeps innoDB and Falcon data tables unchanged during the backup process. the key to this practice is that it imports data tables in the same transaction. mysqldump uses the repeatable read transaction isolation layer to generate a stable and consistent dump file without blocking other customers (for non-transactional tables, the dump process may change ), it cannot be used with the -- lock-all-tables option.

-- Flush-logs: clears the log files of the mysql server before the export starts. this makes it easier to recover, knowing that the binary log files created after the checkpoint time are backed up after the given database. use -- lock-all-tables or -- master-data in combination to clear logs only after all data tables are locked. this option requires reload permission.

-- Master-data: after use, mysqldump will generate the changer master to command in the dump file, which records the detailed log position information corresponding to the dump time.

root@10.1.1.45:mysql# sed -n '1,40p' all.sql -- MySQL dump 10.11---- Host: localhost Database: -- -------------------------------------------------------- Server version 5.0.51a-24+lenny1-log /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;/*!40101 SET NAMES utf8 */;/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;/*!40103 SET TIME_ZONE='+00:00' */;/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; ---- Position to start replication or point-in-time recovery from-- CHANGE MASTER TO MASTER_LOG_FILE='45.000064', MASTER_LOG_POS=98; ---- Current Database: `bbs`-- CREATE DATABASE /*!32312 IF NOT EXISTS*/ `bbs` /*!40100 DEFAULT CHARACTER SET latin1 */; USE `bbs`; ---- Table structure for table `davehe`-- DROP TABLE IF EXISTS `davehe`;SET @saved_cs_client  = @@character_set_client;SET character_set_client = utf8;CREATE TABLE `davehe` (

2. set slave database

1). modify the slave database my. cnf

Server-id = 43 # master/slave nodes can be one to multiple slave nodes and different IDs cannot be the same

2) add the snapshot of the master database to the slave database

root@10.1.1.43:tmp# cat all.sql | mysql -uroot -p

3) set synchronization on the Slave Database. view the slave database status.

mysql> change master to master_host='10.1.1.45', master_user='repl',master_password='replpass',master_log_file='45.000064',master_log_pos=98;Query OK, 0 rows affected (0.01 sec)mysql> start slave;Query OK, 0 rows affected (0.00 sec)  mysql> show slave status\G;*************************** 1. row ***************************    Slave_IO_State: Waiting for master to send event    Master_Host: 10.1.1.45    Master_User: repl    Master_Port: 3306    Connect_Retry: 60   Master_Log_File: 45.000064  Read_Master_Log_Pos: 98    Relay_Log_File: mysqld-relay-bin.000002    Relay_Log_Pos: 228  Relay_Master_Log_File: 45.000064   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: 98   Relay_Log_Space: 228   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: 01 row in set (0.00 sec)  ERROR: No query specified

Test OK

Of course, this is just the simplest configuration.

There are also many parameters that can be changed according to the environment requirements.

For example

• Replicate-do-db = test
• Replicate-wild-do-table = test. dave
• Replicate-wild-do-table = test. davehe

Temporary error skipping during mysql database synchronization

Stop slave; set GLOBAL SQL _SLAVE_SKIP_COUNTER = 1; (transaction type, which may need to be executed several times) start slave; stop slave IO_THREAD // This thread writes the logs of the master segment to the local start slave IO_THREADstop slave SQL _THREAD // This thread applies the logs written to the local database to start slave SQL _THREAD

Slave_IO_Running: No error

Because host 192.168.1.1 of the master database is down, an error is reported when the master database is connected from database 192.168.71.1. Slave_IO_Running: No

root@192.168.71.1:~# mysql -uroot -p --socket=/opt/mysql/3399/3399.sock Enter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 452723Server version: 5.0.51a-24+lenny2 (Debian) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> show slave status\G;*************************** 1. row ***************************    Slave_IO_State:     Master_Host: 192.168.1.1    Master_User: repl    Master_Port: 3306    Connect_Retry: 60   Master_Log_File: 99.000302  Read_Master_Log_Pos: 165112917    Relay_Log_File: 3399-relay-bin.000013    Relay_Log_Pos: 165113047  Relay_Master_Log_File: 99.000302   Slave_IO_Running: No   Slave_SQL_Running: Yes   Replicate_Do_DB:   Replicate_Ignore_DB: mysql   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: 165112917   Relay_Log_Space: 165113047   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: NULL1 row in set (0.00 sec)

View error logs

mysql@192.168.71.1:/opt/mysql/3399$ cat 192.168.71.1.err140115 1:51:01 [ERROR] Error reading packet from server: Client requested master to start replication from impossible position ( server_errno=1236)140115 1:51:01 [ERROR] Got fatal error 1236: 'Client requested master to start replication from impossible position' from master when reading data from binary log140115 1:51:01 [Note] Slave I/O thread exiting, read up to log '99.000302', position 165112917

Locate the location 000302 corresponding to log '99. 165112917 'on the master database based on the error location.

root@192.168.1.1:mysql.bin# mysqlbinlog 99.000302 > /tmp/testroot@192.168.1.1:mysql# tail -n 10 /tmp/test #140115 0:50:25 server id 1176 end_log_pos 165111351  Query thread_id=111 exec_time=0 error_code=0SET TIMESTAMP=1389718225/*!*/;INSERT INTO user_info_db_86.region_info_table_56 (userid, region, gameflag) VALUES (563625686, 0, 2) ON DUPLICATE KEY UPDATE gameflag = (gameflag | 2)/*!*/;# at 165111351#140115 0:50:25 server id 1176 end_log_pos 165111378  Xid = 17877752COMMIT/*!*/;DELIMITER ;# End of log fileROLLBACK /* added by mysqlbinlog */;/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

The result shows that the last 165111351 location on the master database is smaller than 165112917, that is, the location of the synchronization from the master database is larger than that on the master database, so the synchronization fails.

This is because sync_binlog = 0, which is easy to appear.
Sync_binlog = 0. after the transaction is committed, MySQL will not refresh the information in binlog_cache to the disk using disk synchronization commands such as fsync, and the system will decide when to synchronize the data, or, after the cache is full, it is synchronized to the disk.

Sync_binlog = n. after each n transaction commits, MySQL runs a disk synchronization command such as fsync to forcibly write data in binlog_cache to the disk.

In MySQL, sync_binlog is set to 0 by default, that is, no mandatory disk refresh command is performed. at this time, the performance is the best, but the risk is also the biggest. Because once the system Crash, all binlog information in binlog_cache will be lost. When it is set to "1", it is the safest but the biggest loss of performance. When set to 1, even if the system Crash is used, a transaction not completed in binlog_cache can be lost at most, without any material impact on the actual data. From past experience and related tests, for high-concurrency transaction systems, the system write performance gap between "sync_binlog" and "1" may be as high as 5 times or more.

The cause of this error is that when sync_binlog is set to 0, the flush log buffer of the master binlog File (this buffer is caused by the OS buffer of the binlog file) the disk depends on the OS, but the Slave IO thread is reading the location of the master dump thread. Generally, the log buffer is directly read, which may be much larger than the actual size of the binlog file. Therefore, when the host goes down, the binlog buffer is not flushed to the disk. when the Master host starts again, the binlog pos 165112917 in the slave database is 165111351 larger than the actual binlog position.

Solution:

Directly change the master to the next binlog.

CHANGE MASTER TO MASTER_HOST='192.168.1.1', MASTER_USER='repl', MASTER_PASSWORD='replpass', MASTER_PORT=3306, MASTER_LOG_FILE='99.000303', MASTER_LOG_POS=98;