Mysql master-slave copy steps and common error resolution _MYSQL

MySQL Master-slave replication (replication sync) Now enterprise use more, also very mature. It has the following advantages:

1. Reduce the pressure on the primary server, you can perform query work from the library.

2. Make a backup from the library to avoid affecting the primary server service.

3. When there is a problem with the main library, you can switch to from the library.

However, use it to do backup will also have drawbacks, if the main library is incorrect operation, from the library will also receive commands.

The following goes directly into the operation. Here is the DEBIAN5 operating system, mysql5.0, default engine InnoDB

10.1.1.45 Main Library

10.1.1.43 from the Library

1. Set up the main library

1 Modify the main library my.cnf, here is the main Server-id must not be set to the same. Open Binlog log

Log-bin   =/opt/log.bin/45
Server-id  = 45

2 Set up sync account on the main server

Mysql> Grant REPLICATION SLAVE on *.* to ' repl ' @ ' 10.1.1.43 ' identified by ' replpass ';

Note: MySQL permissions system in the implementation of relatively simple, related rights information is mainly stored in several system tables: MYSQL.USER,MYSQL.DB,MYSQL.HOST,MYSQL.TABLE_PRIV,MYSQL.COLUMM_ Priv. Because of the relatively small amount of data, access is very frequent, so MySQL at the start of the time, will be loaded into memory all the permissions information, and stored in a few specific structures. This makes it necessary to perform the flush after each manual modification of the relevant permission table privileges, notify MySQL to reload MySQL permission information. Of course, if you modify related permissions through the Grants,revoke or drop user commands, you do not need to manually execute the flush privileges command.

3 on the primary server to export the database at the time of the snapshot, passed from the library.

root@10.1.1.45:mysql# mysqldump-uroot-p--single-transaction--flush-logs--master-data--all-databases > All.sql
--single-transaction: This option allows the InnoDB and Falcon data tables to remain unchanged during the backup process. The key to this approach is that it imports the relevant data tables in the same transaction. Mysqldump use repeatable Read transaction isolation layer to generate a stable and consistent dump file without blocking other customers (the staging process may change for non-transactional tables) and it cannot be used with the--lock-all-tables option.

--flush-logs: Empty the MySQL server log file before the export work begins. This makes it easier to restore operations, Know that the binary log files that were created after the checkpoint time are completed after the given database is backed up. In combination with--lock-all-tables or--master-data, the log is cleared only after all data tables have been locked. This option requires Reload permissions.

--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 ', MAST
 
er_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 from library

1). Modify from library my.cnf

Server-id = #主从可1对多 from each ID cannot be the same

2 Pouring the snapshot of the main library from the library

root@10.1.1.43:tmp# Cat All.sql | Mysql-uroot-p

3 Set the synchronization from the library. View from library 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 Sl Ave_sql_running:yes REPLICATE_DO_DB:REPLICATE_IGNORE_DB:REPLICATE_DO_TABLE:REPLICATE_IGNORE_TABLE:REPL icate_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:m
 aster_ssl_cipher:master_ssl_key:seconds_behind_master:0 1 row in Set (0.00 sec) Error:no query specified

Test OK

Of course, it's just the simplest configuration.

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

Like what

• Replicate-do-db=test Filter Pull main log to from only need this library and the following table
• Replicate-wild-do-table=test.dave
• Replicate-wild-do-table=test.davehe

MySQL database sync skipped temporary error

stop slave;
Set GLOBAL sql_slave_skip_counter=1; (transaction type, which may need to be performed several times)
Start slave;

Stop slave io_thread//This thread writes the log of the Master section to the local
start slave io_thread
stop slave sql_thread//This thread applies the log to the local to the database
Start slave Sql_thread

Slave_io_running:no Error

Due to the host of the main library 192.168.1.1 downtime, once again Chilai, from the library 192.168.71.1 connection to the main library found an error. 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 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:3 306 connect_retry:60 master_log_file:99.000302 read_master_log_pos:165112917 Relay_log_file:3399-relay-bi 
   n.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_T able:replicate_wild_ignore_table:last_errno:0 last_error:skip_counter:0 exec_master_log_pos:16511
 2917 relay_log_space:165113047  Until_condition:none until_log_file:until_log_pos:0 Master_ssl_allowed:no Master_ssl_ca_file:mas Ter_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 fr Om binary log
140115 1:51:01 [note] Slave I/O thread exiting, read up to log ' 99.000302 ', position 165112917

Locate the log ' 99.000302 ' corresponding position on the main library based on the error location 165112917

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 UPDA TE 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 165,111,351:165,112,917 smaller. That is, the location of the synchronization from the library to find a larger than the main library, so the synchronization is not successful

Why this is so, this is because this in the sync_binlog=0 situation, very easy to appear.
Sync_binlog=0, when the transaction is committed, MySQL does not do fsync, such as the disk synchronization instructions to refresh the information in the Binlog_cache to disk, and let the system decide when to do synchronization, or cache full before synchronizing to disk.

Sync_binlog=n, after each n transaction commits, MySQL will perform a disk synchronization instruction such as Fsync to force the data in the Binlog_cache to disk.

The default setting of the system in MySQL is sync_binlog=0, which is not to do any mandatory disk refresh instructions, at which point performance is the best, but the risk is greatest. Because once the system crash, all binlog information in the Binlog_cache will be lost. And when set to "1", is the most secure but the most performance loss of the settings. Because when set to 1, even if the system is crash, also lose most of the unfinished transaction in Binlog_cache, have no material influence to the actual data. From past experience and related testing, for systems with high concurrent transactions, the system write performance gap of "Sync_binlog" set to 0 and set to 1 may be as high as 5 times times or more.

Here because MySQL is the default configuration, the reason for this error is: sync_binlog=0, master binlog file flush log buffer (this buffer is due to the Binlog file OS buffer) To disk is dependent on the OS itself, but the slave IO thread reads the location of the master dump thread, usually directly reading log buffer, which may be much larger than the actual size of the Binlog file. So when the host is down, binlog buffer does not brush the disk, when the master host again started, at this time from the library Binlog Pos 165112917 is already larger than the actual binlog position size 165111351.

Workaround:

Do the change master directly 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;