cesardba

Migrar MySQL do Windows para Linux

Esta semana surgiu uma dúvida. É possivel migrar o MySQL do Windows para Linux apenas copiando os datafiles? A resposta curta e direta é sim, é possivel migrar de forma simples apenas copiando os datafiles. Abaixo segue o roteiro que utilizei para homologar esta afirmação.

Observação: O Windows por default não é case-sensitive, diferente do linux que é case-sensitive.

Versão do sistema operacional de origem:

mysql> show variables  where variable_name like 'version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| version                 | 5.7.44-log                   |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Win64                        |
+-------------------------+------------------------------+
4 rows in set (0.01 sec)

Databases criados para os testes:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| teste              |
| teste1             |
| teste2             |
+--------------------+
7 rows in set (0.00 sec)

Diretório datadir que irei copiar com todo o conteúdo com os databases e datafiles:

mysql> SHOW VARIABLES WHERE Variable_Name LIKE '%dir' ;
+---------------------------+---------------------------------------------------------+
| Variable_name             | Value                                                   |
+---------------------------+---------------------------------------------------------+
| basedir                   | C:\Program Files\MySQL\MySQL Server 5.7\                |
| character_sets_dir        | C:\Program Files\MySQL\MySQL Server 5.7\share\charsets\ |
| datadir                   | C:\ProgramData\MySQL\MySQL Server 5.7\Data\             |
| innodb_data_home_dir      |                                                         |
| innodb_log_group_home_dir | .\                                                      |
| innodb_tmpdir             |                                                         |
| lc_messages_dir           | C:\Program Files\MySQL\MySQL Server 5.7\share\          |
| plugin_dir                | C:\Program Files\MySQL\MySQL Server 5.7\lib\plugin\     |
| slave_load_tmpdir         | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp         |
| tmpdir                    | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp         |
+---------------------------+---------------------------------------------------------+
10 rows in set (0.00 sec)

Versão do sistema operacional de destino:

[root@srv_mysql ~]# cat /etc/*-release | grep PRETTY
PRETTY_NAME="Oracle Linux Server 7.9"

[root@srv_mysql ~]# uname -a
Linux srv_mysql 5.4.17-2136.322.6.2.el7uek.x86_64 #2 SMP Sat Aug 19 11:55:11 PDT 2023 x86_64 x86_64 x86_64 GNU/Linux

Versão do MySQL de destino:

[root@srv_mysql ~]# mysql --version
mysql  Ver 14.14 Distrib 5.7.44, for Linux (x86_64) using  EditLine wrapper
[root@srv_mysql ~]#

Realize o download da ferramenta winscp, pare o serviço do MySQL do Windows e copie via winscp todo o conteúdo do diretório datadir (C:\ProgramData\MySQL\MySQL Server 5.7\Data\) para o servidor linux:

Copiando todo o conteúdo do datadir para o servidor com Linux:

No servidor Linux, altere o dono de todos os arquivos e diretórios para o mysql:

[root@srv_mysql ~]# cd /var/lib/mysql
[root@srv_mysql mysql]# ls -lat
total 110692
drwxr-x--x   8 mysql mysql     4096 Nov  6 13:26 .
drwxr-xr-x   2 root  root        58 Nov  6 13:26 teste2
drwxr-xr-x   2 root  root        58 Nov  6 13:26 teste1
drwxr-xr-x   2 root  root        56 Nov  6 13:26 teste
drwxr-xr-x   2 root  root      8192 Nov  6 13:26 sys
drwxr-xr-x   2 root  root      8192 Nov  6 13:26 performance_schema
drwxr-xr-x   2 root  root      4096 Nov  6 13:26 mysql
-rw-r--r--   1 root  root  12582912 Nov  6 13:21 ibdata1
-rw-r--r--   1 root  root  50331648 Nov  6 13:21 ib_logfile0
-rw-r--r--   1 root  root     16688 Nov  6 13:21 WIN-6PF17Q3PBGA.err
-rw-r--r--   1 root  root       603 Nov  6 13:21 WIN-6PF17Q3PBGA-slow.log
-rw-r--r--   1 root  root       505 Nov  6 13:21 ib_buffer_pool
drwxr-xr-x. 30 root  root      4096 Nov  6 13:05 ..
-rw-r--r--   1 root  root      1131 Nov  6 12:13 client-cert.pem
-rw-r--r--   1 root  root      1707 Nov  6 12:13 client-key.pem
-rw-r--r--   1 root  root      1707 Nov  6 12:13 private_key.pem
-rw-r--r--   1 root  root       461 Nov  6 12:13 public_key.pem
-rw-r--r--   1 root  root      1131 Nov  6 12:13 server-cert.pem
-rw-r--r--   1 root  root      1707 Nov  6 12:13 server-key.pem
-rw-r--r--   1 root  root        56 Nov  6 12:13 auto.cnf
-rw-r--r--   1 root  root      1707 Nov  6 12:13 ca-key.pem
-rw-r--r--   1 root  root      1131 Nov  6 12:13 ca.pem
-rw-r--r--   1 root  root  50331648 Nov  6 12:13 ib_logfile1

[root@srv_mysql mysql]# chown -R mysql:mysql *

Altere o arquivo de configuração do MySQL e inclua o lower_case_table_names=1:

