Jump to content
  • 0
Argus

Moving data directory for mariadb

Question

so i was following the very helpful tutorial https://linuxhint.com/change_mysql_data_directory_ubuntu/ and when i got to the near end where i issue the systemctl start mariadb i get a failure.

● mariadb.service - MariaDB 10.1.44 database server
   Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
   Active: failed (Result: exit-code) since Sat 2020-07-11 09:00:54 EDT; 1min 54s ago
     Docs: man:mysqld(8)
           https://mariadb.com/kb/en/library/systemd/
  Process: 14717 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION (code=exited, status=1/FAILURE)
  Process: 14636 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ]   && systemctl set-envi
  Process: 14634 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
  Process: 14632 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)
 Main PID: 14717 (code=exited, status=1/FAILURE)
   Status: "InnoDB: Error: Table mysql/innodb_table_stats in the InnoDB data dictionary has tablespace id 1, but tablespace with that id or name does not ex

Jul 11 09:00:52 BrooksFS systemd[1]: Starting MariaDB 10.1.44 database server...
Jul 11 09:00:53 BrooksFS mysqld[14717]: 2020-07-11  9:00:53 140410553461888 [Note] /usr/sbin/mysqld (mysqld 10.1.44-MariaDB-0ubuntu0.18.04.1) starting as pr
Jul 11 09:00:54 BrooksFS systemd[1]: mariadb.service: Main process exited, code=exited, status=1/FAILURE
Jul 11 09:00:54 BrooksFS systemd[1]: mariadb.service: Failed with result 'exit-code'.
Jul 11 09:00:54 BrooksFS systemd[1]: Failed to start MariaDB 10.1.44 database server.
 

/var/log/mysql

2020-07-11  5:48:48 140037265742976 [Note] InnoDB: innodb_empty_free_list_algorithm has been changed to legacy because of small buffer pool size. In order to use backoff, increase buffer pool at least up to 20MB.

