Mysql database InnoDB engine master copy Synchronization experience Summary _mysql

The company's MySQL architecture has recently been upgraded, from the original one of the main more from the Drbd+heartbeat double master from, just have a new E-commerce site project will be online, with the Drbd+heartbeat dual-master one from, Since this process is still different from the previous MyISAM engine, it is summed up here:

1 MySQL replication process is an asynchronous synchronization process, not complete master-slave synchronization, so the process of synchronization is delayed, if the separation of read and write business, it is recommended to monitor this delay time;

2 MySQL master and slave machine remember Server-id to remain inconsistent, if the same, replication process will appear as follows:

Copy Code code as follows:

Fatal error:the slave I/O threadstopsbecause master and slavehave equal MySQL server IDs; These IDs mustbedifferent for replication to work (or the--replicate-same-server-id optionmustbe used on slave but Esnot always make sense; Please check themanualbefore using it).

This problem is very good to deal with, will slave machine Server-id modified to be inconsistent with Master machine.

3 My previous misunderstanding is that the slave machine is using its own binary log to complete the replication process, in fact, is not the case, according to the principle of duplication of work: Slave server is the copy master server binary log to its own relay log, That is, the Relay-log log (that is, centos3-relay-bin.000002 this name), and then the update application to their own database, so the slave machine does not need to open binary log, so the process will be successful, unless you are ready to master the main architecture, this requires the slave machine to open Binary log, this problem has been guiding me, I have always thought slave machine to build replication environment is to open the binary system

4 in the Master machine authorization, as far as possible only to a certain or a few fixed machine permissions, so that they only replication Slav,replication client permissions, as far as possible not to grant permissions; In addition, although the database we generally operate through the intranet, But the more in the intranet to the MySQL database authorization operation, the more should pay attention to security;

5 Replication build process to follow the normal flow of words, generally very easy to implement success, if the error, more than check the network environment, permission issues, generally speaking, the entire construction process should still be relatively smooth.

In the early days of database design, I have defined this E-commerce database engine as InnoDB, in addition to the system tables in the database, the other tables are all converted from MyISAM to InnoDB, for two reasons:

1 e-commerce business will involve transaction payment, in this basic OLTP application, InnoDB should be the primary storage engine of the core application table;
2 DRBD System Restart process will be relatively slow, will frequently read the table, if the table engine for MyISAM the words are very likely to be damaged, in order to create unnecessary problems, I will be the database table engine from MyISAM to the InnoDB engine of the table.

Drbd+heartbeat+mysql reference to the previous working documents, build a relatively smooth, is to build replication environment encountered 1062 error, the detailed process is as follows:
The initial reference to the MySQL manual operation, take the master machine snapshot backup, with the--single-transaction option, and then the synchronization process frequently 1062 error, the error log is as follows:

Copy Code code as follows:

Last_sql_error:error ' Duplicate entry ' d36ad91bff36308de540bbd9ae6f4279 ' for key ' PRIMARY ' on query. DefaultDatabase: ' MyProject '. Query: ' INSERT into ' lee_sessions ' (' session_id ', ' ip_address ', ' user_agent ', ' last_activity ', ' User_data ') VALUES (' d36ad91bff36308de540bbd9ae6f4279 ', ' 180.153.201.218 ', ' mozilla/4.0 ', 1353394206, ') '

Later I changed my mind and used the--MASTER-DATA option to take the master Master snapshot backup, as shown in the following command:

Copy Code code as follows:

Mysqldump-uroot--quick--flush-logs--master-data=1-p myproject > Myproject.sql

Note: The use of--master-data is to suggest a slavereplication when backing up the SQL file with this parameter, and when the value is 1 o'clock, the change master statement is recorded in the SQL file, and when the value is 2 o'clock, the change Master is written as an SQL annotation, and--master-data automatically uses the Lock-all-tables option without using the--single-transaction option (that is, this second-generation option is not used in combination). How do I find log_file and log_pos in SQL? We can use the following command (note that the change Word is written in uppercase) as follows:

Copy Code code as follows:

grep "Change" myproject.sql

The command displays the following results:

Copy Code code as follows:

Change MASTER tomaster_log_file= ' mysql-bin.000008 ', master_log_pos=106;

The next replication process is not explained in detail, after synchronization is completed after a considerable period of observation, no more than 1062 error, as follows:

Copy Code code as follows:

Mysql> show slave status \g;
1.row***************************
Slave_io_state:waitingformaster to send Event
master_host:192.168.11.174
Master_user:rep1
master_port:3306
Connect_retry:60
master_log_file:mysql-bin.000008
read_master_log_pos:27880
relay_log_file:centos3-relay-bin.000002
relay_log_pos:28025
relay_master_log_file:mysql-bin.000008
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:27880
relay_log_space:28182
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
Master_ssl_verify_server_cert:no
last_io_errno:0
Last_io_error:
last_sql_errno:0
Last_sql_error:
1 row in Set (0.00 sec)

Work InnoDB engine database master-slave replication sync experience before the project is also more involved in the InnoDB database backup and replication, a lot of a practice is to stop the library for replication, although it is a way to solve the problem, but it belongs to the maintenance of downtime , it is not allowed in some special application scenarios, we should try to take the master host snapshot with mysqldump logical backup method.