Mysql – Restoring a database backup to a local machine in MySQL

backupMySQLrestore

I have downloaded my .sql file of my database server. It is in the D: drive of my Windows machine.

I want to restore the back up in my this machine. I used:

mysql database -u root < backupfile.sql

Where database is my new database name in this machine. My confusion is that my backupfile.sql is in the D: drive and thus I guess there is error.

ERROR 1064 <42000>:You have an error in your SQL syntax;Check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysql database -u root < backupfile.sql' at line 1.

Best Answer

You should login to mysql like this

C:\> mysql -uroot -p <hit enter>
Enter password:

Next, select the database you want to load the data into

mysql> CREATE DATABASE IF NOT EXISTS mynewdb;
mysql> USE mynewdb

Then, run the script

mysql> source D:\backup\backup.sql

Give it a Try !!!

Related Solutions

Mysql – Why can’t I view the plan for view creation…

EXPLAIN shows an execution plan for SELECT queries. In version 5.6, it was extended to include plans for INSERT, UPDATE, DELETE and REPLACE statements.

No version of MySQL (not even the still in development, 5.7) has EXPLAIN for CREATE TABLE or CREATE VIEW.

You can of course run the explain for the select (that defines the view):

EXPLAIN EXTENDED
    select intercom1,phone from fisgen where fid='f105';

or as @jynus suggested (thank you), the more useful:

EXPLAIN EXTENDED
    select * from fisgen_temptable;

MySQL – How to Solve ERROR 1064 (42000)

(sequel CHAR)
CONCAT(@sequel)

@variables are totally independent of non-@ variables.

Two fixes needed:

SET @SQL = CONCAT(@sequel); --> SET @SQL = sequel;
(sequel CHAR) --> (sequel VARCHAR(999))

Best Answer

Related Solutions

Mysql – Why can’t I view the plan for view creation…

MySQL – How to Solve ERROR 1064 (42000)

Related Question