2020-07-11  5:48:48 140037265742976 [Note] InnoDB: Using mutexes to ref count buffer pool pages
2020-07-11  5:48:48 140037265742976 [Note] InnoDB: The InnoDB memory heap is disabled
2020-07-11  5:48:48 140037265742976 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-07-11  5:48:48 140037265742976 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2020-07-11  5:48:48 140037265742976 [Note] InnoDB: Compressed tables use zlib 1.2.11
2020-07-11  5:48:48 140037265742976 [Note] InnoDB: Using Linux native AIO
2020-07-11  5:48:48 140037265742976 [Note] InnoDB: Using SSE crc32 instructions
2020-07-11  5:48:48 140037265742976 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2020-07-11  5:48:48 140037265742976 [Note] InnoDB: Completed initialization of buffer pool
2020-07-11  5:48:48 140037265742976 [Note] InnoDB: Highest supported file format is Barracuda.
2020-07-11  5:48:48 140037265742976 [Note] InnoDB: The log sequence number 35571493758 in ibdata file do not match the log sequence number 35571493868 in the ib_logfiles!
2020-07-11  5:48:48 140037265742976 [Note] InnoDB: Restoring possible half-written data pages from the doublewrite buffer...
2020-07-11  5:48:48 140037265742976 [ERROR] InnoDB: Table mysql/gtid_slave_pos in the InnoDB data dictionary has tablespace id 3, but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
2020-07-11  5:48:48 140037265742976 [ERROR] InnoDB: Table mysql/innodb_index_stats in the InnoDB data dictionary has tablespace id 2, but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
2020-07-11  5:48:48 140037265742976 [ERROR] InnoDB: Table mysql/innodb_table_stats in the InnoDB data dictionary has tablespace id 1, but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
2020-07-11  5:48:48 140037265742976 [Note] InnoDB: 128 rollback segment(s) are active.
2020-07-11  5:48:48 140037265742976 [Note] InnoDB: Waiting for purge to start
2020-07-11  5:48:48 140037265742976 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.46-86.2 started; log sequence number 35571493868
2020-07-11  5:48:48 140037265742976 [Note] Plugin 'FEEDBACK' is disabled.
2020-07-11  5:48:48 140036599183104 [Note] InnoDB: Dumping buffer pool(s) not yet started
2020-07-11  5:48:48 140037265742976 [ERROR] Could not open mysql.plugin table. Some plugins may be not loaded
2020-07-11  5:48:48 140037265742976 [Note] Recovering after a crash using tc.log
2020-07-11  5:48:48 140037265742976 [Note] Starting crash recovery...
2020-07-11  5:48:48 140037265742976 [Note] Crash recovery finished.
2020-07-11  5:48:48 140037265742976 [ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist
2020-07-11  5:48:48 140037265742976 [Note] Server socket created on IP: '127.0.0.1'.
2020-07-11  5:48:48 140037265742976 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist
2020-07-11  7:03:09 140120431885440 [Note] InnoDB: innodb_empty_free_list_algorithm has been changed to legacy because of small buffer pool size. In order to use backoff, increase buffer pool at least up to 20MB.

2020-07-11  7:03:09 140120431885440 [Note] InnoDB: Using mutexes to ref count buffer pool pages
2020-07-11  7:03:09 140120431885440 [Note] InnoDB: The InnoDB memory heap is disabled
2020-07-11  7:03:09 140120431885440 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-07-11  7:03:09 140120431885440 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2020-07-11  7:03:09 140120431885440 [Note] InnoDB: Compressed tables use zlib 1.2.11
2020-07-11  7:03:09 140120431885440 [Note] InnoDB: Using Linux native AIO
2020-07-11  7:03:09 140120431885440 [Note] InnoDB: Using SSE crc32 instructions
2020-07-11  7:03:09 140120431885440 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2020-07-11  7:03:09 140120431885440 [Note] InnoDB: Completed initialization of buffer pool
2020-07-11  7:03:09 140120431885440 [Note] InnoDB: Highest supported file format is Barracuda.
2020-07-11  7:03:09 140120431885440 [Note] InnoDB: The log sequence number 35571493758 in ibdata file do not match the log sequence number 35571493878 in the ib_logfiles!
2020-07-11  7:03:09 140120431885440 [Note] InnoDB: Restoring possible half-written data pages from the doublewrite buffer...
2020-07-11  7:03:09 140120431885440 [ERROR] InnoDB: Table mysql/gtid_slave_pos in the InnoDB data dictionary has tablespace id 3, but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
2020-07-11  7:03:09 140120431885440 [ERROR] InnoDB: Table mysql/innodb_index_stats in the InnoDB data dictionary has tablespace id 2, but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
2020-07-11  7:03:09 140120431885440 [ERROR] InnoDB: Table mysql/innodb_table_stats in the InnoDB data dictionary has tablespace id 1, but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
2020-07-11  7:03:09 140120431885440 [Note] InnoDB: 128 rollback segment(s) are active.
2020-07-11  7:03:09 140120431885440 [Note] InnoDB: Waiting for purge to start
2020-07-11  7:03:09 140120431885440 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.46-86.2 started; log sequence number 35571493878
2020-07-11  7:03:09 140120431885440 [Note] Plugin 'FEEDBACK' is disabled.
2020-07-11  7:03:09 140119763842816 [Note] InnoDB: Dumping buffer pool(s) not yet started
2020-07-11  7:03:09 140120431885440 [ERROR] Could not open mysql.plugin table. Some plugins may be not loaded
2020-07-11  7:03:09 140120431885440 [Note] Recovering after a crash using tc.log
2020-07-11  7:03:09 140120431885440 [Note] Starting crash recovery...
2020-07-11  7:03:09 140120431885440 [Note] Crash recovery finished.
2020-07-11  7:03:09 140120431885440 [ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist
2020-07-11  7:03:09 140120431885440 [Note] Server socket created on IP: '127.0.0.1'.
2020-07-11  7:03:09 140120431885440 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist
 

 

when i search  InnoDB: Table mysql/innodb_table_stats in the InnoDB data dictionary has tablespace id 1, but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.   i get information on commands to issue in mysql... problem is the mysql server is not running for me to issue commands so dont know what to do any help would be appreciated. im using ubuntu 18.04 that was upgraded from 16.04 and mysql was previously installed but wasnt used so i used apt remove to get rid of it and then installed mariadb. if there is any more information that is needed would be glad to provide.

Share this post


Link to post
Share on other sites

4 answers to this question

Recommended Posts

  • 0

This does not look good.

It seems like some sort of corruption in the files to me.

Since this is a fresh install can you delete it and start over?

What type of filesystem are you installing on?

Share this post


Link to post
Share on other sites
  • 0

so basically i went back and changed  datadir= /var/lib/mysql and my alias changes and  restarted app armor and (mv mysql.bak  mysql) the /var/lib/mysql. and restarted the  mysql server and it came back up with out a hitch... just like it never happened.... so there has to be something not being done correctly or it is something with the fact ubuntu was upgraded from 16.04 to 18.04 and previously haveing MySQL on there and apt remove mysql should have perhaps had an apt purge mysql as well before having MariaDB installed... not sure, but once i got the database server running again i did drop the database (not what i wanted to do but i do have a back up) and all was good for now untill i can get the data on another server, then restore the data

Share this post


Link to post
Share on other sites
  • 0

well i thought that is what the step in the tutorial was taking care of but i dont know exactly... frankly i dont know anything about app armor... i guess that is another thing to sort out in my studies... thank you for you time looking into this.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...