some tips about MySQL query Optimization

1. use the index: for every SQL, use the explain statement to verify the execution plan.
2. dont use index for computing substatement
3. fine the search critieria as much as possible
4. create hash index on fields for min/max/count
5. do manual calculation on some nequalities

MySQL replication introduction

Database plays the essential role in the information system such as websites. To avoid the SPF of MySQL, in production system, we use master-slave replication to synchronise the data and MySQL-proxy to enhance the IO performance.

Advantages of MySQL replication

• Avoid of SPF, when the master server is down, we can use slave server to provide the service
• We can use master server to handle the write request and slaves to handle the read IO.

 Here is how MySQL replication works

• Master server write the data changes to Binary log
• Slave servers IO Thread read from master binary log and put to Relay log.
• Slave server SQL thread read the changes from relay log and implement the change to Slave database.

 

Test Environment:

1. Master server 192.168.139.100
2. Slave server 192.168.139.200

To simplify the illustration,  we suppose both master and slave are from scratch without any data.

1. Set the binary log and server-id:

Master  - my.cnf
[mysqld]
log-bin=mysql-bin            //the binary log must be enabled
server-id=100                    //unique ID in MySQL cluster
Slave – my.cnf
[mysqld]
log-bin=mysql-bin            //the binary log must be enabled
server-id=200                    //unique ID in MySQL cluste

system restart may be required if the parameter changed.

2. Create the user dedicated for replication.

It is not neccessary but strongly recommended that a specified user will be created for replication

create user 'repl'@'192.168.139.200' identified by 'mysql';
grant replication slave on *.* to repl@'192.168.139.200' identified by
'mysql';

3. Get master status in master server

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000001 |      120 |              |                  |                   |

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

4. In the slave server, set the master server and synchronise parameters

mysql>change master to

master_host='192.168.139.100',

master_user='repl',

master_password='mysql',

master_log_file='mysql-bin.000001',

master_log_pos=120;

5. Start the mysql slave
6. Check the status

mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.139.100
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 120
Relay_Log_File: X002-relay-bin.000001
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
------ omitted ------

Now we can test by create database/table and insert data into the master server and confirm we can get in the slave server.

MySQL show commands

MySQL command prompt provides lots of show commands to view the database objects and status. Here is a short summary of show commands:

show tables or show tables from database_name

Explaination: show all tables in current database

show databases;

Explaination: show databases in current server

show processlist;

Explaination: show all running processes associated with the user, if the user has been granted with process privilege, he can check all processes in the server.

show table status;

Explanation: show table detailed status.

show columns from table_name from database_name; 
show columns from database_name.table_name;

Explanation: show table columns = desc [tablename]

show grants for user_name@localhost;

Explanation: show the users privileges;

show index from table_name;

Explanation: show index for the table;

show status;

Explanation: show database very detailed status such as threads numbers, lock numbers, uptime, etc. 

show variables;

Explanation: show variables and the values

show privileges;

Explanation: show different privileges database supports. 

show create database database_name;

Explanation: show create database command syntax

show create table table_name;

Explanation: show create table command syntax 

show engies;

Explanation: show available data engines and the default one.

show innodb status;

Explanation: show innoDB store engine status

show logs;

Explanation: show BDB store engine log status

show warnings;

Explanation: show the last commands warning messages

show errors;

Explanation: show the last commands error messages

MySQL data type

In MySQL, Each data type has several characteristics:

• What kind of values it can represent 
• How much space values take up 
• Whether values are fixed-length (all values of the type take the same amount of space) or variable-length (the amount of space depends on the particular value being stored) 
• How MySQL compares and sorts values of the type 
• Whether the type can be indexed 

Character String Types

• CHAR(n)
• VARCHAR(n)
• BINARY(n)
• VARBINARY(n)
• TEXT(n)
• BLOB(n)

we need to specify the lenth of the characters. char has a fixed length, When the stored string is less than the number you specify, it will be filled with 'null', when we use varchar,the database will specify the length of the value, additional cost will be incurred.

blob -- case sensitive

text/char -- case insensitive

numeric type:

Exact numeric type
integer:

• TINYINT
• SMALLINT
• MEDIUMINT
• INT
• BIGINT

Integer has some attributes.

