MySQL DBA - Tips and Techniques Magazine

13 Nov 2014

mysqlcheck

Useful utility to check, repair, analyze and optimize mysql tables.  Just be aware that it locks the table, so don't run on busy production tables if they are clustered as probe may time out.

 

mysqlcheck --auto-repair --all-databases –uroot

Extracting a database from any mysqldump file

Eg to extract the database OPALGATEWAY  from the dump file all-06-11-11-17:00-mk-staging-1.sql.gz  .

 

 

zcat all-06-11-11-17:00-mk-staging-1.sql.gz | awk '{ if ( $0 ~ /CREATE DATABASE.*OPALGATEWAY.*/ ) a=1; if ( $0 ~ /CREATE DATABASE / && $0 !~ /CREATE DATABASE.*OPALGATEWAY.*/ ) a=0;  if (a==1) print $0 }' | gzip -  | cat - >  /tmp/OPALGATEWAY.sql.gz

Set up slow query log

slow_query_log       = 1

slow_query_log_file  = /ebill-slave-1/mysql-logs/mysqld-slow.log

long_query_time      = 6000

log-queries-not-using-indexes

log-slow-admin-statements

 

max_connections=13000

 

..and

 

cd mk-myacct-dbslave-2/ebill-slave-2/mysql-logs >

 

 

touch mysqld-slow.log

 

chown mysql:mysql mysqld-slow.log

 

chmod 755 mysqld-slow.log

 

Got a packet bigger than 'max_allowed_packet' bytes

mysql> show slave status\G

*************************** 1. row ***************************

               Slave_IO_State:

                  Master_Host: mk-mysqlcluster-2-miscdb-rw.uk.intranet

                  Master_User: replusr_miscdb

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000009

          Read_Master_Log_Pos: 5506942

               Relay_Log_File: mysql-relay.000516

                Relay_Log_Pos: 72730

        Relay_Master_Log_File: mysql-bin.000009

             Slave_IO_Running: No

            Slave_SQL_Running: Yes

              Replicate_Do_DB:

          Replicate_Ignore_DB: sc3_test_database

           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: 5506847

              Relay_Log_Space: 73121

              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: 1153

                Last_IO_Error: Got a packet bigger than 'max_allowed_packet' bytes

               Last_SQL_Errno: 0

               Last_SQL_Error:

1 row in set (0.00 sec)

 

mysql> exit

 

 

 

SOLUTION:

1) Increase max_allowed_packet to 16M, by default its 1MB

 

 

my.cnf

 

 

[mysqldump]

max_allowed_packet                                    = 16M

 

2) bounce database.

 

3) had to rebuild slave as well, as slave wasn't replicating even afterwards.

 

MySQL Privilege management for Stored Procedures and Functions

Execute permission for a Procedure or Function can be granted to Individually as shown below:

 

GRANT EXECUTE ON PROCEDURE `eonline`.`sp_getmailjoblist` TO 'eonline_rw'@'%' ;                            

GRANT EXECUTE ON FUNCTION `eonline`.`fn_getmailjobsub` TO 'eonline_rw'@'%' ;     

 

To Grant execute permission on all Procedures and Functions of a particular database say 'eonline' to a particular user say 'eonline_rw'  in this case, use the following syntax.

 

GRANT EXECUTE ON `eonline`.* TO 'eonline_rw'@'%' ;

 

This will grant the permissions for all the procedures and  functions of this database to be executed from any host. To restrict this to a particular host , hostname may be specified in the grant statement.

Securing MySQL Database – removal of anonymous accounts

Anonymous MySQL accounts allow clients to connect to the server without specifying a user name. To remove anonymous accounts, connect to the server as the MySQL root user to access the mysql database, then issue the following statements:

 

mysql> select user,host FROM user WHERE User = '';

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

| user | host                 |

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

|      | localhost            |

|      | mk-myacct-dbmaster-1 |

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

2 rows in set (0.00 sec)

 

mysql> DELETE FROM user WHERE User = '';

