Import mysql dump into onto a machine using php

Question

I need to create an install.php file that lets my lecturer import a database dump (that I will provide) onto his machine.

The SQL dump will be called dump.sql, I have a config.php file containing credentials and the install.php that should be used to import the sql dump.

config.php

<?php
    //defining variables
    define('DB_HOST','localhost');
    define('DB_USER','root');
    define('DB_PASS','');
    define('DB_NAME','database'); 

    //connection to mysql server
    $conn=mysql_connect(DB_HOST, DB_USER, DB_PASS) or die ("Error connecting to server: ".mysql_error());
?>

install.php

<?php

    include_once ('config.php');

    echo "starting install...";

    //insert dbdump in the same folder as install.php (put them in a separate folder)
    $command = "C:\xampp\mysql\bin\mysql -u".DB_USER." -p".DB_PASS." < dump.sql";
    //echo $command;
    exec($command, $output, $return_var);

    $output = shell_exec($command);

    if ($output) {
    echo "database created";
    }

?>

I`m using the $command variable as I read this is a standard variable which replicates a cmd command but the database is still not being created.

I tested the same command "mysql -uroot < C:\xampp\htdocs\Recipes\dump.sql" and it works.

can anyone point what the issue may be?

Thanks

**Please, [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php).** They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). **Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement)** instead, and **use [PDO](http://us1.php.net/pdo).** — Jay Blanchard, Jan 15 '15 at 17:32
Shouldn't there be `-u USERNAME`? A space between `u` and `USERNAME` — u_mulder, Jan 15 '15 at 17:32
@JayBlanchard Not sure about this, for unix it's definitely should be `-pPASSWORD` otherwise there will be prompt to enter a password. — u_mulder, Jan 15 '15 at 17:36
Really? I just tested that from the command line on my Linux box and it failed @u_mulder — Jay Blanchard, Jan 15 '15 at 17:37
This is Microsoft not Linux, I tried adding a space but it still didn't work. and in cmd it's working well as well without the space — JoseP, Jan 15 '15 at 17:38
http://dev.mysql.com/doc/refman/5.6/en/mysql-command-options.html#option_mysql_password If you use the short option form (-p), you ***cannot*** have a space between the option and the password. — drew010, Jan 15 '15 at 17:39
I am not getting any errors, all that is displayed on the page is starting install... — JoseP, Jan 15 '15 at 17:40
@JoseP The issue is probably that the user you are using that isn't root doesn't have permissions to create databases. — drew010, Jan 15 '15 at 17:41
@drew010 I am still using the root which has permission to create a database. I am not receiving any errors in the error log, this is the last I got: 2015-01-15 18:03:30 5620 [Note] c:\xampp\mysql\bin\mysqld.exe: ready for connections. Version: '5.6.21' socket: '' port: 3306 MySQL Community Server (GPL) — JoseP, Jan 15 '15 at 17:46
You need to escape the \ in your command, try `$command = "C:\\xampp\\mysql\\bin\\mysql...` and see if that makes any difference. — drew010, Jan 15 '15 at 17:59
@JoseP How long did it take when you run it from the command line? Pretty quick? — drew010, Jan 15 '15 at 19:33

score 0 · Answer 1 · answered Aug 27 '19 at 10:57

1.Find mysql.exe in xampp folder:

E:\xampp\mysql\bin\mysql.exe

2.Open CMD in xampp folder

E:\xampp\mysql\bin\mysql.exe

3.In CMD use following things

mysql -h localhost -u root -p

4.Show Databases

show databases;

Example:

 MariaDB [(none)]> show databases;
+--------------------------+
| Database                 |
+--------------------------+
| information_schema       |
| mysql                    |
| performance_schema       |
| phpmyadmin               |
| test                     |
+--------------------------+
7 rows in set (0.101 sec)

MariaDB [(none)]>

5.Use Database

use databases;

Example

MariaDB [(none)]> use test
Database changed
MariaDB [test]>

6.Dump Database Then finally give source path for sql file

source E:\your\sql\file\path\mysqlfile.sql

Example

MariaDB [test]> >source E:\your\sql\file\path\mysqlfile.sql

Import mysql dump into onto a machine using php

1 Answers1