MySQL一主多从

terte

主192.168.65.128上配置/etc/my.cnf

1 2 3

[iyunv@localhost ~]# /etc/init.d/mysqld restart Shutting down MySQL.. SUCCESS! Starting MySQL.. SUCCESS!

备份数据
从1、192.168.65.129的/etc/my.cnf

1 2 3

[iyunv@localhost ~]# /etc/init.d/mysqld restart Shutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS!

从2、192.168.65.130的/etc/my.cnf

1 2 3

[iyunv@localhost ~]# /etc/init.d/mysqld restart Shutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS!

主上备份数据

1	[iyunv@localhost ~]# mysqldump -uroot -p123456 --all-databases > mysqlall.sql

传输数据到从上

1 2	[iyunv@localhost ~]# scp mysqlall.sql 192.168.65.129:/tmp/ [iyunv@localhost ~]# scp mysqlall.sql 192.168.65.130:/tmp/

主上创建用作主从同步的虚拟用户

1 2 3 4 5 6 7

mysql> grant replication slave on *.* to 'repl'@192.168.65.129 identified by '123456'; Query OK, 0 rows affected (0.00 sec) mysql> grant replication slave on *.* to 'repl'@192.168.65.130 identified by '123456'; Query OK, 0 rows affected (0.00 sec) mysql> show master status;

锁表（做实验没有数据变化，可以不锁表）

1 2	mysql> flush tables with read lock; Query OK, 0 rows affected (0.00 sec)

在从上恢复数据

1	[iyunv@localhost /]# mysql -uroot -p123456 < /tmp/mysqlall.sql

1 2 3 4 5 6 7 8 9 10

mysql> stop slave; Query OK, 0 rows affected (0.00 sec) mysql> change master to master_host = '192.168.65.128', master_user = 'repl', master_port=3306, master_password='123456', master_log_file = 'thermos.000001', master_log_pos=542; Query OK, 0 rows affected, 2 warnings (0.01 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec) mysql> show slave statusG

回到主去解锁：

1	mysql> unlock tables;

和从1一样，配置从2

成功！