MySQL

Dear Anyone.

Hello i came across this website doing a search for My.Cnf tweeks or configurations for the SQL_Max_Connections and i was wondering some things. I have a Linux CentOS system with 2.5 gigs of Ram, Running Apache. and PHP. what would be the "proper" My.cnf file settings for a Music Website i keep getting a max connection overload on the network.

Matthew Nalett
New Music Promote
http://www.newmusicpromote.com

Here is my current My.Cnf settings.
Any Suggestions on how to optimize this for a Music Network?


[mysqld]
set-variable=local-infile=0

Can anybody help me?
Basically my app is for an email marketing, so using a while statement its reads from a db (mysam) and make updates

Thank in advance!!

This is the info about my VPS (when typing )
Intel(R) Core(TM)2 Quad CPU Q6600 @ 2.40GHz
Intel(R) Core(TM)2 Quad CPU Q6600 @ 2.40GHz
Intel(R) Core(TM)2 Quad CPU Q6600 @ 2.40GHz
Intel(R) Core(TM)2 Quad CPU Q6600 @ 2.40GHz
Mem: 1206272 kB
OS: CentOS release 5.3 (Final)
Mysql: 5.0.45-log

This is my my.cnf:
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock

###The MySQL server
[mysqld]
set-variable=local-infile=0

Get under the hood of MySQL to find out how you can speed up your database applications.
Jeremy Zawodny

In the open source world, truly great software starts in the hands of enthusiasts and hobbyists. Given time, it matures and develops a more robust community. Then, before most of us realize what is happening, it gains critical mass and moves into the broader industry. Companies that were using expensive commercial software just a year ago are suddenly using a free product — one of the rising stars from the world of Open Source.

Latest Compile with pdo drivers for mysql along with mod_security.

NOTE:
Remove the MySQL-shared rpm else openssl will not work.

Recently, I forgot the root password for MySQL and went about resetting it as below:

1. Stop mysqld and restart it with:
   

   # service mysqld stop
   # mysqld_safe --skip-grant-tables --user=root
   

2. Connect to the mysqld server with this command:

   sql> mysql -u root
   

3. Issue the following statements in the mysql client:

   mysql> UPDATE mysql.user SET Password=PASSWORD('newpassword') WHERE User='root';
   mysql> FLUSH PRIVILEGES;
   

   Replace “newpassword

Here's a quick tip at sorting VARCHAR type data in mysql database with values in a column.

With the default sort, it would look something like below:

mysql> SELECT column FROM table_name ORDER BY column; 

column
======
100
1000
10000
200
2000
20000
...

Now with "... ORDER BY column+0", I get it sorted right:

mysql> SELECT column FROM table_name ORDER BY column+0; 

column
======
100
200
1000
2000
10000
20000
...

This is a quick fix instead of sorting to CAST operator.

Had to do some fine tuning of MySQL 4.1.9 and here is what my.cnf file looks like for a 2GHz machine with 1GB of memory.

[mysqld]
socket=/path/to/mysql.sock
datadir=/var/lib/mysql
skip-locking
skip-innodb
# MySQL 4.x has query caching available.
# Enable it for vast improvement and it may be all you need to tweak.
query_cache_type=1
query_cache_limit=1M
query_cache_size=32M
# max_connections=500
# Reduced to 200 as memory will not be enough for 500 connections.
# memory=key_buffer+(sort_buffer_size+read_buffer_size)*max_connections

# Create User
CREATE USER user [IDENTIFIED BY [PASSWORD] 'password'];

# Create Database
$ mysqladmin -u <username> -p create <nameOfDatabase>

# Drop/Delete Database
$ mysqladmin -u <username> -p drop <nameOfDatabase>

# Check Process List
$ mysqladmin -u root -p proc

# Check Status at 5 seconds interval
$ mysqladmin -u root -p -i 5 status

# Dump Database
$ mysqldump --opt -u <username> -h <hostname> <nameOfDatabase> -p > /path/to/file    

$ mysqldump --opt -u <username> -h <hostname> --all-databases -p > /path/to/file 

# Import Database
$ mysql -h <host> -u <username> <nameOfDatabase> -p < /path/to/file

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER ON <dbname>.* TO <dbuser@localhost> [IDENTIFIED BY '<password>'];

REVOKE ALL ON <dbname> FROM <dbuser@localhost>;

CREATE DATABASE <dbname>;

DROP DATABASE <dbname>;

DROP TABLE <tablename1[, table2, table3...]>;

# To activate new permissions
FLUSH PRIVILEGES; 

USE <nameOfDatabase>;

SHOW DATABASES;

# show tables begining with the prefix
SHOW TABLES LIKE 'prefix%'; 

SELECT * FROM <nameOfTable>;

DESCRIBE <nameOfTable>;

INSERT INTO <table> <username, password, name1, name2, ...> VALUES ('user', password('pass'), 'value1', 'value2' ...);

CREATE TABLE <newtable> AS SELECT DISTINCT <field> FROM <oldtable>;

INSERT INTO <database.table> SELECT * FROM <database.table> WHERE <field> = <value>;

ALTER TABLE <tableOldName> RENAME <tableNewName>;

UPDATE <tableName> SET <field1> = <newValue> [WHERE <field2> = <currentValue>];