MySQL 5.6.10 Cross-platform Gtid replication Practice

Build MySQL replication to achieve data redundancy based on your business needs.

MySQL 5.6.10 version provides a more convenient Gtid-based replication feature, MySQL can automatically identify the last sync point through Gtid, greatly facilitates the operation and maintenance personnel, reduce the chance of error.

As mentioned in the official documentation, the most reliable way to replicate is based on row replication, so it is better to sacrifice some performance to ensure the security of the data.

In the real world, the master master database MySQL 5.6.10 (MSI installation method) is installed on Windows x64, slave from the server is an old Dell server, running the CentOS 6.4 x64 system, Source code compilation install MySQL 5.6.10 Linux version, the installation process can refer to my previous blog post: http://www.cnblogs.com/jlzhou/archive/2013/03/09/2951544.html

Under different platforms, MySQL is somewhat different and should be handled with care.

The first problem is that, under the Windows platform, the file name case is not sensitive, resulting in the corresponding MySQL data table names are in lowercase letters, and the size is not sensitive, reference my previous blog: http://www.cnblogs.com/jlzhou/ Archive/2013/03/18/2966106.html in order to replicate data to MySQL on the Linux platform, we need to set the MySQL data table name setting on the Linux platform: (Modify the My.cnf file)

? [Mysqld]lower_case_table_names=1

The second problem is the self-increment field 0 value problem. Because the existing database is MSSQL, the business logic requires that some table's self-increment fields start at 0. Refer to my previous blog post: http://www.cnblogs.com/jlzhou/archive/2013/03/18/2965384.html in order to replicate data between the Windows platform and the Linux platform MySQL, Increase the global variable settings, adding No_auto_value_on_zero settings to the Sql-mode line in My.ini and MY.CNF respectively:

My.ini the file is in the C:\ProgramData\MySQL\MySQL Server 5.6 directory By default in the Windows7 or Windows2008 operating system (with MSI installation), if you have customized the data directory, The configuration file is in the data directory. # Set The SQL mode to strictsql-mode= "Strict_trans_tables,no_auto_create_user,no_engine_substitution,no_auto_value_ On_zero "

Now to configure Gtid replication, first configure the master-side My.ini file, add the following configuration, and then restart Master's MySQL service:

Binlog-format=rowlog-bin=master-bin.loglog-bin-index=master-bin.indexlog-slave-updates=truegtid-mode= onenforce-gtid-consistency=truemaster-info-repository=tablerelay-log-info-repository=tablesync-master-info= 1slave-parallel-workers=2binlog-checksum=crc32master-verify-checksum=1slave-sql-verify-checksum= 1binlog-rows-query-log-events=1server-id=1sync_binlog=1

Modify the My.cnf file on the slave side, add the following configuration, and then restart the MySQL service for slave:

Binlog-format=rowlog-bin=slave-bin.loglog-bin-index=slave-bin.indexlog-slave-updates=truegtid-mode= onenforce-gtid-consistency=truemaster-info-repository=tablerelay-log-info-repository=tablesync-master-info= 1slave-parallel-workers=2binlog-checksum=crc32master-verify-checksum=1slave-sql-verify-checksum= 1binlog-rows-query-log-events=1server-id=2sync_binlog=1

In fact, it is not necessary to enable binlog on the slave side, but in order to facilitate the conversion slave to master in the case of master failure, and to facilitate the establishment of slave slave, a configuration similar to the primary server is used.

The replication settings Save the user and password for replication in clear text in the Master.info file, preferably for replication, and grant REPLICATION SLAVE permissions.

Execute on the master side:

GRANT REPLICATION SLAVE on * * to ' repluser ' @ ' 192.168.1.101 ' identified by ' 12345678 ';

Finally, execute the command pointing to Master in slave and turn on slave replication.

Change MASTER to master_host= ' 192.168.1.100 ', master_port=3306, master_user= ' Repluser ', master_password= ' 12345678 ', Master_auto_position=1;

START slave;

You can then test the database on master, build the table, and then monitor the replication status of the slave.

This article does not cover the existing database on Master, please refer to this post: http://www.zhaokunyao.com/archives/4131

Postscript:

Attach backup and Recovery scripts: command line execution

Backup from remote server scripts://for test database: "C:\MySQL\MySQL server 5.6\bin\mysqldump.exe"--user=root--max_al lowed_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 that in the script above, the part of the backup is added to the--set-gtid-purged=off parameter to prevent the generation of the SET @ @global in the SQL script that was backed up. Gtid_purged statement:

Warning:a partial dump from A server which has gtids would by default include the Gtids of all transactions, even those tha T changed suppressed parts of the database. If you don ' t want to restore gtids, pass--set-gtid-purged=off.

Official documentation about Set-gtid-purged is written in this way:

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.

MySQL 5.6.10 Cross-platform Gtid replication Practice