====== MySQL Information ======



===== maintenance tips =====

==== reset auto increment value ====

<code>
ALTER TABLE tablename DROP id, ADD id MEDIUMINT AUTO_INCREMENT, AUTO_INCREMENT = 1
</code>

<note important>In order for the AUTO_INCREMENTing to work, you must readd the column with its original datatype.  In this example the dropped id column was of the datatype MEDIUMINT</note>

==== copy a table ====

<code>
CREATE TABLE new_tbl SELECT * FROM orig_tbl;
</code>

===== informational links =====


[[http://interworx.com/support/docs/iworx-cp/sysadmin/system-services/mysql/howto-mysql-options#key_buffer_size|key buffer size information (and other info)]]

[[http://interworx.com/support/docs/iworx-cp/sysadmin/system-services/mysql/howto-mysql-options#table_cache|table cache IMPORTANT READ]]


[[http://isc.sans.org/diary.php?date=2007-01-06&isc=e45f91f919c62587c19f07160c8ebb0d|how to avoid sql injection - IMPORTANT READ]]


[[http://us2.php.net/serialize|how to store arrays in mysql via php]]


[[http://www.whenpenguinsattack.com/2007/04/09/10-tips-for-optimizing-mysql-queries/|10 MySQL optimization tips]]

===== Bored@ Server Tweaks =====

<code diff>[root@boredat etc]# diff -u my.cnf.orig my.cnf
--- my.cnf.orig 2006-12-01 00:13:21.000000000 -0500
+++ my.cnf      2006-12-01 00:34:08.000000000 -0500
@@ -5,6 +5,16 @@
 # clients (those using the mysqlclient10 compatibility package).
 old_passwords=1
 skip-networking
+
+#added by John Friar
+key_buffer = 64M
+max_connections = 150
+sort_buffer_size = 20M
+read_buffer_size = 524288
+read_rnd_buffer_size = 2M
+table_cache = 600
+thread_cache_size = 16
+tmp_table_size = 64M
      
 [mysql.server]
 user=mysql</code>

==

{{tag>:linux :linux:server}}