An ansible role for deploying the percona db server
License: GNU Affero General Public License v3.0
Ansible playbook to install Percona Server on Debian/Ubuntu servers. Adapted from https://github.com/hpcloud-mon/ansible-percona
Ansible >= 2.5
Check the file defaults/main.yml for the role defaults.
As a minimum, override the variable mysql_root_password in your playbook as
it is set to a weak value by default.
MIT / BSD
Problem: Does not install Percona version 8 on Ubuntu 22.04 which is needed for Archivematica 1.16
this is a fork of https://github.com/overdrive3000/ansible-percona (MIT / BSD)
but https://github.com/artefactual-labs/ansible-percona/blob/master/LICENSE is agplv3
On CentOS, when the Percona/MySQL 5.7 packages are newly installed, a random root password is configured, unlike the default for previous versions (no root password set up). This causes the role to fail.
More information: https://www.percona.com/blog/2016/05/18/where-is-the-mysql-5-7-root-password/
Running the role on Bionic the GPG fails:
TASK [artefactual.percona : Adding percona repository] ***************************************************************************************************************************************
fatal: [001-atom]: FAILED! => {
"changed": false,
"failed": true,
"module_stderr": "Shared connection to 35.177.97.128 closed.\r\n",
"module_stdout": "Traceback (most recent call last):\r\n File \"/tmp/ansible_6D0ouN/ansible_module_apt_repository.py\", line 556, in <module>\r\n main()\r\n File \"/tmp/ansible_6D0ouN/ansible_module_apt_repository.py\", line 544, in main\r\n cache.update()\r\n File \"/usr/lib/python2.7/dist-packages/apt/cache.py\", line 559, in update\r\n raise FetchFailedException(e)\r\napt.cache.FetchFailedException: W:GPG error: http://repo.percona.com/apt bionic InRelease: The following signatures couldn't be verified because the public key is not available: NO_PUBKEY 9334A25F8507EFA5, E:The repository 'http://repo.percona.com/apt bionic InRelease' is not signed.\r\n",
"rc": 0
}
In some cases, Percona 5.7 seems not to be using the settings in /etc/my.cnf (for example checked: collation_server, character_set_server, innodb_log_file_size which are set in my.cnf). Not sure if this is an issue with this playbook or a general issue with percona 5.7. Occurring in both ubuntu and RedHat deploys.
PR #10 introduces a check of the python interpreter running ansible by using variable ansible_python_interpreter, but this variable is not defined by default (it needs to be explicitly defined at run time or in host_vars/group_vars).
Testing was performed considering commits:
OS images:
Settings (default):
mysql_version_major: "5"
mysql_version_minor: "6"
Errors:
TASK [ansible-percona : Install percona packages and dependencies on Ubuntu (Percona version < 8)] *************************
fatal: [test-debian11]: FAILED! => {"changed": false, "msg": "No package matching 'percona-server-server-5.6' is available"}
changed: [test-debian10]
changed: [test-debian9]
changed: [test-debian8]
Fixed:
TASK [ansible-percona : Install percona-xtrabackup on Ubuntu (Percona version < 8)] *********************************
fatal: [test-debian10]: FAILED! => {"changed": false, "msg": "No package matching 'percona-xtrabackup' is available"}
...ignoring
changed: [test-debian9]
changed: [test-debian8]
TASK [ansible-percona : Install percona-xtrabackup-24 on Ubuntu (Percona version < 8)] ******************************
skipping: [test-debian8]
skipping: [test-debian9]
changed: [test-debian10]
Results:
PLAY RECAP *******************************************************************************************************
test-debian10 : ok=23 changed=14 unreachable=0 failed=0 skipped=26 rescued=0 ignored=2
test-debian11 : ok=12 changed=6 unreachable=0 failed=1 skipped=7 rescued=0 ignored=1
test-debian8 : ok=22 changed=13 unreachable=0 failed=0 skipped=27 rescued=0 ignored=1
test-debian9 : ok=22 changed=14 unreachable=0 failed=0 skipped=27 rescued=0 ignored=1
Installation on test-debian11 failed.
Settings:
mysql_version_major: "8"
mysql_version_minor: "0"
Errors:
TASK [ansible-percona : Install percona packages and dependencies on Ubuntu (Percona version >= 8)] *****************************************************************************************************************************************
fatal: [test-debian8]: FAILED! => {
"cache_update_time": 1635334459,
"cache_updated": false,
"changed": false,
"msg": "'/usr/bin/apt-get -y -o \"Dpkg::Options::=--force-confdef\" -o \"Dpkg::Options::=--force-confold\" install 'percona-server-server=8.0*' 'percona-server-client=8.0*' 'percona-toolkit' 'percona-xtrabackup-80'' failed: E: Sub-process /usr/bin/dpkg returned an error code (1)\n",
"rc": 100,
"stderr": "E: Sub-process /usr/bin/dpkg returned an error code (1)\n",
"stderr_lines": [
"E: Sub-process /usr/bin/dpkg returned an error code (1)"
],
"stdout": "...",
"stdout_lines": [
"...",
"Setting up percona-server-server (8.0.13-4-1.jessie) ...",
"dpkg: error processing package percona-server-server (--configure):",
" subprocess installed post-installation script returned error exit status 1",
"Setting up libdbi-perl (1.631-3+b1) ...",
"Setting up libdbd-mysql-perl (4.028-2+deb8u2) ...",
"Setting up libfile-fcntllock-perl (0.22-1+b1) ...",
"Setting up libterm-readkey-perl (2.32-1+b1) ...",
"Setting up percona-toolkit (3.2.0-1.jessie) ...",
"Setting up rsync (3.1.1-3+deb8u2) ...",
"Setting up libev4 (1:4.15-3) ...",
"Setting up percona-xtrabackup-80 (8.0.4-1.jessie) ...",
"Processing triggers for libc-bin (2.19-18+deb8u10) ...",
"Processing triggers for systemd (215-17+deb8u13) ...",
"Errors were encountered while processing:",
" percona-server-server"
]
}
changed: [test-debian11]
changed: [test-debian9]
changed: [test-debian10]
Results:
PLAY RECAP *******************************************************************************************************
test-debian10 : ok=24 changed=14 unreachable=0 failed=0 skipped=25 rescued=0 ignored=1
test-debian11 : ok=25 changed=15 unreachable=0 failed=0 skipped=24 rescued=0 ignored=1
test-debian8 : ok=14 changed=6 unreachable=0 failed=1 skipped=9 rescued=0 ignored=1
test-debian9 : ok=24 changed=14 unreachable=0 failed=0 skipped=25 rescued=0 ignored=1
Installation on test-debian8 failed. The crux of the problem here is that python-mysqldb or python3-mysqldb must be installed after mysql-server.
Hello,
We are trying to upgrade archivematica from 1.14 to 1.15 (while also upgrading from Ubuntu 18.04 to Ubuntu 22.04) and when running the playbook it fails at installing Percona 5.7 because 5.6 is already installed, and when you try and uninstall percona 5.6, it wants to install MySQL 8.
Is this something that this role should be able to handle, or do I need to go and uninstall everything by hand, and than rerun the playbook.
Thanks!
ed24318 made the wily packages available in xenial installs. We should revert once percona provides official support for xenial.
Let's revisit in May 2016.
It seems percona has changed their repo names:
TASK [external-roles/artefactual.percona : Enable Percona repository (Percona version >= 8)] *********************************************************************************************************************************************************************************************
fatal: [wits-training]: FAILED! => {"changed": true, "cmd": ["percona-release", "setup", "ps80"], "delta": "0:00:00.764533", "end": "2021-04-10 10:19:17.004342", "msg": "non-zero return code", "rc": 2, "start": "2021-04-10 10:19:16.239809", "stderr": "", "stderr_lines": [], "stdout": "Specified repository is not supported for current operation system!", "stdout_lines": ["Specified repository is not supported for current operation system!"]}
Listing available repos with "percona-release --help":
-> Available repositories: original ps-56 ps-57 ps-80 pxc-56 pxc-57 pxc-80 psmdb-36 psmdb-40 psmdb-42 pxb-24 pxb-80 tools ppg-11 ppg-11.5 ppg-11.6 ppg-11.7 ppg-11.8 ppg-12 ppg-12.2 ppg-12.3 pdmdb-4.2 pdmdb-4.2.6 pdmdb-4.2.7 pdmdb-4.2.8 pdps-8.0.19 pdpxc-8.0.19 pdps-8.0.20
But the role is traying to enable "ps80" instead of "ps-80": https://github.com/artefactual-labs/ansible-percona/blob/master/tasks/install.yml#L23-L26
In new versions, the error log grows a lot because the warnings, for instance:
tail /var/log/mysql/error.log
2024-07-03T10:58:12.021741Z 239 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
2024-07-03T10:58:18.047134Z 240 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
2024-07-03T10:58:20.745714Z 241 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
2024-07-03T10:58:21.212055Z 242 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
2024-07-03T10:58:23.648498Z 243 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
2024-07-03T10:58:24.133764Z 244 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
2024-07-03T10:58:24.469938Z 245 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
2024-07-03T10:58:25.016826Z 246 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
2024-07-03T10:58:26.353797Z 247 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
2024-07-03T10:58:28.527040Z 248 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
We moved away from Travis CI to GitHub Actions (see archivematica/Issues#1329). But I didn't have much luck updating this repo, one of the permutations was not working for me for unknown reasons. For the record, my attempt is here: #53 (closed).
This role should allow to configure the skip_name_resolve variable in the my.cnf config file.
"skip_name_resolve = on" is a desirable option that should be enabled in almost all production servers.
Here is a thread that explains the multiple reasons related to use the above option:
https://www.percona.com/blog/2008/05/31/dns-achilles-heel-mysql-installation/
This variable is useful to fix the following mysql error (seen in AtoM 2.6 and percona 8):
2020/10/17 09:44:33 [error] 9559#9559: *547098 FastCGI sent in stderr: “PHP message: [wrapped: SQLSTATE[HY001]: Memory allocation error: 1038 Out of sort memory, consider increasing server sort buffer size]” while reading response header from upstream, client: XXXXXXXX, server: YYYYYYYYYY, request: “GET /jobs/browse HTTP/1.0", upstream: “fastcgi://unix:/var/run/php-fpm.ZZZZ.sock:“, host: “YYYYYYYYYYY”, referrer: “YYYYYYYYYYYYYYY/”
Default value is 256K
Ansible error in new deploy:
fatal: [template-cluster-mysql]: FAILED! => {"changed": false, "msg": "No package matching 'percona-xtrabackup' found available, installed or updated", "rc": 126, "results": ["No package matching 'percona-xtrabackup' found available, installed or updated"]}
Looking at server:
[root@mamedin-template-cluster-2 ~]# sudo yum search percona-xtrabackup
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
* base: mirror.dst.ca
* extras: centos-canada.altinsoft.net
* updates: centos-canada.vdssunucu.com.tr
============================================================================================================================ N/S matched: percona-xtrabackup =============================================================================================================================
percona-xtrabackup-24-debuginfo.x86_64 : Debug information for package percona-xtrabackup-24
percona-xtrabackup-80-debuginfo.x86_64 : Debug information for package percona-xtrabackup-80
percona-xtrabackup-24.x86_64 : XtraBackup online backup for MySQL / InnoDB
percona-xtrabackup-80.x86_64 : XtraBackup online backup for MySQL / InnoDB
percona-xtrabackup-test-24.x86_64 : Test suite for Percona XtraBackup
percona-xtrabackup-test-80.x86_64 : Test suite for Percona XtraBackup
The role is trying to install percona-xtrabackup but it doesn't exist.
Weird error because I used the same config 3 days ago and it worked on CentOS (new deployment too).
When the password is changed, .my.cnf diff shows the value in clear text. E.g. check run:
TASK [percona : Copy .my.cnf file into the root home folder] *******************
--- before: /root/.my.cnf
+++ after: /<redacted>/root-my-cnf.j2
@@ -1,3 +1,3 @@
[client]
user=root
-password="old"
+password="new"
This can ends up logged if run from CI/CD.
A possible fix could be to add diff: no to the task, but there might be other things to "fix" elsewhere.
When running the play against a host with an existing MySQL root password it fails on:
TASK [artefactual.percona : Set the root password] ************************************************************************************
failed: [ubuntu-18.04-atom-test] (item=ubuntu-18) => {"ansible_loop_var": "item", "changed": false, "item": "ubuntu-18", "msg": "unable to connect to database: (1045, \"Access denied for user 'root'@'localhost' (using password: NO)\")"}
failed: [ubuntu-18.04-atom-test] (item=127.0.0.1) => {"ansible_loop_var": "item", "changed": false, "item": "127.0.0.1", "msg": "unable to connect to database: (1045, \"Access denied for user 'root'@'localhost' (using password: NO)\")"}
failed: [ubuntu-18.04-atom-test] (item=::1) => {"ansible_loop_var": "item", "changed": false, "item": "::1", "msg": "unable to connect to database: (1045, \"Access denied for user 'root'@'localhost' (using password: NO)\")"}
failed: [ubuntu-18.04-atom-test] (item=localhost) => {"ansible_loop_var": "item", "changed": false, "item": "localhost", "msg": "unable to connect to database: (1045, \"Access denied for user 'root'@'localhost' (using password: NO)\")"}
This role doesn't work on CentOS/RedHat
This variable was added to support MySQL 8.0 and PHP 7.2, to be able to set it to mysql_native_password. However, PHP 7.4 now supports caching_sha2_password but the default in here is set to the former:
https://github.com/artefactual-labs/ansible-percona/blob/master/defaults/main.yml#L55
Since AtoM 2.7 will require PHP 7.4 (and I think that was the only use case for this legacy auth. method), we should consider changing the default to caching_sha2_password a more secure authentication method, or just remove it's default as we're checking that the var is defined before using it:
https://github.com/artefactual-labs/ansible-percona/search?q=mysql_default_authentication_plugin
I don't know exactly how this role works upgrading existing instances, but we should consider how this change could affect those instances where we may needed to update existing user passwords:
ALTER USER 'atom'@'localhost' IDENTIFIED WITH caching_sha2_password BY '12345';
Error:
TASK [artefactual-percona : Add Percona yum repository] *************************************************************************************************************
miércoles 03 julio 2024 19:09:29 +0200 (0:00:02.111) 0:57:59.085 *******
fatal: [XXXXXXXXXXXX]: FAILED! => {"changed": false, "msg": "Failed to validate GPG signature for percona-release-1.0-28.noarch: Public key for percona-release-latest.noarchxr6x9u00.rpm is not installed"}
AtoM 2.6 will require MySQL 8.0 after the implementation of CTE queries to improve some hierarchical queries.
The installation process seems to be a little bit different, but still supported on the OS we use:
https://www.percona.com/doc/percona-server/8.0/installation/apt_repo.html
https://www.percona.com/doc/percona-server/8.0/installation/yum_repo.html
The travis tests are failing since Oct 9th.
It is a bug related to the mysql_db module.
The workaround is:
socket=/var/run/mysqld/mysqld.sock in /root/.my.cnf to use the unix socket.Error:
An exception occurred during task execution. To see the full traceback, use -vvv. The error was: OperationalError: (1044, u"Access denied for user 'root'@'127.0.0.1' to database 'gnwt_test'")
fatal: [atom-test-sites -> None]: FAILED! => {"changed": false, "msg": "error creating database: (1044, u\"Access denied for user 'root'@'127.0.0.1' to database 'gnwt_test'\")"}
In https://github.com/artefactual-labs/ansible-percona/blob/master/tasks/users.yml#L6, the role looks for "pass", instead of "password". We should use password, to keep things coherent with the mysql_user task, but I'm afraid this change can break current installs.
The documentation says for Ubuntu 18.04LTS is 5.7, but I am not seeing anything that overwrite the mysql_minor_version https://github.com/artefactual-labs/ansible-percona/blob/master/defaults/main.yml#L5 in deploy-pub to be 5.7.
For 1.13 should we be using 5.6 or 5.7?
Thanks!
By default it uses bin_log, but it is disabled by default in MySQL <= 5.7
Related to #23
If the role is used to modify the value of innodb_log_file_size of an existing percona server installation, the database server service may fail to start due to an "InnoDB: Error: log file ./ib_logfile0 is of different size ...." error.
When desired to change the value of innodb_log_file_size of an existing installation, the suggested procedure is as follows (ref. here):
In the running MySQL instance, set innodb_fast_shutdown=0 (it is 1 per default):
mysql> set global innodb_fast_shutdown=0;
Query OK, 0 rows affected (0.31 sec)
mysql> show global variables like '%innodb_fast%';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| innodb_fast_shutdown | 0 |
+----------------------+-------+
1 row in set (0.00 sec)
Stop the database server service:
# systemctl stop mysql.service
Make the configuration change to the log file size (e.g., set innodb_log_file_size = 64M in the [mysqld] section of /etc/mysql/my.cnf)
Delete or rename ib_logfile0 and ib_logfile1 in /var/lib/mysql/
Start the database server service
# systemctl start mysql.service
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
Django
The Web framework for perfectionists with deadlines.
Laravel
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
Microsoft
Open source projects and samples from Microsoft.
Google
Google ❤️ Open Source for everyone.
Alibaba
Alibaba Open Source for everyone
D3
Data-Driven Documents codes.
Tencent
China tencent open source team.