MySql 主从集群配置
一、准备环境
三台机器 其中190是住,其余俩是节点
192.168.0.190 master
192.168.0.162 slave
192.168.0.163 slave
确保版本一致,启动mysql。
二、修改配置文件
master:
lin@lin190:/etc/mysql$ sudo vim my.cnf
添加下面代码
[mysqld]
log-bin=mysql-bin
server-id=190
开启二进制,server-id 一般写上ip 的后两位 方便自己识别
slave--162:
lin@lin162:~$ sudo vim /etc/mysql/my.cnf
[mysqld]
log-bin=mysql-bin
server-id=162
163 同上server-id为163
三、授权用户
重启三台机器的mysql
sudo service mysql restart
使用master机器创建新用户,并授权。一般不用root用户
lin@lin190:~$ mysql -u root -p
GRANT REPLICATION SLAVE ON *.* to 'lin'@'%' identified by 'fulong'
查看master状态
SHOW MASTER STATUS;
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 107 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.01 sec)
四、配置slave
在162,163 slave上链接master
mysql> change master to master_host='192.168.0.190',master_user='lin',master_password='fulong',master_log_file='mysql-bin.000001',master_log_pos=107;
Query OK, 0 rows affected (0.04 sec)
启动从服务
start slave
查看slave状态
show slave status\G
五、主从测试
在master建立数据库slavetest;
mysql> create database slavetest;
创建表并插入数据
mysql> use slavetest;
Database changed
mysql> create table slave_test(id int(6),name varchar(10));
Query OK, 0 rows affected (0.09 sec)
mysql> insert into slave_test values(000001,'linlin');
Query OK, 1 row affected (0.09 sec)
六、查看结果
分别在三台机器查看数据
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| slavetest |
+--------------------+
4 rows in set (0.00 sec)
mysql> select * from slave_test ;
+------+--------+
| id | name |
+------+--------+
| 1 | linlin |
+------+--------+
1 row in set (0.00 sec)