Questions about this topic? Sign up to ask in the talk tab.

Difference between revisions of "MySQL"

From NetSec
Jump to: navigation, search
Line 433: Line 433:
  
 
{{Notice|If you are upgrading from 4.0->5.[01] or from 4.1->5.1, incrementally upgrade from 4.x->5.0, then run a '''mysqlcheck -r --all-databases''' during the upgrade to the next version, otherwise it might cause incompatibility issues}}
 
{{Notice|If you are upgrading from 4.0->5.[01] or from 4.1->5.1, incrementally upgrade from 4.x->5.0, then run a '''mysqlcheck -r --all-databases''' during the upgrade to the next version, otherwise it might cause incompatibility issues}}
 +
 +
= Optimization Scripts =
 +
 +
== MySQL Tuning Primer ==
 +
 +
  wget -O /root/tuning-primer.sh http://day32.com/MySQL/tuning-primer.sh
 +
  chmod +x /root/tuning-primer.sh
 +
  /root/tuning-primer.sh
 +
 +
== MySQL Tuner ==
 +
 +
  wget -O /root/mysqltuner.pl http://mysqltuner.com/mysqltuner.pl
 +
  chmod +x /root/mysqltuner.pl
 +
  /root/mysqltuner.pl
 +
 +
= Formulae =
 +
 +
== Calculating Minimum Memory Needed ==
 +
 +
  min_memory_needed = global_buffers + (thread_buffers * max_connections)
 +
 +
