Tuesday, November 22, 2005

MySQL 5: Transferring database from MySQL 3.23.XX

Earlier, when I wrote about upgrading to MySQL 5, I didn't transfer any databases from MySQL 3.23.56 to MySQL 5 because I was going to use the new MySQL 5 installation as a client and load all old tables to MySQL 5 server on a separate machine. Today I needed to transfer some tables from MySQL 3.23.56 to new MySQL 5 (on the same machine) so here I am writing this post.

First, I copied my database directory

cp -p -r /drive2/oldsdd4/mysql/mydb /var/lib/mysql/


Once the MySQL database directory has been copied, you may need to fix permissions (see below).

Once the database directory has been moved, the following command can be used to dump the contents of the database.

mysqldump -uroot -p"mypass" mydb > mydb.sql


To load data back, use the following

mysqlimport -uroot -p"mypass" mydb mydb.sql



Fixing Permissions
I was getting the following error:

PHP Fatal error: Table 'mytable' is read only! query: INSERT INTO mytable (s, h, t) values('MySecretSession', '192.168.0.1', 1132694778) in /var/www/html/domain.com/includes/database.mysql.inc on line 120



I used the following to fix the error

chmod -R u=rwx,g=rwx,o=rx /var/lib/mysql/mydb
chown -R mysql:mysql /var/lib/mysql/mydb



You can use the following to view the status of the table

SHOW TABLE STATUS LIKE 'sessions' \G



Remember to make a backup :)

Good luck!


Also see: Upgrading MySQL,
upgrading tables from 4.0, upgrading tables from 3.23
Technorati:

To buy: MYSQL 5 Definitve Guide by Michael Kofler

Thursday, November 17, 2005

Open Source Patents Search by Patent Commons - eBay Launches Developers Challenge

