MYSQL - Purge binary logs

1. SHOW SLAVE STATUS on slave server

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.120.251.21
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000047
          Read_Master_Log_Pos: 925196802
               Relay_Log_File: relay-bin.000140
                Relay_Log_Pos: 925196965
        Relay_Master_Log_File: mysql-bin.000047
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 925196802
              Relay_Log_Space: 925197179
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: 55f6585c-bd69-11e2-90a5-ac162d8b1c44
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
1 row in set (0.00 sec)


2. SHOW BINARY LOGS on master server

mysql> show binary logs;
+------------------+------------+
| Log_name         | File_size  |
+------------------+------------+
| mysql-bin.000001 |        540 |
| mysql-bin.000002 |        143 |
| mysql-bin.000003 |        143 |
| mysql-bin.000004 |        143 |
| mysql-bin.000005 |        143 |
| mysql-bin.000006 |        143 |
| mysql-bin.000007 |       4810 |
| mysql-bin.000008 |        224 |
| mysql-bin.000009 |    2090270 |
| mysql-bin.000010 | 1073795562 |
| mysql-bin.000011 | 1073787709 |
| mysql-bin.000012 | 1073755369 |
| mysql-bin.000013 | 1073760500 |
| mysql-bin.000014 |   13629930 |
| mysql-bin.000015 |      20507 |
| mysql-bin.000016 | 1073756709 |
| mysql-bin.000017 | 1073786502 |
| mysql-bin.000018 |  656410524 |
| mysql-bin.000019 |   11416561 |
| mysql-bin.000020 |   38092807 |
| mysql-bin.000021 | 1073764283 |
| mysql-bin.000022 | 1073763265 |
| mysql-bin.000023 | 1073758670 |
| mysql-bin.000024 | 1073761848 |
| mysql-bin.000025 | 1073767027 |
| mysql-bin.000026 | 1073807701 |
| mysql-bin.000027 | 1073815447 |
| mysql-bin.000028 | 1073746876 |
| mysql-bin.000029 | 1073752803 |
| mysql-bin.000030 | 1073810003 |
| mysql-bin.000031 | 1073742110 |
| mysql-bin.000032 | 1073807390 |
| mysql-bin.000033 | 1073759944 |
| mysql-bin.000034 | 1073742378 |
| mysql-bin.000035 | 1073764107 |
| mysql-bin.000036 | 1073775793 |
| mysql-bin.000037 |  996879580 |
| mysql-bin.000038 |    3833572 |
| mysql-bin.000039 |     240904 |
| mysql-bin.000040 |     610464 |
| mysql-bin.000041 | 1073800478 |
| mysql-bin.000042 | 1073791022 |
| mysql-bin.000043 | 1073796481 |
| mysql-bin.000044 | 1073756133 |
| mysql-bin.000045 | 1073764282 |
| mysql-bin.000046 | 1073796542 |
| mysql-bin.000047 |  925713238 |
+------------------+------------+
47 rows in set (0.01 sec)

3. Determine the earliest log file among all the slaves. This is the target file. If all the slaves are up to date, this is the last log file on the list. (mysql-bin.000046)

4. Copy all binary logs that will be deleted

[root@mysql]# cp mysql-bin.00000* /db/backup_logs/
[root@mysql]# cp mysql-bin.00001* /db/backup_logs/
[root@mysql]# cp mysql-bin.00002* /db/backup_logs/

[root@mysql]# cp mysql-bin.00003* /db/backup_logs/

5. Purge all log files except target log file

mysql> purge binary logs to 'mysql-bin.000039';

Query OK, 0 rows affected (1.00 sec)

6. List remaning binary logs on master

mysql> show binary logs;
+------------------+------------+
| Log_name         | File_size  |
+------------------+------------+
| mysql-bin.000039 |     240904 |
| mysql-bin.000040 |     610464 |
| mysql-bin.000041 | 1073800478 |
| mysql-bin.000042 | 1073791022 |
| mysql-bin.000043 | 1073796481 |
| mysql-bin.000044 | 1073756133 |
| mysql-bin.000045 | 1073764282 |
| mysql-bin.000046 | 1073796542 |
| mysql-bin.000047 |  935732985 |
+------------------+------------+
9 rows in set (0.02 sec)

