1 Topic by dittman

  • dittman
  • Member
  • Offline
  • Registered: 2015-08-15
  • Posts: 55

Topic: Dropping a database and migrating to a new server

==== REQUIRED BASIC INFO OF YOUR IREDMAIL SERVER ====
- iRedMail version (check /etc/iredmail-release): 0.9.5-1
- Deployed with iRedMail Easy or the downloadable installer? downloadable
- Linux/BSD distribution name and version: CentOS 7
- Store mail accounts in which backend (LDAP/MySQL/PGSQL): MySQL
- Web server (Apache or Nginx): Apache
- Manage mail accounts with iRedAdmin-Pro? No
- [IMPORTANT] Related original log or error message is required if you're experiencing an issue.
====

I'm long behind on updating iRedMail and I need to switch from CentOS 7 anyway.  I've built a new Rocky Linux 8 server and installed iRedMail and verified it's working.  The next step is to migrate the data and to do that I need to bring my existing server database up to the current iRedMail version.  As I understand it I don't need to update any of the software, just make the database changes, but I thought I'd do the full upgrade process anyway so I can take longer to update without having to disable email.

I reviewed my notes from previous updates and noticed that at one of them I was supposed to remove cluebringer.  I wasn't sure how to do that at the time and forgot to got back and revisit it.  How do I remove cluebringer?

Also as I'm so far back are there any issues I should watch out for?  My server is a VM so my plan was to snapshot and test between each update.

----

Spider Email Archiver: On-Premises, lightweight email archiving software developed by iRedMail team. Supports Amazon S3 compatible storage and custom branding.

2 Reply by Cthulhu

  • Cthulhu
  • Member
  • Offline
  • Registered: 2019-06-04
  • Posts: 907

Re: Dropping a database and migrating to a new server

gluebringer was removed long long time ago and isn't used anymore at all, and in therory the only thing you need to import is the vmail database go get it to work (and copying the /var/mail/vmail1" since it includes all the mailboxes)

3 Reply by dittman

  • dittman
  • Member
  • Offline
  • Registered: 2015-08-15
  • Posts: 55

Re: Dropping a database and migrating to a new server

As I understand it I have to do all the required database changes between 0.9.5-1 and 1.5.2.  I never removed the cluebringer database as I wasn't sure how to do it and put it aside and forgot to ask.

4 Reply by dittman

  • dittman
  • Member
  • Offline
  • Registered: 2015-08-15
  • Posts: 55

Re: Dropping a database and migrating to a new server

I found it was easy, just drop database cluebringer; and it was gone.  I took a snapshot first just in case.

5 Reply by dittman

  • dittman
  • Member
  • Offline
  • Registered: 2015-08-15
  • Posts: 55

Re: Dropping a database and migrating to a new server

Cthulhu wrote:

gluebringer was removed long long time ago and isn't used anymore at all, and in therory the only thing you need to import is the vmail database go get it to work (and copying the /var/mail/vmail1" since it includes all the mailboxes)

I looked at the documentation for migrating servers and it says just export and import the vmail database but does list the commands.  I'm not a database person.  What are the commands to do that for MariaDB?

6 Reply by dittman

  • dittman
  • Member
  • Offline
  • Registered: 2015-08-15
  • Posts: 55

Re: Dropping a database and migrating to a new server

For testing the process I've made a copy of my current server and gone through the MySQL/MariaDB changes in all the upgrade steps.  I've dumped vmail database and am syncing /var/vmail/vmail1 to the new test server.  In addition to the vmail database I've also dumped the iredapd, iredmail, amavisd, and roundcubemail databases since those were called out as well.

7 Reply by dittman (edited by dittman 2022-04-08 08:35:46)

  • dittman
  • Member
  • Offline
  • Registered: 2015-08-15
  • Posts: 55

Re: Dropping a database and migrating to a new server

It appears to have worked.  I just need to confirm if I need to export and import the other databases or just vmail.

8 Reply by dittman (edited by dittman 2022-04-09 15:32:43)

  • dittman
  • Member
  • Offline
  • Registered: 2015-08-15
  • Posts: 55

Re: Dropping a database and migrating to a new server

