?
Solved

Mysql, How to enable row based log

Posted on 2009-02-15
5
Medium Priority
?
1,199 Views
Last Modified: 2012-06-27
Currently I have already enabled binary log as statement base logging, how do I enable binary logs as row base logging
0
Comment
Question by:motioneye
  • 4
5 Comments
 
LVL 26

Accepted Solution

by:
Umesh earned 2000 total points
ID: 23647081
The default binary logging format depends on the version of MySQL you are using:

    *      For MySQL 5.1.11 and earlier, statement-based logging is used by default.
    *      For MySQL 5.1.12 and later, mixed logging is used by default.

You can force the replication format by starting the MySQL server with --binlog-format=type. When set, all replication slaves connecting to the server will read the events according to this setting. The supported values for type are:

    *  ROW causes replication to be row-based.
    * STATEMENT causes replication to be statement-based. This is the default for MySQL 5.1.11 and earlier, and MySQL 5.1.29 and later.
    * MIXED causes replication to use mixed format. This is the default for MySQL 5.1.12 to 5.1.28.

The logging format also can be switched at runtime. To specify the format globally for all clients, set the global value of the binlog_format system variable. (To change the global value, you must have need the SUPER privilege. This is also true for the SESSION value as of MySQL 5.1.29.)

To switch to statement-based format, use either of these statements:

mysql> SET GLOBAL binlog_format = 'STATEMENT';
mysql> SET GLOBAL binlog_format = 1;

To switch to row-based format, use either of these statements:

mysql> SET GLOBAL binlog_format = 'ROW';
mysql> SET GLOBAL binlog_format = 2;

To switch to mixed format, use either of these statements:

mysql> SET GLOBAL binlog_format = 'MIXED';
mysql> SET GLOBAL binlog_format = 3;

An individual client can control the logging format for its own statements by setting the session value of binlog_format. For example:

mysql> SET SESSION binlog_format = 'STATEMENT';
mysql> SET SESSION binlog_format = 'ROW';
mysql> SET SESSION binlog_format = 'MIXED';

In addition to switching the logging format manually, a slave server may switch the format automatically. This happens when the server is running in either STATEMENT or MIXED format and encounters a row in the binary log that is written in ROW logging format. In that case, the slave switches to row-based replication temporarily for that event, and switches back to the previous format afterwards.

Pls go thur this once for detail

http://dev.mysql.com/doc/refman/5.1/en/binary-log-setting.html
0
 

Author Comment

by:motioneye
ID: 23647101
Ok I have set it explicitly to "ROW" and perfrom some deletion on tables, I check what is captured in binlog but it still in statement based,



SET TIMESTAMP=1234760963/*!*/;
BEGIN
/*!*/;
# at 847
#090216 13:09:23 server id 1  end_log_pos 961   Query   thread_id=6     exec_time=0     error_code=0
SET TIMESTAMP=1234760963/*!*/;
delete from address where id >450001 and id <450116
/*!*/;
# at 961
#090216 13:09:23 server id 1  end_log_pos 988   Xid = 40
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
0
 
LVL 26

Assisted Solution

by:Umesh
Umesh earned 2000 total points
ID: 23647111
Pls note that this system variable binlog-format={ROW|STATEMENT|MIXED} is  Introduced in MySQL version 5.1.5 and  For MySQL 5.1.11 and earlier, statement-based logging is used by default

Thanks,
Umesh
0
 
LVL 26

Assisted Solution

by:Umesh
Umesh earned 2000 total points
ID: 23647190
I checked with both ROW/STATEMENT formats...  for the below insert statement...

insert into hi values(1),(10),(1000);
############## ROW ###################
 
 
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#090216 10:50:04 server id 1  end_log_pos 106 	Start: binlog v 4, server v 5.1.30 created 090216 10:50:04 at startup
ROLLBACK/*!*/;
BINLOG '
hPeYSQ8BAAAAZgAAAGoAAAAAAAQANS4xLjMwLWVudGVycHJpc2UtZ3BsLWFkdmFuY2VkLWxvZwAA
AAAAAAAAAAAAAAAAAACE95hJEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
'/*!*/;
# at 106
#090216 10:50:27 server id 1  end_log_pos 174 	Query	thread_id=1	exec_time=0	error_code=0
use test/*!*/;
SET TIMESTAMP=1234761627/*!*/;
SET @@session.pseudo_thread_id=1/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 174
# at 215
#090216 10:50:27 server id 1  end_log_pos 215 	Table_map: `test`.`hi` mapped to number 16
#090216 10:50:27 server id 1  end_log_pos 257 	Update_rows: table id 16 flags: STMT_END_F
 
