Forum for MySQL Backup. https://forums.mysql.com/list.php?28 Mon, 09 Mar 2026 02:19:20 +0000 Phorum 5.2.23 https://forums.mysql.com/read.php?28,741232,741232#msg-741232 https://forums.mysql.com/read.php?28,741232,741232#msg-741232 I have the following question.
We are using mysqlbackup for making backups of our mysql innodb clusters running version 8.0.42. Restores of the backups is working fine as long the restores are taking place in the original mysql cluster.

Question:
Is it possible to restore some or all databases backed up with mysqlbackup to another mysql innodb cluster (clean installation without new created databases but with other dbdata and dbrouter servers) with the same mysql cluster version?

When restoring, the datadir need to be empty and after a restore information of the other cluster is restored as well like the group replication nodes and router information.

Thanks in advance.]]> Richard vd Hulst Backup Mon, 22 Sep 2025 10:31:49 +0000 https://forums.mysql.com/read.php?28,741095,741095#msg-741095 https://forums.mysql.com/read.php?28,741095,741095#msg-741095
I'm experiencing an unexpected issue with mysqldump after granting SHOW VIEW privileges to our backup user. Hoping someone can help clarify if this is expected behavior or a potential bug.

Background

We have MySQL 5.7 with InnoDB Cluster configured. Our backup script has been running successfully for years using a dedicated backup user with SELECT, PROCESS, EVENT and LOCK TABLES privileges.

What happened :

Last Friday, we created a view and needed to grant SHOW VIEW privilege to our backup user for it to work. After adding this privilege, our backup started failing with:


mysqldump: Couldn't execute 'show fields from `async_cluster_members`':
SELECT command denied to user 'xxxxx'@'xxxxx' for table 'async_cluster_members' (1142)

Before granting SHOW VIEW, mysqldump was working fine and completely ignoring the mysql_innodb_cluster_metadata schema (which has existed since 2020). After adding SHOW VIEW, mysqldump suddenly started trying to access tables in this schema.


We're using: mysqldump flags : --all-databases --single-transaction --flush-privileges --hex-blob --triggers --routines --events --set-gtid-purged=AUTO


Evidence:
Previous backups contain NO CREATE DATABASE statement for mysql_innodb_cluster_metadata
Previous backups only show permission references to this schema in mysql.db table
After SHOW VIEW: mysqldump attempts to dump the actual cluster metadata tables


Is this expected behavior? The documentation for SHOW VIEW doesn't mention any impact on schema enumeration.
Does --all-databases combined with SHOW VIEW change how mysqldump discovers schemas?
Should SHOW VIEW affect which databases mysqldump attempts to process?


Thanks for any insights!

jdelgado]]> Jesús Delgado Ramírez Backup Tue, 19 Aug 2025 08:49:44 +0000 https://forums.mysql.com/read.php?28,725126,725126#msg-725126 https://forums.mysql.com/read.php?28,725126,725126#msg-725126 https://lefred.be/content/mysql-innodb-redo-log-archiving/

When I using oracle enterpise backup, I saw many binlogs been backuped. So, I think we could use mysqlbinlog to backup binlogs on schedule. Maybe there is not necessary to backup redo log archive files.

We could use binlogs to recover data into point in time, but how can we do with backuped redo log archive files ?]]> lei liu Backup Sun, 14 Jul 2024 13:12:07 +0000 https://forums.mysql.com/read.php?28,723167,723167#msg-723167 https://forums.mysql.com/read.php?28,723167,723167#msg-723167
I am trying to use mysqldump to backup the data in a MySQL docker container. I was following the docs here: https://dev.mysql.com/doc/refman/8.0/en/docker-mysql-more-topics.html#docker-mysqldump

I can't copy the instructions exactly because my docker container has its data in a volume (rather than a bind) mount. However I could not see why this would make any difference. I am running two docker containers using docker compose, with the following docker compose file (with irrelevant info removed):

```services:
mysql:
image: 'mysql:8.2.0'
environment:
MYSQL_DATABASE: 'myDb'
MYSQL_ROOT_PASSWORD: 'root'
ports:
- '3306'
networks:
- net1
volumes:
- dataVol:/var/lib/mysql:rw

mysql-backup:
image: 'mysql:8.2.0'
entrypoint: mysqldump -u root --password root --databases myDb --add-drop-database > /path/to/backup.sql
environment:
MYSQL_ROOT_PASSWORD: 'root'
networks:
- net1
ports:
- '3307'
volumes:
- dataVol:/var/lib/mysql:rw

volumes:
dataVol:
driver: local

networks:
net1:
driver: bridge
```

