he'story: mysql

./configure --prefix=/usr/local/mysql --localstatedir=/usr/local/mysql/data --with-charset=utf8
make
make install
cd /usr/local/mysql/bin
./mysql_install_db
useradd -M mysql
chown -R mysql.mysql /usr/local/mysql/data
/usr/local/mysql/bin/mysqld_safe &

해주시면 끝입니다. mysql 접속은

/usr/local/mysql/bin/mysql -u root -p

my.cnf 파일에 포트를 설정해 놓으면 다중으로 mysql 을 띄울수 있다.
(물론.. 설치도 쓰는만큼해야겠지만!)

한 시스템에서 두개의 mysql 띄우는 법...
--[ my.cnf ]--
...
중략
# The following options will be passed to all MySQL clients
[client]
#password    = your_password
port    = 3306    #mysql2일때는 = 3307 (포트는 서로 다르기만 하면 됩니다)
socket    = /tmp/mysql.sock1 #mysql2일때는 = /tmp/mysql.sock2

# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock

중략
....

-------

각각 데몬 띄우기
mysql1은 /usr/local/mysq1/bin/mysqld_safe --user=mysql &
mysql2는 /usr/local/mysq2/bin/mysqld_safe --user=mysql &
이렇게 띄우시고요

중요한건 접속하실 때
mysql1은 /usr/local/mysq1/bin/mysql --socket=/tmp/mysql.sock1 -P 3306 -u사용자 -p
mysql2는 /usr/local/mysq2/bin/mysql --socket=/tmp/mysql.sock2 -P 3307 -u사용자 -p

암턴 이렇습니다.

------------------mysql database user privileges(권한설정)

Global level

GRANT ALL ON *.* TO 'someuser'@'somehost';
GRANT SELECT, INSERT ON *.* TO 'someuser'@'somehost';

Database level

GRANT ALL ON mydb.* TO 'someuser'@'somehost';
GRANT SELECT, INSERT ON mydb.* TO 'someuser'@'somehost';

Table level

GRANT ALL ON mydb.mytbl TO 'someuser'@'somehost';
GRANT SELECT, INSERT ON mydb.mytbl TO 'someuser'@'somehost';

Column level

GRANT SELECT (col1), INSERT (col1,col2) ON mydb.mytbl TO 'someuser'@'somehost';

Routine level

GRANT CREATE ROUTINE ON mydb.* TO 'someuser'@'somehost';
GRANT EXECUTE ON PROCEDURE mydb.myproc TO 'someuser'@'somehost';

SET PASSWORD statement. For example:

GRANT ... IDENTIFIED BY 'mypass';

GRANT ...
IDENTIFIED BY PASSWORD '*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4';

---------------------------sql replication ..

* 환경 MySQL 4.1x 이상

Master Server

[root@ihelpers log]# /usr/local/mysql/bin/mysql -uroot

mysql> grant replication slave on *.* to repl@'%' identified by 'repl';
Query OK, 0 rows affected (0.00 sec)

mysql> grant reload,super on *.* to repl@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

> vi /etc/my.cnf

server-id = 1
binlog-do-db = test
binlog-ignore-db = mysql

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

[root@ihelpers log] rsync -avz test 210.116.xxx.xxx::mysql

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

Slave Server

> vi /etc/my.cnf

server-id=2
master-host=210.116.xxx.xxx
master-user=repl
master-password=xxx
master-port=3306
replicate-do-db=test

[smson@smson smson]$ /usr/local/mysql/bin/mysql -uroot

mysql> CHANGE MASTER TO MASTER_HOST='192.168.2.43', MASTER_PORT=3306, MASTER_USER='repl', MASTER_PASSWORD='j1273k';

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status;

확인

Master

mysql> use test;
Database changed
mysql> create table a ( a int );
Query OK, 0 rows affected (0.00 sec)

Slave

mysql> show slave status;
mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| a |
+----------------+
1 row in set (0.00 sec)

Management

binary logs 파일 초기화

echo "reset master" | /usr/local/mysql/bin/mysql -uroot -pxxxx

[root@smson var]# ls -al smson-bin*
-rw-rw----    1 mysql    mysql    1073742010 Dec 26 18:30 smson-bin.000020
-rw-rw----    1 mysql    mysql    470597632 Jan 6 09:57 smson-bin.000021
-rw-rw----    1 mysql    mysql 786 Jan 6 10:05 smson-bin.000022
-rw-rw----    1 mysql    mysql    1973 Jan 6 10:10 smson-bin.000023
-rw-rw----    1 mysql    mysql 3386154 Jan 6 10:33 smson-bin.000024
-rw-rw----    1 mysql    mysql 456 Jan 6 10:22 smson-bin.index

mysql> PURGE MASTER LOGS TO 'smson-bin.000020';
Query OK, 0 rows affected (0.00 sec)

he'story

6/02/2009

mysql

No comments:

Post a Comment