Comments (0)

By admin, September 5, 2011 8:34 PM

Where the data varies significantly in length from one record to the next with the longest being significantly longer than the average then you need to use a VARCHAR. There is no point in using a CHAR(40) if the average length is only 15 since that will resuult in a lot of wasted space in most records. Using a VARCHAR(40) instead means that you can still fit the leng 40 chharacter values while the average space used will be 15 plus the record length marker overhead.

summary use varchar for variable length fields and use char if you are expecting it to always be the same eg CHAR (ASSS, DASS, FASS, TASS) this is good for a char ie fixed length fields

Comments (0)

By admin, February 25, 2011 9:59 PM

Disable php Directives(fopensock) & (pfopensock) We were victims of a clever spammer, who was able to upload a php script that simulated/emulated SMTP. It also had code that bypassed the syslog.

I finally caught them, after a month of searching in our own server.

I tarred up the scripts for further analysis, and deleted the account, and more.

When I finished breaking down this script I saw that the whole concept depended on one php directive.

fopensock()

Just disable the fsockopen() in your /etc/php.ini

disable_functions = phpinfo,fsocket,fsockopen,pfsockopen

Comments (0)

By admin, February 25, 2011 8:35 PM

One of the factors with the biggest impact on database performance is not the MySQL settings, but your queries! Make sure you have optimized all your queries first, and have created the right indexes on your tables for MySQL to use.

When tuning MySQL, the two most important variables to configure are key_buffer_size and table_cache. You should first feel confident that you have these set appropriately before trying to optimize any other variables. Ideally, key_buffer_size will be large enough to contain all the indexes (i.e. at least the total size of all .MYI files on the server) of your MyISAM tables.

1. query_cache_size:
* MySQL provides one feature that can prove very handy – a query cache. In a situation where the database has to repeatedly run the same queries on the same data set, returning the same results each time, MySQL can cache the result set, avoiding the overhead of running through the data over and over and is extremely helpful on busy servers.
2. key_buffer_size:
* The value of key_buffer_size is the size of the buffer used with indexes. The larger the buffer, the faster the SQL command will finish and a result will be returned. The rule-of-thumb is to set the key_buffer_size to at least a quarter, but no more than half, of the total amount of memory on the server. Ideally, it will be large enough to contain all the indexes (the total size of all .MYI files on the server).
* A simple way to check the actual performance of the buffer is to examine four additional variables: key_read_requests, key_reads, key_write_requests, and key_writes.
* If you divide the value of key_read by the value of key_reads_requests, the result should be less than 0.01. Also, if you divide the value of key_write by the value of key_writes_requests, the result should be less than 1.
3. table_cache:
* The default is 64. Each time MySQL accesses a table, it places it in the cache. If the system accesses many tables, it is faster to have these in the cache. MySQL, being multi-threaded, may be running many queries on the table at one time, and each of these will open a table. Examine the value of open_tables at peak times. If you find it stays at the same value as your table_cache value, and then the number of opened_tables starts rapidly increasing, you should increase the table_cache if you have enough memory.
4. sort_buffer:
* The sort_buffer is very useful for speeding up myisamchk operations (which is why it is set much higher for that purpose in the default configuration files), but it can also be useful everyday when performing large numbers of sorts.
5. read_rnd_buffer_size:
* The read_rnd_buffer_size is used after a sort, when reading rows in sorted order. If you use many queries with ORDER BY, upping this can improve performance. Remember that, unlike key_buffer_size and table_cache, this buffer is allocated for each thread. This variable was renamed from record_rnd_buffer in MySQL 4.0.3. It defaults to the same size as the read_buffer_size. A rule-of-thumb is to allocate 1KB for each 1MB of memory on the server, for example 1MB on a machine with 1GB memory.
6. thread_cache:
* If you have a busy server that’s getting a lot of quick connections, set your thread cache high enough that the Threads_created value in SHOW STATUS stops increasing. This should take some of the load off of the CPU.
7. tmp_table_size:
* “Created_tmp_disk_tables” are the number of implicit temporary tables on disk created while executing statements and “created_tmp_tables” are memory-based. Obviously it is bad if you have to go to disk instead of memory all the time.

Notes for the future (i.e InnoDB):
If you use InnoDB, it’s buffer pool is controlled by: innodb_buffer_pool_size (this cache also holds row level data). This is the equivalent of key_buffer_size for MyISAM key buffers.
innodb_additional_mem_pool_size
This variable stores the internal data structure. Make sure it is big enough to store data about all your InnoDB tables (you will see warnings in the error log if the server is using OS memory instead).
Since MySQL 4.1.1, the buffer block size is available with the key_cache_block_size server variable. Default 1024.

then test your setup on http://www.omh.cc/mycnf/ to check your max memory size

Comments (0)

By admin, December 6, 2010 11:36 AM

OK – REPLICAITON  MASTER -> SLAVE BROKEN

Check the slave
cd to the mysql dir then cd to bin dir
mysql -u root -p
enter the password

then: show slave status \G
stop slave;
flush slave;
** consider putting RESET SLAVE; in (to reset sync file to 01)

ok so it really is broken

Then go to Master
again go to mysql and bin

mysql -u root -p

