이 예제는 루트가 사용자 계정을 master에 추가 했을 경우 slave에 등록 됨을 확인하는 예이다.
master에서 수행
# mysql mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 253 to server version: 4.1.0-alpha-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> select user,host from user;
+---------+-----------+
| user | host |
+---------+-----------+
| repl | % |
| | localhost |
| arirang | localhost |
| jijoe | localhost |
| root | localhost |
| | unix |
| root | unix |
+---------+-----------+
7 rows in set (0.00 sec)
mysql> exit
Bye
# mysql_setpermission
######################################################################
## Welcome to the permission setter 1.2 for MySQL.
## made by Luuk de Boer
######################################################################
What would you like to do:
1. Set password for a user.
2. Add a database + user privilege for that database.
- user can do all except all admin functions
3. Add user privilege for an existing database.
- user can do all except all admin functions
4. Add user privilege for an existing database.
- user can do all except all admin functions + no create/drop
5. Add user privilege for an existing database.
- user can do only selects (no update/delete/insert etc.)
0. exit this program
Make your choice [1,2,3,4,5,0]: 2
kimDB
The new database kimDB will be created
What username is to be created: kim
Username = kim
Would you like to set a password for [y/n]: y
What password do you want to specify for :
Type the password again:
We now need to know from what host(s) the user will connect.
Keep in mind that % means 'from any host' ...
The host please: localhost
Would you like to add another host [yes/no]: n
Okay we keep it with this ...
The following host(s) will be used: localhost.
######################################################################
That was it ... here is an overview of what you gave to me:
The database name : kimDB
The username : kim
The host(s) : localhost
######################################################################
Are you pretty sure you would like to implement this [yes/no]: y
Okay ... let's go then ...
Everything is inserted and mysql privileges have been reloaded.
######################################################################
## Welcome to the permission setter 1.2 for MySQL.
## made by Luuk de Boer
######################################################################
What would you like to do:
1. Set password for a user.
2. Add a database + user privilege for that database.
- user can do all except all admin functions
3. Add user privilege for an existing database.
- user can do all except all admin functions
4. Add user privilege for an existing database.
- user can do all except all admin functions + no create/drop
5. Add user privilege for an existing database.
- user can do only selects (no update/delete/insert etc.)
0. exit this program
Make your choice [1,2,3,4,5,0]: 0
Sorry, hope we can help you next time
# mysql -u root -p
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 255 to server version: 4.1.0-alpha-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show grants for kim@localhost;
+---------------------------------------------------------------------------------------------------------------------------------------+
| Grants for kim@localhost |
+---------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'kim'@'localhost' IDENTIFIED BY PASSWORD '*e47ff17cb6e6fd39bb811e658cd863709f3b10fd3ae0' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER ON `kimDB`.* TO 'kim'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql>
Slave에서 확인
1) master의 DB 변경 전
mysql> select user from user;
+---------+
| user |
+---------+
| repl |
| |
| arirang |
| jijoe |
| root |
| |
| root |
+---------+
7 rows in set (0.18 sec)
mysql>
2) master의 DB 변경 후
mysql> select user,host from user;
+---------+-----------+
| user | host |
+---------+-----------+
| repl | % |
| | localhost |
| arirang | localhost |
| jijoe | localhost |
| kim | localhost |
| root | localhost |
| | unix |
| root | unix |
+---------+-----------+
8 rows in set (0.00 sec)
mysql>
Set Up Replication