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
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
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
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.
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.
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)
You can read about it here. "
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 |
+---------------------+----------+--------------------+---------------------+---------+