Errno: 1590 - The incident LOST_EVENTS occured on the master. Message: error writing to the binary log

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.120.251.21
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000009
          Read_Master_Log_Pos: 120
               Relay_Log_File: relay-bin.000019
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000008
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1590
                   Last_Error: The incident LOST_EVENTS occured on the master. Message: error writing to the binary log
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 120
              Relay_Log_Space: 1758
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1590
               Last_SQL_Error: The incident LOST_EVENTS occured on the master. Message: error writing to the binary log
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: 55f6585c-bd69-11e2-90a5-ac162d8b1c44
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State:
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp: 130527 19:56:53
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
1 row in set (0.00 sec)

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.120.251.21
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000009
          Read_Master_Log_Pos: 120
               Relay_Log_File: relay-bin.000023
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000009
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 120
              Relay_Log_Space: 613
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: 55f6585c-bd69-11e2-90a5-ac162d8b1c44
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
1 row in set (0.00 sec)

MySQL Error 1236 - Reconfigure slave

In case replication fails with error 1236 when reading data from binary log, slave can be reconfigured to skip the wrong binlog file and start with the next one.

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 172.18.16.62
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000077
          Read_Master_Log_Pos: 712511169
               Relay_Log_File: mysqld-relay-bin.000216
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000077
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 712429781
              Relay_Log_Space: 83596
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1236
                Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'binlog truncated in the middle of event; consider out of disk space on master; the first event 'mysql-bin.000077' at 712511169, the last event read from './mysql-bin.000077' at 712511169, the last byte read from './mysql-bin.000077' at 712511488.'
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
1 row in set (0.00 sec)

mysql> stop slave;
Query OK, 0 rows affected (0.03 sec)

mysql> change master to master_log_pos=0;
Query OK, 0 rows affected (0.01 sec)

