maria-discuss team mailing list archive

[Jeff Dyke <jeff.dyke@xxxxxxxxx>]

Hi Karl, someone else just saw that as well.  it was indeed a type in the
email.  To answer your questions; the plugins are the same sql_error_log
and socket_plugin (which is no longer needed in 10.4 and was removed after
the upgrade).  Also since i can restore a new node from a staging backup,
in either VPC.

Databases are a good question, no i have not.  Perhaps i should try a
complete set of combinations, which may be a better use of time than
brining the cluster up to 5 and then seeing if i can take a physical backup
of one of the new nodes.

On Sat, Apr 18, 2020 at 1:11 PM Karl Erik Levik <karl.levik@xxxxxxxxx>
wrote:

> Well, what I mean is, you're missing the "q" in "mysql", so I was thinking
> perhaps that command doesn't actually do what you think it does?
>
> If that's not the issue, then I guess you have to look again at your prod
> nodes and figure out what, if anything, is special/unusual about them. Do
> you have any special plugins enabled? Any special storage engines used that
> you don't have on staging? (Wouldn't think so since only InnoDB is properly
> supported by Galera ...) Do you have any crud in your datadir that might
> create problems for mariabackup?
>
> Have you tried doing a partial backup/restore instead, i.e. only some of
> the databases?
>
> Cheers,
> Karl
>
> On Sat, 18 Apr 2020 at 17:25, Jeff Dyke <jeff.dyke@xxxxxxxxx> wrote:
>
>> Thanks Karl, that is just there so everything under mysql is deleted and
>> not the directory itself as mariabackup needs the datadir clean.  And i'm
>> using the same "script" on both VPCs.
>>
>> Appreciate the read!
>>
>> On Sat, Apr 18, 2020 at 6:01 AM Karl Erik Levik <karl.levik@xxxxxxxxx>
>> wrote:
>>
>>> Hi Jeff,
>>>
>>> Probably not helpful, but I dunno if this is a typo just in your email
>>> or also in the script?
>>>
>>> > sudo rm -rf /var/lib/mysl/*
>>>
>>> Cheers,
>>> Karl
>>>
>>> On Thu, 16 Apr 2020 at 00:47, Jeff Dyke <jeff.dyke@xxxxxxxxx> wrote:
>>>
>>>> This has biting me for a bit, and i have tried a litany of things.
>>>> Here is the issue:
>>>> VPC 1: (prod)
>>>> Mariadb 10.4.12 - galera in a 3 node cluster, cluster functioning
>>>> perfectly
>>>>
>>>> VPC 2: (staging)
>>>> Mariadb 10.4.12 galera in a 3 node cluster also running smoothly.
>>>>
>>>> -- backup script (clipped for only the essentials) --
>>>> set -e
>>>> DIR="/home/jeff/restore"
>>>> rm -rf $DIR
>>>> mkdir $DIR
>>>> AUTH='--defaults-extra-file=/root/db.cnf -u root'
>>>> sudo mysql ${AUTH} -e "SET GLOBAL wsrep_desync = ON;"
>>>> sudo mysql ${AUTH} -e "SET GLOBAL wsrep_on = OFF";
>>>> sudo mariabackup ${AUTH} --backup --binlog-info=ON
>>>> --target-dir="${DIR}" 2>&1 | grep -vE "Copying|done|log scanned"
>>>> sudo mysql ${AUTH} -e "SET GLOBAL wsrep_on = ON;"
>>>> sudo mysql ${AUTH} -e "SET GLOBAL wsrep_desync = OFF";
>>>>
>>>> -- end script --
>>>>
>>>> -- restore process --
>>>> sudo service mariadb stop (or kill -9 PID only when the server won't
>>>> start)
>>>> sudo rm -rf /var/lib/mysl/*
>>>> sudo mariabackup --prepare --target-dir=restore
>>>> sudo mariabackup --copy-back --target-dir=restore
>>>> sudo chown -R mysql:mysql /var/lib/mysql/*
>>>> sudo service mariadb start
>>>> -- end restore --
>>>>
>>>> I can run this on any of the script and backup process on any of the
>>>> *staging* nodes and restore them to a freshly built server within that
>>>> VPC.  I can also take the backup from *staging* and load in into a
>>>> newly build machine on the *prod* VPC.
>>>>
>>>> What i can not do is run the script on any of the prod members and
>>>> restore it to a newly built server, regardless of location.  (really the
>>>> locations don't matter at all)
>>>>
>>>> The following is the log from a failing restore from a proddb backup
>>>> and restore:
>>>> 2020-04-15 22:11:10 0 [Note] InnoDB: Using Linux native AIO
>>>> 2020-04-15 22:11:10 0 [Note] InnoDB: Mutexes and rw_locks use GCC
>>>> atomic builtins
>>>> 2020-04-15 22:11:10 0 [Note] InnoDB: Uses event mutexes
>>>> 2020-04-15 22:11:10 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
>>>> 2020-04-15 22:11:10 0 [Note] InnoDB: Number of pools: 1
>>>> 2020-04-15 22:11:10 0 [Note] InnoDB: Using SSE2 crc32 instructions
>>>> 2020-04-15 22:11:10 0 [Note] mysqld: O_TMPFILE is not supported on /tmp
>>>> (disabling future attempts)
>>>> 2020-04-15 22:11:10 0 [Note] InnoDB: Initializing buffer pool, total
>>>> size = 2G, instances = 8, chunk size = 128M
>>>> 2020-04-15 22:11:10 0 [Note] InnoDB: Completed initialization of buffer
>>>> pool
>>>> 2020-04-15 22:11:10 0 [Note] InnoDB: If the mysqld execution user is
>>>> authorized, page cleaner thread priority can be changed. See the man page
>>>> of setpriority().
>>>> 2020-04-15 22:11:10 0 [Note] InnoDB: Setting log file ./ib_logfile101
>>>> size to 50331648 bytes
>>>> 2020-04-15 22:11:10 0 [Note] InnoDB: Setting log file ./ib_logfile1
>>>> size to 50331648 bytes
>>>> 2020-04-15 22:11:10 0 [Note] InnoDB: Renaming log file ./ib_logfile101
>>>> to ./ib_logfile0
>>>> 2020-04-15 22:11:10 0 [Note] InnoDB: New log files created,
>>>> LSN=42006460184
>>>> 2020-04-15 22:11:10 0 [Note] InnoDB: 128 out of 128 rollback segments
>>>> are active.
>>>> 2020-04-15 22:11:10 0 [Note] InnoDB: Creating shared tablespace for
>>>> temporary tables
>>>> 2020-04-15 22:11:10 0 [Note] InnoDB: Setting file './ibtmp1' size to 12
>>>> MB. Physically writing the file full; Please wait ...
>>>> 2020-04-15 22:11:10 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
>>>> 2020-04-15 22:11:10 0 [Note] InnoDB: Waiting for purge to start
>>>> 2020-04-15 22:11:10 0 [Note] InnoDB: 10.4.12 started; log sequence
>>>> number 42006460428; transaction id 23858673
>>>> 2020-04-15 22:11:10 0 [Note] InnoDB: Loading buffer pool(s) from
>>>> /var/lib/mysql/ib_buffer_pool
>>>> 2020-04-15 22:11:10 0 [Note] Plugin 'FEEDBACK' is disabled.
>>>> 2020-04-15 22:11:11 0 [Note] InnoDB: Buffer pool(s) load completed at
>>>> 200415 22:11:11
>>>> -- hangs here for about an hour before timing out --
>>>>
>>>>
>>>> Logfile from a successful start
>>>>
>>>> 2020-04-15 23:13:15 0 [Note] InnoDB: Using Linux native AIO
>>>> 2020-04-15 23:13:15 0 [Note] InnoDB: Mutexes and rw_locks use GCC
>>>> atomic builtins
>>>> 2020-04-15 23:13:15 0 [Note] InnoDB: Uses event mutexes
>>>> 2020-04-15 23:13:15 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
>>>> 2020-04-15 23:13:15 0 [Note] InnoDB: Number of pools: 1
>>>> 2020-04-15 23:13:15 0 [Note] InnoDB: Using SSE2 crc32 instructions
>>>> 2020-04-15 23:13:15 0 [Note] mysqld: O_TMPFILE is not supported on /tmp
>>>> (disabling future attempts)
>>>> 2020-04-15 23:13:15 0 [Note] InnoDB: Initializing buffer pool, total
>>>> size = 2G, instances = 8, chunk size = 128M
>>>> 2020-04-15 23:13:16 0 [Note] InnoDB: Completed initialization of buffer
>>>> pool
>>>> 2020-04-15 23:13:16 0 [Note] InnoDB: If the mysqld execution user is
>>>> authorized, page cleaner thread priority can be changed. See the man page
>>>> of setpriority().
>>>> 2020-04-15 23:13:16 0 [Note] InnoDB: Setting log file ./ib_logfile101
>>>> size to 50331648 bytes
>>>> 2020-04-15 23:13:16 0 [Note] InnoDB: Setting log file ./ib_logfile1
>>>> size to 50331648 bytes
>>>> 2020-04-15 23:13:16 0 [Note] InnoDB: Renaming log file ./ib_logfile101
>>>> to ./ib_logfile0
>>>> 2020-04-15 23:13:16 0 [Note] InnoDB: New log files created,
>>>> LSN=43323668550
>>>> 2020-04-15 23:13:16 0 [Note] InnoDB: 128 out of 128 rollback segments
>>>> are active.
>>>> 2020-04-15 23:13:16 0 [Note] InnoDB: Creating shared tablespace for
>>>> temporary tables
>>>> 2020-04-15 23:13:16 0 [Note] InnoDB: Setting file './ibtmp1' size to 12
>>>> MB. Physically writing the file full; Please wait ...
>>>> 2020-04-15 23:13:16 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
>>>> 2020-04-15 23:13:16 0 [Note] InnoDB: Waiting for purge to start
>>>> 2020-04-15 23:13:16 0 [Note] InnoDB: 10.4.12 started; log sequence
>>>> number 43323669004; transaction id 22422468
>>>> 2020-04-15 23:13:16 0 [Note] InnoDB: Loading buffer pool(s) from
>>>> /var/lib/mysql/ib_buffer_pool
>>>> 2020-04-15 23:13:16 0 [Note] Plugin 'FEEDBACK' is disabled.
>>>> 2020-04-15 23:13:16 0 [Note] Server socket created on IP: '0.0.0.0'.
>>>> 2020-04-15 23:13:16 0 [Note] Reading of all Master_info entries
>>>> succeeded
>>>> 2020-04-15 23:13:16 0 [Note] Added new Master_info '' to hash table
>>>> 2020-04-15 23:13:16 0 [Note] /usr/sbin/mysqld: ready for connections.
>>>> Version: '10.4.12-MariaDB-1:10.4.12+maria~bionic'  socket:
>>>> '/var/run/mysqld/mysqld.sock'  port: 3306  mariadb.org binary
>>>> distribution
>>>> 2020-04-15 23:13:22 0 [Note] InnoDB: Buffer pool(s) load completed at
>>>> 200415 23:13:22
>>>>
>>>> ------------------------
>>>> The main difference between the two is that:
>>>> - the server socket is logged
>>>> - master_info is logged
>>>> - and it binds to the port/socket
>>>>
>>>> Before they *both* end with
>>>> 2020-04-15 23:13:22 0 [Note] InnoDB: Buffer pool(s) load completed at
>>>> 200415 23:13:22
>>>>
>>>> -- Other Notes --
>>>> - Config files are the same except for ips, both on newly built servers
>>>> as the cluster as they are managed through salt.  I ran a diff -r against
>>>> prod vs staging and only saw
>>>>
>>>> There has to be some corruption in the mariabackup output, but still
>>>> reported OK.  Since i can put a staging backup on a prod server, but i can
>>>> not put a prod backup on a staging server.
>>>>
>>>> If you've gotten this far, thank you.  If you have any ideas on what i
>>>> could try, i'd be grateful.  aria_chk and all other table checks return
>>>> correctly.  I'm not sure what makes a server write out the master_info, so
>>>> possibly that is a pointer.
>>>>
>>>> I'm happy to answer any questions, i've tried way to many things to
>>>> post here as i didn't want to write until i had enough of dumb stuff
>>>> figured out.  I could rebuild the prod cluster but am really looking to
>>>> understand this.  I don't really want to build my own with debug info on
>>>> b/c why would they be different between the two vpcs.
>>>>
>>>>
>>>> _______________________________________________
>>>> Mailing list: https://launchpad.net/~maria-discuss
>>>> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
>>>> Unsubscribe : https://launchpad.net/~maria-discuss
>>>> More help   : https://help.launchpad.net/ListHelp
>>>>
>>> _______________________________________________
>>> Mailing list: https://launchpad.net/~maria-discuss
>>> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
>>> Unsubscribe : https://launchpad.net/~maria-discuss
>>> More help   : https://help.launchpad.net/ListHelp
>>>
>> _______________________________________________
>> Mailing list: https://launchpad.net/~maria-discuss
>> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
>> Unsubscribe : https://launchpad.net/~maria-discuss
>> More help   : https://help.launchpad.net/ListHelp
>>
> _______________________________________________
> Mailing list: https://launchpad.net/~maria-discuss
> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp
>