MySQL 5.6.10 cross-platform GTID replication practices

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, source code compilation and installation of MySQL 5.6.10 Linux version, the installation process can refer to my previous blog: http://www.cnblogs.com/jlzhou/archive/2013/03/09/2951544.html

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, so that the corresponding MySQL Data Table names are all in lowercase letters by default, and the size is not sensitive. refer to my previous blog post: http://www.cnblogs.com/jlzhou/archive/2013/03/18/2966106.html in order to synchronize data to the Linux platform MySQL, we need to set the Linux platform MySQL Data Table Name settings :( 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: http://www.cnblogs.com/jlzhou/archive/2013/03/18/2965384.html in order to replicate data between Windows and Linux MySQL, add global variable settings in my. 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. 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 
 
 

Modify the my. cnf file of the slave, add the following configuration, and restart the MySQL service of the slave:

 
 

  
  
binlog-format=ROW  
  
  
log-bin=slave-bin.log  
  
  
log-bin-index=slave-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=2  
  
  
sync_binlog=1 
 
 

In fact, you do not need to enable binlog on the slave side. However, in order to easily switch the slave to the master when the master node fails and establish the slave conveniently, you must use a configuration similar to that of the master server.
 
The REPLICATION settings Save the user and password used for REPLICATION in the master.info file in plain text. It is best to create a dedicated user for REPLICATION and grant the replication slave permission.
 
Run the following command on the master:
 
Grant replication slave on *. * TO 'repluser' @ '192. 168.1.101 'identified BY '123 ';
Finally, run the command pointing to the master in slave and enable slave replication.
 
Change master to MASTER_HOST = '192. 168.1.100 ', MASTER_PORT = 192, MASTER_USER = 'repluser', MASTER_PASSWORD = '000000', master_auto_position = 1; START slave;
In this case, you can test the creation of a database on the master node, create a table, and then monitor the replication status of the slave.
 
This article does not involve the existing database on the master, please refer to this blog: http://www.zhaokunyao.com/archives/4131

Postscript:
 
Attached backup and recovery Script: Command Line execution

 
 

  
  
Backup from remote server scripts:  
  
  
 
  
  
// for test database:  
  
  
"C:\MySQL\MySQL Server 5.6\bin\mysqldump.exe" --user=root --max_allowed_packet=1G --host=10.192.8.105 --port=3306 --default-character-set=utf8 --set-gtid-purged=OFF --password --databases test > "C:\\Backup\\test.dump.sql"  
  
  
 
  
  
Restore to local dev machine scripts:  
  
  
 
  
  
// for test database:  
  
  
"C:\MySQL\MySQL Server 5.6\bin\mysql.exe" --host=localhost --user=root --port=3306 --password --default-character-set=utf8 --comments < "C:\\Backup\\test.dump.sql" 
 
 

Note: In the preceding script, the -- set-gtid-purged = OFF parameter must be added to the backup part to prevent the SET @ global. gtid_purged statement from being generated in the backup SQL script:
 
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. if you don't want to restore GTIDs, pass -- set-gtid-purged = OFF.

Set-gtid-purged is written as follows:

This option enables control over global transaction ID (GTID) information written to the dump file, by indicating whether to add a SET @ global. gtid_purged statement to the output.

Edit recommendations]

1. MySQL 5.6.10 vs Percona 5.6.6 TPCCC performance test
2. MariaDB has become a turning point in the fate of MySQL?
3. Rollback in case of a DELETE misoperation in MySQL
4. One-step migration from MySQL to Redis
5. Two ways to solve the problem of master-slave synchronization in mysql