We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Mysql, How to enable row based log

motioneye
motioneye asked
on
Medium Priority
1,262 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
Comment
Watch Question

Senior Principal Technical Support Engineer
Top Expert 2009
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
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*/;
UmeshSenior Principal Technical Support Engineer
Top Expert 2009
Commented:
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
UmeshSenior Principal Technical Support Engineer
Top Expert 2009
Commented:
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

UmeshSenior Principal Technical Support Engineer
Top Expert 2009

Commented:
Did you try this at your end? pls let me know.

Thanks,
Umesh
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.