The first attempt appeared to work so I redid the process to make sure I wasn't missing anything.  I built a new server using Rocky Linux 8, made another backup of my original server, then went through the steps to apply all the database changes listed.  I then exported the databases and used rsync to copy over /var/vmail/vmail1, then on the new server I imported the databases.  I checked that things started up and I could connect and send email, then I started digging into the logs to make sure there weren't any issues.  I found several.  Because of this I've restored the backup VM.  I did keep the new server as well as the old server that I applied all the databases changes to.

-----

/var/log/messages has several mysqld errors for aborted connections for vmail and iredadmin:

Apr  9 01:25:24 clarke mysqld[1178]: 2022-04-09  1:25:24 16 [Warning] Aborted connection 16 to db: 'vmail' user: 'vmailadmin' host: 'localhost' (Got an error reading communication packets)

Apr  9 01:27:23 clarke mysqld[1178]: 2022-04-09  1:27:23 29 [Warning] Aborted connection 29 to db: 'iredadmin' user: 'iredadmin' host: 'localhost' (Got an error reading communication packets)

-----

/var/log/dovecot/dovecot.log has these errors with the port after 127.0.0.1: changing:

Apr  9 01:17:02 clarke dovecot[1582]: stats: Error: conn stats-reader (127.0.0.1:46156): didn't reply with a valid VERSION line: EXPORT#011global

-----

/var/log/iredapd/iredapd.log shows a missing table.  I applied all the listed MySQL/MariaDB changes in each step from 0.9.5-1 to 1.5.2.  I found a previous report of this that said the table was fixed when upgrading iRedAPD but I don't see that table listed:

Apr  9 00:43:37 clarke journal[1725]: iredapd <!> Error while logging smtp action: ProgrammingError('(pymysql.err.ProgrammingError) (1146, "Table \'iredapd.smtp_sessions\' doesn\'t exist")',)

-----

Also in /var/log/iredpd/iredapd.log I see errors applying the plugins throttle, greylisting, and wblist_rdns:

Apr  9 00:43:37 clarke journal[1725]: iredapd <!> Error while applying plugin 'throttle': ['Traceback (most recent call last):\n', '  File "/usr/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", line 1244, in _execute_context\n    cursor, statement, parameters, context\n', '  File "/usr/lib64/python3.6/site-packages/sqlalchemy/engine/default.py", line 552, in do_execute\n    cursor.execute(statement, parameters)\n', '  File "/usr/lib/python3.6/site-packages/pymysql/cursors.py", line 163, in execute\n    result = self._query(query)\n', '  File "/usr/lib/python3.6/site-packages/pymysql/cursors.py", line 321, in _query\n    conn.query(q)\n', '  File "/usr/lib/python3.6/site-packages/pymysql/connections.py", line 505, in query\n    self._affected_rows = self._read_query_result(unbuffered=unbuffered)\n', '  File "/usr/lib/python3.6/site-packages/pymysql/connections.py", line 724, in _read_query_result\n    result.read()\n', '  File "/usr/lib/python3.6/site-packages/pymysql/connections.py", line 1069, in read\n    first_packet = self.connection._read_packet()\n', '  File "/usr/lib/python3.6/site-packages/pymysql/connections.py", line 676, in _read_packet\n    packet.raise_for_error()\n', '  File "/usr/lib/python3.6/site-packages/pymysql/protocol.py", line 223, in raise_for_error\n    err.raise_mysql_exception(self._data)\n', '  File "/usr/lib/python3.6/site-packages/pymysql/err.py", line 107, in raise_mysql_exception\n    raise errorclass(errno, errval)\n', 'pymysql.err.OperationalError: (1054, "Unknown column \'max_rcpts\' in \'field list\'")\n', '\nThe above exception was the direct cause of the following exception:\n\n', 'Traceback (most recent call last):\n', '  File "/opt/iRedAPD-5.0.4/libs/utils.py", line 90, in apply_plugin\n', '  File "/opt/iredapd/plugins/throttle.py", line 712, in restriction\n', '  File "/opt/iredapd/plugins/throttle.py", line 281, in apply_throttle\n', '  File "/usr/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", line 982, in execute\n    return self._execute_text(object_, multiparams, params)\n', '  File "/usr/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", line 1155, in _execute_text\n    parameters,\n', '  File "/usr/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", line 1248, in _execute_context\n    e, statement, parameters, cursor, context\n', '  File "/usr/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", line 1466, in _handle_dbapi_exception\n    util.raise_from_cause(sqlalchemy_exception, exc_info)\n', '  File "/usr/lib64/python3.6/site-packages/sqlalchemy/util/compat.py", line 383, in raise_from_cause\n    reraise(type(exception), exception, tb=exc_tb, cause=cause)\n', '  File "/usr/lib64/python3.6/site-packages/sqlalchemy/util/compat.py", line 128, in reraise\n    raise value.with_traceback(tb)\n', '  File "/usr/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", line 1244, in _execute_context\n    cursor, statement, parameters, context\n', '  File "/usr/lib64/python3.6/site-packages/sqlalchemy/engine/default.py", line 552, in do_execute\n    cursor.execute(statement, parameters)\n', '  File "/usr/lib/python3.6/site-packages/pymysql/cursors.py", line 163, in execute\n    result = self._query(query)\n', '  File "/usr/lib/python3.6/site-packages/pymysql/cursors.py", line 321, in _query\n    conn.query(q)\n', '  File "/usr/lib/python3.6/site-packages/pymysql/connections.py", line 505, in query\n    self._affected_rows = self._read_query_result(unbuffered=unbuffered)\n', '  File "/usr/lib/python3.6/site-packages/pymysql/connections.py", line 724, in _read_query_result\n    result.read()\n', '  File "/usr/lib/python3.6/site-packages/pymysql/connections.py", line 1069, in read\n    first_packet = self.connection._read_packet()\n', '  File "/usr/lib/python3.6/site-packages/pymysql/connections.py", line 676, in _read_packet\n    packet.raise_for_error()\n', '  File "/usr/lib/python3.6/site-packages/pymysql/protocol.py", line 223, in raise_for_error\n    err.raise_mysql_exception(self._data)\n', '  File "/usr/lib/python3.6/site-packages/pymysql/err.py", line 107, in raise_mysql_exception\n    raise errorclass(errno, errval)\n', 'sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (1054, "Unknown column \'max_rcpts\' in \'field list\'")\n[SQL: \n        SELECT id, account, priority, period, max_msgs, max_quota, max_rcpts, msg_size\n          FROM throttle\n         WHERE kind=\'outbound\' AND account IN (\'2001:470:b9a8:10::21\', \'@ip\', \'dittman@dittman.net\', \'@dittman.net\', \'@.\', \'@.dittman.net\', \'@.net\')\n         ORDER BY priority DESC\n         ]\n(Background on this error at: ]http://sqlalche.me/e/e3q8)n']

