I need to copy a table from one database to another. This will be a cronjob. Which one is the best way to do it? PHP script or Shell Script. The problem with PHP, both databases has different usernames and passwords so I can't do it like this.
CREATE TABLE db1.table1 SELECT * FROM db2.table1
Should I just connect first DB get all records and insert all to new database using WHILE loop or there is a better way?
I prefer a shell script to do this instead of PHP script.
Thanks
If you need to copy the table on the same server you can use CREATE TABLE LIKE and INSERT:
USE db2;
CREATE TABLE table2 LIKE db1.table1;
INSERT INTO table2
SELECT * FROM db1.table1;
I'd dump it. Much less complicated than anything PHP based.
mysqldump -u user1 -ppassword1 databasename > dump.sql
mysql -u user2 -ppassword2 databasename < dump.sql
MySQL reference: 4.5.4. mysqldump — A Database Backup Program
mysqldump --user=root --password=remote_password the_database_name | mysql --user=root --password=local_password the_database_name; usually this may be done from server to server using SSH-port-forwarding: ssh -f -N -L 3307:localhost:3306 nmmn (nmmn is one of my SSH-shortcuts, use a server-name+port instead); @Ergec for multiple tables use the param --tables table1 table2
-p and the password. Some tend to think it is a typo as well as if your password contains any special characters you will need to accent them with `\`mysqldump -u user1 -ppassword1 databasename TblName | mysql -u user2 -ppassword2 anotherDatabase
It all can be done in a single command.
$L1 = mysql_connect('localhost', 'user1', 'pass1');
$DB1 = mysql_select_db('database1', $L1);
$L2 = mysql_connect('localhost', 'user2', 'pass2');
$DB2 = mysql_select_db('database2', $L2);
$re=mysql_query("SELECT * FROM table1",$L1);
while($i=mysql_fetch_assoc($re))
{
$u=array();
foreach($i as $k=>$v) if($k!=$keyfield) $u[]="$k='$v'";
mysql_query("INSERT INTO table2 (".implode(',',array_keys($i)).") VALUES ('".implode("','",$i)."') ON DUPLICATE KEY UPDATE ".implode(',',$u),$L2) or die(mysql_error());
}
user1, pass1, database1, table1 reffers to initial table user2, pass2, database2, table2 reffers to copied table $keyfield is the primary key of table
One liner with different servers
mysqldump -h host1 -u user1 -ppassword1 databasename TblName | mysql -h host2 -u user2 -ppassword2 anotherDatabase
-P 3309Phpmyadmin has inbuilt functionality to copy tables from one database to another. Otherwise you can go with Pekka or export table then import table.
CREATE TABLE db_target.cloned_table
SELECT *
FROM db_source.source_table;
I'll put this answer up for anyone else looking for help.
If you don't have access to SSH then you can use PhpMyAdmin.
Simply:
If you come across privilege problems, you can temp grant a user Global permissions or add the same user to both databases.
exec('mysqldump --user=username --password="password" --host=hostname --database=database table1 table2 > databasedump.sql');
exec('mysql --user=username --password="password" --host=hostname --database=database < databasedump.sql');
insert into dest.table select * from orginal.table;
use <from database>
create table <to database.new name> as (select * from <table to copy>);
As it seems that nobody answered the initial question actually, here is my PHP script to backup a table from a remote MySQL server to a local MySQL server:
function backup_remote_table (
$remote_db_host, $remote_login, $remote_password, $remote_db_name, $remote_table_name,
$local_db_host, $local_login, $local_password, $local_db_name, $local_table_name
) {
// Generating names with time stamps for local database and/or local table, if not available
if ($local_table_name) {
$applied_local_table_name = $local_table_name;
} else {
$applied_local_table_name = $remote_table_name;
}
if ($local_db_name) {
$applied_local_db_name = $local_db_name;
if (!$local_table_name) {
$applied_local_table_name .= date_format(date_create(), '_Y_m_d_H_i_s');
}
} else {
$applied_local_db_name = $remote_db_name . date_format(date_create(), '_Y_m_d_H_i_s');
}
// Local server connection
$local_db_server = mysql_connect($local_db_host, $local_login, $local_password);
$local_db_server = mysql_query("CREATE DATABASE IF NOT EXISTS " . $applied_local_db_name, $local_db_server);
mysql_select_db($applied_local_db_name, $local_db_server);
// Remote server connection
$remote_db_server = mysql_connect($remote_db_host, $remote_login, $remote_password);
mysql_select_db($remote_db_name, $remote_db_server);
// Getting remote table data
$result_remote_table_info = mysql_query("SHOW CREATE TABLE " . $remote_table_name, $remote_db_server);
$remote_table_info = mysql_fetch_array($result_remote_table_info);
$remote_table_description = substr($remote_table_info[1], 13);
// Creating local table
$sql_new_table = "CREATE TABLE IF NOT EXISTS " . $applied_local_db_name . "." . $remote_table_description;
mysql_query($sql_new_table, $local_db_server);
// Getting all records of remote table
$result_remote_table_data = mysql_query("SELECT * FROM " . $table_name, $remote_db_server);
while ($remote_table_row = mysql_fetch_array($result_remote_table_data, MYSQL_ASSOC)){
// Browsing records of remote table
$sql_new_row = "INSERT INTO $applied_local_db_name.$applied_local_table_name (".implode(", ",array_keys($remote_table_row)).") VALUES (";
$extra_sql = "";
foreach (array_values($remote_table_row) as $value) {
if ($extra_sql != "") {
$extra_sql .= ",";
}
$extra_sql .= "'";
$extra_sql .= mysql_real_escape_string($value);
$extra_sql .= "'";
}
$sql_new_row .= $extra_sql . ")";
// Adding record to local table
$result_new_table_row = mysql_query($sql_new_row, $local_db_server);
}
mysql_free_result($result_remote_table_data);
return;
}
The solution above is not mine, I got it here, with minor changes.
mysql_connect in 2019? Come on.