MySQL/migration/CLI/Import: Difference between revisions
Created page with "{{fmt/title|importing data into MySQL via command-line interface}} If you are restoring to a different database name (as might happen if you were moving the db to a new serve..." |
No edit summary |
||
| Line 1: | Line 1: | ||
{{fmt/title|importing data into MySQL via command-line interface}} | {{fmt/title|importing data into MySQL via command-line interface}} | ||
These instructions assume you have a <code>.sql</code> file created by [[mysqldump]], or something compatible with it. | |||
If you are restoring to a different database name (as might happen if you were moving the db to a new server), you will need to edit the .sql file to use the appropriate database name in the line which begins "CREATE DATABASE" and the "USE" line following it. If you know the database has already been created, you can just delete the CREATE DATABASE line. | '''Caveat''': If you are restoring to a different database name (as might happen if you were moving the db to a new server), you will need to edit the <code>.sql</code> file to use the appropriate database name in the line which begins "<code>CREATE DATABASE</code>" and the "<code>USE</code>" line following it. If you know the database has already been created, you can just delete the <code>CREATE DATABASE</code> line. | ||
You might also want to verify that the mysql utility connects successfully before you try the import: | You might also want to verify that the mysql utility connects successfully before you try the import: | ||
mysql -h{{fmt/arg|server}} -u{{fmt/arg|username}} -p{{fmt/arg|password}} | mysql -h{{fmt/arg|server}} -u{{fmt/arg|username}} -p{{fmt/arg|password}} | ||
If that works, then the full command to create a database from | If that works, then the full command to create a database from the <code>.sql</code> file is: | ||
mysql -h{{fmt/arg|server}} -u{{fmt/arg|username}} -p{{fmt/arg|password}} < {{fmt/arg|path/to/backup/file.sql}} | mysql -h{{fmt/arg|server}} -u{{fmt/arg|username}} -p{{fmt/arg|password}} < {{fmt/arg|path/to/backup/file.sql}} | ||
Note that the path is relative to the system on which you are running the mysql utility, ''not'' the system running the mysql server (mysqld). Note also that this technique doesn't tell you anything until it finishes processing the .sql file. | Note that the path is relative to the system on which you are running the mysql utility, ''not'' the system running the mysql server (mysqld). Note also that this technique doesn't tell you anything until it finishes processing the <code>.sql</code> file. | ||
You can also specify the database in which the SQL commands should be run: | |||
You can also explicitly specify the database in which the [[SQL]] commands should be run, presumably (I haven't tested this yet) overriding the <code>USE</code> line in the file: | |||
mysql -h{{fmt/arg|server}} -u{{fmt/arg|username}} -p{{fmt/arg|password}} -D{{fmt/arg|dbname}} < {{fmt/arg|path/to/backup/file.sql}} | mysql -h{{fmt/arg|server}} -u{{fmt/arg|username}} -p{{fmt/arg|password}} -D{{fmt/arg|dbname}} < {{fmt/arg|path/to/backup/file.sql}} | ||
==Links== | ==Links== | ||
===Official=== | ===Official=== | ||
* v8.4: [https://dev.mysql.com/doc/refman/8.4/en/recovery-from-backups.html 9.3.2 Using Backups for Recovery] | * v8.4: [https://dev.mysql.com/doc/refman/8.4/en/recovery-from-backups.html 9.3.2 Using Backups for Recovery] | ||
Latest revision as of 02:00, 25 February 2026
Template:Fmt/title
These instructions assume you have a .sql file created by mysqldump, or something compatible with it.
Caveat: If you are restoring to a different database name (as might happen if you were moving the db to a new server), you will need to edit the .sql file to use the appropriate database name in the line which begins "CREATE DATABASE" and the "USE" line following it. If you know the database has already been created, you can just delete the CREATE DATABASE line.
You might also want to verify that the mysql utility connects successfully before you try the import:
mysql -hTemplate:Fmt/arg -uTemplate:Fmt/arg -pTemplate:Fmt/arg
If that works, then the full command to create a database from the .sql file is:
mysql -hTemplate:Fmt/arg -uTemplate:Fmt/arg -pTemplate:Fmt/arg < Template:Fmt/arg
Note that the path is relative to the system on which you are running the mysql utility, not the system running the mysql server (mysqld). Note also that this technique doesn't tell you anything until it finishes processing the .sql file.
You can also explicitly specify the database in which the SQL commands should be run, presumably (I haven't tested this yet) overriding the USE line in the file:
mysql -hTemplate:Fmt/arg -uTemplate:Fmt/arg -pTemplate:Fmt/arg -DTemplate:Fmt/arg < Template:Fmt/arg
