mysql resplication

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)

mysql> show master status;
+---------------------+----------+--------------+------------------+
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------------+----------+--------------+------------------+
| ihelpers-bin.000008 |      126 | test         |                  |
+---------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

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

// rsync 를 사용하지 않고 mysqldump 를 사용해서 소켓간에 데이터를  이동 하자!! multi 상태에서 사용하는거니깐...

{

//master Shell > mysqldump -u root -p ‘password’ -B -S 소캣 위치 db_name > dump_file.sql

//slave shell > mysql -u root -p -S 소캣 위치 < dump_file.sql

}

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

Slave Server

> vi /etc/my.cnf

server-id=2
master-host=192.168.2.x
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';
Query OK, 0 rows affected (0.00 sec)

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)

mysql> show master status;
+---------------------+----------+--------------+------------------+
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------------+----------+--------------+------------------+
| ihelpers-bin.000008 |      185 | test         |                  |
+---------------------+----------+--------------+------------------+
1 row in set (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> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| smson-bin.000024 |  3386563 |              | danlaysis        |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

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