MySQL一主多从
主192.168.65.128上配置/etc/my.cnf1
2
3
# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL.. SUCCESS!
备份数据
从1、192.168.65.129的/etc/my.cnf
1
2
3
# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
从2、192.168.65.130的/etc/my.cnf
1
2
3
# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
主上备份数据
1
# mysqldump -uroot -p123456 --all-databases > mysqlall.sql
传输数据到从上
1
2
# scp mysqlall.sql 192.168.65.129:/tmp/
# 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
# 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
成功！
页:
[1]