In the first two articles in this series, I discussed archiving basics, relationships and dependencies, and specific archiving techniques for online transaction processing (OLTP) database servers. This article covers how to move the data from the OLTP source to the archive destination, what the archive destination might look like, and how to un-archive data. If you can un-archive easily and reliably, a whole new world of possibilities opens up.
« 10 Newer Entries
Here is what I did to get it operational under CentOS 4.3.
su - useradd pbxt su - pbxt wget http://www.primebase.com/xt/download/mysql-4.1.16-pbxt-0.9.5.tar.gz tar xvfz mysql-4.1.16-pbxt-0.9.5.tar.gz cd mysql-4.1.16-pbxt-0.9.5 ./configure --with-debug=full --with-pbxt-storage-engine --without-query-cache --with-mysqld-user=pbxt --prefix=/home/pbxt/mysql make make install scripts/mysql_install_db cd /home/pbxt/mysql ./bin/mysqld_safe --user=pbxt --basedir=/home/pbxt/mysql &
Now, lets test and confirm PBXT is operational.
bin/mysql -uroot mysql Reading table information for completion of table and column names You can turn off this …[Read more]
Here are some steps involved. Using the current MySQL defacto engine InnoDB. Of course, Falcon, PBXT and others will enable alternative engines to be used.
Convert Table Storage Engine Types
$ mysql -u[user] -p[password] [database] -e "SHOW TABLES" | grep -v "Tables_in" | sed -e "s/^/ALTER TABLE /" | sed -e "s/$/ ENGINE=InnoDB;/" > upgrade.sql $ mysql -u[user] -p[password] [database] < upgrade.sql
NOTE: This may not work for all tables, for example those with FULLTEXT indexes will fail.
For the introduction of Referential Integrity we need to ensure the following.
- Each Foreign Key column should have an index. Prior to 4.1 I think this was a requirement, however it’s a good general practice regardless for SQL performance.
- The datatype must match between Primary Key and Foreign Keys. The most obvious oversight is normally UNSIGNED, however you also for example have INT …
Round 2. Question 2? From the MySQL Quiz Show. (you had to be there)
« 10 Newer Entries