数据库主从服务器搭建(克隆Master与克隆Slave)

环境设定

Master服务器资料

IP:192.168.1.100
PORT:3306
用户名:root
密码:root
Slave用户:repl_user
Slave密码:root

Slave服务器资料

克隆Master

以下操作在Master服务器上进行

要克隆Master,先要把Master的数据备份出来,为避免备份时数据有所变化,先把还在缓冲区没写入表中的数据写到表中,并锁定服务器,不让再进行写操作.

FLUSH TABLES WITH READ LOCK;

备份服务器中所有数据库到sql文件,注意！！锁定表后，不能退出MySQL！！一旦退出MySQL，会自动解锁！

mysqldump -h192.168.1.100 --port=3306 -uroot -proot --all-databases > master_all.sql

备份单个数据库(如inti)时可用

 mysqldump -h192.168.1.100 --port=3306 -uroot -proot --databases init > init.sql

获取当前二进制日志的文件名和位置

SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000003 |     2670 |              |                  |
+-------------------+----------+--------------+------------------+

现在可以解除数据服务器的锁定

UNLOCK TABLES;

以下操作在Slave服务器上进行

确保Slave服务器上没有运行Slave监听,并把从Master上备份出来的数据导到Slave上

STOP SLAVE;
SOURCE master_all.sql

设置Slave服务器监听Master服务器的参数

CHANGE MASTER TO 
MASTER_HOST = '192.168.1.100',
MASTER_PORT = 3306,
MASTER_USER = 'repl_user',
MASTER_PASSWORD = 'root',
MASTER_LOG_FILE='master-bin.000003', /*之前在 SHOW MASTER STATUS; 获取的二进制文件名 */
MASTER_LOG_POS=2670; /*之前在 SHOW MASTER STATUS; 获取的二进制文件位置 */

启动Slave监听

START SLAVE;

Master克隆完毕.

克隆Slave

如果Master服务器的负载本来就很高,再用Master克隆出新的Slave时,会再次增加Master的负载,
此时可以用Slave服务器作为数据源,克隆出一个新的Slave.

以下设定作为数据源的Slave服务器资料

数据源的Slave服务器为Slave1
新的Slave服务器为Slave2
IP:192.168.1.101
PORT:3306
用户名:root
密码:root
新的Slave服务器搭建好后,依然是从Master获得数据源,所以Slave用户和密码于克隆Master时一致.
Slave用户:repl_user
Slave密码:root

以下操作在Slave1上进行

把还在缓冲区没写入表中的数据写到表中,并锁定服务器,不让再进行写操作.

FLUSH TABLES WITH READ LOCK;

备份Slave1中所有数据库到sql文件

mysqldump -h192.168.1.101 --port=3306 -uroot -proot --all-databases > slave_all.sql

获取当前二进制日志的文件名和位置

SHOW SLAVE STATUS\G
/*找到下面两行,记下信息*/
Relay_Master_Log_File: master-bin.000003
Exec_Master_Log_Pos: 2872

解除Slave1的锁定

UNLOCK TABLES;

以下操作在Slave2上进行

确保Slave2上没有运行Slave监听,并把从Slave1上备份出来的数据导到Slave2上

STOP SLAVE;
SOURCE slave_all.sql

设置Slave服务器监听Master服务器的参数

CHANGE MASTER TO 
MASTER_HOST = '192.168.1.100',/*新Slave服务器仍是Master的Slave,所以IP还是Master的*/
MASTER_PORT = 3306,
MASTER_USER = 'repl_user',
MASTER_PASSWORD = 'root',
MASTER_LOG_FILE='master-bin.000003', /*之前在 SHOW SLAVE STATUS\G 获取的二进制文件名 */
MASTER_LOG_POS=2872; /*之前在 SHOW SLAVE STATUS\G 获取的二进制文件位置 */

启动Slave监听

START SLAVE;

Slave克隆完毕.

PS.

单机多实例运行测试时遇到的问题,通过下面命令创建的用户,允许登录的域为%,
但在本机登录时会遇到access deny错误,
需要手动将mysql.user中的域改成127.0.0.1

CREATE USER repl_user ;
GRANT REPLICATION SLAVE ON *.* TO repl_user IDENTIFIED BY 'root';

更改MySQL权限设置后,执行

flush privileges;

方可生效

后续补充

log-slave-updates 参数作用

举例

主服务器Server_id为1,从服务器Server_id为2
master上执行一条insert语句,log-bin记录如下

*************************** 6. row ***************************    
   Log_name: master-bin.000007    
        Pos: 366    
 Event_type: Query    
  Server_id: 1    
End_log_pos: 470    
       Info: insert into init.test (id) values (7),(8),(9)

如果从服务器上开启log-slave-updates,则这条log-bin记录会出现在从服务器的log-bin中,
即从服务器的log-bin中会包含Server_id为1的内容;
否则,从服务器的log-bin只会包含Server_id为2的内容.