I first then ran (docker compose up) the mysql service (and it works correctly). But then when I run the backup job (docker compose run) I get the error:
`[ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11`

Upon a Google, this appears to occur when two MySQL instances are working with the same data directory which apparently we should never be doing. Eg the MySQL docs here suggest we shouldn't do this: https://dev.mysql.com/doc/refman/8.0/en/multiple-data-directories.html

So I am now confused because it seems that the MySQL docs are contradicting themselves... Or did I somehow do something different (by using a volume not a bind mount) that is causing my error?]]> Ben Lydiard Backup Thu, 07 Mar 2024 08:56:16 +0000 https://forums.mysql.com/read.php?28,722928,722928#msg-722928 https://forums.mysql.com/read.php?28,722928,722928#msg-722928 Tong Li Backup Wed, 21 Feb 2024 10:29:31 +0000 https://forums.mysql.com/read.php?28,710065,710065#msg-710065 https://forums.mysql.com/read.php?28,710065,710065#msg-710065 - https://blogs.oracle.com/mysql/post/how-to-copy-a-mysql-instance]]> Edwin Desouza Backup Thu, 28 Sep 2023 22:24:29 +0000 https://forums.mysql.com/read.php?28,710053,710053#msg-710053 https://forums.mysql.com/read.php?28,710053,710053#msg-710053
(how) is it possible to write a script for my local pc (ubuntu) which backups the databases of my extern website?

I couldn't find anything on the internet that would help me...

Thanks in advance!

Daniel]]> Daniel Sch Backup Thu, 26 Oct 2023 02:11:06 +0000 https://forums.mysql.com/read.php?28,709432,709432#msg-709432 https://forums.mysql.com/read.php?28,709432,709432#msg-709432
Command Used :

mysqldump -f -u root -p --no-tablespaces --protocol=socket -S /tmp/socketpath.sock --no-data --no-create-db --no-create-info --routines --triggers=false --single-transaction --quick --skip-lock-tables database_name > databasename_routines.sql


The number of routines is 4000+ , however whenever we try to take mysqldump of routines it is taking a humongous amount of time, and in the show processlist its doing "show table status like ......."]]> Karnish Master Backup Thu, 06 Jul 2023 13:05:52 +0000 https://forums.mysql.com/read.php?28,709234,709234#msg-709234 https://forums.mysql.com/read.php?28,709234,709234#msg-709234 First of all, excuse me. I am not a computer scientist and I do not speak English, I am using a translator.
My question is, I have a copy of a database directory, with the files ibdata1, ib_logfile, etc.
Can I open it and export it to Excel without needing to restore the copy? I have seen some software that does it but they are all paid.
Thank you so much]]> R a Backup Wed, 14 Jun 2023 11:47:38 +0000 https://forums.mysql.com/read.php?28,707454,707454#msg-707454 https://forums.mysql.com/read.php?28,707454,707454#msg-707454
I tried this command mysqldump -u username -p old_database | mysql -u username -p new_database mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: YES) when trying to connect

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) lakhendrakushwah@LAKHENDRAs-MacBook-Air ~ % sudo -s Password: root@LAKHENDRAs-MacBook-Air ~ # mysqldump -u root -p lakha | mysql -u root -p testDB Enter password: Enter password: mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: YES) when trying to connect

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) root@LAKHENDRAs-MacBook-Air ~ # mysqldump -u root -p lakha | mysql -u root -p testDB Enter password: Enter password: mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: YES) when trying to connect madhu@cool

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)]]> sesame technologies Backup Wed, 08 Feb 2023 12:25:50 +0000 https://forums.mysql.com/read.php?28,707148,707148#msg-707148 https://forums.mysql.com/read.php?28,707148,707148#msg-707148
We newly build the 3 node innodb cluster. we have configured the backup tool (Micro Focus - Data protector).

Mysql EE 8.0.31 with RHEL OS.

If we take the backup from root user, getting below error.

230105 12:34:04 MAIN ERROR: Cannot establish connection for user 'root'
to mysql server at eiacdb1prd:3306. Connection open fails with error
"Access denied for user 'root'@'eiacdb1prd' (using password: YES)"
230105 12:34:04 MAIN ERROR: Connection initialization error.

And I created separate user for backup and granted the needed privilege, if we use backupadmin user, getting below error,

