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

7

Hello Michael, thanks for the response. Im back with some extra information. Each of our databases have its own tablespace which contains all the database’s tables. So for example:

CREATE TABLE test (
id int unsigned NOT NULL AUTO_INCREMENT,
description varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (id)
) /*!50100 TABLESPACE dba */ ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

When I try alter table discard tablespace:
ERROR 1148 (42000): InnoDB: Cannot discard table dba/test because it is in a general tablespace. It must be file-per-table.

How can overcome this situation?