mysql> change master to master_log_file='mysql-bin.000078';
Query OK, 0 rows affected (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.18.16.62
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000079
          Read_Master_Log_Pos: 15734059
               Relay_Log_File: mysqld-relay-bin.000003
                Relay_Log_Pos: 15339130
        Relay_Master_Log_File: mysql-bin.000079
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 15338984
              Relay_Log_Space: 32551481
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 75
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
1 row in set (0.00 sec)

To automatically purge binary log files, there can be used 2 methods:

• periodically use command PURGE BINARY LOGS
•  mysql –uroot –p –e “purge binary logs before date_sub(now(),interval N day);”         // where N = number of days to keep logs
• set varibale expire_log_days
• SET GLOBAL expire_log_days = N;    // where N = number of days to keep logs

MySQL Cluster utilities

During a night action I had the opportunity to reconfigure an MySQL Cluster that had a storage node failed. At start time all storega nodes were stuck in Phase 0. The status of all storage nodes can be view from management client.

# ndb_mgm
ndb_mgm> all status

Also the status of the entire cluster can be view with the command

# ndb_mgm -e show
or from management client
ndb_mgm> show

Memory used on each NDB storage node can be view with:

ndb_mgm> all report memory

From this i learned a lesson: on storage nodes the firewall should be disabled.
Disabling firewall on linux:


# service iptables stop
Flushing firewall rules:                                   [  OK  ]
Setting chains to policy ACCEPT: filter                    [  OK  ]
Unloading iptables modules:                                [  OK  ]
# service iptables status
Firewall is stopped.

# chkconfig iptables off

NDB specific command can be run from storage nodes. These command gives you some details about the data distribution on NDB nodes.

To see all objects stored in NDB engine storage:
# ndb_show_tables
# ndb_show_tables | grep UserTable  // to see only the tables

To see the definitioan of a table and the data distribution for this table in partitions on NDB nodes:
# ndb_desc [table_name] -d [database_name] -p

To get the parameters used for NDB storage nodes:

# ndb_config --type=ndbd --query=id,host,datamemory,indexmemory,datadir -f ' : ' -r '\n'

This ndb_config command check only the data nodes (--type) and shows for each node the values for node's id, hostname, DataMemory, IndexMemory and DataDir parameters.

To get all nodes type in the cluster:

# ndb_config --query=id,type --fields=':' --rows='\n'

To get the row counting for a NDB table:


# ndb_select_count -d [database_name] [table_name1] [table_name2] ...

MySQL datatypes

Integer data types

Type      Storage Required Signed Range                           Unsigned Range
TINYINT   1 byte           –128 to 127                          0 to 255    
SMALLINT  2 bytes          –32,768 to 32,767                    0 to 65,535
MEDIUMINT 3 bytes          –8,388,608 to 8,388,607              0 to 16,777,215
INT       4 bytes          –2,147,683,648 to 2,147,483,647                          0 to 4,294,967,295
BIGINT    8 bytes          –9,223,372,036,854,775,808 to 9,223,372,036,854,775,807                                       0 to 18,446,744,073,709,551,615

Floating point data types

FLOAT represents single-precision floating-point values that require four bytes each for storage.

DOUBLE represents double-precision floating-point values that require eight bytes each
for storage.

Fixed point data types


DECIMAL uses a fixed-decimal storage format: All values in a DECIMAL column have the same
number of decimal places and are stored exactly as given when possible. DECIMAL values are
not processed quite as efficiently as FLOAT or DOUBLE values (which use the processor’s native
binary format), but DECIMAL values are not subject to rounding error, so they are more accurate.
The NUMERIC data type in MySQL is a synonym for DECIMAL.

BIT data type


The BIT data type represents bit-field values. BIT column specifications take a width indicating
the number of bits per value, from 1 to 64 bits.

String data types


Type Description
CHAR Fixed-length non-binary string
VARCHAR Variable-length non-binary string
TEXT Variable-length non-binary string
BINARY Fixed-length binary string
VARBINARY Variable-length binary string
BLOB Variable-length binary string
ENUM Enumeration consisting of a fixed set of legal values
SET Set consisting of a fixed set of legal values

Non Binary sting data types


Type Storage Required Maximum Length
CHAR(M) M characters 255 characters
VARCHAR(M) L characters plus 1 or 2 bytes 65,535 characters (subject to
limitations)
TINYTEXT L characters + 1 byte 255 characters
TEXT L characters + 2 bytes 65,535 characters
MEDIUMTEXT L characters + 3 bytes 16,777,215 characters
LONGTEXT L characters + 4 bytes 4,294,967,295 characters

Binary string data types


Type Storage Required Maximum Length
BINARY(M) M bytes 255 bytes
VARBINARY(M) L bytes plus 1 or 2 bytes 65,535 bytes (subject to limitations)
TINYBLOB L + 1 bytes 255 bytes
BLOB L + 2 bytes 65,535 bytes
MEDIUMBLOB L + 3 bytes 16,777,215 bytes
LONGBLOB L + 4 bytes 4,294,967,295 bytes

ENUM and SET data types


ENUM is an enumeration type. An ENUM column definition includes a list of allowable values;
each value in the list is called a “member” of the list.
The SET data type, like ENUM, is declared using a comma-separated list of quoted strings that

define its valid members. But unlike ENUM, a given SET column may be assigned a value consisting
of any combination of those members.

Temporal data types


Type Storage Required Range
DATE 3 bytes ‘1000-01-01’ to ‘9999-12-31’
TIME 3 bytes ‘-838:59:59’ to ‘838:59:59’
DATETIME 8 bytes ‘1000-01-01 00:00:00’ to
‘9999-12-31 23:59:59’
TIMESTAMP 4 bytes ‘1970-01-01 00:00:00’ to
mid-year 2037
YEAR 1 byte 1901 to 2155 (for YEAR(4)),
1970 to 2069 (for YEAR(2))

2012 Oracle events in Bucharest

Even if we've just entered in 2013, here is the summary of two events that took place in Bucharest in October and November in Bucharest: Oracle Day 2012 and MySQL workshop. Oracle Day was the opportunity to get some details about the announcements made at Oracle Open World 2012.
Those 2 events highlighted the main directions for Oracle in developing and improving his 2 databases: Oracle Database and MySQL Database.

Oracle Day 2012Oracle Open World announcements:
•              Infrastructure as a Service – IaaS
•             Complete Cloud Offering 
◦                     SaaS,
◦                     PaaS,
◦                     IaaS
•             Exadata
•             Exalogic 

2. Oracle Private Cloud

3. Oracle Database 12c
Oracle Database 12c into Oracle Cloud – Multitenant database
•         multitenancy, enables users to create multiple "pluggable" databases that reside within a single database container
•         "heat map“ tracks how frequently different chunks of data are used
.•         Database upgrades and patches are made simpler than in the past thanks to the pluggable database concept.
•         Backup and recovery efforts benefit as well.

4. EXADATA X3 & EXALOGIC X3
•         Exadata X3 (www.youtube.com/watch?v=vcA71QGEhzI)
•          Database In-Memory Machine
◦              26 TB in DRAM (4) in Flash (22) in one rack
◦              X3H2M2 (X3 Heuristic Hierachical Mass Memmory) – Automatically keeps all active data in memory
•          All Active Data in Memory
•          Exadata Database Cloud
•          New Entry-level Eighth Rack
•          Exalogic X3
•          Extreme Performance For Java Applications
•          Extreme Performance For Oracle Business Applications
•          Natively Leverages Exadata
•          Best Consolidation Platform
•          Entry-level Eighth Rack
http://cloud.oracle.com 

Parallel sessions
•         Cloud
•          Instrastructure as a Service
•          Database as a Service
•          Midleware as a Service
•         Customer Experience•          Talent management (Oracle Taleo Cloud Service)
•          Customer experience (Oracle RightNow CX Cloud Service)
•         Data Center•          Exadata, Exalogic, Exalitycs
•         Enterprise Applications•          Business Intelligence
•          Business Process Modelling

MySQL workshop
•         MySQL 5.6 RC (Release Candidate)
•          MySQL Enterprise
•          What’s new in MySQL 5.6
•         MySQL Cluster 7.3 DMR1 (Developemnt Milestone Release)
•          What’s new

 MySQL 5.6 Enterprise Edition
•         MySQL Support
•         MySQL External Authentication
•         MySQL Enterprise Audit
•         MySQL Thread Pool
•         MySQL Enterprise Monitor
•         MySQL Enterprise Backup

 MySQL 5.6 RC
•         InnoDB engine
•          SSD optimization
◦              Page size from 4K to 64K
◦              Separate tablespaces for InnoDB Undo Log
•          Innodb_log_file_size (redo) 4GB limit dropped
•          Online DDL operations
◦              CREATE/DROP INDEX, ADD/DROP FOREIGN KEY
◦              Add, drop, rename, reorder, chane NULL/NOT_NULL for COLUMN
•          Persistent optimizer statistics
•          Optimized for READ ONLY workloads
◦              Autocommit=1: automatic
◦              Autocommit=0: START TRANSACTION READ ONLY
•          NoSQL solution for MySQL - delivered via memcached
•          Full Text Search 

MySQL 5.6 RC
•         Replication
•          Global Transaction IDs
◦              Unique identifier for each transaction written to the Binlog
•          Multi Threaded Slaves
◦              Great for systems which isolate application data using databases – e.g. multi-tenant
•          Optimized row base replication
◦              Binlog-row-image=minimal
•          Crash-safe Slaves
◦              Binlog and table data are transactionally consistent
•          Replication event checksum
 
MySQL 5.6 RC
•         Performance schema
•         Security
•          Password strength policies
•          Stronger password hashing
•         Other
•          Default optimized configuration
•          TIME/TIMESTAMP/DATETIME - fractional second precision
•          Improved partition


MySQL Cluster 7.3
•         MySQL Cluster is designed for
•          Short transactions
•          Many parallel transactions
 
MySQL Cluster
•         MySQL Cluster 7.2 GA
•          Adaptive Query Localization (AQL) – ndb_join_pushdown=on
•          Distribution Aware App
•          Connection pooling between MySQL Servers and data node
◦              ndb-cluster-connection-pool > 1 in my.cnf 
 •          NoSQL API
•         What’s new in MySQL 7.3 (EA)•          Autoinstaller
•          Support for Foreign Keys
•          Integration with the latest MySQL Server
•          New NoSQL APIs;
  

MYSQL log all sqls

[root@b5xox2 ~]# mysql -uroot -p
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.23-ndb-6.2.15-cluster-gpl MySQL Cluster Server (GPL)

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

mysql> show variables like 'general_log%';
+------------------+---------------------------+
| Variable_name    | Value                     |
+------------------+---------------------------+
| general_log      | OFF                       |
| general_log_file | /var/lib/mysql/b5xox2.log |
+------------------+---------------------------+
2 rows in set (0.00 sec)

mysql> set global general_log = 'ON';
Query OK, 0 rows affected (0.25 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bundle             |
| mysql              |
+--------------------+
3 rows in set (0.16 sec)

mysql> use bundle
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables
    -> ;
+------------------+
| Tables_in_bundle |
+------------------+
| bundle_test      |
+------------------+
1 row in set (0.01 sec)

mysql> select * from bundle_test;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
5 rows in set (0.00 sec)

mysql> set global general_log = 'OFF';
Query OK, 0 rows affected (0.06 sec)

mysql>

[root@b5xox2 mysql-cluster]# tail -f /var/lib/mysql/b5xox2.log

/usr/sbin/mysqld, Version: 5.1.23-ndb-6.2.15-cluster-gpl (MySQL Cluster Server (GPL)). started with:

Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock

Time                 Id Command    Argument

/usr/sbin/mysqld, Version: 5.1.23-ndb-6.2.15-cluster-gpl (MySQL Cluster Server (GPL)). started with:

Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock

Time                 Id Command    Argument

120621 19:40:59    2 Query      show databases

120621 19:41:16    2 Query      SELECT DATABASE()

                    2 Init DB   bundle

                    2 Query     show databases

                    2 Query     show tables

                    2 Field List        bundle_test

120621 19:41:22    2 Query      show tables

120621 19:41:29    2 Query      select * from bundle_test

120621 19:41:41    2 Query      set global general_log = 'OFF'

MySQL admin

mysql -u root -p                   -- Connect to Mysql

show databases;                     -- view databases
create database db_name;      -- create database db_name
create schema db_name;       -- create database db_name (schema <=> database)
use db_name;                        -- change database to db information_schema for all statements
show databases;                     -- list all databases
select database();                   -- show current database
help contents;                       -- access help path

create user test identified by 'test';                 -- create user
grant select on test.table_test to test;            -- grant select on table_test to user test
select user, host, password from mysql.user;  -- show all users
drop user test;                                               -- drop user
show grants;                                                  -- show user grants
show grants for 'user'@'host'                          -- show grants for a specific user

Tables are fully defined by using db_name.table_name.

desc table_name;                                           -- describe table
show tables;                                                   -- show database tables;
show index for table_name;                           -- show index for table_name
show table status;                                           -- list detailed table info
show create table table_name;                        -- list DDL for table_name create script
truncate table table_name;                             -- truncates table
analyze table test2;                                        -- analyze table
explain select * from table_tes where pk_col=1; -- view explain plan

select now();                                                  -- show system time
select @@tx_isolation;                                  -- show transaction isolation level  
show character set;                                        -- display a list of available character sets

set autocommit = 1;                                      -- default: set autocommit on

MySql administration (mysqladmin)


mysqladmin status
mysqladmin version



status -- Returns information on server state
password -- Changes a user password
shutdown -- Shuts down the MySQL server
reload -- Reloads the MySQL grant tables
refresh --  Resets all caches and logs
variables -- Returns values of all server variables
version -- Returns the server version
processlist -- Returns a list of all processes active on the server
kill -- Kills an active server process
ping -- Tests if the server is alive

Starting/stopping

/usr/local/mysql/support-files/mysql.server start
/usr/local/mysql/support-files/mysql.server stop
On Windows:
mysqladmin -u root -p shutdown