[Critical] From: INET@eiacdb1prd "" Time: 1/5/2023 12:38:12 PM
[70:2] User "backupadmin" non-existent on this host => aborting



[Normal] From: BSM@xxxxxx.yy.zzz.ae "MySQL-EIAC-TEst" Time: 1/5/2023 12:38:47 PM
OB2BAR application on "eiacdb1prd" disconnected.



[Critical] From: BSM@xxxxxx.yy.zzz.ae "MySQL-EIAC-TEst" Time: 1/5/2023 12:38:47 PM
None of the Disk Agents completed successfully.
Session has failed.]]> Haashir Mohamed Backup Thu, 05 Jan 2023 08:50:27 +0000 https://forums.mysql.com/read.php?28,706586,706586#msg-706586 https://forums.mysql.com/read.php?28,706586,706586#msg-706586
I would like to get your suggestion to automate backup of MYSQL database.

Overview: I have 40 databases migrated from MSSQL to MYSQL. Total size of all databases is about 60 GB. (single database maximum size is 9 GB). Per year the growth rate is very low overall 20 GB for all databases.


Dev team require refresh database monthly from live to Development as well.
Which backup method is best for my application. Percona xtrabackup (OR) mysqldump? (OR) configure slave and do backup?]]> Raja MSSQL Backup Fri, 28 Oct 2022 04:05:33 +0000 https://forums.mysql.com/read.php?28,706258,706258#msg-706258 https://forums.mysql.com/read.php?28,706258,706258#msg-706258 Sagar Malpani Backup Tue, 20 Sep 2022 05:42:12 +0000 https://forums.mysql.com/read.php?28,706235,706235#msg-706235 https://forums.mysql.com/read.php?28,706235,706235#msg-706235 mysqldump --user=$user --password=$password --skip-lock-tables --single-transaction --quick $database > $copia

The problem occurs after restore a backup and with some views for example:

select * from view_ac LIMIT 10;

Error 1449 (HY000): The user specifed as definer ('root'@'%') does not exist.

To solve the problem I execute the following instructions:
update user set host = '%' where user = 'root' and host='localhost';
flush privileges;

But the problem is that instructions enable the remote connections and that is a problem for the the security of my database server.

I have to tell that I have to restore 20 databases and I have a script that automatizes that task, so that is difficult to fix the problem in manual way.]]> Alexander Ceballos Backup Fri, 16 Sep 2022 23:56:33 +0000 https://forums.mysql.com/read.php?28,706144,706144#msg-706144 https://forums.mysql.com/read.php?28,706144,706144#msg-706144 Md. Rafat Monjur Backup Wed, 07 Sep 2022 05:05:03 +0000 https://forums.mysql.com/read.php?28,705105,705105#msg-705105 https://forums.mysql.com/read.php?28,705105,705105#msg-705105 https://lefred.be/content/mysql-innodb-redo-log-archiving/]]> Edwin Desouza Backup Wed, 29 Jun 2022 14:48:32 +0000 https://forums.mysql.com/read.php?28,703465,703465#msg-703465 https://forums.mysql.com/read.php?28,703465,703465#msg-703465 No idea how the backup is handling views, but restore restores the views as tables, with data!
This is not how it should work.
A view is basically just a stored query, certainly not a table.

Since it’s screwed up, how can I backup everything except views? Then I could restore just that.

Easy enough to save just the view definitions separately, and then to restore, execute the view definitions after everything else.

Please help. I have 267 tables and 467 views.]]> Martie Henry Backup Fri, 17 Jun 2022 02:36:13 +0000 https://forums.mysql.com/read.php?28,703251,703251#msg-703251 https://forums.mysql.com/read.php?28,703251,703251#msg-703251 — video + slides: https://fosdem.org/2022/schedule/event/mysql_pitr_rockstar/]]> Edwin Desouza Backup Thu, 03 Mar 2022 22:05:44 +0000 https://forums.mysql.com/read.php?28,703013,703013#msg-703013 https://forums.mysql.com/read.php?28,703013,703013#msg-703013 We have one (community edition) MySQL innodb cluster, including one primary server and 2 slave servers. In the past one year, the system may hit IO issue about several times, and the IO issue cause innodb cluster down, sometimes 1 slave is down, while sometimes 2 slaves are down, or master is switched. The most serious situation we met is that, slave(s) could not recovered by restart mysql/group_replication process. To cover it, we spent several hours, that is stop the whole environment first, and then
1. Removing all binlogs from primary server
2. copy all left files from primary server to other slave servers.
3. Start previous primary server, and then run some sqls to let it run as primary again
4. start group_replication on slave nodes