enter password

then
WARN USERS NOT TO USE MASTER SERVER FOR 3-4 mins

FLUSH TABLES WITH READ LOCK;
RESET MASTER;
FLUSH LOGS;
SHOW MASTER STATUS;

this will give you a file name and a position, write these down
then copy over the databases over to the slave

UNLOCK TABLES;
 QUIT;

Users are ok to use the master server,

SLAVE MACHINE
now go back to the backup server

copy the database files over the top of the existing ones then type in the mysql prompt:

CHANGE MASTER TO MASTER_HOST='192.168.1.20',

MASTER_USER='slave',

MASTER_PASSWORD='slavepassword',

MASTER_LOG_FILE='mserver-relay-sync-bin.000001',

MASTER_LOG_POS=106;

RATHER ALL THAT IS NEEDED IS

CHANGE MASTER TO MASTER_LOG_FILE='mserver-relay-sync-bin.000001', MASTER_LOG_POS=106;
START SLAVE;

START SLAVE;
 SHOW SLAVE STATUS \G

1. Should read seconds behind master a 0 NOT NULL
2. null = problem
3. if there is a number eg 123 seconds behind master then repeat

SHOW SLAVE STATUS;

b. It will read 0 in a few moments as it updates

QUIT;

Now it is all fixed until it breaks again

Comments (0)

By admin, November 1, 2010 6:55 PM

In order to disable and turn off the Apache logging, just comment out the log lines in the Apache configuration file, httpd.conf, which normally located in /etc/httpd/conf, /etc/apache/conf, /etc/apache2/conf, \usr\local\apache\conf or \usr\local\apache2\conf directory.

In httpd.conf, the logging is defined by a CustomLog directive for access logs and an ErrorLog directive for error logs. For example,

CustomLog /usr/local/apache/logs/access_log common
ErrorLog “logs/error_log”

Commenting out the line by adding # (hash, pound or number sign) to the front of the line. To disable logging of access log, comment the line of CustomeLog, and likewise, comment the line of ErrorLog to disable logging of error log. Restart the Apache web server for the change to take effect.

Note: CustomLog and ErrorLog are also been defined within VirtualHost declaration too for logging the activity of particular virtual host. Disabling logging for virtual hosts only may cause the log information been written to main Apache log files.

there were TWO CustomLog directive specifying access log files, so be sure you scan the entire httpd.conf before saving.

If you comment out the VirtualHost access_log and error_log, then it will write it to the server main log. A better way is to send it to /dev/null

ErrorLog /dev/null
AccessLog /dev/null

This way, the logs are not taking up processes on the server and it’s going to be faster.
-Ponch

Comments (0)

By admin, August 31, 2010 7:31 PM

mysql too many connections

go to my.ini

max_connections=500

Please note that an instruction of the form
set-variable=max_connections=500
should be placed in the [mysqld] section. Otherwise MySQL will ignore it.

Comments (0)

By admin, August 14, 2010 9:17 PM

CREATE USER ‘monty’@'%’ IDENTIFIED BY ‘some_pass’;
GRANT ALL PRIVILEGES ON *.* TO ‘monty’@'%’
WITH GRANT OPTION;

Comments (0)

By admin, December 30, 2009 5:26 PM

MYSQL on Server 2008 64 bit and Vista 64 bit Problems

63 bit odbc controller found in wow64
c:\windows\syswow64\odbccad32.exe
turnoff User Account Control in “Control Panel\User Accounts\” and reboot the system

you can control System DSN and User DSN but you need to

The Wow64 setting are actually the 32 bit settings. Since

we were running 32 bit apps it was always working correctly. There is a

different file for setting up the Wow64 32 bit DSNs

c:\windows\syswow64\odbccad32.exe

I turned off User Account Control in “Control Panel\User Accounts\” and rebooted the system.  This allowed me to do the above,

Thanks in advance.

Comments (0)

By admin, November 12, 2009 10:56 PM

How to Backup MySql Databases to another machine.

Method 1 – Dump and reload

You can use mysqldump to create a simple backup of your database using the following syntax

a) Read Lock??????
b) Then dump all the databases by:

Single Databases
Dump only one database – Structure and Data
mysqldump -u [Username] -p [password] [databasename] > backupfile.sql

Dump only one database – Structure Only
mysqldump -u [Username] -p [password] –no-data [databasename] > structurebackup.sq   (remember to remove the []‘s)

Multiple Databases
Dump only Select databases – Structure and Data
mysqldump -u [Username] -p [password] – -databases [databasename1] [databasename2] [databasename3] > multibackup.sql

Back up all the databases in the server- Structure and Data
mysqldump -u [Username] -p [password] – -all-databases> alldatabases.sql

Back up all the databases in the server- Structure Only
mysqldump -u [Username] -p [password] – -no-data –databases [databasename1] [databasename2] [databasename3]> allstructurebackup.sql

Ended up using

mysqldump – - all-databases > fulldb.dump

AND NOW only this shit works

mysqldump –all-databases -u root -p –all-databases>sqldumpp.sql

How to Restore your Mysql Database

Single Databases
mysql -u [Username] -p [password] [databasename] < databasefile.sql

Multiple Databases