Mysql - useful Tips

@ Check the character set on mysql.
mysql> status

mysql> show variables like 'character%';

@ How to copy 1 column from one DB to another DB.
mysql> INSERT INTO `target_databases`.`target_table` (`column21`, `column22`, `column23`) 
SELECT `column11`, `column12`, 1 FROM `source_databases`.`source_table` WHERE column15='test';

@ When these are different DB, -- This is option (--skip-column-names, -N)
$ ./mysql -e "SELECT column1, column2, 1 FROM database_name.table_name WHERE column3 in ('test1', 'test2')" -u root -p xxxx database_name > ~/dumpFile.txt
mysql>LOAD DATA LOCAL INFILE '~/dumpFile.txt' INTO TABLE target_table_name;

@ Backup only the schema
$ mysqldump -u root -p --no-data database_name > ~/onlySchema.sql

@ Ignores foreign keys constraints (0 : ignore, 1:value)
mysql> SET FOREIGN_KEY_CHECKS=0

@ Backup databases
$ ./mysqldump -u root -p Database_Name > DB_backup.sql
$ ./mysql -u root -p Database_Name < DB_backup.sql

@ Create a databases
@ Check character set
mysql> show variables like 'character%';
mysql> CREATE DATABASE databases DEFAULT CHARACTER SET utf8 or utf8mb4;

@SELECT VARIABLES values
mysql>show VARIABLES where Variable_name like '%log%';
@ Start to print logs into a file in mysql
mysql>SET GLOBAL general_log='on';
@If you want to change a path of a log file, change FILE for a path you want
mysql>SET GLOBAL log_output = ‘FILE’;

@Modify a column property
mysql> ALTER TABLE apns_book MODIFY book_badge VARCHAR(2) NULL;

@Remove [on update CURRENT_TIMESTAMP]
# Set the same column name
mysql> ALTER TABLE table_name CHANGE column_name column_name TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

@ How to load data into table from CSV in Mac
mysql> LOAD DATA LOCAL INFILE '~/test.csv' INTO TABLE table_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r';

@ How to control data encrypted.
# select
mysql>SELECT AES_DECRYPT(column_name, 'password') as name FROM table_name;
# update
mysql>UPDATE table_name SET name=AES_ENCRYPT('Update Data', 'password') WHERE id=1;

@ Select data encrypted
mysql>SELECT distinct AES_DECRYPT(column_name, 'password') as test FROM table_name WHERE AES_DECRYPT(column_name, 'password') LIKE '%test%';

@ Change character set
mysql> SET character_set_client = utf8;
mysql> SET character_set_results = utf8;
mysql> SET character_set_connection = utf8;
mysql> ALTER DATABASE DB명 DEFAULT CHARACTER SET utf8;

@ Just Memo
semi  synchronize replication, sync_binlog sync_relay_log 

http://bryan7.tistory.com/101