First time here in DBA. I'm working to create a simple and strong database backup plan. My solution that is already working to create a gzipped, encrypted backup is:
$string = "mysqldump -u$db_user -p$db_password $database $table | gzip | openssl enc -aes-256-cbc -salt -pass pass:$ePass > " . $path . $table . '_' . $d . "_en.sql.gz";
$result = shell_exec ( $string );
I am trying to test the dump and my programming (head to toe) by importing it into another another table. The table User_test_import
below doesn't exist in the operational and live site.
The dump is creating as an example (after modification to test import):
-- MySQL dump 10.11
--
-- Host: localhost Database: accoun3_flifo
<omitted>
--
-- Table structure for table `User`
--
DROP TABLE IF EXISTS `User_test_import`;
CREATE TABLE `User_test_import` (
<omitted>
);
--
-- Dumping data for table `User`
--
<Line in question>
LOCK TABLES `User_test_import` WRITE; /* Line 59 */
/*!40000 ALTER TABLE `User` DISABLE KEYS */;
INSERT INTO `User_test_import` VALUES ( ... <omitted> ...
);
The third line up had to be 'commented' in order for the import to work using:
$import = 'User_5.sql.gz';
$string = "gunzip < $path$import 2>&1 | mysql -u$db_user -p$db_password $database 2>&1";
From the command line the above reports ERROR 1100 (HY000) at line 60: Table 'User' was not locked with LOCK TABLES
User is an existing table. Line 59 is the
The question then is, how do I use the dump with it's LOCK TABLES statement? Thanks.
Edit Question:
Why is TABLE User was not locked with LOCK TABLES
occurring? How can it be prevented?
LOCK TABLE User failed
... shouldn't it be sayingLOCK TABLE User_test_importfailed
? Might the dump be trying to do something to theUser
table? – Andomar Feb 16 at 19:47User_test_import failed
It is actuallyERROR 1100 (HY000) at line 60: Table 'User' was not locked with LOCK TABLES
I don't understand why the User table must be locked. ?? – David Feb 16 at 21:14