Open or not open, that is the stupid question.

There are the popular MySQL GUI client tools: . Aqua Data Studio. http://www.aquafold.com/aquadatastudio_downloads.html . CLIENT FOR MYSQL BY ENGINSITE. http://www.enginsite.com/Download.htm . dbForge Studio. http://www.devart.com/dbforge/mysql/studio/ . DBTools Manager. http://www.dbtools.com.br/EN/dbmanagerpro/ . DbVisualizer. http://www.dbvis.com/ . Dreamcoder for MySQL. http://www.sqldeveloper.net/database-tools/mysql/overview.html . HeidiSQL. http://www.heidisql.com/ . Navicat. http://www.navicat.com/en/products/navicat_mysql/mysql_overview.html . MyDB Studio. http://www.mydb-studio.com . phpMyAdmin. http://www.phpmyadmin.net/home_page/ . Sequel Pro. http://www.sequelpro.com/ . SQL Examiner Suite. http://www.sqlaccessories.com/SQL_Examiner_Suite/ . SQL Maestro MySQL Tools Family. http://www.sqlmaestro.com/products/mysql/ . SQLWave. http://www.nerocode.com/ . SQLyog. https://www.webyog.com/ . SQuirreL. http://squirrel-sql.sourceforge.net/ . Toad for MySQL. http://www.quest....

First, you have to download MySQL source and use the following command to build it. $ ./configure --without-server --enable-thread-safe-client --with-client-ldflags=-all-static --prefix=/usr/local/mysql --with-machine-type=powerpc --with-zlib-dir=/usr/local/zlib --without-debug --without-docs --with-big-tables If you don't want to build it, there is a way to get it. (The sample is for Ubuntu only.) $ apt-get install libmysqlclient15-dev Wish this helps. regards, Stanley Huang

The most popular benchmark tools are: In MySQL official release: sql-bench mysqlslap Developed by community: DBT-2 SysBench Wish this helps. regards, Stanley Huang

Some body asked me any good utilities for MySQL, therefore, I listed several good utilities here and you could try to google it for detail information. Included: dim_STAT Maatkit mysqlreport mysqlresources mytop Wish this helps. regards, Stanley Huang

If you want to install Redmine on Ubuntu, please follow the following steps. Ref: Redmine Wiki The steps are as the following: 1. Download Redmine by subversion. 1-a. Install subversion by apt-get: # apt-get -y install subversion 1-b. Download Redmine by subversion: # svn co http://redmine.rubyforge.org/svn/branches/2.0-stable redmine-2.0 2. Install gem by apt-get. # apt-get -y install rubygems 3. Install Redmine by bundler. 3-a. Pre-install MySQL5.5, bundler and rmagick # apt-get -y install mysql-server-5.5 # gem install bundler # gem install rmagick 3-b. Setup Redmine by bundler # cd ./redmine-2.0 # bundle install --without development test mysql postgresql sqlite rmagick 4. Create database on MySQL. # mysql mysql> create database redmine character set utf8; mysql> create user 'redmine'@'localhost' identified by 'my_password'; mysql> grant all privileges on redmine.* to 'redmine'@'localhost...

mysql> set @row_count; mysql> select *, @row_count := @row_count + 1 as row_count from test; +------+---------+-----------+ | id | product | row_count | +------+---------+-----------+ | 1 | cloth | 1 | | 2 | shoes | 2 | | 3 | paints | 3 | +------+---------+-----------+ 10 rows in set (0.00 sec) Wish this helps. regards, Stanley Huang

delimiter // drop function if exists rw_ether_atob// create function rw_ether_atob(sAscii char(17)) returns bit(48) deterministic begin declare bReturn bit(48); ##set bReturn=conv(replace(sAscii,':',''),16,2); ## string output not bit ##set bReturn=conv(replace(sAscii,':',''),16,10); ## not work ##set bReturn=cast(replace(sAscii,':','') as bit(48)); ## syntax error ##set bReturn=bin(replace(sAscii,':','')); ## syntax error set bReturn=unhex(replace(sAscii,':','')); return bReturn; end// drop function if exists rw_ether_btoa// create function rw_ether_btoa(sBit bit(48)) returns char(17) deterministic begin declare sReturn char(17); set sReturn=lpad(hex(sBit),12,'0'); set sReturn=concat_ws(':', substr(sReturn,1,2), substr(sReturn,3,2), substr(sReturn,5,2), substr(sReturn,7,2), substr(sReturn,9,2), substr(sReturn,11,2)); return sReturn; end// delimiter ; /* mysql> c...

