Hello everybody.
I am playing around with dreamcompute and i am thinking of moving everything to there.
VPS hosting one site.(Planning to host more.)
a MYSQL VPS.
I have exported the MYSQL DB allready but i want to export the MYSQL users aswell.
I’ve tried outputting all the user information and grand statements thru shell access but it didn’t work for me or i’m doing something wrong.
Any help is appreciated.
Thank you for your time.
which commands have you used exactly on each side? you should be able to dump the users table and import it on the other side, and flush privileges after the import. Add details, including the exact error messages or a careful description of the behavior you’re experiencing.
Well i have tried to run this command to get all the information i needed.
mysql -h {host_name} -u {user_name} -p{password} -Ne “select distinct concat( “SHOW GRANTS FOR '”,user,”’@’",host,"’;" ) from user;" mysql | mysql -h {host_name} -u {user_name} -p{password} | sed ‘s/(GRANT .)/\1;/;s/^(Grants for .)/## \1 ##/;/##/{x;p;x;}’
But i get this error:
ERROR 2005 (HY000): Unknown MySQL server host ‘{host_name}’ (0)
The hostname i used is the hostname i see in the GOODIES>MYSQL Databases in the dreamhost panel.
After that i tried getting the hostname by using this command.
mysql -u root -B -N -p -e “SELECT user, host FROM user” mysql
But that gave me this error:
ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)
This was all done by using putty on the VPS running the site (not the MYSQL one) as the way i understand it is that it is local.
I have not started importing anything yet as i want the latest export of the DB as it is still being used.
That is why i want to have the MYSQL users first.
Sorry for the late reply but thank you for the speedy response.
Great, now we’re talking. The hostname you need to use is the one you find in the configuration file of the applications accessing it. I’d look there first, not the panel. Do you run something like WordPress? Or what do you use the SQL dB for?
It’s now being used by xenforo, thanks for the info i’ll have a look and see if i see a different hostname there.
Edit:
Well i checked and it shows the same hostname as the dreamhost panel showed.
So it was correct.
Do you know of a other way to do this?
I see that Percona’s tool pt-show-grants works pretty easy but i’m guessing that such software is not present on the VPS.
It’s not like i need my users if i could convert it all to a different user.
But i don’t know what kind of a impact that would have.
I would have hoped on some dreamhost specific instruction that is relevant to their setup.
As seeing the normal way of doing it is not working as it should be?
Most of the other information i needed i did find here minus how to setup linux.
But i am still learning so hopefully i can get everything setup the way i wanted.
The error you reported seems to have nothing to do with the tools.
ERROR 2005 (HY000): Unknown MySQL server host '{host_name}' (0)
That tells me that you are your script is not setting the variable {hostname} correctly (or you’re hiding the real hostname in this post for some reasons.) In any case, the error seems to be quite clear to me: you’re not connecting to the database, therefore wether you use mysqldump, mysql client, percona or else shouldn’t make a difference.
So, I just tried this out on a new MySQL VPS myself: I looked up at the hostname on https://panel.dreamhost.com/index.cgi?tree=goodies.mysql&… I went to my Linux box and issued the command
$ mysql -u $MYUSER -p -h mysql.$MYDOMAIN.com $MYDATABASE_NAME #(with the correct values in the $VARIABLES)
And I got connected flawlessly. So, this tells me that you can connect remotely to the database from any host. You don’t need to login into the VPS and you are not restricted to use the tools installed on VPS. You can use the tools you prefer to manage that database, even from your Windows or Mac machine.
BTW, the MySQL VPS comes with phpmyadmin installed, too. You should see the link in the Goodies section of the panel. https://phpmyadmin.dreamhost.com/index.php?hostname=$YOUR_MYSQLVPS_HOSTNAME should take you there. Enter username and password and export your tables.
I still had troubles dumping the users and grants.(empty dump files)
So what i did was, manually remake the user for the new DB and granted all the permissions to the xenforo DB tables.
Now all is working well so thank you for all the information regarding this subject.
I learned alot and dreamcompute is a awesome service to work/learn with.
Fantastic! Great to hear that. On the MySQL/MariaDB topic, did you see this tutorial I wrote on how to create a new Ubuntu server on DreamCompute, install mysql/mariadb and create a database with 20 lines of yaml with Ansible?
No but thank you because i was allready interested in trying the ansible playbook as it looks very nice.
And that is a perfect starting point for me, will follow your tutorial for sure to get comfortable with ansible.
So i can try more & more after that offcourse.
I’m not sure why do you have such a troubles with MySQL user backup and restore.
You can dump entire MySQL DB to the file using the command below:
All you need next is copy the ‘users’ table SQL statements from this file and import them to your new MySQL server.
Take a look here for few examples of MySQL backup and restore, it may help you too.
You can do the same with PHPMyAdmin if you feel yourself more comfortable with browser.
Using Ansible to manage your MySQL installation is a great improvement but can be a little overkill to be honest.