32.MySQL-windows冷备恢复到Linux

MySQL windows上面的数据库，直接直接打包恢复到Linux上。

1.关闭windows;

net stop MySQL57 

2.windows打包

cd $DATA

Data.zip  

3.上传到Linux解压

[root@mysql2 mysql]# unzip Data.zip 
[root@mysql2 mysql]# mv Data/* mysql3306 
[root@mysql2 mysql]# chown -R mysql:mysql *
[root@mysql2 mysql]# systemctl start mysql 
[root@mysql2 mysql]# 
[root@mysql2 mysql]# mysql -uroot -prootroot

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

mysql> show databases; 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> mysql> show variables like '%data%';
+---------------------------------------+------------------------+
| Variable_name                         | Value                  |
+---------------------------------------+------------------------+
| character_set_database                | latin1                 |
| collation_database                    | latin1_swedish_ci      |
| datadir                               | /mysql/mysql3306/      |
| innodb_data_file_path                 | ibdata1:12M:autoextend |
| innodb_data_home_dir                  |                        |
| innodb_stats_on_metadata              | OFF                    |
| innodb_temp_data_file_path            | ibtmp1:12M:autoextend  |
| max_length_for_sort_data              | 1024                   |
| metadata_locks_cache_size             | 1024                   |
| metadata_locks_hash_instances         | 8                      |
| myisam_data_pointer_size              | 6                      |
| performance_schema_max_metadata_locks | -1                     |
| skip_show_database                    | OFF                    |
| updatable_views_with_limit            | YES                    |
+---------------------------------------+------------------------+
14 rows in set (0.00 sec)
4.日志检查

2023-02-18T02:44:51.696595Z 0 [Note] /mysql/soft/mysql5.7.23/bin/mysqld (mysqld 5.7.23-log) starting as process 6223 ...
2023-02-18T02:44:51.706637Z 0 [Note] InnoDB: PUNCH HOLE support available
2023-02-18T02:44:51.706663Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2023-02-18T02:44:51.706666Z 0 [Note] InnoDB: Uses event mutexes
2023-02-18T02:44:51.706668Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2023-02-18T02:44:51.706670Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
2023-02-18T02:44:51.706671Z 0 [Note] InnoDB: Using Linux native AIO
2023-02-18T02:44:51.706794Z 0 [Note] InnoDB: Number of pools: 1
2023-02-18T02:44:51.706847Z 0 [Note] InnoDB: Using CPU crc32 instructions
2023-02-18T02:44:51.707973Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2023-02-18T02:44:51.721104Z 0 [Note] InnoDB: Completed initialization of buffer pool
2023-02-18T02:44:51.728164Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2023-02-18T02:44:51.745593Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2023-02-18T02:44:51.763978Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2023-02-18T02:44:51.764052Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2023-02-18T02:44:51.795412Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2023-02-18T02:44:51.795712Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2023-02-18T02:44:51.795717Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2023-02-18T02:44:51.801511Z 0 [Note] InnoDB: 5.7.23 started; log sequence number 2565433
2023-02-18T02:44:51.801883Z 0 [Note] InnoDB: Loading buffer pool(s) from /mysql/mysql3306/ib_buffer_pool
2023-02-18T02:44:51.802262Z 0 [Note] Plugin 'FEDERATED' is disabled.
2023-02-18T02:44:51.807987Z 0 [Note] InnoDB: Buffer pool(s) load completed at 230218 10:44:51
2023-02-18T02:44:51.827890Z 0 [Warning] InnoDB: Table mysql/innodb_table_stats has length mismatch in the column name table_name.  Please run mysql_upgrade
2023-02-18T02:44:51.827933Z 0 [Warning] InnoDB: Table mysql/innodb_index_stats has length mismatch in the column name table_name.  Please run mysql_upgrade
2023-02-18T02:44:51.834135Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
2023-02-18T02:44:51.834276Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2023-02-18T02:44:51.834306Z 0 [Note] IPv6 is available.
2023-02-18T02:44:51.834313Z 0 [Note]   - '::' resolves to '::';
2023-02-18T02:44:51.834324Z 0 [Note] Server socket created on IP: '::'.
2023-02-18T02:44:51.839240Z 0 [Note] Event Scheduler: Loaded 0 events
2023-02-18T02:44:51.839360Z 0 [Note] /mysql/soft/mysql5.7.23/bin/mysqld: ready for connections.
Version: '5.7.23-log'  socket: '/mysql/mysql3306.sock'  port: 3306  MySQL Community Server (GPL)

日志正常但是有一个告警。

5.因为警告问题我们做个升级

windows:5.7.20

mysql> select version();   linux 5.7.23 
+------------+
| version()  |
+------------+
| 5.7.23-log |
+------------+
1 row in set (0.00 sec)
 

mysql5.7.20