== Configuration Variables ==
 +
 +
  max_connections        = '''150'''
 +
  max_user_connections    = (150 / #users)
 +
  key_buffer_size        = ('''RAM_MB''' / 3) # '''64MB''' for every '''1GB of RAM'''
 +
  table_cache            = opened_tables + (opened_tables/sec increment)
 +
  sort_buffer_size   = '''1MB''' for every '''1GB''' of RAM
 +
  read_rnd_buffer_size    = '''1MB''' for every '''1GB''' of RAM
 +
  read_buffer_size        = '''1MB''' for every '''1GB''' of RAM
 +
  thread_cache            = increase until '''Threads_created''' stops increasing
 +
  tmp_table_size          = if '''Created_tmp_disk_tables''' = 0, you're good, otherwise '''double''' along with '''max_heap_table_size'''
 +
  query_cache_size        = recommend '''query_cache_type'''=1, '''32M''' per '''1G RAM''', VPSes max '''8MB''' per '''512M RAM'''
 +
  max_seeks_for_key      = 100
 +
 +
 +
 +
 +
= Premade Configs =
 +
 +
== VPS ==
 +
 +
<syntaxhighlight lang="ini">
 +
[mysqld]
 +
max_connections = 50
 +
safe-show-database
 +
skip-locking
 +
key_buffer = 16M
 +
max_allowed_packet = 1M
 +
table_cache = 128
 +
thread_cache = 2
 +
sort_buffer_size = 512K
 +
net_buffer_length = 8K
 +
read_buffer_size = 256K
 +
read_rnd_buffer_size = 512K
 +
myisam_sort_buffer_size = 8M
 +
query_cache_size=8M
 +
max_heap_table_size = 32M
 +
wait_timeout = 30
 +
interactive_timeout = 150
 +
 +
[mysqldump]
 +
quick
 +
max_allowed_packet = 16M
 +
 +
[mysql]
 +
no-auto-rehash
 +
 +
[isamchk]
 +
key_buffer = 16M
 +
sort_buffer_size = 10M
 +
read_buffer = 2M
 +
write_buffer = 2M
 +
 +
[myisamchk]
 +
key_buffer = 16M
 +
sort_buffer_size = 10M
 +
read_buffer = 2M
 +
write_buffer = 2M
 +
</syntaxhighlight>
 +
 +
== Server with 1G-2G RAM ==
 +
 +
<syntaxhighlight lang="ini">
 +
[mysqld]
 +
max_connections = 50
 +
safe-show-database
 +
skip-locking
 +
key_buffer = 128M
 +
max_allowed_packet = 1M
 +
table_cache = 256
 +
sort_buffer_size = 2M
 +
read_buffer_size = 2M
 +
read_rnd_buffer_size = 2M
 +
myisam_sort_buffer_size = 64M
 +
query_cache_size= 32M
 +
max_heap_table_size = 48M
 +
tmp_table_size = 48M
 +
thread_cache_size = 8
 +
wait_timeout = 30
 +
interactive_timeout = 300
 +
 +
[mysqldump]
 +
quick
 +
max_allowed_packet = 16M
 +
 +
[mysql]
 +
no-auto-rehash
 +
 +
[isamchk]
 +
key_buffer = 96M
 +
sort_buffer_size = 64M
 +
read_buffer = 2M
 +
write_buffer = 2M
 +
 +
[myisamchk]
 +
key_buffer = 96M
 +
sort_buffer_size = 64M
 +
read_buffer = 2M
 +
write_buffer = 2M
 +
</syntaxhighlight>
 +
 +
== Server with 3G-8G RAM ==
 +
 +
<syntaxhighlight lang="ini">
 +
[mysqld]
 +
max_connections = 64
 +
safe-show-database
 +
skip-locking
 +
key_buffer = 192M
 +
max_allowed_packet = 1M
 +
table_cache = 512
 +
sort_buffer_size = 2M
 +
read_buffer_size = 2M
 +
read_rnd_buffer_size = 2M
 +
myisam_sort_buffer_size = 64M
 +
query_cache_size= 96M
 +
max_heap_table_size = 64M
 +
tmp_table_size = 64M
 +
thread_cache_size = 16
 +
wait_timeout = 45
 +
interactive_timeout = 600
 +
 +
[mysqldump]
 +
quick
 +
max_allowed_packet = 16M
 +
 +
[mysql]
 +
no-auto-rehash
 +
 +
[isamchk]
 +
key_buffer = 128M
 +
sort_buffer_size = 64M
 +
read_buffer = 2M
 +
write_buffer = 2M
 +
 +
[myisamchk]
 +
key_buffer = 128M
 +
sort_buffer_size = 64M
 +
read_buffer = 2M
 +
write_buffer = 2M
 +
</syntaxhighlight>
 +
 +
== Server with 8G+ RAM ==
 +
 +
<syntaxhighlight lang="ini">
 +
[mysqld]
 +
max_connections = 128
 +
safe-show-database
 +
skip-locking
 +
key_buffer = 384M
 +
max_allowed_packet = 1M
 +
table_cache = 1024
 +
sort_buffer_size = 2M
 +
read_buffer_size = 2M
 +
read_rnd_buffer_size = 8M
 +
myisam_sort_buffer_size = 64M
 +
query_cache_size = 128M
 +
max_heap_table_size = 64M
 +
tmp_table_size = 64M
 +
thread_cache_size = 32
 +
wait_timeout = 120
 +
connect_timeout = 10
 +
interactive_timeout = 1200
 +
 +
[mysqldump]
 +
quick
 +
max_allowed_packet = 16M
 +
 +
[mysql]
 +
no-auto-rehash
 +
 +
[isamchk]
 +
key_buffer = 256M
 +
sort_buffer_size = 128M
 +
read_buffer = 2M
 +
write_buffer = 2M
 +
 +
[myisamchk]
 +
key_buffer = 256M
 +
sort_buffer_size = 128M
 +
read_buffer = 2M
 +
write_buffer = 2M
 +
 +
[mysqlhotcopy]
 +
interactive-timeout
 +
</syntaxhighlight>

Revision as of 21:07, 26 October 2011

MySQL Setup

In order to run MySQL you will need a MySQL server to work with - you can establish a server on one of your own computers, or use a webserver with MySQL installed.

Installing MySQL

MySQL is widely supported on Windows, Linux and Mac. To download MySQL for linux, simply point yourself at your favourite package manager and download it, or visit the website and download it. For Windows, a GUI is available, along with a commandline option if you wish to follow the instructions in this guide verbatim.

Setup on a personal computer

Once MySQL is installed on your computer and the MySQL daemon is running (in Arch, /etc/rc.d/mysqld start), the next step is to establish users. If you defined a root MySQL password on setup, you can use this to establish a new user; otherwise, just hit enter at any password prompts you encounter.

In order to set up your MySQL databases, you'll first need to log into MySQL - at this point the only MySQL user will be your root user, so log in with:

<syntaxhighlight lang="bash"> mysql -p -u root </syntaxhighlight>

This will log you into MySQL as root after a password prompt. At this point, you can establish your other users with the SQL CREATE USER query. For example, if you wanted to create a user, "K_Mitnick":

<syntaxhighlight lang="SQL"> CREATE USER K_Mitnick; </syntaxhighlight>

Note the terminating semicolon - this indicates to MySQl that you wish to send your input as a query. Another method of doing this is by terminating your queries with '\g' - there is no difference between the two, it is simply a matter of personal preference.

This user will be created with absolutely no privileges: they can log into your server but do little else.

Setting Permissions

In order to allow your users to execute queries and interact with your databases, you must indicate to the server just what they are allowed to do. The most simple form of this is

<syntaxhighlight lang="SQL"> GRANT ALL ON <database name> TO <username> IDENTIFIED BY '<password>'; </syntaxhighlight>

RPU0j.png This kind of heavy handed allowance gives the user total freedom to execute any SQL query that they wish. IT SHOULD NEVER be given to the normal user, as this will create a serious vulnerability in your database.

A more reasonable form of this would be

<syntaxhighlight lang="SQL"> GRANT SELECT ON <database name> TO <username> IDENTIFIED BY '<password>'; </syntaxhighlight>

This only gives access to the SELECT query for the user, which essentially makes their access read-only. The GRANT query can be used with any SQL query as a parameter in order to grant a user the ability to use that query.

After you have changed permissions, it is a good idea to ensure that MySQL is up-to-date with user permissions with

<syntaxhighlight lang="SQL"> FLUSH PRIVILEGES; </syntaxhighlight>

You can now log on as a user other than root.

Basic Database Operation

Display

From the main MySQL prompt you see when you first log in, you usually are not logged into a particular database. In order to see all of the databases in your server, type:

<syntaxhighlight lang="SQL"> SHOW DATABASES; </syntaxhighlight>

This will display all of the databases on a server.

There are two ways to reference a table. One is by using the format (database name).(table name) to refer to its full path. The other is to set the database you're working in as your currently active database with the USE command:

<syntaxhighlight lang="SQL"> USE <database>; </syntaxhighlight>

From this point onwards, you can simply refer to a table by name. Furthermore, you can see a list of all tables in the selected database with

<syntaxhighlight lang="SQL"> SHOW TABLES; </syntaxhighlight>

Creating and Deleting Databases

The procedure for the creation and deletion of entire databases is relatively simple.

To create:

<syntaxhighlight lang="SQL"> CREATE DATABASE <name>; </syntaxhighlight>

To delete:

<syntaxhighlight lang="SQL"> DELETE DATABASE <name>; </syntaxhighlight>

Creating and Deleting Tables

In order to create a new table, we use the CREATE query. The syntax for this query is

<syntaxhighlight lang="SQL">

CREATE TABLE (<column1> <datatype>, <column2> <datatype>, etc...); </syntaxhighlight>

For example, if we were to create a table called 'user' containing usernames and hashed passwords, we might want it to have 3 sections - first of all, a number to reference the rows by, secondly a plaintext username, and finally, a hashed password. In order to generate the ID, we would use the special PRIMARY KEY feature of SQL - each table should have a primary key, which automatically updates itself for each entry and is used to refer to elements in a table. The username and password hash would be VARCHARs, which is an SQL datatype that can hold up to 65,535 characters per row. So for this table, our declaration would be as follows:

<syntaxhighlight lang="SQL"> CREATE TABLE user ( ID INT AUTO_INCREMENT PRIMARY_KEY, username VARCHAR(64), hash VARCHAR(512) ); </syntaxhighlight>

There are a great many SQL datatypes to be used to format the contents of a column. For a full list of datatypes, check The MySQL Website.

To delete a table, simply use the DROP TABLE query.

Editing Tables

Now that you have a table, you can begin to put values into it. There are three methods by which you can manipulate the data in a table: one is the INSERT query, another is the UPDATE query, and the last in the DELETE query.

In order to add a new value into a table, you use the INSERT query, the syntax for which is:

<syntaxhighlight lang="SQL">

INSERT INTO [(<column name>, <column name>, etc...)] VALUES (<value 1>, <value 2>, etc...); </syntaxhighlight>

The column references are optional - if included, the VALUES will be inserted into the specified columns in order. If no column references are provided, the VALUES will simply be inserted from the first column onwards. Relating to our last example, the query we would use to add a new entry to our table would be something like:

<syntaxhighlight lang="SQL"> INSERT INTO user VALUES (null, "Selketraz", md5("lolwut")); </syntaxhighlight>

In this case, we do not insert anything into the first value - ID - as PRIMARY KEY will automatically generate its value. The next value, username, we fill with a value, and for the final value we use MySQL's native md5 function to create a hashed password.

Once we've done this, our table will look a little something like this:

ID username hash
1 Selketraz 05a208028929fd77cfb5b08096a837df


The output from MySQL would be like this: 

  mysql> SELECT * FROM user;
  +----+-----------+----------------------------------+
  | ID | username  | hash                             |
  +----+-----------+----------------------------------+
  |  1 | Selketraz | 05a208028929fd77cfb5b08096a837df |
  +----+-----------+----------------------------------+
  1 row in set (0.00 sec)

If we wanted to change one of these values, we would use the UPDATE query instead. For example, say Selketraz forgets her password and needs to have it reset. We would simply alter her entry in the database with UPDATE to set her password to a default one.

<syntaxhighlight lang="SQL"> UPDATE user SET hash=md5("default1%") WHERE username="Selketraz"; </syntaxhighlight>

This employs the WHERE statement to select only certain rows - in effect, it says FOR every row in the entire table: IF username="Selketraz", THEN change the password hash to 'default1%'.

Finally, entry deletion. In order to delete an entry from a table, simply use the DELETE query, which is structured similarly to UPDATE:

<syntaxhighlight lang="SQL"> DELETE FROM user WHERE username="Selketraz"; </syntaxhighlight>

Troubleshooting

InnoDB Disabled

Run this command in SSH as root to show the current engines: 

 mysql -e "show engines;"

If you run a mysqcheck and get something like the following on a number of tables: 

Error    : Incorrect information in file: './halp_blah/example_innodb.frm'
error    : Corrupt

The tables are not actually corrupt, they are just InnoDB tables and InnoDB is disabled.

Locked Tables

http://dev.mysql.com/doc/refman/5.0/en/table-locking.html

If you see MANY locks on a table (or tables, specifically MyISAM not InnoDB), the option low_priority_updates may help. If it is enabled, selects will get higher priority than updates and waiting updates will not stall all new selects to the table until they are executed. 

low_priority_updates = 1

Post-Upgrade - MySQL does not start

If you get the following error message why trying to start MySQL 5.x after an upgrade: 

Starting MySQL
Couldn't find MySQL manager or server [FAILED]

Then edit /etc/my.cnf and comment out the following line: 

basedir=/var/lib

Then restart MySQL.

MySQL Top Resources Script

<syntaxhighlight lang="bash"> mysqladmin processlist | cut -d"|" -f3 | sort | uniq -c | sort -n | tail -n 8 </syntaxhighlight>

MySQL Datadir Migration

 mkdir -p /home/mysql
 chown mysql:mysql /home/mysql
 rsync -avHl /var/lib/mysql/ /home/mysql/
 /etc/init.d/{crond,mysql} stop
 rsync -avHl /var/lib/mysql/ /home/mysql/
 mv /var/lib/mysql /var/lib/mysql.bak
 ln -sf /home/mysql /var/lib/mysql

If /var is full

 rsync -avHlT /home/rtmp$$ /var/lib/mysql/ /home/mysql/

Post-migration

 [mysqld]
 datadir=/home/mysql
 #basedir=/var/lib/mysql

It should then be safe to restart MySQL: 

 /etc/init.d/{crond,mysql} start
 rm -rf /var/lib/mysql.bak
 rm -f /home/mysql/*.pid
 touch /home/mysql/$(hostname).pid

Database Repair

Run this first to determine which tables are corrupted: 

 mysqlcheck -Asc

Repair Corrupted Database (REPAIR)

 mysqlcheck -r <database name>

Repair Corrupted Database (With FRM)

mysqlcheck -r <database name> --use-frm

Repair ALL Databases and Reindex Tables

<syntaxhighlight lang="bash"> /etc/init.d/{crond,mysql} stop cd /var/lib/mysql/ find . -type f -name '*.MYI' -exec myisamchk -fUp '{}' \; /etc/init.d/{crond,mysql} start </syntaxhighlight>

InnoDB Thread Issues

This is most likely a ulimit issue in /etc/init.d/mysql. You can usually find a 'ulimit -s' somewhere in there, and replace the number after it with a number that works for you. If not, add it near the top. 

 ulimit -s 8192

Slow Query Log

touch /var/lib/mysql/slow.log
chown mysql. /var/lib/mysql/slow.log

In the my.cnf file under the mysqld section add this: 

log-slow-queries=/var/lib/mysql/slow.log

Then restart mysql and you have a slow query log.

If you want to specify the number of seconds that indicates a long or slow query, use this line in /etc/my.cnf : 

long_query_time = 5

MySQL Commands

Create a backup of a database

 <syntaxhighlight lang="bash">mysqldump <database> > <database>.sql</syntaxhighlight>

Restore an entire database

 <syntaxhighlight lang="bash">mysqldump <database> > <database>.backup.sql # Always dump the old databse if it is still there, in case we need to bring it back.
 mysql <database> < <sql-file>.sql</syntaxhighlight>

Restore a single table

 <syntaxhighlight lang="bash">mysqldump <database> > <database>.backup.sql # Backup database, just in case.
 cat <database-backup>.sql | sed -n '/-- Table structure for table `<table_name>`/,/-- Table/{ /^--.*$/d;p }' > <table_name>_table.sql
 mysql <database> < <table_name>_table.sql</syntaxhighlight>

Dump mysql user privileges

 <syntaxhighlight lang="bash">mysql -B -N -e "SELECT DISTINCT CONCAT('SHOW GRANTS FOR , user, @, host, ;') AS query FROM mysql.user" | mysql $@ |   sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/## \1 ##/;/##/{x;p;x;}'</syntaxhighlight>

Restore a single database from a full dump

 <syntaxhighlight lang="bash">mysql --one-database database_name < fulldump.sql</syntaxhighlight>

Extract sql for a single database from a full dump

<syntaxhighlight lang="bash">sed -n '/^-- Current Database: `singledb`/,/^-- Current Database: `/p' fulldump.sql > singledb.sql</syntaxhighlight>

Dumping a database in Plesk

 <syntaxhighlight lang="bash">mysqldump -u admin -p database > .sql</syntaxhighlight>


Downgrading

Downgrading MySQL

  • Grab a list of the currently installed mysql packages on your server
 rpm -qa | grep -i mysql-
  • Make Backups
 mkdir /home/sqlbackup/
 for i in $(mysql -e "show databases;" | sed 's/Database//') ; do mysqldump $i > /home/sqlbackup/$i.sql ; done
RPU0j.png cPanel users

If you are running cPanel on your server, Edit /var/cpanel/cpanel.config/, and set mysql-version to your desired MAJ.MIN (eg. 5.0) and 

 rm -f /root/.cpanel/datastore/*_mysql*
RPU0j.png You can run /scripts/mysqlup --force on cPanel servers to upgrade MySQL automatically, but you'll still need to run EasyApache to use the new mysql shared libraries, since this script wipes the old ones out, breaking php-mysql.
  • Check to see that the new version is installed
 rpm -qa | grep -i mysql-
  • Stop MySQL and CRON
killall -9 crond
# on cPanel servers, killall -9 tailwatchd
/etc/init.d/mysql stop
  • Move MySQL install to timestamped directory
 mv /var/lib/mysql /home/mysql.$(date +%s)
  • Reinstall
 mkdir /var/lib/mysql
 mysql_install_db
 mv /root/.my.cnf /root/.my.cnf.old
 service mysql restart
  • Import everything
 cd /home/temp/sqldump
 for i in *.sql ; do mysqladmin create ${i%.*} ; done
 for i in *.sql ; do mysql ${i%.*} < $i ; done ; done
 mv /root/.my.cnf.old /root/.my.cnf
 service mysql restart
c3el4.png cPanel users will want to run EasyApache in SSH via /scripts/easyapache (recommended in screen)

Upgrading MySQL

 mysqlcheck -r --all-databases
 cd /backup
 mkdir mysqldumps
 cd /backup/mysqldumps
 for i in $(mysql -e "show databases;" | cut -d ' ' -f2 | grep -v Database); do \
   mysqldump $i > $i.sql; \
 done
Notice: If you are upgrading from 4.0->5.[01] or from 4.1->5.1, incrementally upgrade from 4.x->5.0, then run a mysqlcheck -r --all-databases during the upgrade to the next version, otherwise it might cause incompatibility issues

Optimization Scripts

MySQL Tuning Primer

 wget -O /root/tuning-primer.sh http://day32.com/MySQL/tuning-primer.sh
 chmod +x /root/tuning-primer.sh
 /root/tuning-primer.sh

MySQL Tuner

 wget -O /root/mysqltuner.pl http://mysqltuner.com/mysqltuner.pl
 chmod +x /root/mysqltuner.pl
 /root/mysqltuner.pl

Formulae

Calculating Minimum Memory Needed

 min_memory_needed = global_buffers + (thread_buffers * max_connections)

Configuration Variables

 max_connections         = 150
 max_user_connections    = (150 / #users)
 key_buffer_size         = (RAM_MB / 3) # 64MB for every 1GB of RAM
 table_cache             = opened_tables + (opened_tables/sec increment)
 sort_buffer_size	  = 1MB for every 1GB of RAM
 read_rnd_buffer_size    = 1MB for every 1GB of RAM
 read_buffer_size        = 1MB for every 1GB of RAM
 thread_cache            = increase until Threads_created stops increasing
 tmp_table_size          = if Created_tmp_disk_tables = 0, you're good, otherwise double along with max_heap_table_size
 query_cache_size        = recommend query_cache_type=1, 32M per 1G RAM, VPSes max 8MB per 512M RAM
 max_seeks_for_key       = 100



Premade Configs

VPS

<syntaxhighlight lang="ini"> [mysqld] max_connections = 50 safe-show-database skip-locking key_buffer = 16M max_allowed_packet = 1M table_cache = 128 thread_cache = 2 sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M query_cache_size=8M max_heap_table_size = 32M wait_timeout = 30 interactive_timeout = 150

[mysqldump] quick max_allowed_packet = 16M

[mysql] no-auto-rehash

[isamchk] key_buffer = 16M sort_buffer_size = 10M read_buffer = 2M write_buffer = 2M

[myisamchk] key_buffer = 16M sort_buffer_size = 10M read_buffer = 2M write_buffer = 2M </syntaxhighlight>

Server with 1G-2G RAM

<syntaxhighlight lang="ini"> [mysqld] max_connections = 50 safe-show-database skip-locking key_buffer = 128M max_allowed_packet = 1M table_cache = 256 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 2M myisam_sort_buffer_size = 64M query_cache_size= 32M max_heap_table_size = 48M tmp_table_size = 48M thread_cache_size = 8 wait_timeout = 30 interactive_timeout = 300

[mysqldump] quick max_allowed_packet = 16M

[mysql] no-auto-rehash

[isamchk] key_buffer = 96M sort_buffer_size = 64M read_buffer = 2M write_buffer = 2M

[myisamchk] key_buffer = 96M sort_buffer_size = 64M read_buffer = 2M write_buffer = 2M </syntaxhighlight>

Server with 3G-8G RAM

<syntaxhighlight lang="ini"> [mysqld] max_connections = 64 safe-show-database skip-locking key_buffer = 192M max_allowed_packet = 1M table_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 2M myisam_sort_buffer_size = 64M query_cache_size= 96M max_heap_table_size = 64M tmp_table_size = 64M thread_cache_size = 16 wait_timeout = 45 interactive_timeout = 600

[mysqldump] quick max_allowed_packet = 16M

[mysql] no-auto-rehash

[isamchk] key_buffer = 128M sort_buffer_size = 64M read_buffer = 2M write_buffer = 2M

[myisamchk] key_buffer = 128M sort_buffer_size = 64M read_buffer = 2M write_buffer = 2M </syntaxhighlight>

Server with 8G+ RAM

<syntaxhighlight lang="ini"> [mysqld] max_connections = 128 safe-show-database skip-locking key_buffer = 384M max_allowed_packet = 1M table_cache = 1024 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 64M query_cache_size = 128M max_heap_table_size = 64M tmp_table_size = 64M thread_cache_size = 32 wait_timeout = 120 connect_timeout = 10 interactive_timeout = 1200

[mysqldump] quick max_allowed_packet = 16M

[mysql] no-auto-rehash

[isamchk] key_buffer = 256M sort_buffer_size = 128M read_buffer = 2M write_buffer = 2M

[myisamchk] key_buffer = 256M sort_buffer_size = 128M read_buffer = 2M write_buffer = 2M

[mysqlhotcopy] interactive-timeout

</syntaxhighlight>
Retrieved from "http://nets.ec/index.php?title=MySQL&oldid=1796"