MySQL Tip

[My SQL 계정추가]

mysql> create database DB ( 먼저 추가될 사용자가 사용할 DB 생성 )

grant all privileges on DB.* to woos@localhost identified by 'password';
( 생성된 DB 에 USER 사용자 권한주기 )

DB 이름 = 계정이름

/usr/local/mysql/bin/mysql -u root(ID) -p mysql(DB명) (69번 : rma+gold)

mysql>show databases;(기존에 있는지 확인)
mysql>select * from db;
mysql>select * from user;
mysql>create database [계정이름];
mysql>show databases;
mysql>insert into user values('localhost'......)
mysql>select * from user; 확인
mysql>insert into db values("..................");
mysql>flush privileges;
mysql>quit

/usr/local/mysql/bin/mysql -u [계정] -p [db name]
mysql>show tables;

mysql -u root -p mysql
update user set password = password('rma+gold') where user = 'root';

update en_account set aname='사용자' where aid='2';
update en_account set acomment='그리드센터사용자' where aid=2;

update user set password = password('1004girl') where user = '1004girl'
update db set user = '1004girl' where db = '1004girl'
select * from db;
select * from user;
create database sejongmis;
flush privileges;
insert into user values('localhost','sejongmis',password('ikanduit'),'N','N','N','N','N','N','N','N','N','Y','N','N','N','N');
========> 다른서버에서 접속할때 insert into user values('%','gd',password('taemin8161'),'N','N','N','N','N','N','N','N','N','Y','N','N','N','N');

desc golden02;
insert into db values('%','sejongmis','sejongmis','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
delete from user where user='sales';

/usr/local/mysql/bin/mysqldump -u 1004girl -p girl1004 > 1004girl_mysql.sql
 vi ./admin/mode/header.inc(디비커넥터 화일)
find ./ -name *.inc -print(디비커넥터 화일 찾기)
mysql -u 1004girl -p girl1004 < 1004girl_mysql.sql
update member set passwd = 'hokisimx' where *** = ' '
update user set password = password('1n1y1x1') where user = 'gateway1'
delete from user where user='sales';

update db set db = 'buylogistics' where name = 'buy-logistics'
update db set user = password('rma+gold') where name = 'root'

delete from db where user = 'buy-logistics'
delete from user where user = 'buy-logistics'
vi ./admin/mode/header.inc(디비커넥터 화일)
find ./ -name *.inc -print(디비커넥터 화일 찾기)
mysql -u master2 -p master2 < qna_table.sql

alter table new02 rename as bbs2

죽이기 : /usr/local/mysql/bin/mysqladmin -u root -p shutdown
살리기 : /usr/local/mysql/bin/safe_mysqld &

살리기(패스워드 모를때) : /usr/local/mysql/bin/safe_mysqld -Sg &

###### 시스템 튜닝 #######
show variables
./safe_mysqld -O key_buffer=4M -O back_log=32 -O sort_buffer=512k &
./safe_mysqld -O key_buffer=16M -O back_log=32 -O sort_buffer=4M &
./safe_mysqld -O key_buffer=4M -O back_log=32 -O sort_buffer=512k -O table_cache=128 -0 max_connections=180 &
./safe_mysqld -O key_buffer=4M -O back_log=64 -O sort_buffer=512k -O table_cache=128 -O max_connections=180 &

goldenweb;
./safe_mysqld --log=/var/log/mysql.log --language=korean -O key_buffer=4M -O back_log=64 -O sort_buffer=4M -O table_cache=25
6 -O max_connections=128 &

ns1.goldennet.co.kr ;
./safe_mysqld --log=/var/log/mysql.log --language=korean -O key_buffer=4M -O back_log=64 -O sort_buffer=4M -O table_cache=256 -O max_connections=128 &

mysql 5. -> 4.대로 덤프

/usr/local/mysql/bin/mysqldump --create-options --compatible=mysql40 -u cashriver  -p cashriver > backup.sql

한번에 40줄씩

select * from zetyx_board_comment_gallery1 limit 40

33번 이후로 모두 삭제

delete from zetyx_board_comment_gallery1 where no > 33;