search

geerlingguy / ansible-role-mysql

Ansible Role - MySQL
https://galaxy.ansible.com/geerlingguy/mysql/
MIT License
1.05k stars 861 forks source link

MySQL fails to start when `mysql_datadir` is set to anything other than MySQL's default #510

Open liammcdermott opened 1 year ago

liammcdermott commented 1 year ago

Our Ansible playbooks use this role and set the MySQL datadir like this:

mysql_datadir: /data/mysql

When this is set the plays fail with this error on every database server:

fatal: [db05]: FAILED! => {"changed": false, "msg": "Unable to start service mysql: Job for mysql.service failed because the control process exited with error code.\nSee \"systemctl status mysql.service\" and \"journalctl -xeu mysql.service\" for details.\n"}

Then running sudo journalctl -xeu mysql turns up the following:

mysql-systemd-start[450456]: MySQL system database not found in /data/mysql. Please run mysqld --initialize

Unfortunately, running mysqld --initialize appears to work, but causes plays to fail at a later step with a MySQL permissions error. So it's not a viable workaround.

This issue is a duplicate of #399 , the bot closed it, despite it being a current issue.

OS: Ubuntu 22.04 MySQL version: /usr/sbin/mysqld Ver 8.0.31-0ubuntu0.22.04.1 for Linux on x86_64 ((Ubuntu))

liammcdermott commented 1 year ago

Note: I have a task to disable AppArmor for MySQL, so that's probably not the cause.

xeonic-ant commented 1 year ago

try to use mysqld --initialize-insecure

liammcdermott commented 1 year ago

@xeonic-ant Ah, thanks! I found another workaround (symlinking /var/lib/mysql → /data/mysql), but if I hadn't I'd definitely give that a try.

Just want to note: even with a workaround, this role professes support for changing the datadir, so it should work.

misilot commented 1 year ago

@geerlingguy any suggestions on what to do? I am running into this trying to setup a new server on Ubuntu 22.04.

We disable / mask apparmor, so it isn't apparmor stopping it.

My settings


mysql_root_username: root
mysql_root_password: password
mysql_port: 3306

mysql_datadir: "/klib/data/mysql"
mysql_root_password_update: true

This is the error log after a fresh install and run of the playbook.

2023-02-23T16:30:26.338343Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.32-0ubuntu0.22.04.2) starting as process 14533
2023-02-23T16:30:26.354926Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-02-23T16:30:26.913776Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-02-23T16:30:27.243003Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2023-02-23T16:30:27.243049Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2023-02-23T16:30:27.251935Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/tmp' in the path is accessible to all OS users. Consider choosing a different directory.
2023-02-23T16:30:27.302556Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: /var/run/mysqld/mysqlx.sock
2023-02-23T16:30:27.302611Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.32-0ubuntu0.22.04.2'  socket: '/tmp/tmp.wg18ZQFT1Q/mysqld.sock'  port: 0  (Ubuntu).
2023-02-23T16:30:27.352552Z 0 [System] [MY-013172] [Server] Received SHUTDOWN from user <via user signal>. Shutting down mysqld (Version: 8.0.32-0ubuntu0.22.04.2).
2023-02-23T16:30:28.797435Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.32-0ubuntu0.22.04.2)  (Ubuntu).
2023-02-23T16:30:29.739675Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.32-0ubuntu0.22.04.2) starting as process 14604
2023-02-23T16:30:29.747078Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-02-23T16:30:29.977136Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-02-23T16:30:30.208167Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2023-02-23T16:30:30.208219Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2023-02-23T16:30:30.215713Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/tmp' in the path is accessible to all OS users. Consider choosing a different directory.
2023-02-23T16:30:30.242733Z 7 [System] [MY-013172] [Server] Received SHUTDOWN from user boot. Shutting down mysqld (Version: 8.0.32-0ubuntu0.22.04.2).
2023-02-23T16:30:30.248421Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '127.0.0.1' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2023-02-23T16:30:31.778579Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.32-0ubuntu0.22.04.2)  (Ubuntu).
misilot commented 1 year ago

I made a PR that will initialize a custom data directory. This does not handle any AppArmor issues, since we made the decision at this point to disable AppArmor locally.

github-actions[bot] commented 1 year ago

This issue has been marked 'stale' due to lack of recent activity. If there is no further activity, the issue will be closed in another 30 days. Thank you for your contribution!

Please read this blog post to see the reasons why I mark issues as stale.

liammcdermott commented 1 year ago

I don't see anything in the commit logs to suggest this is fixed, so don't mark it stale yet, bot 👍

fidanf commented 1 year ago

Same problem here

github-actions[bot] commented 9 months ago

This issue has been marked 'stale' due to lack of recent activity. If there is no further activity, the issue will be closed in another 30 days. Thank you for your contribution!

Please read this blog post to see the reasons why I mark issues as stale.

misilot commented 9 months ago

not stle

On Sat, Dec 9, 2023 at 2:58 PM github-actions[bot] @.***> wrote:

This issue has been marked 'stale' due to lack of recent activity. If there is no further activity, the issue will be closed in another 30 days. Thank you for your contribution!

Please read this blog post https://www.jeffgeerling.com/blog/2020/enabling-stale-issue-bot-on-my-github-repositories to see the reasons why I mark issues as stale.

— Reply to this email directly, view it on GitHub https://github.com/geerlingguy/ansible-role-mysql/issues/510#issuecomment-1848680909, or unsubscribe https://github.com/notifications/unsubscribe-auth/AALBHSFNQAEAK2IRDGIBOB3YITGGTAVCNFSM6AAAAAASYO5MP2VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTQNBYGY4DAOJQHE . You are receiving this because you commented.Message ID: @.***>

github-actions[bot] commented 4 months ago

This issue has been marked 'stale' due to lack of recent activity. If there is no further activity, the issue will be closed in another 30 days. Thank you for your contribution!

Please read this blog post to see the reasons why I mark issues as stale.

liammcdermott commented 4 months ago

I don't think this is stale

misilot commented 4 months ago

Yeah it’s not stale

github-actions[bot] commented 1 week ago

This issue has been marked 'stale' due to lack of recent activity. If there is no further activity, the issue will be closed in another 30 days. Thank you for your contribution!

Please read this blog post to see the reasons why I mark issues as stale.

misilot commented 1 week ago

Not staleOn Aug 24, 2024, at 3:58 PM, github-actions[bot] @.***> wrote: This issue has been marked 'stale' due to lack of recent activity. If there is no further activity, the issue will be closed in another 30 days. Thank you for your contribution! Please read this blog post to see the reasons why I mark issues as stale.

—Reply to this email directly, view it on GitHub, or unsubscribe.You are receiving this because you commented.Message ID: @.***>