MySQL DBA - Tips And Techniques

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;

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

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

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

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;

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

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

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