Use the following script to analyze MySQL database for tuning purposes.

#!/usr/bin/perl -w
# mysqltuner.pl - Version 1.0.0
# High Performance MySQL Tuning Script
# Copyright (C) 2006-2008 Major Hayden - major@mhtx.net
#
# For the latest updates, please visit http://mysqltuner.com/
# Subversion repository available at http://tools.assembla.com/svn/mysqltuner/
#
# This program is free software: you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation, either version 3 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program.  If not, see <http://www.gnu.org/licenses/>.
#
# This project would not be possible without help from:
#   Matthew Montgomery     Paul Kehrer
#   Dave Burgess           Jonathan Hinds
#   Mike Jackson           Nils Breunese
#   Shawn Ashlee           Luuk Vosslamber
#   Ville Skytta           Trent Hornibrook
#   Jason Gill             Mark Imbriaco
#   Greg Eden              Aubin Galinotti
#   Giovanni Bechis        Bill Bradford
#   Ryan Novosielski       Michael Scheidell
#   Blair Christensen      Hans du Plooy
#   Victor Trac            Everett Barnes
#

MYSQL 5 Tutorial

These are the steps to set up Master/Slave replication on MySQL

mysql> grant replication slave, replication client on *.* to
repl@'10.%' identified by 'rvisitor';

mysql> show master status;

Change on my.cnf on salve
table_cache=750
query_cache_size = 32M
query_cache_limit = 512K
#replication settings
log_bin = replicate/mysql-bin
server_id = 20
relay_log = mysql-relay-bin
log_slave_updates = 1
log-slow-queries = mysql-slow-queries
long_query_time = 1
log-queries-not-using-indexes

replicate-do-db=test

replicate-do-db=test2

binlog_cache_size=128K


Change on my.cnf on Master
table_cache=750
query_cache_size = 32M
query_cache_limit = 512K
#replication settings
log_bin = replication/mysql-bin
binlog-do-db=test
binlog-do-db=test2
binlog-do-db=test2
expire_logs_days=10
server_id = 10
log-slow-queries = mysql-slow-queries
long_query_time = 1

Then restart mysql on both

mysql>
change master to master_host='witwicky',
master_user='repl',
master_password='rvisitor',
master_log_file='mysql-bin.000001',
master_log_pos=0;

mysql> show slave status;
mysql> start slave;
mysql> show slave status;

SHOW MASTER STATUS;

SHOW SLAVE STATUS;
SHOW MASTER LOGS;
PURGE MASTER LOGS TO 'bin-log.XYZ';

To stop execution of the binary log from the master, use STOP SLAVE:

I can view the UT8 characters correctly in phpMyAdmin. The data is stored correctly in the MySQL db.

However, when I call the data on a php page, php displays a? instead of the characters from the db.

This solved it for me.

mysql_query(SET NAMES utf8);
after mysql connection.

or Try this

Here are the contents of my my.cnf:

[mysqld]
set-variable=long_query_time=1
log-slow-queries=/var/log/mysql/log-slow-queries.log
log-slow-queries = slow.log
long_query_time = 20
log-queries-not-using-indexes

You must create the file manually and change owners this way:

mkdir /var/log/mysql
touch /var/log/mysql/log-slow-queries.log
chown mysql.mysql -R /var/log/mysql

Update - New Innodb tuning section added below.  See also Common MySQL Queries.

Here are the tips I learned while setting up MySQL

1. # yum install mysql mysql-server
2. Edit /etc/my.cnf and enter datadir information (i.e /mysql/data)
3. Bring up /etc/init.d/mysqld start
4. Start MySQL server
5. Change the mysql root user password type: mysqladmin -u root password 'newpassword'
6. Test connecting to mysql Type: mysql -u root -p enter the password when prompted.

Reference

 FullText

To index 3 charater words add the following to the /etc/my.cnf file at the end of the [mysqld] section.

ft_min_word_len=3

[myisamchk]
ft_min_word_len=3

Then restart the mysql service, and run the following sql.

mysql> repair table mw_searchindex quick;

Install and Configure

1. Edit /etc/my.cnf and enter Data File Location (datadir) information (i.e /mysql/data)
   • [mysqld] datadir=/my
2. Bring up /etc/init.d/mysqld start
3. Start MySQL server
4. Change the mysql root user password type: mysqladmin -u root password 'newpassword'
5. Test connecting to mysql Type: mysql -u root -p enter the password when prompted.

 Change Root Password

mysqladmin -u root password NEWPASSWORD mysqladmin -u root -p oldpassword newpass

Create Database

The following are the instructions on creating database using mysql client

$ mysql -u adminusername -p

Enter password:Welcome to the MySQL monitor. Commands end with ; or \g.