#!/bin/sh
for i in `find /var/lib/mysql/test/ -name "Table1.*"`
do
cp $i `dirname $i`'/test'`basename $i`
done

# duplicate a table Table1 by coping the files if it is a MyISAM table.

all the following words are actually MySQL keywords and should not be used as table/ column names.

timestamp, date, localtime, primary, repeat, schema

There are times when you do not notice any issue with it but at a later stage, one has to face a lot of issues because of this.

There are 4 checks need to be performed while creating foreign keys.

1) Check if you are using InnoDB table type for both the tables.
2) Make sure that both the columns are indexed.
3) Make sure that the constraint name for e.g. "FK_batch_relation" is not used anywhere else. Or better, change the name to something else.
4) Make sure that the column type matches. For e.g. If one is INT then the parent column can not be varchar.

Downloaded rpms from mysql.org

rpm -ivh MySQL-client-community-5.1.52-1.rhel5.x86_64.rpm MySQL-server-community-5.1.52-1.rhel5.x86_64.rpm

If you want to uninstall just give rpm –e package name.

innodb_flush_method=O_DIRECT
innodb_file_per_table
innodb-flush-log-at-trx-commit = 2

# number of cpus * number of disks * 2
#innodb_thread_concurrency=16
_____
Lowering the value of swappiness may help mysql

# default 60
echo '50' > /proc/sys/vm/swappiness

Make the change permanent:
/etc/sysctrl.conf
vm.swappiness = 50

# innodb basic settings
innodb_buffer_pool_size=1G
innodb_log_file_size=512M

# MyISAM only cnf setting
user=root
skip-innodb
default-storage-engine=myisam
bulk_insert_buffer_size=1G
key-buffer=1G

# increase default 2 GB on 32 bit OS
myisam_max_sort_file_size=40G
myisam_sort_buffer_size=512M

# make sure temp directory has sufficient space
# and mysql has permission to write
tmpdir=/

Instead of specifying password in mysql command line, I prefer storing it in ~/.my.cnf while doing such works to avoid typing passwords every time. you can create this file or supply password as you wish.

$ cat ~/.my.cnf
[client]
user=root
password="password"

In MySQL, the tmpdir path is mainly used for disk-based sorts (if the sort_buffer_size is not enough) and disk-based temp tables.

You can configure a slave with tmpdir=/dev/shm

It is very interesting to know about how to extract statements from binlogs.

mysqlbinlog --start-datetime="`date +%Y-%m-%d' '%H:00:00 -d\"1 hour ago\"`" --stop-datetime="`date +%Y-%m-%d'
'%H:00:00`" mysql-bin.* | awk '/tbl_company/,/;/' | replace tbl_company mydb.tbl_company | mysql -h 172.29.0.0 -uroot -pPasswd mydb

I have not tested it, but guess it should work as expected.

Last_Error: Error 'Unknown or incorrect time zone: 'Etc/UTC'' on query. Default database: 'db_name'. Query: 'INSERT INTO table SET column = CONVERT_TZ('2009-01-24', '-8:00', '+00:00')'

It’s a simple fix. Exit out of MySQL and run the following command - this works on Redhat, I’ve not tried it on other flavors.

shell> mysql_tzinfo_to_sql /usr/share/zoneinfo|mysql -u root -p

When optimizing queries by rewriting them to different forms that should return the same results, you can verify that you get the same results by taking a checksum of them.

Just set your pager to md5sum:

mysql> pager md5sum -
PAGER set to 'md5sum -'
mysql> select * from test;
a09bc56ac9aa0cbcc659c3d566c2c7e4 -
4096 rows in set (0.00 sec)