Preamble
My initial idea was more to test MySQL Replication switchover and failover but had a presentation of MySQL 5.6 new replication features and realized that I have never setup such environment myself. So decided to give a try and use Percona backup tool (XtraBackup) and classic mysqldump utility to create from scratch a slave instance.
Blog post has been done using MySQL 5.6.11 (binary release i.e. Linux – Generic (glibc 2.5) (x86, 64-bit), Compressed TAR Archive), Oracle Enterprise Linux 6.4 64 bits and Percona XtraBackup 2.1.3. In below server1.domain.com is my master server and server2.domain.com the slave one. They are both virtual machine using non routable IP adresses.
Replication with GTID prerequisites
For better segregation I have decided to create a MySQL Replication dedicated account with minimum rights (using root would not be a good idea):
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repadmin'@'%' IDENTIFIED BY 'secure_password';
Query OK, 0 rows affected (0.04 sec)
|
And create a test database and table:
mysql> CREATE DATABASE replicationdb CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.03 sec)
mysql> use replicationdb
Database changed
mysql> CREATE TABLE test1(val int, descr varchar(50));
Query OK, 0 rows affected (0.23 sec)
|
Table I load with something like:
DELIMITER $$
DROP PROCEDURE IF EXISTS fill_test1;
CREATE PROCEDURE fill_test1()
BEGIN
DECLARE count INT DEFAULT 1;
WHILE count <= 20 DO
INSERT INTO test1 VALUES(count,count);
SET count=count+1;
END WHILE;
END;
$$
DELIMITER ;
|
You can check master status and GTID position with:
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000002 | 191 | | | 770d3753-c6e4-11e2-8e78-080027d93e15:1-6 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 1730 |
| mysql-bin.000002 | 941 |
+------------------+-----------+
2 rows in set (0.00 sec)
mysql> SHOW BINLOG EVENTS IN 'mysql-bin.000002';
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| mysql-bin.000002 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.11-log, Binlog ver: 4 |
| mysql-bin.000002 | 120 | Previous_gtids | 1 | 191 | 770d3753-c6e4-11e2-8e78-080027d93e15:1-6 |
| mysql-bin.000002 | 191 | Gtid | 1 | 239 | SET @@SESSION.GTID_NEXT= '770d3753-c6e4-11e2-8e78-080027d93e15:7' |
| mysql-bin.000002 | 239 | Query | 1 | 307 | BEGIN |
| mysql-bin.000002 | 307 | Table_map | 1 | 367 | table_id: 153 (replicationdb.test1) |
| mysql-bin.000002 | 367 | Write_rows | 1 | 410 | table_id: 153 flags: STMT_END_F |
| mysql-bin.000002 | 410 | Xid | 1 | 441 | COMMIT /* xid=374 */ |
| mysql-bin.000002 | 441 | Gtid | 1 | 489 | SET @@SESSION.GTID_NEXT= '770d3753-c6e4-11e2-8e78-080027d93e15:8' |
| mysql-bin.000002 | 489 | Query | 1 | 557 | BEGIN |
| mysql-bin.000002 | 557 | Table_map | 1 | 617 | table_id: 153 (replicationdb.test1) |
| mysql-bin.000002 | 617 | Write_rows | 1 | 660 | table_id: 153 flags: STMT_END_F |
| mysql-bin.000002 | 660 | Xid | 1 | 691 | COMMIT /* xid=379 */ |
| mysql-bin.000002 | 691 | Gtid | 1 | 739 | SET @@SESSION.GTID_NEXT= '770d3753-c6e4-11e2-8e78-080027d93e15:9' |
| mysql-bin.000002 | 739 | Query | 1 | 807 | BEGIN |
| mysql-bin.000002 | 807 | Table_map | 1 | 867 | table_id: 153 (replicationdb.test1) |
| mysql-bin.000002 | 867 | Write_rows | 1 | 910 | table_id: 153 flags: STMT_END_F |
| mysql-bin.000002 | 910 | Xid | 1 | 941 | COMMIT /* xid=1867 */ |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
17 rows in set (0.00 sec)
|
The typical variables to setup for replication are (when using the new GTID functionality), I use non-default binlog_format = row as MySQL 5.6 has apparently been drastically improved for this most used replication format (as Oracle say). In below whether you are on server1 or server2 you must adapt server_id and report_host variables:
log-bin = /mysql/logs/mysql01/mysql-bin
server-id = 1 | 2
relay_log = /mysql/logs/mysql01/relay-bin
binlog_format = row
gtid_mode = on
log_slave_updates = true
enforce_gtid_consistency = true
master_info_repository = table
relay_log_info_repository = table
sync_master_info = 1 # Never do this on a production server, default value = 10000
master-verify-checksum = on
slave-sql-verify-checksum = on
report_host = server1.domain.com | server2.domain.com
report_port = 3326
To ease testing I’m adding the three alias in profile of my mysql Linux account:
alias start_mysql01='cd /mysql/software/mysql01/; ./bin/mysqld_safe --defaults-file=/mysql/software/mysql01/conf/my.cnf &'
alias stop_mysql01="/mysql/software/mysql01/bin/mysqladmin --defaults-file=/mysql/software/mysql01/conf/my.cnf --user=root --password=`cat ~mysql/.root_password` shutdown"
alias mysql01='/mysql/software/mysql01/bin/mysql --defaults-file=/mysql/software/mysql01/conf/my.cnf --user=root --password=`cat ~mysql/.root_password`' |
The .root_password file is in home directory of mysql Linux account:
[mysql@server1 ~]$ ll .root_password
-r-------- 1 mysql dba 16 May 31 17:11 .root_password |
As a reminder I personally use the below MySQL directory naming convention:
| Directory |
Used for |
| /mysql/data01/mysql01 |
Strore MyISAM and InnoDB files, dataxx directories can also be created to spread I/O |
| /mysql/dump/mysql01 |
All log files (slow log, error log, general log, …) |
| /mysql/logs/mysql01 |
All binary logs (log-bin, relay_log) |
| /mysql/software/mysql01 |
MySQL binaries (the my.cnf file is then stored in a conf subdirectory, as well as socket and pid files) |
This MySQL directories naming convention should allow you to have multiple MySQL instance running on same server (mysql01, mysql02 and so on). Please note it is slightly different from what has been nicely presented by George Trujillo in hisInstalling MySQL 5.1 on Solaris 10 using MOCA post. MOCA stands for MySQL Optimal Configuration Architecture (MOCA) and I like this name…