Open Source Patents Search
Supported by Computer Associates, Novell, IBM, Sun, Red Hat and others, OSDL (Open Source Development Labs has launched a patents search platform to allow open source developers to search patents that have been donated to the open source community.



From PatentCommons website:
The Patent Commons Project is dedicated to documenting the boundaries of The Commons -- a preserve where developers and users of software can innovate, collaborate, and access patent resources in an environment of enhanced safety, protected by pledges of support made by holders of software patents

Yet Another Blog by Microsoft CTO
Ray Ozzie, CTO of Microsoft, has started yet another blog. He first began blogging in August of 2002. In his opening post, he writes:

As in the past, it's not my intent to be pitching our products here. We've got plenty of mechanisms - old school and new - that work well for that sort of thing. But to the extent that I'm excited about something, or I think there might be a different angle that you might be interested in, I'll chime in.
...
and
...
At times there will be controversies I just can't or won't engage in.


I am sure some people want Bill Gates to start blogging too by now. Also see, Microsoft to enter supercomputer market.

eBay Developers Program Now Free!
eBay recently introduced changes to the pricing structure for the eBay
Developers Program. According to the recent email I received:


We are excited today to introduce changes to the pricing structure for the eBay
Developers Program that will make it even easier to innovate on the eBay platform.
Those include:

- Membership - now free, effective November 14, 2005
- eBay Web Services/ API calls - now free, effective November 14, 2005
- Standard application certification - now free effective, November 14, 2005

And Developer Live Chat technical support is now available to every member of the
program at one low cost - $75/hour or $250 for four (4) hours.

...
Why are offering this new free pricing to our members? First we want to thank you
for your contributions to the eBay marketplace, because we know that without our
developer community eBay wouldn’t be where we are today. Second, we want to remove
barriers to innovation on the platform. Since the first API was released in 2000,
third party developers have led the charge in developing the most cutting edge and
exciting applications and services for the eBay community.


Its all good from eBay but I would have loved it more if they would have done this just a few months ago, when I started building an interface for eBay using MySQL, PHP and AJAX. I could've saved $250.

If you are an experienced MySQL DBA, I encourage you to join the eBay developer program. If you are a webmaster, consider joining the affiliate program first through CJ and then signing up for the eBay developer program. In addition to earning $12-$20 per active user registration, you can get a good percentage of fees collected by eBay. UPS and eBay also recently announced that they are joining forces to allow US based eBay sellers to ship globally.

eBay Developers Challenge
Greg Isaacs, Director of eBay Developers Program announced the beginning of a developers challenge by eBay. He writes:
Now that most barriers to development have been removed, please consider
participating in the eBay Developers Challenge at
http://developer.ebay.com/challenge2006 to win valuable prizes, including a $5,000 cash prize. The contest starts today!



Technorati Tags:

Google Base Live! - Apache Server Status

Earlier, I posted about GoogleSQL and Google Base being a threat to MySQL. On Tuesday night, Google announced on its blog that Google Base is now live and ready for users content. Some are calling it a threat to small businesses, while others think of it as an opportunity. More on Google Base going live from Google Blog. Also see: Google Base on SMA

If you use Apache, you can turn on server-status and extended status information to get some useful information. Also see: Optimize Apache and using clickstream module

Google Base | MySQL | GoogleSQL

Wednesday, November 16, 2005

MySQL 5 Optimization and Tuning Guide

Earlier, I mentioned some MySQL optimization resources. Here is the guide on optimizing sysctl.conf. Today, I will be optimizing MySQL 5.0.15 on a Red Hat Enterprise Linux ES release 3 with dual Xeon 2GHz processors and 2GB RAM.


Note: Please use this work-in-progress guide for reference only and at your own risk. I am creating this guide on a development server and will not be responsible if anything bad happens. If you find something is incorrect or can be made better please let me know by adding a comment to my blog (mysqldatabaseadministration)



my.cnf: If you have installed or upgraded to MySQL 5, chances are that you may not find the /etc/my.conf file. Don't worry as there are several sample configuration files that come with MySQL. You can invoke
locate my-large.cnf
to find the sample configuration file for MySQL 5 on your server. By default, on Red Hat Enterprise, it can be located at the following location.
/usr/share/doc/MySQL-server-standard-5.0.15/my-large.cnf


Setting MySQL 5 global options:
To set global MySQL5 options, we will need to copy my-large.cnf to /etc/my.cnf

Setting MySQL 5 server-specific options:
By default, MySQL5 data directory is /var/lib/mysql. To set server-specific options for MySQL5, we can move the sample MySQL configuration file to our data directory.

For our MySQL5 optimization and tuning tutorial, we will be setting global MySQL5 configurations by copying the my.cnf file to /etc/ directory. In case, you are waiting for me to give you the command:
cp -p /usr/share/doc/MySQL-server-standard-5.0.15/my-large.cnf /etc/my.cnf
Getting information about current values:
We can obtain information about the currently set values by running the following command. Abbreviated output is displayed for reference.

# mysqladmin variables -uroot -p"PASS"

Output from a development server

+---------------------------------+------------------------------------------+
| Variable_name | Value |
+---------------------------------+------------------------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| automatic_sp_privileges | ON |
| back_log | 50 |
| basedir | / |
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
| completion_type | 0 |
| concurrent_insert | 1 |
| connect_timeout | 5 |
| datadir | /var/lib/mysql/ |
| date_format | %Y-%m-%d |
| datetime_format | %Y-%m-%d %H:%i:%s |
| default_week_format | 0 |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| div_precision_increment | 4 |
| engine_condition_pushdown | OFF |
| expire_logs_days | 0 |
| flush | OFF |
| flush_time | 0 |
| ft_boolean_syntax | + -><()~*:""&| |
| ft_max_word_len | 84 |
| ft_min_word_len | 4 |
| ft_query_expansion_limit | 20 |
| ft_stopword_file | (built-in) |
| group_concat_max_len | 1024 |
| have_archive | YES |
| have_bdb | NO |
| have_blackhole_engine | NO |
| have_compress | YES |
| have_crypt | YES |
| have_csv | NO |
| have_example_engine | NO |
| have_federated_engine | NO |
| have_geometry | YES |
| have_innodb | YES |
| have_isam | NO |
| have_ndbcluster | NO |
| have_openssl | NO |
| have_query_cache | YES |
| have_raid | NO |
| have_rtree_keys | YES |
| have_symlink | YES |
| init_connect | |
| init_file | |
| init_slave | |
| innodb_additional_mem_pool_size | 1048576 |
| innodb_autoextend_increment | 8 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 8388608 |
| innodb_checksums | ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir | |
| innodb_doublewrite | ON |
| innodb_fast_shutdown | 1 |
| innodb_file_io_threads | 4 |
| innodb_file_per_table | OFF |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | |
| innodb_force_recovery | 0 |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_arch_dir | |
| innodb_log_archive | OFF |
| innodb_log_buffer_size | 1048576 |
| innodb_log_file_size | 5242880 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_max_dirty_pages_pct | 90 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_open_files | 300 |
| innodb_support_xa | ON |
| innodb_sync_spin_loops | 20 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 20 |
| innodb_thread_sleep_delay | 10000 |
| interactive_timeout | 28800 |
| join_buffer_size | 131072 |
| key_buffer_size | 8388600 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| language | /usr/share/mysql/english/ |
| large_files_support | ON |
| large_page_size | 0 |
| large_pages | OFF |
| license | GPL |
| local_infile | ON |
| locked_in_memory | OFF |
| log | OFF |
| log_bin | OFF |
| log_bin_trust_routine_creators | OFF |
| log_error | |
| log_slave_updates | OFF |
| log_slow_queries | OFF |
| log_warnings | 1 |
| long_query_time | 10 |
| low_priority_updates | OFF |
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
| max_allowed_packet | 1048576 |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_connect_errors | 10 |
| max_connections | 100 |
| max_delayed_threads | 20 |
| max_error_count | 64 |
| max_heap_table_size | 16777216 |
| max_insert_delayed_threads | 20 |
| max_join_size | 18446744073709551615 |
| max_length_for_sort_data | 1024 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 4294967295 |
| max_sort_length | 1024 |
| max_tmp_tables | 32 |
| max_user_connections | 0 |
| max_write_lock_count | 4294967295 |
| multi_range_count | 256 |
| myisam_data_pointer_size | 6 |
| myisam_max_sort_file_size | 2147483647 |
| myisam_recover_options | OFF |
| myisam_repair_threads | 1 |
| myisam_sort_buffer_size | 8388608 |
| myisam_stats_method | nulls_unequal |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| old_passwords | OFF |
| open_files_limit | 1024 |
| optimizer_prune_level | 1 |
| optimizer_search_depth | 62 |
| pid_file | server.net.pid |
| port | 3306 |
| preload_buffer_size | 32768 |
| protocol_version | 10 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 0 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| range_alloc_block_size | 2048 |
| read_buffer_size | 131072 |
| read_only | OFF |
| read_rnd_buffer_size | 262144 |
| relay_log_purge | ON |
| relay_log_space_limit | 0 |
| rpl_recovery_rank | 0 |
| secure_auth | OFF |
| server_id | 0 |
| skip_external_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slave_compressed_protocol | OFF |
| slave_load_tmpdir | /tmp/ |
| slave_net_timeout | 3600 |
| slave_skip_errors | OFF |
| slave_transaction_retries | 10 |
| slow_launch_time | 2 |
| socket | /var/lib/mysql/mysql.sock |
| sort_buffer_size | 2097144 |
| sql_mode | |
| sql_notes | ON |
| sql_warnings | ON |
| storage_engine | MyISAM |
| sync_binlog | 0 |
| sync_frm | ON |
| sync_replication | 0 |
| sync_replication_slave_id | 0 |
| sync_replication_timeout | 10 |
| system_time_zone | CST |
| table_cache | 64 |
| table_lock_wait_timeout | 50 |
| table_type | MyISAM |
| thread_cache_size | 0 |
| thread_stack | 196608 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| tmp_table_size | 33554432 |
| tmpdir | |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ |
| updatable_views_with_limit | YES |
| version | 5.0.15-standard |
| version_comment | MySQL Community Edition - Standard (GPL) |
| version_compile_machine | i686 |
| version_compile_os | pc-linux-gnu |
| wait_timeout | 28800 |
+---------------------------------+------------------------------------------+


Other commands to get MySQL status information, when connected to a MySQL server
SHOW VARIABLES;
SHOW STATUS;
On shell prompt
mysqladmin variables
mysqladmin extended-status


Decrease the value of connect_timeout: By default, the wait_timeout value is 28800. Unless you want MySQL to keep waiting for 28800 seconds (480 minutes or 8 hours), please decrease its value according to your application needs. On the test server, I have the wait_timeout set to 60.

Open the file /etc/my.cnf and under [mysqld], place a line similar to following

wait_timeout=60
Increase connect_timeout from 5 to 10
connect_timeout=10
Decrease interactive_timeout from 28800 to 100
interactive_timeout=120
Increase join_buffer_size from 131072 to 1M
join_buffer_size=1M
Increase query_cache_size from 0 to 128M
query_cache_size=128M
Increase query cache limit from 1048576 to 2M
query_cache_limit=2M
Increase max allowed packet size from 1M to 16M
max_allowed_packet=16M
Increase table cache cache from 256 to 1024
table_cache=1024
Increase sort buffer size from 1M
sort_buffer_size=2M
Increase read buffer size from 1M
read_buffer_size=2M
Increase read_rnd_buffer_size to 4M
read_rnd_buffer_size=4M
Other settings

sort_buffer_size=8M
key_buffer = 256M
key_buffer_size=64M



MySQL Query Cache
To maximize use of MySQL query cache, similar queries used by an application should be identical, i.e. their casing must mache. From MySQL:

Queries are compared before parsing, so the following two queries are regarded as different by the query cache:

SELECT * FROM tbl_name
Select * from tbl_name

Queries must be exactly the same (byte for byte) to be seen as identical. In addition, query strings that are identical may be treated as different for other reasons. Queries that use different databases, different protocol versions, or different default character sets are considered different queries and are cached separately.

When is a query not be cached?
From MySQL documentation (Full list of when a MySQL query is not cached by MySQL server):

A query cannot be cached if it contains any of the following functions:

BENCHMARK() CONNECTION_ID() CURDATE()
CURRENT_DATE() CURRENT_TIME() CURRENT_TIMESTAMP()
CURTIME() DATABASE() ENCRYPT() with one parameter
FOUND_ROWS() GET_LOCK() LAST_INSERT_ID()
LOAD_FILE() MASTER_POS_WAIT() NOW()
RAND() RELEASE_LOCK() SYSDATE()
UNIX_TIMESTAMP() with no parameters USER()

How can you "tell" MySQL server to cache a query?
If query_cache_type system variable on your MySQL server is set to ON or DEMAND, you can specify SQL_CACHE in your query to tell MySQL server explicity to use query cache for this query. An example will look something like

SELECT SQL_CACHE ... ;

In contrast, tell MySQL 5 server not to cache your query by using SQL_NO_CACHE in your SQL statements.

SELECT SQL_NO_CACHE ... ;
Please note that a user on dev.mysql.com pointed out the following,

Using SQL_NO_CACHE does not bypass the cache mechanism, so it is not a
reliable way to time query speed. All it does is tell the server not to
cache the results of the specific query you're sending.

If you've already executed the query without the option, some other time, that cached result will be fetched.
Another user, Uwe, pointed out the following

To completely disable reading (AND writing) from/to cache use a comment at the beginning of the query. MySQL's ignoring the query cache if the query doesn't start with "select" (at least up to 4.1).

/* disable qcache */ SELECT * FROM tbl WHERE 1

This is very useful to test query performance in a running environment as you can't be sure that the query's not already in the cache.


How to optimize disk seek time on your MySQL 5 server?
To optimize disk seek time on your server, you can try distributing the data onto more than one hard disk drive.

Using BENCHMARK() function (Output shows 10000000 simple additions can be performed in 0.93 seconds)

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 558 to server version: 5.0.15-standard

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT BENCHMARK(1000000,1+1);
+------------------------+
| BENCHMARK(1000000,1+1) |
+------------------------+
| 0 |
+------------------------+
1 row in set (0.07 sec)

mysql> SELECT BENCHMARK(10000000,1+1);
+-------------------------+
| BENCHMARK(10000000,1+1) |
+-------------------------+
| 0 |
+-------------------------+
1 row in set (0.93 sec)

Estimating the performance by counting disk seeks
According to MySQL, using B-tree indexes, you need this many seeks to find a row:
log(row_count) / log(index_block_length / 3 * 2 / (index_length + data_pointer_length)) + 1
Note that in MySQL, an index block is normally 1K (1024 bytes) . The data pointer is usually 4 bytes. According to MySQL,
For a 500,000-row table with an index length of 3 bytes (medium integer), the formula indicates log(500,000)/log(1024/3*2/(3+4)) + 1 = 4 seeks. This index would require storage of about 500,000 * 7 * 3/2 = 5.2MB (assuming a typical index buffer fill ratio of 2/3), so you probably have much of the index in memory and so need only one or two calls to read data to find the row.
For more see MySQL optimization.

Other Notes
Be aware that in case your server's CPU needs more data than can fit in the CPU cache, main memory bandwidth can become a bottleneck.

You can use crash-me to "find functions, types, and limits that you can use with a selection of database servers. crash-me does not check for every possible feature, but it is still reasonably comprehensive, performing about 450 tests." - MySQL

more complex MySQL permissions setup = more overhead


Resources:
Index Merge Optimization
Custom Benchmarks, Super-Smack (can bring a system to its knees)
MySQL and 50GB of transaction tables and 200GB of other customer data
Storage Engines and Table Types
MySQL Optimization
MySQL Query Cache
MySQL in batch mode, Foreign Keys, Searching on Two Keys, MySQL Program Options,
MySQL Instance Manager (Connecting to MySQL Instance Manager and Creating User Accounts) - serves for monitoring and management of MySQL Database Server instances
The typical startup/shutdown cycle for a MySQL server with the MySQL Instance Manager enabled is as follows:

* The MySQL Instance Manager is started with /etc/init.d/mysql script.
* The MySQL Instance Manager starts all instances and monitors them.
* If a server instance fails the MySQL Instance Manager restarts it.
* If the MySQL Instance Manager is shut down (for instance with the /etc/init.d/mysql stop command), all instances are shut down by the MySQL Instance Manager.


MySQL Installation Layouts


Side note: Google recently relaunched Urchin web analytics software as Google Analytics for advertisers and publishers. Details on Search Marketing Ambassador and here. Also, if you are a webmaster in addition to being a MySQL DBA, you can get $50 in credit from Yahoo! Search Marketing for sponsored search. Also see information on Google and Yahoo! sitemaps

Tags:

Thursday, November 10, 2005

TurboDbAdmin - AJAX based PHPMyAdmin on Steroids?

Call it PHPMyAdmin on steroids, or just an online database administration tool that happens to be AJAX based. TurboDbAdmin is one of the coolest AJAX based FREE application-of-the-year for MySQL database administrators. The good news, for those of you who are still using MySQL 4 or MySQL 5, is that minimum requirements for TurboDbAdmin by Turbo AJAX is MySQL 3 or higher with PHP 4 or higher installed.

Some highlights:
- Data is efficiently retrieved from the server so you don't have to wait. Columns are sortable.
- Real-time editing of table data: edits are automatically saved to your database server.
- Add, delete, backup, and rename databases. MySQL servers supported

For those of you still unfamiliar with , here is a quote from Wikipedia

Asynchronous JavaScript and XML, or Ajax, is a web development technique for creating interactive web applications using a combination of:

* XHTML (or HTML) and CSS for presenting information
* The Document Object Model manipulated through JavaScript to dynamically display and interact with the information presented
* The XMLHttpRequest object to exchange data asynchronously with the web server. (XML is commonly used, although any format will work, including preformatted HTML, plain text, JSON and even EBML)

Like DHTML, LAMP, or SPA, Ajax is not a technology in itself, but a term that refers to the use of a group of technologies together. In fact, derivative/composite technologies based substantially upon Ajax, such as AFLAX, are already appearing.


Some features such as executing MySQL queries on the server are still in development according to the website. A Live demo of TurboDbAdmin for MySQL is available at http://www.turboajax.com/turbodbadmin_demo

Other AJAX articles:
Faeriebell: AJAX Buzz - AJAX and PHP

Technorati Tags:

Gain a robust analysis platform by utilizing MySQL with the Pentaho OLAP Server

Pentaho Corporation, a company providing a full spectrum of open source Business Intelligence (BI) tools from reporting and analysis to dashboards to data mining, today announced in a press release that it's customers
"now have the opportunity to gain a robust analysis platform at a remarkably low cost by utilizing open source relational databases such as MySQL together with the Pentaho On-Line Analytical Processing (OLAP) Server."

Mondrian project will become a permanent feature of the Pentaho Business Intelligence (BI) project. The Business Intelligence project provides comprehensive reporting, analysis, dashboards, workflow, and data mining capabilities.

Mondrian Server was initially released in 2002. According to the press release:
Mondrian OLAP Server supports the aggregation, calculation and categorization of data from standard relational databases, including Oracle, , Microsoft Server, IBM DB2, and others. Mondrian uses the world's most popular Java integrated development environment - Eclipse - as the graphical editor for defining and editing OLAP models. It also implements the industry standard OLAP query language - MDX - and works with both the for Analysis and specifications.


More information on Pentaho is available on http://www.pentaho.org/

MySQL, Apache, PHP - Tuning and Optimizing web server

I have been receiving many requests on writing a detailed server optimization guide. Unfortunately, at the moment I am quite busy with work. However, while I keep you guys waiting, keep watching this post as I will post some of the available MySQL and Apache optimization resources.

If you know of a good tutorial on tuning MySQL and Apache, please add it by adding your comments.

Thank you
Frank Mash

EV1 Forums: Four Primary Areas (MySQL, Apache, sysctl.conf, PHP) for Optimizing your server
Jeremy Zawodny: MySQL Tuning Advice
MySQL: MySQL Tuning and Optimization/Optimisation Tips
Linux Magazine: MySQL Performance Tuning (Registration required)

Wednesday, November 09, 2005

First Google Base and Now Google Automat?

Earlier, I reported about Google Base. Now comes an even bigger news. Search Marketing Ambassador is reporting that Peter M. Zollman, Founding Principal of Classified Intelligence said:
"It's crystal clear is planning for an all-out move into classified advertising. These patent filings and the disclosure of Base a few weeks ago show the company is actively preparing to offer free listings for cars, homes, jobs and 'stuff,' even for merchants, among its services."

I read a report earlier about an investment firm analyst predicting that Google Automat has huge potential. Watch out eBay and every auctions and classifieds site. Here comes Google.

Sunday, November 06, 2005

MySQL - Creating multiple tables in multiple databases

I was recently asked to help create multiple MySQL tables on a MySQL 4 server using a MySQL 5 client.

This post will show you how you can create multiple tables in multiple databases using our database of choice, MySQL.

Setup a shell script to execute the query. Assume that the first argument passed is the database, indicated by $db.


# CODE FOR synch_db.sh
db=$1;
mysql -uuser -ppass --host=192.168.0.1 -e 'CREATE TABLE IF NOT EXISTS '$db'.table_name ( id int(11) NOT NULL auto_increment, alias varchar(80) default NULL, tag varchar(80) default NULL, mapping int(1) default 0, PRIMARY KEY (id)) TYPE=MyISAM;'

mysql -uuser -ppass --host=192.168.0.1 -e 'CREATE TABLE IF NOT EXISTS '$db'.table_name2 ( id int(11) NOT NULL auto_increment, alias varchar(80) default NULL, tag varchar(80) default NULL, mapping int(1) default 0, PRIMARY KEY (id)) TYPE=MyISAM;'




Create a CSV file containing database names to which you want changes to be applied. Following is how our CSV looks like. In our case the CSV is semi-colon separated and the 8th field containing the database name.
domain.com;field1;field2;....field7;database_name

And finally we can query the CSV file, extract the database name and run our shell script to create tables across all databases.


cat merged.csv | cut -d ";" -f 8 | while read i ; do sh synch_db.sh $i; done;



Please, add your questions and comments.