1

I have a folder containing the snapshot taken from a database.

They are all in the format organisation_product_tablename.sql.

How can I restore them back into the database?

EDIT 1:

I managed to restore all the files, but one with:

ls -1 *.sql | awk '{ print "source",$0 }' | mysql --batch -u root -p databaseName

The only one that is giving problems fails with an this error:

ERROR 1449 (HY000) at line 346 in file: 'org_sugar_routines.sql': The user specified as a definer ('root'@'%') does not exist.

This is the Head of the file:

USE `organisation_product`;

-- MySQL dump 10.13  Distrib 5.6.13, for Win32 (x86)
--
-- Host: 10.10.0.10    Database: organisation_product
-- ------------------------------------------------------
-- Server version     5.1.60-community

/*!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 */;

--
-- Temporary table structure for view `vw_contact_insurance`
--

When I checked that line in the file I found that in the file marked as error there is this:

/*!50013 DEFINER=`root`@`%` SQL SECURITY DEFINER */

When I run this query

mysql> SELECT USER(),CURRENT_USER();

I obtain this:

+----------------+----------------+
| USER()         | CURRENT_USER() |
+----------------+----------------+
| root@localhost | root@localhost |
+----------------+----------------+
1 row in set (0.02 sec)

Isn't this the root user? I have it as the admin user in the DB. So, what's the problem here?

  • Is this a binary file or a text file ? – RolandoMySQLDBA Jun 18 '14 at 13:41
  • Multiple text files – MaPi Jun 18 '14 at 13:48
  • I need to see the first 22 lines one of the files. Please run head -22 organisation_product_tablename.sql and post the output. – RolandoMySQLDBA Jun 18 '14 at 13:49
  • BTW please run this two queries for me: SELECT USER(),CURRENT_USER(); and SELECT CONCAT(QUOTE(user),'@',QUOTE(host)) FROM mysql.user WHERE user='root'; and show the output. – RolandoMySQLDBA Jun 18 '14 at 14:05
1

I see you restored the tables. Your issue seems to be the owner of either a view or a stored procedure

You need to replace all occurrences of

`root`@`%`

in the file with

`root`@`localhost`

I would not recommend creating the user root@'%' as that would allow root to have remote access.

Since root@'%' is the owner of the routines, I will create root@'%' like root@localhost

Please run the following:

CREATE TABLE mysql.user_root LIKE mysql.user;
INSERT INTO mysql.user_root
SELECT * FROM mysql.user WHERE user='root' and host='localhost';
UPDATE mysql.user_root SET host='%';
REPLACE INTO mysql.user SELECT * FROM mysql.user_root;
DROP TABLE mysql.user_root;
FLUSH PRIVILEGES;
| improve this answer | |
  • Thanks Rolando. What does % mean in this context? – MaPi Jun 18 '14 at 14:07
  • The % is a wildcard character. The user root@'%' means that root can connect to MySQL from anywhere remotely (provided 3306 is open through the firewall). The user root@localhost means root can only connect via the socket file on the local box. If you have the user root@'10.80.30.%', then root can only connect from that netblock. To see all defined users and hosts run SELECT CONCAT(QUOTE(user),'@',QUOTE(host)) FROM mysql.user; – RolandoMySQLDBA Jun 18 '14 at 14:13
  • Keep in mind that root@'%' and root@localhost are two separate users. They can have separate passwords if desired. – RolandoMySQLDBA Jun 18 '14 at 14:21
  • I need to create the root@'%', since I will have to reimport the tables on the original environments. How do I create it? – MaPi Jun 18 '14 at 14:22
  • I'll add that to my answer, give me a couple of minutes ... – RolandoMySQLDBA Jun 18 '14 at 14:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.