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 enable GTIDs on a MySQL server, ensure you have these parameters:

# Replication and GTIDs
log-bin=mysql-bin
log-bin-index=mysql-bin.index
gtid-mode=ON
log-slave-updates
enforce-gtid-consistency
 

• To create a full dump containing GTIDs:

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

--single-transaction

 This option sets the transaction isolation mode to REPEATABLE READ and sends a START TRANSACTION SQL statement to the server before dumping data. It is useful only with transactional tables such as InnoDB, because then it dumps the consistent state of the database at the time when START TRANSACTION was issued without blocking any applications.

When using this option, you should keep in mind that only InnoDB tables are dumped in a consistent state. For example, any MyISAM or MEMORY tables dumped while using this option may still change state.

While a --single-transaction dump is in process, to ensure a valid dump file (correct table contents and binary log coordinates), no other connection should use the following statements: ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A consistent read is not isolated from those statements, so use of them on a table to be dumped can cause the SELECT that is performed by mysqldump to retrieve the table contents to obtain incorrect contents or fail.

The --single-transaction option and the --lock-tables option are mutually exclusive because LOCK TABLES causes any pending transactions to be committed implicitly.

This option is not supported for MySQL Cluster tables; the results cannot be guaranteed to be consistent due to the fact that the NDBCLUSTER storage engine supports only the READ_COMMITTED transaction isolation level. You should always use NDB backup and restore instead. 



--triggers

Include triggers for each dumped table in the output. This option is enabled by default;


 --routines, -R

Include stored routines (procedures and functions) for the dumped databases in the output. Use of this option requires the SELECT privilege for the mysql.proc table. The output generated by using --routines contains CREATE PROCEDURE and CREATE FUNCTION statements to re-create the routines. However, these statements do not include attributes such as the routine creation and modification timestamps. This means that when the routines are reloaded, they will be created with the timestamps equal to the reload time.

If you require routines to be re-created with their original timestamp attributes, do not use --routines. Instead, dump and reload the contents of the mysql.proc table directly, using a MySQL account that has appropriate privileges for the mysql database.

This option was added in MySQL 5.1.2. Before that, stored routines are not dumped. Routine DEFINER values are not dumped until MySQL 5.1.8. This means that before 5.1.8, when routines are reloaded, they will be created with the definer set to the reloading user. If you require routines to be re-created with their original definer, dump and load the contents of the mysql.proc table directly as described earlier.

Prior to MySQL 5.1.62, this option had no effect when used together with the --xml option. (Bug #11760384, Bug #52792) 


--events, -E

Include Event Scheduler events for the dumped databases in the output. This option requires the EVENT privileges for those databases. This option was added in MySQL 5.1.8. 

• 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.