Starting from MySQL cold backup

MySQL cold backup, also called offline backup

Is the simplest:
● Simple backup: you only need to copy the relevant files.
● Easy recovery: you only need to store the copied files in the same directory.
It is also the safest:
● The optimal method for obtaining consistent data copies


So what files should be backed up? The following uses InnoDB as an example:

① Frm File

The table structure definition file, but the view definition is also in it

② Shared tablespace files

The default value is ibdata1. If it is adjusted later, delete ibdata1. Otherwise, an error is returned.
Subject to two parameters:
● Innodb_data_home_dir

● Innodb_data_file_path

mysql> show variables like '%data%';+----------------------------+------------------------------------+| Variable_name              | Value                              |+----------------------------+------------------------------------+| character_set_database     | latin1                             || collation_database         | latin1_swedish_ci                  || datadir                    | /home/mysql/mysql/data/            || innodb_data_file_path      | ibdata1:50M;ibdata2:50M:autoextend || innodb_data_home_dir       | /home/mysql/mysql/ibdata           |

③ Independent tablespace file (*. IBD)

Subject to two parameters
● Innodb_file_per_table
● Datadir <= This is a global parameter. It seems that many files are infected with it.

④ Redo log files

There are two by default: ib_logfile0 and ib_logfile1
Affected by four parameters:
● Innodb_log_file_size: size of log members
● Innodb_log_files_in_group: Number of log members
● Innodb_mirrored_log_groups: Number of log groups
● Innodb_log_group_home_dir: log Group Path

⑤ Configuration file

My. CNF or my. ini


Reminder:
① If an independent tablespace and a shared tablespace are set up, backup is still required! Because,
The independent tablespace only stores data, indexes, and other information, and the rest are still stored in the shared tablespace.

② If all the above parameters are default and only datadir is set, you only need to back up the files in this directory.



What should we pay attention to during backup?

① Do not omit any physical files mentioned above
② Pay attention to the disk space size
③ Copy the local backup image to the remote



TIPS,

If you think it is too costly to shut down the server for cold backup, and the push speed is slow, you can,
Kill-19: Send a stop signal to MySQL,
Then, back up
And thenKill-18Send a Cont signal to wake up MySQL



By David Lin
2013-06-09
Good luck