Migrating from mysql community 5.7 to Percona mysql 5.7

MySQL & MariaDB Percona Server for MySQL 5.7
1

Greetings and Salutations!
I am having some difficulties, and was hoping someone from the community might be able to offer some insights or assistance. I will offer thanks in advance as it might get lost below. Thank you.

My current environment is a production mysql community 5.7 server with a production slave and a non-production slave. I am attempting to bring up a new server w/o impacting production. I have installed Percona mysql on the new server, I can start the mysql service just fine. I then scp the /var/lib/mysql folder from the non-production slave to the new server. Now when I try to start the mysql service it fails.

I have tried scp’ing the data before installing Percona mysql with the service failing to start. It appears to attempt to start but gets stuck with the service activating. This is closer than previous attempts as those failed outright. Below are some output from the system:

[cs@someserver ~]$ sudo systemctl start mysql
Job for mysqld.service failed because the control process exited with error code. See “systemctl status mysqld.service” and “journalctl -xe” for details.

[cs@someserver ~]$ sudo systemctl status mysql
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: activating (start) since Thu 2019-03-07 07:45:46 EST; 2s ago
Docs: man:mysqld(8)
[url]http://dev.mysql.com/doc/refman/en/using-systemd.html[/url]
Process: 64567 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 45646 (code=exited, status=0/SUCCESS); : 64593 (mysqld)
CGroup: /system.slice/mysqld.service
├─64593 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
└─64595 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

