MySQL5.6.10 cross-platform GTID replication practices

MySQL replication is established to achieve data redundancy based on business needs. MySQL5.6.10 provides a more convenient GTID-based replication function. MySQL can automatically identify

MySQL replication is established to achieve data redundancy based on business needs. MySQL 5.6.10 provides a more convenient GTID-based replication function. MySQL can be automatically identified by GTID.

MySQL replication is established to achieve data redundancy based on business needs.

MySQL 5.6.10 provides a more convenient GTID-based replication function. MySQL can automatically identify the last synchronization point through GTID, which greatly facilitates O & M personnel and reduces the chance of errors.

As mentioned in official documents, row-based replication is the safest and most reliable replication method. Therefore, we would rather sacrifice some performance to ensure data security.

In the real environment, the master database MySQL 5.6.10 (msi installation method) is installed on Windows 2008 Server x64, And the slave Server is an old DELL Server running CentOS 6.4 x64 system, compile and install MySQL 5.6.10 in the source code. For the installation process, refer to my previous articles:

There are some differences between MySQL on different platforms. Be careful when dealing with them.

The first problem is that in Windows, the file name is case-insensitive, resulting in the corresponding MySQL Data Table names are all in lowercase letters by default, while the size is not sensitive. refer to my previous article: to synchronize data to MySQL on Linux, we need to set the name of the MySQL DATA table on Linux: (modify my. cnf file)

[Mysqld]
Lower_case_table_names = 1

The second problem is the zero value of the auto-increment field. Because the existing database is MSSQL, the business logic requires that the auto-increment fields of some tables start from 0. Refer to my previous blog post: To copy data between MySQL on Windows and Linux platforms and add global variable settings. ini and my. add NO_AUTO_VALUE_ON_ZERO in cnf to SQL-mode respectively:

// My. ini: this file is located in the C: \ ProgramData \ MySQL Server 5.6 directory in Windows 7 or Windows by default (using the MSI installation method). If you have customized the data directory, the configuration file is in the data directory.
# Set the SQL mode to strict
SQL-mode = "STRICT_TRANS_TABLES, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION, NO_AUTO_VALUE_ON_ZERO"

Now configure GTID replication. First configure the my. ini file on the master side, add the following configuration, and then restart the MySQL service on the master side:

Binlog-format = ROW
Log-bin = master-bin.log
Log-bin-index = master-bin.index
Log-slave-updates = true
Gtid-mode = on
Enforce-gtid-consistency = true
Master-info-repository = TABLE
Relay-log-info-repository = TABLE
Sync-master-info = 1
Slave-parallel-workers = 2
Binlog-checksum = CRC32
Master-verify-checksum = 1
Slave-SQL-verify-checksum = 1
Binlog-rows-query-log-events = 1
Server-id = 1
Sync_binlog = 1