Physical Backup

General Questions
,
1

Hello team! we need to explore alternatives for performing physical backups of a MySQL database, and I wanted to know if it is possible to backup a database by copying the contents of the data directory from one server to another, if necessary, by shutting down MySQL to perform the copy.

Thanks in advance

2

Hi @Percona_new welcome to the Percona forums!

Yes this is a valid method of doing a backup, it is known as a cold backup, and is a supported method.

3

Thank Michael! By any chance, do you have the procedure? or step by step guide?
Thank!

4

Hi @Percona_new
The process is essentially:

  1. Stop MySQL sudo systemctl stop mysql
  2. Copy the data directory cp -avr /var/lib/mysql /my/backup/location
  3. Start MySQL sudo systemctl start mysql
5

Michael thank you for your help! The general process is clear, but maybe I wasn’t clear with the question. What I was asking is whether I can move/copy just a database from one installation to another using the cold backup method.
Let’s suppose I have a database called “test” on Server A, and I want to move it to Server B using the cold backup method. Currently, we are doing it with MySQL Dumper, but we need to test the possibility of doing it with cold backup.
Thanks!

6

ahh I see more clearly your question - you are asking whether a schema (database) can be copied between one server to another. the answer to this question is NO when using InnoDB, as there are internal tables to MySQL that track the existence of schemas and their associated tables. You would need to proceed with a logical export of the database, and then load it in via the MySQL protocol.

Your other option is to leverage Transportable Tablespaces available since MySQL 5.6
https://dev.mysql.com/doc/refman/8.0/en/innodb-table-import.html