## take care with signature '???' [mysql] user=root password database=mysql #!include /etc/my.inc #prompt=\R:\m \d> #prompt=\u:\h \d> [mysqldump] [client] ## new cert #ssl-key=/usr/local/mysql/newcerts/client-key.pem #ssl-cert=/usr/local/mysql/newcerts/client-cert.pem #ssl-ca=/usr/local/mysql/newcerts/ca-cert.pem user=root host=localhost protocol=tcp compress password ######################################################[mysqld_multi] [mysqld_multi] server-id = 0 mysqld = /usr/local/mysql/bin/mysqld_safe mysqladmin = /usr/local/mysql/bin/mysqladmin user = multi_admin password = my_password [mysqld2] socket = /tmp/mysql.sock2 port = 3307 pid-file = /usr/local/mysql/data2/hostname.pid2 datadir = /usr/local/mysql/data2 language = /usr/local/mysql/share/mysql/english user = unix_user1 [mysqld3] mysqld = /path/to/safe_mysqld/safe_mysqld ledir = /path/to/mysqld-binary/ mysqladmin = /path/to/mysqladmin/mysqladmin socket = /...

#!/usr/bin/bash #ln -s /dev/null .mysql_history ## in my.cnf MYSQL_HISTFILE=/dev/null #export MYSQL_PS1="(\u@\h) [\d]> " ## mysql --prompt="(\u@\h) [\d]> " #mysql -u mysql mysql < ./test_mysql.sql #mysql -u root mysql < ./test_mysql.sql #mysql -u root --defaults-file=/etc/my.ini ## mysqlslap, benchmark test # mysqlslap -auto-generate-sql # use default script mysqlslap -a -c100 i10 -u root -p --engine=InnoDB,MyISAM,MEMORY,ARCHIVE ## mysqlcheck, for MyISAM, InnoDB, CSV and Archive. #mysqlcheck world; #mysqlcheck world City Country; #mysqlcheck --databases world test; #mysqlcheck --all-databases; ## bin-log #mysqlbinlog bin.000001 bin.000002 | more #mysqlbinlog bin.000001 bin.000002 | mysql #mysqlbinlog --start-datetime="2009-01-01 00:00:00" --stop-datetime="2009-01-01 23:59:59" bin.000001 bin.000002 | mysql #mysqlbinlog --start-position=1001 --stop-position=1999 bin.000001 bin.000002 | mysql ## purge binary logs #mysql> purge bin...

The following commands are my testing notes for MySQL. set sql_mode:=''; -- set sql_mode='only_full_group_by'; -- with group by syntax, select columns must be included in group by list. set sql_mode='traditional'; -- a set of modes, fail sql transaction while datatype error or out of range. -- set sql_mode='pipes_as_concat'; -- set || from logic 'or' to string concatenation (standard sql). -- set sql_mode='ansi_quote'; -- ' for string, " for column name select @@sql_mode; -- set sql_mode='no_auto_create_user'; -- for not auto create user when grant, except grant with identified by. ######################################### notes ## select @@query_cache_type ## @@query_cache_size ## show variable like 'query%'; ## show status like 'qcache%'; ## select sql_no_cache ... /* mysql client */ /* \c to cancle mysql statement. */ /* command */ /* show processlist; show full processlist; show...

#!/usr/bin/bash #mysqlslap -a -c100 i10 -u root -p --engine=InnoDB,MyISAM,MEMORY,ARCHIVE mysqlslap -a --only-print ## ref.@@pseudo_thread_id to get different values of thread. # mysqlslap --create="CREATE TABLE A (a int);INSERT INTO A values (@@pseudo_thread_id)" # --query="SELECT * FROM A" --concurrency=50 --iterations=200 Wish this helps. regards, Stanley Huang

#!/usr/bin/bash -vx MYSQL_HOME=/opt/mysql/mysql mysql -uroot -proot_pwd <<EOF create database if not exists test; use test; create table if not exists test (id int) engine=InnoDB; alter table test engine=Innodb; truncate table test; insert into test values (1),(2),(3); system cp $MYSQL_HOME/data/test/test.ibd $MYSQL_HOME/data/test/test.ibd.bak start transaction; insert into test values (4); system cp $MYSQL_HOME/data/test/test.ibd $MYSQL_HOME/data/test/test.ibd.tran1; rollback; system cp $MYSQL_HOME/data/test/test.ibd $MYSQL_HOME/data/test/test.ibd.rollback; system diff $MYSQL_HOME/data/test/test.ibd.tran1 $MYSQL_HOME/data/test/test.ibd.rollback > ./diff_tran1_rollback; start transaction; insert into test values (4); system cp $MYSQL_HOME/data/test/test.ibd $MYSQL_HOME/data/test/test.ibd.tran2 commit; system cp $MYSQL_HOME/data/test/test.ibd $MYSQL_HOME/data/test/test.ibd.commit system diff $MYSQL_HOME/data/test/test.ibd.tran2 $MYSQL_HOME/data/test/test.ibd.commit > ./di...

#!/usr/bin/bash createTable() { mysql -ujoseph <<EOF CREATE TABLE persons ( id int auto_increment primary key, data text ); EOF } #createTable createUsers(){ mysql -ujoseph <<EOF INSERT INTO persons(data) values (' <person> <name>stanley</name> <sex>m</sex> <addr>taipei</addr> <tels> <tel>12340001</tel> <tel>12340002</tel> <tel>12340003</tel> </tels> </person> '), (' <person> <name>joseph</name> <sex>m</sex> <addr>taipei</addr> <tels> <tel>12350001</tel> <tel>12350002</tel> <tel>12350003</tel> </tels> </person> ') ; EOF } #createUsers queryUserTels() { mysql -ujoseph <<EOF select ExtractValue(data,'//person/tels/tel[1]'), ExtractValue(data,'/person/tels/tel[2]'), ExtractValue(data,'person/tel...

