MySQL Notes

just some notes about the mysql certification come here, reading and learning everything from a book I bought. Guy who pointed me out to make that certification is Geert Vanderkelen who now works for MySQL

the very beginning of that book ...

mysql> select version(); +----------------------+ | version() | +----------------------+ | 4.0.24_Debian-10-log | +----------------------+ 1 row in set (0.03 sec)

mysql> SELECT foo from bar -> where \c mysql>

\c will not execute the query and result with a new prompt

semicolon and \g are equivalent

mysql> select version(), now(); +----------------------+---------------------+ | version() | now() | +----------------------+---------------------+ | 4.0.24_Debian-10-log | 2005-08-07 23:21:37 | +----------------------+---------------------+ 1 row in set (0.02 sec) mysql> select version(), now()\g +----------------------+---------------------+ | version() | now() | +----------------------+---------------------+ | 4.0.24_Debian-10-log | 2005-08-07 23:21:43 | +----------------------+---------------------+ 1 row in set (0.00 sec)

status

mysql> status -------------- mysql Ver 12.22 Distrib 4.0.24, for pc-linux-gnu (i386) Connection id: 33 Current database: Current user: revenger@localhost SSL: Not in use Current pager: stdout Using outfile: '' Server version: 4.0.24_Debian-10-log Protocol version: 10 Connection: Localhost via UNIX socket Client characterset: latin1 Server characterset: latin1 UNIX socket: /var/run/mysqld/mysqld.sock Uptime: 1 day 13 hours 52 min 32 sec Threads: 1 Questions: 133 Slow queries: 0 Opens: 59 Flush tables: 1 Open tables: 53 Queries per second avg: 0.001 --------------

short form is \s for displaying the status

for getting long command names work in the middle of sql queries (after the first char of a line) invoke mysql with --named-commands

the option --safe-updates is for n00bs :)

if an evil table is full .. like atm somewhere where I have to fix it.. blah

SHOW TABLE STATUS FROM database LIKE 'table_name'; +-----------+--------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+----------------+---------+ | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | +-----------+--------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+----------------+---------+ | table_name | MyISAM | Dynamic | 618186 | 6947 | 4294967292 | 4294967295 | 36902912 | 0 | 3535739 | 2005-01-16 13:11:21 | 2005-08-10 20:08:05 | NULL | | | +-----------+--------+------------+--------+----------------+-----

looking at these evil 618186 .. there's a ttl in it but it didn't get erased so ..

4294967295/1024 4194303.99902343750000000000 4294967295/1024/1024 4095.99999904632568359375 <-- MB

delete from table_name where ttl<now();

and since that fun didn't work at all..

mysql -h hostname -e "show processlist" | grep -v Sleep | 31767 | root | localhost | flights | Query | <toolong> | NULL | doing some query |

truncate table_name; OK

