Check replication type in MySQL?

To check replication type, you can use SHOW GLOBAL VARIABLES command. The syntax is as follows −

SHOW GLOBAL VARIABLES LIKE 'binlog_format';

The above syntax returns either ROW, MIXED or STATEMENT. The default resultant is ROW.

Now you can implement the above syntax to check replication type. The query is as follows −

mysql> SHOW GLOBAL VARIABLES LIKE 'binlog_format';

Here is the output −

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.10 sec)

Here is the query to switch from ROW to STATEMENT −

mysql> SET GLOBAL binlog_format = 'STATEMENT';
Query OK, 0 rows affected (0.04 sec)

Now check the replication type once again. The query is as follows −

mysql> SHOW GLOBAL VARIABLES LIKE 'binlog_format';

Here is the output −

+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.01 sec)

Now the binlog_format is STATEMENT.

You can change from STATEMENT to MIXED or MIXED to ROW and so on.

Change by session also. The query is as follows −

mysql> SET SESSION binlog_format = 'ROW';
Query OK, 0 rows affected (0.00 sec)

Now check the value of binlog_format once again. The query is as follows −

mysql> SHOW VARIABLES LIKE 'binlog_format';

The following is the output −

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.04 sec)

Samual Sam

Learning faster. Every day.

Updated on: 30-Jul-2019

296 Views

Kickstart Your Career

Get certified by completing the course

Get Started