[root@srv_mysql mysql]# cat /etc/my.cnf | grep lower_case_table_names
lower_case_table_names=1

Em outro terminal, verifique os logs em tempo real antes de iniciar o serviço do MySQL:

[root@srv_mysql ~]# tail -f /var/log/mysqld.log

Inicie o serviço do MySQL e verifique os logs em busca de possíveis erros:

[root@srv_mysql mysql]# systemctl start mysqld
[root@srv_mysql mysql]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Mon 2023-11-06 13:32:07 -03; 22s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 20718 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 20701 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 20722 (mysqld)
   CGroup: /system.slice/mysqld.service
           └─20722 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

Nov 06 13:32:06 srv_mysql systemd[1]: Starting MySQL Server...
Nov 06 13:32:07 srv_mysql systemd[1]: Started MySQL Server.

Verifique todos os databases e objetos:

[root@srv_mysql mysql]# mysql -uroot -p'12345678'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.44 MySQL Community Server (GPL)

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases
    -> ;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| teste              |
| teste1             |
| teste2             |
+--------------------+
7 rows in set (0.00 sec)


mysql> show variables  where variable_name like 'version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| version                 | 5.7.44                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Linux                        |
+-------------------------+------------------------------+
4 rows in set (0.00 sec)

mysql> select count(*) from teste.tabela;
+----------+
| count(*) |
+----------+
|     1100 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from teste1.tabela1;
+----------+
| count(*) |
+----------+
|     1200 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from teste2.tabela2;
+----------+
| count(*) |
+----------+
|     1300 |
+----------+
1 row in set (0.00 sec)

Logs gerados sem erros ao iniciar o MySQL:

[root@srv_mysql ~]# tail -f /var/log/mysqld.log
2023-11-06T16:32:06.999774Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2023-11-06T16:32:07.003131Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.44) starting as process 20722 ...
2023-11-06T16:32:07.009321Z 0 [Note] InnoDB: PUNCH HOLE support available
2023-11-06T16:32:07.009376Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2023-11-06T16:32:07.009381Z 0 [Note] InnoDB: Uses event mutexes
2023-11-06T16:32:07.009385Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2023-11-06T16:32:07.009388Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.13
2023-11-06T16:32:07.009392Z 0 [Note] InnoDB: Using Linux native AIO
2023-11-06T16:32:07.009828Z 0 [Note] InnoDB: Number of pools: 1
2023-11-06T16:32:07.010056Z 0 [Note] InnoDB: Using CPU crc32 instructions
2023-11-06T16:32:07.013456Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2023-11-06T16:32:07.028303Z 0 [Note] InnoDB: Completed initialization of buffer pool
2023-11-06T16:32:07.033471Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2023-11-06T16:32:07.046741Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2023-11-06T16:32:07.060947Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2023-11-06T16:32:07.061018Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2023-11-06T16:32:07.092119Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2023-11-06T16:32:07.093980Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2023-11-06T16:32:07.094016Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2023-11-06T16:32:07.094475Z 0 [Note] InnoDB: Waiting for purge to start
2023-11-06T16:32:07.144965Z 0 [Note] InnoDB: 5.7.44 started; log sequence number 3314278
2023-11-06T16:32:07.145513Z 0 [Note] Plugin 'FEDERATED' is disabled.
2023-11-06T16:32:07.158987Z 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.
2023-11-06T16:32:07.159031Z 0 [Note] Skipping generation of SSL certificates as certificate files are present in data directory.
2023-11-06T16:32:07.159040Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2023-11-06T16:32:07.159045Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2023-11-06T16:32:07.164110Z 0 [Warning] CA certificate ca.pem is self signed.
2023-11-06T16:32:07.164160Z 0 [Note] Skipping generation of RSA key pair as key files are present in data directory.
2023-11-06T16:32:07.166023Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2023-11-06T16:32:07.168866Z 0 [Note] InnoDB: Buffer pool(s) load completed at 231106 13:32:07
2023-11-06T16:32:07.169868Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2023-11-06T16:32:07.169983Z 0 [Note] IPv6 is available.
2023-11-06T16:32:07.170020Z 0 [Note]   - '::' resolves to '::';
2023-11-06T16:32:07.170042Z 0 [Note] Server socket created on IP: '::'.
2023-11-06T16:32:07.180041Z 0 [Note] Event Scheduler: Loaded 0 events
2023-11-06T16:32:07.180556Z 0 [Note] /usr/sbin/mysqld: ready for connections.

Por segurança você poderá realizar o checksum de todas as tabelas com o pt-table-checksum, abaixo segue como instalar e testar:

# Repo:
#https://docs.percona.com/percona-software-repositories/index.html
[root@srv_mysql mysql]# yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm
[root@srv_mysql mysql]# yum install -y percona-toolkit

Executando o checksum nos ambientes envolvidos:

[root@srv_mysql mysql]# pt-table-checksum --host=localhost --user=root --password=12345678
[root@srv_mysql mysql]# pt-table-checksum --host=192.168.79130 --user=cesar_dba --password=12345678

Links de referência:

https://dev.mysql.com/doc/refman/8.0/en/identifier-case-sensitivity.html#:~:text=Table%20names%20are%20stored%20in,database%20names%20and%20table%20aliases.

Agradeço ao Vinicius Grippa e ao Marcelo Altmann por sempre ajudarem.

search previous next tag category expand menu location phone mail time cart zoom edit close