drop table if exists abc; create table abc (i int DEFAULT NULL); insert into abc values (1),(2),(2),(3),(3),(3),(NULL),(NULL),(NULL),(NULL); -- insert into abc values (),(),(),(); -- work -- insert into abc values (1),(),(),(),(); -- not work -- ERROR 1136 (21S01): Column count doesn't match value count at row 2 -- case 1: delete abc from abc, (select * from abc group by i having count(*)>1) as xyz where abc.i=xyz.i or (abc.i is NULL and xyz.i is NULL) limit 1; -- case 2: delete abc from abc, (select * from abc group by i having count(*)>1) as xyz where abc.i=xyz.i or (abc.i is NULL and xyz.i is NULL); /* result: mysql> -- case 1: mysql> delete abc from abc, (select * from abc group by i having count(*)>1) as xyz where abc.i=xyz.i or (abc.i is NULL and xyz.i is NULL) limit 1; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'limit 1' at line 1 mysql> ...

#!/usr/bin/bash -vx read -p "Enter Password: " p c="mysqlshow -uroot -p$p" case1() { c1="$c" c2="$c1 mysql" c3="$c2 user" c4="$c3 host" echo $c1 && $c1 echo $c2 && $c2 echo $c3 && $c3 echo $c4 && $c4 } case2() { c1="$c" c2="$c1 m%" c3="$c1 mysql t%" c4="$c1 mysql time_zone_transition Tr%" echo $c1 && $c1 echo $c2 && $c2 echo $c3 && $c3 echo $c4 && $c4 } case2 Wish this helps. regards, Stanley Huang

#!/usr/bin/bash #dData=`dirname $0`/data dData=/tmp/data mkdir -p $dData && cd $dData mysqldump -uroot test > $dData/test.sql mysqldump -uroot test t > $dData/test.t.sql #mysql -uroot < $dData/test.sql #mysql -uroot < $dData/test.t.sql exit exit exit --routines --triggers #!/usr/bin/bash #dData=`dirname $0`/data dData=/tmp/data mkdir -p $dData cd $dData mysql -uroot <<EOF use test; truncate table t; select * from t; EOF mysqlimport --fields-terminated-by=, --fields-enclosed-by='"' \ --lines-terminated-by="\n", --ignore \ test $dData/t.txt ## '--ignore/--replace' contain unique key values already in the table, 'test' is database name, 't' is the tablename. Wish this helps. regards, Stanley Huang

#################################################### master server set server_id=0; ## master server_id must be 0 grant replication slave on *.* to 'slave'@'%' identified by 'slave'; show master status; # +-----------------------+----------+--------------+------------------+ # | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | # +-----------------------+----------+--------------+------------------+ # | Stanley-NB-bin.000103 | 377 | | | # +-----------------------+----------+--------------+------------------+ ## in my.cnf [mysqld] server-id=0 #################################################### slaver server set server_id=1; # shell> mysqldump -uroot -p test t1 > /tmp/t.sql # mysql> create database if not exist test; # mysql> use test; # mysql> source /tmp/t.sql change master to master_host='192.168.1.100', master_user='slave', master_password='slave', master_...

For testing CSV storage engine for MySQL, I wrote a testing script as the following. #!/usr/bin/bash -vx cat > /tmp/test_csv.CSV <<EOF 1,"Tom",90,90,80 2,"Stanley",80,100,100 3,"Joseph",70,80,90 4,"Christy",80,80,100 5,"Chantelle",60,60,60 EOF mysql -uroot -p -e "create table test_csv ( no int not null, name varchar(32) not null, chi int not null, eng int not null, math int not null) engine=CSV;" world cp /tmp/test_csv.CSV /var/lib/mysql/world/test_csv.CSV mysql -uroot -p -e "flush table test_csv; select * from test_csv;" world Wish this helps. regards, Stanley Huang

/* step 1: LOCK TABLE x FOR WRITE; step 2: FLUSH TABLE x; step 3: myisamchk -cFU -- fast check for pre-existing errors. Don't pack if errors exist. Ignore the warning of table "not closed" because this check will go ahead and closed the DB files. step 4: myisampack -f -- force overwrite of any preexisting .TMD file step 5: myisamchk -raqS -- rebuild the index after pack step 6: FLUSH TABLE x; -- force reload of info_schema data step 7: UNLOCK TABLES; -- Release the table. */ mysql -u mysql <<EOF use test; lock table x for write; flush table x; EOF cd /opt/mysql/mysql/data/test; myisamchk -cFU x; mysiampack -f x; myisamchk -raqS x; mysql -u mysql <<EOF flush table x; unlock tables; EOF Wish this helps. regards, Stanley Huang

you can check if MySQL data streams are encrypted or not by the following command: # tcpdump -l -i eth0 -w - src or dst port 3306 | strings Wish this helps. regards, Stanley Huang