Query OK, 2 rows affected (0.00 sec)

 

mysql> flush privileges;

Query OK, 0 rows affected (0.01 sec)

 

The DELETE statement removes accounts that have an empty value in the User column of the user table that lists MySQL accounts, and FLUSH PRIVILEGES tells the server to reload the grant tables so the changes take effect.

 

mysql> select user,host FROM user WHERE User = '';

Empty set (0.00 sec)

 

Install InnoDB Plugin for mysql 5.1

You can read about it here.  "

 

  • The InnoDB Plugin offers new features, improved performance and scalability, enhanced reliability and new capabilities for flexibility and ease of use. Among the features of the InnoDB Plugin are "Fast index creation," table and index compression, file format management, new INFORMATION_SCHEMA tables, capacity tuning, multiple background I/O threads, and group commit.

For information about these features, see InnoDB Plugin 1.0 for MySQL 5.1 User's Guide.

 

 

 

To install innodb plugin on 5.1

 

 

1. Check what current state is

 

mysql> show plugins;

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

| Name       | Status   | Type           | Library | License |

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

| binlog     | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |

| partition  | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |

| ARCHIVE    | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |

| BLACKHOLE  | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |

| CSV        | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |

| FEDERATED  | DISABLED | STORAGE ENGINE | NULL    | GPL     |

| MEMORY     | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |

| InnoDB     | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |

| MyISAM     | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |

| MRG_MYISAM | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |

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

 

You can see by default the builtin innoDB plugin is installed.  To use new features need to install the innoDB plugin as follows.

 

 

2. change mycnf to disable the default builtin innodb and to activate the plugin

 

 

cp $MYCNF/my.cnf $MYCNF/my.cnf.20100629

vi $MYCNF/my.cnf

 

...insert after innodb_open_files = 1000

 

 

ignore-builtin-innodb

plugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so

 

 

3.restart the database

 

../mysql-scripts/

 

 

4. Check

 

 

mysql> show plugins;

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

| Name                | Status   | Type               | Library             | License |

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

| binlog              | ACTIVE   | STORAGE ENGINE     | NULL                | GPL     |

| partition           | ACTIVE   | STORAGE ENGINE     | NULL                | GPL     |

| ARCHIVE             | ACTIVE   | STORAGE ENGINE     | NULL                | GPL     |

| BLACKHOLE           | ACTIVE   | STORAGE ENGINE     | NULL                | GPL     |

| CSV                 | ACTIVE   | STORAGE ENGINE     | NULL                | GPL     |

| FEDERATED           | DISABLED | STORAGE ENGINE     | NULL                | GPL     |

| MEMORY              | ACTIVE   | STORAGE ENGINE     | NULL                | GPL     |

| MyISAM              | ACTIVE   | STORAGE ENGINE     | NULL                | GPL     |

| MRG_MYISAM          | ACTIVE   | STORAGE ENGINE     | NULL                | GPL     |

| InnoDB              | ACTIVE   | STORAGE ENGINE     | ha_innodb_plugin.so | GPL     |

| INNODB_TRX          | ACTIVE   | INFORMATION SCHEMA | ha_innodb_plugin.so | GPL     |

| INNODB_LOCKS        | ACTIVE   | INFORMATION SCHEMA | ha_innodb_plugin.so | GPL     |

| INNODB_LOCK_WAITS   | ACTIVE   | INFORMATION SCHEMA | ha_innodb_plugin.so | GPL     |

| INNODB_CMP          | ACTIVE   | INFORMATION SCHEMA | ha_innodb_plugin.so | GPL     |

| INNODB_CMP_RESET    | ACTIVE   | INFORMATION SCHEMA | ha_innodb_plugin.so | GPL     |

| INNODB_CMPMEM       | ACTIVE   | INFORMATION SCHEMA | ha_innodb_plugin.so | GPL     |

| INNODB_CMPMEM_RESET | ACTIVE   | INFORMATION SCHEMA | ha_innodb_plugin.so | GPL     |

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