Home >> FAQs/Tutorials >> MySQL Tutorials

MySQL FAQs - Server Daemon mysqld Administration

By: FYIcenter.com

Part:   1  2  3   4 

(Continued from previous part...)

How To Turn on Binary Logs?

If you want MySQL to write binary logs to a file, you can use the "--log-bin=fileBaseName" option at the "mysqld" command line. The tutorial exercise below shows you a good example on how to use this option:

>cd \mysql\bin
>mkdir \mysql\logs
>mysqld --log-bin=\mysql\logs\binary

Starts another command window, and enter the following commands:

>cd \mysql\bin
>mysql -u root -pretneciyf test

mysql> drop table test;
Query OK, 0 rows affected (0.12 sec)

mysql> create table test (name varchar(80));
Query OK, 0 rows affected (0.11 sec)

mysql> insert into test values ('dba.fyicenter.com');
Query OK, 1 row affected (0.03 sec)

How To Use mysqlbinlog to View Binary Logs?

If you have binary logs turned on, you can use "mysqlbinlog" to view the binary log files. The tutorial exercise below shows you how to view two binary files together:

>cd \mysql\bin
>mysql -u root -pretneciyf test

>mysqlbinlog ..\logs\binary.000001 ..\logs\binary.000002
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,
 COMPLETION_TYPE=0*/;
# at 4
# server id 1  end_log_pos 98    Start: binlog v 4, 
ROLLBACK;
# at 98
#20:35:34 server id 1  end_log_pos 184   Query   thread_id=1
SET TIMESTAMP=1156530934;
SET @@session.foreign_key_checks=1, 
@@session.sql_auto_is_null=1, @@session.unique_checks=1;
SET @@session.sql_mode=0;
/*!\C latin1 */;
SET @@session.character_set_client=8,
@@session.collation_connection=8,
@@session.collation_server=8;
REVOKE DROP ON *.* FROM DEV;
# at 184
#20:36:02 server id 1  end_log_pos 203   Stop
# at 4
#20:36:06 server id 1  end_log_pos 98    Start: binlog v 4,
# Warning: this binlog was not closed properly. 
# Most probably mysqld crashed writing it.
ROLLBACK;
# at 98
#20:37:08 server id 1  end_log_pos 176   Query   thread_id=1
use test;
SET TIMESTAMP=1156545428;
SET @@session.foreign_key_checks=1, 
@@session.sql_auto_is_null=1, @@session.unique_checks=1;
SET @@session.sql_mode=0;
/*!\C latin1 */;
SET @@session.character_set_client=8,
@@session.collation_connection=8,
@@session.collation_server=8;
drop table test;
# at 176
#20:37:35 server id 1  end_log_pos 275   Query   thread_id=1
SET TIMESTAMP=1156545455;
create table test (name varchar(80));
# at 275
#20:38:00 server id 1  end_log_pos 383   Query   thread_id=1
SET TIMESTAMP=1156545480;
insert into test values ('dba.fyicenter.com');
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

(Continued on next part...)

Part:   1  2  3   4