Mar 07 07:45:47 someserver.cornell.edu mysqld[64593]: 2019-03-07T12:45:47.185072Z 0 [Note…M
Mar 07 07:45:48 someserver.cornell.edu mysqld[64593]: 2019-03-07T12:45:48.525382Z 0 [Note…l
Mar 07 07:45:49 someserver.cornell.edu mysqld[64593]: 2019-03-07T12:45:49.002293Z 0 [Note…
Mar 07 07:45:49 someserver.cornell.edu mysqld[64593]: 2019-03-07T12:45:49.016799Z 0 [Note…e
Mar 07 07:45:49 someserver.cornell.edu mysqld[64593]: 2019-03-07T12:45:49.105372Z 0 [Note…
Mar 07 07:45:49 someserver.cornell.edu mysqld[64593]: 2019-03-07T12:45:49.493878Z 0 [Note…7
Mar 07 07:45:49 someserver.cornell.edu mysqld[64593]: 2019-03-07T12:45:49.517689Z 0 [Note…2
Mar 07 07:45:49 someserver.cornell.edu mysqld[64593]: 2019-03-07T12:45:49.518226Z 0 [Note…!
Mar 07 07:45:49 someserver.cornell.edu mysqld[64593]: 2019-03-07T12:45:49.518239Z 0 [Note…
Mar 07 07:45:49 someserver.cornell.edu mysqld[64593]: 2019-03-07T12:45:49.580948Z 0 [Note…s
Hint: Some lines were ellipsized, use -l to show in full.
[cs@someserver ~]$ sudo systemctl status mysql
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: activating (start) since Thu 2019-03-07 07:47:07 EST; 2s ago
Docs: man:mysqld(8)
[url]http://dev.mysql.com/doc/refman/en/using-systemd.html[/url]
Process: 891 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 45646 (code=exited, status=0/SUCCESS); : 916 (mysqld)
CGroup: /system.slice/mysqld.service
├─916 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
└─918 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

Mar 07 07:47:07 someserver.cornell.edu mysqld[916]: 2019-03-07T12:47:07.435402Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
Mar 07 07:47:07 someserver.cornell.edu mysqld[916]: 2019-03-07T12:47:07.435413Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.7
Mar 07 07:47:07 someserver.cornell.edu mysqld[916]: 2019-03-07T12:47:07.435421Z 0 [Note] InnoDB: Using Linux native AIO
Mar 07 07:47:07 someserver.cornell.edu mysqld[916]: 2019-03-07T12:47:07.436064Z 0 [Note] InnoDB: Number of pools: 1
Mar 07 07:47:07 someserver.cornell.edu mysqld[916]: 2019-03-07T12:47:07.436241Z 0 [Note] InnoDB: Using CPU crc32 instructions
Mar 07 07:47:07 someserver.cornell.edu mysqld[916]: 2019-03-07T12:47:07.438529Z 0 [Note] InnoDB: Initializing buffer pool, total size = 16G, instances = 16, chunk size = 128M
Mar 07 07:47:08 someserver.cornell.edu mysqld[916]: 2019-03-07T12:47:08.739132Z 0 [Note] InnoDB: Completed initialization of buffer pool
Mar 07 07:47:09 someserver.cornell.edu mysqld[916]: 2019-03-07T12:47:09.313487Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the …etpriority().
Mar 07 07:47:09 someserver.cornell.edu mysqld[916]: 2019-03-07T12:47:09.328607Z 0 [Note] InnoDB: Recovering partial pages from the parallel doublewrite buffer at /var/lib/mysql/xb_doublewrite
Mar 07 07:47:09 someserver.cornell.edu mysqld[916]: 2019-03-07T12:47:09.442696Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
Hint: Some lines were ellipsized, use -l to show in full.

[cs@someserver ~]$ sudo journalctl -xe
[sudo] password for cs:
Mar 07 07:55:12 someserver.cornell.edu systemd[1]: mysqld.service: control process exited, code=exited status=1
Mar 07 07:55:12 someserver.cornell.edu systemd[1]: Failed to start MySQL Server.
– Subject: Unit mysqld.service has failed
– Defined-By: systemd
– Support: [url]http://lists.freedesktop.org/mailman/listinfo/systemd-devel[/url]

– Unit mysqld.service has failed.

– The result is failed.
Mar 07 07:55:12 someserver.cornell.edu systemd[1]: Unit mysqld.service entered failed state.
Mar 07 07:55:12 someserver.cornell.edu systemd[1]: mysqld.service failed.
Mar 07 07:55:13 someserver.cornell.edu systemd[1]: mysqld.service holdoff time over, scheduling restart.
Mar 07 07:55:13 someserver.cornell.edu systemd[1]: Stopped MySQL Server.
– Subject: Unit mysqld.service has finished shutting down
– Defined-By: systemd
– Support: [url]http://lists.freedesktop.org/mailman/listinfo/systemd-devel[/url]

– Unit mysqld.service has finished shutting down.
Mar 07 07:55:13 someserver.cornell.edu systemd[1]: Starting MySQL Server…
– Subject: Unit mysqld.service has begun start-up
– Defined-By: systemd
– Support: [url]http://lists.freedesktop.org/mailman/listinfo/systemd-devel[/url]

– Unit mysqld.service has begun starting up.
Mar 07 07:55:13 someserver.cornell.edu mysqld[9902]: 2019-03-07T12:55:13.196088Z 0 [Warning] Changed limits: max_open_files: 5000 (requested 40411)
Mar 07 07:55:13 someserver.cornell.edu mysqld[9902]: 2019-03-07T12:55:13.196531Z 0 [Warning] Changed limits: table_open_cache: 2295 (requested 20000)
Mar 07 07:55:13 someserver.cornell.edu mysqld[9902]: 2019-03-07T12:55:13.422607Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see
Mar 07 07:55:13 someserver.cornell.edu mysqld[9902]: 2019-03-07T12:55:13.425434Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.25-28-log) starting as process 9904 …
Mar 07 07:55:13 someserver.cornell.edu mysqld[9902]: 2019-03-07T12:55:13.433810Z 0 [Note] InnoDB: PUNCH HOLE support available
Mar 07 07:55:13 someserver.cornell.edu mysqld[9902]: 2019-03-07T12:55:13.433864Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
Mar 07 07:55:13 someserver.cornell.edu mysqld[9902]: 2019-03-07T12:55:13.433889Z 0 [Note] InnoDB: Uses event mutexes
Mar 07 07:55:13 someserver.cornell.edu mysqld[9902]: 2019-03-07T12:55:13.433898Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
Mar 07 07:55:13 someserver.cornell.edu mysqld[9902]: 2019-03-07T12:55:13.433910Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.7
Mar 07 07:55:13 someserver.cornell.edu mysqld[9902]: 2019-03-07T12:55:13.433918Z 0 [Note] InnoDB: Using Linux native AIO
Mar 07 07:55:13 someserver.cornell.edu mysqld[9902]: 2019-03-07T12:55:13.434541Z 0 [Note] InnoDB: Number of pools: 1
Mar 07 07:55:13 someserver.cornell.edu mysqld[9902]: 2019-03-07T12:55:13.434716Z 0 [Note] InnoDB: Using CPU crc32 instructions
Mar 07 07:55:13 someserver.cornell.edu mysqld[9902]: 2019-03-07T12:55:13.437046Z 0 [Note] InnoDB: Initializing buffer pool, total size = 16G, instances = 16, chunk size = 128M
Mar 07 07:55:13 someserver.cornell.edu sudo[9840]: cs : TTY=pts/0 ; PWD=/home/cs ; USER=root ; COMMAND=/bin/journalctl -xe
Mar 07 07:55:13 someserver.cornell.edu sudo[9840]: pam_unix(sudo:session): session opened for user root by cs(uid=0)

2

Hi Klassey,

The partial error displayed on systemctl status doesn’t show where the problem is. Can you please send us a copy of full mysql error log?

3

cat mysql-error.log

2019-03-08T12:31:03.250649Z 0 [Warning] Changed limits: max_open_files: 5000 (requested 40411)
2019-03-08T12:31:03.251103Z 0 [Warning] Changed limits: table_open_cache: 2295 (requested 20000)
2019-03-08T12:31:03.483845Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-03-08T12:31:03.486673Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.25-28-log) starting as process 7332 …
2019-03-08T12:31:03.495404Z 0 [Note] InnoDB: PUNCH HOLE support available
2019-03-08T12:31:03.495461Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2019-03-08T12:31:03.495471Z 0 [Note] InnoDB: Uses event mutexes
2019-03-08T12:31:03.495482Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2019-03-08T12:31:03.495494Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.7
2019-03-08T12:31:03.495503Z 0 [Note] InnoDB: Using Linux native AIO
2019-03-08T12:31:03.496136Z 0 [Note] InnoDB: Number of pools: 1
2019-03-08T12:31:03.496314Z 0 [Note] InnoDB: Using CPU crc32 instructions
2019-03-08T12:31:03.498599Z 0 [Note] InnoDB: Initializing buffer pool, total size = 16G, instances = 16, chunk size = 128M
2019-03-08T12:31:04.809513Z 0 [Note] InnoDB: Completed initialization of buffer pool
2019-03-08T12:31:05.291586Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2019-03-08T12:31:05.306046Z 0 [Note] InnoDB: Recovering partial pages from the parallel doublewrite buffer at /var/lib/mysql/xb_doublewrite
2019-03-08T12:31:05.397823Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2019-03-08T12:31:05.783646Z 0 [Note] InnoDB: Log scan progressed past the checkpoint lsn 1039173616797
2019-03-08T12:31:05.808109Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1039176362262
2019-03-08T12:31:05.808646Z 0 [Note] InnoDB: Database was not shutdown normally!
2019-03-08T12:31:05.808657Z 0 [Note] InnoDB: Starting crash recovery.
2019-03-08T12:31:05.871206Z 0 [Note] InnoDB: Created parallel doublewrite buffer at /var/lib/mysql/xb_doublewrite, size 62914560 bytes
2019-03-08T12:31:05.963936Z 0 [ERROR] InnoDB: Trying to access page number 424149251 in space 0, space name innodb_system, which is outside the tablespace bounds. Byte offset 0, len 16384, i/o type read. If you get this error at mysqld startup, please check that your my.cnf matches the ibdata files that you have in the MySQL server.
2019-03-08T12:31:05.963979Z 0 [ERROR] InnoDB: Server exits.
2019-03-08T12:31:06.443957Z 0 [Warning] Changed limits: max_open_files: 5000 (requested 40411)
2019-03-08T12:31:06.444393Z 0 [Warning] Changed limits: table_open_cache: 2295 (requested 20000)
2019-03-08T12:31:06.674135Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-03-08T12:31:06.676979Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.25-28-log) starting as process 7399 …
2019-03-08T12:31:06.684829Z 0 [Note] InnoDB: PUNCH HOLE support available
2019-03-08T12:31:06.684904Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2019-03-08T12:31:06.684914Z 0 [Note] InnoDB: Uses event mutexes
2019-03-08T12:31:06.684923Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2019-03-08T12:31:06.684934Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.7
2019-03-08T12:31:06.684945Z 0 [Note] InnoDB: Using Linux native AIO
2019-03-08T12:31:06.685569Z 0 [Note] InnoDB: Number of pools: 1
2019-03-08T12:31:06.685743Z 0 [Note] InnoDB: Using CPU crc32 instructions
2019-03-08T12:31:06.688045Z 0 [Note] InnoDB: Initializing buffer pool, total size = 16G, instances = 16, chunk size = 128M
2019-03-08T12:31:08.004828Z 0 [Note] InnoDB: Completed initialization of buffer pool
2019-03-08T12:31:08.492308Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2019-03-08T12:31:08.506859Z 0 [Note] InnoDB: Recovering partial pages from the parallel doublewrite buffer at /var/lib/mysql/xb_doublewrite
2019-03-08T12:31:08.595607Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2019-03-08T12:31:08.984681Z 0 [Note] InnoDB: Log scan progressed past the checkpoint lsn 1039173616797
2019-03-08T12:31:09.009445Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1039176362262
2019-03-08T12:31:09.010010Z 0 [Note] InnoDB: Database was not shutdown normally!
2019-03-08T12:31:09.010025Z 0 [Note] InnoDB: Starting crash recovery.
2019-03-08T12:31:09.072782Z 0 [Note] InnoDB: Created parallel doublewrite buffer at /var/lib/mysql/xb_doublewrite, size 62914560 bytes
2019-03-08T12:31:09.164812Z 0 [ERROR] InnoDB: Trying to access page number 424149251 in space 0, space name innodb_system, which is outside the tablespace bounds. Byte offset 0, len 16384, i/o type read. If you get this error at mysqld startup, please check that your my.cnf matches the ibdata files that you have in the MySQL server.
2019-03-08T12:31:09.164855Z 0 [ERROR] InnoDB: Server exits.
2019-03-08T12:31:09.695160Z 0 [Warning] Changed limits: max_open_files: 5000 (requested 40411)
2019-03-08T12:31:09.695598Z 0 [Warning] Changed limits: table_open_cache: 2295 (requested 20000)
2019-03-08T12:31:09.924053Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-03-08T12:31:09.926866Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.25-28-log) starting as process 7465 …
2019-03-08T12:31:09.934647Z 0 [Note] InnoDB: PUNCH HOLE support available
2019-03-08T12:31:09.934707Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2019-03-08T12:31:09.934717Z 0 [Note] InnoDB: Uses event mutexes
2019-03-08T12:31:09.934725Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2019-03-08T12:31:09.934736Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.7
2019-03-08T12:31:09.934747Z 0 [Note] InnoDB: Using Linux native AIO
2019-03-08T12:31:09.935383Z 0 [Note] InnoDB: Number of pools: 1
2019-03-08T12:31:09.935561Z 0 [Note] InnoDB: Using CPU crc32 instructions
2019-03-08T12:31:09.937837Z 0 [Note] InnoDB: Initializing buffer pool, total size = 16G, instances = 16, chunk size = 128M
2019-03-08T12:31:11.252691Z 0 [Note] InnoDB: Completed initialization of buffer pool
2019-03-08T12:31:11.737124Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2019-03-08T12:31:11.751512Z 0 [Note] InnoDB: Recovering partial pages from the parallel doublewrite buffer at /var/lib/mysql/xb_doublewrite
2019-03-08T12:31:11.840374Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2019-03-08T12:31:12.228944Z 0 [Note] InnoDB: Log scan progressed past the checkpoint lsn 1039173616797
2019-03-08T12:31:12.253328Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1039176362262
2019-03-08T12:31:12.253867Z 0 [Note] InnoDB: Database was not shutdown normally!
2019-03-08T12:31:12.253885Z 0 [Note] InnoDB: Starting crash recovery.
2019-03-08T12:31:12.360392Z 0 [Note] InnoDB: Created parallel doublewrite buffer at /var/lib/mysql/xb_doublewrite, size 62914560 bytes

4

Hi Klassey,

2019-03-08T12:31:09.164812Z 0 [ERROR] InnoDB: Trying to access page number 424149251 in space 0, space name innodb_system, which is outside the tablespace bounds. Byte offset 0, len 16384, i/o type read. If you get this error at mysqld startup, please check that your my.cnf matches the ibdata files that you have in the MySQL server.

This looks like you copied files from one server to another but the innodb config such as the size of ibdata1 differs.

Can you please share below information:
my.cnf from old server
my.cnf from new server
output of: ls -l /var/lib/mysql/

5

I made some tweaks and managed to get it running, I then ran into other issues when setting it up as a slave. Many errors with processing of data series files coming in from the master. We experienced a similar issue when we tried to use AWS RDS as a slave. We had to nix that and ensure that the master and slave were running identical versions of mysql-server. I might be in the same boat again. I may just have to bite the bullet and schedule an outage, do a sqldump and import on new servers running Percona. I appreciate the assistance. Thank you.