MySQL dump restore in a remote machine with port 3307

Iyyappan · 09-09-2012, 05:41 AM

Hi All,
We have Mysql installed with port 3307. So we need to specify the sock file location using -S option to login in MySQL. I need help to restore MySQL dump into a remote machine. I want to know how can I specify the sock file while restoring the dump in a remote machine
Server 1: 192.168.1.2 ( will access the server using MySQL client )
Server 2: 192.168.1.3 ( MySQL DB) Port 3307

Dump backup is in 192.168.1.2 machine. I need to execute command in 192.168.1.2 to restore the dump in 192.168.1.3. Since MySQL in 192.168.1.3 is using 3307 I am not able to connect using the following command
mysql -h 192.168.1.3 -u root -p
It says cannot connect to 192.168.1.3. If there is an option to specify the sock while connecting remotely it would be helpful for me

Note: I do have port 22 open in 192.168.1.3 for 192.168.1.2. Only port 3307 is opened in 192.168.1.3 for 192.168.1.2

Guttorm · 09-09-2012, 06:15 AM

Hi

Mysql uses port 3306 by default. So you need to tell the client to use 3307 instead.

Code:

mysql -P 3307 -h 192.168.1.3 -u root -p

Iyyappan · 09-09-2012, 06:19 AM

It did not work either. Is there any specific command to provide MySQL user only to restore dumps. I do not want to give grant all privileges. Instead I would like to give only restore access only. If I give it will work I think. My requirement is, the client should access the MySQL server every month end to drop the existing db, create the db and restore the dump thats all, other than this client should not have the rights to perform any other things in server side

Guttorm · 09-09-2012, 06:34 AM

Maybe post the error message? You need to be able to connect first. Are you sure it's listening on port 3307?

Don't use the root user if you plan to limit the access. Set up a user with the privileges you want, and use that username instead.

Iyyappan · 09-09-2012, 06:34 AM

grant CREATE, DROP on db.* to root@192.168.1.2 identified by 'password';

connect mysql server using mysql -h IP -P 3307 -u root -ppassword

I am able to connect now. Will check and update whether I can restore database. Can you provide with some script to restore mysql dump in remote machine based on date. I have script for restoring databases based on date on same pc.

Iyyappan · 09-09-2012, 07:07 AM

I have a $ symbol is password. If I pass it on script its not accepting. Any idea on this . Password is like zgrief7$Ru ..

#!/usr/bin/expect -f
set DT [exec date +%d-%b-%Y]
set timeout -1
cd /mnt/mysqlbackup

spawn scp user@192.168.1.2:/mysql-store/mysql_.../DB-$DT.sql.gz .
expect {
password: {send "passw123\n" ; exp_continue}
}
spawn /usr/local/Mysql_Restore.sh /mnt/mysqlbackup root zgrief7$Ru 192.168.1.3 19
expect eof

Guttorm · 09-09-2012, 11:14 AM

Why are you using expect? What is it you are trying to do? If you use ssh/scp and expect you are not limiting access at all.

Wim Sturkenboom · 09-09-2012, 11:15 AM

If you made the dump with mysqldump, you can use something like

Code:

mysql -u root -p database_name < dumpfilename.sql

Adjust to your needs with IP and port. It will prompt you for the password. If I'm not mistaken, the database must exist.

Iyyappan · 09-10-2012, 02:17 AM

I have a MySQL dump in 192.168.1.2. I want to import that dump into 192.168.1.3 database every month end once just to check whether the exported dump is able to restore properly. I am automating this as there are many servers which I need to do this. MySQL dump is taken on date basis i.e mysqltest_20120910.sql It will be like this. In script I do the following
1. Moving to the dir of dump location
2. Copying the dump from the source machine
3. Calling the MySQL restore script to restore the Database and send mail whether its success or failure using mutt