(renaming such tables could be useful as well depends what they're needed for .. my case was clear enough..)

another way would be to extend the table

for example:

ALTER TABLE table_name MAX_ROWS=1000000000 AVG_ROW_LENGTH=nnn; (like it's descriped on mysql.com in the documentation)

strange that the certification book doesn't show up replication issues, and detail practical cases like the high performance mysql book does... so ongoing the mysql book lost my interest it's more worth to read the other book

ACID (=> Atomicity Consistency Isolation Durability)

read more about it on wikipedia.. or get the book high performance mysql

http://en.wikipedia.org/wiki/ACID

Tabellentypen ändern

alter table mytable type=bdb

create table mytable( a integer not null primary key, b char(18) not null ) RAID_TYPE=STRIPED RAID_CHUNKS=4 RAID_CHUNKSIZE=16;

MyISAM? Merge Tables

create table mytable0( a integer not null primary key, b char(18) not null ); create table mytable1( a integer not null primary key, b char(18) not null ); create table mytable2( a integer not null primary key, b char(18) not null ); create table mytable( a integer not null primary key, b char(18) not null ) TYPE=MERGE UNION=(mytable0,mytable1,mytable2) INSERT_METHOD=LAST;

mysql benchmarking tools

mysql benchmark suite - (funktioniert auch mit anderen datenbanken)

super-smack -stress test tool

mybench -- stress test (einfacher als super-smack)

fultextindexe sind vorzuziehen bevor man eine spalte mit like durchsucht...

mysql only uses one index when searching a record

primary key unter mysql löschen .. alter table drop primary key

basedir .. basedir von mysql datadir .. db dir von mysql beides ist in der my.cnf notwendig damit z.b mysqldumpslow funktioniert my_print_defaults mysqld gibt die gesetzten variablen aus

TQC .. Tagged Queuing command SCSI, speedup bei festplattenzugriffen

use index, force index example (even if it's useless in that case)

mysql> explain select * from fragen use index(typ) where punkte>0 and typ<1; +--------+------+---------------+------+---------+------+------+-------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +--------+------+---------------+------+---------+------+------+-------------+ | fragen | ALL | typ | NULL | NULL | NULL | 3 | Using where | +--------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) mysql> explain select * from fragen force index(typ) where punkte>0 and typ<1; +--------+------+---------------+------+---------+------+------+-------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +--------+------+---------------+------+---------+------+------+-------------+ | fragen | ALL | typ | NULL | NULL | NULL | 3 | Using where | +--------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec)

temptables ... (also counts tables which get created during some queries not only by create temporary table..)

mysql> show status like 'created_tmp_%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Created_tmp_disk_tables | 11 | | Created_tmp_tables | 33575 | | Created_tmp_files | 0 | +-------------------------+-------+ 3 rows in set (0.00 sec)

installing a mysql db

debian:/proc/22549# /usr/sbin/mysqld --defaults-file=/etc/mysql/my2.cnf 050910 17:17:31 InnoDB: Started 050910 17:17:31 Fatal error: Can't open privilege tables: Table 'mysql.host' doesn't exist 050910 17:17:31 Aborting 050910 17:17:31 InnoDB: Starting shutdown... 050910 17:17:34 InnoDB: Shutdown completed 050910 17:17:34 /usr/sbin/mysqld: Shutdown Complete ---- debian:/proc/22549# mysql_install_db --defaults-file=/etc/mysql/my2.cnf WARNING: The host 'debian' could not be looked up with resolveip. This probably means that your libc libraries are not 100 % compatible with this binary MySQL version. The MySQL daemon, mysqld, should work normally with the exception that host name resolving will not work. This means that you should use IP addresses instead of hostnames when specifying MySQL privileges ! Preparing db table Preparing host table Preparing user table Preparing func table Preparing tables_priv table Preparing columns_priv table Installing all prepared tables 050910 17:20:51 /usr/sbin/mysqld: Shutdown Complete To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: /usr/bin/mysqladmin -u root password 'new-password' /usr/bin/mysqladmin -u root -h debian password 'new-password' See the manual for more instructions. You can start the MySQL daemon with: cd /usr ; /usr/bin/mysqld_safe You can test the MySQL daemon with the benchmarks in the 'sql-bench' directory: cd sql-bench ; perl run-all-tests Please report any problems with the /usr/bin/mysqlbug script! The latest information about MySQL is available on the web at http://www.mysql.com Support MySQL by buying support/licenses at https://order.mysql.com ---- debian:/proc/22549# /usr/sbin/mysqld --defaults-file=/etc/mysql/my2.cnf 050910 17:20:56 InnoDB: Started /usr/sbin/mysqld: ready for connections. Version: '4.0.24_Debian-10-log' socket: '/var/run/mysqld/mysqld1.sock' port: 3307 Source distribution

localhost versus 127.0.0.1 the special case

debian:/home/revenger# mysql -P 3307 -h localhost -u root mysql ERROR 2002: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) debian:/home/revenger# mysql -P 3307 -h 127.0.0.1 -u root mysql Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 to server version: 4.0.24_Debian-10-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>

binär log untersuchen

debian:/var/log/mysql# mysqlbinlog mysql-bin.302 /*!40019 SET @@session.max_insert_delayed_threads=0*/; # at 4 #050910 17:20:51 server id 1 log_pos 4 Start: binlog v 3, server v 4.0.24_Debian-10-log created 050910 17:20:51 at startup # at 79 #700101 1:00:00 server id 1 log_pos 79 Query thread_id=1 exec_time=1126365651 error_code=0 use mysql; SET TIMESTAMP=0;

flush tables with read lock

reset master

... 05:25 < ajdonnison> revenger_, it is HCORLIM, SCORLIM, HVMMLIM and SVMMLIM. ... 05:55 < arjenAU> yes. and then my point would be tat that's probably a waste of memory which in fact costs CPU cycles to use. 05:55 < lokus> max for key-buffer is 4G actaully 05:55 < arjenAU> regardless 05:56 < arjenAU> revenger_: did you actually look at the server stats to work out how much mem for key buffer would make sense? 05:56 < revenger_> arjenAU: which server stats? 05:56 < arjenAU> SHOW STATUS 05:56 < arjenAU> just messing with buffers doesn't make for a better setup. 05:57 < arjenAU> considering you're someone who doesn't know how to swim, you will still drown regardless of the pool size ;-) 05:57 * arjenAU makes up another silly analogy 05:57 < arjenAU> in this case, you want to check key_reads vs key_read_requests 05:57 < revenger_> | Key_read_requests | 863239 | 05:57 < revenger_> | Key_reads | 11487 | 05:58 < arjenAU> you want to have about 97% hitrate. if less, increase the buffer. if more, you're wasting memory. 05:58 < arjenAU> 0.01 you're wasting mem 05:59 < lokus> there is also key_blocks_used and unused 05:59 < arjenAU> indulge me. set it to say 256MB, FLUSH STATUS, and check again in a few days 05:59 < revenger_> Key_blocks_used | 11516 | 05:59 < arjenAU> please note that you can tweak these settings on the live server, no restarts needed. 05:59 < arjenAU> a block is 1K 05:59 < revenger_> |ok 05:59 < revenger_> -l* 06:00 -!- Jivedue has quit [] 06:00 < arjenAU> likewise, for the sort and read buffer... you want to know what your apps need. 06:00 < arjenAU> mem takes time to allocate. 06:00 < arjenAU> so keeping a relatively small default is good, then you can increase WITHIN a session when needed for a particular query.

http://www.livejournal.com/users/arjen_lentz/

a multi-mastered seems a bit weak.. it relies on duplicate key errors but should work..

memory calc: key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections

changing settings onfly

mysql> set global key_buffer_size=256000000; Query OK, 0 rows affected (0.28 sec) mysql> set global read_buffer_size=2000000; Query OK, 0 rows affected (0.00 sec)