MySQLデータベースのバックアップとリカバリ
15250 ワード
一、単一データベースをバックアップして多種のパラメータの使用を練習する
mysqlデータベースには、mysqldumpという使いやすいバックアップコマンドが用意されています.基本的には、文法:mysqldump-uユーザー名-pパスワードデータベース名>バックアップのファイル名mysqldumpバックアップ原理:mysqlデータベースバックアップは、mysqlのデータをSQL文の形式でエクスポートすることです.例1:oldboyという名前のライブラリのバックアップ
リカバリデータ:
上記プラス-Bのバックアップによるリカバリテスト:
以上の例からどのような結論が得られますか.1、エクスポートデータ用-Bパラメータ2、gzipでバックアップのデータを圧縮し、効率を高める.
二、複数のライブラリと複数のパラメータをバックアップする練習
1、操作結果
2、-Bパラメータ説明
三、ライブラリバックアップ
ライブラリバックアップとは、実際には、1つのバックアップ文を実行して1つのライブラリをバックアップすることであり、データベースに複数のライブラリがある場合は、同じバックアップ単一のライブラリのバックアップ文を複数実行することで複数のライブラリをバックアップすることができ、各ライブラリは対応するバックアップのライブラリをライブラリ名として使用することができ、最後に追加することができることに注意する.sql.命令は以下の通りである:法1:命令接合
法二:shellスクリプト(forループ)
ライブラリバックアップの意味は何ですか?1つの企業のデータベースに複数のライブラリ(www,bbs,blog)がある場合がありますが、問題が発生した場合はあるライブラリかもしれませんが、バックアップ時にすべてのライブラリを1つのデータファイルにバックアップすれば、あるライブラリのデータをリカバリする際に面倒になります.
四、バックアップ表
1、単一テーブルのバックアップ構文:mysqldump-uroot-p 123456データベース名テーブル名>バックアップのファイル名実行結果:
2、複数のテーブルをバックアップする構文:mysqldump-uroot-p 123456データベース名テーブル名1テーブル名2...>バックアップのファイル名以下のコマンドは、testテーブルとstudentテーブルの2つのテーブルをバックアップすることです:
分表バックアップの欠点:ファイルが多くて、割れています.a、完全な完全な準備をして、もう一つのライブラリの分表バックアップをします.b、スクリプトは大量に複数のSQLファイルを回復する.4、バックアップデータテーブル構造(データを含まない)mysqldump-dパラメータを利用してテーブルの構造のみをバックアップする.例:oldboyライブラリのすべてのテーブルをバックアップする構造.
5、mysqldumpのキーパラメータ説明キーパラメータ:mysqldump--help
innodbテーブルは、バックアップ時にオプションであるsingle-transactionを有効にしてバックアップの一貫性を保証します.実際には、今回のセッションの独立性レベルをrepeatable readに設定して、今回のセッション(dump)時に他のセッションがコミットしたデータが表示されないようにします.myisamエンジンバックアップコマンド:
ライブラリ・テーブル・バックアップ・スクリプト:
五、データベースの実践を回復する
1、sourceコマンドによるデータベースの復元
2、mysqlコマンドによる復旧(標準)
3、ライブラリのサブテーブルのバックアップデータを回復する
六、mysqlデータベース生産常用コマンド
1、show status; 「==現在のセッションのデータベースステータス情報を表示する2、show global status;==データベース全体の実行ステータス情報を表示することが重要です.分析して監視する必要があります
3、show processlist; 「==実行中のSQL文を表示し、4、show full processlistを見ることができない;==実行中の完全なSQL文を表示し、5、set global key_buffer_size=32777218を完全に表示する;==データベースを再起動しないでデータベースパラメータを調整し、直接有効になり、再起動した後に失効する.(永続的に有効にしたい場合は、プロファイルmy.cnfを変更する)
6、show variables; '==データベースのパラメータ情報を表示します.たとえば、my.cnfでのパラメータの有効化
七、mysqlbinlog命令紹介及び実戦解説
1、mysqlbinlog機能をプロファイルmyで開く方法.cnfの中で注釈を取り消します:
3、mysqlのbinlogログの役割は何ですか?mysql内部の削除・変更などmysqlデータベースに更新された内容を記録するための記録です.(主に更新、変更されたコンテンツレコード)mysqlのbinlogログを表示します.
4、mysqlbinlog重要パラメータコマンド1)、-d指定ライブラリのbinlog
mysqlデータベースには、mysqldumpという使いやすいバックアップコマンドが用意されています.基本的には、文法:mysqldump-uユーザー名-pパスワードデータベース名>バックアップのファイル名mysqldumpバックアップ原理:mysqlデータベースバックアップは、mysqlのデータをSQL文の形式でエクスポートすることです.例1:oldboyという名前のライブラリのバックアップ
[root@mysql-server ~]# mysqldump -uroot -p123456 oldboy > /opt/oldboy_bak.sql
[root@mysql-server ~]# egrep -v "#|\*|--|^$" /opt/oldboy_bak.sql
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`id` int(4) NOT NULL,
`name` char(20) NOT NULL,
`age` tinyint(2) NOT NULL DEFAULT '0',
`dept` varchar(16) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
LOCK TABLES `student` WRITE;
INSERT INTO `student` VALUES (1,'oldboy',0,NULL);
UNLOCK TABLES;
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`name` char(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
LOCK TABLES `test` WRITE;
INSERT INTO `test` VALUES (1,'oldboy'),(2,'oldgirl'),(3,' '),(4,' ');
UNLOCK TABLES;リカバリデータ:
[root@mysql-server ~]# mysql -uroot -p123456 oldboy < /opt/oldboy_bak.sql # oldboy ， -Bサンプル2:バックアップ時に-Bパラメータを追加[root@mysql-server ~]# mysqldump -uroot -p123456 -B oldboy >/opt/oldboy_B_bak.sql
， -B
[root@mysql-server ~]# cd /opt/
[root@mysql-server opt]# diff oldboy_bak.sql oldboy_B_bak.sql
18a19,26
> -- Current Database: `oldboy`
> --
>
> CREATE DATABASE /*!32312 IF NOT EXISTS*/ `oldboy` /*!40100 DEFAULT CHARACTER SET utf8 */;
>
> USE `oldboy`;
>
> --
76c84
< -- Dump completed on 2018-04-22 4:46:07
---
> -- Dump completed on 2018-04-22 5:14:09
： ， -B ， ：
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `oldboy` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `oldboy`;上記プラス-Bのバックアップによるリカバリテスト:
[root@mysql-server opt]# mysql -uroot -p123456 < /opt/oldboy_B_bak.sql例3:圧縮コマンド圧縮バックアップを指定するmysqlデータ[root@mysql-server opt]# mysqldump -uroot -p123456 -B oldboy|gzip > /opt/oldboy_bak.sql.gz
[root@mysql-server opt]# ls -l /opt/oldboy_*
-rw-r--r-- 1 root root 2579 Apr 22 04:46 /opt/oldboy_bak.sql
-rw-r--r-- 1 root root 894 Apr 22 05:22 /opt/oldboy_bak.sql.gz
-rw-r--r-- 1 root root 2722 Apr 22 05:14 /opt/oldboy_B_bak.sql以上の例からどのような結論が得られますか.1、エクスポートデータ用-Bパラメータ2、gzipでバックアップのデータを圧縮し、効率を高める.
二、複数のライブラリと複数のパラメータをバックアップする練習
1、操作結果
[root@mysql-server opt]# mysqldump -uroot -p123456 -B oldboy oldboy_gbk|gzip > /opt/all_bak.sql.gzすべてのライブラリをバックアップする:[root@mysql-server ~]# /application/mysql/bin/mysqldump -uroot -p123456 --all-databases --events --ignore-table=mysql.events > 2.sql
# ，
[root@mysql-server opt]# ll /opt/all_bak.sql.gz
-rw-r--r-- 1 root root 916 Apr 22 05:35 /opt/all_bak.sql.gz2、-Bパラメータ説明
-B, --databases Dump several databases. Note the difference in usage; in
this case no tables are given. All name arguments are
regarded as database names. 'USE db_name;' will be
included in the output.
-B ， create database ‘db_name’； use db_name
：-B ， ， use db， create database db 。三、ライブラリバックアップ
ライブラリバックアップとは、実際には、1つのバックアップ文を実行して1つのライブラリをバックアップすることであり、データベースに複数のライブラリがある場合は、同じバックアップ単一のライブラリのバックアップ文を複数実行することで複数のライブラリをバックアップすることができ、各ライブラリは対応するバックアップのライブラリをライブラリ名として使用することができ、最後に追加することができることに注意する.sql.命令は以下の通りである:法1:命令接合
[root@mysql-server opt]# mysql -uroot -p123456 -e "show databases;"|egrep -vi "database|infor|perfor"|sed -r 's#^([a-z].*$)#mysqldump -uroot -p123456 --events -B \1|gzip > /opt/bak/\1.sql.gz#g'|bash
[root@mysql-server opt]# ll bak
total 164
-rw-r--r-- 1 root root 144463 Apr 22 06:14 mysql.sql.gz
-rw-r--r-- 1 root root 535 Apr 22 06:14 oldboy_gbk.sql.gz
-rw-r--r-- 1 root root 911 Apr 22 06:14 oldboy.sql.gz
-rw-r--r-- 1 root root 801 Apr 22 06:14 oldboy_utf8.sql.gz
-rw-r--r-- 1 root root 532 Apr 22 06:14 test.sql.gz
-rw-r--r-- 1 root root 538 Apr 22 06:14 wordpress.sql.gz法二:shellスクリプト(forループ)
[root@mysql scripts]# cat mysqldump1.sh
#!/bin/sh
USER=root
PASSWORD=oldboy123
SOCKET=/data/3306/mysql.sock
LOGIN="mysql -u$USER -p$PASSWORD -S $SOCKET"
DUMP="mysqldump -u$USER -p$PASSWORD -S $SOCKET"
DATABASE=$($LOGIN -e "show databases;"|egrep -v "*chema|mysql"|sed '1d')
for database in $DATABASE
do
$DUMP -B $database |gzip >/server/backup/${database}_B_$(date +%F).sql.gz
doneライブラリバックアップの意味は何ですか?1つの企業のデータベースに複数のライブラリ(www,bbs,blog)がある場合がありますが、問題が発生した場合はあるライブラリかもしれませんが、バックアップ時にすべてのライブラリを1つのデータファイルにバックアップすれば、あるライブラリのデータをリカバリする際に面倒になります.
四、バックアップ表
1、単一テーブルのバックアップ構文:mysqldump-uroot-p 123456データベース名テーブル名>バックアップのファイル名実行結果:
[root@mysql-server opt]# mysqldump -uroot -p123456 oldboy test > /opt/test_table.sql
： -B ， oldboy test 。
[root@mysql-server opt]# egrep -v "#|\*|--|^$" /opt/test_table.sql
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`name` char(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
LOCK TABLES `test` WRITE;
INSERT INTO `test` VALUES (1,'oldboy'),(2,'oldgirl'),(3,' '),(4,' ');
UNLOCK TABLES;2、複数のテーブルをバックアップする構文:mysqldump-uroot-p 123456データベース名テーブル名1テーブル名2...>バックアップのファイル名以下のコマンドは、testテーブルとstudentテーブルの2つのテーブルをバックアップすることです:
[root@mysql-server opt]# mysqldump -uroot -p123456 oldboy test student > /opt/all_table.sql3、サブテーブルバックアップ企業のニーズ:1つのライブラリに大きなテーブルと小さなテーブルがあり、ある小さなテーブルだけをリカバリする必要がある場合があります.上記のマルチテーブルバックアップは取り外しにくく、ライブラリがないように小さなテーブルをリカバリするのが面倒になります.では、表分割バックアップは、ライブラリの考え方と同様に、文を実行するたびに表をバックアップし、異なるデータファイルを生成すればよい.shellスクリプトによる実装(2つのforループ):[root@mysql backup]# cat /server/scripts/mysqldump1.sh
#!/bin/sh
USER=root
PASSWORD=oldboy123
SOCKET=/data/3306/mysql.sock
LOGIN="mysql -u$USER -p$PASSWORD -S $SOCKET"
DUMP="mysqldump -u$USER -p$PASSWORD -S $SOCKET"
DATABASE=$($LOGIN -e "show databases;"|egrep -v "*chema|mysql"|sed '1d')
for database in $DATABASE
do
TABLE=$($LOGIN -e "use $database;show tables;"|sed '1d')
for table in $TABLE
do
#[ ! -d /server/backup/$database/ ] && mkdir /server/backup/$database/ -p
[ -d /server/backup/$database/ ] || mkdir /server/backup/$database/ -p
$DUMP $database |gzip >/server/backup/$database/${database}_${table}_$(date +%F).sql.gz
done
done分表バックアップの欠点:ファイルが多くて、割れています.a、完全な完全な準備をして、もう一つのライブラリの分表バックアップをします.b、スクリプトは大量に複数のSQLファイルを回復する.4、バックアップデータテーブル構造(データを含まない)mysqldump-dパラメータを利用してテーブルの構造のみをバックアップする.例:oldboyライブラリのすべてのテーブルをバックアップする構造.
[root@mysql-server opt]# mysqldump -uroot -p123456 -B -d oldboy > /opt/biao_jiegou.sql
[root@mysql-server opt]# egrep -v "#|\*|--|^$" /opt/biao_jiegou.sql
USE `oldboy`;
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`id` int(4) NOT NULL,
`name` char(20) NOT NULL,
`age` tinyint(2) NOT NULL DEFAULT '0',
`dept` varchar(16) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`name` char(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
， -t
[root@mysql-server opt]# mysqldump -uroot -p123456 -B -t oldboy > /opt/biao_shuju.sql5、mysqldumpのキーパラメータ説明キーパラメータ:mysqldump--help
1）、-B ， use
2）、--compact ， ，
3）、-A
4）、-F binlog
5）、--master-data binlog
6）、-x，--lock-all-tables
7）、-l，--lock-tables
8）、-d
9）、-t
10）、--single-transaction innodb innodbテーブルは、バックアップ時にオプションであるsingle-transactionを有効にしてバックアップの一貫性を保証します.実際には、今回のセッションの独立性レベルをrepeatable readに設定して、今回のセッション(dump)時に他のセッションがコミットしたデータが表示されないようにします.myisamエンジンバックアップコマンド:
mysqldump -uroot -p123456 -A -B --master-data=2 -x --events|gzip > /opt/all.sql.gzinnodbエンジンバックアップコマンド:推奨mysqldump -uroot -p123456 -A -B --master-data=2 --single-transaction --events|gzip > /opt/all.sql.gz
# --master-data=2 binlog ，-- SQL 。
[root@mysql-server ~]# mysqldump -uroot -p123456 --master-data=2 --events oldboy student
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000052', MASTER_LOG_POS=107;ライブラリ・テーブル・バックアップ・スクリプト:
[root@mysql oldboy]# cat /server/scripts/mysqldump1.sh
#!/bin/sh
USER=root
PASSWORD=oldboy123
SOCKET=/data/3306/mysql.sock
LOGIN="mysql -u$USER -p$PASSWORD -S $SOCKET"
DUMP="mysqldump -u$USER -p$PASSWORD -S $SOCKET"
DATABASE=$($LOGIN -e "show databases;"|egrep -v "*chema|mysql"|sed '1d')
for database in $DATABASE
do
TABLE=$($LOGIN -e "use $database;show tables;"|sed '1d')
for table in $TABLE
do
#[ ! -d /server/backup/$database/ ] && mkdir /server/backup/$database/ -p
[ -d /server/backup/$database/ ] || mkdir /server/backup/$database/ -p
$DUMP $database |gzip >/server/backup/$database/${database}_${table}_$(date +%F).sql.gz
done
$DUMP -B $database |gzip >/server/backup/${database}_B_$(date +%F).sql.gz
done五、データベースの実践を回復する
1、sourceコマンドによるデータベースの復元
mysql> system ls /opt/
all_bak.sql.gz biao_jiegou.sql oldboy_B_bak.sql
all_table.sql oldboy_bak.sql rh
bak oldboy_bak.sql.gz test_table.sql
mysql> source /opt/oldboy_B_bak.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Database changed
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.05 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.06 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)2、mysqlコマンドによる復旧(標準)
[root@mysql-server ~]# mysql -uroot -p123456 oldboy < /opt/oldboy_bak.sql
，
[root@mysql-server opt]# gzip -d oldboy_bak.sql.gz # -d
[root@mysql-server ~]# mysql -uroot -p123456 oldboy < /opt/oldboy_bak.sql 3、ライブラリのサブテーブルのバックアップデータを回復する
[root@mysql-server bak]# ls
mysql.sql oldboy.sql test.sql
oldboy_gbk.sql oldboy_utf8.sql wordpress.sql
[root@mysql-server bak]# for dbname in `ls *\.sql|sed 's#\.sql##g'`;do mysql -uroot -p123456 < ${dbname}.sql;done
[root@mysql-server bak]# mysql -uroot -p123456 -e "show databases;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| oldboy |
| oldboy_gbk |
| oldboy_utf8 |
| performance_schema |
| test |
| wordpress |
+--------------------+六、mysqlデータベース生産常用コマンド
1、show status; 「==現在のセッションのデータベースステータス情報を表示する2、show global status;==データベース全体の実行ステータス情報を表示することが重要です.分析して監視する必要があります
mysql> show global status like 'select%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 0 |
| Select_range_check | 0 |
| Select_scan | 3 |
+------------------------+-------+
5 rows in set (0.00 sec)3、show processlist; 「==実行中のSQL文を表示し、4、show full processlistを見ることができない;==実行中の完全なSQL文を表示し、5、set global key_buffer_size=32777218を完全に表示する;==データベースを再起動しないでデータベースパラメータを調整し、直接有効になり、再起動した後に失効する.(永続的に有効にしたい場合は、プロファイルmy.cnfを変更する)
mysql> set global key_buffer_size = 1024*32;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 1
Current database: *** NONE ***
Query OK, 0 rows affected (0.00 sec)6、show variables; '==データベースのパラメータ情報を表示します.たとえば、my.cnfでのパラメータの有効化
mysql> show variables like 'key_buffer_size';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| key_buffer_size | 32768 |
+-----------------+-------+
1 row in set (0.00 sec)七、mysqlbinlog命令紹介及び実戦解説
1、mysqlbinlog機能をプロファイルmyで開く方法.cnfの中で注釈を取り消します:
[root@mysql-server bak]# sed -i 's%#log-bin=mysql-bin%log-bin=mysql-bin%g' /etc/my.cnf2、mysqlbinlogはmysqlのbinlogログを解析するので、mysqlのbinlogログは何ですか?データディレクトリの下にあるファイルはmysqlのbinlogログです.[root@mysql-server bak]# cat /application/mysql/data/mysql-bin.index
./mysql-bin.000001
./mysql-bin.000002
./mysql-bin.000003
./mysql-bin.0000043、mysqlのbinlogログの役割は何ですか?mysql内部の削除・変更などmysqlデータベースに更新された内容を記録するための記録です.(主に更新、変更されたコンテンツレコード)mysqlのbinlogログを表示します.
[root@mysql-server data]# mysqlbinlog mysql-bin.000025
/*!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*/;4、mysqlbinlog重要パラメータコマンド1)、-d指定ライブラリのbinlog
[root@mysql-server data]# mysqlbinlog -d oldboy_gbk mysql-bin.000024 >oldboy.gbk.sql # oldboy_gbk binlog2)、位置別切り取り:[root@mysql-server data]# mysqlbinlog mysql-bin.000024 --start-position=592 --stop-position=695 -r pos.sql # 592 695 ， ，-r >， 3)、時間別切り取り:[root@mysql-server data]# mysqlbinlog mysql-bin.000024 --start-datetime='2018-04-23 17:57:07' --stop-datetime='2018-04-23 17:57:08' -r time.sql #