In our environment, the total size of datafiles is more than 200G. So step #2 takes very long time, more than 1 hour, or 2 hours, it depends on one or two slave nodes need to recover.

In feture, we may setup another innodb cluster, lets say current one is env1, and the new one is called env2. We need to replicate these two environments. At first, we will copy the data from env2, and every one month, we need to sync the data from env1 to env2 again. The first direct solution is that we make a new full copy from env1, stop env2, remove all files from env2, and copy all data to env2, start env2. But it will take more than 2 hours everytime, is not a good solution.

We notice that MySQL enterprise edition supports incremental backup, and we wonder if it works with innodb cluster environment, and if yes, will it speed up the recover process on slave nodes, and the sync process between env1 and env2? Thanks]]> Liang Cheng Backup Tue, 05 Apr 2022 21:00:43 +0000 https://forums.mysql.com/read.php?28,702988,702988#msg-702988 https://forums.mysql.com/read.php?28,702988,702988#msg-702988 https://lefred.be/content/diskspace-problem-when-modifying-a-large-innodb-table-in-mysql-and-how-to-solve-it/]]> Edwin Desouza Backup Tue, 15 Feb 2022 01:16:08 +0000 https://forums.mysql.com/read.php?28,702701,702701#msg-702701 https://forums.mysql.com/read.php?28,702701,702701#msg-702701
When sbt library, let's say backup.so(to provide --sbt-lib-path for mysqlbackup) is built with openssl(openssl 1.0.2u), I see problems in backup.so during execution. Basically openssl calls are failing in backup.so. The same not seen when mysql community edition is used as database server. This happens in mysql commercial version 8.x. Is there a known issue or have some pre-condition in building the Other-MMS.so or dll?

problem seen with:
------------------
mysql-commercial-server-8.0.19-1.1.el7.x86_64
mysql-commercial-embedded-devel-8.0.19-1.1.el7.x86_64
mysql-commercial-common-8.0.19-1.1.el7.x86_64
mysql-commercial-test-8.0.19-1.1.el7.x86_64
mysql-commercial-client-8.0.19-1.1.el7.x86_64
mysql-commercial-libs-8.0.19-1.1.el7.x86_64
mysql-commercial-embedded-compat-8.0.19-1.1.el7.x86_64
mysql-commercial-devel-8.0.19-1.1.el7.x86_64
mysql-commercial-embedded-8.0.19-1.1.el7.x86_64
mysql-commercial-libs-compat-8.0.19-1.1.el7.x86_64


problem not seen:
-----------------
mysql-community-common-8.0.19-1.el8.x86_64
grafana-mysql-6.3.6-1.el8.x86_64
mysql-community-libs-8.0.19-1.el8.x86_64
pcp-pmda-mysql-5.0.2-5.el8.x86_64
mysql-commercial-backup-8.0.19-1.1.el6.x86_64
mysql-community-server-8.0.19-1.el8.x86_64
mysql-community-client-8.0.19-1.el8.x86_64


any information will be helpful in solving the problem.

Thanks,
Naga]]> Nagaraj K Backup Thu, 03 Feb 2022 18:42:09 +0000 https://forums.mysql.com/read.php?28,700365,700365#msg-700365 https://forums.mysql.com/read.php?28,700365,700365#msg-700365
I have two questions. First, in a typical installation of MySQL on a Windows server, where are the database files stored?

And second, if you had a Windows server with MySQL running on it, and the server crashed, and you were unable to log in to the server, would it be possible to retrieve the MySQL data file by transferring it from its original store location to a new server with MySQL installed on it?

Thank you.]]> It Manager Backup Thu, 23 Dec 2021 16:55:31 +0000 https://forums.mysql.com/read.php?28,700364,700364#msg-700364 https://forums.mysql.com/read.php?28,700364,700364#msg-700364
If you had a Windows server with MySQL running on it, and the server crashed, and you were unable to log in to the server, would it be possible to retrieve the MySQL data file by transferring it to a new server with MySQL installed on it?

Thank you.]]> It Manager Backup Thu, 23 Dec 2021 13:55:01 +0000 https://forums.mysql.com/read.php?28,698862,698862#msg-698862 https://forums.mysql.com/read.php?28,698862,698862#msg-698862
we are getting mysql backup error below. Currently we are using commvault bacup solution to take the backup

