Mercurial > hg > orthanc-book
annotate Sphinx/source/plugins/mysql.rst @ 684:96574b57249a
mysqldump
| author | Sebastien Jodogne <s.jodogne@gmail.com> |
|---|---|
| date | Wed, 12 May 2021 07:25:09 +0200 |
| parents | 411e82bb3a9f |
| children | 8b8df1be0cf9 |
| rev | line source |
|---|---|
| 154 | 1 .. _mysql: |
| 2 | |
| 3 | |
| 4 MySQL/MariaDB plugins | |
| 5 ===================== | |
| 6 | |
| 7 .. contents:: | |
| 8 | |
| 9 The Orthanc project provides two **official** plugins to replace the | |
| 10 default storage area (on the filesystem) and the default SQLite index | |
| 11 by a MySQL or a MariaDB database. | |
| 12 | |
| 13 For general information, check out the `official homepage of the | |
|
358
011b01ccf52d
fixing external hyperlinks
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
270
diff
changeset
|
14 plugins <https://www.orthanc-server.com/static.php?page=mysql>`__. |
| 154 | 15 |
|
643
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
16 For information about scalability, make sure to read the section about |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
17 :ref:`multiple writers in large-scale deployments <multiple-writers>`. |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
18 |
| 230 | 19 **Warning:** According to `this thread on our discussion group |
| 231 | 20 <https://groups.google.com/d/msg/orthanc-users/yV3LSTh_TjI/Fb4ShaYMBAAJ>`__, |
| 21 the MySQL/MariaDB plugins require MySQL 8.x if running on Microsoft | |
| 22 Windows. | |
| 230 | 23 |
| 154 | 24 |
| 25 | |
| 26 Compilation | |
| 27 ----------- | |
| 28 | |
| 29 Static linking | |
| 30 ^^^^^^^^^^^^^^ | |
| 31 | |
| 32 .. highlight:: text | |
| 33 | |
| 34 The procedure to compile these plugins is similar to that for the | |
| 35 :ref:`core of Orthanc <compiling>`. The following commands should work | |
| 36 for most UNIX-like distribution (including GNU/Linux):: | |
| 37 | |
| 38 $ mkdir BuildMySQL | |
| 39 $ cd BuildMySQL | |
| 40 $ cmake ../MySQL/ -DSTATIC_BUILD=ON -DCMAKE_BUILD_TYPE=Release | |
| 41 $ make | |
| 42 | |
| 43 The compilation will produce 2 shared libraries, each containing one plugin for Orthanc: | |
| 44 | |
| 45 * ``OrthancMySQLIndex`` replaces the default SQLite index of Orthanc by MySQL. | |
| 46 * ``OrthancMySQLStorage`` makes Orthanc store the DICOM files it receives into MySQL. | |
| 47 | |
| 48 | |
| 49 Microsoft Windows | |
| 50 ^^^^^^^^^^^^^^^^^ | |
| 51 | |
| 52 Pre-compiled binaries for Microsoft Windows `are also available | |
|
358
011b01ccf52d
fixing external hyperlinks
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
270
diff
changeset
|
53 <https://www.orthanc-server.com/browse.php?path=/plugin-mysql>`__. |
| 154 | 54 |
| 55 | |
| 56 Dynamic linking on Ubuntu 16.04 | |
| 57 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ | |
| 58 | |
| 59 .. highlight:: text | |
| 60 | |
| 61 If static linking is not desired, here are build instructions for | |
| 62 Ubuntu 16.04 (provided build dependencies for the :ref:`core of | |
| 63 Orthanc <compiling>` have already been installed):: | |
| 64 | |
| 65 $ sudo apt-get install libmysqlclient-dev | |
| 66 $ mkdir BuildMySQL | |
| 67 $ cd BuildMySQL | |
| 68 $ cmake ../MySQL/ -DCMAKE_BUILD_TYPE=Release \ | |
| 69 -DALLOW_DOWNLOADS=ON \ | |
| 70 -DUSE_SYSTEM_GOOGLE_TEST=OFF \ | |
| 71 -DUSE_SYSTEM_ORTHANC_SDK=OFF | |
| 72 $ make | |
| 73 | |
| 74 | |
| 75 Usage | |
| 76 ----- | |
| 77 | |
| 78 You of course first have to :ref:`install Orthanc <binaries>`, with a | |
| 79 version above 0.9.5. You then have to **create a database** dedicated | |
| 80 to Orthanc on some MySQL/MariaDB server. Please refer to the `MySQL | |
| 81 documentation | |
| 82 <https://dev.mysql.com/doc/refman/8.0/en/database-use.html>`__. | |
| 83 | |
| 84 .. highlight:: json | |
| 85 | |
| 86 Once Orthanc is installed and the database is created, you must add a | |
| 87 section in the :ref:`configuration file <configuration>` that | |
| 88 specifies the address of the **MySQL/MariaDB server together with your | |
| 89 credentials**. You also have to tell Orthanc in which path it can find | |
| 90 the plugins: This is done by properly modifying the ``Plugins`` | |
| 91 option. You could for instance adapt the following configuration | |
| 92 file:: | |
| 93 | |
| 94 { | |
| 95 "Name" : "MyOrthanc", | |
| 96 "MySQL" : { | |
| 97 "EnableIndex" : true, | |
| 98 "EnableStorage" : true, | |
| 158 | 99 "Host" : "localhost", // For TCP connections (notably Windows) |
| 100 "Port" : 3306, // For TCP connections (notably Windows) | |
| 154 | 101 "UnixSocket" : "/var/run/mysqld/mysqld.sock", // For UNIX on localhost |
| 102 "Database" : "orthanc", | |
| 103 "Username" : "orthanc", | |
| 104 "Password" : "orthanc", | |
| 465 | 105 "EnableSsl" : false, // force SSL connections |
|
643
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
106 "SslVerifyServerCertificates": true, // Verify server certificates if EnableSsl is true |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
107 "SslCACertificates": "", // Path to CA certificates to validate servers |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
108 "Lock" : true, // See section about Locking |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
109 "MaximumConnectionRetries" : 10, // New in release 3.0 |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
110 "ConnectionRetryInterval" : 5, // New in release 3.0 |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
111 "IndexConnectionsCount" : 1 // New in release 4.0 |
| 154 | 112 }, |
| 113 "Plugins" : [ | |
| 114 "/home/user/orthanc-databases/BuildMySQL/libOrthancMySQLIndex.so", | |
| 115 "/home/user/orthanc-databases/BuildMySQL/libOrthancMySQLStorage.so" | |
| 116 ] | |
| 117 } | |
| 118 | |
| 158 | 119 **Important 1:** The ``EnableIndex`` and ``EnableStorage`` options must |
| 154 | 120 be explicitly set to ``true``, otherwise Orthanc will continue to use |
| 121 its default SQLite back-end and the filesystem storage area. | |
| 122 | |
| 158 | 123 **Important 2:** To force a TCP connection on the ``localhost`` in |
| 124 UNIX (i.e. to instruct Orthanc not to use UNIX socket), the | |
| 125 ``UnixSocket`` can be set to the empty string. | |
| 126 | |
| 465 | 127 **Remark:** To force using a TLS connection, you must set ``EnableSsl`` |
| 128 to ``true``. Once ``EnableSsl`` is ``true``, the ``SslVerifyServerCertificates`` | |
| 129 enables the check of server certificates (``true`` by default). | |
| 130 The CA certificates used to verify the server certificate can be defined | |
| 131 through ``SslCACertificates``; if not defined or empty, the value of the global | |
| 132 Orthanc configuration ``HttpsCACertificates`` is used. These options have been | |
| 133 introduced in the mainline in July 2020 and have not yet been released. | |
| 134 | |
| 154 | 135 **Remark:** When using the ``Storage`` MySQL plugin, the DICOM files |
| 158 | 136 are stored as blobs in the database. This might actually consume more |
| 154 | 137 space than the DICOM file itself. |
| 138 | |
| 139 Note that a typical usage of the MySQL plugin is to enable only the | |
| 140 ``Index``, using the default filesystem storage for DICOM files. | |
| 141 | |
| 142 | |
| 143 | |
| 144 .. highlight:: text | |
| 145 | |
| 146 Orthanc must of course be **restarted** after the modification of its | |
| 147 configuration file. The log will contain an output similar to:: | |
| 148 | |
| 149 $ ./Orthanc Configuration.json | |
| 150 W0710 14:25:35.143828 main.cpp:1298] Orthanc version: 1.3.2 | |
| 151 W0710 14:25:35.146528 OrthancInitialization.cpp:120] Reading the configuration from: "./Configuration.json" | |
| 152 [...] | |
| 153 W0710 14:25:35.173652 main.cpp:671] Loading plugin(s) from: /home/jodogne/Subversion/orthanc-databases/BuildMySQL/libOrthancMySQLIndex.so | |
| 154 W0710 14:25:35.175927 PluginsManager.cpp:269] Registering plugin 'mysql-index' (version mainline) | |
| 155 W0710 14:25:35.176213 PluginsManager.cpp:168] Performance warning: The database index plugin was compiled against an old version of the Orthanc SDK, consider upgrading | |
| 156 W0710 14:25:35.176323 main.cpp:671] Loading plugin(s) from: /home/jodogne/Subversion/orthanc-databases/BuildMySQL/libOrthancMySQLStorage.so | |
| 157 W0710 14:25:35.177172 PluginsManager.cpp:269] Registering plugin 'mysql-storage' (version mainline) | |
| 158 W0710 14:25:35.180684 PluginsManager.cpp:168] Your MySQL server cannot store DICOM files larger than 16MB | |
| 159 W0710 14:25:35.180714 PluginsManager.cpp:168] => Consider increasing "max_allowed_packet" in "my.cnf" if this limit is insufficient for your use | |
| 160 W0710 14:25:35.246150 main.cpp:1098] Using a custom database from plugins | |
| 161 W0710 14:25:35.246210 main.cpp:1109] Using a custom storage area from plugins | |
| 162 [...] | |
| 163 W0710 14:25:37.073633 main.cpp:683] Orthanc has started | |
| 164 | |
| 165 | |
| 166 | |
| 167 Advanced options | |
| 168 ---------------- | |
| 169 | |
| 170 Several advanced options are available as well to fine-tune the | |
| 171 configuration of the MySQL plugins. They are documented below. | |
| 172 | |
| 173 | |
|
643
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
174 .. _mysql-multiple-writers: |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
175 |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
176 Multiple writers or connections |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
177 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
178 |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
179 Starting with Orthanc 1.9.2 and MySQL 4.0, it is possible to use |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
180 :ref:`multiple writers or connections in large-scale deployments |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
181 <multiple-writers>`. Here is the list of configuration that control |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
182 this behavior: |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
183 |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
184 * ``Lock`` must be set to ``false`` (cf. :ref:`below <mysql-lock>`) |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
185 |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
186 * ``MaximumConnectionRetries`` governs how many times Orthanc tries to |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
187 connect to the database, as well as how many times Orthanc replays |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
188 transactions to deal with collisions between multiple writers. |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
189 |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
190 * ``IndexConnectionsCount`` controls the number of connections from |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
191 the index plugin to the MySQL database. It is set to ``1`` by |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
192 default, which corresponds to the old behavior of Orthanc <= 1.9.1. |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
193 |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
194 * ``ConnectionRetryInterval`` is only used when opening one database |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
195 connection to MySQL. |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
196 |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
197 * As of release 4.0, the MySQL plugin does **not** support yet the |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
198 :ref:`revision mechanism <revisions>` to protect metadata and |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
199 attachments from concurrent modifications. |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
200 |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
201 |
| 154 | 202 Locking |
| 203 ^^^^^^^ | |
| 204 | |
| 205 .. highlight:: json | |
| 206 | |
| 207 By default, the plugins lock the database (using `MySQL/MariaDB | |
| 208 "GET_LOCK()" | |
|
358
011b01ccf52d
fixing external hyperlinks
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
270
diff
changeset
|
209 <https://dev.mysql.com/doc/refman/8.0/en/locking-functions.html>`__) |
| 154 | 210 to prevent other instances of Orthanc from using the same database. If |
| 211 you want several instances of Orthanc to share the same database, set | |
| 212 the ``Lock`` option to ``false`` in the configuration file. | |
| 213 | |
|
270
f29d75bc5c25
more info about database locking
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
231
diff
changeset
|
214 In the absence of locking, the same limitation apply to the |
|
f29d75bc5c25
more info about database locking
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
231
diff
changeset
|
215 MySQL/MariaDB plugins than to the PostgreSQL plugins (i.e. at most one |
|
f29d75bc5c25
more info about database locking
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
231
diff
changeset
|
216 instance of Orthanc writing to the database). For more information, |
|
f29d75bc5c25
more info about database locking
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
231
diff
changeset
|
217 please check out the :ref:`documentation for PostgreSQL |
|
f29d75bc5c25
more info about database locking
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
231
diff
changeset
|
218 <postgresql-lock>`. |
| 438 | 219 |
| 220 Scalability | |
| 221 ^^^^^^^^^^^ | |
| 222 | |
| 684 | 223 When configuring your MySQL plugin, ensure you've read the |
| 224 :ref:`scalability section <scalability>` | |
| 225 | |
| 226 | |
| 227 Backup | |
| 228 ------ | |
| 229 | |
| 230 The MySQL plugin uses stored routines (i.e. functions/procedures) that | |
| 231 are not archived by default by the ``mysqldump`` tool. As a | |
| 232 consequence, make sure to add the ``--routines`` command-line flag to | |
| 233 also archive such routines in your backup. |