MySQL

• List MySQL tables engines for a MYDB database:

SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = 'MYDB' AND ENGINE IS NOT NULL ORDER BY ENGINE;

• Change engine for a MYTABLE table:

ALTER TABLE MYTABLE ENGINE=InnoDB;
ALTER TABLE MYTABLE ENGINE=MyISAM;

• Unlock replication (ex: Duplicate Key):

STOP SLAVE; SET GLOBAL sql_slave_skip_counter = 1; START SLAVE;

GTID Specific =

• To create a full dump containing GTIDs:

mysqldump --all-databases --single-transaction --triggers --routines --events > sqldump_with_gtids.sql

• To change a MySQL topology (failover/failback) / create a new SLAVE, use:

CHANGE MASTER TO MASTER_HOST='CHANGE_WITH_MASTER_IP', MASTER_USER='REPLICATION_USER', MASTER_PASSWORD='REPLICATION_PASSWORD', MASTER_AUTO_POSITION = 1;

• To initialize a new SLAVE you have to create a full dump containing GTIDs on an up-to-date server, an compare the value of GTID_PURGED from the dump and restored on the new slave:

grep -i gtid_purged sqldump_db2_with_gtids.sql
SET @@GLOBAL.GTID_PURGED='0f01c337-e1f1-11e4-8985-0cc47a45a200:1-34889

mysql> show global variables like 'gtid_executed';
+---------------+-------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                     |
+---------------+-------------------------------------------------------------------------------------------+
| gtid_executed | 0f01c337-e1f1-11e4-8985-0cc47a45a200:1-34889,
9e188f2b-e2be-11e4-8ec1-0cc47a052caa:1-2182 |
+---------------+-------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show global variables like 'gtid_purged';
+---------------+-------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                     |
+---------------+-------------------------------------------------------------------------------------------+
| gtid_purged   | 0f01c337-e1f1-11e4-8985-0cc47a45a200:1-34889,
9e188f2b-e2be-11e4-8ec1-0cc47a052caa:1-2182 |
+---------------+-------------------------------------------------------------------------------------------+

Some errors and their resolutions:

• If MySQL gtid_purged value isn't automatically restored with SOURCE command:

mysql> show global variables like 'gtid_purged';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_purged   |       |
+---------------+-------+

Use instead:

mysql -u root -p < my_dump.sql

• If you have this error when you try to import a dump :

$ mysql -u root -p < sqldump_db1-us_with_gtids.sql
ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.

You have to do this step first:

mysql> reset master;
Query OK, 0 rows affected (0.00 sec)

And then you can import without any issue.