OS : Centos 7.x
MariaDB : 10.4master1 : 192.168.10.1
slave1 : 192.168.10.2
1. master1 서버에버 db dump 후 slave1서버에 database insert
|
1 |
[root@master1]# mysqldump -u root --all-databases > xinet_server_all.sql |
2. slaver 서버에서 database insett
|
1 |
[root@slave1 ]# mysql -u root -p < xinet_server_all.sql |
3. master1 서버에서 server.cnf 환경설정 추가 [mysqld] 항목에 추가해야함
|
1 2 3 4 5 6 7 8 9 10 |
[root@master1 ~]# vi /etc/my.cnf.d/server.cnf [mysqld] ### replication server_id=1 log-basename=master1 log_bin #binlog-do-db=xinet_word #binlog-do-db=pdns |
4. mariadb db restart (master1)
|
1 |
[root@master1 ~]# systemctl restart mariadb |
5. master1 server mysql 접속 사용자 추가 (slave1에서 접근할 사용자 추가)
|
1 2 3 4 5 6 7 8 9 |
[root@master1 ~]# mysql -u root -p mysql MariaDB [(none)]> create user 'replication_user'@'%' identified by 'replication_user00##'; Query OK, 0 rows affected (0.001 sec) MariaDB [(none)]> grant replication slave on *.* to replication_user; Query OK, 0 rows affected (0.002 sec) |
6. master1 데이터베이스 status값 확인
|
1 2 3 4 5 6 7 8 9 10 |
MariaDB [(none)]> show master status; +--------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +--------------------+----------+--------------+------------------+ | master1-bin.000002 | 5338 | | | +--------------------+----------+--------------+------------------+ 1 row in set (0.001 sec) master1-bin.000002 값과 5338값을 기억 |
7. slave1 서버에서 server.cnf 내용 추가
|
1 2 3 4 5 6 7 |
[root@slave1]# vi /etc/my.cnf.d/server.cnf [mysqld] server-id=2 log-bin log-basename=master1 report-host=slave1 |
8. mariadb 재시작 (slave1)
|
1 |
[root@slaver1]# systemctl restart mariadb |
9. slave1 mysql 접속 후 server_id값 확인 후 master정보값 업데이트
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
[root@slave1]# mysql -u root -p mysql MariaDB [(none)]> SHOW VARIABLES LIKE 'server_id'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 2 | +---------------+-------+ MariaDB [(none)]> change master to master_host='192.168.10.1', master_user='replication_user', master_password='replication_user00##', master_port=3306, master_log_file='master1-bin.000002', master_log_pos=5538, master_connect_retry=10; Query OK, 0 rows affected (0.017 sec) ### slaver 시작 MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.001 sec) |
10. 상태값 확인
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 |
MariaDB [(none)]> SHOW SLAVE STATUS \G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 218.145.31.50 Master_User: replication_user Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master1-bin.000002 Read_Master_Log_Pos: 30624 Relay_Log_File: master1-relay-bin.000002 Relay_Log_Pos: 5830 Relay_Master_Log_File: master1-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 30624 Relay_Log_Space: 6141 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: conservative SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it Slave_DDL_Groups: 0 Slave_Non_Transactional_Groups: 14 Slave_Transactional_Groups: 0 1 row in set (0.000 sec) ERROR: No query specified |
해당값에서 2개의 값이 모두 Yes로 표시되어야 한다 Slave_IO_Running: Yes Slave_SQL_Running: Yes
그럼 실제 데이터베이스를 하나 만들어서 테이블을 만들고 데이터를 입력해서 replication 잘되는지 테스트 해보자
master1 데이터베이스 서버에서 작업
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
MariaDB [(none)]> create database sync_dbtest; Query OK, 1 row affected (0.001 sec) MariaDB [(none)]> use sync_dbtest; Database changed MariaDB [sync_dbtest]> create table Users (id int(3) primary key, name varchar(20), email varchar(20), country varchar(20), password varchar(20)); Query OK, 0 rows affected (0.003 sec) MariaDB [sync_dbtest]> INSERT INTO Users (id, name, email, country, password) VALUES (1, 'admin', 'admin@xinet.kr', 'korea', 'pankaj123'); Query OK, 1 row affected (0.001 sec) MariaDB [sync_dbtest]> INSERT INTO Users (id, name, email, country, password) VALUES (4, 'David', 'david@gmail.com', 'USA', 'david123'); Query OK, 1 row affected (0.000 sec) MariaDB [sync_dbtest]> select * from Users; +----+-------+-----------------+---------+-----------+ | id | name | email | country | password | +----+-------+-----------------+---------+-----------+ | 1 | admin | admin@xinet.kr | korea | pankaj123 | | 4 | David | david@gmail.com | USA | david123 | +----+-------+-----------------+---------+-----------+ 2 rows in set (0.000 sec) |
slaver1에서 데이터 확ㅇ니
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
[root@slave1 ~]# mysql -u root -p mysql Enter password: MariaDB [(none)]> use sync_dbtest; Database changed MariaDB [sync_dbtest]> show tables; +-----------------------+ | Tables_in_sync_dbtest | +-----------------------+ | Users | +-----------------------+ 1 row in set (0.000 sec) MariaDB [sync_dbtest]> select * from Users; +----+-------+-----------------+---------+-----------+ | id | name | email | country | password | +----+-------+-----------------+---------+-----------+ | 1 | admin | admin@xinet.kr | korea | pankaj123 | | 4 | David | david@gmail.com | USA | david123 | +----+-------+-----------------+---------+-----------+ 2 rows in set (0.001 sec) MariaDB [sync_dbtest]> |