[Linux] mysql replication

   Á¶È¸ 3785   Ãßõ 0    

http://ehostidc.co.kr/center/EH050402.php?no=141489&page=8&choose=tit¡¦ (242)
https://blog.naver.com/goethe1/221642889788 (356)

OS : CentOS 7.4

mysql : mariadb-5.5.56-2

1. master 서버

a. 환경 설정

[root@ns1 opt]# vi /etc/my.cnf

server-id=1
log-bin=mysql-bin

[root@ns1 opt]# systemctl restart mariadb

b. 권한 설정

[root@ns1 opt]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or g.
Your MariaDB connection id is 121
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type "help;" or "h" for help. Type "c" to clear the current input statement.

MariaDB [(none)]> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mysql]> grant replication slave on *.* to "pdns"@"Slave_IP" identified by "패스워드";

Query OK, 0 rows affected (0.00 sec)

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




c. DB dump

[root@ns1 opt]# mysqldump -u root -p -A > master.sql





해당 sql 파일을 2차 디버 서버로 복사

2. slave 서버

a. 환경 설정

[root@ns2 opt]# vi /etc/my.cnf

server-id=2

log-bin=mysql-bin

b. db import 및 restart

[root@ns2 ehost]# mysql -u root -p < /home/ehost/master.sql

[root@ns1 opt]# systemctl restart mariadb

3. replication 설정

a. master 서버

[root@ns1 opt]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or g.
Your MariaDB connection id is 124
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type "help;" or "h" for help. Type "c" to clear the current input statement.

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 1184 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)




b. slave 서버

[root@ns2 opt]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or g.
Your MariaDB connection id is 124
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type "help;" or "h" for help. Type "c" to clear the current input statement.

MariaDB [(none)]> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

MariaDB [(none)]> reset slave;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> change master to master_host="master_IP", master_user="pdns", master_password="패스워드", master_log_file="mysql-bin.000002", master_log_pos=1184;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show slave status;
+----------------------------------+--------------+-------------+-------------+---------------+------------------+---------------------+--------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+
| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id |
+----------------------------------+--------------+-------------+-------------+---------------+------------------+---------------------+--------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+
| Waiting for master to send event | master_IP | pdns | 3306 | 60 | mysql-bin.000002 | 1184 | mariadb-relay-bin.000002 | 529 | mysql-bin.000002 | Yes | Yes | | | | | | | 0 | | 0 | 1184 | 825 | None | | 0 | No | | | | | | 0 | No | 0 | | 0 | | | 1 |
+----------------------------------+--------------+-------------+-------------+---------------+------------------+---------------------+--------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+
1 row in set (0.00 sec)




4. mysql replication 의 단점

create database 문은 replication 되지 않음으로 인해 신규 디비를 생성할 경우 master / slave 모두 create 필수



https://www.aiocp.co.kr/ ( 딥러닝,머신러닝 서버 판매 ,컨설팅) https://bigbangcloud.co.kr/ ( GPU 클라우드 서비스) ::: AI 서버의 모든것 ::: 인공지능의 시작~ (주)이호스트ICT
ªÀº±Û Àϼö·Ï ½ÅÁßÇÏ°Ô.


PDS
Á¦¸ñPage 2/76
2014-05   4443293   Á¤ÀºÁØ1
2015-12   999315   ¹é¸Þ°¡
2023-10   31016   »õÇϾᱸ¸§
2023-09   45733   ¹é·æ
2023-09   52548   Á¦¿ÂÇÁ·Î
2023-09   49087   ±èÀå¿ì
2023-09   43066   ¹ÌÄ£°¨ÀÚ
2023-08   39229   ´Ù·ÕÀÌ
2023-08   47421   Á¦¿ÂÇÁ·Î
2023-08   30621   ÀÌõdz
2023-08   28425   »õÃÑ
2023-08   29347   ¹ÌÄ£°¨ÀÚ
2023-07   37833   MikroTikÀÌÁø
2023-07   28921   ²Ü¹úlÃÖÀÎÇõ
2023-07   15590   Noobmaster69
2023-07   19335   °£Àå°ÔÀå
2023-07   17604   »õÃÑ
2023-07   16934   ºÀ·¡
2023-07   18358   ±è´ö¿µ
2023-06   24885   µ¿·Â
2023-06   26420   ´Ù·ÕÀÌ
2023-06   22351   ´Ù·ÕÀÌ