Apr  9 00:45:46 clarke journal[1725]: iredapd <!> Error while applying plugin 'greylisting': ['Traceback (most recent call last):\n', '  File "/usr/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", line 1244, in _execute_context\n    cursor, statement, parameters, context\n', '  File "/usr/lib64/python3.6/site-packages/sqlalchemy/engine/default.py", line 552, in do_execute\n    cursor.execute(statement, parameters)\n', '  File "/usr/lib/python3.6/site-packages/pymysql/cursors.py", line 163, in execute\n    result = self._query(query)\n', '  File "/usr/lib/python3.6/site-packages/pymysql/cursors.py", line 321, in _query\n    conn.query(q)\n', '  File "/usr/lib/python3.6/site-packages/pymysql/connections.py", line 505, in query\n    self._affected_rows = self._read_query_result(unbuffered=unbuffered)\n', '  File "/usr/lib/python3.6/site-packages/pymysql/connections.py", line 724, in _read_query_result\n    result.read()\n', '  File "/usr/lib/python3.6/site-packages/pymysql/connections.py", line 1069, in read\n    first_packet = self.connection._read_packet()\n', '  File "/usr/lib/python3.6/site-packages/pymysql/connections.py", line 676, in _read_packet\n    packet.raise_for_error()\n', '  File "/usr/lib/python3.6/site-packages/pymysql/protocol.py", line 223, in raise_for_error\n    err.raise_mysql_exception(self._data)\n', '  File "/usr/lib/python3.6/site-packages/pymysql/err.py", line 107, in raise_mysql_exception\n    raise errorclass(errno, errval)\n', 'pymysql.err.ProgrammingError: (1146, "Table \'iredapd.greylisting_whitelist_domain_spf\' doesn\'t exist")\n', '\nThe above exception was the direct cause of the following exception:\n\n', 'Traceback (most recent call last):\n', '  File "/opt/iRedAPD-5.0.4/libs/utils.py", line 90, in apply_plugin\n', '  File "/opt/iredapd/plugins/greylisting.py", line 368, in restriction\n', '  File "/opt/iredapd/plugins/greylisting.py", line 57, in _is_whitelisted\n', '  File "/usr/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", line 982, in execute\n    return self._execute_text(object_, multiparams, params)\n', '  File "/usr/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", line 1155, in _execute_text\n    parameters,\n', '  File "/usr/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", line 1248, in _execute_context\n    e, statement, parameters, cursor, context\n', '  File "/usr/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", line 1466, in _handle_dbapi_exception\n    util.raise_from_cause(sqlalchemy_exception, exc_info)\n', '  File "/usr/lib64/python3.6/site-packages/sqlalchemy/util/compat.py", line 383, in raise_from_cause\n    reraise(type(exception), exception, tb=exc_tb, cause=cause)\n', '  File "/usr/lib64/python3.6/site-packages/sqlalchemy/util/compat.py", line 128, in reraise\n    raise value.with_traceback(tb)\n', '  File "/usr/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", line 1244, in _execute_context\n    cursor, statement, parameters, context\n', '  File "/usr/lib64/python3.6/site-packages/sqlalchemy/engine/default.py", line 552, in do_execute\n    cursor.execute(statement, parameters)\n', '  File "/usr/lib/python3.6/site-packages/pymysql/cursors.py", line 163, in execute\n    result = self._query(query)\n', '  File "/usr/lib/python3.6/site-packages/pymysql/cursors.py", line 321, in _query\n    conn.query(q)\n', '  File "/usr/lib/python3.6/site-packages/pymysql/connections.py", line 505, in query\n    self._affected_rows = self._read_query_result(unbuffered=unbuffered)\n', '  File "/usr/lib/python3.6/site-packages/pymysql/connections.py", line 724, in _read_query_result\n    result.read()\n', '  File "/usr/lib/python3.6/site-packages/pymysql/connections.py", line 1069, in read\n    first_packet = self.connection._read_packet()\n', '  File "/usr/lib/python3.6/site-packages/pymysql/connections.py", line 676, in _read_packet\n    packet.raise_for_error()\n', '  File "/usr/lib/python3.6/site-packages/pymysql/protocol.py", line 223, in raise_for_error\n    err.raise_mysql_exception(self._data)\n', '  File "/usr/lib/python3.6/site-packages/pymysql/err.py", line 107, in raise_mysql_exception\n    raise errorclass(errno, errval)\n', 'sqlalchemy.exc.ProgrammingError: (pymysql.err.ProgrammingError) (1146, "Table \'iredapd.greylisting_whitelist_domain_spf\' doesn\'t exist")\n[SQL: SELECT LOWER(sender)\n                   FROM greylisting_whitelist_domain_spf\n                  WHERE account IN (\'root@dittman.net\', \'@dittman.net\', \'@.\', \'@.dittman.net\', \'@.net\')]\n(Background on this error at: ]http://sqlalche.me/e/f405)n']

