1. mysql 설치
#tar -xf mysql-5.7.18-1.el6.x86_64.rpm-bundle.tar #yum localinstall *.rpm
1.1 mysql 구성 수정
# For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html [mysqld] # # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M datadir=/data/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid explicit_defaults_for_timestamp=true tmpdir=/tmp [client] default-character-set=utf8mb4 [mysqld] character_set_server=utf8mb4
1.2 권한 수정
[root@172 ~]# chown -R mysql:mysql /data [root@172 ~]# chmod 777 -R /data/ [root@172 ~]# chmod -R 777 /tmp
1.3 mysql 서비스 시작
[root@172 ~]# service mysqld restart Stopping mysqld: [FAILED] Initializing MySQL database: [ OK ] Installing validate password plugin: [ OK ] Starting mysqld: [ OK ]
1.4 임시 비밀번호 보기
more /var/log/mysqld.log |grep temporary
1.5 루트 비밀번호 수정
db1
ALTER USER 'root'@'localhost' IDENTIFIED BY '*****'; flush privileges; exit;
db2
ALTER USER 'root'@'localhost' IDENTIFIED BY '*****'; flush privileges; exit;
2. 마스터-슬레이브 동기화 구성
| master1 | 172.28.8.187 |
|---|---|
| master2 | 172.28. 8.18 8 |
2.1 사용되는 비밀번호 구성 master1을 통해 master2에 로그인
Master1
create user 'repl' identified by '*****'; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'172.28.8.188' IDENTIFIED BY '*****'; FLUSH PRIVILEGES; mysql> create database mydb default charset utf8;
재사용자가 172.28.8.188
mysql -urepl -p -h172.28.8.187
2.1.1에서 데이터베이스에 로그인할 수 있는지 테스트 2.2.1 Master2에서 Master 1로 계정 비밀번호 생성 및 인증
# For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html [mysqld] # # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M datadir=/data/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid explicit_defaults_for_timestamp=true tmpdir=/tmp character_set_server=utf8mb4 server-id=177 log-bin=/var/log/mysql/mysql-bin.log read-only=0 binlog-ignore-db=mysql binlog-ignore-db=information_schema expire_logs_days= 365 auto-increment-increment = 2 auto-increment-offset = 1 [client] default-character-set=utf8mb4
2.3 마스터 동기화 상태 보기
master1#除server-id外，其他与master1保持一致
master2
create user 'repl' identified by '*****'; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'172.28.8.187' IDENTIFIED BY '*****'; FLUSH PRIVILEGES;
master1을 master2에서 동기화하도록 설정
mysql> show master status; +------------------+----------+--------------+--------------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+--------------------------+-------------------+ | mysql-bin.000001 | 154 | mydb | mysql,information_schema | | +------------------+----------+--------------+--------------------------+-------------------+ 1 row in set (0.00 sec)
master2를 동기화하도록 설정 from master1
mysql> show master status; +------------------+----------+--------------+--------------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+--------------------------+-------------------+ | mysql-bin.000001 | 154 | mydb | mysql,information_schema | | +------------------+----------+--------------+--------------------------+-------------------+ 1 row in set (0.00 sec)
표시되는 경우 다음 두 항목은 구성이 성공했음을 나타냅니다!
mysql>CHANGE MASTER TO MASTER_HOST='172.28.8.188',MASTER_PORT=3306,MASTER_USER='repl',MASTER_PASSWORD='b4l:GGtG3s0*',MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=860; mysql> SHOW SLAVE STATUS\G mysql> START SLAVE; mysql> SHOW SLAVE STATUS\G
3. 듀얼 마스터 동기화 테스트master1 mysql 데이터베이스를 입력하세요.
mysql>CHANGE MASTER TO MASTER_HOST='172.28.8.187',MASTER_PORT=3306,MASTER_USER='repl',MASTER_PASSWORD='2S1*8pr+BzqH^8T`',MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=1497; mysql> SHOW SLAVE STATUS\G mysql> START SLAVE; mysql> SHOW SLAVE STATUS\G
master2를 입력하여 crm 데이터베이스와 직원 테이블이 있는지 확인하세요.
Slave_IO_Running: Yes Slave_SQL_Running: Yes
master2에 방금 삽입한 데이터가 있는지 master1에서 확인하세요.
mysql> create database crm;
Query OK, 1 row affected (0.00 sec)
mysql> use crm;
Database changed
mysql> create table employee(id int auto_increment,name varchar(10),primary key(id));
Query OK, 0 rows affected (0.00 sec)
mysql> insert into employee(name) values('a');
Query OK, 1 row affected (0.00 sec)
mysql> insert into employee(name) values('b');
Query OK, 1 row affected (0.00 sec)
mysql> insert into employee(name) values('c');
Query OK, 1 row affected (0.06 sec)
mysql> select * from employee;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 3 | b |
| 5 | c |
+----+------+
3 rows in set (0.00 sec)
추천 학습: "mysql 비디오 튜토리얼
"