• UNSIGNED: the value is just for zero or positive value.
• NOT NULL: the field does not allow NULL value
• AUTO_INCREMENT: the field's value will be increase for each line. usually leave it to system and don't need to change it manually. DBA can use LAST_INSERT_ID() to get the current value

the exact numeric has another type:
DECIMAL

Approximate numeric type:

FLOAT
DOUBLE

The FLOAT data type is a single-precision floating-point number.A FLOAT is limited in how many significant digits it can store

The DOUBLE data type is a double-precision floating-point number.Like a FLOAT, a DOUBLE is limited in how many significant digits it can store

Datetime:

• DATE: only store date
• TIME: only store time
• DATETIME: store the time eclipsed from 1970-01-01
• TIMESTAMP: store the date and time

vThe TIMESTAMP field is stored in UTC. When the value is stored, it is converted to UTC, using the mysqld system variable time_zone to figure out the proper value. When a TIMESTAMP value is retrieved, it is converted to the current time_zone of the mysqld server

Bool:

  True or False

Internal：

ENUM， SET

MySQL adds in two data types that are very different from standard data types

• The ENUM data type is an enumerated list of 1 to 65,535 strings, which indicate the allowed values for the field. Only one of the allowed values can be stored in an ENUM field
• The SET data type is an enumerated list of 1 to 64 strings, which indicate the allowed values for the field.In the SET data type, any combination of the strings in the enumerated list can be stored as a comma-delimited list

MySQL client tool - mysql

MySQL client tool mysql is a very powerful tool for DBA

1. how to connect to the database 

   mysql -- the CLI command for DBA
--user, -u
--host, -h
--password, -p
--port
--protocol
--database DATABASE, -D
-A disable completion of table and column names

   mysql < SQLfile.sql - execute the SQL batch

2. common commands:

here is the official help document about the mysql client command. these commands will take effect on the client only.