Apr  9 00:45:46 clarke journal[1725]: iredapd <!> Error while applying plugin 'wblist_rdns': ['Traceback (most recent call last):\n', '  File "/usr/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", line 1244, in _execute_context\n    cursor, statement, parameters, context\n', '  File "/usr/lib64/python3.6/site-packages/sqlalchemy/engine/default.py", line 552, in do_execute\n    cursor.execute(statement, parameters)\n', '  File "/usr/lib/python3.6/site-packages/pymysql/cursors.py", line 163, in execute\n    result = self._query(query)\n', '  File "/usr/lib/python3.6/site-packages/pymysql/cursors.py", line 321, in _query\n    conn.query(q)\n', '  File "/usr/lib/python3.6/site-packages/pymysql/connections.py", line 505, in query\n    self._affected_rows = self._read_query_result(unbuffered=unbuffered)\n', '  File "/usr/lib/python3.6/site-packages/pymysql/connections.py", line 724, in _read_query_result\n    result.read()\n', '  File "/usr/lib/python3.6/site-packages/pymysql/connections.py", line 1069, in read\n    first_packet = self.connection._read_packet()\n', '  File "/usr/lib/python3.6/site-packages/pymysql/connections.py", line 676, in _read_packet\n    packet.raise_for_error()\n', '  File "/usr/lib/python3.6/site-packages/pymysql/protocol.py", line 223, in raise_for_error\n    err.raise_mysql_exception(self._data)\n', '  File "/usr/lib/python3.6/site-packages/pymysql/err.py", line 107, in raise_mysql_exception\n    raise errorclass(errno, errval)\n', 'pymysql.err.ProgrammingError: (1146, "Table \'iredapd.wblist_rdns\' doesn\'t exist")\n', '\nThe above exception was the direct cause of the following exception:\n\n', 'Traceback (most recent call last):\n', '  File "/opt/iRedAPD-5.0.4/libs/utils.py", line 90, in apply_plugin\n', '  File "/opt/iredapd/plugins/wblist_rdns.py", line 85, in restriction\n', '  File "/usr/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", line 982, in execute\n    return self._execute_text(object_, multiparams, params)\n', '  File "/usr/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", line 1155, in _execute_text\n    parameters,\n', '  File "/usr/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", line 1248, in _execute_context\n    e, statement, parameters, cursor, context\n', '  File "/usr/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", line 1466, in _handle_dbapi_exception\n    util.raise_from_cause(sqlalchemy_exception, exc_info)\n', '  File "/usr/lib64/python3.6/site-packages/sqlalchemy/util/compat.py", line 383, in raise_from_cause\n    reraise(type(exception), exception, tb=exc_tb, cause=cause)\n', '  File "/usr/lib64/python3.6/site-packages/sqlalchemy/util/compat.py", line 128, in reraise\n    raise value.with_traceback(tb)\n', '  File "/usr/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", line 1244, in _execute_context\n    cursor, statement, parameters, context\n', '  File "/usr/lib64/python3.6/site-packages/sqlalchemy/engine/default.py", line 552, in do_execute\n    cursor.execute(statement, parameters)\n', '  File "/usr/lib/python3.6/site-packages/pymysql/cursors.py", line 163, in execute\n    result = self._query(query)\n', '  File "/usr/lib/python3.6/site-packages/pymysql/cursors.py", line 321, in _query\n    conn.query(q)\n', '  File "/usr/lib/python3.6/site-packages/pymysql/connections.py", line 505, in query\n    self._affected_rows = self._read_query_result(unbuffered=unbuffered)\n', '  File "/usr/lib/python3.6/site-packages/pymysql/connections.py", line 724, in _read_query_result\n    result.read()\n', '  File "/usr/lib/python3.6/site-packages/pymysql/connections.py", line 1069, in read\n    first_packet = self.connection._read_packet()\n', '  File "/usr/lib/python3.6/site-packages/pymysql/connections.py", line 676, in _read_packet\n    packet.raise_for_error()\n', '  File "/usr/lib/python3.6/site-packages/pymysql/protocol.py", line 223, in raise_for_error\n    err.raise_mysql_exception(self._data)\n', '  File "/usr/lib/python3.6/site-packages/pymysql/err.py", line 107, in raise_mysql_exception\n    raise errorclass(errno, errval)\n', 'sqlalchemy.exc.ProgrammingError: (pymysql.err.ProgrammingError) (1146, "Table \'iredapd.wblist_rdns\' doesn\'t exist")\n[SQL: SELECT rdns\n               FROM wblist_rdns\n              WHERE rdns IN (\'pohl.int.dittman.net\', \'.pohl.int.dittman.net\', \'.int.dittman.net\', \'.dittman.net\', \'.net\') AND wb=\'W\'\n              LIMIT 1]\n(Background on this error at: ]http://sqlalche.me/e/f405)n']

9 Reply by dittman

  • dittman
  • Member
  • Offline
  • Registered: 2015-08-15
  • Posts: 55

Re: Dropping a database and migrating to a new server

I've been digging through the databases, and other than pulling the domains from iredapd.greylisting_whitelist_domains I think I might not need the other databases brought over.  It looks like the roundcubemail database will be repopulated as accounts are accessed from it and I don't think I'd miss the tracking information from the iredadmin database.

If this is true (can someone confirm?) all I really need is the vmail database and /var/vmail/vmail1 as Chulthu mentioned and ignore the Migration document notes to pull in the other databases.