2012年3月22日 星期四

mysql use utf8 encoding

1.add some parameters on my.conf
#vim /etc/mysql/my.cnf
add two lines on [mysqld] section
default-character-set=utf8
skip-character-set-client-handshake

2.on mysql 5.5 add these into my.conf

init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake

2011年7月1日 星期五

dump one table and insert to anthor mysql

#mysqldump -u root -pabc@123 employees user_list > user_list.sql
export one table form database name employees

#scp root@machineA:user_list.sql .
use scp copy user_list.sql from machineA to machineB

#mysql -u root -pabc@123 employees < user_list.sql
import user_list table into database employees on machineB

2011年3月28日 星期一

mysqldump one table with structure

1.use mysqldump to dump table structure without data
mysqldump -u root -pabc123 -d --databases EbookStore > 20110328.sql

create table structure from another database

1.login to mysql database;

2.use create table tablename as select * from dbname.tablename;
use BookStore;
create table newBook as select * from OldBookStore.newBook;

2011年2月14日 星期一

fix mysql upgrade

1.after your migration mysql from old version to new version
you can use mysql_upgrade command to force upgrade to new version with schema
#mysql_upgrade -u root -pgreen@123

3.check mysql all tables
#mysqlcheck --check --all-databases -u root -pgreen@123

3.check and fix mysql all tables
#mysqlcheck --check-upgrade --all-databases -u root -pgreen@123

4.update all privilege table
#mysql_fix_privilege_tables -u root --password=green@123 --verbose

how to repair table

1.use mysqlcheck to repair one table
#mysqlcheck -r mytable username -u root -pgreen@123

2.use repair table command
#mysql -u root -pgreen@123
->use mytable
->repair table username

2010年9月7日 星期二

use subquery to insert data

mysql>insert into persons values('tommy','lin','22','male');
insert one record to table persons

mysql>insert into customers(lastname,firstname) select lastname,firstname where age=22 and sex='male' from persons limit 1;

use subquery to get data that insert into another table customers