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.
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?
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.
$ 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.
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.