mysql主从设置

来源：互联网发布：js 下载图片到本地编辑：程序博客网时间：2024/06/10 09:59

aster/slave-master/slave
一、测试机器
10.15.2.240（主）
10.15.2.243（从/主）
10.15.2.20（从）

二、安装mysql

三、配置
1、10.15.2.240
[mysqld]
server-id = 1
log-bin
binlog-do-db= MAIN
mysql> GRANT REPLICATION SLAVE ON *.*
-> TO 'mja'@10.15.2.243 IDENTIFIED BY 'abcdefg';

2、10.15.2.243
[mysqld]
server-id = 2
master-host = 10.15.2.240
master-user = mja
master-password = abcdefg
master-port = 3306
log-bin
replicate-do-db=MAIN
master-connect-retry=60
log-slave-updates
mysql> GRANT REPLICATION SLAVE ON *.*
-> TO 'mjb'@10.15.2.20 IDENTIFIED BY '123456';

3、10.15.2.20
[mysqld]
server-id = 3
master-host = 10.15.2.243
master-user = mjb
master-password = 123456
master-port = 3306
log-bin
replicate-do-db=MAIN
master-connect-retry=60

四、测试
1、10.15.2.240
mysql> create table test30 (NAME VARCHAR(8), AGE INT NOT NULL, WEIGHT INT, SEX ENUM("F","M") );
Query OK, 0 rows affected (0.21 sec)

2、10.15.2.243
mysql> use MAIN;
Database changed
mysql> show tables;
+----------------+
| Tables_in_MAIN |
+----------------+
| MYTABLE |
| aa |
| aaaa |
| cc |
| ccc |
| my1 |
| test |
| test1 |
| test10 |
| test11 |
| test12 |
| test13 |
| test15 |
| test2 |
| test20 |
| test21 |
| test3 |
| test30 |
| test4 |
| test5 |
| test7 |
| test8 |
+----------------+
22 rows in set (0.00 sec)

3、10.15.2.20
mysql> use MAIN;
Database changed
mysql> show tables;
+----------------+
| Tables_in_MAIN |
+----------------+
| MYTABLE |
| aa |
| aaaa |
| cc |
| ccc |
| my1 |
| test |
| test1 |
| test10 |
| test11 |
| test12 |
| test13 |
| test15 |
| test2 |
| test20 |
| test21 |
| test3 |
| test30 |
| test4 |
| test5 |
| test7 |
| test8 |
+----------------+
22 rows in set (0.00 sec)