Importing large database (2GB). phpMyAdmin always fails, and via SSH I get credential errors...

InTheWoods

InTheWoods

Verified User
Joined
Dec 31, 2020
Messages
45
Location
Internet
The database has been created in DirectAdmin as a user.

Database name: username_database
Username: username_database
Password: example123

(The above info is just an example)

Via SSH, as ROOT, I run:

Code: 
mysql -u username_database -p username_database < /home/username/staging/mydatabase.sql

When prompted for a password, I of course enter: example123

To which I am presented:

Code: 
ERROR 1044 (42000) at line 18: Access denied for user 'username_database'@'localhost' to database 'mydatabase'

What's the deal here? Why does this fail? Is there something specific about DirectAdmin that would make this not work? Why? I've tried using the password for the DA login for "username" as well as the password set to the specific database, and it fails regardless.

I'm trying once again via phpMyAdmin now, logged in as the da_admin user because logging into phpmyadmin as "username" fails after a while, with weird permission issues even though my PHP upload limit and timeout settings should be more than enough to accommodate importing this database and the credentials are correct, obviously, as I'm signed in via SSO from the panel.
 
ericosman said:
If you go to server.com/phpmyadmin
Can you login with the details?
Click to expand...

I'll try shortly and will let you know. Right now I'm signed into server.com/phpmyadmin as da_admin and am trying to import the data to the "username_database" database right now.

I don't want to interupt this process as I'm already well over my allotted timeframe for maintenance on this and am pulling my hair out, haha.
 
You could add yourself as a user to the database
And see that there is no strange config in the database that wants to create a user / different database name
 
From SSH I can:

Code: 
mysql -u username_database -p username_database

And that works just fine.

Code: 
MariaDB [username_database]>

But if I run the same command with "< /home/username/staging/mydatabase.sql" at the end, using the same password, I get a credential error... So strange.
 
ericosman said:
Does the password contain strange caracters?
Click to expand...

The original did, was long/secure. I dumbed it down and the issue persists. We responded near the same time, so perhaps my last response wasn't seen. I can access the MariaDB via SSH with the same password. It's only when I try to import the DB that it gives me a credential error.
 
Something i found in an old thread:
Good idea - via SSH ;)

Hi,

yeah, the better way for big SQL files is to import via SSH command.

1.) Upload the SQL file via FTP Software on your webspace in for example your public_html folder (but do not forget to delete the SQL file (test.sql) after importing)

2.) Open SSH client and login as root

3.) Run the following command (don't forget to change the path and user and database information):

mysql -u databaseuser -p databasename < /home/username/public_html/test.sql

You will get:
"Enter password:" Enter database password.

Maybe it will take a while to import the 200 MB database (depends on server configuration).

Good luck!
 
Yes, that is exactly what I am doing.

That is the same thread I read a couple hours ago when I started this process. :(
 
Importing now via CLI.

Using the 'source' command after logging into MariaDB CLI.

Seems to be working.... Will report back.

What a wonky setup. I've never had to do this before. Not sure if this is a DirectAdmin thing, a Debian 12 thing, or something in between.
 
InTheWoods said:
Using the 'source' command after logging into MariaDB CLI.
Click to expand...
Can you explain a bit how that is working? Because I also always use the:
mysql -uUSERNAME -p DATABASENAME < /path/to/the/backup.sql
and never had any issues either.
The uppercase username and databasename is just example to make the difference clear, we always use correct case (mostly lowercase).

But I'm curious as to how that source command works.
 
Richard G said:
Can you explain a bit how that is working? Because I also always use the:
mysql -uUSERNAME -p DATABASENAME < /path/to/the/backup.sql
and never had any issues either.
The uppercase username and databasename is just example to make the difference clear, we always use correct case (mostly lowercase).

But I'm curious as to how that source command works.
Click to expand...

Sure, in your example above:

Code: 
mysql -uUSERNAME -p DATABASENAME < /path/to/the/backup.sq

That would result in an error for me. ( ERROR 1044 (42000) at line 18: Access denied for user 'username'@'localhost' to database 'databasename' )

However, I can open the MySQL/MariaDB CLI prompt by instead just doing

Code: 
mysql -uUSERNAME -p DATABASENAME

The exact same password as used before with error, now works just fine.

From there I just did:

Code: 
source /path/to/the/backup.sql;

Took a couple of hours, but it imported it just fine.

Only issue is in my frustration I uploaded, unzipped, and imported the wrong DB backup.... So now I'm repeating the process all over again.

I'm either ready for a nap or a swift kick to the head.
 
You must log in or register to reply here.
Back
Top