출처 - http://blog.naver.com/yangsewon?Redirect=Log&logNo=140052849317
-----------------------------------------------------------------------------------
1. 데이터베이스 접속
- # mysql -u 사용자명 -p dbname
- 설치 직후에는 root 사용자에 비밀번호가 없으므로 다음과 같이 접속하여 MySQL을 관리할 수 있다.
# mysql -u root mysql
2. 비밀번호 관련
- MySQL을 설치한 직후에는 root 계정에 암호가 지정되어 있지 않다.
- mysqladmin이용.
# mysqladmin -u root [flush-privileges] password '새비밀번호'
- update문 이용
# mysql -u root mysql
mysql> UPDATE user SET password=password('새비밀번호') WHERE user='root';
mysql> FLUSH PRIVILEGES;
- Set Password 이용
SET PASSWORD FOR root=password('새비밀번호');
- 일단 root 비밀번호가 설정된 상태에서는 mysql이나 mysqladmin 명령을 실행할 때 -p 옵션을 붙여주고
기존 비밀번호를 입력해야만 한다.
3. 사용자 관련
- 사용자 추가
insert into user (Host, User, Password) values('%', '사용자명', password('패스워드'));
insert into user (host, user, password) values('localhost',mysql,password('mysql'));
- 사용할 DB 설정 (mysql>desc db 를 실행하여 컬럼의 개수를 세어보고 갯수만큼 'Y'로 세팅)
insert into db values('%', 'db명', 'user명', 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
insert into db values('localhost','mydb','mysql','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
flush privileges; (mysql reload)
- 권한 설정
update user set Select_priv ='Y',Insert_priv='Y',Update_priv='Y',Delete_priv='Y';
GRANT [부여할권한] ON [대상DB명] to [사용자명] identified by '비밀번호';
GRANT ALL PRIVILEGES ON *.* to testuser@"%" IDENTIFIED BY 'test' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* to testuser@"localhost" IDENTIFIED BY 'test' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* to testuser@"testuser@210.xxx.xxx.xxx" IDENTIFIED BY 'test' WITH GRANT OPTION;
- mysql> GRANT ALL PRIVILEGES ON dbname.* TO username@localhost IDENTIFIED BY 'password';
username 이라는 사용자를 password라는 비밀번호를 갖도록 하여 추가한다. username은 dbname이라는
데이타베이스에 대해 모든 권한을 가지고 있다.
username 사용자는 로칼 호스트에서만 접속할 수 있다. 다른 호스트에서 접속하려면
- GRANT ALL PRIVILEGES ON dbname.* TO username@'%' IDENTIFIED BY 'password';
위를 또한 번 실행한다. '%'에서 홑따옴표를 주의한다.
- 불필요한 사용자 삭제는
mysql> DLETE FROM user WHERE user='username';
mysql> FLUSH PRIVILEGES;
4. 데이터베이스 관련
- 데이터베이스 생성
mysql> CREATE DATABASE dbname;
- 현재 존재하는 데이터베이스 목록을 보여준다.
mysql> SHOW DATABASES;
- 특정 데이타베이스를 사용하겠다고 선언한다.
mysql> USE dbname;
- 쓸모 없으면 과감히 삭제한다.
mysql> DROP DATABASE [IF EXISTS] dbname;
IF EXISTS 옵션은 비록 데이타베이스가 없더라도 오류를 발생시키지 말라는 의미이다.
5. 테이블 관련
- 현재 데이타베이스의 테이블 목록을 보고
mysql> SHOW TABLES;
- 테이블 구조를 살펴본다.
mysql> desc tablesname;
- 필요 없으면 삭제한다.
mysql> DROP TABLE tablename;
6. MySQL 현재 상태 보기
- mysql> status
7. script파일 실행(Oracle :start, @)
- mysql>source C:scott.sql
8. MySQL root password 재설정 방법
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) 일때.....
1. # killall mysqld
2. # mysqld_safe --skip-grant &
3. # mysql
- mysql>use mysql
- mysql>update user set password=password('newpassword') where user='root';
- mysql>flush privileges
flush privileges <-- (mysql reload)
9. SQL실행 결과를 파일로 저장
- C:mysqlbin>mysql -uscott -ptiger scott > C:dump.txt
select * from emp;
select * from dept;
exit
10. Database 백업
- # mysqldump
mysqldump -uscott -ptiger scott > test.sql
- # BACKUP TABLE : 테이블을 데이터 파일로 백업함
mysql>BACKUP TABLE table_name[,tbl_name] TO '/path/directory'
- # RESTORE TABLE : BACKUP TABLE로 백업한 데이터를 복구한다.
mysql>RESTORE TABLE table_name[,tbl_name] FROM '/path/directory'
안녕하세요?
정말 좋은 자료 감사합니다~!
grant 명령어에 " "가 들어가는 걸 모르고
' ' 사용해서 5시간째 해매었네요
이 글을 읽지 못 했다면 앞으로 한동안 더 고생할뻔 했습니다
다시한번 감사드립니다
저야 말로 감사드리네요. 이렇게 댓글 남겨주시는게 저로선 큰 도움이 된답니다.^^
저야 말로 다시 한번 감사드립니다 :)
Great blog here i like all the information thats being shared, congratulations.
Your Blog is very professional, I like your info thanks for the help.
If you want to use the official bag for mild cases, try to use the Buy Replica Handbags. Custom printed promotional bag is made with materials that are readily available. In fact, you can use recycled paper so that it can serve other purposes, and the promotion of ecological reconstruction.
to be almost the ultimate Black Swan event to the outside observer – might actually have been p
choices the people in those nations faced. Ten years ago, the choice was between the Lexus
Mieszarka synonimów to proste narzędzie pozwalające na generowanie tekstów z zagnieżdżonych w
I must voice my affection for your kind-heartedness for men and women that absolutely need help on the situation. Your real commitment to getting the message throughout had been particularly important and have regularly permitted ladies like me to realize their objectives. Your invaluable publication entails so much to me and somewhat more to my colleagues. Best wishes; from each one of us.
My wife and i ended up being ecstatic when Edward managed to round up his investigations through the ideas he got while using the weblog. It's not at all simplistic to simply happen to be handing out guidance which usually others might have been selling. So we discover we need the website owner to be grateful to for that. The specific illustrations you have made, the easy blog menu, the relationships you make it easier to instill - it is most amazing, and it's leading our son and the family believe that the concept is enjoyable, which is certainly truly vital. Thanks for all!
I read your site for a while. Thanks for your contributions.
I was aware about this previously, but still there are some useful pieces which finalized the image to me, thanks a ton!
Well, that is excellent, however how about additional choices we have here? Would you mind crafting one more post regarding all of them also? Cheers!
That seems decent although i am still not so certain that I like it. Regardless will look a lot more into it and decide personally! :)
That appears fine though i'm still not so sure that I like it. At any rate will look even more into it and decide personally! :)
We definitely have to think far more in that course and find out things i can do about it.
Why didnt I think about this? I hear specifically what youre saying and Im so pleased that I came across your weblog. You truly know what youre talking about, and you created me feel like I really should discover more about this. Thanks for this; Im officially a enormous fan of your blog.
Youre so right. Im there with you. Your weblog is definitely worth a read if anybody comes across it. Im lucky I did because now Ive got a entire new view of this. I didnt realise that this problem was so essential and so universal. You definitely put it in perspective for me.
It is actually a nice and helpful piece of info. I'm glad that you just shared this useful information with us. Please keep us informed like this. Thanks for sharing.
Thanks a lot for sharing this with all folks you actually recognise what you are speaking about! Bookmarked. Kindly also seek advice from my web site =). We will have a hyperlink alternate arrangement between us!
I have not checked in here for a while as I thought it was getting boring, but the last few posts are good quality so I guess I will add you back to my everyday bloglist. You deserve it my friend :)
Hi there! Wonderful post! Please do inform us when we shall see a follow up!
Hi there! Wonderful post! Please inform us when I can see a follow up!
Hi! Nice stuff, please tell me when you finally post something like this!
Hey there! Good post! Please keep us posted when all will see a follow up!
Hi! Great post! Please tell us when I can see a follow up!
Aw, this was a genuinely good quality post. In theory I'd like to write like this too - taking time and real effort to make a good write-up... but what can I say... I procrastinate alot and never seem to get some thing performed.
I personally have embraced the new technologies along with the CMS platforms, I feel the new tools only make the web designs far better. I'm glad that new technologies are coming out in internet design that make issues simpler, improved, and greater seeking for design.
shit i cannot wait any longer because of this film!!
I ought to say, youve got one of the best blogs Ive seen in a lengthy time. What I wouldnt give to be able to create a weblog thats as fascinating as this. I guess Ill just have to keep reading yours and hope that 1 day I can write on a subject with as significantly information as youve got on this one!
Im impressed. I dont feel Ive met anybody who knows as much about this subject as you do. Youre truly properly informed and quite intelligent. You wrote some thing that folks could recognize and produced the subject intriguing for everyone. Genuinely, excellent weblog youve got here.
I hope you in no way stop! This is one of the greatest blogs Ive ever read. Youve got some mad skill here, man. I just hope that you dont lose your style due to the fact youre undoubtedly among the coolest bloggers out there. Please keep it up since the internet requirements a person like you spreading the word.
You sure do know what youre talking about. Man, this blog is just excellent! I cant wait to read far more of what youve got to say. Im really pleased that I came across this when I did due to the fact I was truly beginning to obtain bored with the entire blogging scene. Youve turned me around, man!
Your blog is fine. I just desire to comment on the design. Its too loud. Its performing way an excessive amount of and it takes away from what youve got to say --which I feel is actually essential. I dont know in case you didnt think that your words could hold everyones attention, but you had been wrong.
Yes, thats precisely what I wanted to hear! Wonderful stuff here. The details as well as the detail were just excellent. I believe that your perspective is deep, its just nicely thought out and genuinely amazing to see someone who knows how you can put these thoughts down so properly. Excellent job on this.
This is among the most extraordinary blogs Ive read in a really lengthy time. The amount of details in here is stunning, like you practically wrote the book on the subject. Your blog is great for any person who wants to recognize this subject much more. Excellent stuff; please maintain it up!
This is certainly a topic thats close to me so Im happy that you wrote about it. Im also pleased that you did the subject some justice. Not just do you know a great deal about it, you know how to present in a way that people will wish to read much more. Im so happy to know an individual like you exists on the internet.
I dont know what it really is about this blog that turns me off so significantly, but you just dont appear to obtain me excited. I dont know if its the lack of content or just the way you wrote it. But you genuinely dont appear to realize that your readers may possibly not agree with you. Youre actually just too out there for me.
Dude, please tell me that youre going to write much more. I notice you havent written an additional blog for a while (Im just catching up myself). Your blog is just too critical to be missed. Youve got so significantly to say, such understanding about this subject it could be a shame to see this blog disappear. The world wide web requirements you, man!
All I can say is maintain it up. This blog is so required in a time when everyone just desires to talk about how numerous individuals someones cheated on their wife with. I mean, thanks for bringing intelligence back to the internet, its been sorely missed. Wonderful stuff. Please keep it coming!
All I can say is maintain it up. This blog is so needed in a time when everybody just desires to talk about how a lot of folks someones cheated on their wife with. I mean, thanks for bringing intelligence back to the internet, its been sorely missed. Wonderful stuff. Please maintain it coming!
You...are...awesome! This weblog is so wonderful. I actually hope a lot more folks read this and get what youre saying, simply because let me tell you, its essential stuff. I in no way wouldve thought about it this way unless Id run into your blog. Thanks for putting it up. I hope you've fantastic success.
Its like you read my mind! You appear to know so considerably about this, like you wrote the book in it or something. I feel which you could do with some pics to drive the message residence a bit, but other than that, this is excellent weblog. A fantastic read. Ill certainly be back.
Hello,I adore reading by means of your weblog, I wanted to leave just a little comment to support you and wish you a fantastic continuation. Wishing you the top of luck for all your blogging efforts.
Youre so appropriate. Im there with you. Your weblog is certainly worth a read if anybody comes across it. Im lucky I did simply because now Ive got a entire new view of this. I didnt realise that this problem was so essential and so universal. You definitely put it in perspective for me.
Im a large fan already, man. Youve carried out a brilliant job generating sure that men and women realize where youre coming from. And let me tell you, I get it. Wonderful stuff and I cant wait to read more of your blogs. What youve got to say is critical and needs to be read.
All I can say is keep it up. This blog is so essential in a time when every person just wants to talk about how a lot of folks someones cheated on their wife with. I mean, thanks for bringing intelligence back to the web, its been sorely missed. Fantastic stuff. Please maintain it coming!
I wish to say that I dont think Ive read anything so true in a long time. Youve got plenty of fantastic suggestions, an excellent deal of perspective. I believe which you undoubtedly have something important to say and Im gonna back it 110%, man. Excellent job keeping this subject alive and interesting!
I like it! I like it a lot. You know precisely what youre talking about, precisely where other folks are coming from on this problem. Im glad that I had the fortune to stumble across your blog. Its certainly an essential problem that not enough people are talking about and Im glad that I got the opportunity to see all the angles.
You...are...my...hero!!! I cant believe some thing like this exists online! Its so accurate, so honest, and a lot more than which you dont sound like an idiot! Finally, someone who knows the best way to talk about a subject with out sounding like a kid who didnt get that bike he wanted for Christmas.