英文:
Restoring to mariadb10.1 fails the first time, but succeeds when restoring again after recreating the database
问题
我正在从MariaDB 5.5升级到10.1版本。
我试图将从5.5版本生成的转储文件还原到10.1版本，但遇到了一个现象，即第一次还原总是失败的，只有在删除并重新创建数据库后才能成功还原。
错误内容为：
>ERROR 1449 (HY000) at line 44811: The user specified as a definer ('batch'@'localhost') does not exist
但是 'batch'@'localhost' 确实存在，并且在不添加该用户的情况下，使用相同的转储文件进行第二次还原成功。
我实际使用的命令是：
// 在MariaDB 5.5中
$ mysqldump -u root -p -x -h localhost -R --opt --quick 数据库名称 | gzip > /root/backup_db/数据库名称.sql.gz
$ mysqldump -u root -p -x -h localhost --allow-keywords mysql | gzip > /root/backup_db/mysql.sql.gz
// 安装MariaDB 10.1后
$ mysql_secure_installation
$ mysql_upgrade -u root -p
$ mysql -u root -p -h localhost
# 创建数据库 数据库名称;
# \q
// 解压缩压缩的转储文件后
$ mysql -u root -p -h localhost mysql < mysql.sql
$ mysql -u root -p -h localhost 数据库名称< 数据库名称.sql // 总是在这里失败
$ mysql -u root -p -h localhost
# 删除数据库 数据库名称;
# 创建数据库 数据库名称;
# \q
$ mysql -u root -p -h localhost 数据库名称< 数据库名称.sql // 在这里成功
如果有可能的原因或需要检查的事项，请告诉我。
谢谢。
考虑到可能 net_buffer_length 不足，
>net_buffer_length=1024000
我尝试将其更改为，但仍然失败。
那时的 max_allowed_packet 为
>max_allowed_packet=100MB
英文:
I am upgrading from mariadb5.5 to 10.1.
I'm trying to restore the dump file output from 5.5 to 10.1, but I'm suffering from the phenomenon that the first restore always fails, and the restore succeeds after deleting and recreating the database.
The content of the error is
>ERROR 1449 (HY000) at line 44811: The user specified as a definer ('batch'@'localhost') does not exist
But 'batch'@'localhost' does exist and indeed a second restore succeeds with the same dump file without adding the user.
The commands I'm actually using are:
// in mariadb5.5
$ mysqldump -u root -p -x -h localhost -R --opt --quick databasename | gzip > /root/backup_db/databasename.sql.gz
$ mysqldump -u root -p -x -h localhost --allow-keywords mysql | gzip > /root/backup_db/mysql.sql.gz
// After installing mariadb10.1
$ mysql_secure_installation
$ mysql_upgrade -u root -p
$ mysql -u root -p -h localhost
# create database databasename;
# \q
// After decompressing the compressed dump file
$ mysql -u root -p -h localhost mysql < mysql.sql
$ mysql -u root -p -h localhost databasename< databasename.sql // always fails here
$ mysql -u root -p -h localhost
# drop database databasename;
# create database databasename;
# \q
$ mysql -u root -p -h localhost databasename< databasename.sql // success here
Please let me know if there are any possible causes or things to check.
Thank you.
Thinking that net_buffer_length is insufficient,
>net_buffer_length=1024000
I tried to restore by changing to , but it failed.
Max_allowed_packet at that time was
>max_allowed_packet=100MB.
答案1
得分: 1
当恢复单独获取的 mysql 数据库转储时（与 --all-databases 不同，后者需要特殊处理），需要执行 FLUSH PRIVILEGES 以将该转储恢复到数据库的运行配置中。
在10.4+ 版本中，mysql_upgrade 现在称为 mariadb-upgrade，会修改 mysql.* 表以对应于 mariadb 版本的新特性，并执行 FLUSH PRIVILEGES。
因此，在恢复到新的主要版本时，先恢复 mysql 数据库，然后运行 mysql_upgrade。如果是恢复到相同版本，则在恢复后执行 FLUSH PRIVILEGES 就足够了。
英文:
When restoring a dump of a mysql database taken separately (as opposed to --all-databases which does special handling), a FLUSH PRIVILEGES is needed to restore that dump to the running configuration of the database.
The mysql_upgrade, now called mariadb-upgrade in 10.4+, alters the mysql.* tables to correspond to the new features of the mariadb version and issues a FLUSH PRIVILEGES.
So when restoring to a new major version, run mysql_upgrade after restoring the mysql database. If restoring to the same version, a FLUSH PRIVILEGES after restoration is sufficient.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论