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
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 1
If you need to copy the table on the same server you can use this code:
USE db2;
CREATE TABLE table2 LIKE db1.table1;
INSERT INTO table2
SELECT * FROM db1.table1;It’s copy+pasted from here:
codingforums.com
It’s not my solution, but I find it useful.
Method 2
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
Method 3
mysqldump -u user1 -ppassword1 databasename TblName | mysql -u user2 -ppassword2 anotherDatabase
It all can be done in a single command.
Method 4
$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
Method 5
One liner with different servers
mysqldump -h host1 -u user1 -ppassword1 databasename TblName | mysql -h host2 -u user2 -ppassword2 anotherDatabase
Method 6
Phpmyadmin has inbuilt functionality to copy tables from one database to another. Otherwise you can go with Pekka or export table then import table.
Method 7
CREATE TABLE db_target.cloned_table SELECT * FROM db_source.source_table;
Method 8
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:
- browse to the table you want to move
- Click the Operations tab
- Use the MOVE or COPY to database function
If you come across privilege problems, you can temp grant a user Global permissions or add the same user to both databases.
Method 9
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');Method 10
insert into dest.table select * from orginal.table;
Method 11
use <from database>
create table <to database.new name> as (select * from <table to copy>);
Method 12
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.
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0