database_mysql.md 13.5 KB
Newer Older
Ben Bodenmiller's avatar
Ben Bodenmiller committed
1
# Database MySQL
Marin Jankovski's avatar
Marin Jankovski committed
2

3 4 5 6 7 8 9 10 11 12 13
NOTE: **Note:**
We do not recommend using MySQL due to various issues.
For example, there have been bugs with case
[(in)sensitivity](https://dev.mysql.com/doc/refman/5.7/en/case-sensitivity.html).

Bugs relating to case sensitivity:

- <https://bugs.mysql.com/bug.php?id=65830>
- <https://bugs.mysql.com/bug.php?id=50909>
- <https://bugs.mysql.com/bug.php?id=65830>
- <https://bugs.mysql.com/bug.php?id=63164>
14

15
## Initial database setup
16

17
```sh
18 19
# Install the database packages
sudo apt-get install -y mysql-server mysql-client libmysqlclient-dev
20

21
# Ensure you have MySQL version 5.6 or later
22
mysql --version
23

24 25
# Pick a MySQL root password (can be anything), type it and press enter
# Retype the MySQL root password and press enter
26

27 28
# Secure your installation
sudo mysql_secure_installation
29

30 31
# Login to MySQL
mysql -u root -p
32

33
# Type the MySQL root password
34

35 36 37 38
# Create a user for GitLab
# do not type the 'mysql>', this is part of the prompt
# change $password in the command below to a real password you pick
mysql> CREATE USER 'git'@'localhost' IDENTIFIED BY '$password';
Robert Speicher's avatar
Robert Speicher committed
39

40 41 42
# Ensure you can use the InnoDB engine which is necessary to support long indexes
# If this fails, check your MySQL config files (e.g. `/etc/mysql/*.cnf`, `/etc/mysql/conf.d/*`) for the setting "innodb = off"
mysql> SET storage_engine=INNODB;
43

44 45
# If you have MySQL < 5.7.7 and want to enable utf8mb4 character set support with your GitLab install, you must set the following NOW:
mysql> SET GLOBAL innodb_file_per_table=1, innodb_file_format=Barracuda, innodb_large_prefix=1;
46

47 48 49
# If you use MySQL with replication, or just have MySQL configured with binary logging, you need to run the following to allow the use of `TRIGGER`:
mysql> SET GLOBAL log_bin_trust_function_creators = 1;

50 51
# Create the GitLab production database
mysql> CREATE DATABASE IF NOT EXISTS `gitlabhq_production` DEFAULT CHARACTER SET `utf8` COLLATE `utf8_general_ci`;
52

53
# Grant the GitLab user necessary permissions on the database
54
mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, CREATE TEMPORARY TABLES, DROP, INDEX, ALTER, LOCK TABLES, REFERENCES, TRIGGER ON `gitlabhq_production`.* TO 'git'@'localhost';
55

56 57
# Quit the database session
mysql> \q
58

59 60
# Try connecting to the new database with the new user
sudo -u git -H mysql -u git -p -D gitlabhq_production
61

62
# Type the password you replaced $password with earlier
63

64
# You should now see a 'mysql>' prompt
65

66 67 68
# Quit the database session
mysql> \q
```
69

70
You are done installing the database for now and can go back to the rest of the installation.
71 72 73 74 75 76 77 78 79
Please proceed to the rest of the installation **before** running through the steps below.

### `log_bin_trust_function_creators`

If you use MySQL with replication, or just have MySQL configured with binary logging, all of your MySQL servers will need to have `log_bin_trust_function_creators` enabled to allow the use of `TRIGGER` in migrations. You have already set this global variable in the steps above, but to make it persistent, add the following to your `my.cnf` file:

```
log_bin_trust_function_creators=1
```
80 81 82

### MySQL utf8mb4 support

83
After installation or upgrade, remember to [convert any new tables](#tables-and-data-conversion-to-utf8mb4) to `utf8mb4`/`utf8mb4_general_ci`.
84 85 86

---

87
GitLab 8.14 has introduced [a feature](https://gitlab.com/gitlab-org/gitlab-ce/merge_requests/7420) requiring `utf8mb4` encoding to be supported in your GitLab MySQL Database, which is not the case if you have set up your database before GitLab 8.16.
88 89 90 91

Follow the below instructions to ensure you use the most up to date requirements for your GitLab MySQL Database.

**We are about to do the following:**
92

93
- Ensure you can enable `utf8mb4` encoding and `utf8mb4_general_ci` collation for your GitLab DB, tables and data.
94
- Convert your GitLab tables and data from `utf8`/`utf8_general_ci` to `utf8mb4`/`utf8mb4_general_ci`.
95 96 97 98 99

### Check for utf8mb4 support

#### Check for InnoDB File-Per-Table Tablespaces

100
We need to check, enable and maybe convert your existing GitLab DB tables to the [InnoDB File-Per-Table Tablespaces](https://dev.mysql.com/doc/refman/5.7/en/innodb-multiple-tablespaces.html) as a prerequisite for supporting **utfb8mb4 with long indexes** required by recent GitLab databases.
101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136

    # Login to MySQL
    mysql -u root -p

    # Type the MySQL root password
    mysql > use gitlabhq_production;

    # Check your MySQL version is >= 5.5.3 (GitLab requires 5.5.14+)
    mysql > SHOW VARIABLES LIKE 'version';
    +---------------+-----------------+
    | Variable_name | Value           |
    +---------------+-----------------+
    | version       | 5.5.53-0+deb8u1 |
    +---------------+-----------------+

    # Note where is your MySQL data dir for later:
    mysql > select @@datadir;
    +----------------+
    | @@datadir      |
    +----------------+
    | /var/lib/mysql |
    +----------------+

    # Note whether your MySQL server runs with innodb_file_per_table ON or OFF:
    mysql> SELECT @@innodb_file_per_table;
    +-------------------------+
    | @@innodb_file_per_table |
    +-------------------------+
    |                       1 |
    +-------------------------+

    # You can now quit the database session
    mysql> \q

> You need **MySQL 5.5.3 or later** to perform this update.

137
Whatever the results of your checks above, we now need to check if your GitLab database has been created using [InnoDB File-Per-Table Tablespaces](https://dev.mysql.com/doc/refman/5.7/en/innodb-multiple-tablespaces.html) (i.e. `innodb_file_per_table` was set to **1** at initial setup time).
138

139 140
NOTE: **Note:**
This setting is [enabled by default](http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_file_per_table) since MySQL 5.6.6.
141 142 143 144 145 146 147 148 149

    # Run this command with root privileges, replace the data dir if different:
    sudo ls -lh /var/lib/mysql/gitlabhq_production/*.ibd | wc -l

    # Run this command with root privileges, replace the data dir if different:
    sudo ls -lh /var/lib/mysql/gitlabhq_production/*.frm | wc -l

- **Case 1: a result > 0 for both commands**

150
Congratulations, your GitLab database uses the right InnoDB tablespace format.
151 152 153 154

However, you must still ensure that any **future tables** created by GitLab will still use the right format:

- If `SELECT @@innodb_file_per_table` returned **1** previously, your server is running correctly.
155

156
    > It's however a requirement to check *now* that this setting is indeed persisted in your [`my.cnf`](https://dev.mysql.com/doc/refman/5.7/en/innodb-multiple-tablespaces.html) file!
157 158

- If `SELECT @@innodb_file_per_table` returned **0** previously, your server is not running correctly.
159

160
    > [Enable innodb_file_per_table](https://dev.mysql.com/doc/refman/5.7/en/innodb-multiple-tablespaces.html) by running in a MySQL session as root the command `SET GLOBAL innodb_file_per_table=1, innodb_file_format=Barracuda;` and persist the two settings in your [`my.cnf`](https://dev.mysql.com/doc/refman/5.7/en/innodb-multiple-tablespaces.html) file.
161 162 163 164 165

Now, if you have a **different result** returned by the 2 commands above, it means you have a **mix of tables format** uses in your GitLab database. This can happen if your MySQL server had different values for `innodb_file_per_table` in its life and you updated GitLab at different moments with those inconsistent values. So keep reading.

- **Case 2: a result equals to "0" OR not the same result for both commands**

166
Unfortunately, none or only some of your GitLab database tables use the GitLab requirement of [InnoDB File-Per-Table Tablespaces](https://dev.mysql.com/doc/refman/5.7/en/innodb-multiple-tablespaces.html).
167 168 169 170 171 172 173 174 175 176 177 178 179 180

Let's enable what we need on the running server:

    # Login to MySQL
    mysql -u root -p

    # Type the MySQL root password

    # Enable innodb_file_per_table and set innodb_file_format on the running server:
    mysql > SET GLOBAL innodb_file_per_table=1, innodb_file_format=Barracuda;

    # You can now quit the database session
    mysql> \q

181
> Now, **persist** [innodb_file_per_table](https://dev.mysql.com/doc/refman/5.7/en/innodb-multiple-tablespaces.html) and [innodb_file_format](https://dev.mysql.com/doc/refman/5.7/en/innodb-file-format-enabling.html) in your `my.cnf` file.
182 183 184 185 186 187 188 189 190 191 192

Ensure at this stage that your GitLab instance is indeed **stopped**.

Now, let's convert all the GitLab database tables to the new tablespace format:

    # Login to MySQL
    mysql -u root -p

    # Type the MySQL root password
    mysql > use gitlabhq_production;

193
    # Safety check: you should still have those values set as follows:
194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211
    mysql> SELECT @@innodb_file_per_table, @@innodb_file_format;
    +-------------------------+----------------------+
    | @@innodb_file_per_table | @@innodb_file_format |
    +-------------------------+----------------------+
    |                       1 | Barracuda            |
    +-------------------------+----------------------+

    mysql > SELECT CONCAT('ALTER TABLE `', TABLE_NAME,'` ENGINE=InnoDB;') AS 'Copy & run these SQL statements:' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA="gitlabhq_production" AND TABLE_TYPE="BASE TABLE";

    # If previous query returned results, copy & run all shown SQL statements

    # You can now quit the database session
    mysql> \q

---

#### Check for proper InnoDB File Format, Row Format, Large Prefix and tables conversion

212
We need to check, enable and probably convert your existing GitLab DB tables to use the [Barracuda InnoDB file format](https://dev.mysql.com/doc/refman/5.7/en/innodb-file-format.html), the [DYNAMIC row format](https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_dynamic_row_format) and [innodb_large_prefix](http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_large_prefix) as a second prerequisite for supporting **utfb8mb4 with long indexes** used by recent GitLab databases.
213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237

    # Login to MySQL
    mysql -u root -p

    # Type the MySQL root password
    mysql > use gitlabhq_production;

    # Set innodb_file_format and innodb_large_prefix on the running server:
    # Note: These are the default settings only for MySQL 5.7.7 and later.

    mysql > SET GLOBAL innodb_file_format=Barracuda, innodb_large_prefix=1;

    # Your DB must be (still) using utf8/utf8_general_ci as default encoding and collation.
    # We will NOT change the default encoding and collation on the DB in order to support future GitLab migrations creating tables
    # that require "long indexes support" on installations using MySQL <= 5.7.9.
    # However, when such migrations occur, you will have to follow this guide again to convert the newly created tables to the proper encoding/collation.

    # This should return the following:
    mysql> SELECT @@character_set_database, @@collation_database;
    +--------------------------+----------------------+
    | @@character_set_database | @@collation_database |
    +--------------------------+----------------------+
    | utf8                     | utf8_general_ci      |
    +--------------------------+----------------------+

238
> Now, ensure that [innodb_file_format](https://dev.mysql.com/doc/refman/5.7/en/innodb-multiple-tablespaces.html) and [innodb_large_prefix](http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_large_prefix) are **persisted** in your `my.cnf` file.
239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265

#### Tables and data conversion to utf8mb4

Now that you have a persistent MySQL setup, you can safely upgrade tables after setup or upgrade time:

    # Convert tables not using ROW_FORMAT DYNAMIC:

    mysql> SELECT CONCAT('ALTER TABLE `', TABLE_NAME,'` ROW_FORMAT=DYNAMIC;') AS 'Copy & run these SQL statements:' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA="gitlabhq_production" AND TABLE_TYPE="BASE TABLE" AND ROW_FORMAT!="Dynamic";

    # !! If previous query returned results, copy & run all shown SQL statements

    # Convert tables/columns not using utf8mb4/utf8mb4_general_ci as encoding/collation:

    mysql > SET foreign_key_checks = 0;

    mysql > SELECT CONCAT('ALTER TABLE `', TABLE_NAME,'` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;') AS 'Copy & run these SQL statements:' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA="gitlabhq_production" AND TABLE_COLLATION != "utf8mb4_general_ci" AND TABLE_TYPE="BASE TABLE";

    # !! If previous query returned results, copy & run all shown SQL statements

    # Turn foreign key checks back on
    mysql > SET foreign_key_checks = 1;

    # You can now quit the database session
    mysql> \q

Ensure your GitLab database configuration file uses a proper connection encoding and collation:

266
`sudo -u git -H editor config/database.yml`
267 268 269 270 271 272 273 274

    production:
      adapter: mysql2
      encoding: utf8mb4
      collation: utf8mb4_general_ci

[Restart your GitLab instance](../administration/restart_gitlab.md).

275 276
## MySQL strings limits

Robert Speicher's avatar
Robert Speicher committed
277
After installation or upgrade, remember to run the `add_limits_mysql` Rake task:
278

279
**Omnibus GitLab installations**
280 281

```sh
282 283 284 285 286
sudo gitlab-rake add_limits_mysql
```

**Installations from source**

287
```sh
288
bundle exec rake add_limits_mysql RAILS_ENV=production
289 290
```

Robert Speicher's avatar
Robert Speicher committed
291 292 293
The `text` type in MySQL has a different size limit than the `text` type in
PostgreSQL. In MySQL `text` columns are limited to ~65kB, whereas in PostgreSQL
`text` columns are limited up to ~1GB!
294

Robert Speicher's avatar
Robert Speicher committed
295 296 297
The `add_limits_mysql` Rake task converts some important `text` columns in the
GitLab database to `longtext` columns, which can persist values of up to 4GB
(sometimes less if the value contains multibyte characters).
298

Robert Speicher's avatar
Robert Speicher committed
299
Details can be found in the [PostgreSQL][postgres-text-type] and
300
[MySQL](https://dev.mysql.com/doc/refman/5.7/en/string-type-overview.html) manuals.
301

302
[postgres-text-type]: http://www.postgresql.org/docs/9.2/static/datatype-character.html
303
[ce-38152]: https://gitlab.com/gitlab-org/gitlab-ce/issues/38152