mysql で画像ファイルなど大きなファイルをDBに保存するには、blob型を使います。
medium blob型を使えば、16MBまでのデータを保存することができますが、mysqlの設定でサイズの上限に引っかかってしまうとエラーになってしまいます。
この記事では、サイズ上限の変更方法を説明します。
※blob型への画像の保存方法は別記事とします。
mysqlで大きなファイルを扱うための設定変更方法
変更箇所は2箇所です。
- ログファイルサイズ
- 上限パケットサイズ
ログファイルサイズ変更手順
ログファイルと言っても、通常使われる人が確認するようのログファイルではないです。
mysql は テーブルスペースというファイルにデータを保存していますが、一つの大きなファイルなためアクセスに時間がかかるため、キャッシュ代わりにログファイルに一旦書き込んでいます。そのログファイルのサイズを変更します。
まず、サイズを確認してみましょう。
データのディレクトリを除いてみてください。
分からなければ、設定ファイル my.cnf に載っています。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
[vagrant@localhost test_db]$ cat /etc/my.cnf [mysqld] <span style="color: #ff0000;">datadir=/var/lib/mysql</span> socket=/var/lib/mysql/mysql.sock user=mysql # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid character-set-server=utf8 |
ディレクトリを覗いてみます。
下図の赤字が該当サイズです。以下ではすでに64MBになってますが、これが小さい場合、大きくしましょう。私はここのサイズが1MBぐらいだったです。
|
1 2 3 4 5 6 7 8 9 10 |
[vagrant@localhost test_db]$ ls -l /var/lib/mysql 合計 5376028 -rw-rw---- 1 mysql mysql <span style="color: #ff0000;">67108864</span> 5月 24 06:52 2018 ib_logfile0 -rw-rw---- 1 mysql mysql <span style="color: #ff0000;">67108864</span> 5月 24 06:52 2018 ib_logfile1 -rw-rw---- 1 mysql mysql 5370806272 5月 24 06:52 2018 ibdata1 drwx------ 2 mysql mysql 4096 5月 24 05:08 2018 mysql srwxrwxrwx 1 mysql mysql 0 5月 24 05:07 2018 mysql.sock -rw-r--r-- 1 root root 6 5月 23 16:37 2018 mysql_upgrade_info drwx------ 2 mysql mysql 4096 5月 23 16:37 2018 performance_schema drwx------ 2 mysql mysql 4096 5月 24 05:08 2018 test_db |
ログファイルサイズが小さかった場合、以下の手順で変更します。
手順1. シャットダウン設定の変更
まず、ログファイルがテーブルスペース(ibdata1)に反映されていない状態では、ログファイルのサイズ変更をすることができません。
なので、mysqlサーバーを停止した時、テーブルスペースへログファイルの内容が反映されるようにします。
以下のようにmysql で設定を確認＆必要に応じて再設定します。
下図で、innodb_fast_shutdown = 1 のとき、ログファイルがテーブルスペースへ反映されない設定になってますので、その場合、innodb_fast_shutdown = 0 に変更します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
mysql -uroot -p mysql> show variables like 'innodb_fast_shutdown'; +----------------------+-------+ | Variable_name | Value | +----------------------+-------+ | innodb_fast_shutdown | 1 | ※ 1 のため保存されない！変更必要 +----------------------+-------+ 1 row in set (0.00 sec) mysql> set global innodb_fast_shutdown = 0; Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'innodb_fast_shutdown'; +----------------------+-------+ | Variable_name | Value | +----------------------+-------+ | innodb_fast_shutdown | 0 | ※無事、0 に変更されています +----------------------+-------+ 1 row in set (0.00 sec) |
手順2.データベースのバックアップ
これは念の為ですが、データベースのバックアップを行なっておきます。
|
1 2 |
[root@localhost mysql]# mysqldump -uroot -p --opt --all-databases > alldb.sql Enter password: |
手順3. サーバーを停止します
|
1 2 |
[vagrant@localhost test_db]$ sudo /etc/init.d/mysqld stop mysqld を停止中: [ OK ] |
手順4.ログファイルを削除します
|
1 |
[vagrant@localhost test_db]$ sudo rm /var/lib/mysql/ib_logfile* |
手順5.設定ファイルを編集します。
下図では、[mysqld]の項目に、
innodb_log_file_size=64M
の行を追加してます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
[vagrant@localhost test_db]$ sudo vim /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 #to store large jpeg file innodb_log_file_size=64M [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid character-set-server=utf8 |
手順6. サーバーを起動します
|
1 2 |
[vagrant@localhost test_db]$ sudo /etc/init.d/mysqld start mysqld を起動中: |
以上でログファイルサイズが大きくなってるはずです。
最後にログファイルのサイズを確認しましょう。
|
1 2 3 4 5 6 7 8 9 10 |
[vagrant@localhost test_db]$ ls -l /var/lib/mysql 合計 33425436 -rw-rw---- 1 mysql mysql 67108864 5月 24 07:38 2018 ib_logfile0 -rw-rw---- 1 mysql mysql 67108864 5月 24 07:38 2018 ib_logfile1 -rw-rw---- 1 mysql mysql 34093400064 5月 24 07:38 2018 ibdata1 drwx------ 2 mysql mysql 4096 5月 24 05:08 2018 mysql srwxrwxrwx 1 mysql mysql 0 5月 24 05:07 2018 mysql.sock -rw-r--r-- 1 root root 6 5月 23 16:37 2018 mysql_upgrade_info drwx------ 2 mysql mysql 4096 5月 23 16:37 2018 performance_schema drwx------ 2 mysql mysql 4096 5月 24 05:08 2018 test_db |
上限パケットサイズの変更
サーバーとクライアントの間で転送できるデータサイズが、パケットサイズです。この上限を確認して、小さい場合は大きくしましょう。
確認方法は以下のとおりです。
|
1 2 3 4 5 6 7 8 9 10 |
mysql -uroot -p mysql> show variables like 'max_allowed_packet'; +--------------------+---------+ | Variable_name | Value | +--------------------+---------+ | max_allowed_packet | 1048576 | +--------------------+---------+ 1 row in set (0.00 sec) |
上図の状態だと、1MBに設定されています。
変更手順
my.cnfの編集
変更は、my.cnf に設定しましょう。
16MBまでやり取りできるようにする場合、[mysqld]の中に
max_allowed_packet=16MB
の記述を入れます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
[vagrant@localhost test_db]$ cat /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 #to store large jpeg file innodb_log_file_size=64M max_allowed_packet=16MB [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid character-set-server=utf8 |
サーバーの再起動
|
1 2 3 |
[vagrant@localhost test_db]$ sudo /etc/init.d/mysqld restart mysqld を停止中: [ OK ] mysqld を起動中: [ OK ] |
確認してみますと、以下のようにちゃんとmax_allowed_packetが変更されています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
[vagrant@localhost test_db]$ mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 41 Server version: 5.5.56 MySQL Community Server (GPL) by Remi Copyright (c) 2000, 2017, 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 variables like 'max_allowed_packet'; +--------------------+----------+ | Variable_name | Value | +--------------------+----------+ | max_allowed_packet | 16777216 | +--------------------+----------+ 1 row in set (0.03 sec) |
以上で、1MB以上の画像データをデータベースにストアすることができるようになりました。
コメント