Äú¿ÉÒÔ¾èÖú£¬Ö§³ÖÎÒÃǵĹ«ÒæÊÂÒµ¡£

1Ôª 10Ôª 50Ôª





ÈÏÖ¤Â룺  ÑéÖ¤Âë,¿´²»Çå³þ?Çëµã»÷Ë¢ÐÂÑéÖ¤Âë ±ØÌî



  ÇóÖª ÎÄÕ ÎÄ¿â Lib ÊÓÆµ iPerson ¿Î³Ì ÈÏÖ¤ ×Éѯ ¹¤¾ß ½²×ù Modeler   Code  
»áÔ±   
 
   
 
 
     
   
 ¶©ÔÄ
  ¾èÖú
ÀûÓÃbinlog½øÐÐÊý¾Ý¿âµÄ»¹Ô­
 
×÷Õߣºwzq609 À´Ô´£º51Testing ·¢²¼ÓÚ£º2015-01-06
  3694  次浏览      27
 

ǰÑÔ£ºÔÚѧϰmysql±¸·ÝµÄʱºò£¬ÉîÉîµÄ¸ÐÊܵ½mysqlµÄ±¸·Ý»¹Ô­¹¦ÄÜûÓÐoracleÇ¿´ó£»±ÈÈçÒ»¸öºÜ³£¼ûµÄ»Ö¸´³¡¾°£º»ùÓÚʱ¼äµãµÄ»Ö¸´£¬oracleͨ¹ýrman¹¤¾ß¾ÍÄܹ»ºÜ¿ìµÄʵÏÖÊý¾Ý¿âµÄ»Ö¸´£¬µ«ÊÇmysqlÔÚ½øÐв»ÍêÈ«»Ö¸´µÄʱºòºÜ´óµÄÒ»²¿·ÖÒªÒÀÀµÓÚmysqlbinlogÕâ¸ö¹¤¾ßÔËÐÐbinlogÓï¾äÀ´ÊµÏÖ£¬±¾Îĵµ½éÉÜͨ¹ýmysqlbinlogʵÏÖ¸÷ÖÖ³¡¾°µÄ»Ö¸´£»

Ò»¡¢²âÊÔ»·¾³ËµÃ÷

ʹÓÃmysqlbinlog¹¤¾ßµÄǰÌáÐèÒªÒ»¸öÊý¾Ý¿âµÄÍêÕûÐÔ±¸·Ý£¬ËùÒÔÐèÒªÊÂÏȶÔÊý¾Ý¿â×öÒ»¸öÍêÕûµÄ±¸·Ý£¬±¾ÎĵµÍ¨¹ýmysqlbackup½øÐÐÊý¾Ý¿âµÄÈ«±¸

¶þ¡¢²âÊÔ²½Öè˵Ã÷£º

Êý¾Ý¿âµÄ²åÈë×¼±¸¹¤×÷

2.1 ÔÚʱ¼äµãA½øÐÐÒ»¸öÊý¾Ý¿âµÄÍêÕû±¸·Ý£»

2.2 ÔÚʱ¼äµãB´´½¨Ò»¸öÊý¾Ý¿âBKT,²¢ÔÚBKTÏÂÃæ´´½¨Ò»¸ö±íJOHN£¬²¢²åÈë5ÌõÊý¾Ý£»

2.3 ÔÚʱ¼äµãCÍù±íJOHN¼ÌÐø²åÈëÊý¾Ýµ½10Ìõ£»

Êý¾Ý¿âµÄ»Ö¸´¹¤×÷

2.4 »Ö¸´Êý¾Ý¿âµ½Ê±¼äµãA,È»ºó¼ì²éÊý¾Ý¿â±íµÄ״̬£»

2.5 »Ö¸´Êý¾Ý¿âµ½Ê±¼äµãB,¼ì²éÏàÓ¦µÄϵͳ״̬£»

2.6 »Ö¸´Êý¾Ý¿âµ½Ê±¼äµãC£¬²¢¼ì²é»Ö¸´µÄ״̬£»

Èý¡¢³¡¾°Ä£Äâ²âÊÔ²½Ö裨±¸·Ý»Ö¸´ÊÇÒ»¼þºÜÖØÒªµÄÊÂÇ飩