please find below error message:

Backup of [~pmqconsole~] MySQL database failed. The error message [~mysqldump: Couldn't execute 'FLUSH /*!40101 LOCAL */ TABLES': Lost connection to MySQL server during query(2013)~] is received during backup.


appreciate for experts suggestions.

Regards,
Aashique]]> Mohammad Aashraful aashique Backup Thu, 16 Sep 2021 15:22:28 +0000 https://forums.mysql.com/read.php?28,698827,698827#msg-698827 https://forums.mysql.com/read.php?28,698827,698827#msg-698827
I have a 88MB dump (typo3 website) that I want to load into my MySQL 5.7 database.

Unfortunately it fails with:
ERROR 2006 (HY000): MySQL server has gone away

I've tried a couple of things around the max_allowed_packet and wait_timeout, see my conf.d/custom.cnf below.

Does anyone know what else I could try now to finally load this dump?

Thanks much,

David



-

[mysqld]
skip-host-cache
skip-name-resolve
character-set-server=utf8
collation-server=utf8_unicode_ci
default_storage_engine=InnoDB
innodb_buffer_pool_instances=2
innodb_buffer_pool_size=3G
innodb_log_file_size=256M
innodb_log_buffer_size=4M
innodb_flush_log_at_trx_commit=2
innodb_thread_concurrency=8
key_buffer_size=16M
max_allowed_packet=128M
thread_stack=192K
thread_cache_size=32
query_cache_size=64M
table_open_cache=5000
table_definition_cache=800
sql_mode=NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
slow_query_log=1
slow_query_log_file=/var/log/mysql/mysql-slow.log
default_time_zone=Europe/Zurich
wait_timeout=300
interactive_timeout = 300

[mysql]
default-character-set=utf8
max_allowed_packet=128M

[client]
default-character-set=utf8
max_allowed_packet=128M

[mysqldump]
default-character-set=utf8
quick
quote-names
max_allowed_packet=128M
default_time_zone=Europe/Zurich

[mysqlimport]
default-character-set=utf8
default_time_zone=Europe/Zurich
max_allowed_packet=128M]]> David Ellenberger Backup Tue, 14 Sep 2021 18:29:29 +0000 https://forums.mysql.com/read.php?28,698669,698669#msg-698669 https://forums.mysql.com/read.php?28,698669,698669#msg-698669 I would like to know if the MySQL community edition supports point-in-time recovery option for DB restores. Currently mysqldump backups are configured but would like to explore the option of PITR if available in community edition

Thanks
Var]]> Var able Backup Tue, 07 Sep 2021 14:46:52 +0000 https://forums.mysql.com/read.php?28,698330,698330#msg-698330 https://forums.mysql.com/read.php?28,698330,698330#msg-698330 One of the tables involved has 70 million rows and takes up about 10GB. It has one primary key and four other columns (int,datetime,double,double) each one of which has a non-unique index.
I assume that it is the setting of these indexes after each insert that makes the restore so slow but I might be wrong. Maybe MySQLDump is the wrong approach altogether. Anyway this needs to be solved - ideally, the restore should take a few hours at most so I can automatize the backup/restore to run every day. Ideas anyone?]]> Michael Peters Backup Tue, 07 Sep 2021 15:15:17 +0000 https://forums.mysql.com/read.php?28,697998,697998#msg-697998 https://forums.mysql.com/read.php?28,697998,697998#msg-697998 — https://blogs.oracle.com/mysql/mysql-database-service%3a-snapshot-your-data-backups
— https://blogs.oracle.com/mysql/mysql-database-service%3a-snapshot-your-data-%e2%80%93-restore]]> Edwin Desouza Backup Thu, 05 Aug 2021 16:18:41 +0000 https://forums.mysql.com/read.php?28,697858,697858#msg-697858 https://forums.mysql.com/read.php?28,697858,697858#msg-697858 https://dev.to/nejremeslnici/mysql-shell-the-best-tool-for-your-logical-backups-44fk]]> Edwin Desouza Backup Thu, 29 Jul 2021 01:29:58 +0000 https://forums.mysql.com/read.php?28,697853,697853#msg-697853 https://forums.mysql.com/read.php?28,697853,697853#msg-697853
https://blogs.oracle.com/mysql/mysql-shell-dump-load-and-compression]]> Edwin Desouza Backup Wed, 28 Jul 2021 16:36:23 +0000