SQL Buddy – Web based MySQL administration.
SQL Buddy is my favorite web-based tool for accessing and managing MySQL databases.
PHPMyAdmin alternative – SQL Buddy Rocks!
mysql
Best SVN / CVS client plus project and bug tracking solution
I’ve tried a ton. This is the one package that will keep your code moving forward and deliver your project on time
RDBMS Database Normalization Vs. DeNormalization for speed and performance
the new hot shot coder / friend (he really is a solid coder) was calling me out on my database architecture. why do you have redundant data, it’ll slow the database down. you don’t need the data in this table if it’s connected through a join. i was telling him, for display logic, it’s faster to avoid the CPU + Disk hit to fetch/query a join.
On Jun 1, 2011, at 1:19 AM, Xxxx Xxxxxxxx wrote:
What you know about Boyce Codd Normal form.
http://en.wikipedia.org/wiki/Database_normalization
My response:
yeh yeh… i was a systems major in cawlige. i’ve been building production DB driven sites for 10+ years…
i do follow RDBMS and normalization. i would argue against normalization at all times. definitely using a 1/0 bit is faster than looking through a one to one or many to one relationship because you’ll take a disk + cpu hit.
one nice thing about the INNODB builds the indexing and relationships and stores them in memory. this requires lots of memory, but makes shit faster for the relationship query.
this guy pretty much sums up my thoughts…
Database Denormalization – What is it?
Rookie database administrators are taught in school to always keep their databases normalized. A normalized database is one where you minimize duplication of data by keeping groups of data in separate tables and linking (using indexes) between the tables.
For example, as part of the SoftwareProjects CRM application, we store customer contact information in a ‘customers‘ table, and financial transactions in a ‘transactions‘ table. Both tables are keyed to a ‘customer_id‘, allowing easy lookup of all transactions belonging to an individual customer.
Whenever we take on new clients, we typically find clean, normalized tables with multiple indexes connecting tables together. At first glance the concept of database normalization makes perfect sense – it’s clean, easy to understand and makes it easy to keep data is in sync.
In the real world of high traffic websites and users demanding sub-second response times, database normalization is a fatal mistake.
A de-normalized database is one where you keeps redundant frequently-accessed data, duplicated across several tables, making it possible to fulfill a user request without having to look at more than a single table.
Remember: The key to high performance database access is sticking to single-table SELECT queries with short indexes.
————————————————————————————————-
with pretty good discussions below.
http://www.mysqlperformanceblog.com/2008/12/22/high-performance-click-analysis-with-mysql/
MySQL SSH Export and Import Database in Shell Terminal
In your favorite shell you can export a MySQL database and Import using the following.
on Ubuntu, my MySQL databases are located at
/var/lib/mysql/database_name
To Export a MySQL Database run:
mysqldump -v -u username -p database_name > dumpfile.sql
To Import a MySQL Database run:
mysql -v -u username -p database_name < dumpfile.sql
MySQL 5 move data directory (datadir) to new mount RAID location on UBUNTU 10
i setup a RAID 5 system for our mysql database and wanted to move it over on ubuntu 10.
MOVE MYSQL
first stop mysql
service mysql stop
i’m paranoid, so i check the status
service mysql status
copy your MySQL database files to the new location. in this instance my RAID drive is located at:
/media/speedy/mysql
let’s move database:
mv /var/lib/mysql /media/speedy/mysql
change ownership/group to mysql:
sudo chown mysql:mysql /media/speedy/mysql
i make a backup of the existing database name mysql-bu (move it):
mv /var/lib/mysql /var/lib/mysql-bu
make a new symbolic link and check perms:
sudo ln -s /mnt/mysql /var/lib/mysql
CHANGE MySQL config file, my.cnf to reflect new data directory(datadir):
datadir = /media/speedy/mysql
UPDATE APPARMOR
o.k., after you’ve done all of this, apparmor will try and get in your way if you use these new settings.
Edit apparmors mysql config file
/etc/apparmor.d/usr.sbin.mysqld
replace the original MySQL paths with your new MySQL location:
/media/speedy/mysql/ r,
/media/speedy/mysql/** rwk,
***WARNING*** if you make a back up of the usr.sbin.mysqld don’t put it in the apparmor.d directory! Otherwise, when you try to restart apparmor, it will load both configuration files. This had me stumped for hours………..
Now, MySQL should be ready to run in your new location.
PHPMyAdmin needs to be reconfigured:
dpkg-reconfigure phpmyadmin I wanted to move mysqld.sock to the RAID drive to help with performance. Reconfigure PHP to use the new socket: MYSQL_SOCKET
Ubuntu 10.x completely remove and clean MySQL installation
In my attempt to move the MySQL datadir I have a messed up MySQL installation. Here’s how I’ve completely removed MySQL 5.1 from Ubuntu 10.x. i have a messed up installation and need to completely remove MySQL and re-install it. Warning, this will delete *everything* associated with MySQL on ubuntu! It does give you a fresh install!
using apt:
apt-get –purge remove mysql-server
apt-get –purge remove mysql-client
apt-get –purge remove mysql-common
apt-get autoremove
apt-get autoclean
using aptitude (some people claim aptitude does a better job of removing dependencies):
aptitude remove mysql-client
aptitude remove mysql-server
aptitude remove mysql-common
see if anything depends on the installed packages
apt-cache rdepends mysql-server
apt-cache rdepends mysql-client
if you’ve changed apparmor settings change them back inside here:
pico /etc/apparmor.d/usr.sbin.mysqld
delete preferences(the next find command will delete everything):
rm -rf /etc/mysql
find all files with “mysql” on / and delete them:
find / -iname ‘mysql*’ -exec rm -rf {} \;
after all of the above do a clean install
aptitude install mysql-server mysql-client
check to see if mysql is running:
service mysql status
and you should get the response running
mysql start/running, process 3296
check with mysqladmin:
mysqladmin -u root -p status
