Moving data between two servers with SQL
Hello, I have a background process that runs as a combination of PHPMySQL. The end results are records in a table on server #1 (but not the entire table, just a small subset of the table needs to move). What's the easiest/cleanest way of moving those records to an identical table on another server? In Oracle, we used to be able to set up connection profiles and move data between servers with SQL, but I'm guessing that's not easy to do with MySQL. I'd prefer not to use mysql command line client commands to save the data as an OS file and then import that into the other server using another mysql command line client command. I'd like to find something cleaner than that. I'm using 5.5.8. thanks, -Hank
Re: Moving data between two servers with SQL
On 1/4/2011 15:53, Hank wrote: Hello, I have a background process that runs as a combination of PHPMySQL. The end results are records in a table on server #1 (but not the entire table, just a small subset of the table needs to move). What's the easiest/cleanest way of moving those records to an identical table on another server? In Oracle, we used to be able to set up connection profiles and move data between servers with SQL, but I'm guessing that's not easy to do with MySQL. I'd prefer not to use mysql command line client commands to save the data as an OS file and then import that into the other server using another mysql command line client command. I'd like to find something cleaner than that. I'm using 5.5.8. thanks, -Hank Have you looked at the FEDERATED storage engine? http://dev.mysql.com/doc/refman/5.5/en/federated-storage-engine.html -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Moving data between two servers with SQL
Wow, that might just work! I've seen Federated tables mentioned about, but I never knew that's what they are here for.. thanks. Can I have a host (remote) table on a MySQL 4.1.x server, and the federated table on a 5.5.8 server? -Hank On Tue, Jan 4, 2011 at 4:15 PM, Shawn Green (MySQL) shawn.l.gr...@oracle.com wrote: On 1/4/2011 15:53, Hank wrote: Hello, I have a background process that runs as a combination of PHPMySQL. The end results are records in a table on server #1 (but not the entire table, just a small subset of the table needs to move). What's the easiest/cleanest way of moving those records to an identical table on another server? In Oracle, we used to be able to set up connection profiles and move data between servers with SQL, but I'm guessing that's not easy to do with MySQL. I'd prefer not to use mysql command line client commands to save the data as an OS file and then import that into the other server using another mysql command line client command. I'd like to find something cleaner than that. I'm using 5.5.8. thanks, -Hank Have you looked at the FEDERATED storage engine? http://dev.mysql.com/doc/refman/5.5/en/federated-storage-engine.html -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN
Re: Moving data between two servers with SQL
Also, can I do this: insert into federated_table select * from local_table? -Hank On Tue, Jan 4, 2011 at 4:15 PM, Shawn Green (MySQL) shawn.l.gr...@oracle.com wrote: On 1/4/2011 15:53, Hank wrote: Hello, I have a background process that runs as a combination of PHPMySQL. The end results are records in a table on server #1 (but not the entire table, just a small subset of the table needs to move). What's the easiest/cleanest way of moving those records to an identical table on another server? In Oracle, we used to be able to set up connection profiles and move data between servers with SQL, but I'm guessing that's not easy to do with MySQL. I'd prefer not to use mysql command line client commands to save the data as an OS file and then import that into the other server using another mysql command line client command. I'd like to find something cleaner than that. I'm using 5.5.8. thanks, -Hank Have you looked at the FEDERATED storage engine? http://dev.mysql.com/doc/refman/5.5/en/federated-storage-engine.html -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN
Re: Moving data between two servers with SQL
Nevermind -- it's working absolutely perfectly between 5.5.8 and 4.1.x. Thanks again for the push. -Hank On Tue, Jan 4, 2011 at 5:14 PM, Hank hes...@gmail.com wrote: Also, can I do this: insert into federated_table select * from local_table? -Hank On Tue, Jan 4, 2011 at 4:15 PM, Shawn Green (MySQL) shawn.l.gr...@oracle.com wrote: On 1/4/2011 15:53, Hank wrote: Hello, I have a background process that runs as a combination of PHPMySQL. The end results are records in a table on server #1 (but not the entire table, just a small subset of the table needs to move). What's the easiest/cleanest way of moving those records to an identical table on another server? In Oracle, we used to be able to set up connection profiles and move data between servers with SQL, but I'm guessing that's not easy to do with MySQL. I'd prefer not to use mysql command line client commands to save the data as an OS file and then import that into the other server using another mysql command line client command. I'd like to find something cleaner than that. I'm using 5.5.8. thanks, -Hank Have you looked at the FEDERATED storage engine? http://dev.mysql.com/doc/refman/5.5/en/federated-storage-engine.html -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN
Re: moving data to MySQL
Well, it is just to have a console that is able to connect to every database, ODBC and videogames are one of the few things good about microsoft! I used it to import export data from/to access/sql server/mysql/oracle. Cheers Claudio 2009/2/22 Haidong Ji haidong...@gmail.com Hi, Never thought of the MS Access solution, but it sounds interesting and I am intrigued. If the data volume is not that big, it could be a quick and dirty way of doing it. Sql Server 2005 and Sql Server 2008 Integration Service (SSIS) can do this, but as far as usability is concerned, it is a step back from DTS (Data Transformation Service from Sql Server 7 and Sql Server 2000 days). SSIS has a fairly steep learning curve. I am also for the CSV flat file approach. One could use Sql Server bcp utility to dump tables out as tab delimited file, then import it into MySQL with LOAD DATA INFILE. Sample of bcp command: bcp MyDb.dbo.t1 out c:\junk\t1.txt -c -q -SMySqlServerInstance -T Sample of LOAD DATA command: load data local infile '/home/MyFolder/t1.txt' into table MyDb.t1 lines terminated by 'r\n' It looks like your first task is to restore this backup into a Sql Server database. Sql Server Express management studio might have a GUI way of doing this. If not, here is a sample script: restore database MyDb from disk = 'c:\LocationOfMyDbBackup' with recovery, replace, move 'MyDbLogicalDataFile' to 'c:\MyDirectory\MyDb.mdf', move 'MyDbLogicalLogFile' to 'c:\MyDirectory\MyDb.ldf' Regards, Haidong Alex Ji http://www.HaidongJi.com/tech On Sat, Feb 21, 2009 at 5:48 PM, Claudio Nanni claudio.na...@gmail.comwrote: Hi, Using ODBC Drivers and MS Access you can do the job. This is a quick list of steps(if you need more in depth just ask): Download ODBC drivers for MySQL from www.mysql.com Install the ODBC drivers Create a System Data Source Name that point to the MySQL database (Control Panel-Administration Tools-ODBC Data Sources) Create a System Data Source Name that point to the MSSQL database (Control Panel-Administration Tools-ODBC Data Sources) Using Microsoft Access you can import-export-query the tables as you like. Cheers Claudio Nanni 2009/2/21 Chris Rehm ch...@javadisciple.com I want to write some programs to work with data from eveonline.com but my installed database is MySQL and the data format they provide is a backup of MSSQL. They recommend installing SQL Server 2005 Express and I've downloaded that and am willing to install it, I just want to know if there is a programmatic way of transferring the data to MySQL. Any help or insight would be appreciated, I have been away from coding for several years because of health issues and I'm trying to knock the rust off my brain and get going again. Chris Rehm ch...@javadisciple.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com
moving data to MySQL
I want to write some programs to work with data from eveonline.com but my installed database is MySQL and the data format they provide is a backup of MSSQL. They recommend installing SQL Server 2005 Express and I've downloaded that and am willing to install it, I just want to know if there is a programmatic way of transferring the data to MySQL. Any help or insight would be appreciated, I have been away from coding for several years because of health issues and I'm trying to knock the rust off my brain and get going again. Chris Rehm ch...@javadisciple.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: moving data to MySQL
There are products out there that convert from ms sql to MySql. Though I would test them before trusting them --Original Message-- From: Chris Rehm To: mysql@lists.mysql.com Subject: moving data to MySQL Sent: Feb 21, 2009 10:47 I want to write some programs to work with data from eveonline.com but my installed database is MySQL and the data format they provide is a backup of MSSQL. They recommend installing SQL Server 2005 Express and I've downloaded that and am willing to install it, I just want to know if there is a programmatic way of transferring the data to MySQL. Any help or insight would be appreciated, I have been away from coding for several years because of health issues and I'm trying to knock the rust off my brain and get going again. Chris Rehm ch...@javadisciple.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=chaim.rie...@gmail.com Sent via BlackBerry from T-Mobile
Re: moving data to MySQL
2009/2/21 Chris Rehm ch...@javadisciple.com I want to write some programs to work with data from eveonline.com but my installed database is MySQL and the data format they provide is a backup of MSSQL. They recommend installing SQL Server 2005 Express and I've downloaded that and am willing to install it, I just want to know if there is a programmatic way of transferring the data to MySQL. Any help or insight would be appreciated, I have been away from coding for several years because of health issues and I'm trying to knock the rust off my brain and get going again. Chris Rehm ch...@javadisciple.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=dimitar.vassi...@gmail.com You can make a ODBC/JDBC connection, export to CSV file, create a db link also
Re: moving data to MySQL
Hi, Using ODBC Drivers and MS Access you can do the job. This is a quick list of steps(if you need more in depth just ask): Download ODBC drivers for MySQL from www.mysql.com Install the ODBC drivers Create a System Data Source Name that point to the MySQL database (Control Panel-Administration Tools-ODBC Data Sources) Create a System Data Source Name that point to the MSSQL database (Control Panel-Administration Tools-ODBC Data Sources) Using Microsoft Access you can import-export-query the tables as you like. Cheers Claudio Nanni 2009/2/21 Chris Rehm ch...@javadisciple.com I want to write some programs to work with data from eveonline.com but my installed database is MySQL and the data format they provide is a backup of MSSQL. They recommend installing SQL Server 2005 Express and I've downloaded that and am willing to install it, I just want to know if there is a programmatic way of transferring the data to MySQL. Any help or insight would be appreciated, I have been away from coding for several years because of health issues and I'm trying to knock the rust off my brain and get going again. Chris Rehm ch...@javadisciple.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com
Re: moving data to MySQL
Hi, Never thought of the MS Access solution, but it sounds interesting and I am intrigued. If the data volume is not that big, it could be a quick and dirty way of doing it. Sql Server 2005 and Sql Server 2008 Integration Service (SSIS) can do this, but as far as usability is concerned, it is a step back from DTS (Data Transformation Service from Sql Server 7 and Sql Server 2000 days). SSIS has a fairly steep learning curve. I am also for the CSV flat file approach. One could use Sql Server bcp utility to dump tables out as tab delimited file, then import it into MySQL with LOAD DATA INFILE. Sample of bcp command: bcp MyDb.dbo.t1 out c:\junk\t1.txt -c -q -SMySqlServerInstance -T Sample of LOAD DATA command: load data local infile '/home/MyFolder/t1.txt' into table MyDb.t1 lines terminated by 'r\n' It looks like your first task is to restore this backup into a Sql Server database. Sql Server Express management studio might have a GUI way of doing this. If not, here is a sample script: restore database MyDb from disk = 'c:\LocationOfMyDbBackup' with recovery, replace, move 'MyDbLogicalDataFile' to 'c:\MyDirectory\MyDb.mdf', move 'MyDbLogicalLogFile' to 'c:\MyDirectory\MyDb.ldf' Regards, Haidong Alex Ji http://www.HaidongJi.com/tech On Sat, Feb 21, 2009 at 5:48 PM, Claudio Nanni claudio.na...@gmail.comwrote: Hi, Using ODBC Drivers and MS Access you can do the job. This is a quick list of steps(if you need more in depth just ask): Download ODBC drivers for MySQL from www.mysql.com Install the ODBC drivers Create a System Data Source Name that point to the MySQL database (Control Panel-Administration Tools-ODBC Data Sources) Create a System Data Source Name that point to the MSSQL database (Control Panel-Administration Tools-ODBC Data Sources) Using Microsoft Access you can import-export-query the tables as you like. Cheers Claudio Nanni 2009/2/21 Chris Rehm ch...@javadisciple.com I want to write some programs to work with data from eveonline.com but my installed database is MySQL and the data format they provide is a backup of MSSQL. They recommend installing SQL Server 2005 Express and I've downloaded that and am willing to install it, I just want to know if there is a programmatic way of transferring the data to MySQL. Any help or insight would be appreciated, I have been away from coding for several years because of health issues and I'm trying to knock the rust off my brain and get going again. Chris Rehm ch...@javadisciple.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com
Moving Data between Tables
I moved information about our school locks (serial numbers, combinations, student, etc) from FileMaker to MySQL into a table called lockers and wrote PHP pages so our teachers could record the locks returned at the end of the year. Unfortunately, I missed transferring close to 200 locks. I duplicated the structure of lockers (1313 locks) as lockers2 and reloaded all the information from Filemaker (1492 locks). My problem is moving the information from lockers to lockers2 that was added AFTER the Filemaker migration (2nd semester teacher, returned, paid). This is what I'm getting set to try, but I've never seen a JOIN in an update statement before. Am I on the right track for this? UPDATE lockers2 SET lockers2.returned = lockers.returned, lockers2.teacher2nd = lockers.teacher2nd, lockers2.paid = lockers.paid WHERE lockers2.serialNumber = lockers.serialNumber; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Moving Data between Tables
Hi John, Kebbel, John wrote: I moved information about our school locks (serial numbers, combinations, student, etc) from FileMaker to MySQL into a table called lockers and wrote PHP pages so our teachers could record the locks returned at the end of the year. Unfortunately, I missed transferring close to 200 locks. I duplicated the structure of lockers (1313 locks) as lockers2 and reloaded all the information from Filemaker (1492 locks). My problem is moving the information from lockers to lockers2 that was added AFTER the Filemaker migration (2nd semester teacher, returned, paid). This is what I'm getting set to try, but I've never seen a JOIN in an update statement before. Am I on the right track for this? UPDATE lockers2 SET lockers2.returned = lockers.returned, lockers2.teacher2nd = lockers.teacher2nd, lockers2.paid = lockers.paid WHERE lockers2.serialNumber = lockers.serialNumber; It's not quite right. I hope you're not about to try this for the first time on your production data :-) Maybe you can grab 100 rows from each table into scratch tables and play with it to be sure you will get what you want. The general syntax (with JOIN -- I can't do comma-joins) is more like update foo inner join bar on ... set a = b, c = d... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Moving Data between Tables
I hope you're not about to try this for the first time on your production data :-) I dumped the lockers table before beginning my experiments. If I had trashed lockers2 (my experimental file) in the process, I would have truncated it and re-inserted the 1492 records from my batch file. - It's not quite right. You're correct. I tried my UPDATE/implicit JOIN query shortly after posting my email. I expected the query to fail, which it did. Not knowing how long it would be before someone responded, I went ahead and solved my problem with a PHP script. It was nice that the problem was solve-able with PHP, but it was frustrating to write 20 lines of PHP because I didn't know how to write the 1 line of MySQL that would have solved my problem. - update foo inner join bar on ... set a = b, c = d... I run a backup of the database and associated PHP files every few nights and then carry a copy of the backup home to expand the PHP and incorporate the new things I learn about MySQL into my table structures or into batch files. You can be assured I'll be spending time with our old friends foo and bar and this update syntax between now and Monday. Thanks again, Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: (Errcode: 13) after moving data directory
boll wrote: Dominik Klein wrote: Did you check FAT-permissions? When mounting a FAT-partition, you have to set explicit permissions while mounting as FAT does not understand the unix permission concept. Try to mount this way: mount -t vfat -o uid=mysql,gid=mysql,rw,umask=007 /dev/[yourdevicename] /your/mountpoint Hi Dominik, In /etc/fstab the partition is mounted with this line: /dev/hdb2/mnt/FATvfatuid=27,gid=27,umask=000 0 0 ...which I think is the same as what you recommend (uid 27 is mysql) . What I really don't understand is: Why mysqld will start up and use the dataq on the FAT partition (as I want it to do) if I start it with mysqld_safe, but when I boot the computer or try, as root, service mysqld start, it fails to start with these log errors: 060421 08:43:10 mysqld started 060421 8:43:11 [Warning] Can't create test file /mnt/FAT/mysqldata/localhost.lower-test /usr/libexec/mysqld: Can't change dir to '/mnt/FAT/mysqldata/' (Errcode: 13) 060421 8:43:11 [ERROR] Aborting I'm guessing that since mysqld_safe runs as user mysql, maybe mysqld runs as a different user? How would I find that out? I will keep reading the manual, but will be grateful for any ideas. I was able to get mysqld to start at boot, by disabling selinux. Not happy to do it that way, but couldn't find any alternative. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: (Errcode: 13) after moving data directory
boll schrieb: Hi- Using MySQL 4.1.11 on Fedora 4. I moved my data directory to a FAT partition in order to share it with Windows dual-boot. Now when I try to start mysqld normally, it fails with these messages in the log: 060420 18:16:03 mysqld started 060420 18:16:03 [Warning] Can't create test file /mnt/FAT/mysqldata/localhost.lower-test /usr/libexec/mysqld: Can't change dir to '/mnt/FAT/mysqldata/' (Errcode: 13) 060420 18:16:03 [ERROR] Aborting However, I can start mysqld using: mysqld_safe, so I know it's possible. Any suggestions? Thanks in advance. Did you check FAT-permissions? When mounting a FAT-partition, you have to set explicit permissions while mounting as FAT does not understand the unix permission concept. Try to mount this way: mount -t vfat -o uid=mysql,gid=mysql,rw,umask=007 /dev/[yourdevicename] /your/mountpoint Then it should work. Regards, Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: (Errcode: 13) after moving data directory
Dominik Klein wrote: Did you check FAT-permissions? When mounting a FAT-partition, you have to set explicit permissions while mounting as FAT does not understand the unix permission concept. Try to mount this way: mount -t vfat -o uid=mysql,gid=mysql,rw,umask=007 /dev/[yourdevicename] /your/mountpoint Hi Dominik, In /etc/fstab the partition is mounted with this line: /dev/hdb2/mnt/FATvfatuid=27,gid=27,umask=000 0 0 ...which I think is the same as what you recommend (uid 27 is mysql) . What I really don't understand is: Why mysqld will start up and use the dataq on the FAT partition (as I want it to do) if I start it with mysqld_safe, but when I boot the computer or try, as root, service mysqld start, it fails to start with these log errors: 060421 08:43:10 mysqld started 060421 8:43:11 [Warning] Can't create test file /mnt/FAT/mysqldata/localhost.lower-test /usr/libexec/mysqld: Can't change dir to '/mnt/FAT/mysqldata/' (Errcode: 13) 060421 8:43:11 [ERROR] Aborting I'm guessing that since mysqld_safe runs as user mysql, maybe mysqld runs as a different user? How would I find that out? I will keep reading the manual, but will be grateful for any ideas. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
(Errcode: 13) after moving data directory
Hi- Using MySQL 4.1.11 on Fedora 4. I moved my data directory to a FAT partition in order to share it with Windows dual-boot. Now when I try to start mysqld normally, it fails with these messages in the log: 060420 18:16:03 mysqld started 060420 18:16:03 [Warning] Can't create test file /mnt/FAT/mysqldata/localhost.lower-test /usr/libexec/mysqld: Can't change dir to '/mnt/FAT/mysqldata/' (Errcode: 13) 060420 18:16:03 [ERROR] Aborting However, I can start mysqld using: mysqld_safe, so I know it's possible. Any suggestions? Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Moving Data
Are there any variables here I can tune to try and increase the the query? mysql show variables like '%buff%'; +-+---+ | Variable_name | Value | +-+---+ | bdb_log_buffer_size | 262144| | bulk_insert_buffer_size | 8388608 | | innodb_buffer_pool_size | 8388608 | | innodb_log_buffer_size | 1048576 | | join_buffer_size| 131072| | key_buffer_size | 402653184 | | myisam_sort_buffer_size | 67108864 | | net_buffer_length | 16384 | | read_buffer_size| 2093056 | | read_rnd_buffer_size| 262144| | sort_buffer_size| 2097144 | +-+---+ mysql show variables like '%size%'; +-+--+ | Variable_name | Value| +-+--+ | bdb_cache_size | 8388600 | | bdb_log_buffer_size | 262144 | | binlog_cache_size | 32768| | bulk_insert_buffer_size | 8388608 | | delayed_queue_size | 1000 | | innodb_additional_mem_pool_size | 1048576 | | innodb_buffer_pool_size | 8388608 | | innodb_log_buffer_size | 1048576 | | innodb_log_file_size| 5242880 | | join_buffer_size| 131072 | | key_buffer_size | 402653184| | max_binlog_cache_size | 4294967295 | | max_binlog_size | 1073741824 | | max_heap_table_size | 16777216 | | max_join_size | 18446744073709551615 | | max_relay_log_size | 0| | myisam_max_extra_sort_file_size | 268435456| | myisam_max_sort_file_size | 2147483647 | | myisam_sort_buffer_size | 67108864 | | query_alloc_block_size | 8192 | | query_cache_size| 33554432 | | query_prealloc_size | 8192 | | range_alloc_block_size | 2048 | | read_buffer_size| 2093056 | | read_rnd_buffer_size| 262144 | | sort_buffer_size| 2097144 | | thread_cache_size | 8| | tmp_table_size | 33554432 | | transaction_alloc_block_size| 8192 | | transaction_prealloc_size | 4096 | +-+--+ JS. _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: moving data
Hi Shawn, Thanks for helping me out again! I've cut and pasted the answers below your questions: I would love to say how adequate your hardware should be but I have forgotten what hardware you have, sorry! Would you mind re-posting all of the specs for your DB server? 2 x 500Mhz CPUs 8GB Memory mysql-max-4.0.20-ibm-aix4.3.3.0-powerpc Also could you post the results of : EXPLAIN select * From t20040908 where uid=454 to make sure the query is using the `uid` key (it should be but it never hurts to check). mysql EXPLAIN select * From t20040909 where uid=454; +---+--+---+--+-+---+--+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+---+--+-+ | t20040909 | ref | uid | uid | 4 | const | 4275 | Using where | +---+--+---+--+-+---+--+-+ 1 row in set (0.07 sec) Could you also respond with the results of : SHOW TABLE STATUS LIKE 't20040908' mysql SHOW TABLE STATUS LIKE 't20040909'; +---+++--++-+-+--+---++-+-+-++-+ | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | +---+++--++-+-+--+---++-+-+-++-+ | t20040909 | MyISAM | Fixed | 25209614 | 32 | 806707648 |137438953471 |222495744 | 0 | NULL | 2004-09-09 14:24:41 | 2004-09-10 01:01:35 | 2004-09-09 14:25:40 || | +---+++--++-+-+--+---++-+-+-++-+ 1 row in set (0.01 sec) Part of the time it takes to query any table is the time it takes to load the correct index in from the disk so... maybe? you hardware could be too slow for this volume of information? I just can't say with any degree of confidence one way or the other yet. I did notice that this query returned in only about 11% of the time it took a similar query on the full dataset to return (6.87 seconds vs. 61 seconds a similar lookup on internet_usage). Better but not exactly linearly related to the reduction in the data size. I would think this is related to the fact that the time it takes to find records using a B-TREE index degrades roughly logarithmically. I thought about putting a HASH index on that column but those are only available to the MEMORY table type (bummer). BTW, if you and I take this thread back into the list everyone else will also have a chance to review your hardware specs and throw in their two cents, too! Shawn Green Database Administrator Unimin Corporation - Spruce Pine J S [EMAIL PROTECTED] wrote on 09/09/2004 04:48:47 AM: Hi Shawn, I'm working at the moment on the new database! I had a quick question for you. Here's my table structure: t20040908 | CREATE TABLE `t20040908` ( `uid` int(10) unsigned NOT NULL default '0', `time` timestamp(14) NOT NULL, `ip` int(10) unsigned NOT NULL default '0', `urlid` int(10) unsigned NOT NULL default '0', `timetaken` smallint(5) unsigned default '0', `cs_size` int(10) unsigned default '0', `sc_size` int(10) unsigned default '0', `method_ID` tinyint(3) unsigned NOT NULL default '0', `action_ID` tinyint(3) unsigned NOT NULL default '0', `virus_ID` tinyint(3) unsigned NOT NULL default '0', `useragent_ID` smallint(5) unsigned NOT NULL default '0', KEY `uid` (`uid`) ) TYPE=MyISAM and it currently has 15263552 rows. I ran the command: select * from t20040908 where uid=454; which came back with 8347 rows in set (6.87 sec) Does 6.87 seconds seem rather slow to you ? It could be the box is just too old and slow. But I just wanted to check in case there was anything else I should check? Cheers, JS. There are two ways that I can think of that will combine smaller tables into one longer (not wider) table: MERGE tables and UNION queries. A third option is to manually add rows from multiple source tables to a staging table using INSERT...SELECT... statements. Since we never know from day to day what you will need to query on, creating one big MERGE table would probably be as impractical as your existing setup. That leaves the other two options available to run reports against. Since your information comes in daily
Fw: moving data
How fast do your disks rotate? What kind of disk controller are you using (ATA, SATA, SCSI, ...)? Are they in a RAID array or not. If they are what RAID scheme are you using? How big are your buffers and other working areas? (show variables like '%buff%'; show variables like '%size%';). Your CPUs are probably up to the task but we need to make sure you are using as much available memory as you can. I see PowerPC in your system description... how fast is your system bus (or does anyone else think that would make that much of a difference here)? On another thoughtI was under the impression that you were trying to create a single daily table for testing purposes (hence the date in the table name) yet you seem to have 25M+ entries? Are you sure that's just one day's worth of information? In fact from the time you sent the earliest message in this post until you posted your table status, you added about 10M new rows. Shawn Green Database Administrator Unimin Corporation - Spruce Pine J S [EMAIL PROTECTED] wrote on 09/10/2004 05:56:12 AM: Hi Shawn, Thanks for helping me out again! I've cut and pasted the answers below your questions: I would love to say how adequate your hardware should be but I have forgotten what hardware you have, sorry! Would you mind re-posting all of the specs for your DB server? 2 x 500Mhz CPUs 8GB Memory mysql-max-4.0.20-ibm-aix4.3.3.0-powerpc Also could you post the results of : EXPLAIN select * From t20040908 where uid=454 to make sure the query is using the `uid` key (it should be but it never hurts to check). mysql EXPLAIN select * From t20040909 where uid=454; +---+--+---+--+-+---+-- +-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+---+-- +-+ | t20040909 | ref | uid | uid | 4 | const | 4275 | Using where | +---+--+---+--+-+---+-- +-+ 1 row in set (0.07 sec) Could you also respond with the results of : SHOW TABLE STATUS LIKE 't20040908' mysql SHOW TABLE STATUS LIKE 't20040909'; +---+++--+ +-+-+--+--- ++-+- +-++-+ | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | +---+++--+ +-+-+--+--- ++-+- +-++-+ | t20040909 | MyISAM | Fixed | 25209614 | 32 | 806707648 |137438953471 |222495744 | 0 | NULL | 2004-09-09 14:24:41 | 2004-09-10 01:01:35 | 2004-09-09 14:25:40 || | +---+++--+ +-+-+--+--- ++-+- +-++-+ 1 row in set (0.01 sec) Part of the time it takes to query any table is the time it takes to load the correct index in from the disk so... maybe? you hardware could be too slow for this volume of information? I just can't say with any degree of confidence one way or the other yet. I did notice that this query returned in only about 11% of the time it took a similar query on the full dataset to return (6.87 seconds vs. 61 seconds a similar lookup on internet_usage). Better but not exactly linearly related to the reduction in the data size. I would think this is related to the fact that the time it takes to find records using a B-TREE index degrades roughly logarithmically. I thought about putting a HASH index on that column but those are only available to the MEMORY table type (bummer). BTW, if you and I take this thread back into the list everyone else will also have a chance to review your hardware specs and throw in their two cents, too! Shawn Green Database Administrator Unimin Corporation - Spruce Pine J S [EMAIL PROTECTED] wrote on 09/09/2004 04:48:47 AM: Hi Shawn, I'm working at the moment on the new database! I had a quick question for you. Here's my table structure: t20040908 | CREATE TABLE `t20040908` ( `uid` int(10) unsigned NOT NULL default '0', `time` timestamp(14) NOT NULL, `ip` int(10) unsigned NOT NULL default '0', `urlid` int(10) unsigned NOT NULL default '0', `timetaken` smallint(5) unsigned default '0', `cs_size` int(10) unsigned default '0', `sc_size` int(10)
Re: Moving Data
How fast do your disks rotate? What kind of disk controller are you using (ATA, SATA, SCSI, ...)? Are they in a RAID array or not. If they are what RAID scheme are you using? I had to ask about this one because I'm not too sure about these sort of things. Apparently the box is using ESS disks on SAN, and they're RAID 5. How big are your buffers and other working areas? (show variables like '%buff%'; show variables like '%size%';). mysql show variables like '%buff%'; +-+---+ | Variable_name | Value | +-+---+ | bdb_log_buffer_size | 262144| | bulk_insert_buffer_size | 8388608 | | innodb_buffer_pool_size | 8388608 | | innodb_log_buffer_size | 1048576 | | join_buffer_size| 131072| | key_buffer_size | 402653184 | | myisam_sort_buffer_size | 67108864 | | net_buffer_length | 16384 | | read_buffer_size| 2093056 | | read_rnd_buffer_size| 262144| | sort_buffer_size| 2097144 | +-+---+ mysql show variables like '%size%'; +-+--+ | Variable_name | Value| +-+--+ | bdb_cache_size | 8388600 | | bdb_log_buffer_size | 262144 | | binlog_cache_size | 32768| | bulk_insert_buffer_size | 8388608 | | delayed_queue_size | 1000 | | innodb_additional_mem_pool_size | 1048576 | | innodb_buffer_pool_size | 8388608 | | innodb_log_buffer_size | 1048576 | | innodb_log_file_size| 5242880 | | join_buffer_size| 131072 | | key_buffer_size | 402653184| | max_binlog_cache_size | 4294967295 | | max_binlog_size | 1073741824 | | max_heap_table_size | 16777216 | | max_join_size | 18446744073709551615 | | max_relay_log_size | 0| | myisam_max_extra_sort_file_size | 268435456| | myisam_max_sort_file_size | 2147483647 | | myisam_sort_buffer_size | 67108864 | | query_alloc_block_size | 8192 | | query_cache_size| 33554432 | | query_prealloc_size | 8192 | | range_alloc_block_size | 2048 | | read_buffer_size| 2093056 | | read_rnd_buffer_size| 262144 | | sort_buffer_size| 2097144 | | thread_cache_size | 8| | tmp_table_size | 33554432 | | transaction_alloc_block_size| 8192 | | transaction_prealloc_size | 4096 | +-+--+ 30 rows in set (0.01 sec) Your CPUs are probably up to the task but we need to make sure you are using as much available memory as you can. I see PowerPC in your system description... how fast is your system bus (or does anyone else think that would make that much of a difference here)? Not sure how to check the system bus but this is the info on the CPUs: RS/6000 pSeries Details CPU Architecture=PowerPC Implementation=RS64-III, 64 bit Machine has 2 CPUs (2 CPUs activated) CPU Level 1 Cache is Combined Instruction=131072 bytes Data=131072 bytes Level 2 Cache size=4194304 AIX 4.3.3.86 On another thoughtI was under the impression that you were trying to create a single daily table for testing purposes (hence the date in the table name) yet you seem to have 25M+ entries? Are you sure that's just one day's worth of information? In fact from the time you sent the earliest message in this post until you posted your table status, you added about 10M new rows. I was tailing the proxies from about lunchtime yesterday and piping the output into the database. By the end of the day there were 25m row. _ Want to block unwanted pop-ups? Download the free MSN Toolbar now! http://toolbar.msn.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Moving Data
J S wrote: How fast do your disks rotate? What kind of disk controller are you using (ATA, SATA, SCSI, ...)? Are they in a RAID array or not. If they are what RAID scheme are you using? I had to ask about this one because I'm not too sure about these sort of things. Apparently the box is using ESS disks on SAN, and they're RAID 5. See www.baarf.com for comments against RAID 5 Colin Bull -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Moving Data
SAN --- I think that's the key piece to this puzzle. It doesn't matter how fat your network pipes are, you will always encounter some network lag when reading or writing data from and to a SAN. There is an abstraction layer that must bundle your request to the disk then translate it from the device that will translate into additional CPU overhead PER DISK TRANSACTION. Most people won't notice the difference when they are moving their personal files but a database certainly does. The number of disk reads and writes per second normally handled by hardware translates into network round-trips per second when you are using a SAN. Multiply each round trip (thousands, millions?) by the average lag and you have one source of your slowdown. Add just 1 millisecond to each read request and it quickly adds up to several seconds per query for tables your size. I just became really pressed for time today so must I defer to others on the list to interpret your settings. Please everyone, pitch in on this one, OK? Thanks! Shawn Green Database Administrator Unimin Corporation - Spruce Pine J S [EMAIL PROTECTED] wrote on 09/10/2004 10:55:23 AM: How fast do your disks rotate? What kind of disk controller are you using (ATA, SATA, SCSI, ...)? Are they in a RAID array or not. If they are what RAID scheme are you using? I had to ask about this one because I'm not too sure about these sort of things. Apparently the box is using ESS disks on SAN, and they're RAID 5. How big are your buffers and other working areas? (show variables like '%buff%'; show variables like '%size%';). mysql show variables like '%buff%'; +-+---+ | Variable_name | Value | +-+---+ | bdb_log_buffer_size | 262144| | bulk_insert_buffer_size | 8388608 | | innodb_buffer_pool_size | 8388608 | | innodb_log_buffer_size | 1048576 | | join_buffer_size| 131072| | key_buffer_size | 402653184 | | myisam_sort_buffer_size | 67108864 | | net_buffer_length | 16384 | | read_buffer_size| 2093056 | | read_rnd_buffer_size| 262144| | sort_buffer_size| 2097144 | +-+---+ mysql show variables like '%size%'; +-+--+ | Variable_name | Value| +-+--+ | bdb_cache_size | 8388600 | | bdb_log_buffer_size | 262144 | | binlog_cache_size | 32768| | bulk_insert_buffer_size | 8388608 | | delayed_queue_size | 1000 | | innodb_additional_mem_pool_size | 1048576 | | innodb_buffer_pool_size | 8388608 | | innodb_log_buffer_size | 1048576 | | innodb_log_file_size| 5242880 | | join_buffer_size| 131072 | | key_buffer_size | 402653184| | max_binlog_cache_size | 4294967295 | | max_binlog_size | 1073741824 | | max_heap_table_size | 16777216 | | max_join_size | 18446744073709551615 | | max_relay_log_size | 0| | myisam_max_extra_sort_file_size | 268435456| | myisam_max_sort_file_size | 2147483647 | | myisam_sort_buffer_size | 67108864 | | query_alloc_block_size | 8192 | | query_cache_size| 33554432 | | query_prealloc_size | 8192 | | range_alloc_block_size | 2048 | | read_buffer_size| 2093056 | | read_rnd_buffer_size| 262144 | | sort_buffer_size| 2097144 | | thread_cache_size | 8| | tmp_table_size | 33554432 | | transaction_alloc_block_size| 8192 | | transaction_prealloc_size | 4096 | +-+--+ 30 rows in set (0.01 sec) Your CPUs are probably up to the task but we need to make sure you are using as much available memory as you can. I see PowerPC in your system description... how fast is your system bus (or does anyone else think that would make that much of a difference here)? Not sure how to check the system bus but this is the info on the CPUs: RS/6000 pSeries Details CPU Architecture=PowerPC Implementation=RS64-III, 64 bit Machine has 2 CPUs (2 CPUs activated) CPU Level 1 Cache is Combined Instruction=131072 bytes Data=131072 bytes Level 2 Cache size=4194304 AIX 4.3.3.86 On another
Re: moving data
I want to copy data from one table to another. However I need to do this carefully because I haven't got much of space left. I was thinking of maybe selecting data from every 10 rows of the old table, inserting it into the new table, then deleting those rows from the old table. Could someone help me out with the SQL for this please? Or tell me if there's a better way of doing this? CREATE TABLE new_able SELECT * FROM old_table; Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
moving data
Hi, I want to copy data from one table to another. However I need to do this carefully because I haven't got much of space left. I was thinking of maybe selecting data from every 10 rows of the old table, inserting it into the new table, then deleting those rows from the old table. Could someone help me out with the SQL for this please? Or tell me if there's a better way of doing this? Thanks, JS. _ Want to block unwanted pop-ups? Download the free MSN Toolbar now! http://toolbar.msn.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: moving data
J S wrote: Hi, I want to copy data from one table to another. However I need to do this carefully because I haven't got much of space left. I was thinking of maybe selecting data from every 10 rows of the old table, inserting it into the new table, then deleting those rows from the old table. This will not free up the disk space used by those 10 rows. Could someone help me out with the SQL for this please? Or tell me if there's a better way of doing this? Thanks, JS. _ Want to block unwanted pop-ups? Download the free MSN Toolbar now! http://toolbar.msn.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: moving data
A) 0. optionally: start transaction; 1. create temporary table mytemptable select * from mytable order by mytableprimarykey limit 10; 2. insert into destinationtable select * from mytemptable; 3. delete * from mytable order by mytableprimarykey limit 10; 4. drop mytemptable; 5. optionally: commit; 6. Goto step 0. if you don't want to play it save, then you can skip step 1 and insert your data straight into the destination table. or B) Study the MySQL Manual for the mysqldump's commandline parameters, to dump all your data to a file first. Then recreate your table on the database/table you wish. or C) You can use many of the GUI tools around most of which can backup your data to scripts or other databases/tables. Ex. http://www.scibit.com/products/mycon 0. Double click your table, sort and limit your data as you wish 1. Click Backup 2. Use the resulting script against your destination table. 3. Hit Ctrl+A in your table's grid and then Del to delete the select records and then hit Refresh to start again (if you have selected only a 100,000 batch for example). 4. Goto step 1. By default your resulting script will contain records batched 100 per insert statement and thus for a 100,000 records you will only have a 1000 insert statements. This will of course execute much faster than 100,000 separate insert statements. It would be highly recommended to use an intermediate local dump script file (if your space problem is not a concern for your local computer), that way you have all your data backed up for just in case. Kind Regards SciBit MySQL Team http://www.scibit.com -Original Message- From: J S [EMAIL PROTECTED] To: [EMAIL PROTECTED] [EMAIL PROTECTED] CC: Subject: moving data Sent: Thu, 02 Sep 2004 12:38:30 GMT Received: Thu, 02 Sep 2004 12:40:48 GMT Read: Thu, 02 Sep 2004 13:23:07 GMT Hi, I want to copy data from one table to another. However I need to do this carefully because I haven't got much of space left. I was thinking of maybe selecting data from every 10 rows of the old table, inserting it into the new table, then deleting those rows from the old table. Could someone help me out with the SQL for this please? Or tell me if there's a better way of doing this? Thanks, JS. _ Want to block unwanted pop-ups? Download the free MSN Toolbar now! http://toolbar.msn.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Moving data files
Does anyone know of a good step-by-step document demonstrating moving the data files of a 3.23.5x installation (5 databases, each with several tables) to a new partition on the same Linux box. I want to do this before upgrading to MySQL 4. I think I understand how to do it from reading the docs, but would like a more concrete example. Thanks * Jon Rosenberg www.DeanForAmerica.com www.OhioForDean.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Dumb Question - Moving Data from Access to MySQL
Jeremy Proffitt wrote: Dumb Question. I need to move my data from their current access database to my new MySQL server through an ODBC connection. The tables on both ends are set up identially. I have added a linked table to the access database called EXPARTAB1. The data is in EXPARTAB. I tried: INSERT INTO EXPARTAB1 VALUES (SELECT * FROM EXPARTAB) That should be: insert into EXPARTAB1 select * from EXPARTAB or maybe you should do something like: insert into destination_tablename ( field_1, field_2 ) select field_1, field_2 from source_tablename You'll have to do it the 2nd way if you change any field names. This is the most reliable way to shift data from one database to the other ( via Access ). You can use scripts, but you have to rely on the author of the script setting the data types correctly. Setup your tables yourself, making sure all your destination fields can accommodate your data, and the make a set of queries in Access ( access calls them 'append queries' ). If there is a problem, Access will usually let you know where it occured so you can go and fix it. -- Daniel Kasak IT Developer * NUS Consulting Group* Level 18, 168 Walker Street North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Dumb Question - Moving Data from Access to MySQL
Dumb Question. I need to move my data from their current access database to my new MySQL server through an ODBC connection. The tables on both ends are set up identially. I have added a linked table to the access database called EXPARTAB1. The data is in EXPARTAB. I tried: INSERT INTO EXPARTAB1 VALUES (SELECT * FROM EXPARTAB) I have used exportSQL from CYNERGI which is a very simple script for access, but the file it creates is 125 Megs to add the data, and I can't easily work with that. Ugh! And I can't just UNLOAD/LOAD it all to a file like I did back in my Informix Days. It's gotta be simple, but this had been beating me up all day. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Moving Data Between Proprietary Database and MySQL
Hello,
Because most web providers will not allow
direct TCP/IP access to
MySQL servers, I am considering the following
strategy:
a) Save the proprietary database to a file
containing a series of SQL
CREATE TABLE and INSERT INTO calls.
b) Upload the SQL file to the server via FTP.
c) Launch a PHP script on the web server from
my program that executes
the SQL file to insert the data into the MySQL
database.
Have you tried phpMyAdmin? It's a web-based
administration tool written in PHP to handle mysql
databases.
Check http://www.phpwizard.net/projects/phpMyAdmin/
Another issue I am running into is the
difference between the
proprietary database and MySQL in how quotes
are escaped. The
proprietary database wants two sequential quote
characters, MySQL uses
a backslash before the quote character.
You can do this with a simple search replace. Does
the other database provides any means to do it in the
query itself? i.e., in mysql you can query like this:
SELECT replace(some_field, ',', '.') FROM some_table
This query substitutes commas (,) for periods (.) for
some_field.
If you'd like to loose your sanity, you could start
having lots of fun with strings, like this:
SELECT concat('INSERT INTO TABLE_X (field_1) VALUES
(''', replace(some_field, ',', '.'), ''');');
and you can send the result to a text file. this way
you are building a script using a query.
Hope this helps!
Has anyone else gone down this road before? Any
comments on this
strategy? Are there any tools or routines
available to help facilitate
this process?
--
Bruce Vander Werf
[EMAIL PROTECTED]
-
Please check
http://www.mysql.com/Manual_chapter/manual_toc.html;
before
posting. To request this thread, e-mail
[EMAIL PROTECTED]
To unsubscribe, send a message to the address
shown in the
List-Unsubscribe header of this message. If you
cannot see it,
e-mail [EMAIL PROTECTED]
instead.
~avalon~
-
Email Enviado utilizando o serviço MegaMail
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Moving Data Between Proprietary Database and MySQL
I am adding a web interface to a Windows application I have written that uses another proprietary database system. To allow for my customers to use both Linux and WinNT based web servers, I have settled on MySQL for the web database. I am working on a strategy for allowing my customers to move the database from one platform to the other as needed. Here are the issues I am facing at this point: Because most web providers will not allow direct TCP/IP access to MySQL servers, I am considering the following strategy: a) Save the proprietary database to a file containing a series of SQL CREATE TABLE and INSERT INTO calls. b) Upload the SQL file to the server via FTP. c) Launch a PHP script on the web server from my program that executes the SQL file to insert the data into the MySQL database. Going the other way would be similar: a) Launch a PHP script on the web server from my program to save the MySQL database to a file containing a series of SQL CREATE TABLE and INSERT INTO calls. b) Download the SQL file from the server via FTP. c) Execute the SQL file in my program to insert the data into the proprietary database. At this point, it's going to be a wholesale send and replace of the whole database. At some point in the future, I would like to add some synchronization logic, but that is a subject for another day. Another issue I am running into is the difference between the proprietary database and MySQL in how quotes are escaped. The proprietary database wants two sequential quote characters, MySQL uses a backslash before the quote character. Has anyone else gone down this road before? Any comments on this strategy? Are there any tools or routines available to help facilitate this process? -- Bruce Vander Werf [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Moving data in table from mysql server A to server B
If I have mysql on server A and an identical version on server B, what is the easiest way to move data in Table C from server A to server B? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Moving data in table from mysql server A to server B
* Kemp Randy-W18971 If I have mysql on server A and an identical version on server B, what is the easiest way to move data in Table C from server A to server B? Asuming your databases are stored in /usr/local/mysql/var/ and the relevant database is named database: From Server A: scp /usr/local/mysql/var/database/Table_C.* Server_B:/usr/local/mysql/var/database/. If the data is big and the connection is slow, you may want to compress the data before you transfer it. -- Roger - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Moving data from Filemaker to MySQL
Hi all, I wish to migrate some Filemaker 5 databases over to MySQL on NT. Some of the databases have hundreds of fields, so it would be great if I could export in a format that I can just import without having to pre-set all the fields. Any experience/suggestions? George Pitcher in Edinburgh _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Moving Data
We will be moving our MySql databases from a Winnt server to a linux server. Can I just copy the data files from one machine to the other? If not then how should I do it? Mat Murdock Your mouse has moved. Windows must be restarted for the change to take effect. Reboot now? [ OK ]
Re: Moving Data
It depends on what version of MySQL you are running. MyISAM tables will move across platforms fine, but ISAM tables will not. You could always choose the failsafe and use mysqldump to dump the contents of your database into a file, and then use that to reload the contents on the new machine. John Barton Unix Systems Administrator Primary Networks, Inc. [EMAIL PROTECTED] On Mon, 9 Apr 2001, Mat Murdock wrote: We will be moving our MySql databases from a Winnt server to a linux server. Can I just copy the data files from one machine to the other? If not then how should I do it? Mat Murdock Your mouse has moved. Windows must be restarted for the change to take effect. Reboot now? [ OK ] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
