Moving large database to MariaDB + Galera cluster

[Felipe Carballo]

Hello folks,

I built a MariaDB + Galera cluster and now I need migrate my production database to this new cluster.

My production database has ~250GB. The MySQL dump took a long time to complete and now I'm trying to import it to the cluster.

I'm restoring the dump in a specific node. However, after some minutes, the importing stops with the error message "MySQL Server Has Gone Away". Reading the logs of this specific node I see no message indicating that MariaDB was restarted.

Does anyone have some clue about what is going on? Should I use another approach? And If I make a copy of the database files from the production to a specific node, the database is gonna be synced between the nodes?

Thank you!

Regards,

Felipe Carballo

[Nikolaos Vyzas]

Which version of MariaDB are you using? 

If you are using 5.5 I'm certain you can just copy the data files across however you'll have to snapshot the data in some way - either using a backup tool or LVM... and then replay binary logs to update with the delta.

If you can do with downtime the simplest would be to just copy it across.

[Felipe Carballo]

I'm using 5.5.

2014/1/12 Nikolaos Vyzas <pon...@gmail.com>

--
You received this message because you are subscribed to a topic in the Google Groups "codership" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/codership-team/l79xvz3RsmI/unsubscribe.
To unsubscribe from this group and all its topics, send an email to codership-tea...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

--
Felipe Carballo

[Alex Yurchenko]

On 2014-01-11 20:05, Felipe Carballo wrote:
> Hello folks,
>
> I built a MariaDB + Galera cluster and now I need migrate my production
> database to this new cluster.
>
> My production database has ~250GB. The MySQL dump took a long time to
> complete and now I'm trying to import it to the cluster.
>
> I'm restoring the dump in a specific node. However, after some minutes,
> the
> importing stops with the error message "MySQL Server Has Gone Away".
> Reading the logs of this specific node I see no message indicating that
> MariaDB was restarted.
>
> Does anyone have some clue about what is going on?

1) Does error log contain any messages for that period of time?

2) I would suspect that you used --single-transaction option to
mysqldump which would create huge transactions on mysqldump import.
Galera has 2Gb limit on transaction size, so that will most likely be
triggered. In this case simple editing of mysqldump file to remove START
TRANSACTION/COMMIT statements should fix it.

> Should I use another
> approach? And If I make a copy of the database files from the
> production to
> a specific node, the database is gonna be synced between the nodes?

With this you'll need:

1) shut down all cluster nodes
2) remove grastate.dat file from the node you'll be copying the data
directory to.
3) copy data directory (or use xtrabackup)
4) start that node
5) start other nodes - they will copy db from the first one.

or

1) shut down all cluster nodes
2) copy data directory to ALL nodes
3) start the cluster again

> Thank you!
>
> Regards,
>
> Felipe Carballo

--
Alexey Yurchenko,
Codership Oy, www.codership.com
Skype: alexey.yurchenko, Phone: +358-400-516-011

[Felipe Carballo]

Hello Alex,

I see no messages in the log. I'm also not using the single transaction option.

The problem occurs with only one table that has a blob column. When I try to start the import, after less than a minute I see the error message "MySQL Server Has Gone Away".

2014/1/19 Alex Yurchenko <alexey.y...@codership.com>

--
You received this message because you are subscribed to a topic in the Google Groups "codership" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/codership-team/l79xvz3RsmI/unsubscribe.

To unsubscribe from this group and all its topics, send an email to codership-team+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.

--
Felipe Carballo

[Alex Yurchenko]

On 2014-01-21 22:00, Felipe Carballo wrote:
> Hello Alex,
>
> I see no messages in the log.

Are you sure you're checking the right log file? Do you have proper
startup messages there?

> I'm also not using the single transaction
> option.
>
> The problem occurs with only one table that has a blob column. When I
> try
> to start the import, after less than a minute I see the error message
> "MySQL Server Has Gone Away".

What happens if you first do

mysql> SET GLOBAL wsrep_on=OFF;

and then try importing the dump?

[Felipe Carballo]

Alex,

This is the last lines of the log file.

http://pastebin.com/b0iWjG26

Even after setting the wsrep_on=OFF it still ON. Is it normal?

http://pastebin.com/VL8GpFZ7

2014/1/21 Alex Yurchenko <alexey.y...@codership.com>

--
Felipe Carballo

[Alex Yurchenko]

On 2014-01-22 04:40, Felipe Carballo wrote:
> Alex,
>
> This is the last lines of the log file.
>
> http://pastebin.com/b0iWjG26

What does

SHOW STATUS LIKE 'uptime'

say immediately after "MySQL Server Has Gone Away"?

> Even after setting the wsrep_on=OFF it still ON. Is it normal?
>
> http://pastebin.com/VL8GpFZ7

It is actually not, but there you posted status, not variables.

>
> 2014/1/21 Alex Yurchenko <alexey.y...@codership.com>
>
>> On 2014-01-21 22:00, Felipe Carballo wrote:
>>
>>> Hello Alex,
>>>
>>> I see no messages in the log.
>>>
>>
>> Are you sure you're checking the right log file? Do you have proper
>> startup messages there?
>>
>>
>> I'm also not using the single transaction
>>> option.
>>>
>>> The problem occurs with only one table that has a blob column. When I
>>> try
>>> to start the import, after less than a minute I see the error message
>>> "MySQL Server Has Gone Away".
>>>
>>
>> What happens if you first do
>>
>> mysql> SET GLOBAL wsrep_on=OFF;
>>
>> and then try importing the dump?
>>
>>

--

[Felipe Carballo]

Alex,

This is the uptime right after the error:

+---------------+--------+

| Variable_name | Value  |

+---------------+--------+

| Uptime        | 768085 |

+---------------+--------+

2014/1/22 Alex Yurchenko <alexey.y...@codership.com>

--
Felipe Carballo

[Alex Yurchenko]

On 2014-01-22 15:13, Felipe Carballo wrote:
> Alex,
>

> This is the uptime right after the error:
>
> +---------------+--------+
> | Variable_name | Value |
> +---------------+--------+
> | Uptime | 768085 |
> +---------------+--------+

Well, this is then next to impossible to diagnose... Just in case check
max_allowed_packet on both client and server.

[Felipe Carballo]

Alex,

I don't know exactly what I did but now it's working. The dump was successfully imported.

Here is my confs. I made a new copy of this confs from the old server.

[mysqld]

# # SAFETY # #

max-allowed-packet = 16M

max-connect-errors = 1000000

innodb = FORCE

innodb-strict-mode = 1

skip_name_resolve

# # INNODB # #

innodb_flush_log_at_trx_commit = 2

innodb_buffer_pool_size = 4G

innodb_buffer_pool_instances = 12

innodb_log_file_size = 100M

innodb_file_per_table = 1

# Mandatory settings: these settings are REQUIRED for proper cluster operation

query_cache_size=0

binlog_format=ROW

log_slave_updates = 1

innodb_autoinc_lock_mode=2

# wsrep provider configuration: basic wsrep options

wsrep_provider=/usr/lib64/galera/libgalera_smm.so

wsrep_provider_options="gcache.size=6G"

wsrep_cluster_address=gcomm://192.168.30.240,192.168.30.241,192.168.30.242

wsrep_cluster_name='DB_CLUSTER'

wsrep_node_name='node0'

wsrep_sst_method=rsync

# Additional "frequently used" wsrep settings

wsrep_slave_threads=1

2014/1/22 Alex Yurchenko <alexey.y...@codership.com>

--
Felipe Carballo