问题描述
我正在尝试将生产mysql db导入本地xampp测试环境.通过连接到Web admin(mozff)并简单地导出sql(不需要其他任何东西),然后转到本地phpmyadmin仪表板并导入.它抛出以下错误
I am trying to import the production mysql db into a local xampp test environment. By connecting to web admin(mozff) and simple exporting sql(never needed anything else).And then go to local phpmyadmin dashboard and import. It is throwing the following errors
错误:
实时环境:
数据库服务器
Server: db01.l2.url.com via TCP/IP
Server-Typ: MariaDB
Server-Version: 10.1.26-MariaDB-0+deb9u1 - Debian 9.1
Protokoll-Version: 10
User: user
Server-encoding: UTF-8 Unicode (utf8)
网络服务器
Apache
Datenbank-Client Version: libmysql - mysqlnd 5.0.12-dev - 20150407 - $Id: b5c5906d452ec590732a93b051f3827e02749b83 $
PHP-Erweiterung: mysqliDokumentation curlDokumentation mbstringDokumentation
PHP-Version: 7.0.27-0+deb9u1
phpMyAdmin
phpMyAdmin
Version informationen: 4.6.6deb4
测试环境
数据库服务器
Server: 127.0.0.1 via TCP/IP
Server-Typ: MariaDB
Server-connection: SSL not used
Server-Version: 10.1.33-MariaDB - mariadb.org binary distribution
Protokoll-Version: 10
user: root@localhost
Server-encoding: UTF-8 Unicode (utf8)
网络服务器
Apache/2.4.33 (Win32) OpenSSL/1.1.0h PHP/7.2.6
Datenbank-Client Version: libmysql - mysqlnd 5.0.12-dev - 20150407 - $Id: 38fea24f2847fa7519001be390c98ae0acafe387 $
PHP-extension: mysqliDokumentation curlDokumentation mbstringDokumentation
PHP-Version: 7.2.6
phpMyAdmin
phpMyAdmin
Version informationen: 4.8.1, aktuelle stabile Version: 4.8.2
问题:
- 我在哪里看到什么参数不正确?
- 我假设mysql 4.8.1 db向后兼容4.6.6deb4.. ??
- Where do I see what parameter is incorrect?
- I am assuming mysql 4.8.1 db is backwards compatible with 4.6.6deb4..??
我尝试过的事情:
日志:
180709 11:52:25 246 Connect pma@localhost as anonymous on
247 Connect root@localhost as anonymous on
247 Query SELECT @@version, @@version_comment
247 Query SET NAMES 'utf8mb4' COLLATE 'utf8mb4_general_ci'
247 Query SET lc_messages = 'de_DE'
246 Query SELECT `config_data`, UNIX_TIMESTAMP(`timevalue`) ts FROM `phpmyadmin`.`pma__userconfig` WHERE `username` = 'root'
246 Query SELECT `config_data`, UNIX_TIMESTAMP(`timevalue`) ts FROM `phpmyadmin`.`pma__userconfig` WHERE `username` = 'root'
247 Query SET collation_connection = 'utf8mb4_unicode_ci'
247 Init DB mysql
247 Query SHOW SESSION VARIABLES LIKE 'FOREIGN_KEY_CHECKS'
180709 11:52:26 247 Quit
246 Quit
180709 11:52:33 248 Connect pma@localhost as anonymous on
249 Connect root@localhost as anonymous on
249 Query SELECT @@version, @@version_comment
249 Query SET NAMES 'utf8mb4' COLLATE 'utf8mb4_general_ci'
249 Query SET lc_messages = 'de_DE'
248 Query SELECT `config_data`, UNIX_TIMESTAMP(`timevalue`) ts FROM `phpmyadmin`.`pma__userconfig` WHERE `username` = 'root'
249 Query SET collation_connection = 'utf8mb4_unicode_ci'
249 Query SELECT `SCHEMA_NAME` FROM `INFORMATION_SCHEMA`.`SCHEMATA`, (SELECT DB_first_level FROM ( SELECT DISTINCT SUBSTRING_INDEX(SCHEMA_NAME, '_', 1) DB_first_level FROM INFORMATION_SCHEMA.SCHEMATA WHERE TRUE ) t ORDER BY DB_first_level ASC LIMIT 0, 100) t2 WHERE TRUE AND 1 = LOCATE(CONCAT(DB_first_level, '_'), CONCAT(SCHEMA_NAME, '_')) ORDER BY SCHEMA_NAME ASC
248 Query SELECT `db_name`, COUNT(*) AS `count` FROM `phpmyadmin`.`pma__navigationhiding` WHERE `username`='root' GROUP BY `db_name`
249 Query SELECT COUNT(*) FROM ( SELECT DISTINCT SUBSTRING_INDEX(SCHEMA_NAME, '_', 1) DB_first_level FROM INFORMATION_SCHEMA.SCHEMATA WHERE TRUE ) t
249 Query SELECT COUNT(*) FROM ( SELECT DISTINCT SUBSTRING_INDEX(SCHEMA_NAME, '_', 1) DB_first_level FROM INFORMATION_SCHEMA.SCHEMATA WHERE TRUE ) t
248 Query SELECT * FROM `phpmyadmin`.`pma__bookmark` WHERE ( `user` = '' OR `user` = 'root' ) ORDER BY label ASC
249 Quit
248 Quit
180709 11:52:51 250 Connect pma@localhost as anonymous on
251 Connect root@localhost as anonymous on
251 Query SELECT @@version, @@version_comment
251 Query SET NAMES 'utf8mb4' COLLATE 'utf8mb4_general_ci'
251 Query SET lc_messages = 'de_DE'
250 Query SELECT `config_data`, UNIX_TIMESTAMP(`timevalue`) ts FROM `phpmyadmin`.`pma__userconfig` WHERE `username` = 'root'
251 Query SET collation_connection = 'utf8mb4_unicode_ci'
180709 11:52:52 251 Quit
250 Quit
180709 11:52:53 252 Connect pma@localhost as anonymous on
253 Connect root@localhost as anonymous on
253 Query SELECT @@version, @@version_comment
253 Query SET NAMES 'utf8mb4' COLLATE 'utf8mb4_general_ci'
253 Query SET lc_messages = 'de_DE'
252 Query SELECT `config_data`, UNIX_TIMESTAMP(`timevalue`) ts FROM `phpmyadmin`.`pma__userconfig` WHERE `username` = 'root'
253 Query SET collation_connection = 'utf8mb4_unicode_ci'
253 Query SELECT `SCHEMA_NAME` FROM `INFORMATION_SCHEMA`.`SCHEMATA`, (SELECT DB_first_level FROM ( SELECT DISTINCT SUBSTRING_INDEX(SCHEMA_NAME, '_', 1) DB_first_level FROM INFORMATION_SCHEMA.SCHEMATA WHERE TRUE ) t ORDER BY DB_first_level ASC LIMIT 0, 100) t2 WHERE TRUE AND 1 = LOCATE(CONCAT(DB_first_level, '_'), CONCAT(SCHEMA_NAME, '_')) ORDER BY SCHEMA_NAME ASC
252 Query SELECT `db_name`, COUNT(*) AS `count` FROM `phpmyadmin`.`pma__navigationhiding` WHERE `username`='root' GROUP BY `db_name`
253 Query SELECT COUNT(*) FROM ( SELECT DISTINCT SUBSTRING_INDEX(SCHEMA_NAME, '_', 1) DB_first_level FROM INFORMATION_SCHEMA.SCHEMATA WHERE TRUE ) t
253 Query SELECT COUNT(*) FROM ( SELECT DISTINCT SUBSTRING_INDEX(SCHEMA_NAME, '_', 1) DB_first_level FROM INFORMATION_SCHEMA.SCHEMATA WHERE TRUE ) t
180709 11:52:54 253 Quit
252 Quit
254 Connect pma@localhost as anonymous on
255 Connect root@localhost as anonymous on
255 Query SELECT @@version, @@version_comment
255 Query SET NAMES 'utf8mb4' COLLATE 'utf8mb4_general_ci'
255 Query SET lc_messages = 'de_DE'
254 Query SELECT `config_data`, UNIX_TIMESTAMP(`timevalue`) ts FROM `phpmyadmin`.`pma__userconfig` WHERE `username` = 'root'
255 Query SET collation_connection = 'utf8mb4_unicode_ci'
254 Quit
256 Connect pma@localhost as anonymous on
257 Connect root@localhost as anonymous on
257 Query SELECT @@version, @@version_comment
257 Query SET NAMES 'utf8mb4' COLLATE 'utf8mb4_general_ci'
257 Query SET lc_messages = 'de_DE'
256 Query SELECT `config_data`, UNIX_TIMESTAMP(`timevalue`) ts FROM `phpmyadmin`.`pma__userconfig` WHERE `username` = 'root'
257 Query SET collation_connection = 'utf8mb4_unicode_ci'
256 Query SELECT `config_data`, UNIX_TIMESTAMP(`timevalue`) ts FROM `phpmyadmin`.`pma__userconfig` WHERE `username` = 'root'
256 Query SELECT `username` FROM `phpmyadmin`.`pma__userconfig` WHERE `username` = 'root'
256 Query UPDATE `phpmyadmin`.`pma__userconfig` SET `timevalue` = NOW(), `config_data` = '{\"lang\":\"de\",\"Console\\/Mode\":\"collapse\"}' WHERE `username` = 'root'
180709 11:52:58 257退出
256退出
180709 11:52:58 257 Quit
256 Quit
推荐答案
我遇到了同样的问题,并尝试了很多事情,尤其是增加了php.ini
中的时间限制,但没有任何实际效果.最后,我完成了通过命令行导入sql文件的工作:
I was facing the same issue and tried many things especially increasing the time limits in php.ini
but nothing really worked. Finally I did the job my importing the sql file via command line:
mysql -u username -p databasename < /path/to/file.sql
在大多数情况下,用户名仅为root
.如果旧数据库和新数据库备份文件中都有相同的表,则可能会收到如下错误:
In most cases the username would be just root
. If you have same tables in both the old database and new database backup file, then you may get an error like this:
ERROR 1050 (42S01) at line 31: Table 'wp_commentmeta' already exists
在这种情况下,请删除(删除)现有数据库,然后通过上述命令导入新文件.
In that case, drop (delete) existing database and then import the new file via the command stated above.
这篇关于导入数据库phpMyAdmin-格式参数错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!