• clear     (\c) Clear the current input statement.
• connect   (\r) Reconnect to the server. Optional arguments are db and host.
• delimiter (\d) Set statement delimiter.
• edit      (\e) Edit command with $EDITOR.
• ego       (\G) Send command to mysql server, display result vertically.
• exit      (\q) Exit mysql. Same as quit.
• go        (\g) Send command to mysql server.
• help      (\h) Display this help.
• nopager   (\n) Disable pager, print to stdout.
• notee     (\t) Don't write into outfile.
• pager     (\P) Set PAGER [to_pager]. Print the query results via PAGER.
• print     (\p) Print current command.
• prompt    (\R) Change your mysql prompt.
• quit      (\q) Quit mysql.
• rehash    (\#) Rebuild completion hash.
• source    (\.) Execute an SQL script file. Takes a file name as an argument.
• status    (\s) Get status information from the server.
• system    (\!) Execute a system shell command.
• tee       (\T) Set outfile [to_outfile]. Append everything into given outfile.
• use       (\u) Use another database. Takes database name as argument.
• charset   (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
• warnings  (\W) Show warnings after every statement.
• nowarning (\w) Don't show warnings after every statement.

I think the most important commands are:
use : switch between database;
MySQL>use mysql
Database changed

status: show briefly about the server performance
MySQL>MySQL>status
--------------
mysql  Ver 14.14 Distrib 5.6.15, for Linux (x86_64) using  EditLine wrapper

Connection id:          3
Current database:       mysql
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.6.15 MySQL Community Server (GPL)
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /var/lib/mysql/mysql.sock
Uptime:                 14 min 31 sec

Threads: 1  Questions: 60  Slow queries: 0  Opens: 87  Flush tables: 1  Open tables: 80  Queries per second avg: 0.068


source: execute the SQL in batch
system: execute the shell
MySQL>system cat /var/tmp/test.sql
select user,host from user;
MySQL>source /var/tmp/test.sql
+------+-----------+
| user | host      |
+------+-----------+
| root | 127.0.0.1 |
| root | ::1       |
| root | localhost |
| root | x002      |
+------+-----------+
4 rows in set (0.00 sec)

3. how to understand the prompt:

    mysql> --- standard prompt
-> --- waiting for statement delimiter
'> --- waiting for end of '
"> --- waiting for end of "
`> --- waiting for end of `

memcached client commands

Memcached is a very powerful caching tool and widely used in Internet-based company. We usually consider it as a key-value NoSQL. In this blog, I will introduce some of the common Memcached client commands.

1. connect to memcached

memcached even does not provide any client tool, we can use telnet client to connect to the Memcached database.

[root@X001 ~]# telnet localhost 11211
Trying ::1...
Connected to localhost.
Escape character is '^]'.

2. set

set command is used to set key-value pair to the memcached

<command name> <key> <flags> <exptime> <bytes>
<data block>

parameters：

<command >	set
<key>	key
<flags>	integer, used by client as additional field
<exptime>	keep alive time (seconds), use 0 for never expired
<bytes>	bytes
<data block>	value

for example:

set a 999 0 2
10
STORED

3. add and replace

Set will replace the previous value if the key exist, if you don't want the old value to be overwritten, you can use 'add' instead. if the key is there, it will give out a 'not_stored' warning message.

add a 999 0 2

11

NOT_STORED

If you just wants to update the value, you can use 'replace' to set the value, if the key is not in memcached, it will give out a 'not_stored' warning message.

replace b 99 0 2
44
STORED

4. get and gets

get will return the value of a key while gets will add a version. both of them support get multiple values at a time.

for example:

get b
VALUE b 88 2
tt
END
gets b
VALUE b 88 2 7     # version number
tt
END
gets a b                     #get a and b
VALUE a 888 2 4
12
VALUE b 88 2 7
tt
END

5. delete and flush_all

delete command will delete the value by key. flush_all will just flush the all key-value data.

delete a noreply
get a
END
delete b
DELETED

6. STATS

stats command give the admin the running status for the memcached process

stats
STAT pid 1729
STAT uptime 5865
STAT time 1401159261
STAT version 1.4.4
STAT pointer_size 64
STAT rusage_user 1.018845
STAT rusage_system 1.179820
STAT curr_connections 10
STAT total_connections 11
STAT connection_structures 11
STAT cmd_get 18
STAT cmd_set 14
STAT cmd_flush 2
STAT get_hits 13
STAT get_misses 5
STAT delete_misses 0
STAT delete_hits 2
STAT incr_misses 0
STAT incr_hits 0
STAT decr_misses 0
STAT decr_hits 0
STAT cas_misses 0
STAT cas_hits 0
STAT cas_badval 0
STAT auth_cmds 0
STAT auth_errors 0
STAT bytes_read 503
STAT bytes_written 1432
STAT limit_maxbytes 67108864
STAT accepting_conns 1
STAT listen_disabled_num 0
STAT threads 4
STAT conn_yields 0
STAT bytes 0
STAT curr_items 0
STAT total_items 9
STAT evictions 0

how to set the initial password for MYSQL

After installed MySQL in your machine, you may meet the below error when you first login

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

This is because the password hasn’t been set for the root user

To resolve the problem.

1.       stop mysql server

service mysql stop

2.       start mysql using safe_mode

/usr/bin/mysqld_safe --user=mysql --skip-grant-tables --skip-networking &

3.       login to mysql without password

mysql -u root

4.       update mysql user password

Update user SET Password=PASSWORD('111111') where USER='root';

5.       start mysql as normal and enjoy your mysql trip

MySQL data files

 

The MySQL database stores the data in disks as other databases.  The default datastore will be determined by datadir variable

>show variables like "%datadir%";

For example my output is /var/lib/mysql/

Check the directories in

mysql> system ls -ld /var/lib/mysql/*/

drwx------. 2 mysql mysql 4096 Mar 21 22:09 /var/lib/mysql/mydatabase/

drwx--x--x. 2 mysql mysql 4096 Dec 29 10:33 /var/lib/mysql/mysql/

drwx------. 2 mysql mysql 4096 Dec 29 10:33 /var/lib/mysql/performance_schema/

drwx------. 2 mysql mysql 4096 Jan  1 21:08 /var/lib/mysql/test/

at the moment we got 5 databases on my databases(note file information_schema does not have a real data file storage)

mysql> show databases;

| information_schema      |

| mydatabase                    |

| mysql                             |

| performance_schema    |

| test                                 |

data file in  MyISAM

When I create a table using MyISAM engine.

mysql> create table mytable(id int ,name varchar(20)) engine=myisam;

Query OK, 0 rows affected (0.18 sec)

You will see there are three files related with the table

-rw-rw----. 1 mysql mysql 8586 Mar 25 14:38 mytable.frm

-rw-rw----. 1 mysql mysql    0 Mar 25 14:38 mytable.MYD

-rw-rw----. 1 mysql mysql 1024 Mar 25 14:38 mytable.MYI

• *.frm file contains the table related meta files including the table’s definition information
• *.MYD file contains the data for the table.
• *.MYI file contains the index information for the table.

data file in innodb

When we create a table using innodb engine, there is a option called innodb_file_per_table which will decide if every table has single files or shared file. Please use the command

> show variables like ‘%per_table%’;

To verify the setting

To change the settings,  innodb_file_per_table=0 should be in my.cnf’s [mysqld] section then restart the database.

• innodb_file_per_table – on : each table will create a single innodb file for individual table
• innodb_file_per_table – off: tables will share the innodb files

when innodb_file_per_table is on

mysql> create table intable(id int ,name varchar(20)) engine=innodb;

Query OK, 0 rows affected (0.26 sec)

You may see two files for the table

-rw-rw----. 1 mysql mysql  8586 Mar 25 14:43 intable.frm (the meta file)

-rw-rw----. 1 mysql mysql 98304 Mar 25 14:43 intable.ibd (the data file)

when innodb_file_per_table is off.

All the table will share the same file for example ibdata1(datafile) and ib_logfile0 (logfile) but you should still be able to see the meta file

mysql> create table intablesinglefile1(id int ,name varchar(20)) engine=innodb;

Query OK, 0 rows affected (0.19 sec)

mysql> create table intablesinglefile2(id int ,name varchar(20)) engine=innodb;

Query OK, 0 rows affected (0.08 sec)

-rw-rw----. 1 mysql mysql  8586 Mar 25 15:04 intablesinglefile1.frm

-rw-rw----. 1 mysql mysql  8586 Mar 25 15:04 intablesinglefile2.frm

MySQL user and privilege management

privileges management

MySQL provides privileges that apply in different contexts and at different levels of operation:

·  Administrative  privileges enable users to manage operation of the MySQL server. These privileges are global because they are not specific to a particular database.
·  Database privileges apply to a database and to all objects within it. These privileges can be granted for specific databases, or globally so that they apply to all databases.

Grant or revoke privileges

>grant [priviledge] on object to ‘user’@’host’

>revoke [priviledge] on object from ‘user’@’host’

Object can be

• ON *.*
• ON db_name.*
• ON db_name.table_name
• ON db_name.routine_name

Flush the privilege so it will take effect immediately

>flush privileges

 

Common Administrative privileges:

CREATE USER	create drop or rename other users
PROCESS	display of information about the threads executing within the server
RELOAD	use flush and reset
REPLICATION CLIENT	enables the use of SHOW MASTER STATUS and SHOW SLAVE STATUS
REPLICATION SLAVE	accounts that are used by slave servers to connect to the current server as their master
SHOW DATABASES	see the databases
SHUTDOWN	shutdown the server
SUPER	miscellaneous administrative operations
ALL [PRIVILEGES]	shorthand. It stands for “all privileges available at a given privilege level” (except GRANT OPTION).
USAGE	no privileges

Common database privileges:

alter	modify the table
alter routine	modify the store procedure
create	create database and table
create routine	create store procedure
create view	create the view
delete	do delete SQL
drop	drop db or tables
execute	execute the store procedure
grant option	grant privileges to other accounts
index	modify index
insert	do insert SQL
select	do select SQL
update	do update SQL

MySQL user management

The user is identified by userid  + host, two users with same userid but different host are considered as different user. The host field indicates where the user is logged from. The user information is stored in mysql.user table

Create user:

Users can be created by the “create user” command

>create user ‘user’@’host’ identified by ‘password’

Host can be ip address, localhost or %. % means every host. sample command:

>create user 'rafax'@'%' identified by '123456';

show online users

>show processlist

The above command can see the online users and their activities, but you may need privilege to see other users, otherwise you will only see your own activities.

Rename user

User name or host can be renamed by the following command:

>rename  user 'user1'@'host1'  to 'user2'@'host2';

Delete the user

>drop user [user]@[host]

For example:

>drop user ‘rafax’@’%’