Debian 9 : movinv data to /home

Hello,
I’m trying to configure a Percona Xtradb cluster with a special configuration for the datadir. I have to put it into a /home/data folder for mounting and backup purpose. The problem is that mysql won’t start on Debian 9 when the datadir stays in /home. With MySQL I had to add a override to the systemd unit (protecthome=false) to allow the datadir in /home but as percona works with an init script and not a systemd unit I’m stuck.

Does someone have a solution to this problem ? This is linked to Debian 9 but I don’t find any workaround on Internet.

Thanks

Hi pitouyak,

It can be a folder permission problem as I cannot reproduce the issue:

mysql> select @@datadir;
+---------------------+
| @@datadir |
+---------------------+
| /home/vagrant/data/ |
+---------------------+
1 row in set (0.00 sec)

mysql> \!lsb_release -a
sh: 1: -a: not found
mysql> \! lsb_release -a
No LSB modules are available.
Distributor ID: Debian
Description: Debian GNU/Linux 9.6 (stretch)
Release: 9.6
Codename: stretch
root@pxc-2:/home/vagrant# ls -l
total 4
drwxr-xr-x 5 mysql mysql 4096 Dec 13 07:07 data

Can you share mysqld error log?

Hi jrivera,

Thanks for your quick answer.

I’ve checked the folder permissions and they seems fine :


root@gbl-stretch-test01:/home/data# ls -al
total 32
drwxr-xr-x 5 root root 4096 Dec 12 15:34 .
drwxr-xr-x 15 root root 4096 Dec 12 15:30 ..
drwxr-xr-x 2 root root 4096 Dec 12 15:30 backup_mysql
drwx------ 2 root root 16384 Nov 19 12:07 lost+found
drwxr-xr-x 2 mysql mysql 4096 Dec 13 08:25 mysql

Here the logs when trying to start mysql :


