diff options
Diffstat (limited to 'systems/backup-2/databases/mariadb_replication.nix')
-rw-r--r-- | systems/backup-2/databases/mariadb_replication.nix | 271 |
1 files changed, 271 insertions, 0 deletions
diff --git a/systems/backup-2/databases/mariadb_replication.nix b/systems/backup-2/databases/mariadb_replication.nix new file mode 100644 index 0000000..8d2b457 --- /dev/null +++ b/systems/backup-2/databases/mariadb_replication.nix | |||
@@ -0,0 +1,271 @@ | |||
1 | { pkgs, config, lib, ... }: | ||
2 | let | ||
3 | cfg = config.myServices.databasesReplication.mariadb; | ||
4 | in | ||
5 | { | ||
6 | options.myServices.databasesReplication.mariadb = { | ||
7 | enable = lib.mkEnableOption "Enable mariadb replication"; | ||
8 | base = lib.mkOption { | ||
9 | type = lib.types.path; | ||
10 | description = '' | ||
11 | Base path to put the replications | ||
12 | ''; | ||
13 | }; | ||
14 | hosts = lib.mkOption { | ||
15 | default = {}; | ||
16 | description = '' | ||
17 | Hosts to backup | ||
18 | ''; | ||
19 | type = lib.types.attrsOf (lib.types.submodule { | ||
20 | options = { | ||
21 | package = lib.mkOption { | ||
22 | type = lib.types.package; | ||
23 | default = pkgs.mariadb; | ||
24 | description = '' | ||
25 | Mariadb package for this host | ||
26 | ''; | ||
27 | }; | ||
28 | serverId = lib.mkOption { | ||
29 | type = lib.types.int; | ||
30 | description = '' | ||
31 | Server id to use for replication cluster (must be unique among the cluster!) | ||
32 | ''; | ||
33 | }; | ||
34 | host = lib.mkOption { | ||
35 | type = lib.types.str; | ||
36 | description = '' | ||
37 | Host to connect to | ||
38 | ''; | ||
39 | }; | ||
40 | port = lib.mkOption { | ||
41 | type = lib.types.int; | ||
42 | description = '' | ||
43 | Port to connect to | ||
44 | ''; | ||
45 | }; | ||
46 | user = lib.mkOption { | ||
47 | type = lib.types.str; | ||
48 | description = '' | ||
49 | User to connect as | ||
50 | ''; | ||
51 | }; | ||
52 | password = lib.mkOption { | ||
53 | type = lib.types.str; | ||
54 | description = '' | ||
55 | Password to use | ||
56 | ''; | ||
57 | }; | ||
58 | dumpUser = lib.mkOption { | ||
59 | type = lib.types.str; | ||
60 | description = '' | ||
61 | User who can do a dump | ||
62 | ''; | ||
63 | }; | ||
64 | dumpPassword = lib.mkOption { | ||
65 | type = lib.types.str; | ||
66 | description = '' | ||
67 | Password for the dump user | ||
68 | ''; | ||
69 | }; | ||
70 | }; | ||
71 | }); | ||
72 | }; | ||
73 | }; | ||
74 | |||
75 | config = lib.mkIf cfg.enable { | ||
76 | myServices.chatonsProperties.hostings.mysql-replication = { | ||
77 | file.datetime = "2022-08-27T15:00:00"; | ||
78 | hosting = { | ||
79 | name = "Mysql replication"; | ||
80 | description = "Replication of mysql database"; | ||
81 | website = "db-1.immae.eu"; | ||
82 | status.level = "OK"; | ||
83 | status.description = "OK"; | ||
84 | registration.load = "OPEN"; | ||
85 | install.type = "PACKAGE"; | ||
86 | }; | ||
87 | software = { | ||
88 | name = "MariaDB"; | ||
89 | website = "https://mariadb.org/"; | ||
90 | license.url = "https://github.com/MariaDB/server/blob/10.11/COPYING"; | ||
91 | license.name = "GNU General Public License v2.0"; | ||
92 | version = pkgs.mariadb.version; | ||
93 | source.url = "https://github.com/MariaDB/server"; | ||
94 | }; | ||
95 | }; | ||
96 | users.users.mysql = { | ||
97 | description = "MySQL server user"; | ||
98 | group = "mysql"; | ||
99 | uid = config.ids.uids.mysql; | ||
100 | extraGroups = [ "keys" ]; | ||
101 | }; | ||
102 | users.groups.mysql.gid = config.ids.gids.mysql; | ||
103 | |||
104 | secrets.keys = lib.listToAttrs (lib.flatten (lib.mapAttrsToList (name: hcfg: [ | ||
105 | (lib.nameValuePair "mysql_replication/${name}/slave_init_commands" { | ||
106 | user = "mysql"; | ||
107 | group = "mysql"; | ||
108 | permissions = "0400"; | ||
109 | text = '' | ||
110 | CHANGE MASTER TO master_host="${hcfg.host}", master_port=${builtins.toString hcfg.port}, master_user="${hcfg.user}", master_password="${hcfg.password}", master_ssl=1, master_use_gtid=slave_pos; | ||
111 | START SLAVE; | ||
112 | ''; | ||
113 | }) | ||
114 | (lib.nameValuePair "mysql_replication/${name}/mysqldump_remote" { | ||
115 | permissions = "0400"; | ||
116 | user = "root"; | ||
117 | group = "root"; | ||
118 | text = '' | ||
119 | [mysqldump] | ||
120 | user = ${hcfg.user} | ||
121 | password = ${hcfg.password} | ||
122 | ''; | ||
123 | }) | ||
124 | (lib.nameValuePair "mysql_replication/${name}/mysqldump" { | ||
125 | permissions = "0400"; | ||
126 | user = "root"; | ||
127 | group = "root"; | ||
128 | text = '' | ||
129 | [mysqldump] | ||
130 | user = ${hcfg.dumpUser} | ||
131 | password = ${hcfg.dumpPassword} | ||
132 | ''; | ||
133 | }) | ||
134 | (lib.nameValuePair "mysql_replication/${name}/client" { | ||
135 | permissions = "0400"; | ||
136 | user = "mysql"; | ||
137 | group = "mysql"; | ||
138 | text = '' | ||
139 | [client] | ||
140 | user = ${hcfg.dumpUser} | ||
141 | password = ${hcfg.dumpPassword} | ||
142 | ''; | ||
143 | }) | ||
144 | ]) cfg.hosts)); | ||
145 | |||
146 | services.cron = { | ||
147 | enable = true; | ||
148 | systemCronJobs = lib.flatten (lib.mapAttrsToList (name: hcfg: | ||
149 | let | ||
150 | dataDir = "${cfg.base}/${name}/mysql"; | ||
151 | backupDir = "${cfg.base}/${name}/mysql_backup"; | ||
152 | backup_script = pkgs.writeScript "backup_mysql_${name}" '' | ||
153 | #!${pkgs.stdenv.shell} | ||
154 | |||
155 | set -euo pipefail | ||
156 | |||
157 | filename=${backupDir}/$(${pkgs.coreutils}/bin/date -Iminutes).sql | ||
158 | ${hcfg.package}/bin/mysqldump \ | ||
159 | --defaults-file=${config.secrets.fullPaths."mysql_replication/${name}/mysqldump"} \ | ||
160 | -S /run/mysqld_${name}/mysqld.sock \ | ||
161 | --gtid \ | ||
162 | --master-data \ | ||
163 | --flush-privileges \ | ||
164 | --ignore-database=netdata \ | ||
165 | --all-databases > $filename | ||
166 | ${pkgs.gzip}/bin/gzip $filename | ||
167 | ''; | ||
168 | u = pkgs.callPackage ./utils.nix {}; | ||
169 | cleanup_script = pkgs.writeScript "cleanup_mysql_${name}" (u.exponentialDumps "sql.gz" backupDir); | ||
170 | in [ | ||
171 | "0 22,4,10,16 * * * root ${backup_script}" | ||
172 | "0 3 * * * root ${cleanup_script}" | ||
173 | ]) cfg.hosts); | ||
174 | }; | ||
175 | |||
176 | system.activationScripts = lib.attrsets.mapAttrs' (name: hcfg: | ||
177 | lib.attrsets.nameValuePair "mysql_replication_${name}" { | ||
178 | deps = [ "users" "groups" ]; | ||
179 | text = '' | ||
180 | install -m 0700 -o mysql -g mysql -d ${cfg.base}/${name}/mysql | ||
181 | install -m 0700 -o mysql -g mysql -d ${cfg.base}/${name}/mysql_backup | ||
182 | ''; | ||
183 | }) cfg.hosts; | ||
184 | |||
185 | environment.etc = lib.attrsets.mapAttrs' (name: hcfg: | ||
186 | lib.attrsets.nameValuePair "mysql/${name}_my.cnf" { | ||
187 | text = '' | ||
188 | [mysqld] | ||
189 | skip-networking | ||
190 | socket = /run/mysqld_${name}/mysqld.sock | ||
191 | datadir = ${cfg.base}/${name}/mysql/ | ||
192 | log-bin = mariadb-bin | ||
193 | server-id = ${builtins.toString hcfg.serverId} | ||
194 | ''; | ||
195 | } | ||
196 | ) cfg.hosts; | ||
197 | |||
198 | systemd.services = lib.attrsets.mapAttrs' (name: hcfg: | ||
199 | let | ||
200 | dataDir = "${cfg.base}/${name}/mysql"; | ||
201 | in | ||
202 | lib.attrsets.nameValuePair "mysql_backup_${name}" { | ||
203 | description = "Mysql replication for ${name}"; | ||
204 | wantedBy = [ "multi-user.target" ]; | ||
205 | after = [ "network.target" ]; | ||
206 | restartTriggers = [ config.environment.etc."mysql/${name}_my.cnf".source ]; | ||
207 | unitConfig.RequiresMountsFor = dataDir; | ||
208 | |||
209 | preStart = '' | ||
210 | if ! test -e ${dataDir}/mysql; then | ||
211 | if ! test -e ${dataDir}/initial.sql; then | ||
212 | ${hcfg.package}/bin/mysqldump \ | ||
213 | --defaults-file=${config.secrets.fullPaths."mysql_replication/${name}/mysqldump_remote"} \ | ||
214 | -h ${hcfg.host} \ | ||
215 | -P ${builtins.toString hcfg.port} \ | ||
216 | --ssl \ | ||
217 | --gtid \ | ||
218 | --flush-privileges \ | ||
219 | --master-data \ | ||
220 | --all-databases > ${dataDir}/initial.sql | ||
221 | fi | ||
222 | |||
223 | ${hcfg.package}/bin/mysql_install_db \ | ||
224 | --defaults-file=/etc/mysql/${name}_my.cnf \ | ||
225 | --user=mysql \ | ||
226 | --datadir=${dataDir} \ | ||
227 | --basedir=${hcfg.package} | ||
228 | fi | ||
229 | ''; | ||
230 | |||
231 | serviceConfig = { | ||
232 | User = "mysql"; | ||
233 | Group = "mysql"; | ||
234 | RuntimeDirectory = "mysqld_${name}"; | ||
235 | RuntimeDirectoryMode = "0755"; | ||
236 | SupplementaryGroups = "keys"; | ||
237 | PermissionsStartOnly = true; | ||
238 | Type = "notify"; | ||
239 | |||
240 | ExecStart = "${hcfg.package}/bin/mysqld --defaults-file=/etc/mysql/${name}_my.cnf --user=mysql --datadir=${dataDir} --basedir=${hcfg.package}"; | ||
241 | ExecStartPost = | ||
242 | let | ||
243 | sql_before = pkgs.writeText "mysql-initial-before" '' | ||
244 | DROP DATABASE test; | ||
245 | INSTALL SONAME 'auth_pam'; | ||
246 | ''; | ||
247 | setupScript = pkgs.writeScript "mysql-setup" '' | ||
248 | #!${pkgs.runtimeShell} -e | ||
249 | |||
250 | if test -e ${dataDir}/initial.sql; then | ||
251 | cat \ | ||
252 | ${sql_before} \ | ||
253 | ${dataDir}/initial.sql \ | ||
254 | ${config.secrets.fullPaths."mysql_replication/${name}/slave_init_commands"} \ | ||
255 | | ${hcfg.package}/bin/mysql \ | ||
256 | --defaults-file=/etc/mysql/${name}_my.cnf \ | ||
257 | -S /run/mysqld_${name}/mysqld.sock \ | ||
258 | --user=root | ||
259 | rm -f ${dataDir}/initial.sql | ||
260 | fi | ||
261 | ''; | ||
262 | in | ||
263 | "+${setupScript}"; | ||
264 | # initial dump can take a long time | ||
265 | TimeoutStartSec="infinity"; | ||
266 | TimeoutStopSec = 120; | ||
267 | }; | ||
268 | }) cfg.hosts; | ||
269 | }; | ||
270 | } | ||
271 | |||