I would love to know whether this code is the correct manner to send mysql data to a remote database.

$host = 'www.domain.tld';
$user = 'remote_user';
$pass = 'remote_password';

$time_end = microtime(true);

//grant per;misions to connect to remote host
if (!function_exists("ssh2_connect")) die("function ssh2_connect doesn't exist");

// log in at server1.example.com on port 22
if (!($con = ssh2_connect("www.domain.tld", 22))) {
echo "fail: unable to establish connection\n";
} else {

// try to authenticate with username root, password secretpassword
if (!ssh2_auth_password($con, "remote_user", "remote_password")) {
echo "fail: unable to authenticate\n";
} else {

echo "okay: logged in...\n";

// grant priveliges to access remote database
$priveleges="GRANT ALL ON dest_table.create_info.* TO ' remote_user'@'www.domain.tld'";

//dump database to a file
$dump = "mysqldump -u remote_user -p Profusion.source_cadre > Profusion.source_cadre.sql";

//now compress file for faster upload
$compress="tar zcf Profusion.source_cdr.tar.gz dest_table.sql";

//now start decompressing the file
$decom="tar zxf dumpfilename.tar.gz";

//now import the file to the database
$import="mysql -u remote_user -p dest_table.create_info < Profusion.source_cdr.sql";

//get the number of rows inserted
$progress=mysql_affected_rows();

exec ($priveleges,$dump,$compress,$decom,$import);
sleep(1);


}

I am unable to test this code at the present moment

Your help will be highly appreciated.

Hi,

I can't see how your code would work, I think you've missed couple of somethings.

As I saw on many websites, you need to use the function "ssh2_exec" instead of "exec" and you need to run the commands one by one.

Also, in your case you can't use the command:

$progress=mysql_affected_rows(); // line 38

because you're not managing the mysql connection by php.

For more info about ssh connection try this link and search for "Great! PHP supports SSH - time to code" to skip server configurations.

Hi The Diamonds

I actually wanted to get the number of rows to be affected so that i can include that number in the calculation I need to implement in a progress bar.

Is there a better way to get the rows to be affected in the database dump??

Thanks alot for your help.

I can see that you're trying to dump user data into a file then compress it for faster upload, but I don't see you do the upload, that's why I don't understand what progress are you calculating.

I'm not familiar with reaching Mysql database in PHP through SSH, but I suggest that you use CLI stream, see this link here.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.