3.1 Ö´ÐÐÊý¾Ý¿âµÄÈ«±¸·Ý£»

[root@mysql01 backup]# mysqlbackup --user=root --password --backup-dir=/backup backup-and-apply-log //ÔËÐÐÊý¾Ý¿âµÄÍêÕû±¸·Ý

3.2 ´´½¨Êý¾Ý¿â¡¢±í²¢²åÈëÊý¾Ý

mysql> SELECT CURRENT_TIMESTAMP;
+---------------------+
| CURRENT_TIMESTAMP |
+---------------------+
| 2014-11-26 17:51:27 |
+---------------------+
1 row in set (0.01 sec)
mysql> show databases; //ÉÐδ´´½¨Êý¾Ý¿âBKT
+--------------------+
| Database |
+--------------------+
| information_schema |
| john |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.03 sec)
mysql> Ctrl-C --
Aborted
[root@mysql02 data]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \\g.
Your MySQL connection id is 2
Server version: 5.5.36-log Source distribution
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type \'help;\' or \'\\h\' for help. Type \'\\c\' to clear the current input statement.
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 107 | | | //µ±Ç°Êý¾Ý¿âlogµÄpos״̬
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql> SELECT CURRENT_TIMESTAMP; //µ±Ç°µÄʱ¼ä´Á µ±Ç°Ê±¼äµãA
+---------------------+
| CURRENT_TIMESTAMP |
+---------------------+
| 2014-11-26 17:54:12 |
+---------------------+
1 row in set (0.00 sec)
mysql> create database BKT; //´´½¨Êý¾Ý¿âBKT
Query OK, 1 row affected (0.01 sec)
mysql> create table john (id varchar(32));
ERROR 1046 (3D000): No database selected
mysql> use bkt;
ERROR 1049 (42000): Unknown database \'bkt\'
mysql> use BKT;
Database changed
mysql> create table john (id varchar(32));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into john values(\'1\');
Query OK, 1 row affected (0.01 sec)
mysql> insert into john values(\'2\');
Query OK, 1 row affected (0.01 sec)
mysql> insert into john values(\'3\');
Query OK, 1 row affected (0.00 sec)
mysql> insert into john values(\'4\');
Query OK, 1 row affected (0.01 sec)
mysql> insert into john values(\'5\');
Query OK, 1 row affected (0.01 sec)
mysql> SELECT CURRENT_TIMESTAMP; //²åÈë5ÌõÊý¾ÝºóÊý¾Ý¿âµÄʱ¼äµãB£¬¼Ç¼¸Ãµã±ãÓÚÊý¾Ý¿âµÄ»Ö¸´
+---------------------+
| CURRENT_TIMESTAMP |
+---------------------+
| 2014-11-26 17:55:53 |
+---------------------+
1 row in set (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 1204 | | | //µ±Ç°binlogµÄposλÖÃ
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

3.3 ÉèÖÃʱ¼äµãCµÄ²âÊÔ

mysql> insert into john values(\'6\');
Query OK, 1 row affected (0.02 sec)
mysql> insert into john values(\'7\');
Query OK, 1 row affected (0.01 sec)
mysql> insert into john values(\'8\');
Query OK, 1 row affected (0.01 sec)
mysql> insert into john values(\'9\');
Query OK, 1 row affected (0.01 sec)
mysql> insert into john values(\'10\');
Query OK, 1 row affected (0.03 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 2125 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql> SELECT CURRENT_TIMESTAMP;
+---------------------+
| CURRENT_TIMESTAMP |
+---------------------+
| 2014-11-26 17:58:08 |
+---------------------+
1 row in set (0.00 sec)

3.4 ÒÔÉϵIJÙ×÷Íê³ÉÖ®ºó£¬±ã¿ÉÒÔÖ´ÐÐÊý¾Ý¿âµÄ»Ö¸´²âÊÔ

[root@mysql02 data]# mysqlbackup --defaults-file=/backup/server-my.cnf --datadir=
/data/mysql --backup-dir=/backup/ copy-back
MySQL Enterprise Backup version 3.11.0 Linux-3.8.13-16.2.1.el6uek.x86_64-x86_64 [2014/08/26]
Copyright (c) 2003, 2014, Oracle and/or its affiliates. All Rights Reserved.
mysqlbackup: INFO: Starting with following command line ...
mysqlbackup --defaults-file=/backup/server-my.cnf --datadir=/data/mysql
--backup-dir=/backup/ copy-back
mysqlbackup: INFO:
IMPORTANT: Please check that mysqlbackup run completes successfully.
At the end of a successful \'copy-back\' run mysqlbackup
prints \"mysqlbackup completed OK!\".
141126 17:59:58 mysqlbackup: INFO:
 MEB logfile created at /backup/meta/MEB_2014-11-26.17-59-58_copy_back.log
--------------------------------------------------------------------
Server Repository Options:
--------------------------------------------------------------------
datadir = /data/mysql
innodb_data_home_dir = /data/mysql
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /data/mysql/
innodb_log_files_in_group = 2
innodb_log_file_size = 5242880
innodb_page_size = Null
innodb_checksum_algorithm = none
--------------------------------------------------------------------
Backup Config Options:
--------------------------------------------------------------------
datadir = /backup/datadir
innodb_data_home_dir = /backup/datadir
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /backup/datadir
innodb_log_files_in_group = 2
innodb_log_file_size = 5242880
innodb_page_size = 16384
innodb_checksum_algorithm = none
mysqlbackup: INFO: Creating 14 buffers each of size 16777216.
141126 17:59:58 mysqlbackup: INFO: Copy-back operation starts with following threads
1 read-threads 1 write-threads
mysqlbackup: INFO: Could not find binlog index file. If this is online backup then server may not have started with --log-bin.
Hence, binlogs will not be copied for this backup. Point-In-Time-Recovery will not be possible.
141126 17:59:58 mysqlbackup: INFO: Copying /backup/datadir/ibdata1.
mysqlbackup: Progress in MB: 200 400 600
141126 18:00:22 mysqlbackup: INFO: Copying the database directory \'john\'
141126 18:00:23 mysqlbackup: INFO: Copying the database directory \'mysql\'
141126 18:00:23 mysqlbackup: INFO: Copying the database directory \'performance_schema\'
141126 18:00:23 mysqlbackup: INFO: Completing the copy of all non-innodb files.
141126 18:00:23 mysqlbackup: INFO: Copying the log file \'ib_logfile0\'
141126 18:00:23 mysqlbackup: INFO: Copying the log file \'ib_logfile1\'
141126 18:00:24 mysqlbackup: INFO: Creating server config files server-my.cnf and server-all.cnf in /data/mysql
141126 18:00:24 mysqlbackup: INFO: Copy-back operation completed successfully.
141126 18:00:24 mysqlbackup: INFO: Finished copying backup files to \'/data/mysql\'
mysqlbackup completed //Êý¾Ý¿â»Ö¸´Íê³É

ÊÚȨ²¢´ò¿ªÊý¾Ý¿â

[root@mysql02 data]# chmod -R 777 mysql //ÐèÒªÊÚȨºó²ÅÄÜ´ò¿ª

[root@mysql02 data]# cd mysql

[root@mysql02 mysql]# ll

×ÜÓÃÁ¿ 733220

-rwxrwxrwx. 1 root root 305 11ÔÂ 26 18:00 backup_variables.txt
-rwxrwxrwx. 1 root root 740294656 11ÔÂ 26 18:00 ibdata1
-rwxrwxrwx. 1 root root 5242880 11ÔÂ 26 18:00 ib_logfile0
-rwxrwxrwx. 1 root root 5242880 11ÔÂ 26 18:00 ib_logfile1
drwxrwxrwx. 2 root root 4096 11ÔÂ 26 18:00 john
drwxrwxrwx. 2 root root 4096 11ÔÂ 26 18:00 mysql
drwxrwxrwx. 2 root root 4096 11ÔÂ 26 18:00 performance_schema
-rwxrwxrwx. 1 root root 8488 11ÔÂ 26 18:00 server-all.cnf
-rwxrwxrwx. 1 root root 1815 11Ô 26 18:00 server-my.cnf //ûÓÐBKTÊý¾Ý¿â
[root@mysql02 mysql]# service mysqld start //Æô¶¯Êý¾Ý¿â

3.5 ½øÐÐÊý¾Ý¿âµÄ»Ö¸´µ½Ê±¼äµãB

[root@mysql02 mysql2]# pwd //±¸·ÝµÄʱºò£¬ÐèÒª±¸·ÝbinlogÈÕÖ¾£¬Ö®Ç°µÄbinlogĿ¼Ϊ/data/mysql2

/data/mysql2

[root@mysql02 mysql2]# mysqlbinlog --start-position=107 --stop-position=1203 mysql-bin.000001| mysql -uroot -p //¸ù¾ÝpostµÄλÖýøÐлָ´£¬µ±Ç°µÄposλÖÃΪ107£¬»Ö¸´µ½posλÖõ½1203

Enter password:
[root@mysql02 mysql2]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \\g.
Your MySQL connection id is 3
Server version: 5.5.36-log Source distribution
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type \'help;\' or \'\\h\' for help. Type \'\\c\' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| BKT |
| john |
| mysql |
| performance_schema |
+--------------------+
5 rows in set (0.02 sec)
mysql> use BKT
Database changed
mysql> show tables;
+---------------+
| Tables_in_BKT |
+---------------+
| john |
+---------------+
1 row in set (0.00 sec)
mysql> select * from john;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+------+
5 rows in set (0.01 sec) //²é¿´Êý¾Ý¿â»Ö¸´³É¹¦

3.6 »Ö¸´Êý¾Ý¿âµ½Ê±¼äµãC

[root@mysql02 mysql2]# mysqlbinlog --start-date=\"2014-11-27 09:21:56\" --stop-date=\"2014-11-27 09:22:33\"
 mysql-bin.000001| mysql -uroot -p123456 //±¾´Îͨ¹ý»ùÓÚʱ¼äµãµÄ»Ö¸´£¬»Ö¸´µ½Ê±¼äµãC
Warning: Using unique option prefix start-date instead 
of start-datetime is deprecated and will be removed in a future release. Please use the full name instead.
Warning: Using unique option prefix stop-date instead 
of stop-datetime is deprecated and will be removed in a future release. Please use the full name instead.
[root@mysql02 mysql2]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \\g.
Your MySQL connection id is 6
Server version: 5.5.36-log Source distribution
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type \'help;\' or \'\\h\' for help. Type \'\\c\' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| BKT |
| john |
| mysql |
| performance_schema |
+--------------------+
5 rows in set (0.00 sec)
mysql> use BKT
Database changed
mysql> select * from john;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
+------+
10 rows in set (0.00 sec) //¾­¹ý¼ì²é³É¹¦»Ö¸´µ½Ê±¼äµãC

ËÄ¡¢mysqlbinlogµÄÆäËû×ܽá

ÒÔÉÏÊÇÀûÓÃbinlogÎļþ½øÐлùÓÚʱ¼äµãºÍbinlogµÄPOSλÖûָ´µÄ²âÊÔ£¬mysqlbinlogµÄʹÓû¹Óкܶ๦ÄÜ£¬ÔËÐÐmysqlbinlog --help¿ÉÒԲ鿴ÏàÓ¦²ÎÊý£»

4.1 ²é¿´binlogµÄÄÚÈÝ£º[root@mysql02 mysql2]# mysqlbinlog mysql-bin.000001

4.2 mysqlbinlogµÄÆäËû³£ÓòÎÊý£º

-h ¸ù¾ÝÊý¾Ý¿âµÄIP

-P ¸ù¾ÝÊý¾Ý¿âËùÕ¼ÓõĶ˿ÚÀ´·Ö

-server-id ¸ù¾ÝÊý¾Ý¿âserveridÀ´»¹Ô­£¨ÔÚ¼¯ÈºÖкÜÓÐÓã©

-d ¸ù¾ÝÊý¾Ý¿âÃû³Æ

ÀýÈ磺 [root@mysql02 mysql2]# mysqlbinlog -d BKT mysql-bin.000001 //»¹Ô­BKTÊý¾Ý¿âµÄÐÅÏ¢

   
3694 ´Îä¯ÀÀ       27
Ïà¹ØÎÄÕÂ

»ùÓÚEAµÄÊý¾Ý¿â½¨Ä£
Êý¾ÝÁ÷½¨Ä££¨EAÖ¸ÄÏ£©
¡°Êý¾Ýºþ¡±£º¸ÅÄî¡¢ÌØÕ÷¡¢¼Ü¹¹Óë°¸Àý
ÔÚÏßÉ̳ÇÊý¾Ý¿âϵͳÉè¼Æ ˼·+Ч¹û
 
Ïà¹ØÎĵµ

GreenplumÊý¾Ý¿â»ù´¡Åàѵ
MySQL5.1ÐÔÄÜÓÅ»¯·½°¸
ijµçÉÌÊý¾ÝÖÐ̨¼Ü¹¹Êµ¼ù
MySQL¸ßÀ©Õ¹¼Ü¹¹Éè¼Æ
Ïà¹Ø¿Î³Ì

Êý¾ÝÖÎÀí¡¢Êý¾Ý¼Ü¹¹¼°Êý¾Ý±ê×¼
MongoDBʵս¿Î³Ì
²¢·¢¡¢´óÈÝÁ¿¡¢¸ßÐÔÄÜÊý¾Ý¿âÉè¼ÆÓëÓÅ»¯
PostgreSQLÊý¾Ý¿âʵսÅàѵ
×îл¼Æ»®
DeepSeek´óÄ£ÐÍÓ¦Óÿª·¢ 6-12[ÏÃÃÅ]
È˹¤ÖÇÄÜ.»úÆ÷ѧϰTensorFlow 6-22[Ö±²¥]
»ùÓÚ UML ºÍEA½øÐзÖÎöÉè¼Æ 6-30[±±¾©]
ǶÈëʽÈí¼þ¼Ü¹¹-¸ß¼¶Êµ¼ù 7-9[±±¾©]
Óû§ÌåÑé¡¢Ò×ÓÃÐÔ²âÊÔÓëÆÀ¹À 7-25[Î÷°²]
ͼÊý¾Ý¿âÓë֪ʶͼÆ× 8-23[±±¾©]

MySQLË÷Òý±³ºóµÄÊý¾Ý½á¹¹
MySQLÐÔÄܵ÷ÓÅÓë¼Ü¹¹Éè¼Æ
SQL ServerÊý¾Ý¿â±¸·ÝÓë»Ö¸´
ÈÃÊý¾Ý¿â·ÉÆðÀ´ 10´óDB2ÓÅ»¯
oracleµÄÁÙʱ±í¿Õ¼äдÂú´ÅÅÌ
Êý¾Ý¿âµÄ¿çƽ̨Éè¼Æ


²¢·¢¡¢´óÈÝÁ¿¡¢¸ßÐÔÄÜÊý¾Ý¿â
¸ß¼¶Êý¾Ý¿â¼Ü¹¹Éè¼ÆÊ¦
HadoopÔ­ÀíÓëʵ¼ù
Oracle Êý¾Ý²Ö¿â
Êý¾Ý²Ö¿âºÍÊý¾ÝÍÚ¾ò
OracleÊý¾Ý¿â¿ª·¢Óë¹ÜÀí


GE Çø¿éÁ´¼¼ÊõÓëʵÏÖÅàѵ
º½Ìì¿Æ¹¤Ä³×Ó¹«Ë¾ Nodejs¸ß¼¶Ó¦Óÿª·¢
ÖÐÊ¢Òæ»ª ׿Խ¹ÜÀíÕß±ØÐë¾ß±¸µÄÎåÏîÄÜÁ¦
ijÐÅÏ¢¼¼Êõ¹«Ë¾ PythonÅàѵ
ij²©²ÊITϵͳ³§ÉÌ Ò×ÓÃÐÔ²âÊÔÓëÆÀ¹À
ÖйúÓÊ´¢ÒøÐÐ ²âÊÔ³ÉÊì¶ÈÄ£Ðͼ¯³É(TMMI)
ÖÐÎïÔº ²úÆ·¾­ÀíÓë²úÆ·¹ÜÀí