Details of master slave replication procedures and common error resolution for MySQL
- 2020-12-09 01:05:17
- OfStack
mysql master-slave replication (replication synchronization) is now widely used in enterprises and very mature. It has the following advantages:
1. Reduce pressure on the primary server to perform query work on the slave library.
2. Backup on the slave library to avoid affecting the primary server service.
3. When there is a problem with the main library, you can switch to the slave library.
However, there is a downside to using it as a backup, and if the master library misoperates, the slave library will also receive commands.
Let's go straight to the operation. debian5 operating system is used here,mysql5.0, the default engine innodb
10.1.1.45 main library
10.1.1.43 from library
1. Set up the main library
1) Modify the main library ES24en.ES25en, which is mainly ES26en-id1. Do not set the master and slave as one sample. Open the binlog log
log-bin = /opt/log.bin/45
server-id = 45
mysql> grant REPLICATION SLAVE ON *.* TO 'repl'@'10.1.1.43' IDENTIFIED BY 'replpass';
Note: mysql permissions system on the implementation is simpler, the permission information is mainly stored in several system tables: mysql. user, mysql. db, mysql. host, mysql. table_priv, mysql. columm_priv. Due to the small amount of permission information and the high frequency of access, mysql loads all the permission information into memory at startup and stores it in several specific structures. This causes flush privileges to be executed every time the relevant permission table is changed manually, notifying mysql to reload mysql's permission information. Of course, if you modify the permissions through the grants,revoke, or drop user commands, you do not need to execute the flush privileges command manually.
3) Export the snapshot of the database at that time on the primary server and pass it to the slave library.
root@10.1.1.45:mysql# mysqldump -uroot -p --single-transaction --flush-logs --master-data --all-databases
>
all.sql
-- ES65en-ES66en: This option allows innoDB and Falcon tables to remain unchanged during backup. The key to this approach is that the relevant tables are imported in the same transaction.mysqldump uses the repeatable read transaction isolation layer to generate a stable 1 dump without blocking other clients (for non-transactional tables, the dump process may change), and it cannot be used with -- ES72en-ES73en-ES74en option 1.
flush-logs: Clear the log files of the mysql server before the export begins. This makes it easier to recover, knowing that the base 2 log files created after checkpoint time are done after backing up a given database. Used in combination with -- ES80en-ES81en-ES82en or -- ES83en-ES84en, the log is cleared only after all tables have been locked. This option requires reload permissions.
-- ES88en-ES89en: mysqldump generates changer master to command in dump file, which records the log position information corresponding to 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 up the slave library
1). Modify the slave library ES103en. cnf
server-id = 43 # Master-slave can 1 For more than From the id Not the same
2) Feed a snapshot of the master library into the slave library
root@10.1.1.43:tmp# cat all.sql | mysql -uroot -p
3) Set up synchronization on the slave library. View the status of the slave library.
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: 0
1 row in set (0.00 sec)
ERROR:
No query specified
Test OK
Of course, this is the simplest configuration
There are also many parameters that can vary according to environmental requirements.
Such as
replicate-do-db =test to filter pull master logs from only this library and the following tables are required replicate-wild-do-table=test.dave replicate-wild-do-table=test.davehe
mysql database synchronization skips temporary errors
stop slave;
set GLOBAL SQL_SLAVE_SKIP_COUNTER=1; ( Transaction type , It may need to be executed several times )
start slave;
stop slave IO_THREAD // This thread master The log of the segment is written locally
start slave IO_THREAD
stop slave SQL_THREAD // This thread applies logs written to the local database
start slave SQL_THREAD
Slave_IO_Running: No error
Due to the mainframe 192.168.1.1 of the main library down, after reconnecting with the main library 192.168.71.1, an error was found
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 452723
Server 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: NULL
1 row in set (0.00 sec)
View error log
mysql@192.168.71.1:/opt/mysql/3399$ cat 192.168.71.1.err
140115 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 log
140115 1:51:01 [Note] Slave I/O thread exiting, read up to log '99.000302', position 165112917
Based on the error location, find the location 165112917 corresponding to log '99.000302' on the main library
root@192.168.1.1:mysql.bin# mysqlbinlog 99.000302 > /tmp/test
root@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=0
SET 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 = 17877752
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
It turns out that the location on the main library is 165111351 which is smaller than 165112917. That is, the location found from the library synchronization is larger than the main library, so synchronization is not successful
And the reason why this happens is because this is very easy to do when sync_binlog=0.
sync_binlog=0, when the transaction commits, MySQL does not do fsync and other disk synchronization instructions to flush the information in binlog_cache to disk, but lets the system decide when to do synchronization, or cache will not be synchronized to disk until cache is full.
sync_binlog=n, after each n transaction commit, MySQL will perform a disk synchronization instruction like fsync to force the data in binlog_cache to disk.
In MySQL, the default setting of the system is sync_binlog=0, that is, do not do any mandatory disk refresh instruction, at this time the performance is the best, but the risk is also the greatest. All binlog messages in binlog_cache are lost because of the 1-post system Crash. When it is set to "1", it is the safest setting with the highest performance cost. Because when set to 1, even system Crash loses at most one transaction that is not completed in binlog_cache, without any material impact on the actual data. From past experience and related tests, for systems with high concurrent transactions, the write performance gap between "sync_binlog" set to 0 and "binlog" set to 1 can be as much as 5 times or more.
Since mysql is the default configuration, the reason for the error is: When sync_binlog=0, the flush log buffer of master binlog file (this buffer is due to os buffer of binlog file) to disk is dependent on OS itself, but the Slave IO thread is reading the position of master dump thread, 1 generally read log buffer directly, this position, It may be much larger than the actual size of the binlog file. Therefore, when the host machine is down, binlog buffer does not brush the disk. When the host machine is started again, the size of binlog pos 165112917 from the library is already larger than the actual size of binlog position 165111351.
Solutions:
Do change master to directly to the next binlog.
mysql> grant REPLICATION SLAVE ON *.* TO 'repl'@'10.1.1.43' IDENTIFIED BY 'replpass';