0

I have a production MySQL server and I want to copy everything to a development MySQL server (actually, I believe it is really MariaDB on the development side). Both servers are hosted (one Azure, the other another hosting service).

One challenge is that the database names are different (and cannot be fixed).

I finally found something that seemed like a good solution in this answer, which is basically to use this command:

mysqldump  --no-create-db old_db_name \
  --single-transaction --compress \
  --order-by-primary \
  --host old_db_host \
  -u old_db_user -pOld_db_password  |
mysql --host new_host -u new_user -pnewpassword \
  new_db_name

However, when I execute this, I get the following:

WARNING: --compress is deprecated and will be removed in a future version. Use --compression-algorithms instead. Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.

ERROR 1227 (42000) at line 18: Access denied; you need (at least one of) the SUPER, BINLOG ADMIN privilege(s) for this operation

mysqldump: Got errno 0 on write

I'm specifically concerned about the

Access denied; you need (at least one of) the SUPER, BINLOG ADMIN privilege(s) for this operation

I found this question which made me think the problem has to do with DEFINER (not sure what this is). However, when looking for answers, I found answers like the ones here. However, these seem to only work when dumping the data to a file and I'm trying to transfer directly to the other server.

Is there a way to fix this? Or perhaps I'm really going about this the wrong way.

Rick James
  • 80,479
  • 5
  • 52
  • 119
John
  • 101

3 Answers3

0

I would recommend doing the backup in the existing server then copy into the new server, if storage is not available I prefer using the mysqldump command directly from the new server.

I have tested with the following server

  1. Old server(prodbm:~ #) 192.168.10.10
  2. New server(astdbtemplate:~ #) 192.168.10.11

Run

astdbtemplate:~ # sudo /usr/bin/mysqldump -h 192.168.10.10 -u username -p'YourPassword' --hex-blob --single-transaction --routines asterisk > asterisk.sql

I have dumped asterisk schema including procedures and triggers(which are dumped by default) and not including the CREATE DATABASE .. and USE DATABASE .. into the asterisk.sql file.

Import into a different database name

astdbtemplate:~ # mysql -uroot yourNewDatabaseName < asterisk.sql

Note I have created a user on the old server and gave all permissions before running the dump.

mysqldump — A Database Backup Program

mysqldump requires at least the SELECT privilege for dumped tables, SHOW VIEW for dumped views, TRIGGER for dumped triggers, LOCK TABLES if the --single-transaction option is not used, PROCESS if the --no-tablespaces option is not used, and the RELOAD or FLUSH_TABLES privilege with --single-transaction if both gtid_mode=ON and gtid_purged=ON|AUTO. Certain options might require other privileges as noted in the option descriptions.

Ergest Basha
  • 5,369
  • 3
  • 7
  • 22
0
mysqldump  --no-create-db  \
  --single-transaction  \
  --order-by-primary \
  --host old_db_host \
  -u old_db_user -pOld_db_password \
  old_db_name  |
mysql --host new_host -u new_user -pnewpassword \
  new_db_name

Notes:

  • Put the old_db_name after all options
  • compress is probably a waste since the data is flowing through a pipe; the compressing and uncompressing would chew up a lot of CPU.
  • The other answers may provide other necessary tips. (permissions/grants; routines; etc)
Rick James
  • 80,479
  • 5
  • 52
  • 119
0

After excessive trial-and-error, I finally figured what I need to do. I'm documenting it here in case anyone needs an answer in the future.

I am exporting from MySQL 8.x on Azure and importing on MariaDB on Hostinger. I stopped trying to do a single-line command to export and import at once. Now, I export to a third machine, then upload that .sql file via phpMyAdmin. I don't think the single-line command will work without a lot of extra steps because the .sql data needs to be edited between export and import.

You can export from MySQL Workbench (I'm sure the command line works fine, as you will see the workarounds). Make sure you do not export triggers.

Then, I needed to remove from the .sql file any line like

  • SET @@SESSION.SQL_LOG_BIN= 0;
  • SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;

Remove any line with GLOBAL.GTID_PURGED in it

Replace any utf8mb4_0900_ai_ci with utf8mb4_unicode_520_ci

Remove any line which includes DEFINER

After these changes, I could import successfully.

John
  • 101