mysql> desc mysql.innodb_index_stats;   mysql5.7.23
+------------------+---------------------+------+-----+-------------------+-----------------------------+
| Field            | Type                | Null | Key | Default           | Extra                       |
+------------------+---------------------+------+-----+-------------------+-----------------------------+
| database_name    | varchar(64)         | NO   | PRI | NULL              |                             |
| table_name       | varchar(64)         | NO   | PRI | NULL              |                             |
| index_name       | varchar(64)         | NO   | PRI | NULL              |                             |
| last_update      | timestamp           | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| stat_name        | varchar(64)         | NO   | PRI | NULL              |                             |
| stat_value       | bigint(20) unsigned | NO   |     | NULL              |                             |
| sample_size      | bigint(20) unsigned | YES  |     | NULL              |                             |
| stat_description | varchar(1024)       | NO   |     | NULL              |                             |
+------------------+---------------------+------+-----+-------------------+-----------------------------+
8 rows in set (0.01 sec)

mysql5.7.23

mysql> desc  mysql.innodb_index_stats;
+------------------+---------------------+------+-----+-------------------+-----------------------------+
| Field            | Type                | Null | Key | Default           | Extra                       |
+------------------+---------------------+------+-----+-------------------+-----------------------------+
| database_name    | varchar(64)         | NO   | PRI | NULL              |                             |
| table_name       | varchar(199)        | NO   | PRI | NULL              |                             |
| index_name       | varchar(64)         | NO   | PRI | NULL              |                             |
| last_update      | timestamp           | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| stat_name        | varchar(64)         | NO   | PRI | NULL              |                             |
| stat_value       | bigint(20) unsigned | NO   |     | NULL              |                             |
| sample_size      | bigint(20) unsigned | YES  |     | NULL              |                             |
| stat_description | varchar(1024)       | NO   |     | NULL              |                             |
+------------------+---------------------+------+-----+-------------------+-----------------------------+
8 rows in set (0.01 sec)

升级数据字典。

[root@mysql2 mysql3306]# mysql_upgrade -uroot -prootroot 
mysql_upgrade: [Warning] Using a password on the command line interface can be insecure.
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Checking system database.
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.engine_cost                                  OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.gtid_executed                                OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.server_cost                                  OK
mysql.servers                                      OK
mysql.slave_master_info                            OK
mysql.slave_relay_log_info                         OK
mysql.slave_worker_info                            OK
mysql.slow_log                                     OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
The sys schema is already up to date (version 1.5.1).
Checking databases.
sys.sys_config                                     OK
test.test1                                         OK
Upgrade process completed successfully.
Checking if update is needed.
6.重启观察日志。

发现重启后，警告日志消失，没有前面的：Table mysql/innodb_table_stats has length mismatch in the column name table_name.  Please run mysql_upgrade，说明升级成功。

日志如下：

2023-02-18T03:04:44.274630Z 0 [Note] /mysql/soft/mysql5.7.23/bin/mysqld (mysqld 5.7.23-log) starting as process 6679 ...
2023-02-18T03:04:44.277857Z 0 [Note] InnoDB: PUNCH HOLE support available
2023-02-18T03:04:44.277902Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2023-02-18T03:04:44.277904Z 0 [Note] InnoDB: Uses event mutexes
2023-02-18T03:04:44.277905Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2023-02-18T03:04:44.277907Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
2023-02-18T03:04:44.277909Z 0 [Note] InnoDB: Using Linux native AIO
2023-02-18T03:04:44.278042Z 0 [Note] InnoDB: Number of pools: 1
2023-02-18T03:04:44.278102Z 0 [Note] InnoDB: Using CPU crc32 instructions
2023-02-18T03:04:44.278857Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2023-02-18T03:04:44.284923Z 0 [Note] InnoDB: Completed initialization of buffer pool
2023-02-18T03:04:44.286317Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2023-02-18T03:04:44.297892Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2023-02-18T03:04:44.301734Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2023-02-18T03:04:44.301838Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2023-02-18T03:04:44.307940Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2023-02-18T03:04:44.308276Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2023-02-18T03:04:44.308291Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2023-02-18T03:04:44.308572Z 0 [Note] InnoDB: Waiting for purge to start
2023-02-18T03:04:44.359861Z 0 [Note] InnoDB: 5.7.23 started; log sequence number 2776632
2023-02-18T03:04:44.360316Z 0 [Note] InnoDB: Loading buffer pool(s) from /mysql/mysql3306/ib_buffer_pool
2023-02-18T03:04:44.360476Z 0 [Note] Plugin 'FEDERATED' is disabled.
2023-02-18T03:04:44.361863Z 0 [Note] InnoDB: Buffer pool(s) load completed at 230218 11:04:44
2023-02-18T03:04:44.367577Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
2023-02-18T03:04:44.367825Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2023-02-18T03:04:44.367902Z 0 [Note] IPv6 is available.
2023-02-18T03:04:44.367917Z 0 [Note]   - '::' resolves to '::';
2023-02-18T03:04:44.367943Z 0 [Note] Server socket created on IP: '::'.
2023-02-18T03:04:44.375279Z 0 [Note] Event Scheduler: Loaded 0 events
2023-02-18T03:04:44.375769Z 0 [Note] /mysql/soft/mysql5.7.23/bin/mysqld: ready for connections.
Version: '5.7.23-log'  socket: '/mysql/mysql3306.sock'  port: 3306  MySQL Community Server (GPL)