mysqldumpバックアップおよびデータ復元
mysqldumpは10 g以下のデータをバックアップすることをお勧めします.mysqlデータベースの論理バックアップです.
手動ロックテーブルバックアップ:
自動バックアップ:
復元:
バックアップのシミュレーション後にデータを追加するには、次の手順に従います.
データ消失
データの表示:
at 927を見つけてdropにデータを復元する前に
接続mysql:一意の接続保証データ一意:
over
mysqldump
-A ―all-databases
-B --databases db1 db2 ...: , （ A ）
-x --lock-all-tables ， MyISAM、InnoDB、Aria
--single-transaction: InnoDB ；( innodb )
--flush-logs: 、 ；
： --master-data=[0|1|2] （0: ）（ 1： CHANGE MASTER ）（ 2： CHANGE MASTER ）
：
--events:
--routines:
--triggers：
： ， ；手動ロックテーブルバックアップ:
mysql> flush tables with read lock;
mysql> flush logs;
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000004 | 120 | | | |
+-------------------+----------+--------------+------------------+-------------------+
#mysql> insert into t3 (id,data) values(2,'22');
#ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock
[root@martin ~]# mysqldump -hlocalhost -uroot -p -B martin > a.sql
mysql> unlock tables;自動バックアップ:
[root@martin ~]# mysqldump -hlocalhost -uroot -p -B martin -x --flush-logs --master-data=2 > a.sql
[root@martin ~]# cat a.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='master-bin.000005', MASTER_LOG_POS=120;
--
-- Current Database: `martin`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `martin` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `martin`;
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000005 | 120 | | | |
+-------------------+----------+--------------+------------------+-------------------+復元:
バックアップのシミュレーション後にデータを追加するには、次の手順に従います.
mysql> CREATE TABLE IF NOT EXISTS `t1` (
-> `id` varchar(32) NOT NULL,
-> `data` text NOT NULL ,
-> `expire` int(11) NOT NULL DEFAULT '0',
-> PRIMARY KEY (`id`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1 (id,data) values(2,'22');
Query OK, 1 row affected (0.00 sec)
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000005 | 927 | | | |
+-------------------+----------+--------------+------------------+-------------------+データ消失
mysql> drop database martin;
Query OK, 2 rows affected (0.00 sec)
[root@martin local]# service mysqld stop
Shutting down MySQL.. SUCCESS!データの表示:
[root@martin local]# cd /mydata/mysql/log-bin/
[root@martin log-bin]# mysqlbinlog --start-position=120 master-bin.000005
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#160524 15:29:25 server id 1 end_log_pos 120 CRC32 0xa1962627 Start: binlog v 4, server v 5.6.30-log created 160524 15:29:25
BINLOG '
1QJEVw8BAAAAdAAAAHgAAAAAAAQANS42LjMwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAScm
lqE=
'/*!*/;
# at 120
#160524 15:32:29 server id 1 end_log_pos 249 CRC32 0x74e33ea9 Query thread_id=2 exec_time=0 error_code=0
use `martin`/*!*/;
SET TIMESTAMP=1464075149/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
DROP TABLE IF EXISTS `t1` /* generated by server */
/*!*/;
# at 249
#160524 15:32:40 server id 1 end_log_pos 378 CRC32 0x7ae6077a Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1464075160/*!*/;
DROP TABLE IF EXISTS `t1` /* generated by server */
/*!*/;
# at 378
#160524 15:32:40 server id 1 end_log_pos 643 CRC32 0x10173878 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1464075160/*!*/;
CREATE TABLE IF NOT EXISTS `t1` (
`id` varchar(32) NOT NULL,
`data` text NOT NULL ,
`expire` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
/*!*/;
# at 643
#160524 15:32:54 server id 1 end_log_pos 726 CRC32 0xad187d7d Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1464075174/*!*/;
BEGIN
/*!*/;
# at 726
#160524 15:32:54 server id 1 end_log_pos 843 CRC32 0x861ba6b0 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1464075174/*!*/;
insert into t1 (id,data) values(2,'22')
/*!*/;
# at 843
#160524 15:32:54 server id 1 end_log_pos 927 CRC32 0xa1a5bf4a Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1464075174/*!*/;
COMMIT
/*!*/;
# at 927
#160524 15:35:30 server id 1 end_log_pos 1025 CRC32 0x77d2ec0e Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1464075330/*!*/;
drop database martin
/*!*/;
# at 1025
#160524 15:37:18 server id 1 end_log_pos 1048 CRC32 0xb1a3e339 Stop
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;at 927を見つけてdropにデータを復元する前に
[root@martin log-bin]# mysqlbinlog --start-position=120 --stop-position=927 master-bin.000005>~/a.bin.sql接続mysql:一意の接続保証データ一意:
mysql> set session sql_log_bin=0;
mysql> source ~/a.sql
mysql> source ~/a.bin.sql
mysql> set session sql_log_bin=1;
mysql> show tables;
+------------------+
| Tables_in_martin |
+------------------+
| t1 |
| t3 |
+------------------+over