Open source SQL database often used on Linux with Apache and PHP (LAMP)

mysqltuner.pl

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

Subject

MySQL Replication

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;

Subject

Turn on slow query log in MySQL

[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

Subject

How to setup MySQL

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.

Subject