1

On my local PC (Windows, WAMPserver) my dump script (PHP) works fine:

$createBackup = '"' . $dumpLoc . '" --host='.$dumpHost.' --user='.$myName.' --password="'.$myPass.'" '. $dbName.' '.$table_name.' >> '.$sqlFile;
system($createBackup);

But on the production server (also Windows, with PHP/MySQL) it does not dump the actual database, just the parameters at the beginning, like:

-- MySQL dump 10.13  Distrib 5.7.19, for Win64 (x86_64)
--
-- Host: *****    Database: ********
-- ------------------------------------------------------
-- Server version   5.7.23-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

... without the required

DROP TABLE IF EXISTS `logins`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `logins` ( *** ), etc.

I don't believe it's a memory issue (we're only talking 280 rows and not a lot of data in each).

The .sql file is created properly, it just doesn't contain the data, whereas on the local machine, all works well.

I'm actually dumping from 2 different databases and 3 or 4 tables from each. But even when I restrict it to one (the troublesome one) db and one table, no luck.

The other db is dumping fine on the production server.

MBourne
  • 147
  • 2
  • 9
  • 1
    Do you have the correct user permissions setup? – Mark Feb 24 '20 at 09:48
  • The db that is dumping successfully has one extra wildcard entry in privileges: User name: (same as % and localhost), Host name: (the server's ip) Type: "wildcard:working_db_name; Privileges: ALL; Grant: No. Would this be the issue? – MBourne Feb 25 '20 at 10:28
  • Just a note, you should be using [`escapeshellargs()`](https://www.php.net/manual/en/function.escapeshellarg.php) on variables passed into the command line. This will properly quote and escape values. – miken32 Mar 02 '20 at 19:43

1 Answers1

0

Mark's question was the issue.

I tried to create a new user with that wildcard entry but could not. Turned out it was due to the ERROR 1396 (HY000): Operation CREATE USER failed for ... error.

As per why can't create a new user in mysql?, I deleted the user (even though they didn't exist yet), FLUSHed PRIVILEGES, then I could add the user and give the correct permissions.

Now the dump works as required.

MBourne
  • 147
  • 2
  • 9