CentOS using the MySQL tool Percona Toolkit

Install Percona Toolkit repo to support the installation of binary packages directly with Yum

Yum Install-y https://www.percona.com/redir/downloads/percona-release/redhat/latest/percona-release-0.1-3.noarch.rpm
Yum Install-y Percona-toolkit

Change MySQL table structure DDL pt-online-schema-change

Pt-online-schema-change-u root-h 10.8.8.8-p password--alter= ' add column vid int '--execute d=database,t=table
D=database Library Name
T=table Table Name
–alter= ' add column vid int ' statement to execute

Here's to the pit.

MySQL version 5.5 database and table are UTF8 encoding, Linux terminal is also UTF8 code
Execute pt-online-schema-change to add a field to a table
After the execution, found that this table's field Chinese annotation COMMENT are garbled!!
A classmate who knows how to fix it, can leave a message for me.

Operation, tries, wait:
Copy_rows, 10, 0.25
Create_triggers, 10, 1
Drop_triggers, 10, 1
Swap_tables, 10, 1
Update_foreign_keys, 10, 1
Altering ' Wwek ' ... ' wwek_users ' ...
Creating new Table ...
Created new Table Wwek._wwek_users_new OK.
Altering new Table ...
Altered ' Wwek '. ' _wwek_users_new ' OK.
2015-09-18t10:56:16 Creating triggers ...
2015-09-18t10:56:16 Created triggers OK.
2015-09-18t10:56:16 copying approximately 183066 rows ...
Copying ' Wwek '. ' wwek_users ': 30% 01:10 remain
Copying ' Wwek '. ' wwek_users ': 49% 01:01 remain
Copying ' Wwek '. ' wwek_users ': 63% 00:51 remain
Copying ' Wwek '. ' wwek_users ': 76% 00:37 remain
Copying ' Wwek '. ' wwek_users ': 88% 00:20 remain
2015-09-18t10:59:03 Copied rows OK.
2015-09-18T10:59:03 Swapping tables ...
2015-09-18T10:59:03 swapped original and new tables OK.
2015-09-18t10:59:03 dropping old table ...
2015-09-18T10:59:04 dropped old table ' Wwek '. ' _wwek_users_old ' OK.
2015-09-18T10:59:04 dropping triggers ...
2015-09-18T10:59:04 dropped triggers OK.
Successfully altered ' Wwek '. ' Wwek_users '.

How to use Percona Toolkit to solve MySQL master never sync problem

For a variety of reasons, MySQL master-slave architecture often appear inconsistent data, roughly summed up in the following categories

1: Standby Write Data
2: Execute non-deterministic Query
3: Rollback of transactions doped transaction tables and non-transaction tables
4:binlog or relay log data corruption
The damage to the application caused by the data synchronization is fatal, when the master and subordinate data inconsistencies, the common response is to first from the library line, and then find a midnight time to stop the application, to perform the synchronization, if the size of the database is very large, the workload can be imagined, will let people collapse. This article introduces the use of the Percona-toolkit tool to check and resynchronize the synchronization state of the MySQL master-slave database.

One: Install Percona-toolkit
# yum-y Install Perl-time-hires
# wget
Http://www.percona.com/downloads/percona-toolkit/2.2.13/tarball/percona-toolkit-2.2.13.tar.gz
# TAR-ZXVPF Percona-toolkit-2.2.13.tar.gz
# CD percona-toolkit-2.2.13
# Perl makefile.pl
# make
# make Install

II: Modify the MySQL binlog format binlog_format parameter is row format

The MySQL Binlog log has three formats, statement, Mixed, and row!
1.Statement:

Every SQL that modifies the data is recorded in the Binlog.
Advantages: Do not need to record each row of changes, reduce the amount of binlog log, saving Io, improve performance. (compared to row can save a lot of performance and log volume, depending on the application of SQL, the normal record changes or insert the row format generated by the log volume is less than the statement generated log volume, but given the conditional update operation, and the whole table delete, Operations such as ALTER TABLE, row format produces a lot of logs, so when considering whether to use the row format log, it should be followed by the actual application, the amount of log generated, and the IO performance problem. )
Cons: Because records are just execution statements, for these statements to run correctly on the slave, you must also record some relevant information about each statement at execution time to ensure that all statements can be slave and executed at the master end of the same result. In addition to MySQL replication, like some specific function functions, slave can be consistent with master on a number of related issues (such as sleep () function, last_insert_id (), and user-defined Functions (UDF) problems ).
2.Row
SQL statement Context-sensitive information is not logged and only the record is modified.
Advantage: Binlog can not record the context-related information of the executed SQL statement, just record what the record was modified to. So Rowlevel's log content will be very clear record the details of each row of data modification. There are no stored procedures, or function, and trigger calls and triggers that cannot be replicated correctly in certain situations.
Disadvantage: All executed statements when recorded in the log, will be recorded in each row of records, which may result in a large number of log content, such as an UPDATE statement, modify multiple records, then each of the Binlog changes will have records, resulting in Binlog log volume will be very large, Especially when executing statements such as ALTER TABLE, each record of the table is recorded in the log because of changes in the table structure.
3.Mixed
is a mixture of the above two levels, the general statement to modify the use of statment format to save Binlog, such as some functions, statement can not complete the master-slave copy operation, the row format to save the Binlog, MySQL distinguishes the log form of a record against each specific SQL statement executed, that is, choosing between statement and row. The new version of the MySQL Squadron row level model has also been optimized, not all changes will be recorded at row level, It is recorded in statement mode when a table structure change is encountered. For statements that modify data, such as update or delete, all row changes are recorded.
The master-slave database modifies the my.cnf file separately the related configuration item is as follows:

Binlog_format=row