1. 복제 설정
1.1 GRANT REPLICATION SLAVE
@all
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.*
TO replicant@'%'
IDENTIFIED BY 'replicant';
1.2 my.cnf 편집
@Master
cd /var/log
mkdir /var/log/mysql
chown –R mysql.mysql ./mysql
service mysqld stop
vi /etc/my.cnf
log_bin = /var/log/mysql/mariadb-bin
[mysqld]
server_id = 101
relay_log = db01-relay-binlog
@Slave
service mysqld stop
vi /etc/my.cnf
[mysqld]
server_id = 102
read_only
@Master, Slave
service mysqld start
1.3 복제 설정
@Master
Ifconfig
mysql -u root -p
show master status;
@Slave
CHANGE MASTER TO MASTER_HOST='10.10.10.51',
MASTER_USER = 'replicant',
MASTER_PASSWORD = 'replicant',
MASTER_PORT=33060,
MASTER_LOG_FILE = 'mariadb-bin.000002',
MASTER_LOG_POS = 330;
START SLAVE;
2. 복제 동작 확인
2.1show slave status 로 확인
@Slave
SHOW SLAVE STATUS;
Slave_IO_Running | Slave_SQL_Running => yes 인가?
2.2 global_status 에서 확인
use mysql
SELECT variable_value
FROM information_schema.global_status
WHERE variable_name='SLAVE_RUNNING';
3. 복제 테스트
3.1 기본 설정
@Master
CREATE DATABASE IF NOT EXISTS temp;
USE temp;
CREATE TABLE doctors(
id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
given_names varchar(255),
surname varchar(255),
birthdate date);
INSERT INTO doctors VALUES(1,'William','Hartnell','1908-01-08');
INSERT INTO doctors VALUES(2,'Patrick','Troughton','1920-03-25');
INSERT INTO doctors VALUES(3,'John','Pertwee','1919-07-07');
INSERT INTO doctors VALUES(4,'Tom','Baker','1934-01-20');
@Slave
show databases;
use temp
show tables;
select * from doctors;
3.2 Replicate_ignore_db
- stop slave, 확인
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SELECT variable_value
-> FROM information_schema.global_status
-> WHERE variable_name='SLAVE_RUNNING';
+----------------+
| variable_value |
+----------------+
| OFF |
+----------------+
1 row in set (0.00 sec)
MariaDB [(none)]> exit
Bye
- my.cnf 에 replicate_ignore_db 추가
[mysql@localhost ~]$ vi /etc/my.cnf
...
[mysqld]
replicate_ignore_db='information_schema'
replicate_ignore_db='performance_schema'
replicate_ignore_db='mysql'
...
- mysqld 재기동, slave 재시작
[mysql@localhost ~]$ service mysqld restart
[mysql@localhost ~]$ sql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 13
Server version: 10.2.7-MariaDB-log 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)]> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
MariaDB [(none)]> stop slave;
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;
- 복제 테스트 ( ignore 확인)
@Master
MariaDB [temp]> use mysql
Database changed
MariaDB [mysql]> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| column_stats |
| columns_priv |
| db |
| event |
| func |
| general_log |
| gtid_slave_pos |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| index_stats |
| innodb_index_stats |
| innodb_table_stats |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| reptest |
| roles_mapping |
| servers |
| slow_log |
| table_stats |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
31 rows in set (0.00 sec)
MariaDB [mysql]> create table aaaa(a int);
Query OK, 0 rows affected (0.02 sec)
MariaDB [(none)]> use mysql
Database changed
MariaDB [mysql]> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| column_stats |
| columns_priv |
| db |
| event |
| func |
| general_log |
| gtid_slave_pos |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| index_stats |
| innodb_index_stats |
| innodb_table_stats |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| reptest |
| roles_mapping |
| servers |
| slow_log |
| table_stats |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
31 rows in set (0.00 sec)
- 복제 테스트 ( 나머지는 되는가 확인)
MariaDB [mysql]> use temp
Database changed
MariaDB [temp]> show tables;
+----------------+
| Tables_in_temp |
+----------------+
| a |
| doctors |
+----------------+
2 rows in set (0.00 sec)
MariaDB [temp]> create table aaa(a int);
Query OK, 0 rows affected (0.02 sec)
MariaDB [mysql]> use temp;
Database changed
MariaDB [temp]> show tables;
+----------------+
| Tables_in_temp |
+----------------+
| a |
| aaa |
| doctors |
+----------------+
3 rows in set (0.00 sec)
'생계 > MySQL' 카테고리의 다른 글
| MariaDB galera cluster mysql 유저 binary 설치 centos (0) | 2017.11.25 |
|---|---|
| MariaDB galera cluster yum install 설치 centos (0) | 2017.11.24 |
| mysql 유저 생성 (0) | 2017.09.08 |
| MariaDB 바이너리 설치 10.2.7 CentOS (0) | 2017.09.08 |
| MariaDB 한글입력 character set 변경 (0) | 2017.09.08 |