BINLOG '
m/eYSRMBAAAAKQAAANcAAAAAABAAAAAAAAAABHRlc3QAAmhpAAEBAAA=
m/eYSRgBAAAAKgAAAAEBAAAQABAAAAAAAAEAAf///gD+DP4A/gz+AP4M
'/*!*/;
# at 257
#090216 10:50:27 server id 1  end_log_pos 326 	Query	thread_id=1	exec_time=0	error_code=0
SET TIMESTAMP=1234761627/*!*/;
COMMIT
/*!*/;
# at 326
#090216 10:50:42 server id 1  end_log_pos 415 	Query	thread_id=1	exec_time=0	error_code=0
SET TIMESTAMP=1234761642/*!*/;
truncate table `test`.`hi`
/*!*/;
# at 415
#090216 10:51:29 server id 1  end_log_pos 434 	Stop
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
 
 
############## STATEMENT ###################
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#090216 11:01:25 server id 1  end_log_pos 106 	Start: binlog v 4, server v 5.1.30 created 090216 11:01:25 at startup
ROLLBACK/*!*/;
BINLOG '
LfqYSQ8BAAAAZgAAAGoAAAAAAAQANS4xLjMwLWVudGVycHJpc2UtZ3BsLWFkdmFuY2VkLWxvZwAA
AAAAAAAAAAAAAAAAAAAt+phJEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
'/*!*/;
# at 106
#090216 11:01:36 server id 1  end_log_pos 205 	Query	thread_id=1	exec_time=0	error_code=0
use test/*!*/;
SET TIMESTAMP=1234762296/*!*/;
SET @@session.pseudo_thread_id=1/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
insert into hi values(1),(10),(1000)
/*!*/;
# at 205
#090216 11:02:10 server id 1  end_log_pos 224 	Stop
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
 
 
############## ROW ###################
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#090216 11:05:21 server id 1  end_log_pos 106 	Start: binlog v 4, server v 5.1.30 created 090216 11:05:21 at startup
ROLLBACK/*!*/;
BINLOG '
GfuYSQ8BAAAAZgAAAGoAAAAAAAQANS4xLjMwLWVudGVycHJpc2UtZ3BsLWFkdmFuY2VkLWxvZwAA
AAAAAAAAAAAAAAAAAAAZ+5hJEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
'/*!*/;
# at 106
#090216 11:05:30 server id 1  end_log_pos 174 	Query	thread_id=1	exec_time=0	error_code=0
use test/*!*/;
SET TIMESTAMP=1234762530/*!*/;
SET @@session.pseudo_thread_id=1/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 174
# at 215
#090216 11:05:30 server id 1  end_log_pos 215 	Table_map: `test`.`hi` mapped to number 15
#090216 11:05:30 server id 1  end_log_pos 250 	Write_rows: table id 15 flags: STMT_END_F
 
BINLOG '
IvuYSRMBAAAAKQAAANcAAAAAAA8AAAAAAAAABHRlc3QAAmhpAAEBAAA=
IvuYSRcBAAAAIwAAAPoAAAAQAA8AAAAAAAEAAf/+Af4K/n8=
'/*!*/;
# at 250
#090216 11:05:30 server id 1  end_log_pos 319 	Query	thread_id=1	exec_time=0	error_code=0
SET TIMESTAMP=1234762530/*!*/;
COMMIT
/*!*/;
# at 319
#090216 11:05:32 server id 1  end_log_pos 387 	Query	thread_id=1	exec_time=0	error_code=0
SET TIMESTAMP=1234762532/*!*/;
BEGIN
/*!*/;
# at 387
# at 428
#090216 11:05:32 server id 1  end_log_pos 428 	Table_map: `test`.`hi` mapped to number 15
#090216 11:05:32 server id 1  end_log_pos 463 	Write_rows: table id 15 flags: STMT_END_F
 
BINLOG '
JPuYSRMBAAAAKQAAAKwBAAAAAA8AAAAAAAAABHRlc3QAAmhpAAEBAAA=
JPuYSRcBAAAAIwAAAM8BAAAQAA8AAAAAAAEAAf/+Af4K/n8=
'/*!*/;
# at 463
#090216 11:05:32 server id 1  end_log_pos 532 	Query	thread_id=1	exec_time=0	error_code=0
SET TIMESTAMP=1234762532/*!*/;
COMMIT
/*!*/;
# at 532
#090216 11:05:41 server id 1  end_log_pos 551 	Stop
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

Open in new window

0
 
LVL 26

Expert Comment

by:Umesh
ID: 23718438
Did you try this at your end? pls let me know.

Thanks,
Umesh
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month15 days, 19 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question