2018-12-13T07:25:14.713151Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-12-13T07:25:14.713889Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.23-23-57-log) starting as process 2403 ...
2018-12-13T07:25:14.715056Z 0 [Warning] No argument was provided to --log-bin, and --log-bin-index was not used; so replication may break when this MySQL server acts as a master and has his hostname changed!! Please use '--log-bin=gbl-stretch-test01-bin' to avoid this problem.
2018-12-13T07:25:14.715165Z 0 [Note] WSREP: Setting wsrep_ready to false
2018-12-13T07:25:14.715174Z 0 [Note] WSREP: No pre-stored wsrep-start position found. Skipping position initialization.
2018-12-13T07:25:14.715177Z 0 [Note] WSREP: wsrep_load(): loading provider library '/usr/lib/libgalera_smm.so'
2018-12-13T07:25:14.717111Z 0 [Note] WSREP: wsrep_load(): Galera 3.31(rf216443) by Codership Oy <info&#64;codership.com> loaded successfully.
2018-12-13T07:25:14.717144Z 0 [Note] WSREP: CRC-32C: using hardware acceleration.
2018-12-13T07:25:14.717325Z 0 [Note] WSREP: Found saved state: 18c47387-fe1b-11e8-8fc4-b2bb134380aa:0, safe_to_bootstrap: 1
2018-12-13T07:25:14.718844Z 0 [Note] WSREP: Passing config to GCS: base_dir = /home/data/mysql/; base_host = 192.168.200.10; base_port = 4567; cert.log_conflicts = no; debug = no; evs.auto_evict = 0; evs.delay_margin = PT1S; evs.delayed_keep_period = PT30S; evs.inactive_check_period = PT0.5S; evs.inactive_timeout = PT15S; evs.join_retrans_period = PT1S; evs.max_install_timeouts = 3; evs.send_window = 10; evs.stats_report_period = PT1M; evs.suspect_timeout = PT5S; evs.user_send_window = 4; evs.view_forget_timeout = PT24H; gcache.dir = /home/data/mysql/; gcache.freeze_purge_at_seqno = -1; gcache.keep_pages_count = 0; gcache.keep_pages_size = 0; gcache.mem_size = 0; gcache.name = /home/data/mysql//galera.cache; gcache.page_size = 128M; gcache.recover = no; gcache.size = 128M; gcomm.thread_prio = ; gcs.fc_debug = 0; gcs.fc_factor = 1; gcs.fc_limit = 100; gcs.fc_master_slave = no; gcs.max_packet_size = 64500; gcs.max_throttle = 0.25; gcs.recv_q_hard_limit = 9223372036854775807; gcs.recv_q_soft_limit = 0.25; gcs.sync_donor = no; gmcast.segment = 0; gmcast.version = 0; pc.announce_timeout = PT3S; pc.checksum = false; pc.ignore_quorum = false; pc.ignore_sb = false; pc.npvo = false; pc.recovery = 1; pc.version = 0; pc.wait_prim = true; pc.wait_prim_timeout = PT30S; pc.weight = 1; protonet.backend = asio; protonet.version = 0; repl.causal_read_timeout = PT30S; repl.commit_order = 3; repl.key_format = FLAT8; repl.max_ws_size = 2147483647; repl.proto_max = 9; socket.checksum = 2; socket.recv_buf_size = 212992;
2018-12-13T07:25:14.728295Z 0 [Note] WSREP: Assign initial position for certification: 0, protocol version: -1
2018-12-13T07:25:14.728320Z 0 [Note] WSREP: Preparing to initiate SST/IST
2018-12-13T07:25:14.728323Z 0 [Note] WSREP: Starting replication
2018-12-13T07:25:14.728328Z 0 [Note] WSREP: Setting initial position to 18c47387-fe1b-11e8-8fc4-b2bb134380aa:0
2018-12-13T07:25:14.728511Z 0 [Note] WSREP: Using CRC-32C for message checksums.
2018-12-13T07:25:14.728569Z 0 [Note] WSREP: gcomm thread scheduling priority set to other:0
2018-12-13T07:25:14.728617Z 0 [Warning] WSREP: Fail to access the file (/home/data/mysql//gvwstate.dat) error (No such file or directory). It is possible if node is booting for first time or re-booting after a graceful shutdown
2018-12-13T07:25:14.728622Z 0 [Note] WSREP: Restoring primary-component from disk failed. Either node is booting for first time or re-booting after a graceful shutdown
2018-12-13T07:25:14.728734Z 0 [Note] WSREP: GMCast version 0
2018-12-13T07:25:14.728832Z 0 [Note] WSREP: (3bdfd737, 'tcp://0.0.0.0:4567') listening at tcp://0.0.0.0:4567
2018-12-13T07:25:14.728837Z 0 [Note] WSREP: (3bdfd737, 'tcp://0.0.0.0:4567') multicast: , ttl: 1
2018-12-13T07:25:14.729002Z 0 [Note] WSREP: EVS version 0
2018-12-13T07:25:14.729047Z 0 [Note] WSREP: gcomm: connecting to group 'testtest', peer '192.168.200.10:,192.168.200.11:'
2018-12-13T07:25:14.729455Z 0 [Note] WSREP: (3bdfd737, 'tcp://0.0.0.0:4567') connection established to 3bdfd737 tcp://192.168.200.10:4567
2018-12-13T07:25:14.729465Z 0 [Warning] WSREP: (3bdfd737, 'tcp://0.0.0.0:4567') address 'tcp://192.168.200.10:4567' points to own listening address, blacklisting
2018-12-13T07:25:17.733354Z 0 [Note] WSREP: (3bdfd737, 'tcp://0.0.0.0:4567') connection to peer 00000000 with addr tcp://192.168.200.11:4567 timed out, no messages seen in PT3S (gmcast.peer_timeout)
2018-12-13T07:25:17.733407Z 0 [Note] WSREP: announce period timed out (pc.announce_timeout)
2018-12-13T07:25:17.733472Z 0 [Warning] WSREP: no nodes coming from prim view, prim not possible
2018-12-13T07:25:17.733485Z 0 [Note] WSREP: Current view of cluster as seen by this node
view (view_id(NON_PRIM,3bdfd737,1)
memb {
3bdfd737,0
}
joined {
}
left {
}
partitioned {
}
)
2018-12-13T07:25:18.233547Z 0 [Warning] WSREP: last inactive check more than PT1.5S (3*evs.inactive_check_period) ago (PT3.50454S), skipping check
2018-12-13T07:25:21.736745Z 0 [Note] WSREP: (3bdfd737, 'tcp://0.0.0.0:4567') connection to peer 00000000 with addr tcp://192.168.200.11:4567 timed out, no messages seen in PT3S (gmcast.peer_timeout)
2018-12-13T07:25:25.740548Z 0 [Note] WSREP: (3bdfd737, 'tcp://0.0.0.0:4567') connection to peer 00000000 with addr tcp://192.168.200.11:4567 timed out, no messages seen in PT3S (gmcast.peer_timeout)
2018-12-13T07:25:29.744954Z 0 [Note] WSREP: (3bdfd737, 'tcp://0.0.0.0:4567') connection to peer 00000000 with addr tcp://192.168.200.11:4567 timed out, no messages seen in PT3S (gmcast.peer_timeout)
2018-12-13T07:25:33.748149Z 0 [Note] WSREP: (3bdfd737, 'tcp://0.0.0.0:4567') connection to peer 00000000 with addr tcp://192.168.200.11:4567 timed out, no messages seen in PT3S (gmcast.peer_timeout)
2018-12-13T07:25:37.752366Z 0 [Note] WSREP: (3bdfd737, 'tcp://0.0.0.0:4567') connection to peer 00000000 with addr tcp://192.168.200.11:4567 timed out, no messages seen in PT3S (gmcast.peer_timeout)
2018-12-13T07:25:41.755675Z 0 [Note] WSREP: (3bdfd737, 'tcp://0.0.0.0:4567') connection to peer 00000000 with addr tcp://192.168.200.11:4567 timed out, no messages seen in PT3S (gmcast.peer_timeout)
2018-12-13T07:25:45.762025Z 0 [Note] WSREP: (3bdfd737, 'tcp://0.0.0.0:4567') connection to peer 00000000 with addr tcp://192.168.200.11:4567 timed out, no messages seen in PT3S (gmcast.peer_timeout)
2018-12-13T07:25:47.763976Z 0 [Note] WSREP: Current view of cluster as seen by this node
view ((empty))
2018-12-13T07:25:47.764112Z 0 [ERROR] WSREP: failed to open gcomm backend connection: 110: failed to reach primary view (pc.wait_prim_timeout): 110 (Connection timed out)
at gcomm/src/pc.cpp:connect():159
2018-12-13T07:25:47.764125Z 0 [ERROR] WSREP: gcs/src/gcs_core.cpp:gcs_core_open():209: Failed to open backend connection: -110 (Connection timed out)
2018-12-13T07:25:47.764304Z 0 [ERROR] WSREP: gcs/src/gcs.cpp:gcs_open():1514: Failed to open channel 'testtest' at 'gcomm://192.168.200.10,192.168.200.11': -110 (Connection timed out)
2018-12-13T07:25:47.764318Z 0 [ERROR] WSREP: gcs connect failed: Connection timed out
2018-12-13T07:25:47.764324Z 0 [ERROR] WSREP: Provider/Node (gcomm://192.168.200.10,192.168.200.11) failed to establish connection with cluster (reason: 7)
2018-12-13T07:25:47.764327Z 0 [ERROR] Aborting

2018-12-13T07:25:47.764331Z 0 [Note] Giving 0 client threads a chance to die gracefully
2018-12-13T07:25:47.764335Z 0 [Note] WSREP: Waiting for active wsrep applier to exit
2018-12-13T07:25:47.764338Z 0 [Note] WSREP: Service disconnected.
2018-12-13T07:25:47.764340Z 0 [Note] WSREP: Waiting to close threads......
2018-12-13T07:25:52.764530Z 0 [Note] WSREP: Some threads may fail to exit.
2018-12-13T07:25:52.764589Z 0 [Note] Binlog end
2018-12-13T07:25:52.764721Z 0 [Note] /usr/sbin/mysqld: Shutdown complete

I just make it working by starting with datadir in /var/lib/mysql then stopping, moving files to /home/date/mysql and starting again.
I will run more tests today

More info. I have run more tests :

  1. Fresh install of Percona Xtradb Cluster 5.7
  2. Stopping mysql
  3. Creating /home/data/mysql folder and setting the owner to mysql
  4. Editing /etc/mysql/percona-xtradb-cluster.conf.d/mysqld.cnf to change the datadir from /var/lib/mysql to /home/data/mysql
  5. Starting mysql
    → mysql won’t start

Then I copy all files from /var/lib/mysql to /home/data/mysql and start mysql again → it works

It seems that mysql won’t initialize correctly when I changed the datadir to /home

I will change my ansible script to make the copy from old datadir to new datadir as workaround. Perhaps someone will have a solution.

pitouyak, you have to initialize the data directory before you start the mysqld instance. After step 4 above run this

mysqld --initialize --user=mysql --datadir=/home/data/mysql

A temporary root password will be created and you will need to use it to login to the server after starting it.

jrivera, it works with the --initialize-insecure in my ansible script.

Thanks a lot for your help.