The latest from the MySQL community

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

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

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

          …

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 …

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; …

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;

1. Download the correct version.  There are two versions – a tar file and a package.  The package version installs as part of the Solaris OS , but then means we can have no further mysql installs.  So we tend to download the tar version e.g. mysql-advanced-gpl-5.1.47-solaris10-x86_64.tar

1. Put the download into the bin directory eg /ebilldb-master/mysql-bin

1. untar the download file, this creates a directory called mysql-advanced-gpl-5.1.47-solaris10-x86_64

1. change permissions i.e.

chown -R mysql:mysql mysql-advanced-gpl-5.1.47-solaris10-x86_64

1. create a softlink to the untarred directory called mysql 

ln -s mysql-advanced-gpl-5.1.47-solaris10-x86_64 mysql

1. copy a …

ERROR:

091104 11:12:49 [ERROR] Failed to open the relay log '/var/run/mysqld/mysqld-relay-bin.9339843' (relay_log_pos 233)

091104 11:12:49 [ERROR] Could not find target log during relay log initialization

091104 11:12:49 [ERROR] Failed to initialize the master info structure

Due to the way in which the master and slaves where stopped  the relay logs got out of sync, hence replication would not restart.

Found the solution was to

a) make a note of Master_Log_File and Exec_Master_Log_Pos

mysql> show slave status\G

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

             Slave_IO_State:

…

PROVSERVICE table on VMS ANYPROV has a column which is auto increment

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

| Field               | Type         | Null | Key | Default             | Extra          |

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

| PROVSERVICEID       | int(11)      | NO   | PRI | NULL                | auto_increment |

Development were getting an error suggesting an issue with the auto increment column

…

http://dev.mysql.com/doc/refman/5.1/en/resetting-permissions.html#resetting-permissions-unix