13 May 2009 - 16:55Jak vytvorit uzivatele a db v mysql

bluedot.cz~# mysql -u admin -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 795395
Server version: 5.0.32-Debian_7etch6-log Debian etch distribution

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql> create database db;
Query OK, 1 row affected (0.00 sec)

mysql> grant all privileges on db.* to db@localhost identified by ’somepassword’;
Query OK, 0 rows affected (0.11 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.08 sec)

mysql>

No Comments | Tags: Databáze

21 November 2007 - 23:48Ladění konfigurace mysql

Vylaďte si snadno mysql databázi pomocí tohoto scriptu.
http://www.day32.com/MySQL/tuning-primer.sh

Výstup vypadá následovně, zjistíte kde jsou slabá místa a pak už jenom poladíte parametry.
Jestliže spravujete více serverů, je to vhodná pomůcka.
Na větších mysql serverch trvá provádění testu KEY BUFFER a TABLE CACHE i desítky minut, takže buďte trpělivý.

MySQL Version 5.0.24a-Debian_9-log x86_64

Uptime = 2days 0 hrs 54 min 40 sec
Avg. qps = 181
Total Questions = 16249099
Threads Connected = 37

SLOW QUERIES
Current long_query_time = 10 sec.
You have 4 out of 16249126 that take longer than 10 sec. to complete
The slow query log is enabled.
Your long_query_time may be too high, I typically set this under 5 sec.

WORKER THREADS
Current thread_cache_size = 0
Current threads_cached = 0
Current threads_per_sec = 6
Historic threads_per_sec = 2
Threads created per/sec are overrunning threads cached
You should raise thread_cache_size

MAX CONNECTIONS
Current max_connections = 600
Current threads_connected = 36
Historic max_used_connections = 156
The number of used connections is 26% of the configured maximum.
Your max_connections variable seems to be fine.

MEMORY USAGE
Max Memory Ever Allocated : 5 G
Configured Max Per-thread Buffers : 17 G
Configured Max Global Buffers : 778 M
Configured Max Memory Limit : 17 G
Physical Memory : 1.96 G

Max memory limit exceeds 90% of physical memory

KEY BUFFER
Current MyISAM index space = 122 M
Current key_buffer_size = 512 M
Key cache miss rate is 1 : 1440
Key buffer fill ratio = 0 %
./tuning-primer.sh: line 623: [: 5024a: integer expression expected
Your key_buffer_size seems to be too high.
Perhaps you can use these resources elsewhere

QUERY CACHE
Query cache is enabled
Current query_cache_size = 256 M
Current query_cache_used = 173 M
Current query_cache_limit = 16 M
Current Query cache Memory fill ratio = 67.66 %
Current query_cache_min_res_unit = 4 K
Query Cache is 10 % fragmented
Run “FLUSH QUERY CACHE” periodically to defragment the query cache memory
If you have many small queries lower ‘query_cache_min_res_unit’ to reduce fragmentation.
MySQL won’t cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
Current sort_buffer_size = 16 M
Current record/read_rnd_buffer_size = 3 M
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 6.00 M
You have had 4764 queries where a join could not use an index properly
join_buffer_size >= 4 M
This is not advised
You should enable “log-queries-not-using-indexes”
Then look for non indexed joins in the slow query log.

OPEN FILES LIMIT
Current open_files_limit = 3010 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine

TABLE CACHE
Current table_cache value = 64 tables
You have a total of 4016 tables
You have 65 open tables.
Current table_cache hit rate is 0%, while 101% of your table cache is in use
You should probably increase your table_cache

TEMP TABLES
Current max_heap_table_size = 16 M
Current tmp_table_size = 128 M
Of 116431 temp tables, 16% were created on disk
Effective in-memory tmp_table_size is limited to max_heap_table_size.
Created disk tmp tables ratio seems fine

TABLE SCANS
Current read_buffer_size = 2 M
Current table scan ratio = 3304 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 12661
Your table locking seems to be fine

No Comments | Tags: Databáze